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

■ From the earlier output from GV$RESOURCE_LIMIT , it should be noted that whereas gcs_resources and
gcs_shadows (illustrated in Figure 13-2 ) are maintained in the shared pool, other parameters are maintained in other
sections of the Sga, for example, the fixed Sga.

Note

Step 6
Why are only two instances in the cluster starving for shared pool sizes compared to the other instances? This could
be an indication to look at the processes or application configured to run on these instances. The following query lists
the current service-to-instance distribution:

SQL>!srvctl status service -d PRDDB
Service BCKUP: prddb7, prddb8
Service BIAPPS: prddb1, prddb2, prddb3, prddb4, prddb5,
Service BIETL: prddb6,prddb7, prddb8
Service DBAUSER: prddb1, prddb2, prddb3, prddb4, prddb5, prddb6
Service ENDUSER: prddb7, prddb8

Step 7
The RAC cluster has workload distributed using database services. And the average services times for these services
indicate that the workload has not been distributed evenly across all the instances in the cluster.

Script: MVRACPDnTap_SvcStats.sql

break on SERVICE_NAME skip 1
SELECT SERVICE_NAME,
INSTANCE_NAME,
ELAPSEDPERCALL SERVICE_TIME,
CPUPERCALL CPU_TIME,
DBTIMEPERCALL DB_TIME,
CALLSPERSEC THROUGHPUT
FROM GV$INSTANCE GVI,
GV$ACTIVE_SERVICES GVAS,
GV$SERVICEMETRIC GVSM
WHERE GVAS.INST_ID = GVSM.INST_ID
AND GVAS.NAME_HASH = GVSM.SERVICE_NAME_HASH
AND GVI.INST_ID = GVSM.INST_ID
AND GVSM.GROUP_ID = 10
AND GVSM.SERVICE_NAME NOT IN ('SYS$BACKGROUND')
ORDER BY
SERVICE_NAME,
GVI.INST_ID;