連結：http://blog.itpub.net/28602568/viewspace-1429058/

標題：Oracle 真的有引數可以 1s 統計完 200G使用者下的所有物件麼？

註釋：

今天叫我同事統計一個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 --&gt 增量 = 10% ，用'GATHER AUTO'統計沒變化

SQL > delete from t_1 where rownum <= 1 ; --&gt 任意操作一條就證明 > 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 分類目錄。將固定連線加入收藏夾。

Oracle 真的有引數可以 1s 統計完 200G使用者下的所有物件麼？

相關文章