溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

收集統(tǒng)計(jì)數(shù)據(jù)庫(kù)信息的隱患有哪些

發(fā)布時(shí)間：2021-11-04 11:42:42 來(lái)源：億速云 閱讀：106 作者：iii 欄目：關(guān)系型數(shù)據(jù)庫(kù)

這篇文章主要講解了“收集統(tǒng)計(jì)數(shù)據(jù)庫(kù)信息的隱患有哪些”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“收集統(tǒng)計(jì)數(shù)據(jù)庫(kù)信息的隱患有哪些”吧！

收集統(tǒng)計(jì)信息使得SQL產(chǎn)生硬解析

大多數(shù)情況下，表的統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致了優(yōu)化器對(duì)于執(zhí)行計(jì)劃的錯(cuò)誤計(jì)算，因此需要對(duì)表的統(tǒng)計(jì)信息進(jìn)行更正，

以便讓優(yōu)化器重新選擇準(zhǔn)確的執(zhí)行計(jì)劃。

在進(jìn)行SQL優(yōu)化時(shí)，通過(guò)查看執(zhí)行計(jì)劃，表的統(tǒng)計(jì)信息以及表的具體情況，去分析是否是由于統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃

有問(wèn)題，當(dāng)確定了是統(tǒng)計(jì)信息的問(wèn)題時(shí)，不能盲目的去收集統(tǒng)計(jì)信息，否則會(huì)給數(shù)據(jù)庫(kù)帶來(lái)隱患。

收集統(tǒng)計(jì)信息，給數(shù)據(jù)庫(kù)帶來(lái)隱患：

1、對(duì)重新收集統(tǒng)計(jì)信息的表，對(duì)應(yīng)的一些SQL可能需要重新硬解析生成執(zhí)行計(jì)劃。

2、對(duì)于重新收集統(tǒng)計(jì)信息的表的部分SQL來(lái)說(shuō)，可能會(huì)出現(xiàn)收集完統(tǒng)計(jì)信息了，但是執(zhí)行計(jì)劃更差的情況。

3、收集統(tǒng)計(jì)信息，會(huì)需要額外的資源開(kāi)銷(xiāo)，在業(yè)務(wù)高峰期會(huì)影響數(shù)據(jù)庫(kù)的性能。

用測(cè)試來(lái)驗(yàn)證

（收集統(tǒng)計(jì)信息使得SQL產(chǎn)生硬解析）。

1、創(chuàng)建測(cè)試表

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)計(jì)信息，并且收集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í)行計(jì)劃

查看一下owner為demo和sys的數(shù)據(jù)情況（主要是為了在不同的where條件，查看執(zhí)行計(jì)劃的情況）：

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、為了測(cè)試效果，刷新shared pool（除測(cè)試外，勿用）

SQL> alter system flush shared_pool;
System altered.

6、查看下列SQL的執(zhí)行計(jì)劃：

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')

通過(guò)執(zhí)行計(jì)劃可以看到，使用了索引范圍掃描，cost為3。

此時(shí)統(tǒng)計(jì)信息是正確的，并且owner列也收集了直方圖信息，因此優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息去生成正確的執(zhí)行計(jì)劃，

由于owner='DEMO'的記錄只有44行，在返回這44條記錄時(shí)，采用索引范圍掃描的成本最低。

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')

通過(guò)執(zhí)行計(jì)劃可以看到，使用了全表掃描，cost為347。

由于owner='SYS'的記錄有37815行，在返回這37815條記錄時(shí)，采用全表掃描的成本最低。

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

此時(shí)該SQL當(dāng)前的執(zhí)行計(jì)劃的plan_hash_value為3014608035，硬解析了一次（loads表示硬解析次數(shù)）。

8、更新表中的數(shù)據(jù)，但是不收集統(tǒng)計(jì)信息：

SQL> update demo set owner = 'DEMO' where object_id < 60000;
59659 rows updated
SQL> commit;
Commit complete

再一次進(jìn)行查詢(xún)：

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')

通過(guò)執(zhí)行計(jì)劃可以發(fā)現(xiàn)，使用了索引范圍掃描，cost為3。

此時(shí)的執(zhí)行計(jì)劃是錯(cuò)誤的，返回的數(shù)據(jù)行數(shù)為59659，不適合在使用索引范圍掃描，應(yīng)該使用全表掃描。

但是由于統(tǒng)計(jì)信息未更新，所以?xún)?yōu)化器還是認(rèn)為表中的數(shù)據(jù)情況是之前統(tǒng)計(jì)信息里的，所以延用了之前的執(zhí)行計(jì)劃。

9、查看統(tǒng)計(jì)信息的情況

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í)表的數(shù)據(jù)變化已經(jīng)超過(guò)表數(shù)據(jù)量的10%，應(yīng)該在DBA_TAB_STATISTICS中記錄下來(lái)表demo，

并且把STALE_STATS列的值改為yes。

（STALE_STATS列的值代表了統(tǒng)計(jì)信息的情況，yes表示統(tǒng)計(jì)信息過(guò)期；no表示統(tǒng)計(jì)信息未過(guò)期）

由于表的數(shù)據(jù)的變化的情況未被及時(shí)的刷新（默認(rèn)15分鐘刷新一次），因此DBA_TAB_STATISTICS視圖里的信息也沒(méi)有更新，

采用手動(dòng)刷新數(shù)據(jù)庫(kù)監(jiān)控

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

然后再次查看表的統(tǒng)計(jì)信息的情況：

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，說(shuō)明表demo的統(tǒng)計(jì)信息已經(jīng)過(guò)期了，需要重新收集統(tǒng)計(jì)信息。

10、重新收集統(tǒng)計(jì)信息：

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í)行計(jì)劃

查看一下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)計(jì)信息，再次查看執(zhí)行計(jì)劃：

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')

通過(guò)執(zhí)行計(jì)劃可以看到，使用了全表掃描，cost為347。

此時(shí)的執(zhí)行計(jì)劃是正確的，返回了59703行，此時(shí)不應(yīng)該在使用索引，應(yīng)該使用全表掃描。

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，說(shuō)明增加了一次硬解析，也就是說(shuō)，在正常情況下，如果收集了表的統(tǒng)計(jì)信息，那么對(duì)于某些SQL來(lái)說(shuō)，會(huì)產(chǎn)生硬解析，對(duì)于生產(chǎn)庫(kù)來(lái)說(shuō)，如果盲目的收集統(tǒng)計(jì)信息，則會(huì)產(chǎn)生大量的硬解析，給數(shù)據(jù)庫(kù)帶來(lái)壓力。

感謝各位的閱讀，以上就是“收集統(tǒng)計(jì)數(shù)據(jù)庫(kù)信息的隱患有哪些”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)收集統(tǒng)計(jì)數(shù)據(jù)庫(kù)信息的隱患有哪些這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手