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 CASEWHEN 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
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;
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
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
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
Posts
