LIOs from 10g when using CPU_TEST.SQL
- From: "Tim Onions" <att755@xxxxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 10 Jan 2006 14:42:02 +0000
All
I am using the CPU_TEST.SQL script discussed before Xmas (found on
http://home.comcast.net/~arivenes/sql/cpu_test.sql) to see what effect
moving to 10g will have on our current 9.2.0.7 Win2003 8 CPU systems. Taking
into account all the good words said about this script I'm only using it as
an indicator. However, the results I get puzzle me. I have 2 DBs on the same
server (only one up at any point in time) - a 9.2.0.7 and a 10.2.0.1. Both
are 8k block size and everything else as near as the same as I can get them.
I run the script for 9.2.0.7 it takes 3 seconds, does ~30000 LIOs/sec/CPU
via 40300 LIOs.
I run the script for 10.2.0.1 it takes .25 seconds, does only 25000
LIOs/sec/CPU but (and here is the confusion) only uses 1500 LIOs!. So the
LIO/sec is slower but the overall result is much quicker due to the vastly
reduced number of LIOs needed.
I run and re-run this and also checked the 9.2.0.7 data on other Dbs, the
same number come up for a 9.2.0.7 DB.
So what am I missing, why does 10g require less LIO? It clearly is much
quicker whatever it is doing. Is there something missing from the v$sessstat
the script uses (and yes it gets then via stat name not statistic#). It is
almost as if 10g is getting mutlple blocks per LIO (in a similar way to how
db_file_multiblock_read_count does for physical IO).
Whilst happy with the vast performance improvement I am seeing I need to
understand why and in so understanding see how this might help/hinder a real
appliation.
Many thanks
Tim Onions
PS I made some very slight adpatations to the original script to display the
numbe rof LIOs, total time etc. If you want to see exactly what was run then
it is available (for now) on www.timonions.com/cpu_test.sql
_________________________________________________________________
Be the first to hear what's new at MSN - sign up to our free newsletters!
http://www.msn.co.uk/newsletters
--
//www.freelists.org/webpage/oracle-l
Other related posts: