Oracle Wait Classes
Here is my favorite SQL on getting a feel of what is slowing my system down
select wait_class, sum(total_waits), sum(time_waited), round(sum(time_waited)/sum(total_waits),2) WAIT_TIME_PER_TXN
from V$SERVICE_WAIT_CLASS
where service_name='RECONPRD' group by wait_class
order by sum(time_waited)/sum(total_waits) desc
This would immediately tell me how much time is spent (or “wasted”) on any DB component.
As a DBA, it would be of interest to me to maintain wait classes that are high on Administrative, Configuration, Concurrency. I would definitely hit the programmers when it comes Application-related wait events, and the System Administrators for Disk I/O related wait times.
1. Administrative – Commands issued by a privileged user (that is, DBA) that result in other users waiting—an index rebuild, for example.
2. Application – Generally related to application design, this category includes lock waits caused by row-level locking and explicit or implicit lock commands (implicit lock commands such as those generated by a DDL statement).
3. Cluster Global cache, global enqueue, and global messaging – related wait events in a RAC environment.
4. Commit - Currently only includes a wait event for redo log sync confirmation after a commit.
5. Concurrency - Generally, waits involving concurrent parsing and buffer cache latch and lock contention; indicative of many sessions going after the same resources.
6. Configuration - Wait events in this category can generally be resolved by tuning; includes undersized log buffer space, logfile sizes, buffer cache size, shared pool size, ITL allocation, HW enqueue contention, ST enqueue contention, and so on.
7. Idle Session is inactive.
8. Other – Catchall for wait events not associated with one of the predefined classes.
9. Network – Waits for data to be sent over the network—specifically waits in the networking layer or on hardware.
10. Scheduler - Waits due to Resource Manager prioritization.
11. System I/O – Waits on I/O done by background processes (except for MMON and SMON).
12. User I/O - Wait for blocks to be read off disk by foreground process, SMON, or MMON.
References:
Tags: DBA, Oracle, Wait Events
You can comment below, or link to this permanent URL from your own site.
