Oracle数据库性能调优：查询缺失索引的DMV语句咨询

Oracle提供了和SQL Server DMV功能类似的动态性能视图，可用于查询优化器识别出的缺失索引，以下是可直接使用的查询脚本：

• 执行以下查询需要拥有DBA权限或SELECT_CATALOG_ROLE角色
• 视图返回的是优化器基于历史SQL执行生成的建议，不要直接批量创建所有建议索引，需要结合业务读写比例、索引维护成本评估后再操作
• 建议优先选择预估收益高、对应SQL执行频次高的建议项验证生效

SELECT
  p.object_owner AS schema_name,
  p.object_name AS table_name,
  ROUND(s.perf_gain,2) AS estimated_perf_improve_pct,
  s.executions AS sql_exec_count,
  'CREATE INDEX IX_' || REPLACE(p.object_name, '$', '_') || '_' || 
  REGEXP_REPLACE(REPLACE(REPLACE(p.predicate, ')', ''), '(', ''), '[^a-zA-Z0-9_]', '_') ||
  ' ON ' || p.object_owner || '.' || p.object_name || '(' || 
  REPLACE(TRANSLATE(p.predicate, '()= ''', ''), 'AND', ',') || ')' AS create_index_statement
FROM
  v$sql_plan_mis p
  JOIN v$sql s ON p.sql_id = s.sql_id
WHERE
  p.operation = 'TABLE ACCESS'
  AND p.options = 'FULL'
  AND p.object_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY
  perf_gain DESC,
  s.executions DESC
FETCH FIRST 25 ROWS ONLY;

该脚本逻辑和你提供的SQL Server缺失索引查询逻辑对齐，默认返回前25条预估收益最高的索引建议，其中estimated_perf_improve_pct字段对应SQL Server脚本的Avg_Estimated_Impact，用于衡量索引创建后对应SQL的预估性能提升幅度。

如果你的Oracle实例开启了默认的自动SQL调优任务，也可以直接查询官方调优任务输出的高可信度索引建议：

SELECT
  af.attr1 AS table_name,
  af.attr2 AS index_columns,
  ROUND(af.benefit,2) AS estimated_benefit,
  af.recommendation AS advice
FROM
  dba_advisor_findings af
  JOIN dba_advisor_tasks at ON af.task_id = at.task_id
WHERE
  at.task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  AND af.type = 'INDEX'
ORDER BY
  af.benefit DESC;

内容的提问来源于stack exchange，提问作者mattsmith5