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
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',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block'                -- since 10g
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)
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
LRBA_SEQ    NUMBER } Lowest RBA needed to recover block in cache
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
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)

oracleasm failed to createdisk on raw device


[root@vrh1 yum.repos.d]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting [ENTER] without typing an
answer will keep that current value.  Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:
[  OK  ]
/* configure没有出现错误 */
[root@vrh1 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/raw/raw3
Marking disk "VOL1" as an ASM disk:                        [FAILED]
/* 之后发现在块设备上创建磁盘就没有问题,想想也是如果用了ASMLIB就没必要再建成裸设备了 */
[root@vrh1 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb6
Marking disk "VOL1" as an ASM disk:                        [  OK  ]


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
set serveroutput on
set feedback off
prompt Looking for transactions that are rolling back ...
cursor tx is
sys.x$ktuxe  x,
sys.v_$transaction  t,
sys.v_$session  s
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;
open tx;
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
end if;
inst_id = userenv('Instance') and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
user_name ||
'''s transaction ' ||
xid_usn  || '.' ||
xid_slot || '.' ||
xid_sqn  ||
' will finish rolling back at approximately ' ||
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
end if;
end loop;
if user_name is null
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;

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

Skip Scanning of Indexes Advantages

What is skip scanning?


In prior releases, a composite index would be used to retrieve the records, if the index prefix (leading) column was included in the predicate of the statement. With Oracle9i, the optimizer can use a composite index even if the prefix column value is not known. The optimizer uses an algorithm called skip scanning to retrieve ROWIDs for values that do not use the prefix column.




How does a skip scan work?


During a skip scan, the B*-tree is probed for each distinct value in the prefix column. Under each prefix column value, the normal search algorithm takes over. The result is a series of searches through subsets of the index, each of which appears to result from a query using a specific value of the prefix column. However, with the skip scan, the value of the prefix column in each subset is obtained from the initial index probe rather than from the command predicate.

The optimizer uses the statistics to determine whether a skip scan retrieval would be more efficient than a full table scan, or other possible retrieval paths, when parsing SQL statements.


Suppose a composite index exists on the two columns, LANGUAGE andTERRITORY, with LANGUAGE as the prefix column. The data values stored in the underlying table result in the combinations of values in the table, as shown below. Each combination can occur multiple times in the table and the resulting index.
In previous releases without the skip scan algorithm, a query on a value in theTERRITORY column was forced to execute a full table scan or a fast full index scan. If the query were more common, then you might have needed to add a new index on the TERRITORY column alone. This new index, could negatively impact the performance of DML on the table.
The skip scan solution provides an improvement without the need for the second index. While not as fast as a direct index look up, the skip scan algorithm is faster than a full table scan in cases where the number of distinct values in the prefix column is relatively low.




Note: The skip scan feature cannot be turned on and off by the user. This feature is always on and is transparent to the users.


What are the advantages of skip scanning?

  • Skip scans reduce the need to add an index to support occasional queries that do not reference the prefix column of an existing index. This can be useful when high levels of DML activity is expected to degrade because of the existence of too many indexes used to support infrequent queries.
  • The algorithm is also valuable in the cases where there are no clear advantages as to which column to use as the prefix column in a composite index.
  • The prefix column should be the most discriminating, but also the most frequently referenced in queries. Sometimes, these two requirements are met by two different columns in a composite index, forcing a compromise or the use of multiple indexes. Skip scanning helps to overcome these problems.

Know more about DML

The change vectors as constructed by the transaction layer are used by the cache layer to apply the physical block changes.

Redo log ordering and recovery as well as read consistency are the fundamental purposes of SCNs.

The change vector:
Change vectors are built in the PGA process of the process modifying a block, have the DBA and SCN/SEQ of the block before the change, and only ever perform the change for one block.

The system commit number:
The SCN is a 6-byte structure consisting of the SCN base and SCN wrap.
Is a 6-byte structure consisting of the SCN base and SCN wrap, 4 bytes for the SCN base and 2 bytes for the SCN wrap

The SCN is used within the data block to reflect the committed version of the block.
There is only one global SCN generator for each database.
A large transaction is defined as a transaction that is participating in a parallel DML (PDML) operation or a distributed transaction.
One of the first checks for rollback segment allocation is whether the transaction is participating in a PDML or distributed transaction. These are used to determine the largest rollback segment to be allocated.

steps below to illustrate the order necessary to begin a transaction.

1.bind to the available rollback segment
2.allocate a slot in the transaction table
3.allocate an undo block to the rollback segment

When a free slot cannot be allocated in the transaction table, exclusive access is acquired in the rollback segment header.
Only when a free slot can be allocated in the transaction table do you gain exclusive access to the RBS header.

The system rollback segment will be used only for data for the system tablespace and data that is being created from recursive SQL calls generated by user data.

The search for the allocation of an undo block to a rollback segment will occur in:
Searching for an undo block to be allocated will begin in the current extent if the extent boundary has not been reached, in the next extent provided there are no uncommitted transactions, or in a newly allocated extent if the next extent has uncommitted data.

Rollback segments are shrunk either explicitly through the ’alter rollback segment … shrink to …’ command or implicitly by the SMON process. Implicit shrinkage can be disabled by:
PCTINCREASE has no affect on rollback segments. All extents will be sized by the setting of the next_extent storage attribute.
Setting the event 10512 will disable the implicit shrink operation performed by SMON.
Setting the initialization parameter ‘rbs_disable_shk’. There is no such parameter. Setting the event 10512 will disable the implicit shrinkage operation performed by SMON.

The Oracle server begins shrinking a rollback segment starting from the current extent +2

Rollback segment shrinkage will occur while optimal or minextents is not reached, there are at least two remaining extents, the extent is not busy, and the extent is not extent #0.

An extent is reused if all its blocks are inactive. RBS undo blocks are reused on a per extent basis. An undo block becomes inactive when the active transaction in the undo block commits.

correct order of Interested Transaction List (ITL) allocation.

1. Find an  Unused ITL
2. FInd ITL of oldest commited TX
3. DO a partial block cleanout that stops when an ITL becomes available
4. Extend ITL area

The variable portion of the transaction layer is dictated by the MAXTRANS parameter.
The INITRANS parameter dictates the number of ITL’s and hence the variable portion of the Transaction Layer.

An ITL must be acquired by any transaction wishing to modify a block. If an ITL is unavailable and there is no space left in the block, a transaction will wait.

A transaction that currently has an ITL within a block cannot update any row in the block if the required row is locked by a previous transaction. That transaction will wait on the TX enqueue.When attempting to update a row already held by a previous transaction, a transaction will wait on the TX enqueue in exclusive mode.

The free space credit field of the transaction layer maintains the space as freed for a given transaction, and that is returned on commit of the transaction.

When a transaction is unable to allocate an ITL within a data block for which it needs to modify a block, the transaction will:
That transaction will wait on the TX enqueue in share mode until an ITL becomes available within the block where the modification is required.

The Oracle server maintains the row header flag and lock byte (total 2 bytes) for the deleted row. It is necessary to keep these first two bytes to maintain the row lock. This is referred to as stub space.

The correct statistic Rollback changes – undo records applied is incremented for each link in the undo record chain.

The Data Block Address (DBA) in the Transaction Table slot indicates:
The beginning of the undo chain for the transaction  Correct! The DBA in the Transaction Table slot is the starting point for rolling back a transaction

the correct sequence of events at the COMMIT of a transaction.

1. FIND an scn value
2. update the transaction table slot with the scn
3. create a commit record in the redo log buffer
4. flush the redo log buffer to disk
5. release locks held on the rows and tables

The application of the undo to the data block is performed by the
The data layer ,and the transaction layer do not have the right to perform physical block-level changes.

EVENT:10211 check index block integrity

Event:10211                  See <Note:32969.1> for similar events / parameters
  7.0 - 8.1.7  Check data block integrity after each modification
  9.0+         This event is no longer valid. Use <Parameter:DB_BLOCK_CHECKING>
7.0 - 8.1.7 "Check data block integrity after each modification"
  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:
  This event should be set at instance level:
    EVENT="10211 trace name context forever, level 10"
  This event is either ON (level 1 or higher) or OFF (not set)
  Note that in 8.1.6 onwards you should use the <Parameter:DB_BLOCK_CHECKING>
  instead of this event.
  Event:10211 enables index block integrity checking.
  The causes the index layer to perform a number of checks on the contents
  of a index block. If the block is found to be corrupt in some way it is
  NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT checked
      when accessed for read.                                   
  Take care with this as if there are blocks in the database which
  are being accessed happily but have a minor corruption this will
  mark the block as corrupt.
  Once a block in an index is marked SOFTWARE CORRUPT it is best to
  rebuild the entire index.
        Releases with <Bug:792610> are best to keep this event disabled
        as an index corruption can lead to rollback segment corruption
        which is written to the redo stream.
  See <Note:32969.1> for more detail of various block checking features.
Example Output / Interpreting Output:
  This event will cause an ORA-600 error to be signalled if a corruption
  is detected after a data block has been modified. The exact content
  of the trace file depends on the ORA-600 and the action at the time
  of the corruption being noticed. Refer to the relevant ORA-600 article
  for the error which is signalled.
  Database block checking features                          <Note:32969.1>
  Table and Cluster block equivalents          <Event:10210> <Event:10212>

EVENT: 10060 dump predicates in optimizer (kko)

Text:   dump predicates in optimizer (kko)
        This is a special EVENT code.
        It should *NOT* be used unless explicitly requested by ST support.
        This event can be useful in conjunction with an explain plan
        to confirm which operations occur where in the execution plan.
        It dumps the predicates into a special table.
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE
        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
        - To enable tracing:
            ALTER session set events '10060 trace name context forever';
        - Run the statement to be traced
        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75
        - select * from KKOIPT_TABLE;
        - Best used in conjunction with <Event:10053> trace and an
          execution plan.
KKOIPT_TABLE.C2 output information
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;
Output: (from kkoipt_table)
         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"

EVENT:10218 dump uba of applied undo

@23-JAN-01,, Distribution justification: "Should not be used by
@ customers unless told to by support so making noncustomer-viewable." -
Event: 10218
Text:  dump uba of applied undo
   Events should NEVER be set by customers unless advised to do so by
   Oracle Support Services.  See <Note:75713.1> for more details.
   This event simply shows the undo block addresses for the undo
   applied during recovery.  This can be useful to see the progress of
   the rolling out of transactions, and to help identify where a rollback
   has gotten stuck.
Example output:
         uba: 80000ca.0d.12
         uba: 80000ca.0d.11
         uba: 80000ca.0d.10
         uba: 80000ca.0d.0f
         uba: 80000ca.0d.0e
         uba: 80000ca.0d.0d
         uba: 80000ca.0d.0c
         uba: 80000ca.0d.0b


沪公网安备 31010802001379号