How to find amount of redo generated per a SQL statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to find amount of redo generated per a SQL statement?

  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Exclamation How to find amount of redo generated per a SQL statement?

    Hi,

    What is the best way to find how much redo has been generated per a SQL
    statement?

    I know about "redo size" statistics in v$sesstat, but that is redo size
    per session, not redo size per a statement.

    Thanks.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    From sql plus you cna do that

    sql>set autotrace on

    run your sql statement

    in the trace you can see that

    regards
    Hrishy

  3. #3
    Join Date
    Dec 2005
    Posts
    6

    Question How to find amount of redo generated per a SQL statement?

    OK, let have an example: there is a frequent log switching in a
    database. A session is generating an excessive amount of redo. I can
    easily find which session it is by looking into v$sesstat and "redo
    size" statistics.

    But, I would like to be able to see for each SQL statement that session
    is running the amount of redo entries (in bytes) it generates. Even
    approximate amount would be fine.
    For example:
    SQL STATEMENT REDO SIZE
    ----------------------- ------------
    UPDATE ACC SET A=3; 8460 bytes
    SELECT .A FROM DUAL; 0 bytes
    DELETE NOM; 469000 bytes
    .... etc

    Regards,
    Gate%way........

  4. #4
    Join Date
    Nov 2005
    Posts
    32
    One way to get the redo sizes at the detail you are looking for is to do a dump on the redo log or the archive log files and look for the following details within the trace files:

    A transaction with multiple statements are bundled within the same controlling SCN and so look for the matching SCN values within the redo change vectors. The "KDO Op code:" within the trace file gives the type of operation performed:

    IRP / QMI => Insert Operation
    URP => Update operation
    DRP / QMD => Delete operation

    "siz:" entry within the "ktudb redo:" and "ktudh redo:" sections in the trace file gives the actual redo size for the operation.

    You can arrive at individual redo sizes at the transaction level by using the "objn:" / "objd:" (object ids) entries in conjunction with the entries mentioned above (SCN, KDO Op code and siz) from the redo log or archive log dump.

    Good luck!

    http://www.dbaxchange.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width