Oracle's Myth: keep tables and indexes in separate tablespaces
Snid
indexes in separate tablespaces comes from. I know that this topic has been
done to death, but I've just been reading through Oracles own teaching guide
titled:
Enterprise DBA Part 1A: Architecture and Administration
Volumn 1 . Student Guide
which was printed in August 1999
I see that in Lesson 11, Managing Tables, it gives the first guideline for
creating tables as,
"Place tables in a separate tablespace - not in the tablespace that has
rollback segments, temporary segments, and indexes."
So I guess I can see why lots of people think that it's best to separate
indexes from tables. It doesn't mention that you should only separate them
for administration purposes mainly.
Howard J. Rogers
>
> So I guess I can see why lots of people think that it's best to separate
> indexes from tables. It doesn't mention that you should only separate them
> for administration purposes mainly.
Quite right. The advice is still there, in plain, unadorned incorrectness,
in the performance tuning course notes for Oracle 9i (published in version
2.1 only a few weeks ago).
Oracle are also responsible for promoting the myth that getting a segment
into a single extent is good for performance; that PCTINCREASE 1 is a good
idea to protect against fragmentation (it causes it); that ASSM has no
costs; that tuning consists of getting a good buffer cache hit ratio. You
name it, they've touted it.
These days, they have TUSC to do that sort of thing for them.
Regards
HJR
--
--------------------------------------------
See my brand new website, soon to be full of
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------
Ryan Gaffuri
> Snid wrote:
>
> >
> > So I guess I can see why lots of people think that it's best to separate
> > indexes from tables. It doesn't mention that you should only separate them
> > for administration purposes mainly.
>
> Quite right. The advice is still there, in plain, unadorned incorrectness,
> in the performance tuning course notes for Oracle 9i (published in version
> 2.1 only a few weeks ago).
>
> Oracle are also responsible for promoting the myth that getting a segment
> into a single extent is good for performance; that PCTINCREASE 1 is a good
> idea to protect against fragmentation (it causes it); that ASSM has no
> costs; that tuning consists of getting a good buffer cache hit ratio. You
> name it, they've touted it.
>
> These days, they have TUSC to do that sort of thing for them.
>
> Regards
> HJR
what costs do ASSM have? Seems to me if your not careful you can still
end up with fragmentation. is that what you mean?
I thought seperating indexes and tables was first put into the OFA?
Not for performance reasons, but to make it easier to manage the
database?
Jitendra Patel
> > indexes from tables. It doesn't mention that you should only separate them
> > for administration purposes mainly.
No, Eienstein. (I guess we know why you are unemployed).
Imaging a system on a single disk, with the index on the innermost
cylinder and the data on the outermost cylinder.
During a index range scan, the disk will vibrate like unbalanced
washing machine.
Try it on your PC, and you can watch this first hand. (If you actually
own a computer, that is)
Yes, indexes belong on separate device, just like undo and online
redo.
A single disk cannot write to two placed at once.
Had you any "real" experience, you would know that.
I'll bet TUSC does not offer you work.
Yeesh.
Anurag Varma
"Jitendra Patel" <coldrai...@lycos.com> wrote in message news:7610580f.03100...@posting.google.com...
> > > So I guess I can see why lots of people think that it's best to separate
> > > indexes from tables. It doesn't mention that you should only separate them
> > > for administration purposes mainly.
>
> No, Eienstein. (I guess we know why you are unemployed).
>
> Imaging a system on a single disk, with the index on the innermost
> cylinder and the data on the outermost cylinder.
I'm "imaging" it. An unbalanced washing machine with your master Don's books in it.
>
> During a index range scan, the disk will vibrate like unbalanced
> washing machine.
>
> Try it on your PC, and you can watch this first hand. (If you actually
> own a computer, that is)
The argument is not about putting indexes and tables on one disk.
Read the topic of this thread now and tell us if you believe if 1 tablespace = 1 disk?
Read the threads in this newsgroup which debate this endlessly and then
think about it (If you actually own a brain, that is)
>
> Yes, indexes belong on separate device, just like undo and online
> redo.
>
> A single disk cannot write to two placed at once.
>
> Had you any "real" experience, you would know that.
>
> I'll bet TUSC does not offer you work.
>
> Yeesh.
Anurag
Noons
> Imaging a system on a single disk, with the index on the innermost
> cylinder and the data on the outermost cylinder.
>
> During a index range scan, the disk will vibrate like unbalanced
> washing machine.
REALLY? I'd say it is more than likely NOT in an index range
scan that you'll get the "washing machine effect"...
> A single disk cannot write to two placed at once.
Brilliant deduction. With a tremendously risky extrapolation,
I'd hazard a wild guess that it can't read from two places
at once either...
Yet, I thought the OP (and you) were talking about
"reading" index values and table values?
Since when is an index range scan a "write to two
placed at once"?
> Yeesh.
Indeed...
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
Jack
Actually Oracle recommends S.A.M.E and raid-10, and
they "would like to make it the ONLY configuration for Oracle".
Update your data.
"Snid" <sn...@snider.sno> wrote in message
news:bm2qs6$o1$1...@bunyip.cc.uq.edu.au...
Richard Foote
news:7610580f.03100...@posting.google.com...
Hi Jitendra,
As I posted only a few days ago, for some bizarre reason people confuse
"tables and indexes don't need to be separated for performance reasons" with
"it's OK to place all indexes and tables on one disk".
They hardly mean the same thing ?????
If you have a database with (say) 4 disks for your tables and (say) 4 disks
for your indexes thinking that you're somehow improving performance by
separating them, I'll bet you a cappuccino that by striping both your tables
and indexes together across all 8 disks, performance will not suddenly
plummet as a result.
All things being equal, it might actual *improve* performance.
BTW, did Howard accidentally scratch your car in some carpark ....
Cheers
Richard
Howard J. Rogers
>
> Actually Oracle recommends S.A.M.E and raid-10, and
> they "would like to make it the ONLY configuration for Oracle".
>
> Update your data.
>
Er, no they don't. When was the last performance tuning course you went on?
Or the last DBA Fundamentals course (where they still teach OFA)? The
recommendation the poster spoke about is still there in both of them. As of
last week.
Howard J. Rogers
> Hi Jitendra,
>
> As I posted only a few days ago, for some bizarre reason people confuse
> "tables and indexes don't need to be separated for performance reasons"
> with "it's OK to place all indexes and tables on one disk".
>
> They hardly mean the same thing ?????
>
> If you have a database with (say) 4 disks for your tables and (say) 4
> disks for your indexes thinking that you're somehow improving performance
> by separating them, I'll bet you a cappuccino that by striping both your
> tables and indexes together across all 8 disks, performance will not
> suddenly plummet as a result.
>
> All things being equal, it might actual *improve* performance.
>
> BTW, did Howard accidentally scratch your car in some carpark ....
>
> Cheers
>
> Richard
Funny thing is, Richard, Jitendra was replying to a post that wasn't even
mine! But I think he has a bee in his bonnet about the wonderful work that
Don and his mates at TUSC do, so he throws the occasional tantrum just to
let everyone know where his loyalties lie.
Jack
Look:
http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
You might have a look at Juan Loaiza's article "Optimal Storage
Configuration Made Easy" http://technet.oracle.com/deploy/performance/ for a
different approach to the problem. Since this is on technet, it appears to
be Oracle's latest recommendation.
"Howard J. Rogers" <h...@dizwell.com> wrote in message
news:3f857214$0$28118$afc3...@news.optusnet.com.au...
dario diano
I'm just movinng into the fashinating world of Oracle DB,
Can you explain me How creating a table and an index on this table on
the same tablespaces impact the performances...
Isn't it about concurrent phisical disk access(tablespaces are on one
phisical disk)????
Thanks in advance.
"Snid" <sn...@snider.sno> wrote in message news:<bm2qs6$o1$1...@bunyip.cc.uq.edu.au>...
Daniel Morgan
When did you first start to believe that Oracle and the operating system serialize disk access?
When did you first start to believe that a single disk on a workstation equated with a production Oracle database?
Your statement is absolutely incorrect. What needs to be spread across disk is I/O. Whether that I/O comes
from indexes or tables is irrelevant.
-- 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)
Howard J. Rogers
>
> Look:
> http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
>
> You might have a look at Juan Loaiza's article "Optimal Storage
> Configuration Made Easy" http://technet.oracle.com/deploy/performance/ for
> a
> different approach to the problem. Since this is on technet, it appears
> to be Oracle's latest recommendation.
Sorry Jack: you're wrong on this, though I suppose it depends on what you
mean by 'recommendation'.
Of course there are papers about RAID and SANE on their website. I'm not
disputing that. I'm not even disputing that RAID and SANE are jolly good
ideas and should be implemented wherever possible,
But what I am saying is that as of last week, their own Performance Tuning
course notes and their own DBA Fundamentals course notes state, in black
and white, to separate indexes from tables, and that performance benefits
will arise as a result.
We may agree to differ on what constitutes a recommendation, but as far as
I'm concerned, what they tell their instructors to teach to fee-paying
students is much more of an 'official' point of view than what freebie pdfs
they might have hanging around on their website.
That's all I'm saying. And all what the original poster was saying.
Though I will go one step further, though it's only a logical conclusion
from what's already been discussed: one should be very careful about making
statements about what Oracle recommends or what its 'position' on something
is. Because the right hand quite frequently doesn't agree with what the
left hand has been writing.
Daniel Morgan
Of course they had multiple heads and multiple platters unlike the current disk. ;-)
Yes folks that is sarcasm dripping off your monitor: Heavy sarcasm. I'm afraid Jitendra is so busy defening a
religion, TUSC, he has failed to take the time required to read what is being written.
Daniel Morgan
>pardon me...
>I'm just movinng into the fashinating world of Oracle DB,
>Can you explain me How creating a table and an index on this table on
>the same tablespaces impact the performances...
>Isn't it about concurrent phisical disk access(tablespaces are on one
>phisical disk)????
>
>Thanks in advance.
>
><snipped>
>
Apparently being new to Oracle hasn't hurt your ability to think. Glad
to have you here.
Some people apparently have a hard time contemplating a computer system
with more than a
single user performing more than one serialized transaction at a time
with an operating system
that performs based on rules not seen since DOS 1.0.
Noons
>
> Though I will go one step further, though it's only a logical conclusion
> from what's already been discussed: one should be very careful about making
> statements about what Oracle recommends or what its 'position' on something
> is. Because the right hand quite frequently doesn't agree with what the
> left hand has been writing.
>
Which reminded me of something:
Do you know of ANY documentary evidence
FROM Oracle about public synonyms being
considerably heavier on performance than
private ones or none?
I just had a minor argument at work about
precisely this and need to find something
"heavier" than SA's site...
Joel Garry
At least one hand is getting it right. From 9.2 performance manual
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/iodesign.htm#23329
:
-------------- Begin Inclusion -------------------
One popular approach to manual I/O distribution suggests separating a
frequently used table from its index. This is not correct. During the
course of a transaction, the index is read first, and then the table
is read. Because these I/Os occur sequentially, the table and index
can be stored on the same disk without contention. It is not
sufficient to separate a datafile simply because the datafile contains
indexes or table data. The decision to segregate a file should be made
only when the I/O rate for that file affects database performance.
-------------- End Inclusion ---------------------
jg
--
@home.com doesn't know what cox.net is doing.
"I've got one hand in my pocket, and the other is flashing a peace
sign." - whatshername
Howard J. Rogers
Having looked about a year ago for exactly that, I think you are out of
luck. It's the kind of detail no-one at Oracle would have the time or
inclination to test to get a definitive answer on.
Howard J. Rogers
A good catch, Joel. Trouble is....
> "Howard J. Rogers" <h...@dizwell.com> wrote in message
>> Though I will go one step further, though it's only a logical conclusion
>> from what's already been discussed: one should be very careful about
>> making statements about what Oracle recommends or what its 'position' on
>> something is. Because the right hand quite frequently doesn't agree with
>> what the left hand has been writing.
>
> At least one hand is getting it right. From 9.2 performance manual
>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/iodesign.htm#23329
> :
>
> -------------- Begin Inclusion -------------------
> One popular approach to manual I/O distribution suggests
....this is what is commonly called being "economical with the actualite"!
Why do they state "a popular approach suggests", as though it has nothing
to do with them? When it is in fact their own bloomin' courseware that
suggests it in the first place!!
But seriously: there is indeed better and better stuff coming out of Oracle
on these sorts of things these days. Their courseware on the buffer cache
hit ratio has improved out of all recognition since the dark days of 'make
it 95% or else'. I think it might have something to do with James Morle's
involvement, but that's just a guess.
It is therefore excellent seeing this quote you found in black and white...
hopefully, it will mean the same old question doesn't keep getting
re-hashed in the future.
Now, I can't wait for this nugget actually to filter into their classrooms
(without my having to abandon the courseware to get it there).
Regards
HJR
Mark Townsend
> Jack wrote:
>
>
>>Look:
>>http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
>>
>>You might have a look at Juan Loaiza's article "Optimal Storage
>>Configuration Made Easy" http://technet.oracle.com/deploy/performance/ for
>>a
>>different approach to the problem. Since this is on technet, it appears
>>to be Oracle's latest recommendation.
> Sorry Jack: you're wrong on this, though I suppose it depends on what you
> mean by 'recommendation'.
I don't believe that Jack is wrong. In fact, if the courses ARE teaching
it differently, then I think the course needs to be urgently updated -
can anybody send me a reference number, and I'll follow up on it.
Note that ASM (Automated Storage Management) in Oracle Database 10g
implements the SAME methodology as the preferred (and only supported)
best practice.
Howard J. Rogers
> Howard J. Rogers wrote:
>> Jack wrote:
>>
>>
>>>Look:
>>>http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
>>>
>>>You might have a look at Juan Loaiza's article "Optimal Storage
>>>Configuration Made Easy" http://technet.oracle.com/deploy/performance/
>>>for a
>>>different approach to the problem. Since this is on technet, it appears
>>>to be Oracle's latest recommendation.
>
>> Sorry Jack: you're wrong on this, though I suppose it depends on what you
>> mean by 'recommendation'.
>
> I don't believe that Jack is wrong.
It's not a matter of belief. If the course notes say it in black and white,
then he's wrong.
>In fact, if the courses ARE teaching
> it differently,
No, they've been teaching it in exactly the same way since at least version
8.0.
>then I think the course needs to be urgently updated -
> can anybody send me a reference number, and I'll follow up on it.
They just *have* been updated. Check out 9i Performance Tuning. They've just
released version 2.1 of that document, and it contains the 'separate tables
from indexes' "recommendation" in Chapter 3. I taught it just last week,
and my notes were version 2.0, and the students had version 2.1... that's
how recent the update of the material was... I didn't actually get told a
new revision had been issued. Fortunately, the changes were minor (and this
wasn't one of them).
I can't give you the course code, I'm afraid, because I haven't actually
cleared my desk out yet, so the manuals are still sitting in the office.
But I can promise you that Fundamentals I is still "recommending" the same
thing, too.
I only know it because every time I teach it I have to spend 15 minutes
explaining why the course notes are wrong, usually to a bunch of
initially-sceptical faces. And it's been that way for years, so I doubt
anything will change in a hurry.
If you care to wait until early next week, I can have the precise course
codes and page numbers for you (unless Richard wants to jump in
beforehand).
>
> Note that ASM (Automated Storage Management) in Oracle Database 10g
> implements the SAME methodology as the preferred (and only supported)
> best practice.
Oh dear. SAME isn't a magic bullet, either. Frying pan and fire time, I
suppose. Still it's better than nothing.
Connor McDonald
I thought Steve's proof was fairly definitive - more latching. If you
aren't getting on 'latch free' or CPU, then by all means, let the pub
syns fly. Otherwise, buyer beware.
C.
Geomancer
Um, had you made several caveats and been less insulting, your point
might have been better taken.
If the disk is not RAID striping (RAID10, 0+1, 5) then many of your
points may be correct.
In my experience with Oracle on Windows, I demonstrated significant
slowdown with the whole database on a single non-RAID disk.
When I moved indexes to a separate disk, undo to another and redo to a
4th disk, performance doubled!
Not sure why, maybe it's just a Windows thing.
Jack
Look Howard what You did say at 23 Jun 2002 :
LOL
------------------------------------------------
From: "Howard J. Rogers" <d...@hjrdba.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Basic question on RAID array / Tablespace configuration.
Date: Sun, 23 Jun 2002 00:33:46 +1000
There is precisely zero benefit in separating tables from their indexes for
performance reasons. Both are segments. Just like Table A and Table B are
both segments. Separate A from B by all means, but unless you're consistent,
there's no point in separating a table from its index.
Regards
HJR
Richard Foote
Hi Jack,
That's great, it really is, that's really really funny (excuse me while I
wipe tears away).
Ummmm, while I'm trying to compose myself here, could you please just remind
me exactly why it is so funny.
Howard's been consistent in what he's been saying, and .....
I feel all embarrassed now but I actually don't get it !!
?????
Richard
Howard J. Rogers
I'm missing your point Jack.
Yes those were my views in 2002. They've been my views for a long, long
time.
So how does that add weight to your argument that Oracle's official position
is the same as mine?
Or are you suggesting that when I wrote those words I was doing so as a
representative of Oracle? If so, that won't fly. I've never posted here as
an official Oracle representative.
My views are and have always been mine alone.
Richard Foote
Hi Howard,
I don't have the latest and greatest notes but the last set of 8i
Performance Tuning notes (30052GC10) does quote on P.8-5 "Tables and indexes
should be split into separate tablespaces because indexes and tables are
often inserted into and read from simultaneously." This was the advice these
courses have always recommended.
However, the last Performance Tuning notes I taught were the much improved
first 9i version (D11299GC11) which only mentions on the PowerPoint on page
6-6 quote "Split tables and indexes into separate tablespaces". It doesn't
actually mention why which is kinda of an improvement ;)
There is no doubt that Oracle itself is the biggest myth spreader of them
all. Just look at it's own OTN site where you can still read why
indexes/tablespaces should be split for performance, that Oracle indexes
become unbalanced over time and need frequent rebuilding, that you can SAME
including your redo logs with your datafiles with no performance issues
whatsoever, etc.....
Going back back back, *I* spread many of these myths myself as I didn't have
the confidence and experience to say otherwise. It was only after a year or
so of teaching and of investigating things for myself that I began to say,
"Ummm, actually this not quite right" and that I *really* began teaching.
There's an assumption that because one works for Oracle that you somehow
know the ins and outs of everything when in actual fact, many in Oracle
(especially outside the US) are just as "ignorant" (not sure that's quite
the word I'm after) and often only have access to the same information as
those outside of Oracle. The best way to actually learn how something works
is to experiment and learn for yourself. It might sound extraordinary but
it's true and many of those who write these documents do so by basing it on
existing documents, hence keeping many myths "alive" and by having only a
matter of weeks experience with the latest product or versions.
Oracle doco IMHO is actually quite good but it ain't perfect and just
because "Oracle" says it's so doesn't necessarily mean that it is so.
Cheers
Richard
Howard J. Rogers
>
> Hi Howard,
>
> I don't have the latest and greatest notes but the last set of 8i
> Performance Tuning notes (30052GC10) does quote on P.8-5 "Tables and
> indexes should be split into separate tablespaces because indexes and
> tables are often inserted into and read from simultaneously." This was the
> advice these courses have always recommended.
>
> However, the last Performance Tuning notes I taught were the much improved
> first 9i version (D11299GC11) which only mentions on the PowerPoint on
> page 6-6 quote "Split tables and indexes into separate tablespaces". It
> doesn't actually mention why which is kinda of an improvement ;)
Agreed. The new 9i notes mention it silently as a bullet point in the
chapter about I/O (which is now chapter 3, but used to be chapter 8). They
don't elaborate.
>
> There is no doubt that Oracle itself is the biggest myth spreader of them
> all. Just look at it's own OTN site where you can still read why
> indexes/tablespaces should be split for performance, that Oracle indexes
> become unbalanced over time and need frequent rebuilding, that you can
> SAME including your redo logs with your datafiles with no performance
> issues whatsoever, etc.....
>
> Going back back back, *I* spread many of these myths myself as I didn't
> have the confidence and experience to say otherwise. It was only after a
> year or so of teaching and of investigating things for myself that I began
> to say, "Ummm, actually this not quite right" and that I *really* began
> teaching. There's an assumption that because one works for Oracle that you
> somehow know the ins and outs of everything when in actual fact, many in
> Oracle (especially outside the US) are just as "ignorant" (not sure that's
> quite the word I'm after) and often only have access to the same
> information as those outside of Oracle. The best way to actually learn how
> something works is to experiment and learn for yourself. It might sound
> extraordinary but it's true and many of those who write these documents do
> so by basing it on existing documents, hence keeping many myths "alive"
> and by having only a matter of weeks experience with the latest product or
> versions.
Correct. In Oracle's defense there is an *enormous* quantity of material to
write, and tight teaching schedules within which to produce the course
material specifically, and it is no wonder that some of their course notes
have occasionally looked like a bad cut and paste job from the previous
version. Remember that slide for 8.1.5 Oracle that said you couldn't have
unique constraints on an IOT... straight lift out of the 8.0 material. And
totally wrong, of course. I can't blame them: that's tens of thousands of
hours to write it all from scratch. That's unfortunately why it's a bit of
a lottery as to which instructor you get, I think. The good ones do their
own research and can provide perspective and correction to the material
where needed (and generally, it's pretty good, so that's not all the time
by any means). But there are a lot that just regurgitate the material.
>
> Oracle doco IMHO is actually quite good but it ain't perfect and just
> because "Oracle" says it's so doesn't necessarily mean that it is so.
Entirely agree. And then go on to ask... what's your excuse for being up so
late???!!
Oh, don't bother answering that. I just saw the rugby score. Are you still
quite sober??
Regards
HJR
> Cheers
>
> Richard
Richard Foote
> Entirely agree. And then go on to ask... what's your excuse for being up
so
> late???!!
>
> Oh, don't bother answering that. I just saw the rugby score. Are you still
> quite sober??
>
Hiii Howeyy,
Caues Im soba, i onlllyy hadd a Few hic burp, dinks.
Gooo yu ossiesss ..
................
zzzzzzzzz
riHcrd
Alex Filonov
If anything, there is an opposite recommendation. See note 131272.1
on Metalink.
They state that prefixing (no synonyms) is the best approach (from
tuning point of view), public synonyms is the next best and private
synonyms is the worst possible approach.
Daniel Morgan
beating me with a large bag of sand so I went straight to bed.
But, a bit belatedly, a quick comment for Mark.
Oracle Corp. would do itself a huge favor if it gave far more attention to the content of what it teaches in its classes,
what it puts its name to on its various web sites, on the advice given out in its name by Oracle Press, and by some
person's associated with IOUG.
This is not to say that all is horrible and rotten. For the most part it is remarkably good. But there are some things
that seem to persist year after year, version after version, that are just not true. A quick review of postings here on
usenet with key words like "Myth" and "Mythology" will point you to some of the more blatantly obvious of them.
Daniel Morgan
Volker Hetzer
"Richard Foote" <richar...@bigpond.com> schrieb im Newsbeitrag news:Wczhb.145191$bo1.1...@news-server.bigpond.net.au...
> There is no doubt that Oracle itself is the biggest myth spreader of them
> all. Just look at it's own OTN site where you can still read why
> indexes/tablespaces should be split for performance, that Oracle indexes
> become unbalanced over time and need frequent rebuilding
Could you tell me more about it?
Because the Documentation on my 9i CD says
"Serializing within Indexes
Use of sequences, or timestamps, to generate key values that are indexed
themselves can lead to database hotspot problems, which affect response time and
throughput. This is usually the result of a monotonically growing key that results in
a right-growing index. To avoid this problem, try to generate keys that insert over
the full range of the index. This results in a well-balanced index that is more
scalable and space efficient. You can achieve this by using a reverse key index or
using a cycling sequence to prefix and sequence values."
So, presumably there are unbalanced indexes too. I mean, I can accept that there are all
sorts of docs on the otn but I always assumed that the delivered doc always has the final
word.
Lots of Greetings!
Volker
Anurag Varma
"Volker Hetzer" <volker...@ieee.org> wrote in message
news:bm6l3t$fdg$1...@news.fujitsu-siemens.com...
Volker,
It kinda depends on which way people define unbalanced. unbalanced to some
means vertical imbalance
and to some it means horizontal imbalance.
By horizontal imbalance (which might not be a word though :), its meant that
indexes might have more
entries in some leaf blocks and less in other.
Jonathan explains this very clearly in one of his papers:
http://www.dbazine.com/jlewis13.shtml
Anurag
Niall Litchfield
news:cf90fb89.03101...@posting.google.com...
> In my experience with Oracle on Windows, I demonstrated significant
> slowdown with the whole database on a single non-RAID disk.
>
> When I moved indexes to a separate disk, undo to another and redo to a
> 4th disk, performance doubled!
>
> Not sure why, maybe it's just a Windows thing.
Um, You seem to be saying that when you went from one to 4 disks/spindles
performance improved significantly. Well to misquote christine keeler "it
would wouldn't it" 4 disks for the IO is the significant factor not the
separation of tables and indexes. Nothing to do with windows and everything
to do with io split.
--
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
******************************************
Joel Garry
> "Howard J. Rogers" <h...@dizwell.com> wrote in message
> news:3f866670$0$29372$afc3...@news.optusnet.com.au...
> >
I wonder why people can't just submit a documentation correction
through support and have it work its way up to where it can be
corrected? Is Oracle support that much different than every other
vendor that it doesn't allow it? Do they just ignore the
documentation feedback in all the e-manuals? Is O education that far
off-planet?
>
> Oracle doco IMHO is actually quite good but it ain't perfect and just
> because "Oracle" says it's so doesn't necessarily mean that it is so.
It's getting harder to find really big errors! :-)
jg
--
@home.com
"Revenge is a dish best served cold." - Kill Bill Vol. 1, the latest
in a long line.
Howard J. Rogers
> Howard J. Rogers wrote:
>> Jack wrote:
>>
>>
>>>Look:
>>>http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
>>>
>>>You might have a look at Juan Loaiza's article "Optimal Storage
>>>Configuration Made Easy" http://technet.oracle.com/deploy/performance/
>>>for a
>>>different approach to the problem. Since this is on technet, it appears
>>>to be Oracle's latest recommendation.
>
>> Sorry Jack: you're wrong on this, though I suppose it depends on what you
>> mean by 'recommendation'.
>
> I don't believe that Jack is wrong. In fact, if the courses ARE teaching
> it differently, then I think the course needs to be urgently updated -
> can anybody send me a reference number, and I'll follow up on it.
OK, Mark. I found one reference (the manual was discovered after some
housework!). DBA Fundamentals I (course code: D11321GC20). Chapter 12, page
13.
Index Guidelines:
1. Balance query and DML needs
2. Place in seaparate tablespace
...and so on
The text for that page reads:
"Place indexes in a separate tablespace, not in a tablespace that has undo
segments, temporary segments or tables."
They don't (as Richard pointed out elsewhere) go on to elaborate as to *why*
they should be so separated, or what the basis of this recommendation is.
steve
(in message <9Hyhb.145121$bo1....@news-server.bigpond.net.au>):
I think we all need to be a little clearer on what we are saying.
when we are saying separate tables and indexes, do we mean:
1. in separate segmnents on the same disk.
2. in sequential disk blocks within the same oracle segment
3. on seperate disks on the same controller.
4. on separate disks on different controllers.
it seems that people are assuming different conditions, based on their
experience.
even the guy who thinks that you can only write to a disk in a singularity,
but perhaps hes only ever used single head, single analog port disk drives.
The training material is partly at fault for not clearly describing the
physical hardware configuration, without which we can all argue that oranges
look like apples.
Niall Litchfield
news:0001HW.BBADE608...@news.newsguy.com...
> I think we all need to be a little clearer on what we are saying.
>
> when we are saying separate tables and indexes, do we mean:
>
> 1. in separate segmnents on the same disk.
> 2. in sequential disk blocks within the same oracle segment
> 3. on seperate disks on the same controller.
> 4. on separate disks on different controllers.
Um well as tables and indexes are both types of segment 1 and 2 are
nonsensical statements. Did you perhaps mean tablespace or
something else instead of segment?
The myth is that just by putting data and indexes in seperate tablespaces
performance improves. This is demonstrably untrue. Putting contending
segments (which tables and their indexes may be) on different disks does
improve performance but this is not the same thing.
Noons
>
> In my experience with Oracle on Windows, I demonstrated significant
> slowdown with the whole database on a single non-RAID disk.
>
> When I moved indexes to a separate disk, undo to another and redo to a
> 4th disk, performance doubled!
>
> Not sure why, maybe it's just a Windows thing.
Well, you go from a single spindle to 4 and you not sure why? :)
and I'll bet with you that if you move your indexes back to the tables
disk and leave everything else the same on the remaining two disks,
you won't even notice any degradation!
Richard Foote
news:bm6l3t$fdg$1...@news.fujitsu-siemens.com...
>
news:Wczhb.145191$bo1.1...@news-server.bigpond.net.au...
> > There is no doubt that Oracle itself is the biggest myth spreader of
them
> > all. Just look at it's own OTN site where you can still read why
> > indexes/tablespaces should be split for performance, that Oracle indexes
> > become unbalanced over time and need frequent rebuilding
> They don't become unbalanced???
> Could you tell me more about it?
Hi Volker,
This has been done to death recently but (deep breath) here goes ...
Oracle indexes are always balanced, always. By that I mean all leaf nodes
are on the same level within the B-Tree structure, that's all leaf nodes,
exactly the same level or "distance" from the root node. Always.
Indexes can become "skewed" or have blocks less densely populated in some
parts of the index than in other parts. This is what some consider to be
"unbalanced" and to which I give some the benefit of the doubt. However,
skewed indexes deserving of an index rebuild are not common despite myths to
the contrary.
Lets examine the text you quote carefully.
> Because the Documentation on my 9i CD says
> "Serializing within Indexes
> Use of sequences, or timestamps, to generate key values that are indexed
> themselves can lead to database hotspot problems, which affect response
time and
> throughput. This is usually the result of a monotonically growing key that
results in
> a right-growing index.
The above describes indexes in which the "right most" leaf node is always
accessed during insert operations because, as the B-Tree is a logically
ordered structure, it's this leaf node that contains the current maximum
value. Hence it's the leaf node into which the new maximum value in
monotonically growing keys will always want to insert it's index entry. This
leads to a "hotspot" in high concurrent insert applications as everyone
wants this poor leaf node leading to an excess of Buffer Busy Waits and
related performance issues.
>To avoid this problem, try to generate keys that insert over
> the full range of the index.
And yes, a solution is to instead create a reverse key index that will
spread logical reads across the entire index structure, thereby reducing the
BBW. There are of course issues with this, not least the impact on index
range scans but yes, so far so good. Note though we have not addressed or
touched on the subject of "balanced" indexes yet.
> This results in a well-balanced index that is more
> scalable and space efficient.
Now the "bull-shit factor" rears it's ugly head. Firstly as mentioned
previously, it does not effect the balance on the index one bit, nor does it
necessarily effect the skewness of the index, nor does it necessarily make
it more space efficient.
Two key issues (or Oracle myths)
Firstly, there is a misconception that by always inserting into the
"right-most" leaf node, you somehow either make the index unbalanced (for
those who have absolutely no idea how indexes work) or skewed (for those who
only have a vague understanding how indexes work). It all comes down to what
happens when the last leaf node gets full.
Those that have no idea think that the node "spawns" two new leaf nodes into
which the index entries go and that the current index leaf node now becomes
a branch node to the new leaf nodes. The height of the index thereby
increases at this section of the index structure and we have an unbalanced
index on our hands. This goes on and on and so the unbalanced index problem
worsens.
Crap.
Those with only a vague idea think that the leaf block splits, that 50% of
the entries remain in the current block and the maximum 50% of entries go
into the new block. The associated branch block is then updated to reflect
this new leaf node. This new block then fills, the 50-50 split happens again
and so on. We now have an index that is trailing behind itself half empty
blocks that never get filled because of the monotonically growing key and so
we have a skewed index on our hands.
Crap.
Of course what happens is that when Oracle attempts to insert an entry into
a node that would logically be the *maximum* index entry for the node a
100-0 split (or 99-1 or 90-10 depending on your terminology) occurs, instead
of the "usual" 50-50 split, whereby only the new maximum entry goes into the
new node. The "previous" last leaf node remains full resulting in no index
skewness whatsoever as a result of the inserts. The associated branch node
is then updated to reflect the new leaf node. If it fills, it then splits
(in the same manner as the leaf nodes) and it's parent node has the new
corresponding node entry added. If the "root" node were to fill in this
manner (the root node really only being a branch node that sits "on top" of
the index structure), then it splits into two branch nodes and an additional
new root node is also allocated to point to the two branch nodes. We have
now increased the height of the index BUT it has resulted in *all* leaf
nodes now being one level further away from the root.
Our index is still fully balanced and it has no data skewness at all. None.
It's as space efficient as can be.
Issues could only arise if we perform deletes. And that's only a could. I'll
ignore the issue of updates which are logically index deletes anyway because
updating the monotonically increasing index entries makes no sense. In my
experience, row deletion is not so common nowadays, we always seem to want
to keep collecting our data, storage is relatively cheap, etc.
However, if we were to delete rows from this table, then the deleted entries
within the *index structure* can not be reused. That's obviously because all
new entires are always inserted into the leaf node containing the maximum
value. Note that deleted index space within the *index structure* generally
can be reused, that's another Oracle myth I'll leave for now. This could
result in data skewness but even here, that's generally not a problem.
Why ?
Because generally when we delete from these sorts of tables (for historical
reasons, etc.) we delete whole batches of data (eg. all data from 5 years
ago, etc.). And when we delete whole chunks of data, that data resides in
the same index leaf nodes, it's monotonically increasing remember. So we are
left with areas of our index with *fully* emptied index nodes. And these
nodes *are* reusable, they get placed on the freelist and are reused when a
split occurs at the insertion end of the index. The index logically "walks"
with old emptied nodes on the "left" of the index structure being passed
across to the "right".
The index remains both balanced and nonskewed automatically without a
rebuild in sight.
*IF* data is deleted "sparsely" such that many nodes are not fully emptied,
then we have a situation where data skewness could be an issue with
monotonically increasing values, because unlike the usual scenario of
reusing deleted entries, we have no chance of reusing this deleted space.
However, this is not a common scenario with most indexes.
>You can achieve this by using a reverse key index or
> using a cycling sequence to prefix and sequence values."
This may help contention and buffer busy wait scenarios but this will
definitely not help to balance your index and it will probably not help to
prevent data skewness either.
>
> So, presumably there are unbalanced indexes too.
No. Never.
In some rare cases you can have data skewness but these are exceptions, not
the norm. Oracle's self balancing, 50-50 and 90-10 node splitting, node
reuse and deleted space reuse mechanisms ensure that index rebuilds are very
rare requirements.
And even then, index coalescing rather than index rebuilds are generally
sufficient.
But there are always exceptions ...
> I mean, I can accept that there are all
> sorts of docs on the otn but I always assumed that the delivered doc
always has the final
> word.
This might be old advice but it's still good advice.
"Never Assume" !!
Cheers
Richard
Jonathan Lewis
Good test - which exhibits an interesting
point: when you quadrupled the number of
discs, you only doubled the performance.
Of course, the conclusions you may draw
from the test depend on the activity - but
it does suggest that there is mileage in
thinking carefully about how to make best
use of the available spindles.
Possible explanations that could fit your
observation:
Your test was read only, so the spindles
for undo and redo were effectively inactive
so the effective spindle count was only
doubled.
Your test was I/O bound on one disc, but
required only a little extra I/O capacity to
become CPU bound
Your test was write-intensive, but you had
lots of indexes on the updated tables, so the
index-holding disc became the I/O bottleneck
before you go close to capacity on the other
three.
Your test was highly concurrent with lots of
very small updates on tables that did not
affect indexes - so your bottleneck was in
the redo log allocation and redo log buffer.
--
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
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Geomancer" <pharfr...@hotmail.com> wrote in message
news:cf90fb89.03101...@posting.google.com...
Noons
> I thought Steve's proof was fairly definitive - more latching. If you
> aren't getting on 'latch free' or CPU, then by all means, let the pub
> syns fly. Otherwise, buyer beware.
I know that. You know that. Anyone with a gram
of functioning grey matter knows that. Do you
think for one minute it will stick with damagement
unless it is underwritten by "credible" Oracle?
Noooooo....
Jonathan Lewis
The problem, I think, is that it's easy to
argue the case in theory, but very hard
to set up a demonstration. You need
a high degree of concurrency with a
reasonable number of hard parses going
on to make the effect stand out. Building
the test case realistically is time-consuming.
--
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
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Noons" <wizo...@yahoo.com.au.nospam> wrote in message
news:3f881704$2$28122$afc3...@news.optusnet.com.au...
steve
(in message <3f87eeb5$0$264$cc9e...@news.dial.pipex.com>):
> "steve" <m...@me.com> wrote in message
> news:0001HW.BBADE608...@news.newsguy.com...
>> I think we all need to be a little clearer on what we are saying.
>>
>> when we are saying separate tables and indexes, do we mean:
>>
>> 1. in separate segmnents on the same disk.
>> 2. in sequential disk blocks within the same oracle segment
>> 3. on seperate disks on the same controller.
>> 4. on separate disks on different controllers.
>
> Um well as tables and indexes are both types of segment 1 and 2 are
> nonsensical statements. Did you perhaps mean tablespace or
> something else instead of segment?
>
> The myth is that just by putting data and indexes in seperate tablespaces
> performance improves. This is demonstrably untrue. Putting contending
> segments (which tables and their indexes may be) on different disks does
> improve performance but this is not the same thing.
>
Sure if it is a Global statment that performance ALWAYS improves by
separating them out then it can be proven very easily that it is nonsence.
sorry segment=tablespace.
1. i was refering to the oracle paper where they state that trying to put the
data on a certain physical part of the disc can improve the performance.
2. even with an oracle tablespace, it is not possible to gurantee that the
rawdata is in sequential blocks( at the disk level, not the table level), so
in theory you can have 2 identical setups ( hardware wise) that would perform
differently, if one of them had tablespace raw areas that were fragmented at
the raw level.
Jitendra Patel
> luck. It's the kind of detail no-one at Oracle would have the time or
> inclination to test to get a definitive answer on.
Ah Sali Kuta, I wish you could find day work, as you annoy many.
forb...@spamcollectors.net
Thus sayeth the man to the mirror. The mirror being the wiser,
respondeth not.
Howard J. Rogers
Oh, he can call me a stupid bastard if he wants. I have to say it sounds
nicer in Hindi.
I believe karma is also a Hindo word. With interesting implications.
Daniel Morgan
forb...@spamcollectors.net wrote:Jitendra Patel wrote:Having looked about a year ago for exactly that, I think you are out of luck. It's the kind of detail no-one at Oracle would have the time or inclination to test to get a definitive answer on.Ah Sali Kuta, I wish you could find day work, as you annoy many.Thus sayeth the man to the mirror. The mirror being the wiser, respondeth not.Oh, he can call me a stupid bastard if he wants. I have to say it sounds nicer in Hindi. I believe karma is also a Hindo word. With interesting implications. Regards HJR
you, and others, to do what I'm doing. Confirming his employment and forwarding his postings to his management
team so that they can see how one of their employees is "representing" them.
If he were my employee ... I'd find a creative way to express my displeasure. I've no doubt Richard Niemiec and
others on the management team have better things in mind for their organization than to be embarrased by a troll.
Daniel Morgan
Howard J. Rogers wrote:
forb...@spamcollectors.net wrote:Jitendra Patel wrote:Having looked about a year ago for exactly that, I think you are out of luck. It's the kind of detail no-one at Oracle would have the time or inclination to test to get a definitive answer on.Ah Sali Kuta, I wish you could find day work, as you annoy many.Thus sayeth the man to the mirror. The mirror being the wiser, respondeth not.Oh, he can call me a stupid bastard if he wants. I have to say it sounds nicer in Hindi. I believe karma is also a Hindo word. With interesting implications. Regards HJR
that is worthy of consideration. Seems that someone named J.A. Patel is leaving perfect reviews on them.
The following were copied, intact, from Amazon's web site and are not my comments on these books.
========================== BEGINNING OF REVIEWS
Oracle Performance Tuning Tips and Techniques
by Richard J. Niemiec, Joe Trezzo, Rich Niemiec, Bradley D. Brown, Joseph C. Trezzo
contains this review:
Most excellent helper, April 26, 2003
| |
Reviewer: J. A. Patel from Councel Bluffs, IA |
And then there's this:
Oracle9i UNIX Administration Handbook
by Donald K. Burleson, Don Burleson
which contains this review:
Super for a Unix
newbie, April 26, 2003
| |
Reviewer: J. A. Patel from Councel Bluffs, IA |
and then there's:
Oracle9i High-Performance Tuning with STATSPACK
by Donald K. Burleson, Don Burleson
which contains this review:
Fantastic Oracle tuning reference, June 22, 2003
| |
Reviewer: J. A. Patel from Councel Bluffs, IA |
========================== END OF REVIEWS Needless to say there is an obvious problem here. One that goes far beyond the previous concerns about trolling, insults, and abusive off-topic remarks. I've no doubt Amazon.com will be unhappy to find out that some of what they have published in good faith has been submitted under false pretenses. A copy of this posting has been forwarded to a friend at Amazon, here in Seattle, for their consideration. BTW Jitendra. Learn to spell the name of the city in which you claim to live. You got it wrong three times over the course of two months. -- Daniel Morgan
Jitendra Patel
Mr. Morgan,
Observe below is it is FORUM MEMBERS doing the attacking.
There is good reason why TUSC does not post here because nobody like
hurtful insults.
Not just professional insult, but making folly of there looks and
other private things.
Every week hurtful insults are hurled here.
Pretend you are Niemiec while you read below and think how you would
feel.
How would you feel if you were nice person, trying to help, and was
treated in such mean way?
Even when Niemiec tries to engage in productive discussion, his
private e-mail is posted on public forum and dissed.
Read sample below and see.
++++++++++++++++++++++++++++
From: "Niemiec Rich - IL" <NIEM...@Tusc.com>
To: howard...@yahoo.com.au
Subject: back up your statements
Date: Sun, 9 Feb 2003 22:06:55 -0600
If you have questions, ask me directly so that I can answer them
before you post slanderous statements on the web. I spend a lot of
nights, weekends...etc. writing things with the only goal to try and
help people out, so I am honestly concerned with your comments.
What are things that you've read? What do you find bad with them? Do
you ever try to get clarification before you post things like this?
Thanks,
Rich Niemiec
-------
Quote Off
Now I could comment on the arrogance that suggests I can't post an
opinion without having it cleared (or 'clarified') by Rich first. I
could also suggest that throwing words like 'slanderous' around is
designed to initmidate (and my inbox tells me that it has worked on at
least one occasion). But I won't mention that Usenet and the Web are
two completely different beasts.
And I would also say that if I'd had the courtesy of a reply from Rich
to *my* reply to his original email, I wouldn't be posting this here.
But I haven't, so I will.
Other posters have to know that its OK to think Rich is wrong, or
think
that his books miss the point by a long mile.
And if he doesn't like the opinions expressed here, or can't tell the
difference between a poor opinion and a 'slanderous statement', he can
do what we all have to do here when criticised: grow up, deal with it
and move on.
HJR
++++++++++++++++++++++++++++++++++++++++++++
Having just one extent per segment improves performance by, just wait
a
minute while I go and check a Niemeic Tuning book ...
Richard
PS. For any newbies, the above is a joke, a rather sad and sorry one
but a joke nonetheless.
++++++++++++++++++++++++++++++
And let's not forget the interminable scripts to calculate "ratios".
Very useful. God only knows to whom.
And the "defrag" early and often crap.
++++++++++++++++++++++++++++++++++++++++++++++++
I haven't touched any of the books from the TUSC mob since Jonathan's
book's been out.
That lot has spouted a lot of wind and little substance.
+++++++++++++++++++++++++++++++++++++++++++++++
If you've found their material useful, or Ault's stuff in particular,
then fair enough. But the general case still holds true, too. They
don't know their ASSM from their elbows most of the time.
Regards
HJR
++++++++++++++++++++++++++++
Now I find the above very appealing because for some weird reason, I
find Rich Niemiec reminds me heaps of the first husband in Bewitched
and a photo with him (with suitably forged autograph) would make a
great poster, hung in a place of honour in the study.
Sybrand Bakker
Patel) wrote:
>> Needless to say there is an obvious problem here. One that goes far beyond the previous concerns about trolling, insults, and abusive off-topic remarks.
>
>Mr. Morgan,
>
>Observe below is it is FORUM MEMBERS doing the attacking.
>
>There is good reason why TUSC does not post here because nobody like
>hurtful insults.
>
>Not just professional insult, but making folly of there looks and
>other private things.
>
>Every week hurtful insults are hurled here.
>
>Pretend you are Niemiec while you read below and think how you would
>feel.
>
>How would you feel if you were nice person, trying to help, and was
>treated in such mean way?
Pretend for a moment you are not Jitendra Patel. How would you feel
when you read Mr. Patel's insults, especially the one, where he is
claiming you shouldn't visit this forums for useful answers.
You find yourself asking: When did Mr. Patel in the history of
comp.databases.oracle.server posted any useful answer?
When did he post anything that didn't have a racist undertone,
glorifying his own compatriot Indian trained monkeys, because they are
so much better developers than any non-Indian developer.
Didn't Mr. Patel notice how often his compatriots on Metalink are
providing incorrect answers or trying to prevent to answer a question
atl all?
What is the reason Mr. Patel is continually glorifying his employer,
Mr. Niemic, is in this forum?
Didn't Mr. Patel notice the TUSC recommendations are often just
blatantly *WRONG*
When will Mr. Patel stop trying to refute the justified concerns about
the gospel the TUSC mob is spreading, by flaming and ranting other
members in this newsgroups.
--
Sybrand Bakker, Senior Oracle DBA
Noons
>
> Observe below is it is FORUM MEMBERS doing the attacking.
No. It is YOU and your little "pals" that have engaged in DIRECT
slanderous attacks on people who post here. And by the way:
this is a public Usenet forum, there is no such thing as "MEMBERS".
There are only posters.
"MEMBERS" is for the other private e-mail lists you frequent,
where under the excuse of "proper information" outsiders
are regularly insulted, with no option to respond. A cowardly
attitude at the very least, but it's obvious you're impervious
to the concept.
> There is good reason why TUSC does not post here because nobody like
> hurtful insults.
TUSC is most welcome to post here. And argue why they do or
do not agree with anything said here. That they prefer to
use idiotic e-mails or trolls like you as "actors" is most
unfortunate.
Here is a thought: T. Kyte and quite a few others from Oracle
post here. Sometimes, they provide additional information. Others,
they point out errors. No one takes that as insulting or abuses
them when they do. Why do you think that is? Does the OBVIOUS
difference to TUSC's attitude even register with you?
> Every week hurtful insults are hurled here.
No one from TUSC has been insulted. Some of their materials
have been exposed as incorrect. However, a few posters here have
been directly and personally insulted by you and your band of trolls.
> Even when Niemiec tries to engage in productive discussion, his
> private e-mail is posted on public forum and dissed.
Productive discussion is NOT private e-mail. Post here, for
all to read. What's the matter, is the thought of public
critique and evaluation of your ideas a concept you can't cope
with?
> (HJR text)
> And if he doesn't like the opinions expressed here, or can't tell the
> difference between a poor opinion and a 'slanderous statement', he can
> do what we all have to do here when criticised: grow up, deal with it
> and move on.
and there is NOTHING wrong with this.
Although you obviously disagree.
Daniel Morgan
Needless to say there is an obvious problem here. One that goes far beyond the previous concerns about trolling, insults, and abusive off-topic remarks.Mr. Morgan, Observe below is it is FORUM MEMBERS doing the attacking. There is good reason why TUSC does not post here because nobody like hurtful insults.
which you have absolutely no knowledge?
You mean that kind of hurtful insults?
I hope so because while you are correct that there are people here that have criticized some TUSC employees and
authors ... those criticisms have been based on whether their Oracle advice was valid. And each of those people
has made genuine efforts in these usenet groups to be helpful to other members of the Oracle community.
Your postings on the other hand are Oracle irrelevant. They read like the slimy yellow pages of a trashy novel. Not
once have you offered help to someone with respect to an Oracle related question. Not once have your criticisms
of another person here been based upon their work with the Oracle database or Oracle Corp. products.
And now one of my students has caught you perpetrating a fraud on a major corporation here in Seattle.
I can only wonder what some of my other students will discover now that they've taken an interest in you.
Daniel Morgan
Volker Hetzer
"Richard Foote" <richar...@bigpond.com> schrieb im Newsbeitrag news:QPThb.146517$bo1.1...@news-server.bigpond.net.au...
> "Volker Hetzer" <volker...@ieee.org> wrote in message
> news:bm6l3t$fdg$1...@news.fujitsu-siemens.com...
> >
> > "Richard Foote" <richar...@bigpond.com> schrieb im Newsbeitrag
> news:Wczhb.145191$bo1.1...@news-server.bigpond.net.au...
> > > There is no doubt that Oracle itself is the biggest myth spreader of
> them
> > > all. Just look at it's own OTN site where you can still read why
> > > indexes/tablespaces should be split for performance, that Oracle indexes
> > > become unbalanced over time and need frequent rebuilding
> > They don't become unbalanced???
> > Could you tell me more about it?
>
> Hi Volker,
>
> This has been done to death recently but (deep breath) here goes ...
>
> Oracle indexes are always balanced, always. By that I mean all leaf nodes
> are on the same level within the B-Tree structure, that's all leaf nodes,
> exactly the same level or "distance" from the root node. Always.
the depth of indexes. That's why I was confused.
> Indexes can become "skewed" or have blocks less densely populated in some
> parts of the index than in other parts. This is what some consider to be
> "unbalanced"
All right, but if we are talking B-trees, the meaning of "unbalanced" is generally
well defined, meaning different leaf nodes have different distances to the
root.
> Lets examine the text you quote carefully.
>
[reason fir right-growing indexes=bad: contention for the last buffer if
multiple inserts with monotonous growing sequences. Solution:]
> >To avoid this problem, try to generate keys that insert over
> > the full range of the index.
> Firstly, there is a misconception that by always inserting into the
> "right-most" leaf node, you somehow either make the index unbalanced (for
> those who have absolutely no idea how indexes work) or skewed (for those who
> only have a vague understanding how indexes work). It all comes down to what
> happens when the last leaf node gets full.
My teacher told us indexes don't get rebalanced (as university tought b-trees get)
which I found hard to believe anyway.
[you state that indexes get rebalanced]
[You say that indexes get rebalanced when deleting too]
Ok, what you say makes sense to me because I believe the overhead of balancing
to be noth a prohibitive burden. When I've got some time I'll test this.
> "Never Assume" !!
Thanks a lot!!
Volker
Richard Foote
news:7610580f.03101...@posting.google.com...
beyond the previous concerns about trolling, insults, and abusive off-topic
remarks.
>
> Mr. Morgan,
>
> Observe below is it is FORUM MEMBERS doing the attacking.
>
Hi Jitendra,
You really have no idea do you ?
How about a little technical challenge !!
How about you defend the TUSC God you worship from these awful "attacks"
(your term) you so denounce by suppling a technically factual
explanation/defence instead of the boring and rather juvenile tripe you
usually dish up. Here's a chance to prove the TUSC publications correct,
that you know something vaguely about Oracle, actually post something
technical and earn some respect, something you're sadly lacking at the
moment.
Two very simple challenges. Explain why the following claims printed in TUSC
8i performance tuning books are correct and undeserving of these awful
recent "attacks" (your term):
Challenge 1) "The binary height of an index increases due to the size of a
table and *the fact that the range of values in the indexed columns is very
narrow*".
Please explain how the range of values in an index influences the height of
an index ?
Challenge 2) "Generally speaking, fragmentation decreases performance by
10-20 percent on average. To repair fragmentation, you'll need to find the
fragmented tables/indexes. It's recommended that you regularly monitor your
database to find tables/indexes fragmented into more than five pieces (or
extents). The key to eliminating fragmentation is rebuilding the table to
one extent or the size it has currently grown to."
Please explain how having five extents decreases performance by 20% and how
having just one extent subsequently improves things by so much ? Remember
this is in a tuning book that covers 8i but to make things easier for you,
please justify such claims with either dictionary or locally managed
tablespaces. You don't even have to concern yourself with the tablespace
fragmentation such steps would generate in DMTs.
Here's your chance at last. To post something that's actually related to
Oracle, that supports TUSC teachings and that supports the above claims as
being correct.
Because if you can't, if you agree that the above is crap, then surely you
support these "attacks" (your term) as being justified and that exposing
them as technical illusions is in the best interests of the Oracle community
and that such "attacks" (your term) may not actually be levelled directly at
TUSC but at the information itself and at the confusion and
misunderstandings that such inaccuracies generates.
So Jitendra, are you up to the challenge ?
Cheers
Richard
Daniel Morgan
related to Oracle. Rather it appears that her relationship to TUSC is more in line with the relationship
a 12 year old girl has with Brittany Spears. She is an infatuated, fixated, groupie in desperate need of
help. I wish I could be more specific but invite you to use your imagination an read between the lines.
It would be best to just ignore her and anything she posts.
Hulse
> Snid wrote:
>
> >
> > So I guess I can see why lots of people think that it's best to separate
> > indexes from tables. It doesn't mention that you should only separate them
> > for administration purposes mainly.
>
> Quite right. The advice is still there, in plain, unadorned incorrectness,
> in the performance tuning course notes for Oracle 9i (published in version
> 2.1 only a few weeks ago).
>
> Oracle are also responsible for promoting the myth that getting a segment
> into a single extent is good for performance; that PCTINCREASE 1 is a good
> idea to protect against fragmentation (it causes it); that ASSM has no
> costs; that tuning consists of getting a good buffer cache hit ratio. You
> name it, they've touted it.
>
> These days, they have TUSC to do that sort of thing for them.
I'm curious. TUSC archives their presentations and make them
freely available for download. So, can you point me to where they
are still making such claims? I have worked with Rich directly and
he's never suggested such things.
Hulse
> > No, Eienstein. (I guess we know why you are unemployed).
>
> Um, had you made several caveats and been less insulting, your point
> might have been better taken.
>
> If the disk is not RAID striping (RAID10, 0+1, 5) then many of your
> points may be correct.
>
> In my experience with Oracle on Windows, I demonstrated significant
> slowdown with the whole database on a single non-RAID disk.
>
> When I moved indexes to a separate disk, undo to another and redo to a
> 4th disk, performance doubled!
This hardly demonstrates anything. You altered a number of things
to generate the 100% performance improvement. The improvement could
have been a result of isolating only a subset of those files.
Howard J. Rogers
Who said *anything* about Rich?????????
And to quote a post by Richard Foote:
"The binary height of an index increases due to the size of a
table and *the fact that the range of values in the indexed columns is very
narrow*".
"Generally speaking, fragmentation decreases performance by
10-20 percent on average. To repair fragmentation, you'll need to find the
fragmented tables/indexes. It's recommended that you regularly monitor your
database to find tables/indexes fragmented into more than five pieces (or
extents). The key to eliminating fragmentation is rebuilding the table to
one extent or the size it has currently grown to."
Which are quotations from TUSC material, I believe.
Billy Verreynne
> It would be best to just ignore her and anything she posts.
Bloody hell mate, this has been some of the best amusement all year.
Why stop now?! :-)
--
Billy
PS. Daniel, please return my lead pipe when you're done beating up on
the troller. Thanks. ;-)
Niall Litchfield
news:NLxib.149038$bo1....@news-server.bigpond.net.au...
> Hi Jitendra,
>
> You really have no idea do you ?
ouch!
>
> How about a little technical challenge !!
> Two very simple challenges. Explain why the following claims printed in
TUSC
> 8i performance tuning books are correct and undeserving of these awful
> recent "attacks" (your term):
<snip>
> Challenge 2) "Generally speaking, fragmentation decreases performance by
> 10-20 percent on average. To repair fragmentation, you'll need to find the
> fragmented tables/indexes. It's recommended that you regularly monitor
your
> database to find tables/indexes fragmented into more than five pieces (or
> extents). The key to eliminating fragmentation is rebuilding the table to
> one extent or the size it has currently grown to."
>
> Please explain how having five extents decreases performance by 20% and
how
> having just one extent subsequently improves things by so much ? Remember
> this is in a tuning book that covers 8i but to make things easier for you,
> please justify such claims with either dictionary or locally managed
> tablespaces. You don't even have to concern yourself with the tablespace
> fragmentation such steps would generate in DMTs.
In case folk think that this is an unfortunate old example with no relevance
to the real world I have just had the very great satisfaction of giving a
DBA who is a single mother her weekends back. Having inherited a script
based on exactly this piece of advice (I'd wondered where the figure 5 came
from) she was spending many Sundays rebuilding objects so that they fitted
in one extent and occasionally sorting out the resultant fragmentation. This
lady is not a stupid person by any means, however when you have several
Oracle instances, DB2, developers and networks to look after it isn't
uncommon to just rely on 'expert' advice, it helps then if said experts can
justify their recommendations either with sound argument, or better yet
demonstration or proof. People will just trust you if you are a recognisable
name and this carries a special responsibility.
I should add that I have no especial beef with TUSC or anyone else for that
matter - I'm sure I've given enough bad advice to last a lifetime myself -
hopefully we can divorce this argument from personalities and centre it on
the actual merits of the recommendations.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
>
Daniel Morgan
Hulse
> Hulse wrote:
>
> > "Howard J. Rogers" <h...@dizwell.com> wrote in message
> > news:<3f84f251$0$30614$afc3...@news.optusnet.com.au>...
> >> Snid wrote:
> >>
> >> >
> >> > So I guess I can see why lots of people think that it's best to
> >> > separate indexes from tables. It doesn't mention that you should only
> >> > separate them for administration purposes mainly.
> >>
> >> Quite right. The advice is still there, in plain, unadorned
> >> incorrectness, in the performance tuning course notes for Oracle 9i
> >> (published in version 2.1 only a few weeks ago).
> >>
> >> Oracle are also responsible for promoting the myth that getting a segment
> >> into a single extent is good for performance; that PCTINCREASE 1 is a
> >> good idea to protect against fragmentation (it causes it); that ASSM has
> >> no costs; that tuning consists of getting a good buffer cache hit ratio.
> >> You name it, they've touted it.
> >>
> >> These days, they have TUSC to do that sort of thing for them.
> >
> > I'm curious. TUSC archives their presentations and make them
> > freely available for download. So, can you point me to where they
> > are still making such claims? I have worked with Rich directly and
> > he's never suggested such things.
>
>
> Who said *anything* about Rich?????????
One would certainly hope that the CEO of a company would have
some some control over what sort of silliness the underlings
spread around. Also, Richard himself produces quite a bit of the
tuning advice (printed materials) that comes out of TUSC.
Howard J. Rogers
> "Howard J. Rogers" <h...@dizwell.com> wrote in message
> news:<3f8b0378$0$24515$afc3...@news.optusnet.com.au>...
>> Hulse wrote:
>> >
>> > I'm curious. TUSC archives their presentations and make them
>> > freely available for download. So, can you point me to where they
>> > are still making such claims? I have worked with Rich directly and
>> > he's never suggested such things.
>>
>>
>> Who said *anything* about Rich?????????
>
> One would certainly hope that the CEO of a company would have
> some some control over what sort of silliness the underlings
> spread around.
You might certainly hope that.
>Also, Richard himself produces quite a bit of the
> tuning advice (printed materials) that comes out of TUSC.
And one of my quotations was from one of those printed materials, IIRC.
George Barbour
tablespaces
> performance improves. This is demonstrably untrue. Putting contending
> segments (which tables and their indexes may be) on different disks does
> improve performance but this is not the same thing.
>
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
Hummm.....
Performance gains are insignificant during queries, but ..... Doesn't this
come back to "it depends"?
If the data is being updated, by bulk, regularly then it is beneficial to
seperate them, for performance.
I'm talking about 10's of new Gigabytes a day having new indexes rebuilt per
day. (actually its during the night).
I seperate them for administration anyway.
George Barbour.
Howard J. Rogers
>> > The myth is that just by putting data and indexes in seperate
> tablespaces
>> performance improves. This is demonstrably untrue. Putting contending
>> segments (which tables and their indexes may be) on different disks does
>> improve performance but this is not the same thing.
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> Audit Commission UK
>
> Hummm.....
> Performance gains are insignificant during queries, but ..... Doesn't this
> come back to "it depends"?
I'm getting a bit fed up with that cop-out. Everything "depends". The
intelligent question (and difficult answer) is "on what".
> If the data is being updated, by bulk, regularly then it is beneficial to
> seperate them, for performance.
Er, in a word.... Crap.
> I'm talking about 10's of new Gigabytes a day having new indexes rebuilt
> per day. (actually its during the night).
It could be during your afternoon cup of cocoa. It makes no difference. And
why in God's name are you rebuilding indexes DAILY??? Are you completely
mad, or what?? Sorry George... that was a rhetorical question. I get quite
a lot of sad cases waltzing through classes saying the same thing (and
often it's because the vendor tells them they have to. Very sad). When you
ask them why them put themselves through this purgatory, they haven't a
clue. Here's another one liner, then, with very, very few exceptions:
Indexes NEVER need rebuilding. Ever. (well, OK, extremely, extremely
occasionally, depending on 1001 circumstances and characteristics of the
index in question).
As for the performance improvement issue. Go back to your first statement:
it depends. What depends? Er, I/O contention. On what?? On what people are
doing. NOT, you will notice, on the nature of the segment.
But I give up, I really do. There are a thousand and one people out there
who have finally got the message (hurrah!): just *because* it's an index
doesn't mean it needs separating from its table. Tables contend with
tables, indexes with indexes, tables with indexes. *THE ISSUE IS
CONTENTION* not what bloody sort of segment you're dealing with.
Deal with contention, wheresoever and howsoever it arises. But don't fall
into the trap of thinking that indexes inherently contend with tables, and
that by separating them you've dealt with the issue. Have some
intelligence. Answer the question "on what", and then you will be a happy
camper.
Even (thanks to Joel's pointing me in the right direction) Oracle's own
documentation has finally got this message. Why will no-one else?
> I seperate them for administration anyway.
Don't we all? But let's stop this complete and utter silliness which says
"surely there must be a performance benefit". Of separating indexes from
tables, *because* they happen to be indexes and tables?? Nah. There isn't.
Ever. Anytime. This side of Eternity. Ever, ever, ever. Absolutely, not.
Never.
"It depends" means: work out what is contending with what, and don't fall
for the silly lines of anyone who tells you 'because it is an index, it
must be in X and because it is a table it must be in Y'
Deal with contention where it *actually* arises, not where you think it
might, or ought, or might possible perhaps, arise because of the nature of
the segments under scrutiny.
Why this quite straightforward message hasn't yet got through, I have no
idea. But I'm seriously regretting ever mentioning it, and starting to wish
the likes of Don Burleson would just take this issue away with him to La La
Land, which he would do very well, no doubt.
Let's see if I can put it this way. For years, DBAs lead a quiet life
instinctively separating tables from indexes because they were told it was
good for them. I'm telling you (and so is Oracle -thanks Joel) that you
were told wrong, but that you can still have a quiet life by NOT separating
them.
Why is doing it one way so much more acceptable than doing it the other way?
Both seek to make your DBA life easier and less full of petty
administrative hassles. All you have to ask yourself, confronted with two
completely contradictory bits of advice, is "which side is the physics on".
To which there is only one answer: mine (and Oracle's). Separating indexes
and tables is a complete and utter waste of a DBAs time and salary if its
spent in search of performance benefits.
Phew. Do you think the message has got through yet???
>
> George Barbour.
Regards
Niall Litchfield
George Barbour
> George Barbour wrote:
>
> >> > The myth is that just by putting data and indexes in seperate
> > tablespaces
> >> performance improves. This is demonstrably untrue. Putting contending
> >> segments (which tables and their indexes may be) on different disks
does
> >> improve performance but this is not the same thing.
> >>
> >> --
> >> Niall Litchfield
> >> Oracle DBA
> >> Audit Commission UK
> >
> > Hummm.....
> > Performance gains are insignificant during queries, but ..... Doesn't
this
> > come back to "it depends"?
>
>
> I'm getting a bit fed up with that cop-out. Everything "depends". The
> intelligent question (and difficult answer) is "on what".
>
> > If the data is being updated, by bulk, regularly then it is beneficial
to
> > seperate them, for performance.
>
> Er, in a word.... Crap.
>
> > I'm talking about 10's of new Gigabytes a day having new indexes rebuilt
> > per day. (actually its during the night).
>
> It could be during your afternoon cup of cocoa. It makes no difference.
And
> why in God's name are you rebuilding indexes DAILY??? Are you completely
> mad, or what?? Sorry George... that was a rhetorical question. I get quite
> a lot of sad cases waltzing through classes saying the same thing (and
> often it's because the vendor tells them they have to. Very sad). When you
> ask them why them put themselves through this purgatory, they haven't a
> clue. Here's another one liner, then, with very, very few exceptions:
> Indexes NEVER need rebuilding. Ever. (well, OK, extremely, extremely
> occasionally, depending on 1001 circumstances and characteristics of the
> index in question).
>
not "rebuilt" but "built" .e.g. being created.
The data is coming from a number of different data sources, moved into
Oracle segments getting collated and indexed, in order for our people to be
able to query it.
>
> As for the performance improvement issue. Go back to your first statement:
> it depends. What depends? Er, I/O contention. On what?? On what people are
> doing. NOT, you will notice, on the nature of the segment.
>
>
> But I give up, I really do. There are a thousand and one people out there
> who have finally got the message (hurrah!): just *because* it's an index
> doesn't mean it needs separating from its table. Tables contend with
> tables, indexes with indexes, tables with indexes. *THE ISSUE IS
> CONTENTION* not what bloody sort of segment you're dealing with.
>
>
> Deal with contention, wheresoever and howsoever it arises. But don't fall
> into the trap of thinking that indexes inherently contend with tables, and
> that by separating them you've dealt with the issue. Have some
> intelligence. Answer the question "on what", and then you will be a happy
> camper.
>
>
> Even (thanks to Joel's pointing me in the right direction) Oracle's own
> documentation has finally got this message. Why will no-one else?
>
> > I seperate them for administration anyway.
>
> Don't we all? But let's stop this complete and utter silliness which says
> "surely there must be a performance benefit". Of separating indexes from
> tables, *because* they happen to be indexes and tables?? Nah. There isn't.
> Ever. Anytime. This side of Eternity. Ever, ever, ever. Absolutely, not.
> Never.
>
>
> "It depends" means: work out what is contending with what, and don't fall
> for the silly lines of anyone who tells you 'because it is an index, it
> must be in X and because it is a table it must be in Y'
>
> Deal with contention where it *actually* arises, not where you think it
> might, or ought, or might possible perhaps, arise because of the nature of
> the segments under scrutiny.
>
> Why this quite straightforward message hasn't yet got through, I have no
> idea. But I'm seriously regretting ever mentioning it, and starting to
wish
> the likes of Don Burleson would just take this issue away with him to La
La
> Land, which he would do very well, no doubt.
>
years of,
if you like brainwashing about how to administer Oracle from all sorts of
questionable sources, including Oracle themselves.
We now know to question these people, and not to just except glib answers
from these so called gurus.
>
> Let's see if I can put it this way. For years, DBAs lead a quiet life
> instinctively separating tables from indexes because they were told it was
> good for them. I'm telling you (and so is Oracle -thanks Joel) that you
> were told wrong, but that you can still have a quiet life by NOT
separating
> them.
>
>
> Why is doing it one way so much more acceptable than doing it the other
way?
> Both seek to make your DBA life easier and less full of petty
> administrative hassles. All you have to ask yourself, confronted with two
> completely contradictory bits of advice, is "which side is the physics
on".
> To which there is only one answer: mine (and Oracle's). Separating indexes
> and tables is a complete and utter waste of a DBAs time and salary if its
> spent in search of performance benefits.
>
> Phew. Do you think the message has got through yet???
>
Well thank you Howard, for taking the time to answer the question, leaving
little room for doubt, and with, lets be frank here, lots of emotion; which
demonstrates your affection for the product and subject.
George Barbour.
Howard J. Rogers
>>
> Well thank you Howard, for taking the time to answer the question, leaving
> little room for doubt, and with, lets be frank here, lots of emotion;
> which demonstrates your affection for the product and subject.
> George Barbour.
It had probably been the end of a long day :-(
But there was a serious point there (and one not directed entirely or even
mainly at you).
When told (a) separate or (b) don't separate *for performance reasons*, why
is the general opinion to favour (a) over (b)?
Both are simplistic over-statements, though (b) is truer more generally than
(a).
Both aim to make life easy, in fact (b) makes it rather easier than (a).
Both have been said by Oracle themselves, though (a) has been said for
rather longer than (b), admittedly.
You would have thought (well, *I* would have thought) the balance of the
argument was firmly on the (b) side, or there's not much in it. But you get
people here regularly trying to argue for (a) with 'but what about' or
'surely'. There seems a reluctance to let go of (a) rather than just mere
inertia or old habits, and I can't work out why.
Something about (a) seems to make more intrinsic sense to people that makes
them want to doubt (b), or think anyone pushing (b) is selling snake oil.
It's exactly the same as when you offer people (and I take the point you
made, so this isn't directed at you at all) (a) rebuild indexes regularly
or (b) don't bother. (b) is truer, less work, and better for the database
than (a), and yet of 100 newbies you ask, 80 or so will favour (a). And
probably passionately so.
If only I could work out what it was.
Job Miller
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1463404632043
for one of his opinion on the subject.
or
from another article on his site:
http://asktom.oracle.com/~tkyte/google.html
is a very very very big thread but makes some (sometimes) amusing reading.
Separate them by size, separate THINGS (things, not tables vs indexes) to
achieve even IO. Separate them because it makes you feel better. Don't do it
because "you must", it is a myth.
sally
Can I get some snake oil from around these parts, I hear it makes my
database perform better.
Or was I missing the point!!!!!!!!!
I hope it makes you feel happier if I tell you that I HEAR you, you
are not flogging a dead horse.
Sal
Joel Garry
> George Barbour wrote:
>
> >>
> > Well thank you Howard, for taking the time to answer the question, leaving
> > little room for doubt, and with, lets be frank here, lots of emotion;
> > which demonstrates your affection for the product and subject.
> > George Barbour.
>
>
> It had probably been the end of a long day :-(
>
> But there was a serious point there (and one not directed entirely or even
> mainly at you).
>
> When told (a) separate or (b) don't separate *for performance reasons*, why
> is the general opinion to favour (a) over (b)?
>
> Both are simplistic over-statements, though (b) is truer more generally than
> (a).
>
> Both aim to make life easy, in fact (b) makes it rather easier than (a).
>
> Both have been said by Oracle themselves, though (a) has been said for
> rather longer than (b), admittedly.
>
> You would have thought (well, *I* would have thought) the balance of the
> argument was firmly on the (b) side, or there's not much in it. But you get
> people here regularly trying to argue for (a) with 'but what about' or
> 'surely'. There seems a reluctance to let go of (a) rather than just mere
> inertia or old habits, and I can't work out why.
Well, for me it is this:
Physical database layout as a proactive art requires you to take into
account many different situations. Some of them are contradictory,
and some are critical from the viewpoint of users. Traditional OLTP
transactions quite obviously are going to have some critical points,
and I agree these threads have shown (b) is entirely appropriate for
them, given appropriate monitoring and tweaking of hotspots. The
performance issue is going to be something like "3 second response
times for a customer inquiry." Or, "Hey, this thing ran in seconds
before, now it's taking hours?" :-)
What I think is a very common case is a mixture of OLTP and batch.
Your basic accounting and manufacturing software tend to have times of
major batch processing - MRP runs, end-of-month processing/reports and
so forth. These batch jobs may be considered to be serial single-user
modes if you wish. Why? Because the performance issue tends to be a
small set of jobs whose total wall-time is the critical measure. For
example, it may not be an issue if it runs overnight in 2 hours or 8.
But if it isn't done then, people scream.
So why do people think interspersing indices will hurt? Consider a
master/detail relationship - Order headers and lines. The order
numbers are always increasing, and periodically the older ones are
sent elsewhere. So a certain number of headers will fit in the blocks
of this table H, and a different number of lines will fit in the
blocks of that table L, such that one of the tables will be extending
faster than the other, and of course the index blocks I will extend at
a different rate. So you might wind up with this:
HLIHLIHLLHILHHLLIHHHLLIHLLIIHLIHHHLLLLLLIHHLLIHHLHLHLHII...
(Hmm, shoulda called the index E :-)
Anyways, now we are running some damn report, and it does a full table
scan of just the headers, and another of just the lines, because it's
some old OCI piece of, uh, work that does it's own join and then later
gets by rowid's. It becomes difficult for people to accept that the
disk device is able to scoop up all the H's, even with linked lists,
faster than if they were contiguous, since there's some kind of
multiblock read going on - and with hundreds of tables in an actual
system, the dispersal would be higher. It is difficult for people to
accept that Oracle will not reload them contiguously with an import
(especially if they've looked at a file with a debugger after a
compress=y, and they see HHHHHHH...LLLLLLLLLL...IIIIII, even with
LMT). It is difficult for people to accept that unix will scatter the
disk blocks about invisibly, especially difficult on a newly created
file system that have big data files that will never be deleted.
Now archive all those old records. All those beginning blocks are now
free, and how will Oracle load them?
Dang, sounds like these critical tables and indices should _each_ be
in their own tablespace.
>
> Something about (a) seems to make more intrinsic sense to people that makes
> them want to doubt (b), or think anyone pushing (b) is selling snake oil.
>
> It's exactly the same as when you offer people (and I take the point you
> made, so this isn't directed at you at all) (a) rebuild indexes regularly
> or (b) don't bother. (b) is truer, less work, and better for the database
> than (a), and yet of 100 newbies you ask, 80 or so will favour (a). And
> probably passionately so.
>
> If only I could work out what it was.
>
> Regards
> HJR
jg
--
@home.com is bogus.
http://www.letssingit.com/?http://www.letssingit.com/liam-lynch-whatever-8286shz.html
Billy Verreynne
> >Bloody hell mate, this has been some of the best amusement all year.
> >Why stop now?! :-)
> >
> If that's the best amusement you've had all year I'd suggest you need to
> push back from that keyboard.
Huh? What keyboard..!? [adjusting the electrodes at the temples]
;-)
--
Billy
Noons
>
> Huh? What keyboard..!? [adjusting the electrodes at the temples]
>
Hey Billy: put them back on, the "boks" need all
the help they can get after that night....
<g,d&r>
Volker Hetzer
"Joel Garry" <joel-...@home.com> schrieb im Newsbeitrag news:91884734.03101...@posting.google.com...
- The OS is looking for free blocks anywhere. Ok, so you might allocate
the datafiles first and large enough. That's IMHO a job for a scheduled
maintenance once you know how much data you're dealing with.
- The Db is looking for free extents anywhere in the datafile. Ok, set
the extent size properly and the skips won't matter anymore. Not having
tried this I would probably start with an extent size of n
multiblock_read_counts.