Oracle Database Wait Event Scripts -1

Hi,

When you take many complaints from customer about slowness of database, you should check Oracle database wait events.

You can check Top Oracle database wait events in Cache which is v$ queries with below script.

select  wait_class,
       sum(total_waits), sum(time_waited)
    from gv$session_wait_class
    where wait_class !='Idle'
    group by wait_class
    order by 3 desc;

You can check Top Oracle database wait events from Active session history which is v$active_session_history queries with below script.

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from gv$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum <= 10;

 

Query result is like below screenshot.

 

  

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/