Re: Should you still tune queries by LIOs?
- From: J.Velikanovs@xxxxxxxx
- Date: Tue, 7 Sep 2004 18:32:44 +0300
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
============================================================
PL/SQL procedure successfully completed.
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
============================================================
PL/SQL procedure successfully completed.
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)
============================================
Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html
ryan_gaffuri@xxxxxxxxxxx
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
07.09.2004 16:24
Please respond to ryan_gaffuri
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
