EVENT 10051:"trace OPI calls"

Error:  ORA 10051
Text:   trace OPI calls
——————————————————————————-
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.

Event 10051 allows you to track OPI calls on the server side.
This can be useful to home in on what sequence of events lead
to a problem. It complements SQL*Net trace and <Event:10046>
trace. You can quickly see where FAST UPI etc.. is in use.

Levels:    The event is just either on or off.

Output: The output is simply of the form:

OPI CALL: type= 2 argc= 2 cursor=  0 name=OPEN

where:    type     = the OPI call type (program interface function call)
argc     = Argument count
cursor     = the cursor number the call is being made against
name       = description of the program interface function call.

Articles:
Interpreting DUMP LOGFILE Output                      <Note:29726.1>

EVENT 10235:"check memory manager internal structures"

Event:10235                     
~~~~~~~~~~~ 
Version/Use: 
  7.0 - 10.1.X   Check memory manager internal structures. 
  7.0 - 10.1.X "Check memory manager internal structures" 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  NOTE: Events should NEVER be set by customers unless advised to do so by

      Oracle Support Services. Read [NOTE:75713.1] before setting any event. 
Summary Syntax: 
~~~~~~~~~~~~~~~ 
  EVENT="10235 trace name context forever,  level LL" 
  (Always comment exactly when and why this event is being set) 
   ** IMPORTANT: Do **NOT** use ALTER SESSION SET EVENTS or ORADEBUG 
                syntax to set this event in sessions. This can cause 
                lots of ORA-600 errors against SGA heaps as not all  
                sessions using the SGA heaps will be using the same event 
                level. This applies to ALL levels except level 65536. 
Levels: 
~~~~~~~ 
  The event being set at all causes certain heap checks to be performed. 
   ***  WARNING *********************************************************** 
    ***   This event should only EVER be set at the request of Oracle Support. 
    ***   It can impact performance on most types of system. 
    ***   Level 2 and above can impact latch contention. 
    ***   Level 3 and above can have a *SEVERE* impact on performance. 
  ************************************************************************ 
  The bottom 3 bits of the level cause the following checks to occur: 
     ~~~~~         ~~~~~~~~~~~~ 
     Level         Description 
     ~~~~~         ~~~~~~~~~~~~ 
        1             Fast check on heap free (kghfrh) 
        2             Do 1 AND fill memory with junk on alloc / free 
        3             Do 2 AND ensure the chunk belongs to given heap on free 
        4             Do 3 AND make permanent chunks freeable so they can  
                      also be checked 
                       This level can give rise to increased memory use 
                       and can trigger false ORA-4030 and false ORA-4031 
                       errors. 
 
  Oracle 9205 onwards only: 
    65536             This is introduced by the diagnostic enhancement in 
                      bug 3293155. It is a totally independent bit setting 
                      which has minimal impact on performance (unless ORed  
                      with other levels). When this is set Oracle tries to 
                      keep comments with "permanent" memory allocations 
                      which can be useful for memory leak problems if the 
                      leaked memory appears to be a leak of "perm" memory. 
                      This level can be set/unset dynamically but will only  
                      store comments in "perm" memory allocated when the  
                      event is set. 
 
  There are additional values which Oracle Support can use. 
 
Description/Steps: 
~~~~~~~~~~~~~~~~~~ 
  This event may be used to try to catch HEAP corruption problems closer  
  to when they occur.  Typically level 12 is required to get close to the 
  corruption but this can impact performance too much to be useful. 
 
Example Output / Interpreting Output: 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  The event should cause an ORA-600 and heapdump to be produced if an 
  error is detected.  
Related: 
~~~~~~~~ 
 

EVENT:10052 "Stop SMON from cleaning up obj$"

Text:   Stop SMON from cleaning up obj$
——————————————————————————-

NOTE:
Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. You must read <Note:75713.1> before setting ANY
event on a system.

Usage:
~~~~~~
To disable cleanup of OBJ$ by SMON set this event in the init.ora file
thus:

EVENT=”10052 trace name context forever”

Explanation:
~~~~~~~~~~~~
Setting event 10052 stops SMON trying to clean up OBJ$.

The implication of this is that OBJ$ will grow with entries for
non-existent objects, eg: objects which have been dropped and
object entries created for negative dependency reasons.

The usual reason for setting this event is to prevent SMON from
crashing / spinning in the OBJ$ cleanup code whilst diagnostics
or further investigations are performed. It is also sometimes
used in Parallel Server environments so that OBJ$ cleanup is
only performed on selected nodes.

