Script:To Report Information on Indexes

Reports index fragmentation statistics:

==========
Script #1:
==========
SET ECHO off
REM NAME:   TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                     Index Fragmentation Statistic
REM
REM    index name        S_EMP_USERID_UK
REM    leaf rows deleted            0
REM    leaf rows in use            25
REM    index badness            0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr  = &&1
def name  = &&2
ttitle -
center 'Index Fragmentation Statistic'   skip 2
set heading off
col name                 newline
col lf_blk_rows          newline
col del_lf_rows          newline
col ibadness newline
validate index &ownr..&name;
select
'index name        '||name,
'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,
'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,
'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
index_stats
/
undef ownr
undef name
set verify on
==============
Sample Output:
==============
Index Fragmentation Statistic
index name                   S_EMP_USERID_UK
leaf rows deleted            0
leaf rows in use             25
index badness                0.000
==========
Script #2:
==========
SET ECHO off
REM NAME:   TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                Index Statistics
REM
REM    S_EMP_USERID_UK
REM    ----------------------------------------------------------
REM    height                          1
REM    blocks                          5
REM    del_lf_rows                     0
REM    del_lf_rows_len                 0
REM    distinct_keys                  25
REM    most_repeated_key               1
REM    btree_space                 1,876
REM    used_space                    447
REM    pct_used                       24
REM    rows_per_key                    1
REM    blks_gets_per_access            2
REM    lf_rows                        25            br_rows               0
REM    lf_blks                         1            br_blks               0
REM    lf_rows_len                   447            br_rows_len           0
REM    lf_blk_len                  1,876            br_blk_len            0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr        = &&1
def name        = &&2
ttitle -
center  'Index Statistics'  skip 2
set heading off
col name   newline
col headsep              newline
col height               newline
col blocks               newline
col lf_rows              newline
col lf_blks        	 newline
col lf_rows_len          newline
col lf_blk_len           newline
col br_rows              newline
col br_blks              newline
col br_rows_len          newline
col br_blk_len           newline
col del_lf_rows          newline
col del_lf_rows_len      newline
col distinct_keys        newline
col most_repeated_key    newline
col btree_space          newline
col used_space    	 newline
col pct_used             newline
col rows_per_key         newline
col blks_gets_per_access newline
validate index &ownr..&name;
select
name,
'----------------------------------------------------------'    headsep,
'height               '||to_char(height,     '999,999,990')     height,
'blocks               '||to_char(blocks,     '999,999,990')     blocks,
'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,
'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,
'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,
'used_space           '||to_char(used_space,'999,999,990')        used_space,
'pct_used                     '||to_char(pct_used,'990')          pct_used,
'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,
'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+
'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,
'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+
'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,
'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+
'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,
'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+
'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len
from
index_stats
/
undef ownr
undef name
set verify on
==============
Sample Output:
==============
Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height                          1
blocks                          5
del_lf_rows                     0
del_lf_rows_len                 0
distinct_keys                  	25
most_repeated_key               1
btree_space                 	1,876
used_space                    	447
pct_used                       	24
rows_per_key                    1
blks_gets_per_access            2
lf_rows                		25
br_rows                 	0
lf_blks				1
br_blks                 	0
lf_rows_len           		447
br_rows_len            		0
lf_blk_len          		1,876
br_blk_len              	0
==========
Script #3:
==========
SET ECHO off
REM NAME:   TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM             Index Keys Summary
REM
REM    Uniqueness                Index Name                    Column Name
REM    ---------- ----------------------------------------  ------------------
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID
REM
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner	= &&1
def tabname	= &&2
ttitle -
center  'Index Keys Summary'  skip 2
col uniq    format a10 heading 'Uniqueness'  justify c trunc
col indname format a40 heading 'Index Name'  justify c trunc
col colname format a25 heading 'Column Name' justify c trunc
break -
on indname skip 1 -
on uniq
select
ind.uniqueness                  uniq,
ind.owner||'.'||col.index_name  indname,
col.column_name                 colname
from
dba_ind_columns  col,
dba_indexes      ind
where
ind.owner = upper('&ixowner')
and
ind.table_name = upper('&tabname')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
order by
col.index_name,
col.column_position
/
undef ixowner
undef tabname
set verify on
==============
Sample Output:
==============
Index Keys Summary
Uniqueness                Index Name                    Column Name
---------- ---------------------------------------- ----------------------
UNIQUE                SCOTT.S_EMP_ID_PK                        ID
UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID

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)

oracleasm failed to createdisk on raw device

在测试ASMlib的时候出现了oracleasm无法创建磁盘的错误,具体如下:

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

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
~~~~~~~~~~~                    
Version/Use:
  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>
                instead.
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"
Levels:
  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.
Description/Steps:
  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
  marked as SOFTWARE CORRUPT.
  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.
*** WARNING:
        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.
Related:
  Database block checking features                          <Note:32969.1>
  Table and Cluster block equivalents          <Event:10210> <Event:10212>

沪ICP备14014813号

沪公网安备 31010802001379号