怎么理解Oracle統(tǒng)計(jì)信息

dbms_stats.gather_table_stats(

owner VARCHAR2,

tablename VARCHAR2,

partname VARCHAR2,

estimate_percent NUMBER,

block_sample BOOLEAN,

method_opt VARCHAR2,

degree NUMBER,

granularity VARCHAR2,

cascade BOOLEAN,

stattab VARCHAR2,

statid VARCHAR2,

statown VARCHAR2,

no_invalidate BOOLEAN,

force BOOLEAN

)

參數(shù)說(shuō)明

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname     => 'SCOTT',

tabname          => 'DEPT',

estimate_percent => 30,

method_opt       => 'for all columns size repeat',

no_invalidate    => FALSE,

degree           => 8,

cascade          => TRUE);

END;

/

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname    => 'ROBINSON',

tabname          => 'P_TEST',

estimate_percent => 30,

method_opt       => 'for all columns size repeat',

no_invalidate    => FALSE,

degree           => 8,

granularity      => 'ALL',

cascade          => TRUE);

END;

/

FOR ALL INDEXED COLUMNS

指定只有含有索引的字段才能收集列的基本統(tǒng)計(jì)信息. 一般不推薦使用這個(gè)選項(xiàng)值, 因?yàn)樵跀?shù)據(jù)庫(kù)環(huán)境中的所有 sql 語(yǔ)句所使用的字段, 比如 select 后面的字段, where 后面字段, group by 中的字段, 并不只是會(huì)引用含有索引的字段.

AUTO  Oracle 自己決定根據(jù)列的統(tǒng)計(jì)信息(sys.col_usage$)以及列的數(shù)據(jù)傾斜程度(均勻分布程度)決定哪些列需要收集直方圖      

Integer  指定收集直方圖的桶數(shù), 桶數(shù)最小為 1 最大為 254 (針對(duì) 11g 及以前的版本, 12c 后沒有這個(gè)限制).注意如果桶數(shù)為 1, 即 size 1 意味著不建立直方圖, 如果已經(jīng)有直方圖的列則會(huì)刪除該列的直方圖.

REPEAT 只在已經(jīng)有直方圖的列上重新收集直方圖. repeat 會(huì)確保在全局級(jí)別上對(duì)已經(jīng)存在直方圖的列重新收集直方圖. 一般不推薦使用這個(gè)選項(xiàng), 因?yàn)樾碌闹狈綀D使用的桶數(shù)將不能超過(guò)舊的直方圖中的桶數(shù). 假設(shè)當(dāng)前直方圖中桶數(shù)為 5, 當(dāng)使用 size repeat 重新收集直方圖時(shí), 新的直方圖使用的桶數(shù)將不能超過(guò) 5 , 這鐘方式可能不會(huì)取得好的效果.

SKEWONLY 只在數(shù)據(jù)不均勻分布的列上收集直方圖. 讓ORACLE 自己判斷列是否收集直方圖 只要是列傾斜了  ORACLE就會(huì)收集直方圖 OLTP系統(tǒng)用這個(gè) 非?？拥?基本上所有列都要收集直方圖

exec dbms_stats.flush_database_monitoring_info;

--刷新sys.col_usage$ 和視圖:sys.DBA_TAB_MODIFICATIONS

select owner, table_name name, object_type, stale_stats, last_analyzed

from dba_tab_statistics

where table_name in (table_name)

and owner = 'OWNER_NAME'

and (stale_stats = 'YES' or last_analyzed is null);

1.創(chuàng)建一個(gè)實(shí)驗(yàn)表

CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;

select * from

(

select * from

(

select * from

(

select u.name owner, o.name table_name, null partition_name, null subpartition_name,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO') truncated,

m.drop_segments

from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u

where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#

union all

select u.name, o.name, o.subname, null,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO'),

m.drop_segments

from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u

where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19

union all

select u.name, o.name, o2.subname, o.subname,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO'),

m.drop_segments

from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,

sys.user$ u

where o.obj# = m.obj# and o.owner# = u.user# and

o.obj# = tsp.obj# and o2.obj# = tsp.pobj#

) where owner not like '%SYS%' and owner not like 'XDB'

union all

select * from

(

select u.name owner, o.name table_name, null partition_name, null subpartition_name,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO') truncated,

m.drop_segments

from sys.mon_mods$ m, sys.obj$ o, sys.tab$ t, sys.user$ u

where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#

union all

select u.name, o.name, o.subname, null,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO'),

m.drop_segments

from sys.mon_mods$ m, sys.obj$ o, sys.user$ u

where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19

union all

select u.name, o.name, o2.subname, o.subname,

m.inserts, m.updates, m.deletes, m.timestamp,

decode(bitand(m.flags,1),1,'YES','NO'),

m.drop_segments

from sys.mon_mods$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,

sys.user$ u

where o.obj# = m.obj# and o.owner# = u.user# and

o.obj# = tsp.obj# and o2.obj# = tsp.pobj#

) where owner not like '%SYS%' and owner not like '%XDB%'

) order by inserts desc

) where rownum<=50;

可以發(fā)現(xiàn)是刪除導(dǎo)致統(tǒng)計(jì)信息過(guò)期

我在10點(diǎn)收集了統(tǒng)計(jì)信息，

10點(diǎn) 過(guò)5分鐘執(zhí)行了 一個(gè) 大批量的 update操作

你在 10點(diǎn)10 執(zhí)行查詢，但是我發(fā)現(xiàn)查詢變慢了怎么辦？

也就是說(shuō)，某個(gè)表會(huì)突然發(fā)生大批的DML操作怎么辦？

SELECT owner,

table_name,

num_rows,

sample_size,

trunc(sample_size / num_rows * 100) estimate_percent

FROM DBA_TAB_STATISTICS

WHERE owner='SCOTT' AND table_name='TEST';

1創(chuàng)建一個(gè)新的實(shí)驗(yàn)表

create table test as select * from dba_objects;

1創(chuàng)建一個(gè)新的實(shí)驗(yàn)表

create table test as select * from dba_objects;

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname     => 'SCOTT',

tabname          => 'TEST',

estimate_percent => 30,

method_opt   => 'for owner columns size skewonly',

no_invalidate    => FALSE,

degree           => 1,

cascade          => TRUE);

END;

/

這里 for all 換成for owner