- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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;
- Related Articles
- How to identify blocked and blocking sessions in Oracle ?
- How to identify the SQL consuming more resources in Oracle?
- How to limit Database Resources per Session in Oracle?
- How to view storage configuration of Oracle database?
- How to identify SQL queries with the most waits in Oracle?
- Oracle Database Connection in Python
- How to create a Stored procedure in Oracle database using JDBC API?
- How to drop a table from Oracle database using JDBC API?
- How to insert an image in to Oracle database using Java program?
- Extracting data from SAP HANA database and load to ORACLE database
- How to retrieve a record from an existing table in oracle database using JDBC API?
- How to remove a record from an existing table in oracle database using JDBC API?
- How to identify rows that are not parents of any other rows in a Hierarchy table in Oracle?
- How to identify composite primary key in any MySQL database table?
- How to use Primary Key Constraints and Foreign Key Constraints to enforce database integrity in Oracle?
