Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Generate SSH without password authentication. Linux/Unix
  • Oracle Metalink useful notes Oracle
  • This is im telling Kishore Oracle
  • Remove duplicate rows from table Oracle
  • Zip and unzip with tar Linux/Unix
  • Jai Shree Ram Oracle
  • rm_backup_arch_file.ksh Linux/Unix
  • Rman Notes -1 Oracle
  • upload.html PHP/MYSQL/Wordpress
  • lck.sql Oracle
  • Linux CPU info. Linux/Unix
  • How to connect to Oracle Database with Wallet with Python. Oracle
  • DBA_HIST_SQLSTAT contents Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • good note for shared pool tunnig Oracle

Facts about SCN and Rollback Segment

Posted on 21-Apr-2007 By Admin No Comments on Facts about SCN and Rollback Segment

1. When should you create more rollback segments?

The number of rollback segments needed to prevent contention between processes can be determined with the help of the monitor rollback display and with the use of the v$waitstat table. The rollback monitor column “header waits/sec” gives an indication of the current rollback segment contention. Waits are a definite indication of contention. The following v$waitstat query will display number of waits since instance startup:

SELECT * FROM V$WAITSTAT;

Any non-zero value for ‘undo header’ in the CLASS column of “v$waitstat” indicates contention for rollback segment header blocks.

You may want to add a rollback segment when you know you have a large transaction.

2. Is PCTINCREASE for rollback segments always 0?

True.

3. What DML creates the most undo?

Delete and update will probably create the most undo. INSERT STATEMENT creates the least undo because oracle only needs to store the rowid.

The number of bytes required for storage of undo, or information that is needed in case of rollback, depends on two things: 1) the type of transaction being performed (insert, update, delete, etc.) and 2) the actual data being processed. In general, inserting a given record into a table generates less undo than deleting the same record, because while rolling back an insert only requires deleting the row, rolling back a delete requires reinserting that row. (It would take less bytes to store a rowid than to store information to reconstruct the actual row itself.)

Additionally, the undo generated for an update on a fraction of a row is not proportionate to the amount generated for the entire row, due to additional overhead required.

4. If you have a table with an index and you insert a row, does Oracle create undo for the index as well?

Yes. When inserting into a table with an index, there is considerably more undo created than when inserting into a table without an index

Here is an example:

Update emp set empno=15; when no one else is on the system.

In v$rollstat, these numbers were compared:

1,214,058 writes (no indexes)

4,688,328 writes (one index)

Update can cause a key to change, meaning Oracle is deleting from one part of the index and inserting into potentially an entirely different part of the index. If the block has to split then more undo is generated.

5. How does Oracle determine which rollback segment to use?

The rules are:

(i) always assign to rbs which has least number of active txns

(ii) if two or more rbs have the same “least number of active txns” , then assign to the one which is after the last one used. This ensures that undo is kept for a longer time.

6. Can a transaction span several rollback segments?

No. A transaction can span several extents but must remain in the same rollback segment throughout the duration of the transaction.

7. What do you record in the data block when DML occurs?

Here are the generic steps:

a. Pin the current version of the block in database buffer cache.

b. Write the change to the block in the buffer cache. This is the real change that was poited by DML.

c. Write the undo information to a redo record in the redo buffers.

d. Write the undo information to a block in the rollback segment.

e. Write the change to a redo record in the redo buffers.

f. LGWR writes out redo buffers at some later time. DBWR writes out the changed blocks at some later time. LGWR always writes out redo records before DBWR can write out the changed block.

g. When you commit, Oracle updates transaction table and record the SCN for that transaction.

Oracle, SQL scripts

Post navigation

Previous Post: logminer and my_lbu
Next Post: Session_info.ksh

Related Posts

  • Absolute file number and relative file number Oracle
  • New OFA for 11g Oracle
  • UTL_FILE test program Oracle
  • Jai Shree Ram Oracle
  • dbinv.sql Oracle
  • Find all users who have DML privileges Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • SQL Tracker by SID sqltrackerbysid.sql Oracle
  • Kill a session dynanically using execute immediate Oracle
  • Looping for remote servers and find its database from oratab file. Linux/Unix
  • Renaming Oracle Instance Name Oracle
  • dbms_job.submit example Oracle
  • Absolute file number and relative file number Oracle
  • SQLPLUS COPY command Precautions. Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme