And just to add my bit...the possible performance
gains with various blocksizes under various conditions
all assume that your underlying OS layer provides good
support for what you want to do.
Whilst hardly being definitive, I remember (a few
years ago now) a few colleagues of mine had access to
a complete benchmarking suite of tools - as we were
migrating a mainframe ERP product to oracle (on sun).
So whilst we had the benchmarking gear, we had all
sorts of fun with different db and os configs...Bottom
line was, if you were purely on raw, then choosing the
right blocksize was a function of how you intended
oracle to be used - in our case (for the anticipated
number of concurrent users), 4k outperformed the
higher block sizes. For any non-raw test we could
come up with (vxfs, ufs, ufs direct), the moment the
oracle block size did not match the file system
blocksize, things went to pot.
So unless you intend totally avoiding a file system
layer (something I'm personally a big fan of), a
oracle blocksize decision may well be something that
is not within the realms of the database anyway - its
an OS issue.
Cheers
Connor
- Cary Millsap <cary.millsap_at_hotsos.com> wrote: >
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......
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, December 17, 2002 10:33 PM
>
>
> > 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 .
> >
> > Following Article advocates HIGHER Block Size for
> Index , Strangely :-
> >
> >
>
http://www.tusc.com/oracle/download/author.html#loneyk
> >
> > What may be the Commonly followed Best practice
> for DB_BLOCK_SIZE for
> Index Tablespaces in 9.2 ?
> >
> > Thanks
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, December 18, 2002 12:14 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I can't point to a paper or article on that, but
> based on tests I have
> done,
> > the block size of index tablespaces for a high
> volume transaction
> system
> > should be lower than the table block sizes. 2000
> tps with 25 changes
> each
> > does not sound like a really huge transaction
> rate. You could set the
> > tables' block size as 8k and indexes' as 4k.
> >
> > The reason for smaller block sizes in index is
> simple - to reduce the
> > dreaded buffer busy waits that will inevitably
> happen for this type of
> > transaction rate. A smaller block size will make
> sure less number of
> rows
> > are in a block - making the chance that two
> sessions are trying to get
> the
> > same buffer low.
> >
> > Caveat - if your database has a large percentage
> of DSS type quries
> (you
> > mentioned hybrid), then they will suffer slightly
> as the block gets
> will
> > increase.
> >
> > HTH
> >
> > Arup Nanda
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, December 17, 2002 11:28 AM
> >
> >
> > >
> > > What is the Best Practice for DB_BLOCK_SIZE for
> Index Tablespaces
> > Undergoing High Volume of Insert , Update ,
> Selects ? Why ?
> > >
> > > Any Docs , Links for the Same ?
> > >
> > >
> > > NOTE - This is for a Hybrid Application
> Benchmark undergoing 2000
> Banking
> > Transactions per second
> > > approx.
> > >
> > > OLTP Transaction sample = 1 Cash Deposit
> Transaction undergoing
> Insert
> > Update Delete Operations on Total of 5 Tables Each
> having About 5
> Indexes
> > >
> > > Thanks
> > >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: VIVEK_SHARMA
> > INET: VIVEK_SHARMA_at_infosys.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: 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).
>
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Fri Dec 20 2002 - 03:18:42 CST
