Notes in-line
--
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:3ff2e475$1$18694$afc38c87_at_news.optusnet.com.au...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:bsua97$rsb$1$8300dec7_at_news.demon.co.uk...
>
>
> > 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.
> >
>
> Would this also stand for an index block access?
> I'm just reminded of Steve's argument in the "7 sins"
> about the root index block access not being scalable.
> That would be mostly because of the above?
>
Yes.
Root blocks are less scalable because they are
very popular. Get 10,000 queries hitting a table
through an index for a random row, and you
might get:
1 root block hit 10,000 times
100 branch blocks hits 100 times each
10,000 table blocks hit once each.
If there is a non-scaling point (other than the
table I/O, perhaps) it's going to happen at
the root block.
Oracle has worked to reduce the problem -
in recent versions a lot of root block access
seems to single latch access, which helps.
There have also been changes in the way
root blocks are pinned during nested loops
and inside pl/sql calls to reduce the number
of latch gets.
>
> > 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.
>
> Has it changed significantly between 8i and 9i?
>
I think there has been a steady increase in the code
path, so in my test case, the rate has tended to decrease
with version of Oracle. On the other hand, some SQL
has gone faster because new strategies have been
introduced to reduce the number of units of action
required even though the cost of a single unit may
have increased.
> --
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam
>
>
Received on Wed Dec 31 2003 - 09:37:24 CST
