有没有办法强制Oracle在不使用提示的情况下更改查询计划？ [英] Is there a way to force Oracle to change a query's plan without using hints?

问题描述

我有一个使用错误索引的查询。我可以看到，使用索引时，没有简单的方法可以让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:

1. SQL计划基准 - 将一个计划替换为另一个计划计划。


2. SQL配置文件 - 为计划添加更正提示。例如，配置文件可能会说此连接返回的行数比预期多100倍，这会间接更改计划。


3. 存储大纲 - 类似于SQL计划基准，但功能较少。此选项使用起来更简单但功能更少且不再受支持。


4. DBMS_STATS.SET_X_STATS - 手动修改表，列和索引统计信息可以显着改变计划使对象人为地看起来或多或少。


5. 会话控制 - 例如 alter session set optimizer_features_enable = '11 .2.0.3'; 。并不总是有用的参数。但其中一个OPTIMIZER_ *参数可能有所帮助，或者您可以使用未记录的提示更改计划或禁用此类功能： alter session set_fix_control='XYZ：OFF';


6. 系统控制 - 与上述类似，但适用于整个系统。


7. DBMS_SPD - SQL计划指令类似于配置文件，因为它为优化程序提供了一些纠正信息。但是这在所有计划中都处于较低级别，并且是12c的新功能。


8. DBMS_ADVANCED_REWRITE - 将查询更改为另一个查询。


9. 虚拟专用数据库 - 通过添加谓词将查询更改为另一个查询。它不是为了提高性能，但您可能会滥用它来更改索引访问路径。


10. SQL Translation Framework - 在查询之前将查询更改为另一个查询得到解析。这可以使完全错误的SQL运行。


11. SQL Patch（dbms_sqldiag internal.i_create_patch） - 将查询更改为另一个查询。与DBMS_ADVANCED_REWRITE类似，但它没有文档记录，可能更强大。

1. SQL Plan Baseline - Replace one plan with a another plan.
2. 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.
3. 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.
4. 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.
5. 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';
6. System Control - Similar to above but applies to the whole system.
7. 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.
8. DBMS_ADVANCED_REWRITE - Change a query into another query.
9. 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.
10. SQL Translation Framework - Change a query into another query, before it even gets parsed. This can enable totally "wrong" SQL to run.
11. 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在不使用提示的情况下更改查询计划？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！