How to Check Wait Event in Oracle Database

DBA scripts to Check Wait Event in Oracle Database:

SQL > set colsep '|'
   set pagesize 2000
   set linesize 150
   column username format a15
   column sid format 9999
   column status format a10
   column event format a30
   column name format a30
   column p1 format a20
   column p2 format a20
   column p3 format a20
 SQL > 			select v.sid,
   v.username username,
   v.status status,
   n.name,
   w.state,
   n.parameter1 || ' : ' || w.p1 p1,
   n.parameter2 || ' : ' || w.p2 p2,
   n.parameter3 || ' : ' || w.p3 p3
   from v$session v, v$event_name n , v$session_wait w
   where v.username is not null
   and w.event = n.name
   and w.sid=v.sid
   and w.event not in('null event')
   and v.STATUS='ACTIVE'
   /

Example:

SID|USERNAME       |STATUS    |NAME                          |STATE              |P1                  |P2                  |P3
-----|---------------|----------|------------------------------|-------------------|--------------------|--------------------|--------------------
 4914|PUBLIC         |ACTIVE    |class slave wait              |WAITING            |slave id : 0        | : 0                | : 0
 4536|PUBLIC         |ACTIVE    |class slave wait              |WAITING            |slave id : 0        | : 0                | : 0
 3027|PUBLIC         |ACTIVE    |class slave wait              |WAITING            |slave id : 0        | : 0                | : 0
 4540|P1RPHWGBM      |ACTIVE    |db file sequential read       |WAITING            |file# : 103         |block# : 1490384    |blocks : 1
 3023|P1RPHWGBM      |ACTIVE    |direct path read              |WAITED SHORT TIME  |file number : 105   |first dba : 1383936 |block cnt : 128
   23|P1RPHWGBM      |ACTIVE    |PX Deq: Table Q Normal        |WAITED KNOWN TIME  |sleeptime/senderid :|passes : 3          | : 0
     |               |          |                              |                   | 200                |                    |
  2289|P1RPHWGBM      |ACTIVE    |SQL*Net message to client     |WAITED SHORT TIME  |driver id : 14136975|#bytes : 1          | : 0
     |               |          |                              |                   |36                  |                    |
  1888|SYS            |ACTIVE    |SQL*Net message to client     |WAITED SHORT TIME  |driver id : 16508152|#bytes : 1          | : 0
     |               |          |                              |                   |32                  |                    |
  4178|P1RPHWGBM      |ACTIVE    |enq: PS - contention          |WAITING            |name|mode : 13476167|instance : 1        |slave ID : 4
     |               |          |                              |                   |74                  |                    |
  4927|P1RPHWGBM      |ACTIVE    |enq: BF - allocation contentio|WAITING            |name|mode : 11118837|node#/parallelizer# |bloom# : 0
     |               |          |                              |                   |82                  |: 875663873         |

LLEVTIC Computer Speakers, Desktop Speakers with 6 Colorful RGB Lights, Volume Control PC Speakers, USB Powered Gaming Speakers with 3.5mm Aux Cable for PC Monitor Laptop Tablet Phone

44% Off $26.88 $14.99 (as of 28/06/2024 15:51 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

【Full-Range Stereo Sound Quality】Equipped with advanced sound drivers and a full-range 2.0 channel reinforced stereo core, this computer speakers provides the perfect atmosphere for your entertainment activities such as watching movies, gaming, and l... read more

PC Gaming Headset Headphone Hook Holder Hanger Mount, Headphones Stand with Adjustable & Rotating Arm Clamp, Under Desk Design, Universal Fit, Built in Cable Clip Organizer EURPMASK

(27324)

$12.99 (as of 28/06/2024 15:50 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

🎧【Clamp-on Headphone Stand】The E-Hold Series headphone holder fits desks from 0.31 inch to 1.57 inch thick, making it a versatile choice for most setups. The spring clamp is a breeze to remove and reattach. No glue, no mess. It blends seamlessly into... read more

Laptop Cleaning Kit Keyboard Cleaner, Keyboard Cleaning Kit Electronics Cleaning Tool for MacBook iPad Phone iPhone Pro, Brush Tool for Tablet, Computer, PC, TV Camera Lens Computer Vacuum Keyboard

(2045)

$16.98 (as of 28/06/2024 15:43 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

DEEP CLEAN YOUR DEVICE The electronics cleaner kit can effectively deep clean all your electronic devices such as mobile phones, tablets, TVs, etc. It comes with retractable large keyboard brush, polishing cleaning cloth X 2, keycap puller, metal pen... read more

RIWUCT Foldable Laptop Stand, Height Adjustable Ergonomic Computer Stand for Desk, Aluminum Portable Laptop Riser Holder Mount Compatible with MacBook Pro Air, All Notebooks 10-16"

39% Off $30.99 $18.99 (as of 28/06/2024 15:50 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

【Adjustable & Foldable】The laptop stand can raise your laptop up to 7.48 inches. You can incline at different angles and stand it up higher or lower depending on your needs, which will be extremely convenient if you use your laptop in a variety of po... read more