My TABLE STATISTICS GATHER METHOD

1.内容概述

 

  从上面的ORACLE SQL优化和执行流程图可以看出，一条语句从客户端发送到数据库执行，需要经历解析---查询转换----估算---生成执行计划---行源生成。其中为了生成正确的执行计划，除了查询转换这种基础工作，还需要估算器进行估算（估算表和索引等大小，每步返回的行数等）才能选择正确的访问路径和访问方法，这当中，数据库和对象（表、索引、分区等）的统计信息扮演着极其重要的角色。

本文档从统计信息收集和更新流程几个值得注意的方面出发，着重有以下几点：

1）  检查统计信息

2）  收集统计信息

3）  更新统计信息

2.检查统计信息

tabstat.sql用于查看表、列（是否有直方图）、索引、分区等统计信息。（输入表名不区分大小写，可以输入一次之后，COPY输出的结果）

SQL> @tabstat

Enter value for tabname: AB_ACRECORD_0（输入一次后，其它的可以根据输出结果COPY OWNER和TABLE_NAME）
old   1: select owner,table_name from dba_tables where table_name=upper('&tabname')
new   1: select owner,table_name from dba_tables where table_name=upper('AB_ACRECORD_0')
OWNER                          TABLE_NAME
------------------------------ ------------------------------
TESTCADM                       AB_ACRECORD_0
Enter value for table_name: AB_ACRECORD_0
TABLE_NAME
------------------------------
AB_ACRECORD_0
Please enter Name of Table Owner: TESTCADM
...

 

3.收集统计信息

统计信息收集需要根据具体数据分布特征以及结合对应SQL进行，这里说的收集只是一般情况以及收集注意点。一般收集统计信息比例10%足够了，也就是estimate_percent=>10,对于大表收集慢，比如1G以上的，1%-5%一般也可以。（对于月表收集策略，按照同类型表以往收集策略）统计信息收集，注意表、列、索引、分区等统计信息的完整性和准确性。

对原表没有统计信息的表收集，一般有如下方式：

以SPA下表T为例:

1）  直方图由ORACLE自动确定（method_opt不用写）

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);

如果没有收集过统计信息，不确定是否要收集某些列直方图，建议不写method_opt参数，由oracle决定是否收集直方图。

对于月表，则要根据以往表收集策略收集。

2）  不收集直方图（SIZE 1）,收集所有列统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',no_invalidate=>false,cascade=>true,degree => 10);

其中收集比例10%,method_opt=>’FOR ALL COLUMNS SIZE 1’表示不收集直方图，但是收集所有列统计信息，注意不要丢掉ALL（丢掉ALL不收集列统计信息），no_invalidate=>false表示原有游标失效，使用新统计信息。Degree是并行度，根据情况增加或减少。

3）  收集索引列直方图，其它列不收集统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',no_invalidate=>false,cascade=>true,degree => 10);

以上语句只收集索引列直方图，其它列不收集，所以使用可能会因为其它列没有统计信息而导致SQL性能问题。

4）  混合收集，某些列收集直方图某些列不收集

为了解决3）收集索引直方图信息丢失其它列统计信息问题，可以通过下列语句：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY FOR COLUMNS SIZE 1 OBJECT_NAME,OBJECT_TYPE,CREATED',no_invalidate=>false,cascade=>true,degree => 10);

以上是对索引列收集直方图，对其它对应列不收集直方图但是收集统计信息。语法是

FOR ALL INDEXED COLUMNS SIZE SKEWONLY FOR COLUMNS SIZE 1 列1，列2，…

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY FOR COLUMNS SIZE 1 OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME SIZE 254',no_invalidate=>false,cascade=>true,degree => 10);

上面语句与第一条区别在于后面多了LAST_DDL_TIME SIZE 254,此列不是索引列，也收集直方图，buckets为254.

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T1',estimate_percent=>10,method_opt=>

'FOR ALL COLUMNS SIZE 1  FOR COLUMNS SIZE skewonly RESOURCE_CODE,GROUP_ID,LOGIN_ACCEPT',no_invalidate=>false,cascade=>true,degree => 10);

上面语句表示先把所有列统计信息收集一遍（不收集直方图，SIZE 1，然后对RESOURCE_CODE到LOGIN_ACCEPT收集直方图。

解释：method_opt参数用来决定列的统计信息收集方式（直方图OR非直方图）,语法如下：

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

