【原】Oracle統(tǒng)計信息中的Pending Statistics

數(shù)據(jù)和云 2021-09-17

展開全文

前言

Oracle中的統(tǒng)計信息相信大家都不陌生，統(tǒng)計信息中有Pending Statistics這個概念。

統(tǒng)計信息準(zhǔn)確性對于CBO評估SQL的各種可能執(zhí)行路徑的Cost非常重要，當(dāng)統(tǒng)計信息不準(zhǔn)時，很可能CBO選擇了不佳的執(zhí)行計劃，此時需要收集統(tǒng)計信息。

或者當(dāng)進(jìn)行SQL優(yōu)化時，懷疑是統(tǒng)計信息不準(zhǔn)導(dǎo)致的問題時，需要收集統(tǒng)計信息。但生產(chǎn)環(huán)境下統(tǒng)計信息的收集也是有風(fēng)險的，有可能當(dāng)收集了統(tǒng)計信息后執(zhí)行計劃反而變的更差，此時就可以利用Pending Statistics。

默認(rèn)的，當(dāng)收集完統(tǒng)計信息后，統(tǒng)計信息會存儲到數(shù)據(jù)字典表中。

可以使用SET_TABLE_PREFS過程對表將PUBLISH選項設(shè)置為false，新收集的統(tǒng)計信息就會存儲到系統(tǒng)的一塊私有區(qū)域，這樣的統(tǒng)計信息稱為Pending Statistics，當(dāng)參數(shù)optimizer_use_pending_statistics為true時CBO才會使用私有區(qū)域中的統(tǒng)計信息，默認(rèn)為false即不使用，此參數(shù)可以在會話級或系統(tǒng)級設(shè)置。

因此，可以在會話級別使用Pending Statistics來驗證新收集的統(tǒng)計信息對SQL執(zhí)行計劃的影響，還不會使數(shù)據(jù)庫系統(tǒng)受到影響。

實(shí)驗步驟如下：

1.復(fù)制dba_objects創(chuàng)建t1表，同時創(chuàng)建索引并收集統(tǒng)計信息。

SQL> conn mdd/mdd

已連接。

SQL> create table t1 as select * from dba_objects;

表已創(chuàng)建。

SQL> create index ind_object_id on t1(object_id);

索引已創(chuàng)建。

SQL> exec dbms_stats.gather_table_stats('mdd','t1',cascade=>true);

PL/SQL 過程已成功完成。

2.查看統(tǒng)計信息。

SQL> select table_name object_name,last_analyzed from dba_tab_statistics where owner='MDD' and table_name='T1' union all select index_name object_name,last_analyzed from dba_ind_statistics where owner='MDD' and table_name='T1'; OBJECT_NAME LAST_ANALYZED------------------------------ -------------------T1 2021-07-12 11:14:37IND_OBJECT_ID 2021-07-12 11:14:37SQL> select table_name object_name,last_analyzed from dba_tab_pending_stats where owner='MDD' and table_name='T1' union all select index_name object_name,last_analyzed from dba_ind_pending_stats where owner='MDD' and table_name='T1';

未選定行。

3.查看SQL的執(zhí)行計劃，執(zhí)行計劃沒有問題。

SQL> select * from t1 where object_id=5;

執(zhí)行計劃：

Plan hash value: 1662447412---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 98 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=5)

統(tǒng)計信息：

0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1615 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

4.模擬表中數(shù)據(jù)變化，統(tǒng)計信息變得不準(zhǔn)、陳舊。

SQL> update t1 set object_id=5 where rownum<=86200;

已更新86200行。

SQL> commit;

提交完成。

5.再次查看SQL的執(zhí)行計劃，consistent gets為12915。

SQL> select * from t1 where object_id=5;

已選擇86200行。

執(zhí)行計劃：

統(tǒng)計信息：

0 recursive calls 0 db block gets12915 consistent gets 0 physical reads 0 redo size 9747648 bytes sent via SQL*Net to client 63726 bytes received via SQL*Net from client 5748 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 86200 rows processed

