Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: Connor McDonald <mcdonald.connor_at_gmail.com>
Date: 2006-01-10 15:54:27
Message-id: 5e3048620601100654m660748b4i2460373d97cd0ef9@mail.gmail.com


Its one of the auto-magic optimizations in 10g plsql. Code that looks like:

for i in my_cursor loop

   ...
end loop;

is translated behind the scenes into something equivalent to:

open my_cursor
loop

   fetch my_cursor BULK COLLECT into i LIMIT 100 end loop;
close my_cursor

so you're getting array processing benefits for free...

hth
Connor

On 1/10/06, Tim Onions wrote:
>
> 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 http://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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Connor McDonald
===========================
email: connor_mcdonald_at_yahoo.com
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"
Received on Tue Jan 10 2006 - 15:54:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US