创建索引是否应该立即更新 Oracle 的查询计划？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3310973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should creating an index instantly update Oracle's query plan?
提问by Marcus Leon
If you have an inefficient query, and you add an index to help out performance, should the query "instantly" start using the index?
如果您有一个低效的查询，并且您添加了一个索引来帮助提高性能，那么查询是否应该“立即”开始使用该索引？
Or do you need to clear out the Oracle "cache" (v$sql I believe) by runningalter system flush shared_pool;?
或者您是否需要通过运行来清除 Oracle“缓存”（我相信是 v$sql）alter system flush shared_pool;？
回答by Adam Musch
As the DBA loves to answer, "it depends."
正如 DBA 喜欢回答的那样，“视情况而定”。
It depends on if Oracle thinks the index will help performance. If Oracle thinks the index isn't the best choice for the query, Oracle's not using it anyway.
这取决于 Oracle 是否认为索引有助于提高性能。如果 Oracle 认为索引不是查询的最佳选择，那么 Oracle 无论如何都不会使用它。
It depends on whether you're using prepared statements. A prepared statement isn't reparsed during its lifetime, so if a running app uses a prepared statement you're trying to fix, you'lll need to restart the app.
这取决于您是否使用准备好的语句。准备好的语句在其生命周期内不会重新解析，因此如果正在运行的应用程序使用您要修复的准备好的语句，您将需要重新启动该应用程序。
Flushing the shared pool will force Oracle to reparse and reoptimize all statements (a hard parse), so if Oracle thinks the index will help performance, flushing the shared pool will do trick. However, it can also have far reaching consequences in a live production system -- causing a "parse storm", as every statement in use must be reparsed and reoptimized -- and should only be undertaken as a last resort.
刷新共享池将强制 Oracle 重新解析和重新优化所有语句（硬解析），因此如果 Oracle 认为索引有助于提高性能，则刷新共享池将起作用。然而，它也可能在实时生产系统中产生深远的影响——导致“解析风暴”，因为每个使用中的语句都必须重新解析和重新优化——并且只能作为最后的手段进行。
回答by Brian
You should regather statistics on the table. You can compute or estimate statistics. Example usage
您应该重新收集表上的统计信息。您可以计算或估计统计信息。示例用法
Compute
计算
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'ENROLLMENT'
,TabName => 'STUDENTS'
,Estimate_Percent => 0
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
Note the cascade argument is telling oracle to also gather stats on any indexes on the table as well.
请注意，级联参数告诉 oracle 还收集表上任何索引的统计信息。
Estimate
估计
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'ENROLLMENT'
,TabName => 'STUDENTS'
,Estimate_Percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
回答by Marcus Leon
Shared poolis not used to cache data.
共享池不用于缓存数据。
Oracle Server has two performance measurement, logical read and physical read. Physical read is a measurement of disk read performance. Logical read is a measurement of read data from memory.
Oracle Server 有两种性能度量，逻辑读和物理读。物理读取是衡量磁盘读取性能的指标。逻辑读取是从内存读取数据的度量。
In any read method (index, full table scan or something), rows in blocks must be retrieved into buffer cache. It's the action of physical read.
在任何读取方法（索引、全表扫描等）中，必须将块中的行检索到缓冲区缓存中。这是物理读取的操作。
Logical read is return result from cache if hit, if you use index to improve SQL performance, it's the improvement of logical read.
逻辑读是命中时从缓存返回的结果，如果使用索引来提高SQL性能，就是逻辑读的改进。
So in short, it's not necessary.
所以简而言之，没有必要。
