链接：http://blog.itpub.net/28602568/viewspace-1429058/

注释：

今天叫我同事统计一个200G用户，刚刚说完话，他随后不到1分钟说统计完了..

他执行的语句： BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'user1' ,options => 'GATHER AUTO' ,cascade => TRUE ,no_invalidate=> false); END ;

和我往常统计语句不同的是多了一段"options => 'GATHER AUTO' "

那我们解析来下为啥(默认)不写时，200G的用户要统计1个小时，加了这个属性1s 就统计完成呢？

Oracle自带dbms_stats.gather_schema_stats存储信息如下：

procedure gather_schema_stats

(ownname varchar2 ,

estimate_percent number default DEFAULT_ESTIMATE_PERCENT,

block_sample boolean default FALSE,

method_opt varchar2 default DEFAULT_METHOD_OPT,

degree number default to_degree_type(get_param( 'DEGREE' )),

granularity varchar2 default DEFAULT_GRANULARITY,

cascade boolean default DEFAULT_CASCADE,

stattab varchar2 default null, statid varchar2 default null,

options varchar2 default 'GATHER', statown varchar2 default null ,

no_invalidate boolean default

to_no_invalidate_type(get_param( 'NO_INVALIDATE' )),

gather_temp boolean default FALSE,

gather_fixed boolean default FALSE,

stattype varchar2 default 'DATA',

force boolean default FALSE ,

obj_filter_list ObjectTab default null );

--

-- Input arguments:

-- ownname - schema to analyze (NULL means current schema)

-- estimate_percent - Percentage of rows to estimate (NULL means compute).

-- The valid range is [0.000001,100]. Use the constant

-- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the

-- appropriate sample size for good statistics. This is the default.

-- The default value can be changed using set_param procedure.

-- block_sample - whether or not to use random block sampling instead of

-- random row sampling. Random block sampling is more efficient, but

-- if the data is not randomly distributed on disk then the sample values

-- may be somewhat correlated. Only pertinent when doing an estimate

-- statistics.

-- method_opt - method options of the following format

--

-- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

--

-- size_clause := SIZE [integer | auto | skewonly | repeat],

-- where integer is between 1 and 254

--

-- default is FOR ALL COLUMNS SIZE AUTO.

-- The default value can be changed using set_param procedure.

-- This value will be passed to all of the individual tables.

-- degree - degree of parallelism (NULL means use table default value which

-- is specified by DEGREE clause in CREATE/ALTER TABLE statement)

-- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value

-- based on the initialization parameters.

-- default for degree is NULL.

-- The default value can be changed using set_param procedure.

-- granularity - the granularity of statistics to collect (only pertinent

-- if the table is partitioned)

-- 'AUTO' - the procedure determines what level of statistics to collect

-- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics

-- 'SUBPARTITION' - gather subpartition-level statistics

-- 'PARTITION' - gather partition-level statistics

-- 'GLOBAL' - gather global statistics

-- 'ALL' - gather all (subpartition, partition, and global) statistics

-- default for granularity is AUTO.

-- The default value can be changed using set_param procedure.

-- cascade - gather statistics on the indexes as well.

-- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine

-- whether index stats to be collected or not. This is the default.

-- The default value can be changed using set_param procedure.

-- Using this option is equivalent to running the gather_index_stats

-- procedure on each of the indexes in the schema in addition to

-- gathering table and column statistics.

-- stattab - The user stat table identifier describing where to save

-- the current statistics.

-- statid - The (optional) identifier to associate with these statistics

-- within stattab.

-- options - further specification of which objects to gather statistics for

-- 'GATHER' - gather statistics on all objects in the schema

-- 'GATHER AUTO' - gather all necessary statistics automatically. Oracle

-- implicitly determines which objects need new statistics, and

-- determines how to gather those statistics. When 'GATHER AUTO' is

-- specified, the only additional valid parameters are ownname, stattab,

-- statid, objlist and statown; all other parameter settings will be

-- ignored. Also, return a list of objects processed.

-- 'GATHER STALE' - gather statistics on stale objects as determined

-- by looking at the *_tab_modifications views. Also, return

-- a list of objects found to be stale.

-- 'GATHER EMPTY' - gather statistics on objects which currently

-- have no statistics. also, return a list of objects found

-- to have no statistics.

-- 'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'

-- 'LIST STALE' - return list of stale objects as determined

-- by looking at the *_tab_modifications views

-- 'LIST EMPTY' - return list of objects which currently

-- have no statistics

-- objlist - list of objects found to be stale or empty

-- statown - The schema containing stattab (if different then ownname)

-- no_invalidate - Do not invalide the dependent cursors if set to TRUE.

-- The procedure invalidates the dependent cursors immediately

-- if set to FALSE.

-- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to

