Table of Contents
Trigger events for Dynamic Remastering
- Manual remastering using oradebug lkdebug -m pkey
- Resource affinity
- Instance crash
Init.ora paramaters for DRM
_gc_policy_time: (Oracle10g: _gc_affinity_time), default=10 Interval in minutes in which object statistics are evaluated and decisions are made to establish or dissolve affinity or read-mostly locking. When set to zero, object affinity and read-mostly policies are disabled. _gc_policy_minimum: (Oracle10g: _gc_affinity_minimum) default=6000 in Oracle10g, 1500 in Oracle11g Minimum number of global cache operations (open, convert, close) per minute on the object to qualify for affinity or read-mostly. The default may be tuned to allow more or less objects to be picked. _gc_read_mostly_locking (default is TRUE). Setting this to FALSE would disable read mostly related DRMs. gcs_server_processes (default is derived from CPU count/4). May need to increase this above the default to add LMS processes to complete the work during a DRM but the default is usually adequate. _gc_element_percent (default is 110). May need to apply the fix for bug 14791477 and increase this to 140 if running out of lock elements. Usually not necessary to change this parameter. Change these paramaters: SQL> alter system set "_gc_policy_minimum" = 10 scope=spfile; SQL> alter system set "_gc_policy_time" = 1 scope=spfile Query these parameters: col Parameter FOR a20 col Instance FOR a10 col Description FOR a60 word_wrapped select instance_name, host_name from V$instance; SELECT a.ksppinm "Parameter", c.ksppstvl "Instance", a.ksppdesc "Description" FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p WHERE a.indx = b.indx AND a.indx = c.indx AND p.name(+) = a.ksppinm AND UPPER(a.ksppinm) LIKE UPPER('%gc_policy%') ORDER BY a.ksppinm;
Data Dictionary Table used by DRM
X$OBJECT_POLICY_STATISTICS X$OBJECT_POLICY_STATISTICS (Oracle11g) shows per-object statistics maintained by LCK0 and used to make affinity or read-mostly decisions. These statistics are kept since the last policy decision period (determined by _gc_policy_time). The sum of the last 3 columns (sopens,xopens,xfers) decides whether the object will be considered for DRM (_gc_policy_minimum). The duration of the stats are controlled by _gc_policy_time (default is 10 minutes). V$GCSPFMASTER_INFO V$GCSPFMASTER_INFO describes the current and previous master instances and the number of re-masterings of Global Cache Service resources belonging to files mapped to instances. Column Datatype Description FILE_ID NUMBER File number DATA_OBJECT_ID NUMBER Data object ID GC_MASTERING_POLICY VARCHAR2(11) Data object type. The possible values are Affinity or Read mostly. CURRENT_MASTER NUMBER Master instance of this file PREVIOUS_MASTER NUMBER Previous master instance of this file REMASTER_CNT NUMBER Number of times this has been remastered gv$policy_history ( new in 11g ) gv$policy_histor displays a DRM history for at object level GV$DYNAMIC_REMASTER_STATS GV$DYNAMIC_REMASTER_STATS displays statistical information about the dynamic remastering process. All times are given in hundredths of a second, and total values reflect what has been collected since instance startup. Column Datatype Description REMASTER_OPS NUMBER Total number of dynamic remastering operations REMASTER_TIME NUMBER Total dynamic remastering time REMASTERED_OBJECTS NUMBER Total number of objects dynamically remastered due to affinity QUIESCE_TIME NUMBER Total quiesce step time FREEZE_TIME NUMBER Total freeze step time CLEANUP_TIME NUMBER Total cleanup step time REPLAY_TIME NUMBER Total replay step time FIXWRITE_TIME NUMBER Total fixwrite step time SYNC_TIME NUMBER Total synchronization step time RESOURCES_CLEANED NUMBER Total number of resources cleaned in the cleanup steps REPLAYED_LOCKS_SENT NUMBER Total number of locks replayed to other instances in the replay steps REPLAYED_LOCKS_RECEIVED NUMBER Total number of locks received from other instances in the replay steps CURRENT_OBJECTS NUMBER Current number of objects remastered on this instance due to affinity Live data: INST_ID REMASTER_OPS REMASTER_TIME REMASTERED_OBJECTS QUIESCE_TIME FREEZE_TIME CLEANUP_TIME REPLAY_TIME FIXWRITE_TIME SYNC_TIME ---------- ------------ ------------- ------------------ ------------ ----------- ------------ ----------- ------------- ---------- 1 6 231 6 23 2 7 89 6 100 3 19 497 19 41 7 29 76 14 326 2 8 309 8 20 5 15 41 11 213 INST_ID RESOURCES_CLEANED REPLAYED_LOCKS_SENT REPLAYED_LOCKS_RECEIVED CURRENT_OBJECTS ---------- ----------------- ------------------- ----------------------- --------------- 1 0 68414 129113 2 3 0 104043 145541 2 2 0 88213 78888 1 Summary : - Instance 3 has spent about 5s since db startup did 19 remaster operations - Remaster operations is executed in steps like: QUIESCE, FREEZE , CLEANUP, REPLAY, FIXWRITE, SYNC - Instance 3 has sent 104043 locks and received 145541 locks - According to lmon strace trace the whole operation is done in chunks
LMD, LMOM trace files for a remastering operation
LMON trace : Remastering operation is done in chunks starts at 09:40:21 and finishes at 09:40:28 2013-09-18 09:40:21.276424 : DRM(16) resources quiesced [0-4095], rescount 1348 .. 2013-09-18 09:40:21.350448 : DRM(16) resources quiesced [28672-32767], rescount 1358 End DRM(16) *** 2013-09-18 09:40:28.504 * DRM RCFG called (swin 1) CGS recovery timeout = 85 sec Begin DRM(17) (swin 1) object id 87478.0, objscan 1.1, dissolve affinity from instance 3 Total pkey count in this drm 1 * drm quiesce 2013-09-18 09:40:28.507186 : DRM(17) resources quiesced [0-32767], rescount 24165 2013-09-18 09:40:28.507235 : DRM(17) local converts quiesced [0-32767], lockcount 0, bucket 0 * drm sync 1 * drm freeze * DRM(17) window 1, drm freeze complete. * drm cleanup * drm sync 2 * drm replay * drm sync 3 * drm fix writes * drm sync 4 * drm end End DRM(17) LMD tracefile: *** 2013-09-18 09:40:28.422 * received DRM start msg from 3 (cnt 1, last 1, rmno 17) Rcvd DRM(17) AFFINITY Dissolve pkey 87478.0 from 3 oscan 32767.1 ftd (30) received from node 2 (13 0.0/0.0) ftd (30) received from node 3 (13 0.30/0.0) all ftds received
Manual DRM remastering using oradebug lkdebug -m pkey
Node grac41 =========== SQL> create table obj as select * from sys.obj$; SQL> select object_id,owner,object_type from dba_objects where object_name='OBJ' and object_type='TABLE'; OBJECT_ID OWNER OBJECT_TYPE ---------- ------------------------------ ------------------- 87460 SCOTT TABLE SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID= ( select data_object_id from dba_objects where object_name='OBJ' and object_type='TABLE' ); no rows selected Go to node grac43 and manually master the object there.. Node grac43 =========== SQL> oradebug setmypid Statement processed. SQL> oradebug lkdebug -m pkey 87460 Statement processed. SQL> select * from V$GCSPFMASTER_INFO where data_object_id=80283; FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87460 Affinity 2 32767 1 Change back to master to original node Node grac41 ============ SQL> oradebug setmypid Statement processed. SQL> oradebug lkdebug -m pkey 87460 Statement processed. SQL> select * from V$GCSPFMASTER_INFO where data_object_id=87460; FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87460 Affinity 0 2 2 Remaster_cnt is correctly set to 2 and CURRENT_MASTER has changed from 2 to 0.
Dynamic Resource Management processing due to Resource Affinity
Setup: Create a large table on grac41 and remaster this object to grac41 ( cr_chp21.sql ) set linesize 132 connect scott/tiger@grac41 set echo on drop table obj; create table obj as select * from sys.obj$; insert into obj ( select * from obj ); insert into obj ( select * from obj ); insert into obj ( select * from obj ); insert into obj ( select * from obj ); insert into obj ( select * from obj ); commit; connect sys/sys@grac41 as sysdba REM REM Create a manual master reference on grac41 oradebug setmypid oradebug lkdebug -m pkey &&obj_key Verify GCS settting Parameter Instance Description -------------------- ---------- ---------------------------------------- _gc_policy_minimum 1500 dynamic object policy minimum activity per minute _gc_policy_time 10 how often to make object policy decisions in minutes Lower this values: SQL> alter system set "_gc_policy_minimum" = 10 scope=spfile; SQL> alter system set "_gc_policy_time" = 1 scope=spfile Restart database $ srvctl stop database -d grac4 $ srvctl start database -d grac4 $ srvctl status database -d grac4 Verify gc_policy parameters on all nodes ( $ source all.csh @check_param ) Parameter Instance Description -------------------- ---------- ---------------------------------------- _gc_policy_minimum 20 dynamic object policy minimum activity per minute _gc_policy_time 4 how often to make object policy decisions in minutes -> This means every 4 minutes object policy decisions is done On grac1: SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87478 Affinity 0 32767 1 --> Here we know the master is instance grac41 ( instance_id: 0) and Remaster Count is 1. - Connect to grac43 and run an long insert to trigger a Remastering Event - monitor x$object_policy_statistics on grac43 SQL> select object, node, sopens, xopens, xfers from x$object_policy_statistics where object= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); OBJECT NODE SOPENS XOPENS XFERS ---------- ---------- ---------- ---------- ---------- 87478 3 0 3817 1195 SQL> select object, node, sopens, xopens, xfers from x$object_policy_statistics where object= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); OBJECT NODE SOPENS XOPENS XFERS ---------- ---------- ---------- ---------- ---------- 87478 3 0 11695 82 After a while we get SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87478 Affinity 2 0 2 --> Remastering has occured as Master switched from instance_id 0 to instance_id 2 . SQL> select object, node, sopens, xopens, xfers from x$object_policy_statistics where object= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); OBJECT NODE SOPENS XOPENS XFERS ---------- ---------- ---------- ---------- ---------- 87478 3 595 21360 820 < 20 ( default 1500 ) Verify history by quering from gv$policy_history SQL> select * from gv$policy_history; INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE ---------- -------------------- -------------- ---------------------- -------------------- 1 initiate_affinity 87478 1 09/17/2013 18:28:24 1 push_affinity 87478 3 09/17/2013 19:42:31
Remastering due to instance crash
Check current Master: SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87495 Affinity 2 0 5 Crash this instance: $ srvctl stop instance -d grac4 -i grac43 -o abort Review V$GCSPFMASTER_INFO SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID= ( select object_id pe from dba_objects where object_name='OBJ' and object_type='TABLE' and owner='SCOTT'); FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT ---------- -------------- ----------- -------------- --------------- ------------ 0 87495 Affinity 1 2 6
DRM Tracing 10430
DRM Tracing 10430 event can be turned ON dynamically ( use level 1 or level 8 ) SQL> alter system set events '10430 trace name context forever, level 8'; System altered. Disable Event: SQL> alter system set events '10430 trace name context off';