Myth revisited ...
Hans Forbrich
I know (and agree with) the fundemental discussion that separating
indexes and tables into separate tablespaces should not be done for
performance reasons - in pre-Oracle9i environments!
However, with Oracle9i and it's support for multiple block sizes: Is
there a possible performance benefit to be obtained by placing the
tables and [some] indexes in separate tablespaces, IF the tablespaces
have different blocksizes?
(If this has been previously discussed, please just point me to the
approximate time frame so I can review the archives.)
/Hans
Daniel Morgan
Hans Forbrich wrote:
> At the risk of being shot, drawn and quartered:
Make that sliced and diced. Quartered is too generous.
> I know (and agree with) the fundemental discussion that separating
> indexes and tables into separate tablespaces should not be done for
> performance reasons - in pre-Oracle9i environments!
>
> However, with Oracle9i and it's support for multiple block sizes: Is
> there a possible performance benefit to be obtained by placing the
> tables and [some] indexes in separate tablespaces, IF the tablespaces
> have different blocksizes?
Well those that approach this with religious zeal will likely weight in
but I suspect we should all suspend judgement until Jonathan, Richard,
or someone else with more time than I have actually benchmarks it.
I have to confess it is something I've not contemplated and I can, at
least in theory, see some possibilities. Hmmmm.
> (If this has been previously discussed, please just point me to the
> approximate time frame so I can review the archives.)
>
> /Hans
I think you've stirred up some brand new trouble not previously added to
the stew. I like it. A little salt, pepper, and a Bay leaf and I might
like it even better.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
nobody
performance sakes, but how then if its not worse why not do it just for
naming sakes convention.
"Hans Forbrich" <forb...@yahoo.net> wrote in message
news:3FB6E237...@yahoo.net...
Hans Forbrich
>
> okay, it may be no better to place your indexes in a seperate database for
> performance sakes, but how then if its not worse why not do it just for
> naming sakes convention.
I assume your reference to 'database' was just a typo and you really
meant tablespace.
The discussions to this point basically say: go ahead and separate
indexes and tables to your heart's content, just don't do it because of
performance. Separate for reasons such as administration,
backup/recovery, management, symmetry on the wall chart, because it
looks pretty when your drunk - any other reason is valid except
performance! (Well almost any other reason.)
That said: I have no idea what you mean by 'naming sakes convention'.
No insult intended, but that sounds like it's derived from a
SQL*Server-ism.
Noons
ago. Funny how these things come around.
The consensus seemed to be you can get a measure
of better indexing (less b-tree levels) if you put
the index in a tablespace with a large block size.
Therefore reducing I/O even more for the indexes.
I can't remember if there were any caveats on key sizes
and complexity. In any case, something I'd consider
only after a lot of experimentation and testing.
As for tables in tablespaces of larger block sizes,
I can see an advantage when dealing with LOBs: you get
(hopefully) more "in-lined" rows. It also may be
advantageous for IOTs.
Again: very hard to say it will work everytime.
Highly dependent on workloads, blah-blah-blah.
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
"Hans Forbrich" <forb...@yahoo.net> wrote in message news:3FB6E237...@yahoo.net...
Hans Forbrich
>
> There was a thread on Oracle-L about this a few weeks
> ago. Funny how these things come around.
>
Guess I'll have to search for it. Thanks for the lead.
> The consensus seemed to be you can get a measure
> of better indexing (less b-tree levels) if you put
> the index in a tablespace with a large block size.
> Therefore reducing I/O even more for the indexes.
>
> I can't remember if there were any caveats on key sizes
> and complexity. In any case, something I'd consider
> only after a lot of experimentation and testing.
>
I agree that this entire case is subject to a lot of skull sweat and may
be useless except in extreme cases.
Understading the caveats would be mandatory. I suspect that there would
be a mix of tablespace block sizes required. Gut feeling says the
optimum mix would likely be where the number of levels & leaves per
block are roughly similar regardless of size/complexity.
> As for tables in tablespaces of larger block sizes,
> I can see an advantage when dealing with LOBs: you get
> (hopefully) more "in-lined" rows. It also may be
> advantageous for IOTs.
>
I also see advantages for inline collections and some complex object
types.
IOT makes sense in the same way as plain index.
> Again: very hard to say it will work everytime.
> Highly dependent on workloads, blah-blah-blah.
Of course. I also expect certain combinations will provide a negative
impact.
Also am interested whether this is a 'bag of tricks' thing or whether
the myth-buster statement holds for the mixed block size environment.
Niall Litchfield
news:3fb74440$1$13634$afc3...@news.optusnet.com.au...
> There was a thread on Oracle-L about this a few weeks
> ago. Funny how these things come around.
>
> The consensus seemed to be you can get a measure
> of better indexing (less b-tree levels) if you put
> the index in a tablespace with a large block size.
> Therefore reducing I/O even more for the indexes.
http://www.ixora.com.au/tips/block_size.htm
has a pretty good discussion on large block size and indexed access paths.
I'm not sure how realistic it would be to expect b-level reduction for most
indexes but for range scans and the like the argument would seem to hold
well.
> As for tables in tablespaces of larger block sizes,
> I can see an advantage when dealing with LOBs: you get
> (hopefully) more "in-lined" rows. It also may be
> advantageous for IOTs.
I'm not sure that I necessarily think that in-line LOBS are *ever* a good
idea. ISTM that in nearly all the cases where an inline LOB would be
appropriate, then a Varchar field would probably be a better thing to do.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
nobody
consuming beer.
"Hans Forbrich" <forb...@yahoo.net> wrote in message
news:3FB6FB8D...@yahoo.net...
Hans Forbrich
>
> right on both counts Hans.. guess I should only answer questions before
> consuming beer.
Or let us know how many we need to consume to stay synchronized.
Holger Baer
Well, if it means anything to you, Don Burleson promotes it:
http://www.oracle-training.cc/oracle_tips_block_sizes.htm
(I believe I once read a paper on dbazine.com, also by Don, you might
want to search there, too).
Anyway, even Oracle makes use of multiple blocksizes in TPC-Benchmarks,
exactly to eliminate the need for an overflow segment as Nuno suggested
in his post.
I once asked Tom Kyte on asktom about this, however his answer never
got published, so I can't point you there. But he didn't see much point
in using multiple blocksizes.
Holger
Noons
news:3fb7d87d$0$3347$cc9e...@news.dial.pipex.com...
>
> I'm not sure that I necessarily think that in-line LOBS are *ever* a good
> idea. ISTM that in nearly all the cases where an inline LOB would be
> appropriate, then a Varchar field would probably be a better thing to do.
Oh, I kinda know EXACTLY why are they a good idea:
you don't ALWAYS get a 2Gb LOB. And they aren't always
very large. That's the definition of variable.
So, in-line ones are quite handy. Particularly since the
access to these for nearly all operations is twice as fast
at least. If not more.
Richard Foote
I hate to be a party poop, but there's a fundamental issue that everyone has
missed thus far.
The vast majority of Oracle databases sit on top of O/S file systems and
file systems use buffered I/O (unless direct I/O is set). Therefore,
although having large block sizes for indexes sounds like a great idea in
principle, in practice it will actually *hurt* performance. The correct,
optimal block size for these environments is the I/O buffer size. Setting
the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
would result in every logical I/O on the index requiring two O/S calls.
Having double (or more) O/S calls per logical read on your indexes is going
to have a detrimental effect, even if such larger blocks succeed in reducing
the index's overall height.
The whole thing is counter productive and will have a negative impact on
overall performance. The concept of having multiple block sizes to aid
*performance* is generally questionable. Multi block sizes are there to aid
transportable tablespaces, not performance per se.
That said, there are *very* specific examples where multi sized blocks may
provide some benefit. Interestingly going the other way, small block sizes
for many numbers of tiny sized, read only lookup tables (smaller than a
'small' block, say 2K) as commonly defined in large financials type
applications could be advantages, but I emphasize the benefits are not
generally significant. And any benefits in reading and effectively caching
such data needs to be carefully balanced with any writes that may occur
(hence why R/O is better).
With raw I/O, larger is nearly always better. Not necessarily just for
indexes, but for larger row tables, ASSM objects, undo segments, higher data
density, etc.
In summary, using multi sized blocks to aid performance, especially in
buffered I/O environments is likely to end in tears.
Cheers
Richard
Noons
>
> The vast majority of Oracle databases sit on top of O/S file systems and
> file systems use buffered I/O (unless direct I/O is set). Therefore,
> although having large block sizes for indexes sounds like a great idea in
> principle, in practice it will actually *hurt* performance. The correct,
> optimal block size for these environments is the I/O buffer size. Setting
> the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
> would result in every logical I/O on the index requiring two O/S calls.
> Having double (or more) O/S calls per logical read on your indexes is going
> to have a detrimental effect, even if such larger blocks succeed in reducing
> the index's overall height.
Whoa boss! Are you telling me Oracle will divide the block size by the
file system block size and issue multiple calls?
Because if it does the usual read() or write() call, all it needs
to indicate is that it wants 16K and it's up to the OS to do two
I/O requests (which are NOT the same as two calls from Oracle) to
get those 16K, assuming of course 8k file system size.
Otherwise you'd be limited on I/O size to the file system block size
on each OS call, which is not true at all! You can issue in a single
read()/write() call quite large requests. Without any major context
switching needed.
So yes, in the example above you get 2 I/O calls, but initiated
by the file systems code in the OS itself, which is quite efficient
and very close to a readv() in speed. We talking low level I/O
calls, the kind you can't do unless you're using raw.
What is terribly inefficient is to request an I/O size that is
not an exact multiple of the file system block size. For obvious
reasons.
But to issue a call that asks for twice the file system block size
(or three or four times, for that matter) is not a killer by
any stretch of the imag.
Of course, raw would be better. But fs is not THAT bad!
Unless of course your fs is totally fragged and the two blocks
are on opposite ends of the partition...
> In summary, using multi sized blocks to aid performance, especially in
> buffered I/O environments is likely to end in tears.
Quite true.
Niall Litchfield
news:9u3ub.14700$aT....@news-server.bigpond.net.au...
> news:3FB6E237...@yahoo.net...
> > At the risk of being shot, drawn and quartered:
> >
> > I know (and agree with) the fundemental discussion that separating
> > indexes and tables into separate tablespaces should not be done for
> > performance reasons - in pre-Oracle9i environments!
> >
> > However, with Oracle9i and it's support for multiple block sizes: Is
> > there a possible performance benefit to be obtained by placing the
> > tables and [some] indexes in separate tablespaces, IF the tablespaces
> > have different blocksizes?
> >
> > (If this has been previously discussed, please just point me to the
> > approximate time frame so I can review the archives.)
> >
> Hi Hans,
>
> I hate to be a party poop, but there's a fundamental issue that everyone
has
> missed thus far.
>
> The vast majority of Oracle databases sit on top of O/S file systems and
> file systems use buffered I/O (unless direct I/O is set).
see I've always said you should use windows <g,d&r>
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
Jonathan Lewis
I don't think multiple block sizes really changes
the argument. I think the line that was generally
accepted after the last round of discussion was
that you separated objects because of different
characteristic behaviour.
If it is the characteristic of an object that it's
behaviour would benefit from being in a tablespace
with a different block size, then it goes in a
different tablespace. It's just another dimension
to categorising objects and their behaviour
patterns.
I would object very strongly to any ideas like:
Separate tables from indexes because
tables should be in 8K blocks and indexes
should be in 16K blocks.
which is the sort of area that people would be
heading towards if they got carried away with
the question you raised.
--
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
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
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
"Hans Forbrich" <forb...@yahoo.net> wrote in message
news:3FB6E237...@yahoo.net...
Jonathan Lewis
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
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
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
"Holger Baer" <holge...@science-computing.de> wrote in message
news:bpa1e8$2cp$1...@news.BelWue.DE...
>
> Well, if it means anything to you, Don Burleson promotes it:
>
> http://www.oracle-training.cc/oracle_tips_block_sizes.htm
>
> (I believe I once read a paper on dbazine.com, also by Don, you
might
> want to search there, too).
>
You mean if he says it twice is must be true ?
> Anyway, even Oracle makes use of multiple blocksizes in
TPC-Benchmarks,
> exactly to eliminate the need for an overflow segment as Nuno
suggested
> in his post.
There are always special cases where a little extra edge can
be found by doing something that is irrelevant to the rest of
the world. I suspect TPC benchmarks fall into the category
of 'tune it properly, then see if we can squeeze a little more'.
>
> I once asked Tom Kyte on asktom about this, however his answer never
> got published, so I can't point you there. But he didn't see much
point
> in using multiple blocksizes.
>
I had a quick look at the article - Don Burleson suggests that he
has six GENERAL rules: I think you should read this to mean
'here's a point to consider if you think your requirement match',
not 'this is a good idea, get the blinkers on'.
In particular:
1) Why should large tables that experience tablescans go
into the largest possible block size ? If I am doing such
regular tablescans that it makes a difference , then (a) should
I be tuning the code to reduce tablescans before I fiddle with
block sizes, or (b) should I be using parallel query - which
bypasses the buffer anyway and (c) how much single block
I/O should I be doing before the suggestion should be ignored ?
2) Seems to be saying that any table that gets hit with
large tablescans regularly should be in the recycle pool -
but this means EVERY i/o to those table is lost fairly
rapidly, and you might want to keep some of them.
I'll agree with the general principle, though, that if an
object is damaging the general effectiveness of your
cache then you should think about putting it into it's
own section of cache until you can fix the problem
properly.
3) In a well-tuned system, you do not want the
data dictionary cached in the db_cache_size - that's
what the v$rowcache and v$library_cache areas are
for. If you're depending on the buffer pool to minimise
the cost of acquiring object definition data, then you
need to do something more important than adjust the
block size.
4) Indexes will NOT always favour large block sizes.
If you double the size of an index block, then you double
the activity on the block, which means you double the
latching on the block. Remember what happened in
8.1.7.0 when a bug caused root block latching to go
over the top. Even in the absence of updates, introducing
a larger block size for indexes could produce a performance
problem. (There are other reasons why this should not
be considered a generally sound rule - but this is a good
starter for 10).
5) The block size for a table should, indeed, be larger
than the average row length - if the design is right. But
if someone showed me a system with 7 CLOBs in the
table definition, I wouldn't suggest that they create the
table in a 32K block size - I'd ask if it was a suitable
design, and then ask if the CLOBs should be stored
out of line, and then worry about damage limitation.
6) (a) The TEMP tablespace has to have the same
block size as the SYSTEM tablespace. (b) The sorting
happens in memory as dictated by sort_area_size or
pga_aggregate_target. (c) The larger the I/O, the more
space from the sort_area_size you have to allocate
to disk buffers for TEMP segment reads and writes,
hence the less memory you have for sorting and the
smaller the number of sort runs you can merge
concurrently, hence the more likely you are to increase
the sort costs if you choose a large block size without
adjusting your memory allocation accordingly. And if
you increase the memory to cater for very large block
sizes, then you encourage the optimizer to use
sort/merge joins and hash joins when they might not
be the most appropriate thing.
Joel Garry
> I don't think multiple block sizes really changes
> the argument. I think the line that was generally
> accepted after the last round of discussion was
> that you separated objects because of different
> characteristic behaviour.
>
> If it is the characteristic of an object that it's
> behaviour would benefit from being in a tablespace
> with a different block size, then it goes in a
> different tablespace. It's just another dimension
> to categorising objects and their behaviour
> patterns.
I can't help but think that a major characteristic of indices is
schizoid behavior. How do you categorize something that sometimes is
typically online, gimme-that-particular block then other times is
range scanned? While that is true of tables too, don't indices push
this enough further (on density, if nothing else) to be qualitatively
different?
It's tempting to think indices should have their own ts for
performance, _because_ they are different in this way. Being able to
identify index-specific hot-spots would be a bonus.
>
> I would object very strongly to any ideas like:
> Separate tables from indexes because
> tables should be in 8K blocks and indexes
> should be in 16K blocks.
> which is the sort of area that people would be
> heading towards if they got carried away with
> the question you raised.
>
>
Thanks especially for the other post pointing out why not to use 32K
index blocks and that it might detrimentally affect CBO sort/merge
decisions.
jg
--
@home.com is bogus.
http://www.davemcnally.com/lyrics/KingCrimson/21stCENTURYSCHIZOIDMANincludingMirrors.asp
Yong Huang
I was going to post a response to Richard's message and saw yours.
You're quite right. When db_block_size is 16k for instance, the Oracle
process issues one single system call that asks for 16k read or write,
even though the filesystem block size (I/O chunk size) is another
number, e.g. 8k. Richard may be misreading Steve Adams' original
db_block_size article.
Regardless what theory proposed by whom, I only trust real stories or
good benchmarks. I have a feeling that in many typical cases, using a
db_block_size twice as big as filesystem block size yields better
performance than using a db_block_size matching filesystem block size.
Bert Scalzo's article shows that 8k is definitely better than 4k on
ext2 (www.linuxjournal.com/article.php?sid=5840&mode=thread&order=0).
It's very likely his filesystem is 4k but we can confirm with him.
Yong Huang
ctc...@hotmail.com
>
> I'm not sure that I necessarily think that in-line LOBS are *ever* a good
> idea. ISTM that in nearly all the cases where an inline LOB would be
> appropriate, then a Varchar field would probably be a better thing to do.
I have a column where 99% of the entries are less than 4K, but I wish
it to work 100% of the time. An inline LOB seems to be a pretty good
trade-off.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service New Rate! $9.95/Month 50GB
Richard Foote
> >
> > The vast majority of Oracle databases sit on top of O/S file systems and
> > file systems use buffered I/O (unless direct I/O is set). Therefore,
> > although having large block sizes for indexes sounds like a great idea in
> > principle, in practice it will actually *hurt* performance. The correct,
> > optimal block size for these environments is the I/O buffer size. Setting
> > the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
> > would result in every logical I/O on the index requiring two O/S calls.
> > Having double (or more) O/S calls per logical read on your indexes is going
> > to have a detrimental effect, even if such larger blocks succeed in reducing
> > the index's overall height.
>
> Whoa boss! Are you telling me Oracle will divide the block size by the
> file system block size and issue multiple calls?
>
Hi Nuno,
No !!!
Re-reading my post, I'm certainly not particularly clear. I meant the
OS performs two I/O calls, not Oracle !!
Although small on it's own, it's a preventable overhead per *every*
I/O request that will likely cancel out any benefits and cause
scalability issues in the long run.
Cheers
Richard
Holger Baer
Surely not. I mentioned dbazine.com because I seem to remember the article
was a bit more elaborate, however none of them made much sense to me. Perhaps
I should have added more of a personal opinion in my first post.
My impression was that if a large enough quantity of my database would profit
by a larger blocksize, so be the whole database of the size. No need to fiddle
with different sizes.
>
>
>
>>Anyway, even Oracle makes use of multiple blocksizes in
>
> TPC-Benchmarks,
>
>>exactly to eliminate the need for an overflow segment as Nuno
>
> suggested
>
>>in his post.
>
>
> There are always special cases where a little extra edge can
> be found by doing something that is irrelevant to the rest of
> the world. I suspect TPC benchmarks fall into the category
> of 'tune it properly, then see if we can squeeze a little more'.
>
Spot on. Thats what I meant: In very very special cases there might
be a measurable effect, however normally we're dealing with real life.
Thank you for your detailed explanation.
Holger
Jonathan Lewis
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
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
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
"Holger Baer" <holge...@science-computing.de> wrote in message
news:bpceis$rn$1...@news.BelWue.DE...
> Jonathan Lewis wrote:
> >
> > You mean if he says it twice is must be true ?
>
> Surely not. I mentioned dbazine.com because I seem to remember the
article
> was a bit more elaborate, however none of them made much sense to
me. Perhaps
> I should have added more of a personal opinion in my first post.
>
Apologies - I should have put one of those 'tongue in cheek' icons
there. The comment was not intended seriously.
Noons
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message news:bpaocc$ov9$1$8302...@news.demon.co.uk...
Niall Litchfield
standard store elsewhere LOBs and there must be a tradeoff somewhere in
between. And assuming that Nuno is correct about the performance (I haven't
tested) it probably depends on the nature of the data access. I guess I was
probably assuming that generally LOBS are of *about* the same size, i.e you
are storing 8k images or 100mb video streams but not both in one column.
This is almost certainly a bad assumption.
So um I guess what I am saying is that my gut reaction is almost certainly
100% wrong and 'it depends on data,access, etc etc' is in fact correct.
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
<ctc...@hotmail.com> wrote in message
news:20031117190936.052$U...@newsreader.com...
Noons
news:3fb9f03d$0$265$ed9e...@reading.news.pipex.net...
> between. And assuming that Nuno is correct about the performance (I haven't
> tested) it probably depends on the nature of the data access. I guess I was
Absolutely, nature and pattern of access is important.
What I noted with "out of line" (lack of a better term?)
storage is it's not guaranteed to be contiguous or as easy
to find as "inline" storage. After all, inline is just a
stroll down the block while the other means another index
follow-up and another block(s) read/written. Of course it
will be much slower, assuming same size of LOB. Once sizes
are different (inline small, outofline large), then times
compound even more for the outofline access.
The little experience I have with this is with CLOBs. It confirms
this. Inline ones have virtually no diff from varchar for normal
get/put access. Out of line are considerably slower even if size
difference is just borderline. This goes for both index access of
base row or FTS.
Geomancer
However, can we assume?
1. The on-the-margin cost of reading a 32k blocks vs. an 8k block is
VERY SMALL. (I can read a 4k block in 15ms, a 32k blocks in 16ms)
Hence we assert that thjere is less letency is reading data in 32k
chunks, BUT ONLY IF WE NEED THE ADJACENT ROWS.
2. It is a waster of RAM to read 32k to get a 30 byte row.
3 - OPS and RAC (according to experts) like SMALL blocksizes, to
increase granularity and reduce IDLM, and CF pinging.
Hans Forbrich
>
> I don't think multiple block sizes really changes
> the argument. I think the line that was generally
> accepted after the last round of discussion was
> that you separated objects because of different
> characteristic behaviour.
>
> If it is the characteristic of an object that it's
> behaviour would benefit from being in a tablespace
> with a different block size, then it goes in a
> different tablespace. It's just another dimension
> to categorising objects and their behaviour
> patterns.
>
> I would object very strongly to any ideas like:
> Separate tables from indexes because
> tables should be in 8K blocks and indexes
> should be in 16K blocks.
> which is the sort of area that people would be
> heading towards if they got carried away with
> the question you raised.
>
I like the way you redirect the discussion from block size to
charateristics. Forces one to review the object's existance
holistically.
I agree with your final paragraph - that's heading toward a new myth as
well. But I wonder whether the statement about "don't separate tables
and indexes - no performance benefit" isn't verging on the beginnings of
a new myth in itself.
(Total and unwavering belief in Myths and Patterns is for people with
limited imagination?? <g>)
Many thanks (to all) for looking at this.
/Hans
Geomancer
> limited imagination?? <g>)
Do you distinguish between Myths and Rule of Thumb?
ROT are aften false under special circumstances, but true most of the time. . . .
Mladen Gogala
Of course, you know that the good, old International Business Machines
have an excellent file system called JFS which supports both async I/O
and direct I/O both on AIX and Linux, so that if you set
filesystemio_options parameter to "setall" (both direct and async I/O),
things will work almost as if it was a raw device? I am against larger
blocks for indexes, but I do strongly advocate larger blocks for the
tables that are frequently read by using full table scan and don't have
much update activiy on them.
--
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)
Mladen Gogala
>> The vast majority of Oracle databases sit on top of O/S file systems and
>> file systems use buffered I/O (unless direct I/O is set).
>
> see I've always said you should use windows <g,d&r>
Boooo!
Galen Boyer
> because it looks pretty when your drunk
I know I'm now a fully graduated Oracle geek. I do think my
sqlplus session is pretty when I'm drunk, and if it isn't I
either drink more or caress the fontsizes.
--
Galen Boyer
Hans Forbrich
Know what you mean. It's beautiful the way some of those fonts can just
spin and twirl. And the colors ......
Paul
pharfr...@hotmail.com says...
> Do you distinguish between Myths and Rule of Thumb?
> ROT are aften false under special circumstances, but true most of the time. . . .
Is that not the definition of a rule of thumb?
Paul...
--
plinehan x__AT__x yahoo x__DOT__x com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.
Paul Drake
> right on both counts Hans.. guess I should only answer questions before
> consuming beer.
where is the fun in that?
Its not like you have to put a block dump in _every_ post ... :)
Pd