oracle 查snap_id中cost最少最好的plan

从AWR中找执行计划

SELECT SQL_ID, COUNT (*)
FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;

查 SQL Plan Hash Value

SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='&SQL_ID';

查snap_id中cost最少最好的plan

SELECT SS.SNAP_ID,
SS.INSTANCE_NUMBER,
BEGIN_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,OPTIMIZER_COST,
DISK_READS_TOTAL,
BUFFER_GETS_TOTAL,
ROWS_PROCESSED_TOTAL,
CPU_TIME_TOTAL,
ELAPSED_TIME_TOTAL,
IOWAIT_TOTAL,
NVL (EXECUTIONS_DELTA, 0) EXECS,
( ELAPSED_TIME_DELTA
/ DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
/ 1000000
AVG_ETIME,
( BUFFER_GETS_DELTA
/ DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE SQL_ID = '&SQL_ID'
AND SS.SNAP_ID = S.SNAP_ID
AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;