Oracle dbms_stats

第一部分：Gathering Optimizer Statistics

 

1. GATHER_DATABASE_STATS

收集数据库中所有对象的统计信息

 

Parameters:

--estimate_percent: 需分析的百分比(NULL means compute)，默认值可以通过SET_PARAM来改变。The valid range is [0.000001,100]

--block_sample:无论是否是随机块采样替代随机行采样，随机块采样的效率都会比较高。除非数据不是随机分布在磁盘上的。DEFAULT FALSE

--method_opt:

FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause]

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

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

- integer: Number of histogram buckets. Must be in the range [1,254].- REPEAT: Collects histograms only on the columns that already have histograms.- AUTO: Oracle determines the columns to collect histograms based ondata distribution and the workload of the columns.- SKEWONLY: Oracle determines the columns to collect histograms based on thedata distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.默认值可以通过SET_PARAM来改变

--degree:并行度，The default for degree is NULL.

--granularity:只有分区表才会用到此参数

--cascade:是否要统计此表上的index,默认为false,默认值可以通过SET_PARAM来改变

--Stattab：User statistics table identifier describing where to save the current statistics.

--Statid:Identifier (optional) to associate with these statistics withinstattab.

--options:默认值GATHER(Gathers statistics on all objects in the schema),

其它选项：

GATHERAUTO: Gathers all necessary statistics automatically

GATHERSTALE: Gathers statistics on stale objects as determined by looking at the *_tab_modificationsviews.

GATHEREMPTY: Gathers statistics on objects which currently have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO, When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown;

LISTSTALE: Returns a list of stale objects as determined by looking at the *_tab_modifications views.

LISTEMPTY: Returns a list of objects which currently have no statistics.

--objlist: List of objects found to be stale or empty

--statown: Schema containingstattab(if different from current schema)

--gather_sys: Gathers statistics on the objects owned by the'SYS'user，默认值TRUE

--no_invalidate: 设置为TRUE时，相关的游标不会失效; 反之，游标失效。

 

2. GATHER_DICTIONARY_STATS

此过程收集统计数据字典用户’SYS’,’SYSTEM’以及RDBMS组件用户.

必须有SYSDBA或ANALYZEANYDICTIONARY和ANALYZEANYSYSTEM权限才能执行此过程。

参数：

--comp_id：指定需统计用户的COMP_ID,通过DBA_REGISTRY视图来查看COMP_ID。如此值为NULL，则统计所有RDBMS组件的用户。此管是否有此参数，’SYS’,’SYSTEM’用户都会被统计。

其它参数estimate_percent--block_sample --method_opt –degree –granularity –cascade –stattab –statid –options –objlist –statown --no_invalidate都同上

 

3. GATHER_FIXED_OBJECTS_STATS

此过程收集统计所有的动态性能表

必须有SYSDBA或具有ANALYZEANYDICTIONARY权限才能执行此过程。

参数：stattab –statid –statown --no_invalidate

 

4. GATHER_INDEX_STATS

此过程收集索引的统计。一些参数被限制的，不会并行处理cluster indexes, domain indexes,和bitmap join indexes，granularity和no_invalidate参数在这些index中不起作用。

参数：

--ownname：被统计的schema

-- indname:index名

--Partname：Name of partition

--force：即使表被锁定，也强行统计

--estimate_percent–stattab –statid–statown–degree–granularity --no_invalidate都同上

 

5. GATHER_SCHEMA_STATS

此过程收集统计某用户下所有的对象

参数：

--ownname：需分析的schema, null时则为当前schema

--estimate_percent --block_sample--method_opt–degree–granularity–cascade –stattab –statid–options –objlist –statown --no_invalidate都同上

 

6. GATHER_SYSTEM_STATS

此过程收集系统统计，用来系统的cpu和IO指标

参数：

--gathering_mode:

-NOWORKLOAD,收集I/O的性能指标，数据库的大小决定收集时间。收集期间ORACLE将评估IO平均的读取寻道时间和传输速度。

-INTERVAL，指定统计的时间(分钟数)，统计完成后，统计信息将建立或更新在数据字典或指定的stattab中。在统计期间可以用GATHER_SYSTEM_STATS (gathering_mode=>'STOP')来中断统计。

SQL> exec dbms_stats.gather_system_stats('interval',15);

SQL> exec dbms_stats.gather_system_stats('stop');

