Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

To quote Cary Millsap (who, I believe quotes someone else) "you can't tell how long something took by counting how often it occured". Trying to use any count (lio, pio, BCHR, ...) as s substitute for elapsed time is misguided from the start. Your summary below contains the answer. Remember

response time (elapsed time) = service time + wait time.

as an aside, note that there are no counts anywhere in that formula. In your case the service time (cpu) is the difference. Due to the peculiarities of cpu time accounting, wait time is -0.01 for both of your queries, but plan 1 takes 4 times the cpu to do the work.

Just another example why it is important to gather system statistics so that the CBO will consider cpu time when making a plan selection. That doesn't mean he/she/it will get it right every time, but there is a better chance with cpu costing.

Quoting "Radoulov, Dimitre" <cichomitiko_at_gmail.com>:

> Thanks for the feedback,
> I will check the different types of logical IO in oracle.
>
> I'm trying to undestand why I have:
>
> 1. Fetch 2 0.26 0.25 0 1725 0
> 1
> 2. Fetch 2 0.06 0.05 0 7559 0
> 1
>
> No waits other than SQL*Net messages from/to client in the trace file:
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

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