Oracle近30天Top10常用/少用索引统计SQL正确性咨询

需求与现有SQL #

需要生成月度报告，展示指定用户的Top10最常用索引和Top10最少用索引，已编写如下SQL（用于获取Top10最少用索引，将ORDER BY num_executions asc改为desc即可获取Top10最常用索引）：

SELECT
    i.owner,
    i.index_name,
    c.constraint_type,
    COUNT(*) AS num_executions
FROM
    dba_hist_sqlstat s
JOIN
    dba_hist_sql_plan p ON s.sql_id = p.sql_id AND s.plan_hash_value = p.plan_hash_value
JOIN
    dba_indexes i ON p.object_owner = i.owner AND p.object_name = i.index_name
JOIN 
    dba_hist_snapshot sp on sp.snap_id = s.snap_id
LEFT JOIN
    dba_constraints c ON i.table_owner = c.owner AND i.table_name = c.table_name AND C.index_name = i.index_name 
WHERE
    s.elapsed_time_total > 0 
    and i.owner IN ('your_value_here')
    AND sp.BEGIN_INTERVAL_TIME >= SYSDATE - 30
GROUP BY
    i.owner, i.index_name,c.constraint_type
ORDER BY
    num_executions asc
FETCH FIRST 10 ROWS ONLY;

核心疑问 #

通过BEGIN_INTERVAL_TIME >= SYSDATE - 30筛选近30天数据的方式是否正确？

解答 #

1. 原有筛选条件的问题 #

仅用sp.BEGIN_INTERVAL_TIME >= SYSDATE -30存在统计不完整的风险：

• dba_hist_snapshot的每个快照对应一个统计周期（BEGIN_INTERVAL_TIME到END_INTERVAL_TIME），如果某个快照的开始时间在30天前，但结束时间在近30天内，这个快照里的SQL执行数据会被遗漏，导致统计结果缺失部分有效数据。

2. 优化后的筛选条件 #

建议修改为覆盖所有与近30天有交集的快照周期：

AND sp.END_INTERVAL_TIME >= SYSDATE - 30
AND sp.BEGIN_INTERVAL_TIME <= SYSDATE

这个条件确保所有统计周期结束时间在近30天内的快照都被纳入，同时排除逻辑上不存在的未来快照，保证统计范围的完整性。

3. 额外的SQL逻辑修正 #

现有SQL中用COUNT(*)作为num_executions是错误的：

• COUNT(*)统计的是该索引关联的快照记录条数，而非实际的索引执行次数。正确的做法是求和dba_hist_sqlstat中的executions_total字段，这才是每个快照周期内SQL的总执行次数。修改后的统计字段应为：

  SUM(s.executions_total) AS num_executions
  

  否则统计结果会严重偏离真实的索引使用频次。

4. 验证方法 #

可以先查询筛选后的快照时间范围，确认是否覆盖近30天：

SELECT MIN(BEGIN_INTERVAL_TIME), MAX(END_INTERVAL_TIME)
FROM dba_hist_snapshot
WHERE END_INTERVAL_TIME >= SYSDATE -30 AND BEGIN_INTERVAL_TIME <= SYSDATE;

内容的提问来源于stack exchange，提问作者Proximus Seraphim Dimitri Davi