Database Reference
In-Depth Information
Remember that gv$event_histogram gives performance metrics from the start of an instance and could be
misleading. So, it is crucial to review the performance metrics during a short period of time using AWR reports or
custom scripts. The query in Listing 10-8 shows the wait histogram for the past day. This query uses AWR tables to
retrieve the wait_time for an event.
Listing 10-8.
Script: event_hist_from_awr.sql
set lines 160
col begin_interval_time format a30
col event_name format a30
col instance_number format 99 head "Inst"
break on begin_interval_time
SELECT snaps.begin_interval_time,
snaps.instance_number,
hist.event_name,
hist.wait_time_milli,
hist.wait_count
FROM dba_hist_event_histogram hist, DBA_HIST_SNAPSHOT snaps
WHERE snaps.snap_id = hist.snap_id
AND snaps.instance_number = hist.instance_number
AND snaps.begin_interval_time > sysdate-1 -- modify to specify constrained time window
AND hist.event_name = lower('&event_name')
ORDER BY snaps.snap_id ,
instance_number,
wait_time_milli
/
BEGIN_INTERVAL_TIME Inst EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT
------------------------------ ---- -------------------- --------------- ----------
01-NOV-12 05.00.15.638 PM 1 gc cr block 2-way 1 110970
1 gc cr block 2-way 2 3620790
1 gc cr block 2-way 4 1708432
1 gc cr block 2-way 8 15685
1 gc cr block 2-way 16 340
1 gc cr block 2-way 32 26
1 gc cr block 2-way 64 8
1 gc cr block 2-way 128 1
1 gc cr block 2-way 256 1
1 gc cr block 2-way 2048 1
Note that this query retrieves performance metrics for a period of one day. If you are analyzing a performance
problem for a constrained time frame, then modify the query to retrieve data for that problem time frame. If the
problem is constrained to a smaller window of time, then reviewing the performance metrics over a longer period of
time can be misleading.
Eliminate the Network as Root Cause
Another action item is to eliminate the network as a problem. A few RAC wait events serve as a baseline and can be
effectively used to eliminate the network layer being the probable root cause of a performance problem. There are two
events that primarily can be used as baseline events. These events do not suffer from busy or congestion-related waits.
So, if the average wait time for these baseline events is in a 2-4 ms range, that would imply that the network can be
eliminated as an issue. This technique is useful when you are debugging a stressful production performance issue.
