Tuning Oracle RAC

CLUSTER WAIT EVENTS QUERIES

 

1. cluster wait events class should not be at top

 

  SELECT wait_class time_cat,

         ROUND ( (time_secs), 2) time_secs,
         ROUND ( (time_secs) * 100 / SUM (time_secs) OVER (), 2) pct
    FROM (  SELECT wait_class wait_class,
                   SUM (time_waited_micro) / 1000000 time_secs
              FROM gv$system_event
             WHERE wait_class <> 'Idle' AND time_waited > 0
          GROUP BY wait_class
          UNION
          SELECT 'CPU', ROUND ( (SUM (VALUE) / 1000000), 2) time_secs
            FROM gv$sys_time_model
          WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;

TIME_CAT,TIME_SECS,PCTCPU,153097,44.27
Other,100067.74,28.94
System I/O,56365.91,16.3
Concurrency,24033.05,6.95
Cluster,4419.66,1.28
Commit,4003.31,1.16
User I/O,2554.04,0.74
Application,774.07,0.22
Configuration,291.68,0.08
Network,154.79,0.04
Administrative,46.5,0.01
Scheduler,0.01,0

2. time spent in cluster wait events or avergae wait ms should not be higher

 WITH system_event

        AS (SELECT CASE
                      WHEN wait_class = 'Cluster' THEN event
                      ELSE wait_class
                   END
                      wait_type,
                   e.*
             FROM gv$system_event e)
SELECT wait_type,
         ROUND (total_waits / 1000, 2) waits_1000,
        ROUND (time_waited_micro / 1000000 / 3600, 2) time_waited_hours,
         ROUND (time_waited_micro / 1000 / total_waits, 2) avg_wait_ms,
         ROUND (time_waited_micro * 100 / SUM (time_waited_micro) OVER (), 2)
            pct_time
    FROM (  SELECT wait_type,
                   SUM (total_waits) total_waits,
                   SUM (time_waited_micro) time_waited_micro
              FROM system_event e
          GROUP BY wait_type
          UNION
          SELECT 'CPU', NULL, SUM (VALUE)
            FROM gv$sys_time_model
           WHERE stat_name IN ('background cpu time', 'DB CPU'))
   WHERE wait_type <> 'Idle'
ORDER BY time_waited_micro DESC;

WAIT_TYPE,WAITS_1000,TIME_WAITED_HOURS,AVG_WAIT_MS,PCT_TIME
CPU,,42.53,,44.27
Other,301559.5,27.8,0.33,28.94
System I/O,62707.59,15.66,0.9,16.3
Concurrency,46016.57,6.68,0.52,6.95
Commit,3903.83,1.11,1.03,1.16
User I/O,3276.44,0.71,0.78,0.74
gc current block 2-way,3027.9,0.42,0.5,0.44
gc cr block 2-way,2144.59,0.33,0.55,0.34
gc current grant busy,1330.46,0.22,0.61,0.23
Application,2154.83,0.22,0.36,0.22
gc cr multi block request,371.18,0.09,0.87,0.09
Configuration,30.9,0.08,9.44,0.08
gc current block busy,77.06,0.08,3.54,0.08
Network,43979.22,0.04,0,0.04
gc current grant 2-way,194.44,0.02,0.4,0.02
gc cr block busy,39.41,0.02,1.6,0.02
Administrative,0.47,0.01,98.73,0.01
gc cr disk read,114.88,0.01,0.37,0.01
gc cr grant 2-way,103.01,0.01,0.39,0.01
gc buffer busy release,6.71,0.01,4.06,0.01
gc current multi block request,31.51,0.01,0.75,0.01
gc current block congested,20.39,0,0.59,0
gc cr block congested,16.11,0,0.65,0
gc current split,0.8,0,8.63,0
gc buffer busy acquire,1.27,0,2.07,0
gc current retry,0.12,0,20.61,0
gc current grant congested,1.22,0,0.5,0
gc cr grant congested,0.59,0,0.51,0
gc cr failure,0.11,0,0.67,0
gc remaster,0,0,45.5,0
gc domain validation,0,0,6.73,0
Scheduler,0,0,2.43,0

3. MEASURING GLOBAL CACHE LATENCY

 

 

To measure Global Cache latency, we use the wait interface as exposed by GV$SYSTEM_EVENT.2 The following query reports on average times for each of the Global Cache request types and single-block read times (for comparison):

  SELECT event,
         SUM (total_waits) total_waits,
         ROUND (SUM (time_waited_micro) / 1000000, 2) time_waited_secs,
         ROUND (SUM (time_waited_micro) / 1000 / SUM (total_waits), 2) avg_ms
    FROM gv$system_event
   WHERE wait_class <> 'Idle'
         AND (   event LIKE 'gc%block%way'
              OR event LIKE 'gc%multi%'
             OR event LIKE 'gc%grant%'
              OR event = 'db file sequential read')
GROUP BY event
  HAVING SUM (total_waits) > 0
ORDER BY event;

EVENT,TOTAL_WAITS,TIME_WAITED_SECS,AVG_MSdb file sequential read,559187,1438.21,2.57
gc cr block 2-way,2144622,1180.78,0.55
gc cr grant 2-way,103014,40.35,0.39
gc cr grant congested,590,0.3,0.51
gc cr multi block request,371178,323.34,0.87
gc current block 2-way,3027919,1526.22,0.5
gc current grant 2-way,194439,77.95,0.4
gc current grant busy,1330461,806.32,0.61
gc current grant congested,1223,0.61,0.5
gc current multi block request,31511,23.68,0.75

EXAMINING THE INTERCONNECT

SELECT instance_number, host_name, instance_name,

 name_ksxpia network_interface, ip_ksxpia private_ip 

 FROM x$ksxpia CROSS JOIN  v$instance

WHERE pub_ksxpia = 'N';

Inst     Host                     Net    Private

#       Name      INSTANCE_NAME  IFace   IP

---- ----------- --------------  ----- ------------

1 elquest.dev.me MELRAC3         eth1 192.168.0.12

On linux box : ping -c 5 -s 8192 192.168.0.12

see the response of private interconnect network

for windows use flag -l for packet size and -n for counts :

SIGNS OF INTERCONNECT PROBLEMS

SELECT name, SUM(VALUE)

 FROM gv$sysstat

 WHERE name LIKE 'gc%lost'

 OR name LIKE 'gc%received'

 OR name LIKE 'gc%served'

GROUP BY name

 ORDER BY name;

NAME,SUM(VALUE)gc blocks lost,8
gc claim blocks lost,0
gc cr blocks received,2888683
gc cr blocks served,2888684
gc current blocks received,4051222
gc current blocks served,4051229