怎么收集统计信息不影响数据库

这篇文章主要讲解了“怎么收集统计信息不影响数据库”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么收集统计信息不影响数据库”吧！

大多数情况下，表的统计信息不准导致了优化器对于执行计划的错误计算，因此需要对表的统计信息进行更正，

以便让优化器重新选择准确的执行计划。

但是在生产情况下，随意的收集统计信息，则会给数据库带来隐患：

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

通过执行计划可以看到，使用了全表扫描，使用了新收集的统计信息。

感谢各位的阅读，以上就是“怎么收集统计信息不影响数据库”的内容了，经过本文的学习后，相信大家对怎么收集统计信息不影响数据库这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！