@   The routine it disables is <Function:kqlclo> (KQL Clean Obj$) which scans
@   obj$ for objects marked as non-existent and then deletes them if
@   they are not child dependencies of anything.

Know more about Oracle Latches

Two purposes of latches were:Serialize Execution,Serialize Access

Match the characteristics with latches and enqueues.

Several modes of access                   Enqueue
FIFO queue acquisition                    Enqueue
Mostly exclusive access                   Latch
Non-deterministic acquisition             Latch
No atomicity                              Enqueue
Atomicity                                 Latch

Most latches have a level between 0 and 8.

Usually a process can request a latch X only:After obtaining latch Y and if level X > level Y.
Remember that the level of X, which is child latch, must be greater than level Y, which is most likely a parent latch.

The structure for parent and child latches can be seen by querying X$KSLLT .
There is one KSLLT structure for each parent and child latch in an instance.

The characteristic of latch acquisition in NO-WAIT mode is:
One attempt to get a latch, no spin or sleep .
This is typically for latches where there are many of the same type. The process will attempt to acquire another latch.

The sleep time on latches approximately doubles with each wait until upper limit is reached. This is an exponential backoff.

The statistic waiters_woken counts the number of times a posting for a latch has occurred.
It can be found in V$LATCH.This view contains aggregate statistics for latches

PMON invokes a clean up function for releasing latches if the holding process dies.PMON will initiate the clean up function.

_MAX_SLEEP_HOLDING_LATCH The maximum exponential backoff when waiting for a latch and already holding anothers
_MAX_EXPONENTIAL_SLEEP Controls the maximum time a process has to sleep before requesting the latch again
_SPIN_COUNT Designates the number of times a process will spin while trying to acquire a latchs

V$SESSION_WAIT dynamic performance view gives real time information regarding what sessions are currently waiting or have just waited for an event.

When diagnosing latch contention, we should typically look for Latches with the most impact.

We should use below formula  to calculate the total cost of latch spinning:

_spin_count * sleeps/misses

Statistics from V$LATCH with their associated description:

IMMEDIATE_MISSES Number of times the fast get failed when requested in “no-wait” mode

MISSES Number of times the fast get call failed when requested in “willing-to-wait” modes

SPIN_GETS Number of times where the fast get failed but the latch was acquired during the first spin before having to yield to the CPU

GETS Number of times the latch was obtained when requested in “willing-to-wait” mode

SLEEPi Number of times a “willing-to-wait” request had to sleep I times

IMMEDIATE_GETS Number of times the latch was obtained when requested in “no-wait” mode

