Oracle Cloud Infrastructure
Coming soon ...
Apps R12.2
  • R12.2 Architecture
  • Cloning Error (RC-50208)
  • Apps R12.1
  • Major changes from 11i
  • R12:HTTP Debug Logging
  • Compile Apps Schema invalid objects in R12
  • Apps 11i
  • Apps Components and Architecture
  • Concurrent managers
  • Patching
  • Using AD Patch
  • Using AD Control
  • FNDCPASS utility
  • Single-node Installation
  • Multi-node Installation
  • Find Apps Version
  • Cloning
  • Upgrade 11.5.9 to 11.5.10.2
  • Upgrade from 11.5.10.2 to R12
  • Upgrading 9i to 10g with 11i
  • 11i/R12 General Topics
  • AppsDBA System Management Utilities Guide
  • Identifying Long Idle Sessions
  • Identifying High Active Sessions
  • Change hostname for EBS
  • Oracle 12c Database
  • Oracle12c PSU Apply
  • Oracle12c Datafile moved Online
  • Oracle 11g Database
  • Upgrade 10g to 11g R1
  • Upgrade 11.2.0.2 to 11.2.0.3
  • Database 9i-10g
  • Top 99 Responsibilities of a DBA
  • General Info
  • Database Patching
  • 10g:ASM
  • 10g:Data Pump
  • 10g:Data Guard Installing
  • 10g:Rollback Monitoring
  • 10g:Flashback Table
  • Tablespace Management
  • Materialized Views
  • 10g:Enterprise Manager
  • 10g:Upgrade
  • Error:Ora-01631
  • DBA Scripts
  • Disk I/O,Events,Waits
  • Tablespace Information
  • Session Statistics
  • Hit/Miss Ratios
  • User Information
  • Rollback Segments
  • Full Table Scans
  • Contention/Locking
  • Redo Log Buffer
  • Data Dictionary Info
  • Oracle10g Application Server
  • Oracle10g Application Installation
  • (Re)Securing OAS Control
  • Oracle AS10g null protocol issue
  • Oracle Backup & Recovery
  • RMAN
  • RMAN Setup
  • Backup Recovery
  • Flash Recovery Area
  • Oracle10g Discoverer with Apps
    Coming soon ..............
    Troubleshooting
  • Discoverer Troubleshooting
  • Access EBS in mozile
  • Linux and Unix Platforms
  • How To configure NFS
  • Unix/Linux Command
  • Change hostname in Linux
  • SENDMAIL configuration
  • This Oracle Application DBA Portal is the biggest knowledge gateway for the people in the world of Oracle...
    Saturday, April 4, 2009
    10g:Rollback Monitoring
    How Much Longer?: Rollback Monitoring

    Give users an accurate estimate of the duration of a rollback operation
    Are we there yet? How much longer?

    Sound familiar? These questions may come from the back seat on your way to the kids' favorite theme park, often incessantly and with increasing
    frequency. Wouldn't you want to tell them exactly how much longer it will take—or better yet, know the answer yourself?
    Similarly, when a long, running transaction has been rolled back, there are often several users breathing down your neck asking the same
    questions. The questions are justified, because the transaction holds the locks and normal processing often suffers as the rollback progresses.

    In Oracle 9i Database and below, you can issue the query
    SELECT USED_UREC
    FROM V$TRANSACTION;
    which returns the number of undo records used by the current transaction, and if executed repeatedly, will show continuously reduced values
    because the rollback process will release the undo records as it progresses. You can then calculate the rate by taking snapshots for an interval and
    then extrapolate the result to estimate the finishing time.
    Although there is a column called START_TIME in the view V$TRANSACTION, the column shows only the starting time of the entire transaction
    (that is, before the rollback was issued). Therefore, extrapolation aside, there is no way for you to know when the rollback was actually issued.
    Extended Statistics for Transaction Rollback
    In Oracle Database 10g, this exercise is trivial. When a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, which
    shows long running transactions. For rollback purpose, if the process takes more than six seconds, the record appears in the view. After the
    rollback is issued, you would probably conceal your monitor screen from prying eyes and issue the following query:

    select time_remaining
    from v$session_longops
    where sid = ;

    Now that you realize how important this view V$SESSION_LONGOPS is, let's see what else it has to offer. This view was available pre-Oracle

    Database 10g, but the information on rollback transactions was not captured. To show all the columns in a readable manner, we will use the

    PRINT_TABLE This procedure simply displays the columns in a tabular manner rather than the
    usual line manner.

    SQL> set serveroutput on size 999999
    SQL> exec print_table('select * from v$session_longops where sid = 9')
    SID : 9
    SERIAL# : 68
    OPNAME : Transaction Rollback
    TARGET :
    TARGET_DESC : xid:0x000e.01c.00000067
    SOFAR : 10234
    TOTALWORK : 20554
    UNITS : Blocks
    START_TIME : 07-dec-2003 21:20:07
    LAST_UPDATE_TIME : 07-dec-2003 21:21:24
    TIME_REMAINING : 77
    ELAPSED_SECONDS : 77
    CONTEXT : 0
    MESSAGE : Transaction Rollback: xid:0x000e.01c.00000067 :
    10234 out of 20554 Blocks done
    USERNAME : SYS
    SQL_ADDRESS : 00000003B719ED08
    SQL_HASH_VALUE : 1430203031
    SQL_ID : 306w9c5amyanr
    QCSID : 0

    Let's examine each of these columns carefully. There may be more than one long running operation in the session—especially because the view
    contains the history of all long running operations in previous sessions. The column OPNAME shows that this record is for "Transaction Rollback,"
    which points us in the right direction. The column TIME_REMAINING shows the estimated remaining time in seconds, described earlier and the
    column ELAPSED_SECONDS shows the time consumed so far.
    So how does this table offer an estimate of the remaining time? Clues can be found in the columns TOTALWORK, which shows the total amount of
    "work" to do, and SOFAR, which shows how much has been done so far. The unit of work is shown in column UNITS. In this case, it's in blocks;
    therefore, a total of 10,234 blocks have been rolled back so far, out of 20,554. The operation so far has taken 77 seconds. Hence the remaining
    blocks will take:

    77 * ( 10234 / (20554-10234) ) » 77 seconds
    But you don't have to take that route to get the number; it's shown clearly for you. Finally, the column LAST_UPDATE_TIME shows the time as of
    which the view contents are current, which will serve to reinforce your interpretation of the results.
    SQL Statement
    Another important new piece of information is the identifier of the SQL statement that is being rolled back. Earlier, the SQL_ADDRESS and
    SQL_HASH_VALUE were used to get the SQL statement that was being rolled back. The new column SQL_ID corresponds to the SQL_ID of the
    view V$SQL as shown below:
    SELECT SQL_TEXT
    FROM V$SQL
    WHERE SQL_ID = ;
    This query returns the statement that was rolled back, thereby providing an additional check along with the address and hash value of the SQL
    statement.

    Parallel Instance Recovery
    If the DML operation was a parallel operation, the column QCSID shows the SID of the parallel query server sessions. In the event of a parallel
    rollback, such as during instance recovery and subsequent recovery of a failed transaction, this information often comes in handy.

    For example, suppose that during a large update the instance shuts down abnormally. When the instance comes up, the failed transaction is rolled
    back. If the value of the initialization parameter for parallel recovery is enabled, the rollback occurs in parallel instead of serially, as it occurs in
    regular transaction rollback. The next task is to estimate the completion time of the rollback process.

    The view V$FAST_START_TRANSACTIONS shows the transaction(s) occurring to roll-back the failed ones. A similar view, V
    $FAST_START_SERVERS, shows the number of parallel query servers working on the rollback. These two views were available in previous
    versions, but the new column XID, which indicates transaction identifier, makes the joining easier. In Oracle9i Database and below, you would have
    had to join the views on three columns (USN - Undo Segment Number, SLT - the Slot Number within the Undo Segment, and SEQ - the sequence
    number). The parent sets were shown in PARENTUSN, PARENTSLT, and PARENTSEQ. In Oracle Database 10g, you only need to join it on the
    XID column and the parent XID is indicated by an intuitive name: PXID.
    The most useful piece of information comes from the column RCVSERVERS in V$FAST_START_TRANSACTIONS view. If parallel rollback is
    going on, the number of parallel query servers is indicated in this column. You could check it to see how many parallel query processes started:
    select rcvservers from v$fast_start_transactions;
    If the output shows just 1, then the transaction is being rolled back serially by SMON process--obviously an inefficient way to do that. You can
    modify the initialization parameter RECOVERY_PARALLELISM to value other than 0 and 1 and restart the instance for a parallel rollback. You can
    then issue ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH to create parallel servers as much as 4 times the number of
    CPUs.

    If the output of the above query shows anything other than 1, then parallel rollback is occurring. You can query the same view (V
    $FAST_START_TRANSACTIONS) to get the parent and child transactions (parent transaction id - PXID, and child - XID). The XID can also be
    used to join this view with V$FAST_START_SERVERS to get additional details.

    Labels:

    posted by Srinivasan .R @ 2:03 PM  
    0 Comments:
    Post a Comment
    << Home
     
    About Me

    Name: Srinivasan .R
    Home: Chennai, India

    About Me:
    I am working as an Oracle Applications DBA specializing in EBS 11i/R12 with Over 14+ years of experience, mainly in different versions of Oracle Database & Application administration on various platforms like HP-UX, SOLARIS, AIX, Red hat Linux & Windows
    See my complete profile
    High Availability
  • Oracle10g RAC Installation
  • A Quick Reference for Oracle Database 10g RAC on Linux and Unix Platforms
  • Implementing Oracle 10g RAC with ASM on AIX
  • Locked objects for whole RAC
  • Monitor Memory RAC
  • Sessions RAC
  • Install Oracle 11g RAC On Linux
  • Migrating Oracle10g DB to ASM
  • Helpful Links
  • Good Metalink Notes
  • Discoverer:Metalink Notes
  • Logs Scripts:Metalink Notes
  • Support:Metalink Notes
  • Previous Post
  • How To configure NFS
  • Find Apps Version (11i/R12/12i)
  • Oracle10g:Grid Control Installing
  • Oracle10g Data Guard
  • Oracle Application Installation Guide10g Release3
  • Enable HTTP/OC4J/OPMN debug logging
  • Oracle10g: Data Pump
  • Oracle10g RAC Installation
  • Identifying High Active Sessions
  • FNDCPASS utility
  • Archives
    Download Software
  • Oracle 11g
  • Oracle 10g
  • 10g Express Edition
  • Oracle 9i
  • Oracle Apps
  • Oracle Linux
  • Oracle VM
  • App Server
  • Solaris
  • Fedora
  • Fedora
  • OpenSUSE
  • Ubuntu
  • Advertisement Links
    INTUIT Technology

    MACHS DATA

    Add Ons
    Locations of visitors to this page

    Add to Google Reader or Homepage

    Template by
    Sreene



    Oracle Application DBA Portal