DB Wait events
CPU Time:
Scenario:
A SQL query is taking a long time to execute, causing the CPU Time wait event to occur. The query is using a full table scan instead of an index, which is causing a high CPU load and slowing down other processes.
fixes:
=====
Adding indexes
Query rewrites
limiting number of sessions
I/O Related Wait Events:
Scenario:
A database application is experiencing slow I/O operations, causing the I/O Related Wait Event to occur. The storage systems are not optimized and there is heavy contention for storage resources.
Fixes:
=====
Review your Storage system
Check for disk usage
Check for storage replacement for a high end hardware
Locks:
Scenario:
A database application is experiencing slow performance due to excessive locking. The application is using table-level locking, causing a high contention for resources and slowing down other processes.
fixes :
====
switch to row level locking
Make sure to use frequent commits in the code
Network Related Wait Events:
Scenario:
A database application is experiencing slow performance due to network-related wait events. The application is transferring large amounts of data over the network, causing a high network traffic and slowing down other processes.
Memory Related Wait Events:
Scenario:
A database application is experiencing slow performance due to memory-related wait events. The application is running out of memory, causing frequent paging and slow performance.
egs:
CPU Time:
AWR Report:
lua
Copy code
Event Waits Time(s) Avg Time(ms) %Total
------------------------------------- ----------- ---------- ----------- ------
CPU time 10,000 80.1%
db file sequential read 1,000,000 2,000 2 15.9%
log file parallel write 10,000 500 50 4.0%
In this scenario, we can see that the CPU time event is responsible for 80.1% of the total wait time. This indicates that we need to optimize our SQL queries to reduce the CPU load.
Solution:
To optimize our SQL queries, we can do the following:
Identify queries that are consuming too much CPU using SQL profiling tools.
Rewrite queries to use indexes instead of full table scans.
Break up large queries into smaller ones to reduce the CPU load.
Use connection pooling to reduce the number of connections and optimize resource usage.
I/O Related Wait Events:
AWR Report:
lua
Copy code
Event Waits Time(s) Avg Time(ms) %Total
------------------------------------- ----------- ---------- ----------- ------
db file sequential read 100,000 20,000 200 60.0%
db file scattered read 50,000 10,000 200 30.0%
log file parallel write 10,000 500 50 1.5%
In this scenario, we can see that the db file sequential read and db file scattered read events are responsible for 90% of the total wait time. This indicates that we need to optimize our storage systems to reduce I/O wait times.
Solution:
To optimize our storage systems, we can do the following:
Balance storage across multiple disks to reduce contention.
Optimize read and write operations to reduce I/O wait times.
Use asynchronous I/O to improve performance.
Upgrade to faster storage devices such as solid-state drives (SSDs).
Locks:
AWR Report:
Event Waits Time(s) Avg Time(ms) %Total
------------------------------------- ----------- ---------- ----------- ------
enqueue 10,000 5,000 500 80.0%
latch free 500 100 200 1.6%
log file parallel write 10,000 500 50 8.0%
In this scenario, we can see that the enqueue event is responsible for 80% of the total wait time. This indicates that we need to optimize our locking strategy to reduce contention.
Solution:
To optimize our locking strategy, we can do the following:
Switch to row-level locking to reduce contention.
Reduce the number of connections to reduce contention.
Use connection pooling to optimize resource usage.
Tune lock parameters to improve performance.
Network Related Wait Events:
AWR Report:
Event Waits Time(s) Avg Time(ms) %Total
------------------------------------- ----------- ---------- ----------- ------
SQL*Net message from client 100,000 5,000 50 80.0%
SQL*Net message to client 100,000 500 5 8.0%
log file parallel write 10,000 500 50 8.0%
In this scenario, we can see that the SQL*Net message from client event is responsible for 80% of the total
db file sequential read:
Reason: This wait event occurs when a process is waiting for a single block to be read from disk in a sequential manner.
Fix: To reduce this wait event, you can use direct path reads or tune the I/O subsystem by using faster disks, adding more disks to reduce contention, and increasing the size of the buffer cache.
db file scattered read:
Reason: This wait event occurs when a process is waiting for multiple blocks to be read from disk in a random or scattered manner.
Fix: To reduce this wait event, you can use direct path reads or tune the I/O subsystem by using faster disks, adding more disks to reduce contention, and increasing the size of the buffer cache.
direct path read / direct path read temp:
Reason: These wait events occur when a process is waiting for a large number of blocks to be read in a single I/O operation.
Fix: To reduce these wait events, you can tune the I/O subsystem by using faster disks, adding more disks to reduce contention, and increasing the size of the buffer cache. You can also use parallel query or partitioning to improve performance.
global cache cr request:
Reason: This wait event occurs when a process is waiting for a cache fusion request to complete.
Fix: To reduce this wait event, you can use the appropriate data partitioning technique, reduce the number of transactions, and increase the number of nodes in the cluster.
buffer busy waits / read by other session:
Reason: These wait events occur when a process is waiting for a buffer to be read or written to by another session.
Fix: To reduce these wait events, you can tune the buffer cache size, use partitioning to reduce contention, and reduce the number of transactions.
FREELISTS and/or INITRANS:
Reason: These wait events occur when a process is waiting for a free buffer or a free block in a buffer.
Fix: To reduce these wait events, you can increase the number of freelists or the initrans parameter.
log file sync:
Reason: This wait event occurs when a process is waiting for a log buffer to be written to disk.
Fix: To reduce this wait event, you can tune the log buffer size, use asynchronous I/O, and use a faster disk subsystem.
direct path write / direct path write temp:
Reason: These wait events occur when a process is waiting for a large number of blocks to be written in a single I/O operation.
Fix: To reduce these wait events, you can tune the I/O subsystem by using faster disks, adding more disks to reduce contention, and increasing the size of the buffer cache.
library cache lock:
Reason: This wait event occurs when a process is waiting for a lock on a library cache object.
Fix: To reduce this wait event, you can use a smaller number of shared pool objects or reduce contention by increasing the number of library cache latches.
db file parallel read / db file parallel write:
Reason: These wait events occur when a process is waiting for a parallel I/O operation to complete.
Fix: To reduce these wait events, you can increase the degree of parallelism, use faster disks, add more disks to reduce contention, and increase the size of the buffer cache.
log buffer space:
Reason: This wait event occurs when a process is waiting for space in the log buffer to become available.
Fix: To reduce this wait event, you can increase the size of the log buffer, use asynchronous I/O, and use a faster disk subsystem.
Comments
Post a Comment