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';
VIEW: X$KTUXE – Transaction Entry (table)
2007/04/19 by 1 Comment
EVENT: 10500 "turn on traces for SMON"
2007/04/19 by Leave a Comment
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
2007/01/24 by Leave a Comment
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
2006/08/04 by Leave a Comment
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
2006/07/22 by Leave a Comment
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>
EVENT: 10060 dump predicates in optimizer (kko)
2006/07/22 by Leave a Comment
Event:10060 Text: dump predicates in optimizer (kko) ------------------------------------------------------------------------------- Explanation: 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. Usage: - 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 Example: ~~~~~~~ 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
2006/07/22 by Leave a Comment
@23-JAN-01, rpowell.uk, Distribution justification: "Should not be used by @ customers unless told to by support so making noncustomer-viewable." - dbither.us Event: 10218 Text: dump uba of applied undo ------------------------------------------------------------------------------- Note: Events should NEVER be set by customers unless advised to do so by Oracle Support Services. See <Note:75713.1> for more details. Description: 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
EVENT:10226 trace CR applications of undo for data
2006/07/22 by Leave a Comment
Error: ORA 10226 Text: trace CR applications of undo for data operations ------------------------------------------------------------------------------- Explanation: This is NOT an error but is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE actions. This event causes application of UNDO for producing CR copies of blocks. It can produce a lot of output (in the form of REDO application dump to rewind the block)
EVENT:10061 disable SMON from cleaning temp segment
2006/07/22 by Leave a Comment
Error: ORA 10061 Text: disable SMON from cleaning temp segment ------------------------------------------------------------------------------- Explanation: This is NOT an error but is a special EVENT code. It should *NOT* be used unless explicitly requested by RD support. It allows you to prevent SMON from cleaning up temporary segments. If set in: 7.0.X: This will prevent SMON performing cleanup of TEMP segments and from coalsecing extents. The cleanup action was performed every 3 minutes in 7.0.X releases. 7.1.X: The event behaviour is modified in that TEMP segment cleanup is skipped on TIMEOUTS but will be performed if SMON is posted. The cleanup only occurs every 12 hours or when posted in 7.1.X releases. You can POST SMON by performing a failed CREATE TABLE command. A new event 10269 is for coalescing extents. Usage: event="10061 trace name context forever, level 10" Articles: @Archived Managing SMON Activity on multiple OPS instances <Note:45856.1>
EVENT:10212 check cluster integrity
2006/07/22 by Leave a Comment
Error: ORA 10212 Text: check cluster integrity ------------------------------------------------------------------------------- Explanation: This is NOT an error but is a special EVENT code. It should *NOT* be used unless explicitly requested by RD support. Event 10212 enables cluster block integrity checking. The causes a number of checks on the contents of a 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 cluster is marked SOFTWARE CORRUPT it is best to rebuild the entire cluster. Usage: event="10212 trace name context forever, level 10" Articles: Data and Index block equivalents <Oerr:10210> <Oerr:10211>