We can set event 10005 to perform latch tracing, or oradebug dump latches 3 ;

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
[oracle@rh2 ~]$ oerr ora 10005
10005, 00000, "trace latch operations for debugging"
// *Document: NO
// *Cause:
// *Action: Enable tracing for various latch operations
// *Comment:
//    level 1 - trace latch gets and frees
//    level 4 - trace multiple posts by processes when latch is freed
//
Wait and latch (KSL) layer (10005)
We can set events 10005 (trace latch gets and frees) and 10073 (have PMON
dump info before latch cleanup).  Level does not matter for these events.
We can also set event="600 trace name LATCHES level 1" to dump latch info
when PMON hits the error (I'm not so sure this one is going to work).
event = "600 trace name latches level 10"
event = "10005 trace name context forever, level 1"
10005 event has been removed above 9i , try dump KSTDUMPCURPROC 1
SQL> oradebug setmypid;
SQL> oradebug unlimit;
SQL> oradebug dump KSTDUMPCURPROC 1
SQL> oradebug tracefile_name;
KSTDUMP: In-memory trace dump
TIME(usecs):SEQ# ORAPID   SID EVENT  OP DATA
========================================================================
D9396B13:000044F8    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 146/0x92 0/0x0
D9573C3D:00004568    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 146/0x92 0/0x0 time=1954087
D9573C4F:00004569    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 147/0x93 0/0x0
D9750D82:000045FC    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 147/0x93 0/0x0 time=1954097
D9750D8F:000045FD    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 148/0x94 0/0x0
D992DEA2:00004639    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 148/0x94 0/0x0 time=1954065
D992DEB2:0000463A    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 149/0x95 0/0x0
D9B0AFD8:000046A9    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 149/0x95 0/0x0 time=1954084
D9B0AFEB:000046AA    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 150/0x96 0/0x0
D9CE8110:00004742    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 150/0x96 0/0x0 time=1954083
D9CE811E:00004743    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 151/0x97 0/0x0
D9EC5246:00004790    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 151/0x97 0/0x0 time=1954086
D9ECA80D:00004792    14     0 10280   8 kst: process state object about to be deleted
E308018E:00006780    14     0 10280   1 kst: process state object created on 06-30 22:11:24.211
E308018F:00006781    14     0 10280   3 kst: process info: ospid=10380 pso_num=14 pso_serial#=2
E30801D0:00006782    14     0 10420   1 kso: new process: pid=10380 (legacy spawn)
E4ACF279:00006D4A    14     0 10280   8 kst: process state object about to be deleted
E4B2CD42:00006D4B    14     0 10280   1 kst: process state object created on 06-30 22:11:52.853
E4B2CD43:00006D4C    14     0 10280   3 kst: process info: ospid=10382 pso_num=14 pso_serial#=3
E4B2CDA3:00006D4D    14     0 10420   1 kso: new process: pid=10382 (legacy spawn)
KSTDUMP: End of in-memory trace dump
**** LGWR trace
05A46022:0000DEB0     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05A46025:0000DEB1     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05A46026:0000DEB2     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05A46027:0000DEB3     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05A46029:0000DEB4     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05A4602A:0000DEB5     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05A4602A:0000DEB6     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05A4602B:0000DEB7     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05A4602E:0000DEB8     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05D117DB:0000DF45     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930603
05D117E6:0000DF46     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D117E7:0000DF47     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D117E8:0000DF48     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D117ED:0000DF49     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05D117EE:0000DF4A     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05D117FE:0000DF4B     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05D117FF:0000DF4C     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05D11803:0000DF4D     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05D11804:0000DF4E     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05D11805:0000DF4F     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05D11807:0000DF50     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05D11808:0000DF51     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D11808:0000DF52     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D11809:0000DF53     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D1180C:0000DF54     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05FDCFC0:0000DFE1     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930610
05FDCFCB:0000DFE2     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05FDCFCC:0000DFE3     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05FDCFCD:0000DFE4     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05FDCFD1:0000DFE5     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05FDCFD2:0000DFE6     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05FDCFEF:0000DFE7     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05FDCFF0:0000DFE8     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05FDCFF3:0000DFE9     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05FDCFF4:0000DFEA     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05FDCFF5:0000DFEB     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05FDCFF7:0000DFEC     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]

Know more about Buffer Cache and Latch

We can examine X$BH table to obtain buffer header information,The BH stands for buffer header.

Structures that maintain a list of data buffer headers are called:Hash buckets.

Hash buckets are grouped by relative DBA and class number.
Hash chains list data buffer headers in one hash bucket.

Cache Recycle Pool For randomly accessed large tables

Default Pool For normally accessed tables

Keep Pool for frequently updated small tables

Set DB_BLOCK_LRU_LATCHES parameter to create multiple LRU lists.This parameter sets the number of latches,
and it is best used on a multi-CPU system.

DB_BLOCK_BUFFERS sets the number of buffers to allocate in the buffer cache.
DB_BLOCK_LRU_EXTENDED_STATISTICS is obsolete in Oracle8i.
DB_WRITER_PROCESSES sets the number of database writer processes.

Cache buffers LRU chain protects LRU lists.

The total number of working sets per instance is defined by DB_BLOCK_LRU_LATCHES. There is one latch per set.

The number of DBWR processes cannot be greater than the number of working sets.

Oracle get buffers as below steps:

  1. get a buffer descriptor
  2. specify a mode in which a buffer should be obtained
  3. scan the appropriate hash chain
  4. find the appropriate buffer in the chain or read from disk
  5. attach a state object to the buffer header

Buffers are initially hashed to LRU_AUX.This contains buffers that are candidates for reuse.
LRU_MAIN List houses buffers that are in use (pinned or dirty).
LRU_W List is the write list for dirty buffers.
LRU_XR List is the reuse range list for buffers that are to be written for reuse.

DBWR processes operate on working sets that are assigned to them in a cyclical manner.

There are three reasons may cause DBWR write buffers to disk:

  • To provide checkpoints
  • To do free requests
  • To do ping writes(obsolete)

The write batch size controls the number of asynchronous writes slots allocated to each DBWR.The write batch size controls the number of asynchronous writes slots allocated to each DBWR.

Two Most important DBWR statistics: DBWR make free request,DBWR lru scans 。