-- invalidate dependend cursors. This is the default. The default

-- can be changed using set_param procedure.

-- When 'cascade' option is specified, not pertinent with certain types

-- of indexes described in the gather_index_stats section.

-- gather_temp - gather stats on global temporary tables also. The

-- temporary table must be created with "on commit preserve rows" clause,

-- and the statistics being collected are based on the data in the session

-- which this procedure is run but shared across all the sessions.

-- gather_fixed - Gather statistics on fixed tables also.

-- Statistics for fixed tables can be collected only by user SYS.

-- Also the ownname should be SYS or NULL.

-- Specified values for the following arguments will be ignored while

-- gathering statistics for fixed tables.

-- estimate_percent, block_sample, stattab, statid, statown

-- It will not invalidate the dependent cursors on fixed table

-- on which stats is collected.

-- This option is meant for internal use only.

-- force - gather statistics of objects even if they are locked.

-- obj_filter_list - a list of object filters. When provided, gather_schema_stats

-- will only gather statistics on the objects which satisfy at least one

-- object filter in the list as needed. Please refer to obj_filter_list

-- in gather_database_stats.

-- Exceptions:

-- ORA-20000: Schema does not exist or insufficient privileges

-- ORA-20001: Bad input value

-- ORA-20002: Bad user statistics table, may need to upgrade it

************
options参数

　　gather —— 重新剖析整个架构（Schema）
　　gather empty —— 只剖析目前还没有统计的表。
　　gather stale —— 只重新剖析修改量超过10%的表（这些修改包含拔出、更新和删除）。
　　gather auto —— 重新剖析以后没有统计的对象，以及统计数据过期（变脏）的对象.

注意，使用gather auto相似于组合使用gather stale和gather empty。

例如：create table t as select * from dba_objects;（10w数据的话，insert = 1w数据(等于10%) gather auto 统计后还是10w数据，而默认的gather统计后是 ≈ 11w）

但当10w数据，insert = 1w ,delete =1条 (求和＞10% ) 用 gather auto 统计和gather 都是一样的...

（测试：

SQL > SELECT T.TABLE_NAME,T.NUM_ROWS FROM USER_TABLES T WHERE TABLE_NAME=upper ('t_1' );

TABLE_NAME NUM_ROWS

------------------------------ ----------------------

T_1 100000

SQL > insert into t_1 select * from dba_objects where rownum <=10000 ;

10000 rows inserted

SQL >

SQL > BEGIN

2 DBMS_STATS.GATHER_SCHEMA_STATS(

3 OWNNAME => 'user1' ,

4 options => 'GATHER AUTO' ,

5 cascade => TRUE

6 );

7 END ;

8 /

PL /SQL procedure successfully completed

SQL > SELECT T.TABLE_NAME,T.NUM_ROWS FROM USER_TABLES T WHERE TABLE_NAME=upper ('t_1' );

TABLE_NAME NUM_ROWS

------------------------------ --------------------------

T_1 100000 --> 增量 = 10% ，用'GATHER AUTO'统计没变化

SQL > delete from t_1 where rownum <= 1 ; --> 任意操作一条就证明 > 10% ，用'GATHER AUTO'统计应该和默认一样~

1 row deleted

SQL >

SQL > BEGIN

2 DBMS_STATS.GATHER_SCHEMA_STATS(

3 OWNNAME => 'user1' ,

4 options => 'GATHER AUTO' ,

5 cascade => TRUE

6 );

7 END ;

8 /

PL /SQL procedure successfully completed

SQL > SELECT T.TABLE_NAME,T.NUM_ROWS FROM USER_TABLES T WHERE TABLE_NAME=upper ('t_1' );

TABLE_NAME NUM_ROWS

------------------------------ ------------------------------

T_1 109999

SQL >

）

所以，也就证明了，使用BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'user1' ,options => 'GATHER AUTO' ,cascade => TRUE ,no_invalidate=> false); END ;

统计用户1s就完成是因为USER1 用户下没有满足 options => 'GATHER AUTO' 属性 -- （修改量超过10%的表（这些修改包含拔出、更新和删除）或没统计的表）

而往常使用 BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'user1' ,options => 'GATHER' (default),cascade => TRUE ,no_invalidate=> false); END ;

统计要花1小时，是因为针对整个200G用户重新统计 ...

话说大家看到这，是否觉得自己受骗了捏~ 是的，骗了大家看完整个文章, 早知道的当复习找错啦哦~ 不知道options => 'GATHER AUTO'的也算简单了解了哈~~

同时大家也仔细考虑好，统计选择那个参数更适合自己的环境，满足自己的需求~~~

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

ITPub博客

Oracle 真的有参数可以 1s 统计完 200G用户下的所有对象么？

最新文章