RAC Performance Overview

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

Leave a Reply

Your email address will not be published. Required fields are marked *