Note 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:3ff2e731$0$18390$afc38c87_at_news.optusnet.com.au...
>
> Jonathan might want to confirm this or not, but I reckon striping does
> help mostly when you have to access consecutive (adjacent) blocks in a
> single read request. Which is the case in FTS, or in data range scans
> (if the data is clustered), or in index range scans.
>
> Single index access will gain little benefit from striping. You'd have
> to simulate a very large user load to really see a benefit, I reckon.
>
You won't gain "directly" from striping. if all you do is
single block access. But in cases like this, the purpose of
striping is not to speed up the individual read, but to
scatter the reads as evenly as possible across the
available and avoid queues. It's a statistical game,
of course, and you may get unlucky, but it does tend
to avoid hot spots.
In another post, the poster said there were 6 data devices
and 8 index devices. I don't have the statspack I/O report
any more, but for highly random accesses, I would guess
that a relatively small number of the reads are going physical
on the indexes, and most are going on the writes.
The statspack also said 213 reads per second - which is about
35 per second per disc - which is poor but not a total surprise.
Two strategies: first, spread the data and indexes across all 14
discs if there are any indications that most of the I/O is on the
data - this would drop you to 18 I/Os per disc if my assumption is
correct, and this might help.
Second: look at putting the indexes for the most popular access
path into a very large KEEP pool and the table into a small
RECYCLE pool. This is to protect the indexes, and stop their
effectiveness (cache hit ratio) being eroded by all the table reads.
The potential benefit of these moves, though, can only be
confirmed by a careful check of the SQL, and its costs.
The OP also mentions that all the SQL is efficient and
using indexes - one guideline for precision is to compare
buffer_gets with rows_processed. For a single-table access
in this type of situation, I would be hoping for no more than
4 or 5 buffer_gets per row_processed, and no more than
ONE disk_read per row processed.
Received on Wed Dec 31 2003 - 09:51:53 CST
