| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cary/Others RE: should you seperate indexes from tables in
Cary,
On the same line, I want to propose a different thought - smaller block sizes for index tablespaces to reduce the chance that a single block is contended for by two different sessions, which indices the wait event "buffer busy waits". Making them smaller, a typical index block will hold less number of leafs and therefore will have less chance of experiencing this wait.
Will appreciate your thoughts on this.
Arup
> Tracy,
>
> I would have expected you to say the opposite: big blocks for index
segments
> (to reduce B*-tree height), and small blocks for table data (to improve
> block selectivity).
>
> It's a pretty expensive thing to implement though (assuming you're already
> "up," the downtime to rebuild a tablespace could be costly). And most
sites
> have a lot of much less costly things they should be doing to create
vastly
> bigger impact (like getting rid of unnecessary LIO calls and parse calls).
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Washington, Denver, Sydney
> - Hotsos Symposium 2004, March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Tracy Rahmlow
> Sent: Tuesday, July 15, 2003 5:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Based on Cary's paper regarding when to use an index, would there not be
> value
> in having index tablespaces with a smaller block size vs tables using a
> larger
> block size?
>
>
>
>
>
>
>
> AM PST
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: ml-errors_at_fatcity.com
>
>
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> cc:
>
>
> The thing that occurred to me a few years ago (as a result of a test
> designed by Craig Shallahamer) is that "what disks do" gets very, very
> complicated when you add users. On any system busy enough to have a
> performance problem, the odds are usually slim that a disk is just
"sitting
> there" waiting for your next I/O call. On a busy system, someone else's
I/O
> call is almost always going to intercede between two of *your* I/O calls.
>
> As has been said many times, many ways...
>
> - DO separate tables and indexes into different tablespaces. There are
lots
> of reasons you should do this.
>
> - DON'T necessarily feel that you have to put the index and data
tablespaces
> on different devices. One decision criterion is performance: don't ever
put
> two files on the same device if the sum of their I/O-per-second rates
> exceeds the I/O-per-second capacity of the device. Another decision
> criterion is availability: don't ever put more data on a device than you
can
> recover in your acceptable downtime window. The list goes on...
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Washington, Denver, Sydney
> - Hotsos Symposium 2004, March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Daniel Fink
> Sent: Tuesday, July 15, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
>
> I may be way off base here, so any gurus please correct me with a gentle
> slap to the back of the head...
>
> Index and table access is not as simple as index entry..table row..index
> entry..table row..etc. I just ran a quick test (which may not be
> represntative and is using the primary key which can be understood as the
> row number in physical order of the data blocks) and I found (using the
> sequence of wait events) that there was substantial access to the index
> datafiles initially, followed by substantial access to the data datafiles.
> Then another single access to index, multiple access to data, single
> access to index, multiple access to data. It seems to me that this pattern
> is read several index blocks, then access several data blocks, read
several
> index blocks, access several data blocks. This may be due to the
sequential
> nature of the pk in the data blocks. It seems that the most efficient
> algorithm is to read enough index blocks to set up a list of data blocks
to
> read, then go get them. Since you have the index block pinned, don't waste
> any resource in releasing the pin to pin the data
> blocks, then repin the index block.
>
> The other issue is that indexes can be accessed using multiblock reads
> (index fast full scan) and tables can be indexed using single block reads
> (table access by rowid).
>
>
> Garry Gillies wrote:
> >
> > It's hot here. I wish I was at the beach and I feel like a rant.
> >
> > "oracle actually accesses indexes and tables serially"
> >
> > Is it just me or is this blindingly obvious?
> > You cannot access the table data until you have completed accessing the
> > index data
> > because the index data contains the location of the table data.
> >
> > During an indexed query on a single table the index will be accessed,
then
> > the table,
> > then the index,then the table, then the index,then the table then the
> > index,then the table.
> > If the index and the table are on the same disk then a lot of time will
be
> > taken up by
> > head seek movement.
> > If they are on the different disks then the "index" heads can locate
their
> > data and stay
> > there - and the "data" heads can locate their data and stay there.
> > Less head movement, less wasted time.
> >
> > That is the argument for what it is worth. Real life is of course vastly
> > more complex than
> > this and we are swimming in very muddy waters, which is why there is so
> > much
> > argument on the subject (raid salesmen - spit).
> >
> > Thanks for the vent
> >
> > Garry Gillies
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
>
> American Express made the following
> annotations on 07/15/2003 02:15:07 PM
> --------------------------------------------------------------------------
--Received on Tue Jul 15 2003 - 17:49:11 CDT
> --
>
****************************************************************************
> **
>
> "This message and any attachments are solely for the intended
recipient
> and may contain confidential or privileged information. If you are not the
> intended recipient, any disclosure, copying, use, or distribution of the
> information included in this message and any attachments is prohibited.
If
> you have received this communication in error, please notify us by reply
> e-mail and immediately and permanently delete this message and any
> attachments. Thank you."
>
>
****************************************************************************
> **
>
>
>
============================================================================
> ==
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tracy Rahmlow
> INET: tracy.rahmlow_at_aexp.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 |
 |