Two circumstances where DBWR purges dirty buffers whose DBA falls between the lowest and highest DBA of the datafiles:

  • ALTER TABLESPACE…BEGIN BACKUP
  • Make a tablespace read only

Of the nine latches that protect the buffer cache,the three that are most important:

  • Cache buffers lru chain
  • Cache buffers chains
  • Checkpoint queue latch

each of the fixed tables below with its purpose:

  • X$KCBWAIT         Wait statistics by block class
  • X$KCBFWAIT      Wait statistics by file id
  • X$KCBBHS              DBWR histogram statistics
  • X$KCBWBPD         Buffer pool descriptors

A target buffer cache hit ratio is above 90%.
But 60% could be a valid hit ratio for a DSS or Data Warehouse application.

Two primary events that relate to the buffer cache:

  • Buffer busy waits
  • Free buffer waits

We can use the views and table:V$SYSTEM_EVENT, V$WAITSTAT, and X$KCBFWAIT to determine the reason and block class for buffer busy waits.

Know More About Libarary Cache and Latches

Stored objects And Transient objects are stored in the library cache, neither Temporary objects nor Permanent objects.

The library cache is structured as a Hash table .But Library cache objects are composed of data heaps.
Oracle access hash tables through hash buckets.

SHARED_POOL_SIZE does the Oracle server determine the number of hash tables. When the the shared pool is larger, it can accommodate more object handles.

_KGL_BUCKET_CNT can be used to set the number of hash buckets and deprecated.
The minimum size of the hash table is 509 buckets in ORACLE 7.

For performance reasons, when the linked list has an average depth of 2 the Oracle server doubles the size of the hash table.

Locks manage concurrency whereas pins manage cache coherency.

There are two valid library cache pin modes: Share and Exclusive

An object handle is protected by a latch determined by the bucket it hashes into using the formula latch# = mod(bucket#, #latches) .

The hidden parameter _KGL_LATCH_COUNT is used to determine the number of child latches.”The default value should be adequate, but if contention for the library cache latch cant be resolved, it may be advisable to increase this value. The default value for _KGL_LATCH_COUNT is the next prime number after CPU_COUNT. This value cannot exceed 66 (See: <>). ”

Begin 10.2.0.2, mutex take place cursor pin latch.To avoid using Mutex latches, you can set _kks_use_mutex_pin=false .

CURSOR_SPACE_FOR_TIME has been deprecated in 10.2.0.5 and 11.1.0.7.

CURSOR_SPACE_FOR_TIME was originally introduced to try and help reduce latch contention by keeping cursors in memory in the SGA rather than allowing their data to be flushed from the shared pool. Such latch contention is avoided in current releases by the use of cursor mutexes and so this parameter is no longer relevant.

v$open_cursor lists kinds of library cache lock,x$kgllk – Details about Object locks

v$open_cursor
select inst_id,
kgllkuse,
kgllksnm,
user_name,
kglhdpar,
kglnahsh,
kgllksqlid,
kglnaobj,
kgllkest,
decode(kgllkexc, 0, to_number(NULL), kgllkexc),
kgllkctp
from x$kgllk
where kglhdnsp = 0
and kglhdpar != kgllkhdl
SQL> select distinct kgllkctp from x$kgllk ;
KGLLKCTP
--------------------------------------------
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED

Oracle内部视图:X$BH

