Originally posted by JMac
[B]Can someone please tell me why BAD SQL (poorly tuned) causes more LIO than PIO?
The amount of LIO's practically defines what is good and what is bad SQL (relatively).

Try the following ...

Code:
set autotrace on

select owner,object_name from dba_objects
where timestamp =
(select max(timestamp) from dba_objects)
/

select owner,object_name
from
(
select owner,object_name,timestamp,
max(timestamp) over () max_timestamp
from dba_objects
)
where timestamp=max_timestamp
/
Try these on your system ... same result, different LIO's.

Now if this were a user query then the blocks would either be in memory or not, so the PIO's would be the same. On my little test system I got 41420 consistent gets for the first methodology and 20710 for the second -- exactly half. The cache hit ratio for second methodology is therefore half of the ratio for the first methodology.

You see? Better SQL = lower cache hit ratio. High cache hit ratio = potentially bad SQL.