Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to identify recent WAIT events in a Oracle database ?
Problem:
You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.
Solution
Oracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users responsible for those waits.
SQL to find the wait events
Example
SELECT event, SUM(wait_time + time_waited) total_wait_time FROM v$active_session_history GROUP BY event ORDER BY total_wait_time DESC;
To find out the users who experienced the most waits we can use below SQL.
Example
SELECT s.sid, s.username, SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a, v$session s WHERE a.session_id=s.sid GROUP BY s.sid, s.username ORDER BY total_wait_time DESC;
To find out the objects with the most waits we can use below SQL.
Example
SELECT a.current_obj#, d.object_name, d.object_type, a.event, SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a, dba_objects d WHERE a.current_obj# = d.object_id GROUP BY a.current_obj#, d.object_name, d.object_type, a.event ORDER BY total_wait_time;
Finally, we can identify the SQL statements that have been waiting the most with below query.
Example
SELECT a.user_id, u.username, s.sql_text, SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a, v$sqlarea s, dba_users u WHERE a.sql_id = s.sql_id AND a.user_id = u.user_id GROUP BY a.user_id, s.sql_text, u.username;
6K+ Views
