怎么收集統計信息不影響數據庫

這篇文章主要講解了“怎么收集統計信息不影響數據庫”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“怎么收集統計信息不影響數據庫”吧！

大多數情況下，表的統計信息不準導致了優化器對于執行計劃的錯誤計算，因此需要對表的統計信息進行更正，

以便讓優化器重新選擇準確的執行計劃。

但是在生產情況下，隨意的收集統計信息，則會給數據庫帶來隱患：

1、對重新收集統計信息的表，對應的一些SQL可能需要重新硬解析生成執行計劃。

2、對于重新收集統計信息的表的部分SQL來說，可能會出現收集完統計信息了，但是執行計劃更差的情況。

3、在業務高峰期收集統計信息，會需要額外的資源開銷，影響數據庫的性能。

在進行SQL優化時，通過查看執行計劃，表的統計信息以及表的具體情況，去分析是否是由于統計信息不準導致執行計劃有

問題，當確定了是統計信息的問題時，不能盲目的去收集統計信息，需要進一步驗證“重新收集統計信息可以提升SQL性能”。

因此在針對“重新收集統計信息可以提升SQL性能”時，主要介紹一下如何去重新收集統計信息而不影響數據庫中

正在運行的SQL。

在Oracle中，統計信息的收集，都是存儲在對應的數據字典里，因此正常收集完統計信息，就會被對應的SQL去用來生成

執行計劃。但是，Oracle也提供了一種收集完統計信息卻不會被記錄在數據字典里，因此也不會被對應的SQL使用，只有

在需要使用這些統計信息的時候，通過設置一些參數，才可以正常的使用這些統計信息。

Oracle中可以利用DBMS_STATS里的Pending Statistics去操作可以控制新收集的統計信息不會被存儲到數據字典。

The package gather statistics and stores it in the dictionary by default. User's can store these statistics in the 
system's private area instead of the dictionary by turning the PUBLISH option to FALSE using the SET*PREFS 
procedures. The default value for PUBLISH is TRUE.The statistics stored in private area are not used by Cost Based
 Optimizer unless parameter optimizer_use_pending_statistics is set to TRUE. The default value of this parameter 
is FALSE and this boolean parameter can be set at the session/system level. Users can verify the impact of the new 
statistics on query plans by using the pending statistics on a session.
Pending statistics provide a mechanism to verify the impact of the new statistics on query plans before making
 them available for general use. There are two scenarios to verify the query plans:
Export the pending statistics (use the EXPORT_PENDING_STATS Procedure) to a test system, 
then run the query workload and check the performance or plans.
Set optimizer_use_pending_statistics to TRUE in a session on the system where pending statistics have been 
gathered, run the workload, and check the performance or plans.
Once the performance or query plans have been verified, the pending statistics can be published 
(run the PUBLISH_PENDING_STATS Procedure) if the performance is acceptable or delete
 (run the DELETE_PENDING_STATS Procedure) if not.

大致的意思：可以使用這種方法，針對統計信息正確與否，對執行計劃影響的驗證。

接下來用一個測試來驗證。

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的數據情況：

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行記錄。

查看下列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

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

更新表中的數據，但是不收集統計信息：

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，不適合在使用索引范圍掃描，應該使用全表掃描。

但是由于統計信息未更新，所以優化器還是認為表中的數據情況是之前統計信息里的，所以延用了之前的執行計劃。

需要重新收集統計信息，但是直接收集統計信息的話，會對表demo相關的SQL產生硬解析，并且此時的執行計劃不一定

就高效，因此需要對表demo收集統計信息，但是不讓這些SQL使用統計信息。

使用Pending Statistics的方法去收集統計信息，以保證其他SQL不會去使用新的統計信息，同時也可以去判斷執行計劃

是否合理。

使用Pending Statistics的步驟：

1、設置表demo上的 PUBLISH 參數為false，默認為true

該參數表示，收集完的統計信息，是存儲到數據字典（正常情況），還是存儲到私有區域。

當為true的時候，表示存儲統計信息到數據字典，可以被SQL使用

當為false的時候，表示統計信息存儲到私有區域，不能被SQL使用，除非設置了參數來使用這些統計信息

EXEC DBMS_STATS.SET_TABLE_PREFS('DEMO', 'DEMO', 'PUBLISH','FALSE');

2、收集表demo的統計信息

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;
/

3、把會話的 optimizer_use_pending_statistics 的參數設置為 true，默認為false

該參數表示：在私有區域的統計信息是否被會話或者系統使用。

當為true的時候，表示會話/系統 可以使用這些統計信息

當為false的時候，表示會話/系統 不可以使用這些統計信息

alter session set optimizer_use_pending_statistics = true;

4、查看SQL的執行計劃

5、對存儲在私有區域的統計信息操作

方法1：把這些統計信息直接刪除了，然后重新正常的收集統計信息

方法2：把這些統計信息直接發布了，可以讓SQL使用（存在隱患）

EXEC DBMS_STATS.DELETE_PENDING_STATS('DEMO', 'DEMO');

6、設置表demo上的 PUBLISH 參數修改為true

EXEC DBMS_STATS.SET_TABLE_PREFS('DEMO', 'DEMO', 'PUBLISH','TRUE');

7、收集統計信息

具體步驟：

1、設置表demo上的 PUBLISH 參數為false，默認為true

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('DEMO', 'DEMO', 'PUBLISH','FALSE');
PL/SQL procedure successfully completed.

2、收集表demo的統計信息

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;
/

3、把會話的 optimizer_use_pending_statistics 的參數設置為 true，默認為false

查看SQL的執行計劃

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

此時還是沿用之前的執行計劃，索引范圍掃描，說明新收集的統計信息并沒有被使用到。

修改參數：

SQL> alter session set optimizer_use_pending_statistics = true;
Session altered.

4、查看SQL的執行計劃

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

此時執行計劃已經變為全表掃描，說明采用了新收集的統計信息。

5、對存儲在私有區域的統計信息操作

這里選擇把這些統計信息刪除

SQL> EXEC DBMS_STATS.DELETE_PENDING_STATS('DEMO', 'DEMO');
PL/SQL procedure successfully completed.

再次查看SQL的執行計劃：

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

執行計劃為最初的執行計劃，索引范圍掃描，說明使用的是舊的統計信息，沒有使用新收集的統計信息。

6、設置表demo上的 PUBLISH 參數修改為true

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('DEMO', 'DEMO', 'PUBLISH','TRUE');
PL/SQL procedure successfully completed.

7、收集統計信息：

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的執行計劃

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

通過執行計劃可以看到，使用了全表掃描，使用了新收集的統計信息。

感謝各位的閱讀，以上就是“怎么收集統計信息不影響數據庫”的內容了，經過本文的學習后，相信大家對怎么收集統計信息不影響數據庫這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！