Re: LIOs from 10g when using CPU_TEST.SQL

  • From: Sriram Kumar <k.sriramkumar@xxxxxxxxx>
  • To: att755@xxxxxxxxxxx
  • Date: Tue, 10 Jan 2006 10:58:49 -0400

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

Cheers

Sriram Kumar






On 1/10/06, Tim Onions <att755@xxxxxxxxxxx> 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 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: