| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LogMnr tuning?
Anyone have experience in getting LogMnr to run just a tad faster?
Vital stats: Oracle 9.2.0.6, 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
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 04 2006 - 12:33:44 CST
 |
 |