有没有办法强制 Oracle 在不使用提示的情况下更改查询计划?

2021-12-30 00:00:00 indexing sql oracle11g tags 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:

  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 翻译框架 - 在一个查询被解析之前将其更改为另一个查询.这可以让完全错误"的 SQL 运行.
  11. SQL 补丁 (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.

相关文章