這篇文章主要講解了“收集統(tǒng)計數(shù)據(jù)庫信息的隱患有哪些”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“收集統(tǒng)計數(shù)據(jù)庫信息的隱患有哪些”吧！
創(chuàng)新互聯(lián)10多年成都企業(yè)網(wǎng)站建設服務;為您提供網(wǎng)站建設,網(wǎng)站制作,網(wǎng)頁設計及高端網(wǎng)站定制服務,成都企業(yè)網(wǎng)站建設及推廣,對成都發(fā)電機租賃等多個行業(yè)擁有豐富的網(wǎng)站運維經(jīng)驗的網(wǎng)站建設公司。
大多數(shù)情況下，表的統(tǒng)計信息不準導致了優(yōu)化器對于執(zhí)行計劃的錯誤計算，因此需要對表的統(tǒng)計信息進行更正，
以便讓優(yōu)化器重新選擇準確的執(zhí)行計劃。
在進行SQL優(yōu)化時，通過查看執(zhí)行計劃，表的統(tǒng)計信息以及表的具體情況，去分析是否是由于統(tǒng)計信息不準導致執(zhí)行計劃
有問題，當確定了是統(tǒng)計信息的問題時，不能盲目的去收集統(tǒng)計信息，否則會給數(shù)據(jù)庫帶來隱患。
收集統(tǒng)計信息，給數(shù)據(jù)庫帶來隱患：
1、對重新收集統(tǒng)計信息的表，對應的一些SQL可能需要重新硬解析生成執(zhí)行計劃。
2、對于重新收集統(tǒng)計信息的表的部分SQL來說，可能會出現(xiàn)收集完統(tǒng)計信息了，但是執(zhí)行計劃更差的情況。
3、收集統(tǒng)計信息，會需要額外的資源開銷，在業(yè)務高峰期會影響數(shù)據(jù)庫的性能。
（收集統(tǒng)計信息使得SQL產(chǎn)生硬解析）。
1、創(chuàng)建測試表
SQL> drop table demo purge; Table dropped. SQL> create table demo as select * from dba_objects; Table created.
2、在owner列上創(chuàng)建索引
SQL> create index idx_owner_demo on demo(owner); Index created.
3、收集表的統(tǒng)計信息，并且收集owner列的直方圖信息：
begin dbms_stats.gather_table_stats(ownname => 'DEMO', tabname => 'DEMO', estimate_percent => 100, method_opt => 'for columns owner size skewonly', no_invalidate => false, degree => 1, cascade => true); end; /
4、查看SQL的執(zhí)行計劃
查看一下owner為demo和sys的數(shù)據(jù)情況（主要是為了在不同的where條件，查看執(zhí)行計劃的情況）：
重慶分公司
重慶分公司
SELECT (SELECT COUNT(*) FROM DEMO) CNT
,OWNER
,COUNT(*)
FROM DEMO
WHERE OWNER IN ('DEMO', 'SYS')
GROUP BY OWNER;
CNT OWNER COUNT(*)
---------- ------------------------------ ----------
87069 DEMO 44
87069 SYS 37815表demo共有87096行記錄，其中owner為demo的有44行記錄，owner為sys的有37815行記錄。
5、為了測試效果，刷新shared pool（除測試外，勿用）
SQL> alter system flush shared_pool; System altered.
6、查看下列SQL的執(zhí)行計劃：
SQL> set autot trace
SQL> select /* demo */* from demo where owner = 'DEMO';
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3014608035
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 44 | 4312 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='DEMO')通過執(zhí)行計劃可以看到，使用了索引范圍掃描，cost為3。
此時統(tǒng)計信息是正確的，并且owner列也收集了直方圖信息，因此優(yōu)化器會根據(jù)統(tǒng)計信息去生成正確的執(zhí)行計劃，
由于owner='DEMO'的記錄只有44行，在返回這44條記錄時，采用索引范圍掃描的成本最低。
SQL> select /* sys */* from demo where owner = 'SYS';
37815 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37815 | 3619K| 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| DEMO | 37815 | 3619K| 347 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')通過執(zhí)行計劃可以看到，使用了全表掃描，cost為347。
由于owner='SYS'的記錄有37815行，在返回這37815條記錄時，采用全表掃描的成本最低。
7、查看SQL的信息：
SQL> select sql_id,sql_text,child_number,plan_hash_value,parse_calls,loads from v$sql where sql_text like '%/* demo */%'; SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS LOADS ------------- ------------------------------------------------------------ ------------ --------------- ----------- ---------- 45skkr08bw1m8 select /* demo */* from demo where owner = 'DEMO' 0 3014608035 1 1
此時該SQL當前的執(zhí)行計劃的plan_hash_value為3014608035，硬解析了一次（loads表示硬解析次數(shù)）。
8、更新表中的數(shù)據(jù)，但是不收集統(tǒng)計信息：
SQL> update demo set owner = 'DEMO' where object_id < 60000; 59659 rows updated SQL> commit; Commit complete
再一次進行查詢：
SQL> select /* demo */* from demo where owner = 'DEMO';
59703 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3014608035
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 44 | 4312 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='DEMO')通過執(zhí)行計劃可以發(fā)現(xiàn)，使用了索引范圍掃描，cost為3。
此時的執(zhí)行計劃是錯誤的，返回的數(shù)據(jù)行數(shù)為59659，不適合在使用索引范圍掃描，應該使用全表掃描。
但是由于統(tǒng)計信息未更新，所以優(yōu)化器還是認為表中的數(shù)據(jù)情況是之前統(tǒng)計信息里的，所以延用了之前的執(zhí)行計劃。
9、查看統(tǒng)計信息的情況
SELECT OWNER ,TABLE_NAME ,OBJECT_TYPE ,STALE_STATS ,TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh34:mi:ss') LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER = 'DEMO' AND TABLE_NAME = 'DEMO'; OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- ------------------- DEMO DEMO TABLE NO 2020-05-12 10:57:46
此時表的數(shù)據(jù)變化已經(jīng)超過表數(shù)據(jù)量的10%，應該在DBA_TAB_STATISTICS中記錄下來表demo，
并且把STALE_STATS列的值改為yes。
（STALE_STATS列的值代表了統(tǒng)計信息的情況，yes表示統(tǒng)計信息過期；no表示統(tǒng)計信息未過期）
由于表的數(shù)據(jù)的變化的情況未被及時的刷新（默認15分鐘刷新一次），因此DBA_TAB_STATISTICS視圖里的信息也沒有更新，
采用手動刷新數(shù)據(jù)庫監(jiān)控
SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed.
然后再次查看表的統(tǒng)計信息的情況：
OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- ------------------- DEMO DEMO TABLE YES 2020-05-12 10:57:46
列STALE_STATS的值已經(jīng)變?yōu)閥es，說明表demo的統(tǒng)計信息已經(jīng)過期了，需要重新收集統(tǒng)計信息。
10、重新收集統(tǒng)計信息：
begin dbms_stats.gather_table_stats(ownname => 'DEMO', tabname => 'DEMO', estimate_percent => 100, method_opt => 'for columns owner size skewonly', no_invalidate => false, degree => 1, cascade => true); end; /
查看SQL的執(zhí)行計劃
查看一下owner為demo和sys的數(shù)據(jù)情況：
SELECT (SELECT COUNT(*) FROM DEMO) CNT
,OWNER
,COUNT(*)
FROM DEMO
WHERE OWNER IN ('DEMO', 'SYS')
GROUP BY OWNER;
CNT OWNER COUNT(*)
---------- ------------------------------ ----------
87069 DEMO 59703
87069 SYS 5486表demo共有87096行記錄，其中owner為demo的有59703行記錄，owner為sys的有5486行記錄。
收集完統(tǒng)計信息，再次查看執(zhí)行計劃：
SQL> select /* demo */* from demo where owner = 'DEMO';
59703 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59703 | 5713K| 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| DEMO | 59703 | 5713K| 347 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='DEMO')通過執(zhí)行計劃可以看到，使用了全表掃描，cost為347。
此時的執(zhí)行計劃是正確的，返回了59703行，此時不應該在使用索引，應該使用全表掃描。
12、查看SQL的信息：
SQL> select sql_id,sql_text,child_number,plan_hash_value,parse_calls,loads from v$sql where sql_id = '45skkr08bw1m8'; SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS LOADS ------------- ------------------------------------------------------------ ------------ --------------- ----------- ---------- 45skkr08bw1m8 select /* demo */* from demo where owner = 'DEMO' 0 4000794843 1 2
查看SQL的信息發(fā)現(xiàn)，loads變成了2，說明增加了一次硬解析，也就是說，在正常情況下，如果收集了表的統(tǒng)計信息，那么對于某些SQL來說，會產(chǎn)生硬解析，對于生產(chǎn)庫來說，如果盲目的收集統(tǒng)計信息，則會產(chǎn)生大量的硬解析，給數(shù)據(jù)庫帶來壓力。
感謝各位的閱讀，以上就是“收集統(tǒng)計數(shù)據(jù)庫信息的隱患有哪些”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對收集統(tǒng)計數(shù)據(jù)庫信息的隱患有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關知識點的文章，歡迎關注！
