Table of Contents
Overview about RAC GV$ performance tables
GV$ACTIVE_SESSION_HISTORY
The GV$ACTIVE_SESSION_HISTORY view displays active database session activity, sampled once every second.
SAMPLE_ID The unique identifier of the Active Session History record.
SAMPLE_TIME When Active Session History captured this data on all active sessions.
USER_ID The numerical user ID (not the username) of the database user who created this session.
SESSION_ID The session ID (SID) of the session.
SESSION_STATE The state the session was in when Active Session History took the sample. It shows WAITING
if the session was waiting for something;
otherwise, it shows ON CPU to indicate that the session was doing productive work.
EVENT If the session was in a WAITING state (in the SESSION_STATE column), this column will show
the wait event the session was waiting for.
TIME_WAITED. If the session was in a WAITING state, this column will show how long it had been waiting
when Active Session History took the sample.
WAIT_TIME If the session is doing productive work—not in a WAITING state—this column will show how
long the session waited for the last wait event.
SQL_ID The ID of the SQL statement the session was executing at the time the sample was taken.
SQL_CHILD_NUMBER The child number of the cursor. If this is the only version of the cursor,
the child number will be 0.
GV$SESS_TIME_MODEL and GV$SYS_TIME_MODEL (you can also use cluster view : dba_hist_sys_time_model -> INSTANCE_NUMBER,
SNAP_ID )
The GV$SESS_TIME_MODEL and GV$SYS_TIME_MODEL views contain time model statistics, including DB time which is the
total time spent in database calls.
DB Time Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does
not include the elapsed time spent on instance background processes such as PMON.
DB CPU Amount of CPU time (in microseconds) spent on database user-level calls. This does not include
the CPU time spent on instance background processes such as PMON.
background elapsed time Amount of elapsed time (in microseconds) consumed by database background processes.
background CPU time Amount of CPU time (in microseconds) consumed by database background processes.
SQL> select inst_id, stat_name, trunc(value/1000000,2) seconds from gv$sys_time_model order by 1, 3 desc;
SQL> select * from dba_hist_sys_time_model where snap_id = 925 order by 6 desc;
SNAP_ID DBID INSTANCE_NUMBER STAT_ID STAT_NAME VALUE
---------- ---------- --------------- ---------- ----------------------------------------- ----------
925 794109772 1 2451517896 background cpu time 2.3122E+10
925 794109772 1 4157170894 background elapsed time 2.1866E+10
925 794109772 2 4157170894 background elapsed time 2.0039E+10
925 794109772 3 4157170894 background elapsed time 1.2775E+10
925 794109772 3 3649082374 DB time 7605348722
GV$SESSION_WAIT
The GV$SESSION_WAIT view displays information about the current or last wait for each session (such as wait ID,
class, and time).
GV$SESSION
The GV$SESSION view displays information about each current session and contains the same wait statistics as
those found in the V$SESSION_WAIT view. If applicable, this view also contains detailed information about the
object that the session is currently waiting for (such as object number, block number, file number, and row
number), the blocking session responsible for the current wait (such as the blocking session ID, status, and type),
and the amount of time waited.
GV$SESSION_EVENT
The GV$SESSION_EVENT view provides summary of all the events the session has waited for since it started.
GV$SESSION_WAIT_CLASS
The GV$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for
each session.
GV$SESSION_WAIT_HISTORY
The GV$SESSION_WAIT_HISTORY view displays information about the last ten wait events for each active session
(such as event type and wait time).
GV$SYSTEM_EVENT
The GV$SYSTEM_EVENT view provides a summary of all the event waits on the instance since it started.
GV$EVENT_HISTOGRAM
The GV$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time
on an event basis.
GV$FILE_HISTOGRAM
The GV$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.
GV$SYSTEM_WAIT_CLASS
The GV$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent
in each class of wait events.
Typical GCS Latencies for sending/receiving CR/Current blocks
Basically CR block request time and current block request time are what you are looking at. CR block request time = build time + flush time + send time Current block request time = pin time + flush time + send time CR block request time is the time it takes to build the CR block in an instance that owns the appropriate image and the time to flush it, you have to write to disk , and how long it takes to send it across the interconnect. Current block request time is how long it takes to pin the image in an instance that owns the block image and time it takes to flush it and send it across, because you cannot send it while some is changing that block at the same time. That is why you need to pin the block in exclusive mode then flush it and send it over the interconnect.
How to find segḿent_name, object_type from gc wait messages ?
Identify block number and file_id from 10046 trace WAIT #140557198260504: nam='gc cr block 2-way' ela= 529 p1=1 p2=667 p3=1 obj#=74 tim=1384249119101498 SQL> SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks -1 ) ); Enter value for file_no: 1 Enter value for block_value: 667 SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS ---------------- --------------------- ---------- ------- SEQ$ TABLE 664 8 WAIT #140557198270352: nam='gc current block busy' ela= 174746 p1=4 p2=554384 p3=33554433 obj#=88483 tim=1384249119278823 SQL> @which_obj Enter value for file_no: 4 Enter value for block_value: 554384 old 3: WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks -1 ) ) new 3: WHERE file_id = 4 AND ( 554384 BETWEEN block_id AND ( block_id + blocks -1 ) ) SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS -------------------- ----------------------- ---------- ---------- PK_RAC_PERFTEST INDEX 554384 8