| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Should you still tune queries by LIOs?
One more comment:
How do you think, are different types of LIO consumes the same amount of
CPU time?
Try to compare two SQL with the same LIO count but
FIRST SQL ------------ SECOND SQL
Dose aggregation Doesn’t
or sorting
operations
...
I have made the test, recently (single CPU server, single connection to
database, no other load):
First SQL
Elapsed: 00:00:01.92
NAME VALUE -------------------------------------------------- session logical reads 23041 CPU used by this session 141 physical reads 25 sorts (memory) 3
6 rows selected.
Second SQL
Elapsed: 00:00:14.51
NAME VALUE -------------------------------------------------- session logical reads 23000 CPU used by this session 1447 physical reads 0 sorts (memory) 1001
6 rows selected.
SYS:jozh>
As you can see the same amount of LIO, CPU utilization defers by 1000% (as well as response time).
You can toy to compare Nested Loop with Full table scan or FILTER operation and will see that LIO doesn’t effectively represent CPU consuming by SQL.
If you would like to lower CPU usage in your system, then sort SQL by CPU usage not by LIO.
Jurijs
+371 9268222 (+2 GMT)
ryan_gaffuri_at_comcast.net
Sent by: oracle-l-bounce_at_freelists.org
07.09.2004 16:24
Please respond to ryan_gaffuri
To: oracle-l_at_freelists.org
cc:
Subject: Should you still tune queries by LIOs?
I believe its Mogens chapter in the Tales of the Oak Table book where he
said he found with 10g that LIOs and CPU usage do not necessarily
correspend. He argues that tuning queries should be explicitly based on
elapsed time.
My understanding of LIOs is that every LIO is a buffer cache latch get, so
even if you do not use up more CPU you are incurring serialization and
under concurrency can cause performance problems. I have seen queries go
from 20,000 LIOs down to 300 with a very small performance improvement. Is
it worth it to spend the time to do this?
BTW, its a very good book. The chapter by Dave Ensor on the history of
Oracle is one of the best chapters you can find anywhere. I hope he writes
more now that he is retired.
-- To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ -- To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/Received on Tue Sep 07 2004 - 10:36:56 CDT
 |
 |