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

2025-03-14 技术教程

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

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

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

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

1、对重新收集统计信息的表，对应的一些SQL可能需要重新硬解析生成执行计划。

2、对于重新收集统计信息的表的部分SQL来说，可能会出现收集完统计信息了，但是执行计划更差的情况。

3、在业务高峰期收集统计信息，会需要额外的资源开销，影响数据库的性能。

在进行SQL优化时，通过查看执行计划，表的统计信息以及表的具体情况，去分析是否是由于统计信息不准导致执行计划有

问题，当确定了是统计信息的问题时，不能盲目的去收集统计信息，需要进一步验证“重新收集统计信息可以提升SQL性能”。

因此在针对“重新收集统计信息可以提升SQL性能”时，主要介绍一下如何去重新收集统计信息而不影响数据库中

正在运行的SQL。

在Oracle中，统计信息的收集，都是存储在对应的数据字典里，因此正常收集完统计信息，就会被对应的SQL去用来生成

执行计划。但是，Oracle也提供了一种收集完统计信息却不会被记录在数据字典里，因此也不会被对应的SQL使用，只有

在需要使用这些统计信息的时候，通过设置一些参数，才可以正常的使用这些统计信息。

Oracle中可以利用DBMS_STATS里的Pending Statistics去操作可以控制新收集的统计信息不会被存储到数据字典。

Thepackagegatherstatisticsandstoresitinthedictionarybydefault.User'scanstorethesestatisticsinthesystem'sprivateareainsteadofthedictionarybyturningthePUBLISHoptiontoFALSEusingtheSET*PREFSprocedures.ThedefaultvalueforPUBLISHisTRUE.ThestatisticsstoredinprivateareaarenotusedbyCostBasedOptimizerunlessparameteroptimizer_use_pending_statisticsissettoTRUE.ThedefaultvalueofthisparameterisFALSEandthisbooleanparametercanbesetatthesession/systemlevel.Userscanverifytheimpactofthenewstatisticsonqueryplansbyusingthependingstatisticsonasession.Pendingstatisticsprovideamechanismtoverifytheimpactofthenewstatisticsonqueryplansbeforemakingthemavailableforgeneraluse.Therearetwoscenariostoverifythequeryplans:Exportthependingstatistics(usetheEXPORT_PENDING_STATSProcedure)toatestsystem,thenrunthequeryworkloadandchecktheperformanceorplans.Setoptimizer_use_pending_statisticstoTRUEinasessiononthesystemwherependingstatisticshavebeengathered,runtheworkload,andchecktheperformanceorplans.Oncetheperformanceorqueryplanshavebeenverified,thependingstatisticscanbepublished(runthePUBLISH_PENDING_STATSProcedure)iftheperformanceisacceptableordelete(runtheDELETE_PENDING_STATSProcedure)ifnot.

大致的意思：可以使用这种方法，针对统计信息正确与否，对执行计划影响的验证。

接下来用一个测试来验证。

1、创建测试表

SQL>droptabledemopurge;Tabledropped.SQL>createtabledemoasselect*fromdba_objects;Tablecreated.

2、在owner列上创建索引

SQL>createindexidx_owner_demoondemo(owner);Indexcreated.

3、统计表的统计信息，并且收集owner列的直方图信息：

begindbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'DEMO',estimate_percent=>100,method_opt=>'forcolumnsownersizeskewonly',no_invalidate=>false,degree=>1,cascade=>true);end;/

4、查看SQL的执行计划

查看一下owner为demo和sys的数据情况：

SELECT(SELECTCOUNT(*)FROMDEMO)CNT,OWNER,COUNT(*)FROMDEMOWHEREOWNERIN('DEMO','SYS')GROUPBYOWNER;CNTOWNERCOUNT(*)--------------------------------------------------87069DEMO4487069SYS37815

表demo共有87096行记录，其中owner为demo的有44行记录，owner为sys的有37815行记录。

查看下列SQL的执行计划：

