Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: LIOs from 10g when using CPU_TEST.SQL

Re: LIOs from 10g when using CPU_TEST.SQL

From: Sriram Kumar <>
Date: 2006-01-10 15:58:49

Hi Tim,

What was your PL/SQL optimization level set as?. Can you try with PL/SQL optimization level as 0 and try. presently I dont have access to 10g environment may it would be great if you can try and share the results


Sriram Kumar

On 1/10/06, Tim Onions wrote:
> All
> I am using the CPU_TEST.SQL script discussed before Xmas (found on
> to see what effect
> moving to 10g will have on our current 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 and a Both
> are 8k block size and everything else as near as the same as I can get
> them.
> I run the script for it takes 3 seconds, does ~30000 LIOs/sec/CPU
> via 40300 LIOs.
> I run the script for 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 data on other Dbs, the
> same number come up for a 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
> _________________________________________________________________
> Be the first to hear what's new at MSN - sign up to our free newsletters!
> --
Received on Tue Jan 10 2006 - 15:58:49 CST

Original text of this message