可以是其中一条或几条组合，第3）点种，使用了组合方式，对于for columns [size clause]有很多列是一样的收集方式，直接for columns [size clause] 列1，列2，列3.。。。如果有特殊列，把size_clause放在column后面。如上面last_ddl_time size 254。

其中size_clause语法有： size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY。

  注意不要使用FOR ALL COLUMNS SIZE 1 FOR ALL INDEXED COLUMNS SIZE SKEWONLY方式，否则只会收集索引列统计信息。也就是只能第一个语句使用FOR ALL COLUMNS，后面不能使用FOR ALL，只能是FOR COLUMNS SIZE [FLAG] 列1，列2.。。 FOR COLIMNS 列1 [FLAG], 列2 [FLAG]…

  对于列很多的部分收集直方图部分不收集直方图，上面语法需要拼凑列，比较麻烦，可以采用多次收集方法：

1） 比如先执行FOR ALL COLUMNS SIZE 1,然后执行FOR ALL INDEXED COLUMNS SIZE SKEWONLY，得写多次才能保证索引列有直方图其它列收集普通统计信息。（columns size 1需要先写，如果第二条写columns size 1,则直方图信息全部丢失）

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'DINGJUN123',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL  COLUMNS SIZE 1',no_invalidate=>false,cascade=>true,degree => 10);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'DINGJUN123',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',no_invalidate=>false,cascade=>true,degree => 10);

2） 先执行FOR ALL INDEXED COLUMNS SIZE SKEWONLY,再执行REPEAT,REPEAT可以保证原来有直方图的保留。

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'DINGJUN123',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',no_invalidate=>false,cascade=>true,degree => 10);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'DINGJUN123',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL  COLUMNS SIZE REPEAT',no_invalidate=>false,cascade=>true,degree => 10);

5）  删除统计信息

exec DBMS_STATS.DELETE_TABLE_STATS(ownname=>'SPA',tabname=>'T', force=>true, no_invalidate=>false);

有时候需要删除统计信息后重新收集，性能变好，主要原因在于原有统计信息紊乱，在此基础上重新收集也不准确。

6）  收集指定分区统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',partname=>'P201504',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',no_invalidate=>false,cascade=>true,degree => 10);

增加partname即可，其它按照第3）点组合。

附：update_tabstats.sql

--author:dingjun123

--desc:update table stats from dictionary,gen gather table stats scripts

undefine tabname

undefine owner

undefine degree

set line 200 pagesize 999

col scripts for a200 word_wrapped

select q'[exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'&&owner',tabname=>'&&tabname',estimate_percent=>]' ||

       (case

         when num_rows = 0 then

          null

         else

          round(sample_size / num_rows, 2) * 100

       end) || q'[,method_opt=>]' ||

       (SELECT q'['for all columns size 1 for COLUMNS SIZE SKEWONLY - ]' ||

               listagg(column_name, ',') WITHIN GROUP(ORDER BY column_name)

          from dba_tab_col_statistics

         WHERE table_name = UPPER('&&tabname')

           AND owner = UPPER('&&owner')

           AND histogram <> 'NONE') ||

       q'[',no_invalidate=>false,cascade=>true,degree =>°ree);]' scripts

  from dba_tables

 where table_name = UPPER('&&tabname')

   AND owner = UPPER('&&owner');

4.更新统计信息及其注意点

  原表有统计信息，但是因为收集比例，直方图，统计信息失效等原因，导致不准确，影响SQL执行性能，需要进行更新，更新要注意：

保留原有表对应列直方图信息，除非直方图原因导致SQL性能低下才可以删除或处理对应列统计信息，只因统计信息过旧需要更新的，必须是SIZE REPEAT。

1）  更新统计信息保留原有直方图信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE repeat',no_invalidate=>false,cascade=>true,degree => 10);

使用for all columns size repeat,保证原来列有直方图的不被删除。

2）  有直方图的列更新为普通统计信息，其它列更新

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SPA',tabname=>'T',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE repeat FOR COLUMNS size 1 OBJECT_ID',no_invalidate=>false,cascade=>true,degree => 10);

以上语句对OBJECT_ID，原先有直方图，现在删除直方图，收集普通统计信息，其它列收集方式不变，更新最新统计信息，详细语法参考“收集统计信息”部分第3)点。