Oracle Performance Tunning

This is how to detect the longest running queries in an Oracle Database by utilizing the tables dba_hist_sqlstat, dba_hist_sqltext, and dba_hist_snapshot. We will also explore the previous execution plans (hash values) that could potentially offer better performance. Lastly, we will differentiate between SQL Profiles and SQL Plan Baselines and determine which approach is better for specific scenarios, along with their implementation using queries.

To detect the longest running queries, we need to query the dba_hist_sqlstat and dba_hist_sqltext tables. The following query retrieves the longest running queries along with their elapsed times:

1. List the SQL statements with the highest CPU usage:

SELECT parsing_schema_name,
       sql_id,
       executions,
       elapsed_time,
       round(elapsed_time / (1000000 * decode(executions,0,1, executions)), 2) AS etime_per_exec
FROM v$sqlarea
ORDER BY etime_per_exec ASC;