Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re : *Measuring sql performance (elapsed time and scalability) by number of logical reads
it may comes from so many reasons, beside the DB load at the moment you run the 2 sql, you may have other considerations such as the number of intermediate sorts, presence of connect by clauses, presence of function somewhere in the first SQL, libray latches, the tables parallel degree setting.
I don't think anybody could give you the answer you expect without seeing both sql and explain plan.
Usually when you deal with query that runs hundreds time per seconds and there is only buffer gets at the end, I tend to look for a user defined function in the selected fields. A function there is fired every row. You may find it also in the where clause but developer usually bring more attention when it is in the where clause.
B.polarski
http://www.smenu.org
From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com]
Sent: Tuesday, 02 May, 2006 2:09 PM
To: Bernard Polarski
Cc: oracle-l_at_freelists.org
Subject: ** SPAM scored: Low **Re: *Measuring sql performance (elapsed
time and scalability) by number of logical reads
>I think you have been a bit short in the problem description.
>
> You just meant that all the requested data is already in buffer and no
> physical read is needed.
> Thanks but we have no information on the nature of the sql, the amount of
> data, the expected goal.
> Bad or good SQL is a ratio of these. What if I read one million blocks
> from my multi gig db block buffer
> to return a tiny rowset for the worse ever seen SQL, it will satisfy your
> prerequisite and still be very bad.
Excuse me for not being clear, I meant, theoretically speaking:
SQL 1 reads n1 blocks from buffer (no physical read) to complete, elapsed
time t1
SQL 2 reads n2 (where n2 > n1) blocks from buffer (no physical read) to
complete, elapsed time t2
t1 is greater than t2
Always theoretically/hypothetical speaking: could anyone comment the possibile reasons behind such behaviour.
Regards,
Dimitre
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 02 2006 - 07:29:53 CDT
![]() |
![]() |