Re: High elapsed/cpu ratio

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?

> 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.

> 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 Received on Tue Sep 11 2007 - 12:52:51 CDT