Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 401

5 PRD_5 prddb7 01-OCT-10 01:07:05 36 BIAPPS
PRD_5 prddb7 01-OCT-10 01:07:05 36 PRDDB
6 PRD_6 prddb8 19-OCT-10 08:55:45 17 BIETL
PRD_6 prddb8 19-OCT-10 08:55:45 17 PRDDB
7 PRD_7 prddb1 19-OCT-10 07:31:26 18 BIETL
PRD_7 prddb1 19-OCT-10 07:31:26 18 PRDDB
8 PRD_8 prddb2 08-OCT-10 05:17:47 29 BIETL
PRD_8 prddb2 08-OCT-10 05:17:47 29 PRDDB
Based on the discussions in the previous two sections, let's start to look at a production environment to
determine which phase of the global cache service is causing a system to slow down.
Step 1
Using the following query, determine the average time for the prepare phase of the operation:
Script: MVRACPDnTap_ blkpreptime11g.sql
SELECT b1.INST_ID INT,
((b1.VALUE + b2.VALUE + b3.VALUE + b4.value + b5.value + b6.value)*10) "BLK SRV TIME",
(b7.VALUE + b8.VALUE) "BLK SRVD",
((b7.VALUE + b8.VALUE)/((b1.VALUE + b2.VALUE + b3.VALUE + b4.value + b5.value + b6.value)*10))
"PREP TIME"
FROM GV$SYSSTAT b1,
GV$SYSSTAT b2,
GV$SYSSTAT b3,
GV$SYSSTAT b4,
GV$SYSSTAT b5,
GV$SYSSTAT b6,
GV$SYSSTAT b7,
GV$SYSSTAT b8
WHERE b1.NAME = 'gc cr block build time'
AND b2.NAME = 'gc cr block flush time'
AND b3.NAME = 'gc current block flush time'
AND b4.NAME = 'gc cr block send time'
AND b5.NAME = 'gc current block send time'
AND b6.NAME = 'gc current block pin time'
AND b7.NAME = 'gc cr blocks served'
AND b8.NAME = 'gc current blocks served'
AND b1.INST_ID = b2.INST_ID
AND b1.INST_ID = b3.INST_ID
AND b1.INST_ID = b4.INST_ID
AND b1.INST_ID = b5.INST_ID
AND b1.INST_ID = b6.INST_ID
AND b1.INST_ID = b7.INST_ID
AND b1.INST_ID = b8.INST_ID
ORDER BY 1;