Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: LogMnr tuning?

RE: LogMnr tuning?

From: Herring Dave - dherri <>
Date: Sat, 4 Feb 2006 12:33:44 -0600
Message-ID: <>

Anyone have experience in getting LogMnr to run just a tad faster?  

Vital stats: Oracle, Tru64 5.1, 1GB redologs.

I'm trying to retrieve a DELETE statement and eventually undo it.  Its across 2 archived logs, according to date stamps I've got.  The problem is, querying V$LOGMNR_CONTENTS is taking hrs. upon hrs!  From what I can tell, every SQL statement found is parsed, as the execute count is over 108 million.  I tried setting CURSOR_SHARING=FORCE in my session and have also considered setting OPTIMIZER_MAX_PERMUTATIONS=4 (or something low), as I don't care about the plans at all.

Does this make sense and has anyone seem similar results?  Most importantly, has anyone found a way to speed up the process?

A check of V$SESSION_EVENT and V$SESSTAT for my session shows the following waits/stats:

EVENT                                    TOTAL_WAITS TIME_WAITED

---------------------------------------- ----------- -----------
enqueue                                            3           4 SQL*Net message from client                       11          11 db file scattered read                           211         136 log file sequential read                         604         216 log file switch completion                       114         444 buffer busy waits                              3,587         551 control file sequential read                   2,554         583 db file sequential read                        1,133         609 log file switch (archiving needed)                10         615 log buffer space                                  33         629 control file parallel write                    1,530       1,143 latch free                                   164,389      18,043 <- shared pool, library cache NAME                                                           VALUE
------------------------------------------------------- ------------
physical reads                                                 2,901 deferred (CURRENT) block cleanout applications                 4,521 commit cleanouts                                               4,598 commit cleanouts successfully completed                        4,598 rollbacks only - consistent read gets                          4,695 cluster key scans                                              4,997 cluster key scan block gets                                    9,711 messages sent                                                 10,205 buffer is pinned count                                        17,379 table scan rows gotten                                        17,383 index scans kdiixs1                                           21,986 db block gets                                                 62,450 prefetched blocks aged out before use                         66,030 session uga memory                                           147,648 session uga memory max                                       161,656 table fetch by rowid                                         165,596 parse time cpu                                               204,712 parse time elapsed                                           245,667 buffer is not pinned count                                   508,431 change write time                                            525,625 physical writes                                              588,206 physical writes non checkpoint                               588,206 physical writes direct                                       588,206 recursive cpu usage                                        7,704,328 cleanouts and rollbacks - consistent read gets             8,197,498 immediate (CR) block cleanout applications                 8,197,581 cleanout - number of ktugct calls                          8,197,581 CR blocks created                                          8,202,807 free buffer requested                                      8,205,868 calls to kcmgas                                            8,207,492 db block changes                                           8,269,068 redo entries                                               8,317,039 active txn count during cleanout                          24,097,598 data blocks consistent reads - undo records applied       26,033,197 consistent changes                                        26,033,267 shared hash latch upgrades - no wait                      46,109,699 no work - consistent read gets                            46,431,128 index fetch by key                                        54,292,599 session cursor cache hits                                 54,296,868 parse count (total)                                       54,296,892 opened cursors cumulative                                 54,296,893 enqueue releases                                          54,307,095 enqueue requests                                          54,307,100 calls to get snapshot scn: kcmgss                         54,310,804 consistent gets - examination                            104,428,540 execute count                                            108,584,589 consistent gets                                          159,088,544 session logical reads                                    159,150,994 recursive calls                                          163,161,845 redo size                                                536,465,168

Thanks for any help you can offer.


Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

Received on Sat Feb 04 2006 - 12:33:44 CST

Original text of this message