Show Best Performing Plan Hash Values Over Time

--
-- Show Best Performing Plan Hash Values Over Time
--
 
SELECT
  sql_id,
  plan_hash_value,
  SUM(executions_total),
  SUM(elapsed_time_total),
  ROUND(SUM(elapsed_time_total)/(sum(executions_total)+1),0)/1000000 as Per_Execution_sec,
  ROUND(SUM(rows_processed_total)/(sum(executions_total)+1),0) as Rows_Per_Exec
FROM 
  dba_hist_sqlstat
WHERE 
  sql_id='&sqlid'
AND 
  plan_hash_value='&phv'
GROUP BY 
  sql_id,
  plan_hash_value
ORDER BY 3,5 ASC

Published 3rd November 2022