Oracle Performance TOP SQL Scripts -3

Hi,

If you get slowness complaint from customer,  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 -1

Oracle Performance TOP SQL Scripts -2

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 IO SQL for last 24 hours with following script.

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum<10;

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/