| 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: db block Size for Indexes Tablespaces in 9.2 ?
Please pardon the forthcoming non-quantitative response...
<RESPONSE>
Yaaaaaaaaaaaaay!
</RESPONSE>
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas
-----Original Message-----
Sent: Thursday, December 19, 2002 10:49 PM
To: Multiple recipients of list ORACLE-L
Please pardon the forthcoming rant...
<RANT>
Block size is one of the last things to consider in tuning indexes.
Yes,
it's a knob that can be twiddled, but it should be one of those
out-of-the-way knobs that hardly ever get touched. It's much the same
as
adjusting DB_BLOCK_BUFFERS in order to improve performance -- after a
certain point you are just rearranging deck chairs on the Titanic (i.e.
waste of time and energy)...
This list is for education, and it is important to understand how new
features (like multiple block sizes and the multiple cache sizes to
support
them) can be used, so the discussion is useful. But these particular
features are essentially intended for enabling transportable tablespaces
between databases with differing block sizes. Not performance tuning.
If
they were actually intended for performance tuning, then why aren't
there
KEEP and RECYCLE buffer pools for each block size, to make them
equivalent
to the "default" buffer cache? How difficult would it have been to have
those features in the other block size?
Of course, this is not to say that a feature can't be adapted to
purposes
for which it wasn't intended, but it is food for thought, something to
keep
in mind...
Time is more profitably spent ensuring that indexes are built only on
columns that have data with high selectivity, ensuring that they are
rebuilt
periodically or that REVERSE indexes are used for
monotonically-ascending
data, that column-level CBO statistics are gathered where data skew
exists,
that indexes on columns with low selectively are used only in selecting
"unpopular" data values, etc. After all these issues are resolved, then
decisions about block size *might* produce a noticeable result. Tuning
the
block size for certain sets of indexes might yield a marginal
improvement in
performance at the very most, but shrewd SQL tuning commonly produces
improvements of hundred-fold or thousand-fold, at the very least.
Efficient
SQL and wisely chosen indexes will beat out the perfect block size, any
day
of the week...
</RANT>
OK, I'm better now...
......Mmmmm......donuts......
> Hi Arup , List
>
> Your point is Correct about High "buffer busy wait" Contention During
Large OLTP Insert /Updates.
>
> High "buffer busy wait" on Corresponding INDEX during INSERT
Operations
was Observed
> during our previous benchmark which overcame by Converting to REVERSE
Index as the Field Value
> was Sequentially Increasing .
>
>
>
> >
> >
>
>
>
>
> >
>> To REMOVE yourself from this mailing list, send an E-Mail message
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim_at_SageLogix.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 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).Received on Thu Dec 19 2002 - 23:38:40 CST
 |
 |