本文介绍了有没有办法强制Oracle在不使用提示的情况下更改查询计划?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个使用错误索引的查询。我可以看到,使用索引时,没有简单的方法可以让oracle获取数据。查询是由供应商软件构建的,无法更改,是否有办法强制oracle在没有提示的情况下更改解释计划。
我们非常感谢任何帮助。
I have a query using wrong indexes. I can see that with the usage of index there is no easy way for oracle fetch the data.The query is framed by a vendor software, and cannot be changed, Is there a way to force oracle to change the explain plan without hints.Any help would be much appreciated.
推荐答案
至少有11种方法可以在不修改查询的情况下控制计划。它们大致按照有用性列出如下:
There are at least 11 ways to control a plan without modifying the query. They are listed below roughly in the order of usefulness:
- SQL计划基准 - 将一个计划替换为另一个计划计划。
- SQL配置文件 - 为计划添加更正提示。例如,配置文件可能会说此连接返回的行数比预期多100倍,这会间接更改计划。
- 存储大纲 - 类似于SQL计划基准,但功能较少。此选项使用起来更简单但功能更少且不再受支持。
- DBMS_STATS.SET_X_STATS - 手动修改表,列和索引统计信息可以显着改变计划使对象人为地看起来或多或少。
- 会话控制 - 例如
alter session set optimizer_features_enable = '11 .2.0.3';
。并不总是有用的参数。但其中一个OPTIMIZER_ *参数可能有所帮助,或者您可以使用未记录的提示更改计划或禁用此类功能:alter session set_fix_control='XYZ:OFF';
- 系统控制 - 与上述类似,但适用于整个系统。
- DBMS_SPD - SQL计划指令类似于配置文件,因为它为优化程序提供了一些纠正信息。但是这在所有计划中都处于较低级别,并且是12c的新功能。
- DBMS_ADVANCED_REWRITE - 将查询更改为另一个查询。
- 虚拟专用数据库 - 通过添加谓词将查询更改为另一个查询。它不是为了提高性能,但您可能会滥用它来更改索引访问路径。
- SQL Translation Framework - 在查询之前将查询更改为另一个查询得到解析。这可以使完全错误的SQL运行。
- SQL Patch(dbms_sqldiag internal.i_create_patch) - 将查询更改为另一个查询。与DBMS_ADVANCED_REWRITE类似,但它没有文档记录,可能更强大。
- SQL Plan Baseline - Replace one plan with a another plan.
- SQL Profiles - Add "corrective" hints to the plans. For example, a profile might say "this join returns 100 times more rows than expected", which indirectly changes the plan.
- Stored Outline - Similar in idea to SQL Plan Baseline, but with less features. This option is simpler to use but less powerful and not supported anymore.
- DBMS_STATS.SET_X_STATS - Manually modifying table, column, and index stats can significantly change plans by making objects artificially look more or less expensive.
- Session Control - For example
alter session set optimizer_features_enable='11.2.0.3';
. There aren't always helpful parameters. But one of the OPTIMIZER_* parameters may help, or you may be able to change the plan with an undocumented hint or disabling a feature like this:alter session set "_fix_control"='XYZ:OFF';
- System Control - Similar to above but applies to the whole system.
- DBMS_SPD - A SQL Plan Directive is similar to a profile in that it provides some corrective information to the optimizer. But this works at a lower level, across all plans, and is new to 12c.
- DBMS_ADVANCED_REWRITE - Change a query into another query.
- Virtual Private Database - Change a query into another query, by adding predicates. It's not intended for performance, but you can probably abuse it to change index access paths.
- SQL Translation Framework - Change a query into another query, before it even gets parsed. This can enable totally "wrong" SQL to run.
- SQL Patch (dbms_sqldiag internal.i_create_patch) - Change a query into another query. Similar to DBMS_ADVANCED_REWRITE but it's undocumented and perhaps a bit more powerful.
这篇关于有没有办法强制Oracle在不使用提示的情况下更改查询计划?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!