溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“收集統計數據庫信息的隱患有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“收集統計數據庫信息的隱患有哪些”吧！
大多數情況下，表的統計信息不準導致了優化器對于執行計劃的錯誤計算，因此需要對表的統計信息進行更正，
以便讓優化器重新選擇準確的執行計劃。
在進行SQL優化時，通過查看執行計劃，表的統計信息以及表的具體情況，去分析是否是由于統計信息不準導致執行計劃
有問題，當確定了是統計信息的問題時，不能盲目的去收集統計信息，否則會給數據庫帶來隱患。
收集統計信息，給數據庫帶來隱患：
1、對重新收集統計信息的表，對應的一些SQL可能需要重新硬解析生成執行計劃。
2、對于重新收集統計信息的表的部分SQL來說，可能會出現收集完統計信息了，但是執行計劃更差的情況。
3、收集統計信息，會需要額外的資源開銷，在業務高峰期會影響數據庫的性能。
（收集統計信息使得SQL產生硬解析）。
1、創建測試表
SQL> drop table demo purge; Table dropped. SQL> create table demo as select * from dba_objects; Table created.
2、在owner列上創建索引
SQL> create index idx_owner_demo on demo(owner); Index created.
3、收集表的統計信息，并且收集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的執行計劃
查看一下owner為demo和sys的數據情況（主要是為了在不同的where條件，查看執行計劃的情況）：
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的執行計劃：
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')通過執行計劃可以看到，使用了索引范圍掃描，cost為3。
此時統計信息是正確的，并且owner列也收集了直方圖信息，因此優化器會根據統計信息去生成正確的執行計劃，
由于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')通過執行計劃可以看到，使用了全表掃描，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當前的執行計劃的plan_hash_value為3014608035，硬解析了一次（loads表示硬解析次數）。
8、更新表中的數據，但是不收集統計信息：
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')通過執行計劃可以發現，使用了索引范圍掃描，cost為3。
此時的執行計劃是錯誤的，返回的數據行數為59659，不適合在使用索引范圍掃描，應該使用全表掃描。
但是由于統計信息未更新，所以優化器還是認為表中的數據情況是之前統計信息里的，所以延用了之前的執行計劃。
9、查看統計信息的情況
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
此時表的數據變化已經超過表數據量的10%，應該在DBA_TAB_STATISTICS中記錄下來表demo，
并且把STALE_STATS列的值改為yes。
（STALE_STATS列的值代表了統計信息的情況，yes表示統計信息過期；no表示統計信息未過期）
由于表的數據的變化的情況未被及時的刷新（默認15分鐘刷新一次），因此DBA_TAB_STATISTICS視圖里的信息也沒有更新，
采用手動刷新數據庫監控
SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed.
然后再次查看表的統計信息的情況：
OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- ------------------- DEMO DEMO TABLE YES 2020-05-12 10:57:46
列STALE_STATS的值已經變為yes，說明表demo的統計信息已經過期了，需要重新收集統計信息。
10、重新收集統計信息：
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的執行計劃
查看一下owner為demo和sys的數據情況：
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行記錄。
收集完統計信息，再次查看執行計劃：
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')通過執行計劃可以看到，使用了全表掃描，cost為347。
此時的執行計劃是正確的，返回了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的信息發現，loads變成了2，說明增加了一次硬解析，也就是說，在正常情況下，如果收集了表的統計信息，那么對于某些SQL來說，會產生硬解析，對于生產庫來說，如果盲目的收集統計信息，則會產生大量的硬解析，給數據庫帶來壓力。
感謝各位的閱讀，以上就是“收集統計數據庫信息的隱患有哪些”的內容了，經過本文的學習后，相信大家對收集統計數據庫信息的隱患有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