6.在再次收集統(tǒng)計信息之前，先使用Pengding Statistics做驗證。使用set_table_prefs過程將t1表的publish選項設(shè)置為false，并查看驗證：

SQL> exec dbms_stats.set_table_prefs('mdd','t1','publish','false');

PL/SQL 過程已成功完成。

SQL> set autot offSQL> select dbms_stats.get_prefs('publish','mdd','t1') from dual;DBMS_STATS.GET_PREFS('PUBLISH','MDD','T1')-----------------------------------------------FALSESQL> select * from dba_tab_stat_prefs where table_name='T1';OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE--------------- --------------- -------------------- --------------------MDD T1 PUBLISH FALSE

7.收集統(tǒng)計信息，發(fā)現(xiàn)原有的統(tǒng)計信息沒有受影響，此次收集的統(tǒng)計信息為Pending Statistics。

SQL> exec dbms_stats.gather_table_stats('mdd','t1',cascade=>true);

PL/SQL 過程已成功完成。

8.會話級別設(shè)置參數(shù)為true，發(fā)現(xiàn)SQL使用了全表掃描的方式，consistent gets由12915下降到6899，收集統(tǒng)計信息是有效的。

SQL> alter session set optimizer_use_pending_statistics=true;

會話已更改。

SQL> select * from t1 where object_id=5;

已選擇86200行。

執(zhí)行計劃：

Plan hash value: 3617692013--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 86172 | 8078K| 344 (1)| 00:00:05 ||* 1 | TABLE ACCESS FULL| T1 | 86172 | 8078K| 344 (1)| 00:00:05 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"=5)

統(tǒng)計信息：

0 recursive calls 0 db block gets 6899 consistent gets 0 physical reads 0 redo size 4040027 bytes sent via SQL*Net to client 63726 bytes received via SQL*Net from client 5748 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 86200 rows processed

9.可以使用PUBLISH_PENDING_STATS過程，Pending Statistics轉(zhuǎn)化為正常的統(tǒng)計信息。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('mdd','t1',no_invalidate=>false);

PL/SQL 過程已成功完成。

SQL> select table_name object_name,last_analyzed from dba_tab_statistics where owner='MDD' and table_name='T1' union all

     select index_name object_name,last_analyzed from dba_ind_statistics where owner='MDD' and table_name='T1';  OBJECT_NAME           LAST_ANALYZED------------------------------ -------------------T1             2021-07-12 13:43:04IND_OBJECT_ID           2021-07-12 13:43:04SQL> select table_name object_name,last_analyzed from dba_tab_pending_stats where owner='MDD' and table_name='T1' union all     select index_name object_name,last_analyzed from dba_ind_pending_stats where owner='MDD' and table_name='T1';

未選定行。

10.若發(fā)現(xiàn)收集了Pending Statistics后效果不好，可以使用DELETE_PENDING_STATS過程刪除Pending Statistics。

SQL> exec DBMS_STATS.DELETE_PENDING_STATS ('mdd','t1');

PL/SQL 過程已成功完成。

要點(diǎn)總結(jié)

1.使用set_table_prefs過程將表的publish選項設(shè)置為false，收集統(tǒng)計信息時表和索引的統(tǒng)計信息都會被收集為pending statistics，可以查看視圖dba_tab_pending_stats和dba_ind_pending_stats。

2.可以使用dbms_stats.get_prefs或查詢dba_tab_stat_prefs來查看相關(guān)選項設(shè)置，當(dāng)要查看多個選項或多張表時，查詢視圖dba_tab_stat_prefs更方便。

3.使用PUBLISH_PENDING_STATS過程，將Pending Statistics轉(zhuǎn)化為正常的統(tǒng)計信息，其LAST_ANALYZED顯示的是Pending Statistics收集的時間，而不是執(zhí)行PUBLISH_PENDING_STATS過程時的時間。