RE: how many LIOs is too many

> In this case the number of LIOs per execution is probably not the important
> bit - the important bit is that the query seems to take a little over 2 CPU
> seconds per execution.
> At 1,800 executions per hour (rather than the "few thousand" you suggest,
> this would be equivalent to eliminating one of your CPUs. Unless you've got
> at least 8 (real) CPUs, you don't want to be running this query; if you've
> got a small number of real CPUs which are using threads to fake themselves
> up to look like lots of CPUs you really don't want to be running this query.

Also watch your buffer cache (e.g. v$bh). If your table(s) and index(es) used in that query grow to a point that the required blocks for this query no longer play nice with the other growing data in your buffer cache, you'll quickly see how unnice and unquick the query really is.

The only good IO -- PIO or LIO -- is the one you don't need to do.

Your pain point seems to be that this is a 3rd party app. Been there, done that. No matter what we say, the business demand for that app may come with a cost of hardware (RAM and/or CPU) to hopefully mitigate in the short term what may be a scalability issue. UNLESS your vendor listens to your feedback! Had that actually happen once...

GL!

Rich

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