微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊

orastar發表於2020-03-03
SQL

3、如何收集統計資訊

基於成本的最佳化器（CBO）使用統計資訊來確定特定查詢的執行計劃。隨著取樣量的變化，取樣可能產生不同的統計資料，改變SQL語句執行計劃。
在11g時，建議使用計劃的統計資訊收集指令碼收集統計資訊。在大多數情況下，預設指令碼提供足夠的取樣級別，並考慮以下建議：
  • 足夠大的取樣率
在11g的支援下，建議對ESTIMATE_PERCENT使用預設DBMS_STATS.AUTO_SAMPLE_SIZE。這將為表生成100％的估計樣本大小（如果可能的話可以在維護視窗中進行）。如果資料量較大，100%取樣無法完成，至少使用30％的估計值，由於11g使用雜湊演算法來計算統計量，因此在大多數情況下效能應該是可以接受的。
  • 所有物件收集統計資訊
確保所有物件（表和索引）都已收集統計資訊。實現這一點的簡單方法是使用CASCADE引數。
  • 傾斜資料列收集直方圖
確保任何具有偏斜資料分佈的列都收集了直方圖，並使用METHOD_OPT引數以足夠的解析度進行。通常，支援建議使用“AUTO”的預設列統計資訊設定，這意味著DBMS_STATS將決定哪些列新增直方圖，以確定它們可能有助於生成更好的計劃。“，只有在已知需要的情況下才新增直方圖”是一種保守和更加規劃穩定的方法，而不是在所有列上收集列統計資訊。
  • 收集分割槽物件的全域性統計資訊
如果分割槽正在使用，如果可能，由於時間限制，收集全域性統計資訊。全球統計資料非常重要，但由於需要的大小和時間長短，往往會避免收集。如果100％的樣品是不可能的，那麼支援將建議至少為1％。收集小樣本（例如0.001，0.0001，0.00001等）可以非常有效，但同樣地，大部分資料將不被檢查，這可能對最佳化器的計劃選擇是決定性的。請注意，ESTIMATE_PERCENT引數的可用範圍是非常靈活的[0.000001 - > 100]，可以使用非常小的適用於巨大分割槽表的樣本大小。測試將顯示每個系統最合適的設定。

4、統計資訊收集語句

非分割槽表：
begin
 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',
tabname => 'TEST',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 4,
cascade => TRUE);
end;
分割槽表：
begin DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',
tabname => 'TEST',
estimate_percent => 30, --關於採集率：1、小於5G，100%收集;2、大於5G，30%收集，根據資料量逐漸降低
method_opt => 'for all columns size repeat',--穩定系統建議使用repeat,有確定直方圖收集的，單獨執行
no_invalidate => FALSE,
degree => 8,                                             --關於並行度，建議系統總CPU/2,個人經驗，建議不超過16
granularity => 'ALL',
cascade => TRUE
);
end;
/

參考文件：

Oracle? Database PL/SQL Packages and Types Reference 11 g  Release 2 (11.2)

5、練習2：統計資訊收集

CREATE TABLE ht.a_amt_p1
(amt_id number primary key,
cons_no number,
amt_ym varchar2(20),
amt number)
PARTITION BY LIST (amt_ym)
(
PARTITION p01 VALUES ('201701'),
PARTITION p02 VALUES ('201702'),
PARTITION p03 VALUES ('201703'),
PARTITION p04 VALUES ('201704'),
PARTITION p05 VALUES ('201705'),
PARTITION p06 VALUES  ('201706'));
insert into ht.a_amt_p1 select * from ht.a_amt;
請分別對錶ht.a_amt、ht.a_amt_p1收集統計資訊?
SQL> set line 200
col owner for a10
col table_name for a20 
col LAST_ANALYZED for a30
select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL> 
OWNER       TABLE_NAME      NUM_ROWS     BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- ---------- ---------- --------- ----------- ------------------------------
HT       A_AMT         59968      244 NO          59968 2017-08-17 22:01
HT       C_CONS          9817       58 NO           2945 2017-08-17 13:17
HT       A_AMT_BAK         59967      247 NO          59967 2017-08-17 22:01
HT       TEST           77695     1261 NO          77695 2017-08-18 22:03
HT       A_AMT_P1         59968      276 YES          59968 2017-08-19 18:02
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT_P1',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,granularity => 'ALL',cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> set line 200
col owner for a10
col table_name for a20 
col LAST_ANALYZED for a30
select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL> 
OWNER       TABLE_NAME      NUM_ROWS     BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- ---------- ---------- --------- ----------- ------------------------------
HT       A_AMT         59968      244 NO          59968 2017-08-19 18:33
HT       C_CONS          9817       58 NO           2945 2017-08-17 13:17
HT       A_AMT_BAK         59967      247 NO          59967 2017-08-17 22:01
HT       TEST           77695     1261 NO          77695 2017-08-18 22:03
HT       A_AMT_P1         59968      276 YES          59968 2017-08-19 18:33
SQL> !date
Sat Aug 19 18:34:27 CST 2017
SQL> 

6、小結

1、統計資訊取樣率建議100%，如果資料量較大，至少建議30%。-- estimate_percent => 30, --關於採集率：1、小於5G，100%收集;2、大於5G，30%收集，根據資料量逐漸降低

2、所有物件收集統計資訊，使用 CASCADE引數。-- cascade => TRUE

3、穩定系統直方圖收集，建議 method_opt => 'for all columns size repeat'，否則根據需求收集。

4、 分割槽表， 收集全域性統計資訊， -- granularity => 'ALL'。

5、 關於並行度，建議系統總CPU/2,個人經驗，建議不超過32 -- degree => 8,  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678250/，如需轉載，請註明出處，否則將追究法律責任。

相關文章