SQL>select pname,pval1 from sys.aux_stats$; --查看统计结果

-START|STOP:开始和结束统计

--interval:指定统计的分钟数，当上面的参数是INTERVAL时才用此参数

--no_invalidate:设置为TRUE时，相关的游标不会失效;反之，游标失效。

–statid–statown同上

 

7. GATHER_TABLE_STATS

此过程统计table and column (and index)信息。

参数：

Ownname：用户名

Tabname：TABLE名

Partname：分区名Name of partition

--estimate_percent --block_sample--Method_opt–degree–granularity–cascade –stattab –statid –statown --no_invalidate--force都同上

 

第二部分：Setting or Getting Statistics

 

1. SET_COLUMN_STATS

设置列相关信息

参数：

--ownname：用户名

--tabname：TABLE名

--colname：列名

--partname：分区名

--stattab：指定存放用户分析的数据的表名

--statid:stattab的ID,只有当stattab没有指定时使用

--ext_stats:自定义的统计

--stattypown:统计类型的所属用户

--STATTYPNAME:统计类型的名称

--distinct:有多少个值(number of distinct values)

--density:列密度。如果此值是null且distinct不为空，那么此值将参照distinct

--nullcnt：number of NULLS

--srec:StatRecstructure filled in by a call to PREPARE_COLUMN_VALUES or GET_COLUMN_STATS

--avgclen:列的平均长度(in bytes)

--flags:ORACLE内部使用，应该为NULL

--statown:Schema containing stattab (if different than ownname)

--no_invalidate:设置为TRUE时，相关的游标不会失效;反之，游标失效。

--force:被设置的column被锁时也强行设置

 

2. SET_INDEX_STATS

设置索引相关信息

 

3. SET_PARAM

此过程是用来设置DBMS_STATS的值的，可以用GET_PARAM函数来得到参数的当前值。

参数：

--pname:可以是CASCADE,DEGREE,EASTIMAT_PERCENT,METHOD_OPT,NO_INVALIDATE,GRANULARITY,AUTOSTATS_TARGET

--pval:参数的值。如果是NULL，则设成默认值。

To run this procedure, you must have theSYSDBAor both theANALYZEANYDICTIONARYandANALYZEANYsystem privileges

举例：

DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE');

DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','5');

DBMS_STATS.SET_PARAM('DEGREE','NULL');

 

4. SET_SYSTEM_STATS

设置系统的统计

参数:

--pname:

--iotfrspeed:IO的传输速度/毫秒

--ioseektim:seek time + latency time + operating system overhead time, in milliseconds

--sreadtim:average time to read single block (random read), in milliseconds

--mreadtim:average time to read an mbrc block at once (sequential read), in milliseconds

--cpuspeed:average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options)

--cpuspeednw:average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option.

--mbrc:average multiblock read count for sequential read, in blocks

--maxthr:I/O的最大吞吐量，bytes/second

--slavethr:average slave I/O throughput, in bytes/second

--pvalue:参数的值

--stattab:指定存放用户分析的数据的表名

--statid:用户ID

--statown：stattab指定表的所属用户

--cachedblk:段在buffer cache中的平均块数

--cachehit：段的平均命中率

 

5.SET_TABLE_STATS

设置表的统计

参数：

--ownname,--tabname,--partname,--stattab,--statid同上

--numrows:表中有多少行

--numblks:表占用了多少块

--avgrlen：平均的行长度

--flags，--statown，--no_invalidate，--cachedblk,--cachehit,--force同上

 

6. PREPARE_COLUMN_VALUES

7. PREPARE_COLUMN_VALUES_NVARCHAR2

8. PREPARE_COLUMN_VALUES_ROWID

9. GET_COLUMN_STATS Procedures

取栏位的统计信息

参数：

--ownname,--tabname,--colname, --partname,--stattab,--statid, --ext_stats, --stattypown, --STATTYPNAME, --distinct, --density, --nullcnt,--srec,--avgclen,--statown

 

10. GET_INDEX_STATS

取索引的统计信息

参数

--ownname,--indname,--partname,--stattab,--statid,--ext_stats,--stattypown,--STATTYPNAME, --numrows, --numblks

--numdist:Number of distinct keys in the index (partition)

--avglblk:Average integral number of leaf blocks in which each distinct key appears for this index (partition)

--avgdblk:Average integral number of data blocks in the table pointed to by a distinct key for this index (partition)

