Oracle Performance TOP SQL Scripts -4

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 -3

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 10 SQL for last 1 hour with following script.

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum <11;

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/