DBA Scripts - Very Useful - 10, 11. Redo Log Buffer, Contention/Locking

10. Redo Log Buffer

Here are some scripts related to Redo Log Buffer .

Contention

REDO LOG CONTENTION NOTES:

  Redo Latch Name - Name of the latch

  Gets - Number of times gotten wait

  Misses - Number of times gotten wait but failed first try

  Sleeps - Number of times slept when wanted wait

  Immediate Gets - Number of times gotten without wait

  Immediate Misses - Number of times failed to get without wait

  Heavy access to the redo log buffer can result in contention for redo log prompt buffer latches.

select       NAME,

               GETS,

               MISSES,

               SLEEPS,

               IMMEDIATE_GETS,

               IMMEDIATE_MISSES

from        v$latch

where       NAME in ('redo allocation','redo copy')

Statistics

REDO BUFFER NOTES:

  Parameter - Name of the parameter

  Value - Current value for the parameter

select       NAME,

               VALUE

from        v$sysstat

where       NAME like 'redo%'                     

and          VALUE > 0

11. Contention/Locking

Here are some scripts related to Contention/Locking .

Lock Info

LOCK INFORMATION NOTES:

  OS User - Name of operating system user

  OS PID - Operating system process id

  Oracle User - Name of Oracle user

  Oracle ID - Oracle session id

  Lock Type - Type of lock

  Lock Held - Current lock held

  Lock Requested - Type of lock requested

  Status - Status of object (Blocking, Not Blocking, Global)

  Object Owner - Owner of the object

  Object Name - Name of the object

select       OS_USER_NAME os_user,

               PROCESS os_pid,

               ORACLE_USERNAME oracle_user,

               l.SID oracle_id,

               decode(TYPE,

                               'MR', 'Media Recovery',

                               'RT', 'Redo Thread',

                               'UN', 'User Name',

                               'TX', 'Transaction',

                               'TM', 'DML',

                               'UL', 'PL/SQL User Lock',

                               'DX', 'Distributed Xaction',

                               'CF', 'Control File',

                               'IS', 'Instance State',

                               'FS', 'File Set',

                               'IR', 'Instance Recovery',

                               'ST', 'Disk Space Transaction',

                               'TS', 'Temp Segment',

                               'IV', 'Library Cache Invalidation',

                               'LS', 'Log Start or Switch',

                               'RW', 'Row Wait',

                               'SQ', 'Sequence Number',

                               'TE', 'Extend Table',

                               'TT', 'Temp Table', type) lock_type,

               decode(LMODE,

                               0, 'None',

                               1, 'Null',

                               2, 'Row-S (SS)',

                               3, 'Row-X (SX)',

                               4, 'Share',

                               5, 'S/Row-X (SSX)',

                               6, 'Exclusive', lmode) lock_held,

               decode(REQUEST,

                               0, 'None',

                               1, 'Null',

                               2, 'Row-S (SS)',

                               3, 'Row-X (SX)',

                               4, 'Share',

                               5, 'S/Row-X (SSX)',

                               6, 'Exclusive', request) lock_requested,

               decode(BLOCK,

                              0, 'Not Blocking',

                               1, 'Blocking',

                               2, 'Global', block) status,

               OWNER,

               OBJECT_NAME

from        v$locked_object lo,

               dba_objects do,

               v$lock l

where       lo.OBJECT_ID = do.OBJECT_ID

AND     l.SID = lo.SESSION_ID

SQL Lock Info

SQL ASSOCIATED WITH LOCK NOTES:

  Oracle User - Name of the oracle user

  SID - Oracle session id

  Serial# - Serial# of the process

  Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)

  Held - Type of lock held

  Requested - Type of lock requested

  ID1 - Resource identifier #1

  ID2 - Resource identifier #2

  SQL - SQL statement

select       sn.USERNAME,

               m.SID,

               sn.SERIAL#,

               m.TYPE,

               decode(LMODE,

                               0, 'None',

                               1, 'Null',

                               2, 'Row-S (SS)',

                               3, 'Row-X (SX)',

                               4, 'Share',

                               5, 'S/Row-X (SSX)',

                               6, 'Exclusive') lock_type,

               decode(REQUEST,

                               0, 'None', 

                               1, 'Null',

                               2, 'Row-S (SS)',

                               3, 'Row-X (SX)', 

                               4, 'Share', 

                               5, 'S/Row-X (SSX)',

                               6, 'Exclusive') lock_requested,

               m.ID1,

               m.ID2,

               t.SQL_TEXT

from        v$session sn, 

               v$lock m , 

               v$sqltext t

where       t.ADDRESS = sn.SQL_ADDRESS 

and          t.HASH_VALUE = sn.SQL_HASH_VALUE 

and          ((sn.SID = m.SID and m.REQUEST != 0) 

or            (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in

        (select s.ID1, s.ID2 

         from               v$lock S 

         where             REQUEST != 0 

         and                s.ID1 = m.ID1 

         and                s.ID2 = m.ID2)))

order by sn.USERNAME, sn.SID, t.PIECE