Oracle Performance TOP SQL Scripts -1

Hi,

If you get slowness complaint you need to monitor database which sql is consuming a lots of resource.

You can read older Performance TOP SQL Scripts for Oracle database with following links.

Oracle Performance TOP SQL Scripts -2

Oracle Performance TOP SQL Scripts -3

Oracle Performance TOP SQL Scripts -4

Oracle DBA should monitor database everytime and if there are many sqls which is running long execution time then it should be reported to the developer and developer and dba should examine these sqls.

You can find TOP Elapsed time SQL in certain hours with below script.

 SELECT st.sql_text, sub.sql_id, sub.ELAPSED_TIME PER_EXEC_ELAPSED_TIME_MINUTES
FROM DBA_HIST_SQLTEXT st,
( SELECT t.sql_id,
ROUND (
SUM (t.elapsed_time_delta / 60000000)
/ SUM (t.executions_delta))
ELAPSED_TIME
FROM dba_hist_sqlstat t, dba_hist_snapshot s, DBA_HIST_SQLTEXT st
WHERE t.snap_id = s.snap_id
AND t.dbid = s.dbid
AND t.instance_number = s.instance_number
AND t.executions_delta > 0
AND s.BEGIN_INTERVAL_TIME >
TO_DATE ('11/15/2018 13:00:00',
'mm/dd/yyyy hh24:mi:ss')
AND END_INTERVAL_TIME <
TO_DATE ('11/15/2018 16:01:00',
'mm/dd/yyyy hh24:mi:ss')
GROUP BY t.sql_id
ORDER BY 2 DESC) sub
WHERE sub.sql_id = st.sql_id
ORDER BY 3 DESC;

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/