SQL>setautottraceSQL>select/*demo*/*fromdemowhereowner='DEMO';44rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3014608035----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||44|4312|3(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|DEMO|44|4312|3(0)|00:00:01||*2|INDEXRANGESCAN|IDX_OWNER_DEMO|44||1(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OWNER"='DEMO')

通过执行计划可以看到，使用了索引范围扫描，cost为3

SQL>select/*sys*/*fromdemowhereowner='SYS';37815rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4000794843--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||37815|3619K|347(1)|00:00:05||*1|TABLEACCESSFULL|DEMO|37815|3619K|347(1)|00:00:05|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OWNER"='SYS')

通过执行计划可以看到，使用了全表扫描，cost为347

更新表中的数据，但是不收集统计信息：

SQL>updatedemosetowner='DEMO'whereobject_id<60000;59659rowsupdatedSQL>commit;Commitcomplete再一次进行查询：SQL>select/*demo*/*fromdemowhereowner='DEMO';59703rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3014608035----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||44|4312|3(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|DEMO|44|4312|3(0)|00:00:01||*2|INDEXRANGESCAN|IDX_OWNER_DEMO|44||1(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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使用，除非设置了参数来使用这些统计信息

EXECDBMS_STATS.SET_TABLE_PREFS('DEMO','DEMO','PUBLISH','FALSE');

2、收集表demo的统计信息

begindbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'DEMO',estimate_percent=>100,method_opt=>'forcolumnsownersizeskewonly',no_invalidate=>false,degree=>1,cascade=>true);end;/

3、把会话的 optimizer_use_pending_statistics 的参数设置为 true，默认为false

该参数表示：在私有区域的统计信息是否被会话或者系统使用。

当为true的时候，表示会话/系统可以使用这些统计信息

当为false的时候，表示会话/系统不可以使用这些统计信息

altersessionsetoptimizer_use_pending_statistics=true;

4、查看SQL的执行计划

5、对存储在私有区域的统计信息操作

方法1：把这些统计信息直接删除了，然后重新正常的收集统计信息

方法2：把这些统计信息直接发布了，可以让SQL使用（存在隐患）

EXECDBMS_STATS.DELETE_PENDING_STATS('DEMO','DEMO');

6、设置表demo上的 PUBLISH 参数修改为true

EXECDBMS_STATS.SET_TABLE_PREFS('DEMO','DEMO','PUBLISH','TRUE');

7、收集统计信息

具体步骤：

1、设置表demo上的 PUBLISH 参数为false，默认为true

SQL>EXECDBMS_STATS.SET_TABLE_PREFS('DEMO','DEMO','PUBLISH','FALSE');PL/SQLproceduresuccessfullycompleted.

2、收集表demo的统计信息

begindbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'DEMO',estimate_percent=>100,method_opt=>'forcolumnsownersizeskewonly',no_invalidate=>false,degree=>1,cascade=>true);end;/

3、把会话的 optimizer_use_pending_statistics 的参数设置为 true，默认为false

查看SQL的执行计划

SQL>select/*demo*/*fromdemowhereowner='DEMO';59703rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3014608035----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||44|4312|3(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|DEMO|44|4312|3(0)|00:00:01||*2|INDEXRANGESCAN|IDX_OWNER_DEMO|44||1(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OWNER"='DEMO')

此时还是沿用之前的执行计划，索引范围扫描，说明新收集的统计信息并没有被使用到。

修改参数：

SQL>altersessionsetoptimizer_use_pending_statistics=true;Sessionaltered.

4、查看SQL的执行计划

SQL>select/*demo*/*fromdemowhereowner='DEMO';59703rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4000794843--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||59703|5713K|347(1)|00:00:05||*1|TABLEACCESSFULL|DEMO|59703|5713K|347(1)|00:00:05|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OWNER"='DEMO')

此时执行计划已经变为全表扫描，说明采用了新收集的统计信息。

5、对存储在私有区域的统计信息操作

这里选择把这些统计信息删除

SQL>EXECDBMS_STATS.DELETE_PENDING_STATS('DEMO','DEMO');PL/SQLproceduresuccessfullycompleted.

再次查看SQL的执行计划：

执行计划为最初的执行计划，索引范围扫描，说明使用的是旧的统计信息，没有使用新收集的统计信息。

6、设置表demo上的 PUBLISH 参数修改为true

SQL>EXECDBMS_STATS.SET_TABLE_PREFS('DEMO','DEMO','PUBLISH','TRUE');PL/SQLproceduresuccessfullycompleted.

7、收集统计信息：

begindbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'DEMO',estimate_percent=>100,method_opt=>'forcolumnsownersizeskewonly',no_invalidate=>false,degree=>1,cascade=>true);end;/

查看SQL的执行计划

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

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