Oracle Performance TOP SQL Scripts -2

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

select * from (
select ss.sql_text,
    a.SQL_ID, 
    sum(CPU_TIME_DELTA), 
    sum(DISK_READS_DELTA),
    count(*)
from 
    DBA_HIST_SQLSTAT a, dba_hist_snapshot s,v$sql ss
where
 s.snap_id = a.snap_id and a.sql_id=ss.sql_id
 and s.begin_interval_time > sysdate -1
    group by 
    ss.sql_text,a.SQL_ID
order by 
    sum(CPU_TIME_DELTA) desc)
where rownum<20;

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/