X$BH Fixed Table Buffer Cache Diagram
Column     Type Description
~~~~~~     ~~~~~ ~~~~~~~~~~~
ADDR        RAW(4) Hex address of the Buffer Header.
INDX        NUMBER Buffer Header number
BUF#        NUMBER
HLADDR      RAW(4) Hash Chain Latch Address
See . ADDR
LRU_FLAG    NUMBER     8.1+ LRU flag
KCBBHLDF   0x01     8.1  LRU Dump Flag used in debug print routine
KCBBHLMT   0x02     8.1  moved to tail of lru (for extended stats)
KCBBHLAL   0x04     8.1  on auxiliary list
KCBBHLHB   0x08     8.1  hot buffer - not in cold portion of lru
FLAG        NUMBER
KCBBHFBD   0x00001       buffer dirty
KCBBHFAM   0x00002  7.3  about to modify; try not to start io
KCBBHFAM   0x00002  8.0  about to modify; try not to start io
KCBBHNAC   0x00002  8.1  notify dbwr after change
KCBBHFMS   0x00004       modification started, no new writes
KCBBHFBL   0x00008       block logged
KCBBHFTD   0x00010       temporary data - no redo for changes
KCBBHFBW   0x00020       being written; can't modify
KCBBHFWW   0x00040       waiting for write to finish
KCBBHFCK   0x00080  7.3  checkpoint asap
0x00080  8.0  not used
KCBBHFMW   0x00080  8.1  multiple waiters when gc lock acquired
KCBBHFRR   0x00100       recovery reading, do not reuse, being read
KCBBHFUL   0x00200       unlink from lock element - make non-current
KCBBHFDG   0x00400       write block & stop using for lock down grade
KCBBHFCW   0x00800       write block for cross instance call
KCBBHFCR   0x01000       reading from disk into KCBBHCR buffer
KCBBHFGC   0x02000       has been gotten in current mode
KCBBHFST   0x04000       stale - unused CR buf made from current
0x08000  7.3  Not used.
KCBBHFDP   0x08000  8.0  deferred ping
KCBBHFDP   0x08000  8.1  deferred ping
KCBBHFDA   0x10000       Direct Access to buffer contents
KCBBHFHD   0x20000       Hash chain Dump used in debug print routine
KCBBHFIR   0x40000       Ignore Redo for instance recovery
KCBBHFSQ   0x80000       sequential scan only flag
KCBBHFNW  0x100000  7.3  Set to indicate a buffer that is NEW
0x100000  8.0  Not used
KCBBHFBP  0x100000  8.1  Indicates that buffer was prefetched
KCBBHFRW  0x200000  7.3  re-write if being written (sort)
0x200000  8.0  Not used
KCBBHFFW  0x200000  8.1  Buffer has been written once
KCBBHFFB  0x400000       buffer is "logically" flushed
KCBBHFRS  0x800000       ReSilvered already - do not redirty
KCBBHFKW 0x1000000  7.3  ckpt writing flag to avoid rescan */
0x1000000  8.0  Not used
KCBBHDRC 0x1000000  8.1  buffer is nocache
0x2000000  7.3  Not used
KCBBHFRG 0x2000000  8.0  Redo Generated since block read
KCBBHFRG 0x2000000  8.1  Redo Generated since block read
KCBBHFWS 0x10000000 8.0  Skipped write for checkpoint.
KCBBHFDB 0x20000000 8.1  buffer is directly from a foreign DB
KCBBHFAW 0x40000000 8.0  Flush after writing
KCBBHFAW 0x40000000 8.1  Flush after writing
TS#         NUMBER 8.X Tablespace number
DBARFIL     NUMBER 8.X Relative file number of block
DBAFIL      NUMBER 7.3 File number of block
DBABLK      NUMBER Block number of block
CLASS       NUMBER See Note 33434.1
1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block'                -- since 10g
STATE       NUMBER
KCBBHFREE         0       buffer free
KCBBHEXLCUR       1       buffer current (and if DFS locked X)
KCBBHSHRCUR       2       buffer current (and if DFS locked S)
KCBBHCR           3       buffer consistant read
KCBBHREADING      4       Being read
KCBBHMRECOVERY    5       media recovery (current & special)
KCBBHIRECOVERY    6       Instance recovery (somewhat special)
MODE_HELD   NUMBER    Mode buffer held in (MODE pre 7.3)
0=KCBMNULL, KCBMSHARE, KCBMEXCL
CHANGES     NUMBER
CSTATE      NUMBER
X_TO_NULL   NUMBER Count of PINGS out (OPS)
DIRTY_QUEUE NUMBER You wont normally see buffers on the LRUW
LE_ADDR     RAW(4) Lock Element address (OPS)
SET_DS      RAW(4) Buffer cache set this buffer is under
OBJ         NUMBER       Data object number
TCH     NUMBER 8.1 Touch Count
TIM     NUMBER 8.1 Touch Time
BA          RAW(4)
CR_SCN_BAS  NUMBER       Consistent Read SCN base
CR_SCN_WRP  NUMBER       Consistent Read SCN wrap
CR_XID_USN  NUMBER CR XID Undo segment no
CR_XID_SLT  NUMBER CR XID slot
CR_XID_SQN  NUMBER CR XID Sequence
CR_UBA_FIL  NUMBER CR UBA file
CR_UBA_BLK  NUMBER CR UBA Block
CR_UBA_SEQ  NUMBER CR UBA sequence
CR_UBA_REC  NUMBER CR UBA record
CR_SFL      NUMBER
LRBA_SEQ    NUMBER } Lowest RBA needed to recover block in cache
LRBA_BNO    NUMBER }
LRBA_BOF    NUMBER }
HRBA_SEQ    NUMBER } Redo RBA to be flushed BEFORE this block
HRBA_BNO    NUMBER } can be written out
HRBA_BOF    NUMBER       }
RRBA_SEQ    NUMBER } Block recovery RBA
RRBA_BNO    NUMBER }
RRBA_BOF    NUMBER }
NXT_HASH    NUMBER Next buffer on this hash chain
PRV_HASH    NUMBER Previous buffer on this hash chain
NXT_LRU     NUMBER Next buffer on the LRU
PRV_LRU     NUMBER Previous buffer on the LRU
US_NXT      RAW(4)
US_PRV      RAW(4)
WA_NXT      RAW(4)
WA_PRV      RAW(4)
ACC         RAW(4)
MOD         RAW(4)

