溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“怎么理解Oracle統計信息”，在日常操作中，相信很多人在怎么理解Oracle統計信息問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么理解Oracle統計信息”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
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 ) 參數說明 |
1.owner:要分析表的所有者 2.tablename：要分析的表的表名 3.partname：分區名 4.estimate_percent:采樣行的百分比，從0.000001-100，null為全部分析，不采樣。常量DBMS_STATS.AUTO_SAMPLE_SIZE是默認值，由Oracle決定最佳采樣率。 5.block_sample:是否用塊采樣代替行采樣。 6.method_opt：決定histograms信息是怎樣被統計的，method_opt的取值如下：
7.degree:設置統計信息收集的并行度，默認值為null。 8.cascade：收集索引的統計信息，默認為false 9.stattab：指定存儲統計信息的表。 10.statid：如果多個表的統計信息存儲在一個stattab中時，statid用作分區條件。 11.statown：存儲統計信息表的所有著。 如果不指定上述三個參數，則統計信息會被更新到數據字典。 12.force:即使表鎖住了也收集統計信息。 |
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; / |
注意：分區的統計信息合并 到 DBA_TABLES
estimate_percent
表示采樣率，采樣率設置太大，也沒必要，使用dbms_stats.auto_sample_size選項允許Oracle自動估算要采樣的一個segment的最佳百分比。
如果表非常大，采樣率過高會導致收集統計信息跑很長，增加了系統壓力。
采樣率設置過小，統計的信息就不能很完整的體現表中數據的分布，這樣CBO在進行執行計劃的選擇上，很可能選擇錯誤的執行計劃。
根據工作經驗：
表小于1GB 采樣率可以設置50%-100%
表大于1GB小于5GB可以設置30%
表大于5GB 這類表都應該進行分區，采樣率可以設置為30%
method_opt 有兩部分構成
表示收集的方法，參數分為兩部分
這一部分for all [indexed | hidden] columns"
控制著哪些列將會收集列的基本統計信息(目標列上的最小值, 最大值, 列上不同值的數量, 空值的數量等等). 系統默認值為 for all columns, 它將收集表上所有列(包括隱藏列)的基本的統計信息. 此外, 它的其他可選值如下所示：
FOR ALL INDEXED COLUMNS 指定只有含有索引的字段才能收集列的基本統計信息. 一般不推薦使用這個選項值, 因為在數據庫環境中的所有 sql 語句所使用的字段, 比如 select 后面的字段, where 后面字段, group by 中的字段, 并不只是會引用含有索引的字段. |
FOR ALL HIDDEN COLUMNS 指定表中所有不可見的字段才能收集列的基本統計信息, 也就是說不會去收集表上實際可見的列的統計信息. 同樣的一般也不推薦使用這個選項值. 這個選項值通常只用于這種情況, 在一個所有列的統計信息都是準確的表中新增了一個或幾個不可見或者說是虛擬的列, 只需要收集這個或者這幾個不可見列的統計信息, 而不再重復去其他列的統計信息, 那么就使用 for all hidden columns 這個選項. |
第二部分"Size [size_clause]"
控制收集直方圖的方式, size 后面可以有以下選項
AUTO Oracle 自己決定根據列的統計信息(sys.col_usage$)以及列的數據傾斜程度(均勻分布程度)決定哪些列需要收集直方圖 Integer 指定收集直方圖的桶數, 桶數最小為 1 最大為 254 (針對 11g 及以前的版本, 12c 后沒有這個限制).注意如果桶數為 1, 即 size 1 意味著不建立直方圖, 如果已經有直方圖的列則會刪除該列的直方圖. REPEAT 只在已經有直方圖的列上重新收集直方圖. repeat 會確保在全局級別上對已經存在直方圖的列重新收集直方圖. 一般不推薦使用這個選項, 因為新的直方圖使用的桶數將不能超過舊的直方圖中的桶數. 假設當前直方圖中桶數為 5, 當使用 size repeat 重新收集直方圖時, 新的直方圖使用的桶數將不能超過 5 , 這鐘方式可能不會取得好的效果. SKEWONLY 只在數據不均勻分布的列上收集直方圖. 讓ORACLE 自己判斷列是否收集直方圖 只要是列傾斜了 ORACLE就會收集直方圖 OLTP系統用這個 非常坑爹 基本上所有列都要收集直方圖 |
如果 method_opt 的默認參數 for all columns size auto 在你的數據環境不適用, 可能你遇到的情況屬于下面兩種情況：
1.除了指定的列, 在其它列上創建直方圖
2.只在指定的列上創建直方圖
一個穩定的系統收集統計信息的時候推薦使用method_opt=> 'for all columns size repeat'，repeat表示以前收集過直方圖，現在收集統計信息的時候就收集直方圖，如果以前沒收集過直方圖，現在收集統計信息的時候就不收集。
有時候收集統計信息的時候，用method_opt => 'for all columns size auto',很有可能把當前的sql搞定了，但是把其他的sql搞悲劇了，這是因為auto表示Oracle根據謂詞過濾信息(前文講解直方圖的時候提到過的where條件過濾)，自動判斷該列是否收集直方圖。一個穩定的系統，不應該讓Oracle去自動判斷，自動判斷很可能就會出事，比如某列不該收集直方圖，設置auto過后它自己去收集直方圖了，從而導致系統不穩定。
options
控制Oracle統計信息的刷新方式:
gather:重新分析整個架構
gather empty:只分析目前還沒有統計的表
gather stale:只重新分析修改量超過10%的表（包括插入、更新和刪除）
gather auto:重新分析當前沒有統計的對象，以及統計數據過期（變臟）的對象。使用gather auto類似于組合使用gather stale和gather empty
degree
表示收集統計信息的時候并行度，并行度根據你系統配置以及當前系統可用資源自行設置。 一般degree設置4--8。一個CPU 一般可以開2個線程
DEGREE 就等于 show parameter cpu
你開并行8去收集統計信息，很有可能開 17個進程 ，1個進程作為主進程來協調其他16個并行進程，8個進程 進行 讀取數據 另外8個進程 來進行 CPU運算 進行分析
cascade
表示收集表的統計信息時候同時收集索引的統計信息。其實收集索引的統計信息非常坑爹， 因為索引收集統計信息 是單塊讀。
no_invalidate
表示收集統計信息之后在共享池中引用了相關表的SQL游標是否失效。這個一定要設置為FALSE，默認是TRUE ，不然你可能在做SQL優化的時候，你發現明明更新了統計信息，但是執行計劃還是沒改變。
granularity
統計數據的收集，'ALL' - 收集所有（子分區，分區和全局）統計信息
① ALL：采集Global、partition、subpartition等粒度統計信息。
② AUTO：根據分區類型，由Oracle確定統計信息采集粒度。
③ PARTITION：只采集partition粒度統計信息。
④ SUBPARTITION：只采集subpartition粒度統計信息
partname
分區表的某個分區名
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.創建一個實驗表 CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS; |
2.收集統計信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 100, method_opt => 'for all columns size auto', no_invalidate => FALSE, degree => 1, cascade => TRUE); END; / |
3.刷新 exec dbms_stats.flush_database_monitoring_info; |
4.查看test表的統計的信息是否過期，顯示空行表示沒有過期 select owner, table_name name, object_type, stale_stats, last_analyzed from dba_tab_statistics where table_name in ('TEST') and owner = 'SCOTT' and (stale_stats = 'YES' or last_analyzed is null); -----結果空行----- |
5.刪除20%的數據，讓統計信息過期 select count(*) from test; delete from test where rownum<=72388*0.2; |
6.再次刷新 exec dbms_stats.flush_database_monitoring_info; |
7．查看統計信息是否過期，有結果返回表示統計信息過期 select owner, table_name name, object_type, stale_stats, last_analyzed from dba_tab_statistics where table_name in ('TEST') and owner = 'SCOTT' and (stale_stats = 'YES' or last_analyzed is null); OWNER NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- SCOTT TEST TABLE YES 2018-05-13 20:18:40 |
實驗二 查看是什么操作讓統計信息過期的腳本
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;
|
案列1 執行大批量的update，立即手動收集統計信息
我在10點收集了統計信息， 10點 過5分鐘執行了 一個 大批量的 update操作 你在 10點10 執行查詢，但是我發現查詢變慢了怎么辦？ 也就是說，某個表會突然發生大批的DML操作怎么辦？ |
解決方法：
收集統計信息的腳本直接放update后面，如果不立即收集
那肯定要動態采樣，動態采樣默認是2 ，沒用
這種至少要LEVEL 達到6，才可能有效果
如果是偶爾性質的，那么就要注意統計信息收集策略，當他發生變化了就立即收集
如果是經常性質的，那么就在SQL里面加上動態采樣的HINT
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';
|
實驗 size auto 的方法
1創建一個新的實驗表 create table test as select * from dba_objects; |
2收集統計信息，這里注意方法是 size auto , Oracle 自己決定根據列的統計信息(sys.col_usage$)以及列的數據傾斜程度(均勻分布程度)決定哪些列需要收集直方圖 BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 30, method_opt => 'for all columns size auto', no_invalidate => FALSE, degree => 1, cascade => TRUE); END; / |
3查看統計信息 select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'SCOTT'
從上面的結果發現 HISTOGRAM返回的是none 沒有直方圖信息，是因為我們沒有select查詢， |
4接著我們執行select查詢， SELECT COUNT(*) FROM TEST WHERE OWNER='SCOTT'; 再次收集統計信息方法同步驟2和再次查看是否收集直方圖同步驟3 發現有where條件就可以收集直方圖 |
實驗 size repeat 的方法
1創建一個新的實驗表 create table test as select * from dba_objects; |
2.收集統計信息,這里我們使用的size repeat只在已經有直方圖的列上重新收集直方圖. repeat 會確保在全局級別上對已經存在直方圖的列重新收集直方圖. |
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 30, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 1, cascade => TRUE); END; / 3查看統計信息 select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'SCOTT' and a.table_name = 'TEST'; |
|
實驗 對某個列（test表的owner列）收集直方圖
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 |
到此，關于“怎么理解Oracle統計信息”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
可以發現是刪除導致統計信息過期 
and a.table_name = 'TEST';
沒有直方圖的信息
