Configuring a baseline using queries

Configuring a baseline using queries
Photo by Sylvia Yang / Unsplash

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:

  1. 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;