Script:when transaction will finish rollback

-------------------------------------------------------------------------------
--
-- Script:	rolling_back.sql
-- Purpose:	to predict when transactions will finish rolling back
-- For:		9.0+
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings
set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt
declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe  x,
sys.v_$transaction  t,
sys.v_$session  s
where
x.inst_id = userenv('Instance') and
x.ktuxesta = 'ACTIVE' and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name  varchar2(30);
xid_usn    number;
xid_slot   number;
xid_sqn    number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv('Instance') and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn  || '.' ||
xid_slot || '.' ||
xid_sqn  ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;
/
prompt
@restore_sqlplus_settings

VIEW: X$KTUXE – Transaction Entry (table)

View:   X$KTUXE
[K]ernel [T]ransaction [U]ndo
Transa[x]tion [E]ntry (table)
This view is very useful as it gives an indication of the state
of the transaction tables in the rollback segment headers.
The information here can be used to see the state of transactions
requiring transaction recovery do not show in <View:V$TRANSACTION>
<Event:10013> may be useful to trace transaction recovery.
Column          Type               Description
--------        ----               --------
ADDR            RAW(4|8)           address of this row/entry in the array or SGA
INDX            NUMBER             index number of this row in the fixed table array
INST_ID         NUMBER       8.x   oracle instance number
Transaction ID
KTUXEUSN        NUMBER             undo seg number
KUSNOLTP UB2MAXVAL           is no-undo xac
KTUXESLT        NUMBER             slot number
KSLTINV  UB2MAXVAL           not a valid slot num
KTUXESQN        NUMBER             wrap number, is savept # at start if no-undo xac
position within transaction
KTUXERDBF       NUMBER             relative File
KTUXERDBB       NUMBER             relative Block
KTUXESCNB       NUMBER             SCN base for prepare/commit
KTUXESCNW       NUMBER             SCN wrap for prepare/commit
KTUXESTA        VARCHAR2(16)       Transaction Status
KTUXECFL        VARCHAR2(24)       Transaction flags
KTUXEUEL        NUMBER             Used for extent of tx and link to commit list
Distributed tx: collecting dba and undo bk to start retrieving collecting info rec
KTUXEDDBF       NUMBER             relative file
KTUXEDDBB       NUMBER             relative dba
Parent transaction id
KTUXEPUSN       NUMBER       8.x   undo seg number
KUSNOLTP UB2MAXVAL           is no-undo xac
KTUXEPSLT       NUMBER       8.x   slot number
KSLTINV  UB2MAXVAL           not a valid slot num
KTUXEPSQN       NUMBER       8.x   wrap number, is savept # at start if no-undo xac
position within transaction
KTUXESIZ        NUMBER       8.1  number of undo blocks used by the transaction
Notes:
To see any DEAD transactions for deferred transaction recovery
after startup:
select * from x$ktuxe where ktuxecfl='DEAD';

EVENT: 10500 "turn on traces for SMON"

Error:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
Cause:
Action:
Level:  <=5  trace instance recovery
> 5  trace posting of SMON
To set event 10500:
For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
event="10500 trace name context forever, level <value>"
c.  restart the database
For the SMON session:
Post the SMON process using oradbx (Oracle 7) or oradebug (Oracle 8).
For oradebug from server manager issue:
oradebug setospid <OS PID>
oradebug event 10500 trace name context forever, level <value>
For further information about oradebug Note 29786.1
oradbx   Note 28863.1
<value> is 'Level' as per above

沪ICP备14014813号

沪公网安备 31010802001379号