RE: LogMnr tuning?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sat, 18 Feb 2006 12:59:15 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3601785B11@CWYMSX04.Corp.Acxiom.net>

In case anyone is interested, I found where the problem was and got around it for now. I was using Oracle's online dictionary, but when I sent it to a flat file and used that, response time was normal. Apparently, somewhere in SYS.LOGMNR_GTLO3 when SYS-owned tables are used on metadata lookups, very poor joins occur. I saw that index I_TABPART$ had 790,656,496 logical reads (according to STATSPACK in my tests), so that looks like a good place to start.

Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

> -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org]
> > On Behalf Of Herring Dave - dherri
> > Sent: Saturday, February 04, 2006 12:34 PM
> > To: oracle-l_at_freelists.org
> > Subject: 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?

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 18 2006 - 12:59:15 CST