A closer look into DRM ( Dynamic Resource Management )

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                          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';

References:

Leave a Reply

Your email address will not be published. Required fields are marked *