Configuring a baseline using queries
Here are some queries that can be used to check the best execution plan using the tables dba_hist_sqlstat, dba_hist_sqltext, and dba_hist_snapshot:
- List the SQL statements with the highest CPU usage in the last hour:
SELECT sql_id, elapsed_time_delta/1000000 as elapsed_seconds, cpu_time_delta/1000000 as cpu_seconds
FROM dba_hist_sqlstat
WHERE TRUNC(begin_interval_time) = TRUNC(SYSDATE)
AND begin_interval_time > SYSDATE - 1/24
ORDER BY cpu_seconds DESC;