--clstfct:Clustering factor for the index (partition)

--indlevel:Height of the index (partition)

--statown:stattab的用户名

--guessq：估计INDEX的质量

--cachedblk:段在buffer cache中的平均块数

--cachehit：段的平均命中率

 

11．GET_SYSTEM_STATS

此过程取系统的统计信息

语法：

DBMS_STATS.GET_SYSTEM_STATS (

statusOUTVARCHAR2,

dstartOUTDATE,

dstopOUTDATE,

pnameVARCHAR2,

pvalueOUTNUMBER,

stattabINVARCHAR2 DEFAULT NULL,

statidINVARCHAR2 DEFAULT NULL,

statownINVARCHAR2 DEFAULT NULL);

参数：

--Status:会输出以下值之一COMPLETED,AUTOGATHERING,MANUALGATHERING,BADSTATS

--dstart:收集分析开始的时间

--dstop:收集分析结束的时间

--pname:需要获取值的参数，可选的值有：iotfrspeed，ioseektim，sreadtim,mreadtim,cpuspeed,cpuspeednw,mbrc,maxthr,slavethr

--pvalue:值

--stattab:统计表所表名，如果此值为null,则会从数据字典中取。

--statid:stattab的id

--statown:stattab的own

 

12.GET_TABLE_STATUS

取table相关的统计信息

语法：

DBMS_STATS.GET_TABLE_STATS (

ownnameVARCHAR2,

tabnameVARCHAR2,

partnameVARCHAR2 DEFAULT NULL,

stattabVARCHAR2 DEFAULT NULL,

statidVARCHAR2 DEFAULT NULL,

numrowsOUT NUMBER,

numblksOUT NUMBER,

avgrlenOUT NUMBER,

statownVARCHAR2 DEFAULT NULL,

cachedblkOUT NUMBER,

cachehitOUT NUMBER);

 

13．CREATE_STAT_TABLE

此过程建立stattab的TABLE，以保存统计信息。

语法：

DBMS_STATS.CREATE_STAT_TABLE (

ownnameVARCHAR2,

stattabVARCHAR2,

tblspace VARCHAR2 DEFAULT NULL);

参数：

--ownname:用户名

--stattab:需建立的stattab表名。

--tblspace:stattab的tablespace.如果为null则会使用用户的默认表空间

 

14．DROP_STAT_TABLE

此过程删除用户定义的统计表

语法：

DBMS_STATS.DROP_STAT_TABLE (

ownname VARCHAR2,

stattab VARCHAR2);

 

第三部分Deleting Statistics

 

1．DELETE_COLUMN_STATS

语法：

DBMS_STATS.DELETE_COLUMN_STATS (

ownnameVARCHAR2,

tabnameVARCHAR2,

colnameVARCHAR2,

partnameVARCHAR2 DEFAULT NULL,

stattabVARCHAR2 DEFAULT NULL,

statidVARCHAR2 DEFAULT NULL,

cascade_parts BOOLEANDEFAULT TRUE,

statownVARCHAR2 DEFAULT NULL,

no_invalidate BOOLEANDEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

forceBOOLEAN DEFAULT FALSE);

参数：

--ownname:table所属用户名

--tabname:表名

--colname:column

--partname:分区名

--stattab:统计表名，如果为null,则从数据字典中取

--statid:stattab的id(当stattab为null时才需指定)

--cascade_parts:如果指定的表是分区表且partname是null，此值为TRUE时会删除所有指定字段分区的统计信息。

--statown:stattab的owner

--no_invalidate:如设为TRUE，相关的游标不失效。

--force:When value of this argument is TRUE, deletes column statistics even if locked

 

2. DELETE_DATABASE_STATS

此过程删除数据库中所有TABLE的统计信息

语法：

DBMS_STATS.DELETE_DATABASE_STATS (

stattabVARCHAR2 DEFAULT NULL,

statidVARCHAR2 DEFAULT NULL,

statownVARCHAR2 DEFAULT NULL,

no_invalidateBOOLEANDEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

forceBOOLEANDEFAULT FALSE);

 

3．DELETE_DICTIONARY_STATS

此过程删除'SYS', 'SYSTEM' and RDBMS component schemas的统计信息

 

分析表中的index

execute dbms_stats.gather_table_stats(ownname => 'tab',tabname => 'HWM',method_opt =>'for all indexed columns size auto');

