RE: LogMnr tuning?
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 4 Feb 2006 12:33:44 -0600
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
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
***************************************************************************
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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
