Re: High elapsed/cpu ratio

Comments embedded.
On Sep 11, 12:52 pm, ebwri..._at_gmail.com wrote:
> So many people have offered suggestions since I checked yesterday and
> I truly appreciate it.
> Rather than try to get the quoting exactly right, I'll just excerpt
> with responses below.
>
> "R.Wang" <R.W..._at_oraclepoint.com> wrote
>
> > Hi Eric,
> > 1.The hit ratio is Good.
> > Logical Reads = query + current
> > Logical Reads = 16953935+0 + 49+0
> > Logical Reads = 16953935 + 49
> > Logical Reads = 16953984
> > Hit Ratio = 1 - (Physical Reads / Logical Reads)
> > Hit Ratio = 1 - ((870183+0) / 16953984)
> > Hit Ratio = 1 - (0.05)
> > Hit Ratio = 95%
>
> "Richard Foote" <richard.fo..._at_nospam.bigpond.com>
>
> > Hi R.Wang
> > The problem with hit ratios of course is that even 5% of a real real lot
> > can
> > still equal a lot.
> > Here we have 16,953,984 LIOs (which might well be considered a real real
> > lot) of which we still have 870,183 PIOs (which might well be considered
> > to
> > still be a lot).
> > Perhaps, just perhaps, a fair proportion of the "unexplained" elapsed
> > time
> > might just be explained by the fact we have nearly 1 million PIOs
> > happening
> > here. That works out to be roughly 380 PIOs per sec which isn't too bad
> > (3ms
> > average per I/O).
>
> I think Richard has hit on the real problem. 870k physical I/O
> operations at 3ms each is going to take quite a bit of time. Is there
> anything that can be done about this? Is this just the overhead of
> reading from disk all the data that is being used in the query? Would
> tweaking any of the database init parameters that deal with SGA/PGA
> make a difference?
>

Not likely. A better effort, if at all possible, would be to work on the query itself and try to filter some of the 'errant' data earlier in the stream.

> > Now, absolutely look at the raw trace file and investigate the actual
> > wait
> > events but not because of yet another poor example of using a hit ratio
> > ...
>
> Is this getting back to the level 12 trace? I already have a level 8
> trace file that has 42 instances of a WAIT line. Summing the ela
> values on the lines gives 2743, although I'm unsure of the units
> used. I'm not sure if this is useful or not.
>

Level 8 gives you the wait statistics, level 12 adds to that bind variables, if I remember correctly, so you should have the wait statistics necessary to troubleshoot this query. Statspack might provide better information as it can show waits other than those already in the 10046 trace.

> > It also seems to be an awful lot of work just to insert 5 rows into a
> > table,
> > hope there's some aggregation happening ;)
>
> Yes, there is *some* aggregation taking place, but unfortunately, this
> query is the catch-all for exceptions (essentially, one customer
> paying an invoice for another customer).
>
> > Cheers
> > Richard
>
> Steve Howard <stevedhow..._at_gmail.com>
>
> > I agree with this. It boggles my mind that you would have to join two
> > multi-million tables to insert five rows? Maybe something is wrong
> > with the application (LOL! I supported Oracle Apps for six years, so
> > I *know* that is true :)) Up until the most recent release, their
> > application software still *required* 8.0.6 client side software.
>
> You would have any experience with AR Statement Generation would you?
>
> > Regards,
>
> > Steve
>
> Thanks again,
> Eric

David Fitzjarrell Received on Tue Sep 11 2007 - 13:49:42 CDT