分析指定列 buket 5

 execute dbms_stats.gather_table_stats(ownname => 'LINYU',tabname => 'HWM',method_opt =>'for columns OWNER size 5');

收集指定column的统计信息时,会自动把对应的index的信息收集起来.

删除指定表的统计信息

execute dbms_stats.delete_table_stats(ownname => 'LINYU',tabname => 'HWM');

删除指定列

execute dbms_stats.delete_column_stats(ownname => 'LINYU',tabname => 'HWM',colname => 'OWNER');

 

例子:

 

分析表中的index

execute dbms_stats.gather_table_stats(ownname => 'tab',tabname => 'HWM',method_opt =>'for all indexed columns size auto');

 

分析指定列 buket 5

 execute dbms_stats.gather_table_stats(ownname => 'LINYU',tabname => 'HWM',method_opt =>'for columns OWNER size 5');

收集指定column的统计信息时,会自动把对应的index的信息收集起来.

 

删除指定表的统计信息

execute dbms_stats.delete_table_stats(ownname => 'LINYU',tabname => 'HWM');

 

删除指定列

execute dbms_stats.delete_column_stats(ownname => 'LINYU',tabname => 'HWM',colname => 'OWNER');

统计信息采集具体方法

Oracle提供了丰富的统计信息采集方法。

（1）数据库级统计信息采集

建议对数据规模较小、数据变化比较大，而且时间和资源充裕的系统，在整个数据库级采集统计信息。例如：

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_  
     percent=>10, Degree=>8, Cascade=>TRUE, Granularity=>'ALL'); 

（2）schema级统计信息采集

其次，可考虑在schema级进行统计信息采集。例如：

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>' DW_HW_  
     CHCH', estimate_percent=>10, Degree=>8, Cascade=>TRUE,  
     Granularity=>'ALL'); 

（3）表级统计信息采集

建议对数据量较大、数据变化也比较大的表，在表级进行统计信息采集。例如：

SQL> exec DBMS_STATS. GATHER_TABLE_STATS (ownname=>' DW_HW_  
     CHCH', tabname=>'表名',estimate_percent=>10, Degree=>8,  
     Cascade=>TRUE, Granularity=>'ALL'); 

（4）分区级统计信息采集

建议对数据量较大、数据变化也比较大的分区表，在分表级进行统计信息采集，特别是只采集数据变化较大的分区。例如：

SQL> exec DBMS_STATS. GATHER_TABLE_STATS (ownname=>' DW_HW_  
     CHCH', tabname=>'表名', partname='分区名',estimate_  
     percent=>5, Degree=>8, Cascade=>TRUE, Granularity=>'  
     PARTITION', method_opt=>' FOR ALL INDEXED COLUMNS'); 

其中，Granularity用于定义采集粒度，具体值的含义如下。

① ALL：采集Global、partition、subpartition等粒度统计信息。

② AUTO：根据分区类型，由Oracle确定统计信息采集粒度。

③ PARTITION：只采集partition粒度统计信息。

④ SUBPARTITION：只采集subpartition粒度统计信息。

建议根据各个系统分区的特点，有针对性地设置Granularity参数。例如设置为ALL，统计信息精度最高，但资源消耗最大。而如果SQL语句只在分区或子分区内部进行操作，即没有跨分区操作，则可设置为PARTITION或SUBPARTITION。

（5）数据字典统计信息采集

由于10g只支持CBO，因此数据字典统计信息也需要进行采集和及时更新，从而保障对数据字典表访问的SQL语句性能最优。特别是数据库结构发生变化时，例如增加新表、表结构变化时，应进行数据字典统计信息采集。例如：

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_  
     percent=>100, Degree=>8, Cascade=>TRUE, Granularity=>'ALL'); 

（6）动态性能表统计信息采集

由于10g只支持CBO，因此动态性能表(Fixed Object)统计信息也需要进行采集和及时更新，从而保障当使用AWR、Statspack等工具时，对动态性能表访问的SQL语句性能最优。特别是业务负载（Workload）发生变化之后应进行此项工作。例如：

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 

（7）硬件统计信息采集

为更准确地评估SQL语句执行的成本和代价，10g增加了对硬件指标统计信息的采集功能。例如I/O寻址速度、I/O传输速度、CPU处理速度等。建议在某个典型业务开始之前，执行如下语句：

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('START'); 

在典型业务结束之后，执行如下语句：

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('STOP');