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

DBA Scripts - Very Useful - 8, 9. Full Table Scans, Data Dictionary Info

8. Full Table Scans

Here are some scripts related to Full Table Scans .

System Statistics (Table)

SYSTEM STATISTICS (TABLE) NOTES:

  Statistic Name - Name of the statistic

  Bytes - Size

  This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.

  Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.

  Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.

  Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.

  Table scans rows gotten reflect the cumulative number of rows read for full table scans.

  Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.

  Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.

  Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.

  Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.

select       NAME,

               VALUE

from        v$sysstat

where       NAME like '%table'

Process Table Scans

PROCESS TABLE SCAN NOTES:

  User Process - Name of user process

  Long Scans - Full scan of a table that has > 5 database blocks.

  Short Scans - Full scan of a table that has <= 5 database blocks.

  Row Retrieved - Cumulative number of rows read for full table scans.

select       ss.username||'('||se.sid||') ' "User Process",

               sum(decode(name,'table scans (short tables)',value)) "Short Scans",

               sum(decode(name,'table scans (long tables)', value)) "Long Scans",

               sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"

from        v$session ss,

               v$sesstat se,

               v$statname sn

where       se.statistic# = sn.statistic#

and          (name  like '%table scans (short tables)%'

or             name  like '%table scans (long tables)%'

or             name  like '%table scan rows gotten%')

and          se.sid = ss.sid

and          ss.username is not null

group       by ss.username||'('||se.sid||') '

Process Table Scans (Avg)

PROCESS TABLE SCAN (AVERAGE) NOTES:

  User Process - Name of user process

  Short Scans - Number of short scans (<= 5 blocks)

  Long Scans - Number of long scans (> 5 blocks)

  Rows Retrieved - Number of rows retrieved

  Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)

select       ss.username||'('||se.sid||') ' "User Process",

               sum(decode(name,'table scans (short tables)',value)) "Short Scans",

               sum(decode(name,'table scans (long tables)', value)) "Long Scans",

               sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",

round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"

from        v$session ss,

               v$sesstat se,

               v$statname sn

where       se.statistic# = sn.statistic#

and          (name  like '%table scans (short tables)%'

or             name  like '%table scans (long tables)%'

or             name  like '%table scan rows gotten%')

and          se.sid = ss.sid

and          ss.username is not null

group       by ss.username||'('||se.sid||') '

having sum(decode(name,'table scans (long tables)', value)) != 0

order by 3 desc

9. Data Dictionary Info

Here are some scripts related to Data Dictionary Info .

Dictionary Cache

DATA DICTIONARY CACHE NOTES:

  Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.

  Gets - Total number of requests for information on the data object.

  Getmisses - Number of data requests resulting in cache misses.

  % Cache Misses - Miss Ratio

  Count - Total number of entries in the cache

  Usage - Number of cache entries that contain valid data.

  This report shows the DC cache statistics (a part of the shared pool).

  Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.)

  Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer.

  If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file)

select       PARAMETER,

               GETS,

               GETMISSES,

               round(GETMISSES/GETS,2)*100 "% Cache Misses",

               COUNT,

               USAGE

from        v$rowcache

where       GETS > 0

order        by (GETMISSES/GETS)*100 desc

Latch Gets/Misses

LATCH GET AND MISS NOTES:

  Latch Name - Name of the latch

  Gets - Number of times obtained wait

  Misses - Number of time obtained with wait but failed first try

  Gets / Misses % - Ratio of misses to gets

  Immediate Gets - Number of times obtained with no wait

  Immediate Misses - Number of times failed to get with no wait

select       NAME,

               GETS,

               MISSES,

               round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",

               IMMEDIATE_GETS,

               IMMEDIATE_MISSES

from        v$latch

where       GETS != 0

or            IMMEDIATE_MISSES > 0

order        by ((GETS-MISSES) / GETS) desc