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

WHERE GCSPF.data_object_id = DO.data_object_id
AND remaster_cnt > 1
ORDER BY inst_id,
10 DESC;
OBJID ONAME/SONAME OT GC_MP CM PM CNT
--------- ----------------------------- ---- ----------- --- --- ----
470215 X_SRV_REQ_CHARGES_D T Affinity 5 8 11
469833 W_PRODUCT_XACT_A T Affinity 7 8 10
492984 W_INV_DY_BAL_F_20101231 IP Affinity 7 8 8
501323 W_INV_MTHBAL_F_U1 I Affinity 6 7 6
470136 X_SRV_INSTALL_BASE_AUDIT_F T Affinity 6 8 6
613934 W_GL_REVN_F1_20101131 TP Affinity 8 7 5
492704 BCI_W_PROD_XACT_F_N2 I Affinity 8 6 5
469945 W_SALES_SCHEDULE_LINE_F T Affinity 6 8 5
468803 W_SLS_ORDR_LIN_F_20080331 TP Affinity 6 8 4
501319 X_SRV_REQ_CHARGES_F_U1 I Affinity 6 8 4
493462 W_EMP_DAYSNP_F_U1 I Affinity 7 6 4
1092087 X_SRV_REQ_F_U1 I Affinity 6 8 4
489597 X_SRV_REQ_CHARGES_D_U1 I Affinity 8 7 3
469403 W_AR_AGING_INVOICE_A T Affinity 8 7 3
From the preceding output, most of the objects are currently being mastered by instances 6, 7, and 8. The output
also illustrates objects at the partition level (IP = index partition and TP = table partition). Recollecting from Figure 13-6 ,
instances 6, 7, and 8 are from SSKYPOOL2 and run the BIETL database service.
From Figure 13-7 discussed in the previous workshop, one of the primary wait events experienced by the instance
in SSKYPOOL1 is the gc remaster . It amounted to about 3.23% of the wait time and also experienced several timeouts
during remaster operations. Contentions experienced during remastering operations are primarily due to a high
level of modifications to blocks within an object. As illustrated in the previous output, BIETL is a load process and
data is being loaded through instances 6, 7, and 8 ( SSKYPOOL2 ), causing frequent opens on the objects. This in turn
causes frequent DRM or also could indicate the DRM is taking a long time to complete. Starting with Oracle Database
11g Release 2, dynamic remastering is the default configuration and can be disabled by setting _GC_POLICY_TIME 8
parameter to 0. (In prior releases of RAC, dynamic remastering could be disabled using the parameter
_LM_DYNAMIC_REMASTERING or by setting the parameter _GC_AFFINITY_TIME=0. Mastering and remastering of objects
is based on policies, illustrated in column GC_MP in the preceding output.
Polices for the object can also be obtained by querying the V$POLICY_HISTORY view as illustrated in the
following output.
Script: MVRACPDnTap_policyhistory.sql
SELECT inst_id,
policy_event,
data_object_id,
target_instance_number,
event_date
8 Underscore parameters should be changed only with prior approval and discussion with Oracle support.