Oracle Optimizer Statistics Advisor及其对表的影响收集统计信息

czq61nw1  于 2022-12-11  发布在  Oracle
关注(0)|答案(1)|浏览(131)

最近我阅读了更多关于“优化统计顾问”,我做了一些测试我的测试数据库。它给了下面的建议:

Rule Name:         UseConcurrent                                               
 Rule Description:  Use Concurrent preference for Statistics Collection         
 Finding:  The CONCURRENT preference is not used.                               

 Recommendation:  Set the CONCURRENT preference.                                
 Example:                                                                       
 dbms_stats.set_global_prefs('CONCURRENT', 'ALL');                              
 Rationale:  The system's condition satisfies the use of concurrent statistics  
             gathering. Using CONCURRENT increases the efficiency of statistics 
             gathering.

据我从甲骨文公司了解

Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. This is done using a combination of the job scheduler, advanced queuing and resource manager.

因此,此建议适用于所有数据库,而不是表。我的意思是,如果我收集表的统计信息,这样的远程建议不会有任何好处,对吗?还有,是否有一种方法可以在特定表上实现“优化统计顾问”?

bpsygsoo

bpsygsoo1#

Unlikely, because one of the overall aims for the optimizer team is that for 99% of customers, the default settings of the optimizer statistics gathering mechanisms will be sufficient to ensure good plans.
This is why (for example)

  • when you use CTAS, you'll get stats collected automatically on creation, so (in most cases) no need for additional steps before using table in queries.
  • a table that has queries that use predicates on skewed data will ultimately end up with histograms (via SYS.COL_USAGE$) without user intervention needed
  • tables without stats will get them automatically in the next run, and until then will be dynamically sampled
  • tables that change by 10% will get stats picked up again on next run

and so forth. The aim is that (except in niche cases) you'll not need to "worry" about the optimizer statistics process.

相关问题