Re: Buffer cache statistics (ratios) and CBO SQL optimization?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Dec 2003 10:58:22 -0000
Message-ID: <bsua97$rsb$1$8300dec7@news.demon.co.uk>

Just for the record, the 10,000 LI/Os
per 100MHz of CPU is a guideline only.

It assumes that the action on reaching a block is simple (e.g. find a row by rowid) and not inherently CPU intensive.

Basically it is pointing out that the code path to

    calculate hash
    latch
    pin
    drop latch

        ACCESS DATA - time unknown
    latch
    unpin
    drop latch
is going to be fairly constant, and a significant fraction of the total cost of an LI/O provided you don't do anything horrible at the ACCESS DATA bit.

If the code path is roughly constant, then the time to run it will be largely dependent on CPU speed. However, different O/S features (such as latch handling) may give a noticeable variation in the time.

Nowadays you also have to allow for the fact that some CR Gets actually access the data whilst holding the latch, so something like half the code path can disappear.

and so on.....

Over the years, across different plaforms, and different versions of Oracle, I've seen a variation in my base test from about 5,000 to 17,000.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message
news:3ff2a667$0$18750$afc38c87_at_news.optusnet.com.au...


> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message

news:3FF275DD.3F0_at_yahoo.com...


>

> > Some figures from machines I've played with in the past

> >

> > Machine              LIO/sec           PIO/sec

> > Home PC, 500Mhz      34,950             340

> > HP, 266 Mhz          26,770             650

> > HP, 650 Mhz          74,850            2780

> > HP, 750 Mhz,         83,150            2840

> > Compaq, 700Mhz       66,470            3380

>

>

> The Compaq numbers are quite surprising.  The LIO is lower

> than others of similar speed, but the PIO is much faster.

> I wonder: was that an Alpha?

>

> With the exception of the home PC, the numbers seem to

> confirm JL's claim of 10K LIO/s for each 100MHz.  They

> are certainly around those figures.

>

> Do you recall what size was the PIO?  I can pump small blocks

> from my new 80Gb drive higher than that 340, nowadays. Given

> ideal conditions, of course.

>

> -- 

> Cheers

> Nuno Souto

> wizofoz2k_at_yahoo.com.au.nospam

>

>

Received on Wed Dec 31 2003 - 04:58:22 CST