怎么收集統(tǒng)計(jì)信息不影響數(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ù)”吧！

大多數(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ì)劃。

但是在生產(chǎn)情況下，隨意的收集統(tǒng)計(jì)信息，則會(huì)給數(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、在業(yè)務(wù)高峰期收集統(tǒng)計(jì)信息，會(huì)需要額外的資源開銷，影響數(shù)據(jù)庫(kù)的性能。

在進(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ì)信息，需要進(jìn)一步驗(yàn)證“重新收集統(tǒng)計(jì)信息可以提升SQL性能”。

因此在針對(duì)“重新收集統(tǒng)計(jì)信息可以提升SQL性能”時(shí)，主要介紹一下如何去重新收集統(tǒng)計(jì)信息而不影響數(shù)據(jù)庫(kù)中

正在運(yùn)行的SQL。

在Oracle中，統(tǒng)計(jì)信息的收集，都是存儲(chǔ)在對(duì)應(yīng)的數(shù)據(jù)字典里，因此正常收集完統(tǒng)計(jì)信息，就會(huì)被對(duì)應(yīng)的SQL去用來(lái)生成

執(zhí)行計(jì)劃。但是，Oracle也提供了一種收集完統(tǒng)計(jì)信息卻不會(huì)被記錄在數(shù)據(jù)字典里，因此也不會(huì)被對(duì)應(yīng)的SQL使用，只有

在需要使用這些統(tǒng)計(jì)信息的時(shí)候，通過(guò)設(shè)置一些參數(shù)，才可以正常的使用這些統(tǒng)計(jì)信息。

Oracle中可以利用DBMS_STATS里的Pending Statistics去操作可以控制新收集的統(tǒng)計(jì)信息不會(huì)被存儲(chǔ)到數(shù)據(jù)字典。

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.

大致的意思：可以使用這種方法，針對(duì)統(tǒng)計(jì)信息正確與否，對(duì)執(zhí)行計(jì)劃影響的驗(yàn)證。

接下來(lái)用一個(gè)測(cè)試來(lái)驗(yà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ì)表的統(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ù)情況：

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的執(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

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

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

SQL> update demo set owner = 'DEMO' where object_id < 60000;
59659 rows updated
SQL> commit;
Commit complete
再一次進(jì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ì)信息未更新，所以優(yōu)化器還是認(rèn)為表中的數(shù)據(jù)情況是之前統(tǒng)計(jì)信息里的，所以延用了之前的執(zhí)行計(jì)劃。

需要重新收集統(tǒng)計(jì)信息，但是直接收集統(tǒng)計(jì)信息的話，會(huì)對(duì)表demo相關(guān)的SQL產(chǎn)生硬解析，并且此時(shí)的執(zhí)行計(jì)劃不一定

就高效，因此需要對(duì)表demo收集統(tǒng)計(jì)信息，但是不讓這些SQL使用統(tǒng)計(jì)信息。

使用Pending Statistics的方法去收集統(tǒng)計(jì)信息，以保證其他SQL不會(huì)去使用新的統(tǒng)計(jì)信息，同時(shí)也可以去判斷執(zhí)行計(jì)劃

是否合理。

使用Pending Statistics的步驟：

1、設(shè)置表demo上的 PUBLISH 參數(shù)為false，默認(rèn)為true

該參數(shù)表示，收集完的統(tǒng)計(jì)信息，是存儲(chǔ)到數(shù)據(jù)字典（正常情況），還是存儲(chǔ)到私有區(qū)域。

當(dāng)為true的時(shí)候，表示存儲(chǔ)統(tǒng)計(jì)信息到數(shù)據(jù)字典，可以被SQL使用

當(dāng)為false的時(shí)候，表示統(tǒng)計(jì)信息存儲(chǔ)到私有區(qū)域，不能被SQL使用，除非設(shè)置了參數(shù)來(lái)使用這些統(tǒng)計(jì)信息

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

2、收集表demo的統(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;
/

3、把會(huì)話的 optimizer_use_pending_statistics 的參數(shù)設(shè)置為 true，默認(rèn)為false

該參數(shù)表示：在私有區(qū)域的統(tǒng)計(jì)信息是否被會(huì)話或者系統(tǒng)使用。

當(dāng)為true的時(shí)候，表示會(huì)話/系統(tǒng) 可以使用這些統(tǒng)計(jì)信息

當(dāng)為false的時(shí)候，表示會(huì)話/系統(tǒng) 不可以使用這些統(tǒng)計(jì)信息

alter session set optimizer_use_pending_statistics = true;

4、查看SQL的執(zhí)行計(jì)劃

5、對(duì)存儲(chǔ)在私有區(qū)域的統(tǒng)計(jì)信息操作

方法1：把這些統(tǒng)計(jì)信息直接刪除了，然后重新正常的收集統(tǒng)計(jì)信息

方法2：把這些統(tǒng)計(jì)信息直接發(fā)布了，可以讓SQL使用（存在隱患）

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

6、設(shè)置表demo上的 PUBLISH 參數(shù)修改為true

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

7、收集統(tǒng)計(jì)信息

具體步驟：

1、設(shè)置表demo上的 PUBLISH 參數(shù)為false，默認(rèn)為true

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

2、收集表demo的統(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;
/

3、把會(huì)話的 optimizer_use_pending_statistics 的參數(shù)設(shè)置為 true，默認(rèn)為false

查看SQL的執(zhí)行計(jì)劃

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

此時(shí)還是沿用之前的執(zhí)行計(jì)劃，索引范圍掃描，說(shuō)明新收集的統(tǒng)計(jì)信息并沒(méi)有被使用到。

修改參數(shù)：

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

4、查看SQL的執(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')

此時(shí)執(zhí)行計(jì)劃已經(jīng)變?yōu)槿頀呙?，說(shuō)明采用了新收集的統(tǒng)計(jì)信息。

5、對(duì)存儲(chǔ)在私有區(qū)域的統(tǒng)計(jì)信息操作

這里選擇把這些統(tǒng)計(jì)信息刪除

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

再次查看SQL的執(zhí)行計(jì)劃：

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í)行計(jì)劃為最初的執(zhí)行計(jì)劃，索引范圍掃描，說(shuō)明使用的是舊的統(tǒng)計(jì)信息，沒(méi)有使用新收集的統(tǒng)計(jì)信息。

6、設(shè)置表demo上的 PUBLISH 參數(shù)修改為true

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

7、收集統(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ì)劃

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ì)劃可以看到，使用了全表掃描，使用了新收集的統(tǒng)計(jì)信息。

感謝各位的閱讀，以上就是“怎么收集統(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)注！