Undo Latch Contention

Here is the SQL to find sessions causing UNDO Latch Contention:

select

   swh.seq#,

   sess.sid,

   sess.username username,

   swh.event     event,

   swh.p1,

   swh.p2

from

   v$session               sess,

   v$session_wait_history  swh

where

   sess.sid = 798

and

   sess.sid = swh.sid

order by

   swh.seq#;

Sample Output:

     SEQ#     SID      USERNAME        EVENT                                                  P1                      P2
---------- -------    --------------- ------------------------------------   --------------       ----------
         1        798     ORCL               latch free                                              5.0440E+17        127
         2        798     ORCL               latch free                                              5.0440E+17        127
         3        798     ORCL               latch free                                              5.0440E+17        127
         4        798     ORCL               latch free                                              5.0440E+17        127
         5        798     ORCL               latch: cache buffers chains           5.0440E+17        122
   


5 rows selected.

To find list of objects accessed by a database session

Here is a simple query that shows all the objects accessed by a database:

SELECT sid,
       owner,
       TYPE,
       object
  FROM v$access
 WHERE sid = 'session_id';

Ex:

SELECT sid,

       owner,

       TYPE,

       object

  FROM v$access

 WHERE sid = '769';

Temporary Tablespace Space Monitoring

Here are some quick SQL's to monitor the usage of TEMP Tablespace:

Space Usage of TEMP Tablespace:

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

Temp Space Usage by Session:

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

 
Temp Space Usage by Statement:
 

SELECT S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

Waits Related to Initialization Parameters

Wait Problem: Potential Fix
Free buffer: Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code
Buffer busy:  Segment Header — Add freelists or freelist groups or use ASSM
Buffer busy:  Data Block — Separate hot data; use reverse key indexes; small block sizes
Buffer busy:  Data Block — Increase initrans and/or maxtrans
Buffer busy:  Undo Header —Use automatic undo management
Buffer busy:  Undo Block — Commit more; use automatic undo management
Latch free:  Investigate the detail (listing in next table of this chapter for fixes)
Log buffer space: Increase the log buffer; use faster disks for the redo logs
Scattered read: Indicates many full table scans — tune the code; cache small tables
Sequential read: Indicates many index reads — tune the code (especially joins)
Write complete waits: Adds database writers; checkpoint more often; buffer cache too small

Latch Problem: Potential Fix

Library cache:  Use bind variables; adjust the shared_pool_size
Shared pool:  Use bind variables; adjust the shared_pool_size
Row cache objects: Increase the shared pool. This is not a common problem.
Cache buffers chain:  If you get this latch wait, it means you need to reduce logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning
a block highly accessed). Cache buffer lru chain latch contention can be resolved by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache. You should adjust DB_BLOCK_BUFFERS, and possible DB_BLOCK_SIZE. Multiple buffer pools can help
reduce contention on this latch. You can create additional cache buffer lru chain latches by adjusting the configuration parameter DB_BLOCK_LRU_LATCHES. You may be able to reduce the load on the cache buffer chain latches by increasing the configuration parameter. _DB_BLOCK_HASH_BUCKETS may need to be increased or set to a prime number (in pre-9i versions).

Library Cache SQL

The following query uses the V$LIBRARYCACHE view to examine the reload ratio in the library cache:

select Sum(Pins) "Hits",

Sum(Reloads) "Misses",

((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"

from V$LibraryCache;