separate data/inidex
Titi
For a user, I have data and index in one tablespace.
I want to separate data and index ( good idea ??)
How can do that ???
Thanks in advance ...
( oracle 8.1.7)
Troy Meyerink
issuing an
'alter index rebuild' statement.
Troy Meyerink
Oracle DBA
Raytheon
"Titi" <thierry....@wanadoo.fr> wrote in message
news:3cbf2607$0$15182$626a...@news.free.fr...
Howard J. Rogers
at least in routine database configurations. No performance gain arises if
you do. There are real *management* issues (tables *need* backing up,
indexes don't). And the rebuild command is fine to move the indexes
somewhere else if you decide to do it; but then the 'alter table X move'
command will do much the same by shunting the tables off somewhere new, too.
Regards
HJR
"Troy Meyerink" <meye...@usgs.gov> wrote in message
news:Gus70...@igsrsparc2.er.usgs.gov...
Guy D. Kuhn
disk heads. They can only be over one disk cylinder at a time. If the disk is
reading the index, it can not be reading the data block.
If you only have one session accessing the table at a time, no problem.
Likewise, if you have enough memory for enough db_block_buffers to remove
contention, no problem.
But since an index access requires reading the index at least twice and the data
block at once, placing them on different disks (not tablespaces) can improve
performance by removing physical I/O contention.
Thomas Kyte
>
>There is a very "physical" reason for separating indexes and tables, naming
>disk heads. They can only be over one disk cylinder at a time. If the disk is
>reading the index, it can not be reading the data block.
>
If you
o have a single user system
o with no disk arrays
o have no other files at all on this disk
o use raw so that you ->
o don't have anything buffering
o are running in a dust free laboratory......
o have a separate disk for each and every table/index in your system
o .....
(all of the above and others that could be added must be ANDED together)
I think you see. The argument from above was valid in 1990 maybe but in 2002 it
is really not anymore. with raid arrays, logical volume managers, striping,
more then one user on the system, 36gig disks, etc etc etc -- it just doesn't
matter any more.
Just did a benchmark. Had about 9 files, 3 tablespaces. Tablespaces were named
"small", "medium" and "large". Indexes and data intermixed. One mount point,
one virtual file system. Indexes and data just sort of sitting where ever. No
point in spreading it "out". The underlying file system software did all of
that. Never did push the file system over the edge...
--
Thomas Kyte (tk...@oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Howard J. Rogers
> There is a very "physical" reason for separating indexes and tables,
> naming
> disk heads. They can only be over one disk cylinder at a time. If the
> disk is reading the index, it can not be reading the data block.
There is ZERO physical reason for separating indexes and tables, period.
>
> If you only have one session accessing the table at a time, no problem.
> Likewise, if you have enough memory for enough db_block_buffers to remove
> contention, no problem.
>
> But since an index access requires reading the index at least twice and
> the data block at once, placing them on different disks (not tablespaces)
> can improve performance by removing physical I/O contention.
Not true. The only reason for separating indexes and tables into separate
tablespaces is management issues. Anything else is just buying in to old
myths.
On this basis, every damn table needs to be in its own tablespace on its
own disk. After all, you've got lots of tables, and each of them are being
accessed simultaneously (never mind that index accesses and table accesses
are not simultaneous). So if you permit two tables to reside in the same
tablespace, why not a table and its index???
Performance-wise, there's nothing in it.
Regards
HJR
RSH
I can't believe you said this.
Tablespaces containing tables, and tablespaces containing indices
corresponding to them, would be like locating matter and antimatter on the
same spindle.
In RAID environments, I do not even like them sharing the same controller;
failing that, certainly not sharing the same RAID ranks.
How do you arrive at this astonishing (to me at least) notion?
RSH.
"Howard J. Rogers" <d...@hjrdba.com> wrote in message
news:a9ov52$kjn$1...@lust.ihug.co.nz...
Daniel A. Morgan
And please don't just throw out jargon like RAID and striping. This statement
appears to fly in the face of more than a few books by well respected authors
(not that you aren't one yourself now) as well as a lot of HTM and PDF files put
out on technet.
Daniel Morgan
RSH
You know full well that spindle diversity, particularly between (in my
triage) tables and indices, and then secondly, further separation of
concomitantly used tables and their indices, is a key to performance.
Further, of course, making sure tedious items like REDO and TEMP don't
caught in the crossfire.
It is a trite and juvenile statement in asserting "well, then, every table
should have its own tablespace", etc. A careful analysis of just what is
being used, how much, by whom, in what volume, and when, is how I plan out
my storage on a physical and logical basis.
For example, flat tables loaded by SQL*Loader should reside separately from
their derivative tables. It goes without saying that the residence of the
flat ASCII feeder files should not be on the same spindles, either. The
derivative tables and the tablespaces that hold them should be diversified
based on their volume of concurrent demand, and needless to say, the
tablespaces holding their indices ought not to be on the same spindles. Of
course, if one has a 95% DSS application, the worry of simultaneous update
and read activity and disk heads flying all over the place is not as much of
a concern. And with humungous DB_BLOCK_BUFFERS and DB_BLOCK_SIZE and the
rest, the performance hit would not be too bad.
SYSTEM, TEMP, and the REDO log files should be judiciously isolated from
this madness on other devices.
Of course, if you have only one disk or two, you are quite right in saying
it wouldn't make much difference, apart from the fact that defining the
tablespaces now, despite their residence, would make rebuilding the database
on a larger platform, with more diverse storage available, would be eased
greatly.
I guess I missed the part where the original poster of this question said
that only one session / connection to the database would be in use. Lord
knows, I probably did, in which case I apologize profusely.
RSH.
"Howard J. Rogers" <d...@hjrdba.com> wrote in message
news:a9r92f$vjm$1...@lust.ihug.co.nz...
Howard J. Rogers
You seem to be getting rather worked up about something, and I can't think
why.
You assert there is a performance improvement in separating indexes from
tables by housing them in separate tablespaces and placing the associated
datafiles on separate disks.
For that to make a performance difference, you must postulate that the
table and the index are accessed simultaneously - because if they are
accessed serially (ie, one after the other), then housing them on the same
disk cannot induce performance woes.
Guess what? They are accessed serially.
Don't take my word for it: test it for yourself. Don't forget to post the
results back here. Also have a look at what Thomas Kyte wrote in this same
thread... he essentially agreed that the separation of indexes from their
tables is a management nicety, not a performance necessity.
Regards
HJR
Howard J. Rogers
> I can't believe it either. Please explain Howard.
>
> And please don't just throw out jargon like RAID and striping. This
> statement appears to fly in the face of more than a few books by well
> respected authors (not that you aren't one yourself now) as well as a lot
> of HTM and PDF files put out on technet.
>
> Daniel Morgan
>
Arggh! What's there to explain? For there to be a performance difference, a
piece of DML must want to update the index and the table simultaneously. A
select statement must want to read *from* the index and the table
simultaneously. However... they don't. Table access and index access are
serialised. Separating the two segments onto separate spindles to get a
performance improvement is just chasing a mirage.
(If it makes anyone happier, I always recommending separating them in
practice because day-to-day management is easier that way).
Of course, having more spindles is always good for performance. But that's
a general thing, and has nothing to do specifically with indexes and tables
being kept apart.
Assuming you don't house each table in its own tablespace on its own
spindle, you have far more to worry about having simultaneous access by
different users to different table segments than you do about simultaneous
access to one table and its associated index. Strangely, no-one seems to
get worked up at the idea of different tables being housed in the same
tablespace... yet an index and its table! Quelle horreur!!
Read what Thomas Kyte has to say about it, in this very thread.
And I didn't mention RAID once.
Bummer... I just did.
Regards
HJR
MauMau
No prob just rebuild your indexes using a predefined tablespace
good idea to rebuild from time to time.
Connor McDonald
Separating data and indexes is a very (very) special case of the
argument for balancing IO. I would suspect that it came about
originally when everything when running rule based optimizer so your app
was doing full scans or nested loops and that was it...Even then the
advice was dubious in a multiuser database anyway...
Nowadays to satisfy queries, you're full scanning tables, full scanning
indexes, not using indexes at all, just using indexes, dumping sort
segments out, dumping temporary hash workareas out and 'n' permutations
of all them..
Quality IO balancing is important...separating data and indexes is not
the same thing
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Thomas Kyte
>
>I can't believe it either. Please explain Howard.
>
>And please don't just throw out jargon like RAID and striping. This statement
>appears to fly in the face of more than a few books by well respected authors
>(not that you aren't one yourself now) as well as a lot of HTM and PDF files put
>out on technet.
>
>Daniel Morgan
>
I'm giving a presentation next week. It's on developing successful
applications. My favorite slide in there has two words on it:
Question Authority
It is followed by a bunch of slides that have quotes from respected authorities
like this:
o segments should be in as few extents as possible
o the most selective fields must/should be first in an index
o when coding plsql, ALWAYS use explicit cursors
o rebuilding an index will save space and increase performance
o you should commit frequently to save resources and time
o index space is not reused
o adding more cpu will make all systems faster for sure
o a cold backup is better then a hot one
and more. I'll bet you can find each of those statements in a book by a famous
and respected author ;)
If enough people "believe" something or enough people say the same thing -- does
that make it true?
The theory behind putting index and data on separate devices goes like this:
"you read the index, then you read the data. We'd like to leave the head
positioned in the index structure just so -- so that the next read on it doesn't
require head movement". (or something like that).
Well, the problem with that is
o an index is a data structure. The next block you need to read from it *is not
anywhere near the block you just read* in general (the reads of an index
structure in a range scan are single block reads all over the place).
o a table is generally a heap. When you read the index block, the rows pointed
by it may be on 1 data block or 100 data blocks and the 100 data blocks are
generally NOT physically colocated by eachother.
The odds of the heads "remaining" in place is sort of silly as well. We do have
many more then one user on systems these days. I cannot remember the last time
I saw a "real" system that did not employ some form of disk mgmt where the
physical disks were visible -- logical volumes are the rule, not the exception
(not that it matters in this discussion even!!)
On a single disk to process:
select * from emp where empno = 55;
assuming EMPNO is indexed, we would do something like this:
- read the root index block from the index (SEEK, READ)
- figure out whether to go right, left, whatever in the index (assuming index
has more then root block). Assume we go "right"
- read the block pointed to by the "right" hand pointer. (SEEK, READ)
- say that got us to the leaf block with empno 55 on it. That has a rowid for
the block we need.
- we now access that block (SEEK, READ)
So, we did three SEEKS, and three READS. We did them serially.
So, if there were two disks -- would we be better off then with one? Nah, we
still need to do three random seeks and reads.
Our goal in life, should we choose to accept it, is to get a nice even
distribution of all IO across everything evenly in a multi-user system as often
as possible. Today 99% of that can be achieved at the hardware/LVM layer,
releasing our DBA's to do more important things like getting all of those tables
to fit into a single extent ;) (wonder when someone is going to say -- Hey,
we could use a cluster to fit ALL of our tables into a single extent -- think of
that, instead of a terrible extent/table, we would have one extent for say 100
of our tables -- that would be great wouldn't it!!!) That last comment was a
joke, please don't anyone take it seriously....
--
Jonathan Lewis
I suspect this came about with Oracle 5
when disc was expensive, memory was
tiny, and every Oracle block access was a GET
with no options for a PIN, so that logical I/O
was converted to physical I/O at a much higher
rate than it was with newer versions of Oracle
and larger, modern machines.
Even then it was daft because it encouraged
DBAs to put one table in tablespace X and
its N indexes in tablespace Y - thus insuring
that a single row inserted would require one
block write on one disc, and N block writes
on another.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Connor McDonald wrote in message <3CC2F8...@yahoo.com>...
Galen Boyer
> The odds of the heads "remaining" in place is sort of silly as well.
Can you explain how it is that parallel helps performance? I thought
parallel meant that a particular set of rowids where given to a query
slave while another set to another query slave and those slaves went and
got all the data between there respective ids. If they can do so
without the head jumping around from request to request (ie, on separate
disks and no other read requests), then that will be most optimal. But
if they can't, which is on every multi-user system and/or disk array out
there, then how does parallel speed things up? Is it based mainly on the
fact that there are more cpus being utilized in going after a particular
set of data? More instructions for the parallel read are being sent and
therefore executed?
PQ1 asks CPU1 which asks arrayA for some data, PQ2 asks CPU2 which asks
arrayA for some data, back and forth ... If it wasn't in parallel, then
only process1 would ask CPU1 which would be asking arrayA for some data
while other processes would be asking CPU2 which would ask arrayA for
some data?
--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Daniel A. Morgan
1. "Nowadays to satisfy queries, you're full scanning tables, full scanning indexes,
not using indexes at all,...."
Since when? I work very hard to not do these things.
2. "Quality IO balancing is important...separating data and indexes is not the same
thing"
I'll agree that I can't put an equals sign between them. But isn't one way of
guaranteeing that the goal will be met (I said one way, not the only way) to force a
physical separation?
Thanks,
Daniel Morgan
Thomas Kyte
>
>On 21 Apr 2002, tk...@oracle.com wrote:
>
>> The odds of the heads "remaining" in place is sort of silly as well.
>
>Can you explain how it is that parallel helps performance? I thought
>parallel meant that a particular set of rowids where given to a query
>slave while another set to another query slave and those slaves went and
>got all the data between there respective ids. If they can do so
>without the head jumping around from request to request (ie, on separate
>disks and no other read requests), then that will be most optimal. But
>if they can't, which is on every multi-user system and/or disk array out
>there, then how does parallel speed things up? Is it based mainly on the
>fact that there are more cpus being utilized in going after a particular
>set of data? More instructions for the parallel read are being sent and
>therefore executed?
er? this followup appears to have been made to my post but there was nothing
but nothing about parallel in there?
I'll guess as to what you might mean. In another posting in this thre someone
(believe it was HJR) said words to the effect of "the placement of index
separate from table might make sense IF index and table data were accessed in
parallel. Since they are not, they are accessed serially and since they are
accessed serially having them separated matters not".
If that is what you are referring to, then it is straight forward. If the index
were accessed concurrently with the table, then it would make sense from a LOAD
BALANCING perspective to have them on separate devices. They are not so -- it
doesn't.
Our goal is to achieve even distribution across all devices. If in your case,
the best you can do is put index on one -- table on another -- thats great. In
general, putting index separate from data won't do that (achieve a balance).
Striping and other technologies will.
>
>PQ1 asks CPU1 which asks arrayA for some data, PQ2 asks CPU2 which asks
>arrayA for some data, back and forth ... If it wasn't in parallel, then
>only process1 would ask CPU1 which would be asking arrayA for some data
>while other processes would be asking CPU2 which would ask arrayA for
>some data?
>
>--
>Galen deForest Boyer
>Sweet dreams and flying machines in pieces on the ground.
--
Thomas Kyte
>
>Please help me here with two things.
>
>1. "Nowadays to satisfy queries, you're full scanning tables, full scanning
>indexes,
>not using indexes at all,...."
>
>Since when? I work very hard to not do these things.
depends on the system you are building. Last benchmark I did ran best without
indexes on the tables in most cases. DW vs OLTP.. Full scan away. Every now
and again I would create an index for the sole purpose of using it as a
"skinnier" table and fast full scanning it.
>
>2. "Quality IO balancing is important...separating data and indexes is not the
>same
>thing"
>
>I'll agree that I can't put an equals sign between them. But isn't one way of
>guaranteeing that the goal will be met (I said one way, not the only way) to
>force a
>physical separation?
no, could be that indexes are more "cacheable" -- you could end up with phyiscal
IO on the data and none on the index after a bit. Now you have an hot disk and
a cold disk.
Or conversely -- if the cacheability of the index is questionable, you could end
up doing 3 or 4 physical IO's on the index to get to a single data block (that
was already cached)
Now, if I took a single tablespace with two datafiles -- one on disk1, one on
disk2 and created the index and table in the same tablespace and used lots of
extents for each -- I would achieve a nice even striping across the two disks --
evenly distributing the data across the two devices (as we tend to allocate an
extent from datafile1, then datafile2, then datafile1 and so on for the objects
-- in the goal of spreading the IO around)
Now -- regardless of the cacheability (or lack thereof) I get a nice even
distribution of physical IO in most/many cases...
Hey another reason why the "single extent theory" doesn't hold up ;)
--
Galen Boyer
>> The odds of the heads "remaining" in place is sort of silly as well.
>>Can you explain how it is that parallel helps performance? I thought
>>parallel meant that a particular set of rowids where given to a query
>>slave while another set to another query slave and those slaves went
>>and got all the data between there respective ids. If they can do so
>>without the head jumping around from request to request (ie, on
>>separate disks and no other read requests), then that will be most
>>optimal. But if they can't, which is on every multi-user system
>>and/or disk array out there, then how does parallel speed things up?
>>Is it based mainly on the fact that there are more cpus being utilized
>>in going after a particular set of data? More instructions for the
>>parallel read are being sent and therefore executed?
>
> er? this followup appears to have been made to my post but there was
> nothing but nothing about parallel in there?
Oops. I should have renamed the subject. Your description of the
placement of indexes and tables not affecting performance just triggered
a question I've had for awhile on PQ. I'm not asking about indexes and
tables here. I'm asking about parallel scans of a particular table.
(Indexes and tables are quite similar to partitions of tables)
AFAIU, PQ, in a most simple setup, would work best if a table were to be
split across multiple disks and with only one process running. When the
PQ is issued, Oracle can notice what rowids exist on what disk, assign a
PQ slave those particular rowid sets and those disks can all be scanned
in parallel. The disk head can start at one section of the disk, and
without jumping around, just straight scan the disk. Now, this makes
sense to me. But, then introduce Disk Arrays, RAID, and other hardware
configurations. Now, how does Oracle use PQ to speed up scans of
tables? How does Oracle assign the correct rowid sets so that the
multiple cpus assigned to the scanning task don't make the disk head
jump all over the place as new read requests come in?
Sorry for the redirection without the notification.
Daniel A. Morgan
With the respect to the rest of it ... fascinating.
So who at Oracle is charged with correcting all of the information that goes out under
its name. Not just at Oracle Press, but at technet, tahiti, etc.
Once again thanks for the clarification.
But is there a general rule that developers can follow. Each development project can
not turn into an exploration of what "common knowledge" is wrong. Or trying out a
multitude of tablespace, extent combinations to find the one that is optimal. Some
rules must be applied or no rules are applied.
Thanks,
Daniel Morgan
Daniel A. Morgan
separation was that with multiple CPUs and multiple drives, on multiple controllers
Oracle could simultaneously read tables and indexes. Apparently this is not the
case.
So when I look at parameters such as multi_block_read_count the idea that putting
table in a single extent, meaning a single physical area, will allow the heads to
read large sections without the heads having to move is also not the case? If not,
what is the point of a multi_block_read. My assumption based on what is being
written in this thread is that the ideal situation is for the heads on a hard disk
to bounce all over the place. One block here, one block there.
If you don't like the old rule. And I am hardly wedded to it. What would you replace
it with? Chaotic access?
Daniel Morgan
Mark Townsend
damo...@exesolutions.com wrote on 4/21/02 12:01 PM:
> So who at Oracle is charged with correcting all of the information that goes
> out under
> its name. Not just at Oracle Press, but at technet, tahiti, etc.
The relevant people for technet and tahiti are reading the newsgroup. If you
see anything specific, by all means point it out.
Jonathan Lewis
Comments in-line
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel A. Morgan wrote in message <3CC2CB08...@exesolutions.com>...
>Please help me here with two things.
>
>1. "Nowadays to satisfy queries, you're full scanning tables, full scanning
indexes,
>not using indexes at all,...."
>
It is very easy to misunderstand the intent of a comment
on the newsgroup, but I think if you re-read Connor's post
carefully you will appreciate that he is not saying "you must
full scan tables"; he is simply listing a number of the mechanism
that are commonly (and necessarily) activated in a modern system.
>Since when? I work very hard to not do these things.
>
Don't work too hard at it. Sometimes the biggest
performance problems are introduced by trying
to push Oracle too hard into paths which use
index range scans and index unique scans to
access tables by rowid; resulting in excessive
CPU usage and CBC latch contention.
Howard J. Rogers
news:3CC30E18...@exesolutions.com...
> Please excuse me if I beat this one to near death. But I thought the point
of
> separation was that with multiple CPUs and multiple drives, on multiple
controllers
> Oracle could simultaneously read tables and indexes. Apparently this is
not the
> case.
>
> So when I look at parameters such as multi_block_read_count the idea that
putting
> table in a single extent, meaning a single physical area,
Ah! But it doesn't. It's this same idea we came up against a while back.
Extents are not contiguous on disk. Even an Oracle block does not need to be
contiguous on disk. On disk are file system blocks (for want of a better
term), and they can be scattered all over the place, yet you (and Oracle)
treat them as a single block or a single extent.
So a single extent does *not* mean "a single physical area", but a single
logical one.
As to why m_b_r_c speeds up full table scans, it has nothing to do with
positioning the heads once and then vaccuuming up the blocks as they waltz
pass. It has rather more to do with how many O/S blocks can be read with one
read request being issued by the process doing the reading before second and
subsequent requests must be issued.
>will allow the heads to
> read large sections without the heads having to move is also not the case?
Definitely not the case.
>If not,
> what is the point of a multi_block_read.
You cut down on the number of requests a process has to issue and the number
of responses it has to deal with.
>My assumption based on what is being
> written in this thread is that the ideal situation is for the heads on a
hard disk
> to bounce all over the place. One block here, one block there.
>
But that's going to happen anyway, because an extent is not contiguous on
disk.
> If you don't like the old rule. And I am hardly wedded to it. What would
you replace
> it with? Chaotic access?
>
Even I/O across all disks, regardless of what segment types they happen to
contain.
Easy.
Regards
HJR
Thomas Kyte
>
>Please excuse me if I beat this one to near death. But I thought the point of
>separation was that with multiple CPUs and multiple drives, on multiple
>controllers
>Oracle could simultaneously read tables and indexes. Apparently this is not the
>case.
>
for a single user, this is not possible. The access plan would be something
like:
table access by rowid
index range scan
In order to do the table access by rowid - you need the rowid. to get the rowid
you need to scan the index. Hence, they are serial operations in this case --
one after the other.
Read index, then read table.
Now, if you have 2 users -- one of them could be reading the table -- the other
the index at the same time. Nothing stopping that.
Then again, they could both be reading the index and then both be reading the
table at the same time (contending with eachother for this shared resource)....
even distribution of IO, thats the goal. whatever gets you there is good.
>So when I look at parameters such as multi_block_read_count the idea that
>putting
>table in a single extent, meaning a single physical area, will allow the heads
>to
>read large sections without the heads having to move is also not the case? If
>not,
>what is the point of a multi_block_read. My assumption based on what is being
>written in this thread is that the ideal situation is for the heads on a hard
>disk
>to bounce all over the place. One block here, one block there.
if files were contigous, Norton Speed Disk never would have been invented.
files are not contigous in general (we wouldn't have to defrag if they were).
At least not since you had to use the CCF tool in v5 to create a file (create
contigous file).
multi-block-read count lets us efficiently ask the OS to get us N number of
blocks as fast as possible (as good as it can). Analogy: instead of going to
the library and asking for page 1 of a book, then page 2 and so on -- we ask for
the book. There is much less context switching going on. Even if the book must
be assembled from its various chapters -- it'll be faster to ask for the book
then the component pieces.
>
>If you don't like the old rule. And I am hardly wedded to it. What would you
>replace
>it with? Chaotic access?
>
evenly distributed IO is what I would replace it with. I love these disks these
days where I am not allowed to pick the placement of files since its all
virtualized for me. One big mount point, 10's or 100's of disks underneath with
lots of stuff going on that I quite simply "cannot care about" anymore -- the
hardware guys, they care -- they move things around to keep the IO even, I
don't.
In the olden days, I used to use many files/tablespace spread out across many
devices. Put objects into many extents to get them spread out. A "poor mans
stripe". Today -- I let the hardware do it.
Thomas Kyte
>
>If the only reason you are building an index is for scanning it why not an IOT?
>
the index is a "skinnier" version of the table.
an IOT is organized by the primary key. I needed a non-primary key set of
columns...
>With the respect to the rest of it ... fascinating.
>
>So who at Oracle is charged with correcting all of the information that goes out
>under
>its name. Not just at Oracle Press, but at technet, tahiti, etc.
>
it is hard to kill myths, they die slow painful deaths many times.
if you point me to explicit doc refs, I'll "bug them"
Oracle press -- cannot do a thing about that. anyone can publish for Oracle
press.
Jonathan Lewis
And that only worked on VAX, I believe.
UNIX filesystems even have (had) a
parameter to restrict how much of a
single file would be created on a single
track before the heads were moved on
a track anyway. Not sure what the reasoning
was - possibly something to do with leaving
space in the track for updates to the file,
but a bad idea as far as Oracle files were
concerned.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Thomas Kyte wrote in message ...
Dusan Bolek
> Our goal is to achieve even distribution across all devices. If in your case,
> the best you can do is put index on one -- table on another -- thats great. In
> general, putting index separate from data won't do that (achieve a balance).
> Striping and other technologies will.
Even distribution may not be always the main goal. Sometimes we even
want the exact oposite - isolating some heavy load on a ew devices.
For example some heavy workload - backup, export of data, adding disk
to array. Maybe we do not care how long this takes, but very important
can be to minimalise negative impact on response time for ordinary
users. Of course, access to data in the same tablespace/same disc is
very slow, but other parts of database has no problem with this heavy
I/O. If we've used an all drives stripping method, then every single
tablespace will suffer with this I/O.
Or partitioning issues. For example historical data and current data
in one database. Current data are heavily accessed and we want no time
response to them, historical data are used by some analytics for
extensive queries. They can (and must) wait, so high I/O is no problem
for them, but online clerks needs a low I/O to get data ASAP.
Recovery from HW can be another problem. If some disk needs to be
replaced, then while using smaller sets, we would also have a quickier
rewriting of data to a new one.
So I think that even with all great technologies (like striping, array
caching, ...), very careful placing planning is still an important
part of DB design.
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.
Daniel Morgan
---------------------------------------------------------------------------------
Place Datafiles Appropriately
Tablespace location is determined by the physical location of the datafiles that
constitute that tablespace. Use the hardware resources of your computer appropriately.
For example, if several disk drives are available to store the database, it might be
helpful to store table data in a tablespace on one disk drive, and index data in a
tablespace on another disk drive. This way, when users query table information, both
disk drives can work simultaneously, retrieving table and index data at the same time.
http://technet.oracle.com/doc/server.815/a67772/dfiles.htm#423
---------------------------------------------------------------------
Place Datafiles Appropriately
Tablespace location is determined by the physical location of the datafiles that
constitute that tablespace. Use the hardware resources of your computer appropriately.
For example, if several disk drives are available to store the database, consider
placing potentially contending datafiles on separate disks.This way, when users query
information, both disk drives can work simultaneously, retrieving data at the same
time.
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90117/dfiles.htm#7384
---------------------------------------------------------------------
You see what I mean about Oracle's own literature. You, and others here, have clearly
stated that the concept of simultaneously retrieving data is not valid and yet every
reference I can find at technet and tahiti states that this is the case. If Couchman
and others, myself included, repeat this the source is Oracle. And the first
reference, as you can see, clearly suggests separating tables and indexes to improve
IO.
So is Tom Kyte another one of those 'experts' we should ignore? Just kidding? Or can
we get corrections to these things pubished in Oracle Magazine so that they get wider
distribution. Perhaps you could make your slideshow presentation available for
download at AskTom? And, if so, I would like permission to present it to my students.
Well here I go revising the curriculum again. ;-)
Thanks.
But can I get you to weigh in with a repacement rule or guideline?
Thanks again.
Daniel Morgan
8.1.7
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/dfiles.htm#423
9i
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90117/dfiles.htm#7384
If you want the rest go to technet and type in the following text in the "Search
for:" box: "Place Datafiles Appropriately". Same advice goes for tahiti. It's
everywhere it's everywhere.
As Pogo said: "We have met the enemy and it is us."
Daniel Morgan
Daniel Morgan
etc. And given that many of my application tables have been in the
multi-gigabyte range that would be a complete non-starter.
When one is looking for a single part of a Boeing 747 ... one does not want to
scan every part for every plane ever made.
Daniel Morgan
Daniel Morgan
single most valuable thread in this group all year.
Thank you.
Daniel Morgan
Jonathan Lewis
I think the problem you've particularly highlighted here,
Daniel, is the massive inertia that keeps manuals (and
users, and 'experts') permanently wrong.
It looks as if the two extracts are the matching entries
from the 8.1.7 and 9.0.1 manuals - and the 9.0.1 manual
has FINALLY corrected an error that has been reproduced
faithfully from the 6.0.27 manuals onwards.
Of course, it could always go further and suggest better ways
of using disks to spread the I/O load evenly - but at least it
has dropped the traditional error of indexes/tables in favour
of a more 'neutral' comment about 'contending data files'.
Unfortunately, few of us have the time to reread all 20,000
pages of the manuals on each new release to find the odd
little paragraphs which are adjustments to previous errors -
so the errors keep getting quoted.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CC4351E...@exesolutions.com>...
Niall Litchfield
much of the same ground as well.
That recommends striping across multiple disks to get even IO as Tom,Howard
& Jonathan have been saying. It also recommends a stripe size of 1mb. It
does have the merit (from memory) of being based on extensive testing as
well.
--
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
******************************************
"Daniel Morgan" <damo...@exesolutions.com> wrote in message
news:3CC4351E...@exesolutions.com...
Scott Gamble
> This is repeated in many places but here are the first two links to it
> I found.
> ------------------------------------------------------------------------
> --------- Place Datafiles Appropriately
>
> Tablespace location is determined by the physical location of the
> datafiles that constitute that tablespace. Use the hardware resources
> of your computer appropriately.
>
> For example, if several disk drives are available to store the
> database, it might be helpful to store table data in a tablespace on
> one disk drive, and index data in a tablespace on another disk drive.
> This way, when users query table information, both disk drives can work
> simultaneously, retrieving table and index data at the same time.
I read this as multiple 'users' querying the table at the same time, which
means the drives could be used simultaneously.
Query A reading the index, query b reading the table.
RSH
unless Oracle's internal machinery now includes Query Row Precognition,
where data about to be asked for is loaded into cache before the SQL hits
the RDBMS.
Which would be kind of like 'thiotimoline', the mythical chemical Isaac
Asimov invented that annoyed chemists because it wood dissolve just before
the chemist added the solvent, only if he really WAS going to dissolve it.
In any case, no matter how many books have been authored by whom, and no
aspersions meant, some of my best friends have written books, unless CBO
decides an index (like a regular index on a field like "GENDER") [even
before BMI's, can you imagine people indexing that???] would be dumb as heck
to use, and dives direct in for a full scan; if statistics are kept fresh,
indices and keys are intelligently constructed, I simply don't get how one
can say (in Martha Stewartese) "Putting tables and the indexes they pertain
to on the same physical device is 'a good thing'"
I'm probably just too stupid to understand this.
Also, the term "data warehouse" has been thrown about with an implication DW
= full scans; there I do have to beg to differ. I don't know how other
people build their data warehouses, but my goal has always been, to the best
degree possible, to anticipate all the most common and rational ways users
would want to view and use the information, and construct accordingly.
Say you have half a billion rows or more coming in from feeds originating
from 80+ odd systems worldwide (hypothetically), and you don't happen to
have more money than God. (Or, your corporation does have more money than
God, but they won't give you much of it.)
So you do your flat table loads and then:
1. Extraction - pulling out atomic level rows based upon very picky WHEREs
and storing that particular data 'as is'.
2. Summation - collapsing rows along particular criteria (say, rolling up
all MJEs to a journal date/company/division/etc level) and adding them to
your derivative data sets.
3. Accumulation - applying rows as transactions to tables that maintain year
to date, forever-to-date, etc balance type data.
In our hypothetical systems, we did the apparently old fashioned load as is,
as fast (i.e. DIRECT, etc) into huge input tables and then had a set of
PL/SQL processes that ran to do the rest.
Not sorting out the physical storage, using several tiers of balancing
decisions, would have been insanity. And we knew insanity, because our
development - pre-production box had been set up with no thought given to
these little details. And it was a pig.
Of course, I was hypothetically using raw partitions for anyplace Oracle
would have tablespaces, diced up the RAID-1 farm into uniform 250 Mb chunks
(except of course where file systems were living) as our hypothetical
platform couldn't support VTOCS more finely diced, and the games began,
including whiteboarding and a lot of thought and planning in the core team
of three.
1. Tables and their indices (including the indices behind their PK's, UI's,
the works) must not be on the same device.
2.Incoming feeder flat files live on spindles devoted to that purpose alone.
3. REDO lives by itself.
4. Raw disks shall never have any UNIX file systems on them.
5. Tables (and their associated indices) often used together should be
spindle separated as much as possible (in addition to #1).
6. Large, flat tables (like our enormous feeder tables) should be striped
across at least CPUCNT / 2 spindles, and not co-reside with tables or
indices related to the derivative data, since that would be r/w head madness
x 100.
7. The file system based disks including, especially, the enormous area
reserved for incoming flat files, should be on a different controller than
the feeder tables loaded by SQL*LOADER.
8. Balance again, making compromises based on what you can buy for the
budget you managed to beg and grovel for.
I can't speak for anyone else, but this worked well for us. (hypothetically)
I think VERITAS and the other tools that abstract physical actual storage to
the point even the system administrators can't tell you where things
actually live, do not help matters. RAID-5, where bits of things live all
over, isn't a dream either, since the best you have then is rank diversity
and controller diversity.
In my simple, corncob-pipe smoking mentality, this seems like arguing that a
bridge with two tollbooths works just as well as one with ten, and I just
don't see how that makes sense.
RSH.
"Daniel Morgan" <damo...@exesolutions.com> wrote in message
news:3CC4351E...@exesolutions.com...
Connor McDonald
I'll give an example of what I was trying to convey in terms of "full
scans good sometimes, full scans bad sometimes" using an example
somewhat poached from JL's excellent seminar
create table BOEING1 ( x number, y char(100));
create table BOEING2 ( x number, y char(100));
create index BOEING1 on BOEING1 ( x);
create index BOEING2IX on BOEING2IX ( x);
10000 rows inserted into each table, 200 rows for each value of x from 0
to 49. So after running an analyze...
analyze table BOEING1 compute statistics;
analyze table BOEING2 compute statistics;
we can see that they are virtually identical..
select table_name, num_rows, blocks, empty_blocks
from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BOEING1 9999 153 6
BOEING2 9999 153 6
select table_name, column_name, NUM_DISTINCT,DENSITY
from user_tab_columns
where TABLE_NAME like 'TE%';
TABLE_NAME COLUMN_NAME
NUM_DISTINCT DENSITY
------------------------------ ------------------------------
------------ ----------
BOEING1 X
50 .02
BOEING2 X
50 .02
select max(x), min(x), avg(x) from BOEING1;
MAX(X) MIN(X) AVG(X)
---------- ---------- ----------
49 0 24.5024502
select max(x), min(x), avg(x) from BOEING2;
MAX(X) MIN(X) AVG(X)
---------- ---------- ----------
49 0 24.5024502
But they exhibit spectacularly different performance characteristics...
select /*+ FULL(t) */ count(y) from BOEING2 t
where x < 20;
took 156 consistent gets
select /*+ INDEX(t) */ count(y) from BOEING2 t
where x < 20;
took 70 consistent gets,
thus you get the "conventional" result of "we should be using an index"
when examing this particular BOEING. But the same queries against
BOEING1 give
select /*+ FULL(t) */ count(y) from BOEING1 t
where x < 20;
took 156 consistent gets
select /*+ INDEX(t) */ count(y) from BOEING1 t
where x < 20;
took 3120 consistent gets (20 times worse!)
So - sometimes checking every Boeing ever made is the best option :-)
Cheers
Connor
Daniel Morgan
with you. At 10,000 rows the only index I would create would be one created by a
required constraint. But try that same experiment with the 1.4 million parts in a
747, understanding that every part may be in there multiple times (for example at
one time there was a choice 21 different pilot clipboards). Then add the sub-models
(different seating capacity, different galleys, etc.) then add 717, 737, 757, 767,
and 777 models.
You are correct with the example you gave. But I can't believe that with
100,000,000+ rows you would ever find this to be the case. Am I wrong?
Daniel Morgan
Howard J. Rogers
there is a need to cling to what passes for security in old (albeit wrong)
knowledge. Saw the exact same behaviour about 6 weeks ago with a certain
poster who shall remain nameless stating that contiguous extents were
performance-boosters... and kept stating it even though the theory was
explained to him, and *kept* stating it even when I posted the statistics
that contradicted his assertion.
Now here we have a long thread explaining why tables and indexes don't need
to be separated physically. Tom explains it; Jonathan explains it; I explain
it.
So naturally, the first "rule" that gets re-posted aas though it were gospel
is: "1. Tables and their indices (including the indices behind their PK's,
UI's, the works) must not be on the same device."
What was that about horses and drinking?
HJR
"RSH" <RSH_O...@worldnet.att.net> wrote in message
news:guYw8.40277$Rw2.3101459@bgtnsc05-news.ops.worldnet.att.net...
Connor McDonald
The example I displayed is somewhat table size independent - that is,
the queries are requesting approx 40% of the table rows. The example
shows that in some cases, the index is easily the best option, in other
cases, the table scan is easily the best.
If you had 100,000,000 rows it wouldn't matter - if the question
requires probing 40% of the rows, then the index vs scan decision still
needs to be evaulated
Daniel Morgan
data in the table. That has a huge potential impact.
Obviously in a table with 100,000,000+ rows only someone crazier than I would try a stunt
like that. And if they do I hope they use the +ALL_ROWS hint.
In my experience queries like that are generally scheduled for 2:00am on a Sunday. The
more general query that returns all rows, or a large percentage of rows, is generally one
from a look-up table.
So at the risk of rubbing salt into a wound ... it appears that everyone counseling not
separating tables and indexes is avoiding providing a replacement rule of thumb. Why no
takers? ;-) What is the verbiage you think would be valid at technet and tahiti as advice
for developers and DBAs?
Howard Rogers
The replacement advice is: even out the I/O distribution across all devices.
If you want it more wordy, the Oracle 9i doco you quoted earlier seems to
have got it about right: avoid placing contending datafiles on the same
device. That covers everything, really, on the assumption that users don't
leap to the erroneous conclusion that indexes and their tables inevitably
contend.
Regards
HJR
"Daniel Morgan" <damo...@exesolutions.com> wrote in message
news:3CC4969D...@exesolutions.com...
Mark Townsend
Cheers - you will of course note that the wording has changed between the
versions. This change is for a reason - to remove the myth about indexes and
data needing to be seperated.
This myth has been around for a long time - and unfortunately was actually
started by an Oracle consultant (who will remain nameless). So it does
permeate some of the earlier docs (which is why I asked you to point it out
for me), but is slowly and steadfastly being exposed for the myth it is.
For the (currently) definitive document on data/disk placement, see the
white paper on SAME (Stripe And Mirror Everything) at
http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf
This is a good, generic replacement guideline that suits most workloads and
also drives HA. It IS, however, a generic set of guidelines, and should not
be treated as dogma. It is also subject to change as Oracle's IO profile,
and the underlying capabilities of disk storage, evolve.
in article 3CC43663...@exesolutions.com, Daniel Morgan at
damo...@exesolutions.com wrote on 4/22/02 9:12 AM:
Daniel A. Morgan
and tie a ribbon around it.
I am looking at at Kevin Loney and Marlene Theriault's book Oracle 8i DBA
Handbook, page 97, section title I/O Contention Among Datafiles. And the next
page where Table 4-2 contains estimated I/O weights. This is one of the books I
use to teach my classes at the U.
Based on this material my conclusion, and the one I teach my students, is that
putting indexes into the same tablespace with data is a bad thing. In fact it
seems to me that a reasonable conclusion would be that putting anything else
with data is a bad thing. If Loney and Theriault are incorrect ... and we all
agree that the goal is I/O distribution. Could you please provide an corrected
table that would serve as a guideline as to where the I/O is? I don't mean a
table that will be used to put your feet to the fire ... but something that,
like Loney and Theriault's, can be used as a guideline.
Thanks. I hate the thought that I am teaching the next generation of Oracle
developers and DBAs things that are not correct.
Daniel Morgan
Howard J. Rogers
touch it with an exceptionally long bargepole). I'm willing to give it a go,
if I can lay my hands on the requisite original to see the basis of their
table. Unless someone else wants to have a go in the meantime....
HJR
"Daniel A. Morgan" <damo...@exesolutions.com> wrote in message
news:3CC480A0...@exesolutions.com...
Daniel Morgan
I don't have it here at the office. But someone had the 8.0 version of their
book and they use the following as an example in that one:
Disk Weight Contents
1 Oracle Software
2 35 System, Control file 1
3 40 RBS
4 100 DATA
5 33 INDEXES
6 9 TEMP
7 3 TOOLS
8 40+ REDO LOGS
9 40+ APPLICATION & ARCHIVED REDO
It is not hard, from the above, for a reasonable person to say ... I'm not
putting anything on the same physical disks holding my indexes. Is the above
table reasonably accurate and you disagree with the conclusion? Or is the above
table inaccurate in which case I invite you to provide values you think better
reflect the real world.
Also, to what extent is this discussion predicated on having RAID and Veritas or
other disk striping as opposed to raw devices?
Thanks.
Daniel Morgan
Connor McDonald
>
> Great ... we are 70% of the way there. Lets see if we can but the top on the box
> and tie a ribbon around it.
>
> I am looking at at Kevin Loney and Marlene Theriault's book Oracle 8i DBA
> Handbook, page 97, section title I/O Contention Among Datafiles. And the next
[snip]
Here is quote from a Loney book (can't remember the title offhand)
"...you should specify a very low value for pctfree ...the space used by
deleted entries within an index is not reused..."
which pretty much sums up the amount of credence I place on that book
Daniel Morgan
better than the table Loney and Theriault created? Or is all of the discussion here
just off-the-cuff remarks not based on actual testing?
Thanks.
Daniel Morgan
Howard J. Rogers
their
> book and they use the following as an example in that one:
>
> Disk Weight Contents
> 1 Oracle Software
> 2 35 System, Control file 1
> 3 40 RBS
> 4 100 DATA
> 5 33 INDEXES
> 6 9 TEMP
> 7 3 TOOLS
> 8 40+ REDO LOGS
> 9 40+ APPLICATION & ARCHIVED REDO
>
> It is not hard, from the above, for a reasonable person to say ... I'm not
> putting anything on the same physical disks holding my indexes.
Why? Can't you add 35+33+9+3 and still come in at much less than 100?
So what I'm really saying is: what do those weights mean? I'm sure they're
not kilogrammes, but apart from that, they seem pretty meaningless. Are they
trying to say that writes and reads from DATA are 3 times more frequent than
from INDEXES? Or 3 times as expensive? Some seem peculiarly precise: 9 for
TEMP, for example, and not, say, 5 or 10.
>Is the above
> table reasonably accurate and you disagree with the conclusion?
I can't disagree with it until I know what it actually means. That means
knowing how they arrived at those numbers, and what significance they are
invested with in their text. For which I have to get hold of a copy of their
book.
Regards
HJR
Jonathan Lewis
As I understand it, everyone is saying to you
the world is full of colour
and your reply is
I want it in black or white please.
You've told us several times that you are a teacher -
you should be teaching people to think and
understand; not scrabbling about for mantras
and simple tables and tick boxes.
Does the Loney book explain the meaning of
'weight' ? Does it justify the numbers that is
has ascribed to the default install database ?
Do the arguments make sense, can you extrapolate
them to more complex cases ?
Does the book consider the fact that an awful lot
of small systems consist of nothing but a little
black box with 2 CPUs and an on-board hardware
RAID-5 with 45GB in 5 discs; and that not many
small systems have 9 discs, and the systems that
do have 9 discs probably have more than one data
tablespace and one index tablespace ?
I understand your request for a better (performance)
guideline. But the trivial, mindless, yet precisely
detailed mantra you seem to want does not exist.
However, try this as something to present to
your students:
a) Be aware of what your database has to do
b) Understand how indexes work and what they cost
c) Understand how UNDO and redo work
d) Understand the possible data access mechanisms that Oracle users
e) Identify the parts of your database that are necessarily
read-intensive
f) Identify the parts of your database that are necessarily
write-intensive
g1) For administrative reasons separate parts of your system with
different
I/O characteristics into separate modules. (One consequence of this
is that in general no tablespace will hold tables and indexes
because
in most cases table access behaviour can be different from index
access behaviour)
g2) For administrative reasons (sub)section your system into units
that can be categorised by size.
g3) For administrative reasons (sub)section your system into units
that can be categorised by backup/restore requirements.
g4) For administrative reasons consider reducing the number of units
generated by g1, g2, g3 above.
h) Allocate estimates of physical I/O costs to each unit specified
in g4
i) Spread the units from g4 across the available devices with a
view to balancing the I/O evenly whilst avoiding significant contention
between units of significantly contradictory characteristic behaviour.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CC59B1B...@exesolutions.com>...
Daniel Morgan
I'm not asking for black and white. Obviously that does not exist here any more
than I can look at a SQL statement and guess in advance its cost.
But that does not mean that every single time someone goes into a development
cycle for an application they should start by putting everything into the SYSTEM
tablespace. Then add a single tablespace for data and indexes. Then break that
up. Then add more, etc. and test every single possible permutation.
I have worked numerous times with consultants from Oracle's own consulting
division and not once have I ever seen one of them question the "rule" about
separating tables and indexes. And that goes for consultants from lots of other
name companies.
Let me try proposing a rule and I'll let you agree or disagree.
The rule:
The goal is to balance I/O across as many separate physical devices, hard disks
and controller channels, as possible. One method of doing this is to separate
system, table, index, temp, and rollback files onto seprate physical devices. If
performance problems exist it may be due to the way the data is being utilized.
Verify the nature of the performance issue and if it relates to I/O balancing
you may need to look at modifying where objects and datafiles are placed in
order to optimize performance.
Does that work?
I feel like a great deal of effort is being expended to drag out a simple
general statement. But I definitely appreciate you sticking with me on this. I
can't just tell students: "The books are all wrong, the experts are all wrong,
and no one in the Oracle development community has a clue where to start." Nor
do I think it appropriate to tell them that one group of experts is calling
another group of experts amateurs.
Thanks.
Daniel Morgan
Sean M
>
> I feel like a great deal of effort is being expended to drag out a simple
> general statement. But I definitely appreciate you sticking with me on this. I
> can't just tell students: "The books are all wrong, the experts are all wrong,
> and no one in the Oracle development community has a clue where to start."
Here's the problem Daniel: you want to replace the old adage "separate
tables and indexes" with a new adage. Problem is, the old addage *was
never right*, at least for the vast majority of real world, multi-user
systems. So what makes you think there's a new one to replace it? If
you're looking for general recommendations for distributing your I/O,
the SAME paper is probably your best bet:
http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
Basically, to use the toll booth analogy, you want to have as even a
distribution of vehicles going through however many toll booths you
have. If only one toll operator is allowed to service trucks, and
another only motorcycles, and a third only sedans, etc., eventually one
booth is going to back up. But if every booth is capable of servicing
every vehicle at random, voila, even distribution. Now, *maybe*, for
special applications and with very careful testing, you *might* manage
to do better by manually allocating tablespaces to physical devices.
But if you do manage to do better, it won't be very noticible, and
you'll have spent far more time trying. Furthermore, the minute you
need to add a new tablespace with new requirements, it's time to start
all over again testing the manual process and moving datafiles around.
No fun to manage.
Is SAME perfect? No. But if a general approach that works well for the
majority of databases is what you're after, SAME is about as close as
you're going to get.
Regards,
Sean
Jonathan Lewis
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CC5B90A...@exesolutions.com>...
>
>I'm not asking for black and white. Obviously that does not exist here any
more
>than I can look at a SQL statement and guess in advance its cost.
>
I wouldn't claim to be able to guess the CBO-calculated cost - but in lots
of the more straightforward cases I make it my business to estimate
reasonably accurately the real cost in logical and physical I/O of
high-profile
statements in the very early stages of design. That may be why I can appear
to be so casual about the issue of deciding on balancing I/O.
>
>I have worked numerous times with consultants from Oracle's own consulting
>division and not once have I ever seen one of them question the "rule"
about
>separating tables and indexes. And that goes for consultants from lots of
other
>name companies.
But did you ask them every single time if they were doing it because
it improved performance or if they were doing it because it was an
administrative benefit for monitoring activity and trapping errors.
Perhaps if you had asked, some of them would have explained that it
had little impact on performance.
>
>Let me try proposing a rule and I'll let you agree or disagree.
>
>The rule:
>The goal is to balance I/O across as many separate physical devices, hard
disks
>and controller channels, as possible.
Stop here, and you're doing okay.
>One method of doing this is to separate
>system, table, index, temp, and rollback files onto separate physical
devices. If
>performance problems exist it may be due to the way the data is being
utilized.
>Verify the nature of the performance issue and if it relates to I/O
balancing
>you may need to look at modifying where objects and datafiles are placed in
>order to optimize performance.
Another method is to stick files anywhere you fancy, then if
performance problems exist it may be ..... ;)
More significantly: SAME (as previously mentioned) is the
best 'low-skill', 'low-risk' option around.
If you want a simple starting approach for unexceptional
database systems, and you absolutely cannot stripe
your filesystems :-
Reserve drives for redo
Reserve drives for archived redo
Create one rollback tablespace per device
Create a couple of files per drive for a TEMP tablespace
Allow about two to four tablespaces per drive for tables
Allow about two to four tablespaces per drive of indexes
(The separation of indexes and tables is for admin
NOT performance reasons)
Allocate segments to tablespaces in a way that you feel
will balance physical I/O, and map objects of about the
same size to the same tablespace.
Start looking for special cases, e.g. a very large table
with highly random I/O requirements, which may need
its own tablespace spread at one file per device.
And before you do all that, try to work out when you
are going to need to grow into some new disks and
figure out how you can fit them into the pattern
without rebuilding the entire database.
Howard J. Rogers
the real issues involved in a RAID-less set up, and it's what I would have
posted if I'd been less tied up with other things.
Best Regards
HJR
[Snip]
Daniel Morgan
Thank you. Exactly what I ahve been looking for. The ribbon has been tied, the
card signed, and the envelope sealed.
I really appreciate the effort. In three and one-half hours this thead will
become "material" in Mary Gates Hall. Yep I teach tonight in a building built by
Bill Gates and named after his mother.
Daniel Morgan
Niall Litchfield
The relevant section of the book is headed IO contentention among datafiles
(p83 in my copy v8 but Loney alone listed as author). The 'weight' is the
estimated relative IO load on the datafiles. Thus the procedure that the
author starts off following is based on the , somewhat familiar, goal of
distributing IO among available devices. My reading of the section is that
the procedure being suggested is not at all unreasonable since it is
pressing upon us as DBAs the idea that we need to understand the IO
requirements of our databases. Unfortunately at the end of the section we
get the statement that "These weightings serve to illustrate several
cardinal rules about ... file placement. : the DATA tablespaces should be
seperated from their INDEXES.." This is a case of drawing an unjustified
general conclusion from a given example - I guess we've all done that from
time to time. It should also be noted that the suggested practice is also to
monitor the actual IO as compared to the estimated IO (and change locations
of datafiles accordingly).
My commentary on this section would be
1. Yes the weights given are too precise (unless you are moving an existing
db to new hardware in which case you can measure the values over a period).
2. The goal is still IO balancing not seperating index and data per se
(though as jonathan states they may have a different IO profile and so need
to be seperated for that reason).
3. None of this received wisdom deals hugely satisfactory with heavily
cached raid arrays and logical volume managers. It is all predicated on
individual disks.
4. It always pays to read the argument as well as the conclusion and
consider whether the one follows from the other and even if it does wether
the argument is valid.
Pete Sharman
>
>
>Cheers - you will of course note that the wording has changed between the
>versions. This change is for a reason - to remove the myth about indexes and
>data needing to be seperated.
>
>This myth has been around for a long time - and unfortunately was actually
>started by an Oracle consultant (who will remain nameless).
And it wasn't me!
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Nuno Souto
> >
> >This myth has been around for a long time - and unfortunately was actually
> >started by an Oracle consultant (who will remain nameless).
>
> And it wasn't me!
Proof?
:-D
Narh, I think I know who it was. Mind you, back then it probably made a
lot of sense. Problem with all these "gospel" statements is that they
tend to be self-perpetuating. Which in a technical arena like IT is a
recipe for disaster! Like Jonathan recognizes in his book, there is no
such thing as a technical recommendation that remains valid ad infinitum.
Reminds me of many "benchmarks", heavily promoted here as the be-all-and-
end-all of performance evaluations. These were originally developed to
make a Version 5 Oracle database run fast in a mainframe, period. Never
meant to be then sold as "tests" for V6 and V7 databases. For obvious
reasons. Yet they were sold as such, and promoted as the ultimate by all
and sundry including Oracle Consultancy!
Oh well, one day people will learn not to look for silver bullets...
--
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Pete Sharman
>
>In article <aa83v...@drn.newsguy.com>, you said (and I quote):
>> >
>> >This myth has been around for a long time - and unfortunately was actually
>> >started by an Oracle consultant (who will remain nameless).
>>
>> And it wasn't me!
>
>Proof?
>:-D
You mean the word of an Oracle consultant isn't enough? ;)
>
>Narh, I think I know who it was. Mind you, back then it probably made a
>lot of sense. Problem with all these "gospel" statements is that they
>tend to be self-perpetuating. Which in a technical arena like IT is a
>recipe for disaster! Like Jonathan recognizes in his book, there is no
>such thing as a technical recommendation that remains valid ad infinitum.
>
>Reminds me of many "benchmarks", heavily promoted here as the be-all-and-
>end-all of performance evaluations. These were originally developed to
>make a Version 5 Oracle database run fast in a mainframe, period. Never
>meant to be then sold as "tests" for V6 and V7 databases. For obvious
>reasons. Yet they were sold as such, and promoted as the ultimate by all
>and sundry including Oracle Consultancy!
>
>Oh well, one day people will learn not to look for silver bullets...
>--
>Cheers
>Nuno Souto
>nso...@optushome.com.au.nospam
HTH. Additions and corrections welcome.
Pete Sharman
>> >
>> >This myth has been around for a long time - and unfortunately was actually
>> >started by an Oracle consultant (who will remain nameless).
>>
>> And it wasn't me!
>
>Proof?
>:-D
You mean the word of an Oracle consultant isn't enough? ;)
>
>Narh, I think I know who it was. Mind you, back then it probably made a
>lot of sense. Problem with all these "gospel" statements is that they
>tend to be self-perpetuating. Which in a technical arena like IT is a
>recipe for disaster! Like Jonathan recognizes in his book, there is no
>such thing as a technical recommendation that remains valid ad infinitum.
>
>Reminds me of many "benchmarks", heavily promoted here as the be-all-and-
>end-all of performance evaluations. These were originally developed to
>make a Version 5 Oracle database run fast in a mainframe, period. Never
>meant to be then sold as "tests" for V6 and V7 databases. For obvious
>reasons. Yet they were sold as such, and promoted as the ultimate by all
>and sundry including Oracle Consultancy!
>
>Oh well, one day people will learn not to look for silver bullets...
>--
>Cheers
>Nuno Souto
>nso...@optushome.com.au.nospam
HTH. Additions and corrections welcome.
D.Y.
to clear up some confusions about disk I/O. If you think at the PHYSICAL
level, each I/O involves two types of activities:
1) moving electrons from disk head to memory. This is instantaneous if
you have enough bandwidth, and
2) moving disk head to where your data is. This is mechanical motion and
is tens of thousands of times slower. How far the disk head has to move
determines how slow your I/O is.
So there is more to tuning disk I/O than simply make your instance read
from many disks at the same time. Depending on your application, you
may have some control on the speed of an average I/O.
If the data or index you want to read is always next to the disk head
then you've achieve the best possible configuration for performance.
Well, in a real application this almost never happens. Separation of
table and index segments was intended to keep disk heads from jumping
between segments. However, the access pattern of multi-user applications
is inherently random, and that normally negates whatever performance you
hope to gain by separating segments. Let's say your application simply reads
index then table, and reads index again then table again, with no interference
from other sessions, and you have very limited cache ... Can you still say
separating table and index won't make a difference?
So the old rule (I didn't invent it) is not just a myth, it has its merits.
Daniel Morgan <damo...@exesolutions.com> wrote in message news:<3CC5D29F...@exesolutions.com>...
Daniel Morgan
It isn't entirely a question of looking for silver bullets. It is rather that you
must start somewhere. And from where you start you tune or modify until you get
what you want.
Of course no paradigm lasts forever. But you don't create successful applications
by starting out with each new database by retesting every single basic
assumption. If you did ... nothing would ever be created and deployed. Lets see
... I wonder if in this version the tables are still heaps? I know. We'll run
some tests to see if that is still the case. ;-(
Daniel Morgan
Thomas Kyte
says...
>
>Didn't read the entire thread. But I like to take this opportunity
>to clear up some confusions about disk I/O. If you think at the PHYSICAL
>level, each I/O involves two types of activities:
>1) moving electrons from disk head to memory. This is instantaneous if
> you have enough bandwidth, and
>2) moving disk head to where your data is. This is mechanical motion and
> is tens of thousands of times slower. How far the disk head has to move
> determines how slow your I/O is.
>So there is more to tuning disk I/O than simply make your instance read
>from many disks at the same time. Depending on your application, you
>may have some control on the speed of an average I/O.
>
>If the data or index you want to read is always next to the disk head
>then you've achieve the best possible configuration for performance.
>Well, in a real application this almost never happens. Separation of
>table and index segments was intended to keep disk heads from jumping
>between segments.
not to beat a dead horse but...
it *never* did that.
Let's say you have a plan like:
index range scan
table access by rowid
Oracle would goto the data dictionary to figure out where block 1 in the index
was. Let's say that is file 5, block 55. We would seek there, read that block.
That block is generally not a "leaf block" so that block would tell us to goto
another block -- say file 5 block 12345. Now, we seek there and read that
block. Supposing that block is a leaf block -- we find the row(s) in question.
We get a rowid. That rowid tells us "file 72, block 532" has the data you want.
Fine, we goto file 72 -- seek to block 532 and read it.
Whether file 5 and file 72 are on different physical devices *never made a
difference*. It is still 3 serial "seek/read" pairs. The heads jumped all over
the place.
> However, the access pattern of multi-user applications
>is inherently random, and that normally negates whatever performance you
>hope to gain by separating segments. Let's say your application simply reads
>index then table, and reads index again then table again, with no interference
>from other sessions, and you have very limited cache ... Can you still say
>separating table and index won't make a difference?
>
yup, 100% -- always. In that case, what I would like is for my table to be in
MANY extents and my INDEX to be in MANY extents and these extents are spread
across disk 1 and disk 2 evenly (we do that -- when allocating an extent for a
segment we go from file to file). Now I have an even distribution of data
across the two devices.
If the index is cached -- i don't get a hot disk (from all reads going to DATA)
If the index is not cached -- i don't get a hot disk -- the IO is even and since
the reads are *random* reads anyway -- it matters NOT whether they are on 1 or
1,000 disks.
--
Mark Townsend
peter....@oracle.com wrote on 4/24/02 10:24 PM:
>> This myth has been around for a long time - and unfortunately was actually
>> started by an Oracle consultant (who will remain nameless).
>
> And it wasn't me!
Well, not this time, and not this myth, at least. ;-)
Nuno Souto
>
> It isn't entirely a question of looking for silver bullets. It is rather that you
> must start somewhere. And from where you start you tune or modify until you get
> what you want.
>
Yeah, but you see: that's the correct attitude. The wrong one is the one
that desperately looks for "easy solutions" and "rules of thumb" where
there is nothing else but simple engineering analysis. That's what I
call the search for the "silver bullet".
Nuno Souto
> >> And it wasn't me!
> >
> >Proof?
> >:-D
>
> You mean the word of an Oracle consultant isn't enough? ;)
Hehehe! Do they have "words" now?
<for those who might think I'm having a go at Pete, let me be VERY clear:
I most definitely am NOT. He knows what I was talking about, having been
here himself>
Niall Litchfield
news:f369a0eb.02042...@posting.google.com...
> If the data or index you want to read is always next to the disk head
> then you've achieve the best possible configuration for performance.
> Well, in a real application this almost never happens. Separation of
> table and index segments was intended to keep disk heads from jumping
> between segments. However, the access pattern of multi-user applications
> is inherently random, and that normally negates whatever performance you
> hope to gain by separating segments. Let's say your application simply
reads
> index then table, and reads index again then table again, with no
interference
> from other sessions, and you have very limited cache ... Can you still say
> separating table and index won't make a difference?
>
> So the old rule (I didn't invent it) is not just a myth, it has its
merits.
In a single user environment without file systems, caching controllers,
system processes (CKPT for example writing to each datafile ever 3 seconds).
maybe. But then one wouldn't be using ORACLE for that environment anyway.
Soumitra Bose
I guess I heard this myth too!
D.Y.
No arguments there. These are scattered single block I/Os. The seek length is
non-zero even if you stay in the same index segment. Now if you have table and
index on the same disk, what would the seek length be between the two segments?
Possibly 50 or 100 cylinders. I am not claiming we should always consider these
factors. In a typical application the disk head rarely stays on the same
table/index. It normally jumps around because different sessions have different
requests, so location of table/index segments doesn't matter that much. My
point is we shouldn't generalize it so much to say there is nothing we can do
to control disk I/O regardless of the application. The simple fact is,
seek time = avg(seek length)/(speed of disk) (well, a little more than that).
If we have a special situation where we can reduce the average seek length,
we should take advantage of it by all means.
>
> > However, the access pattern of multi-user applications
> >is inherently random, and that normally negates whatever performance you
> >hope to gain by separating segments. Let's say your application simply reads
> >index then table, and reads index again then table again, with no interference
> >from other sessions, and you have very limited cache ... Can you still say
> >separating table and index won't make a difference?
> >
>
> yup, 100% -- always. In that case, what I would like is for my table to be in
> MANY extents and my INDEX to be in MANY extents and these extents are spread
> across disk 1 and disk 2 evenly (we do that -- when allocating an extent for a
> segment we go from file to file). Now I have an even distribution of data
> across the two devices.
>
> If the index is cached -- i don't get a hot disk (from all reads going to DATA)
>
> If the index is not cached -- i don't get a hot disk -- the IO is even and since
> the reads are *random* reads anyway -- it matters NOT whether they are on 1 or
> 1,000 disks.
>
That'd be a perfectly good configuration for most of applications I've seen.
Pete Sharman
>
>In article <aa9i7...@drn.newsguy.com>, you said (and I quote):
>> >> And it wasn't me!
>> >
>> >Proof?
>> >:-D
>>
>> You mean the word of an Oracle consultant isn't enough? ;)
>
>Hehehe! Do they have "words" now?
>
Of course we have words - words like "that will mean I'll have to extend the
contract by 12 months" and "that's fixed in the next release" :)
>
>
>
><for those who might think I'm having a go at Pete, let me be VERY clear:
>I most definitely am NOT. He knows what I was talking about, having been
>here himself>
>--
>Cheers
>Nuno Souto
>nso...@optushome.com.au.nospam
HTH. Additions and corrections welcome.
Pete Sharman
Daniel Morgan
If it takes beating a dead horse please do so. This has been of the most valuable
discussions to appear in an Oracle usenet group in quite a long time. It sure beats
the heck out of Oracle versus SQL Server nonsense. So please keep it up.
PS: Any chance we can get access to your slide presentation? When? Where? Please ...
Thank you.
Or at least have you, perhaps, address those myths here with the same level of clarity
that you have brought to this thread.
Thanks.
Daniel Morgan
Nuno Souto
(and I quote):
I'll jump in two with my $0.02 worth, given that not long ago I did the
same on another thread on the same subject for the same reasons.
> snippage
> > >level, each I/O involves two types of activities:
> > >1) moving electrons from disk head to memory. This is instantaneous if
> > > you have enough bandwidth, and
just one minor correction here, from the engineering point of view. The
speed of movement of electrons in a solid (a metal conductor) is MUCH
LESS than the speed of light in a vacuum (the top speed we see quoted so
much). This is one of the two major reasons that miniaturisation is so
essential for high processing speed in modern CPUs. The other of course
is power dissipation.
> more snippage
> > >2) moving disk head to where your data is. This is mechanical motion and
> > > is tens of thousands of times slower. How far the disk head has to move
> > > determines how slow your I/O is.
and I'd add a third one. It wasn't very relevant many years ago, but it
is today, in this day and age of sub-nanosecond CPU cycle speeds. And
that is the rotational speed. It hasn't increased even by an order of
magnitude in the last 20 years, while processing speed has by quite a
few. I'm talking linear rotational speed, not angular speed.
It is nowadays a relevant slow down whereas before it wasn't. It used to
be referred to as "rotational latency". Thrown it in into the equation
and the argument for splitting tables/indexes becomes even less relevant!
> more snippage
> > >So there is more to tuning disk I/O than simply make your instance read
> > >from many disks at the same time. Depending on your application, you
> > >may have some control on the speed of an average I/O.
Actually, I'd say this a most relevant aspect. You WANT your application
to be able to read from as many disks as possible at the same time. WHEN
it has to read from disks.
What I think is important is coming up with a method of achieving this,
as this implies. Splitting indexes and tables is not the best one, as
has been amply demonstrated here. This however doesn't mean we all give
up on tuning our I/O!!!
The best approach IME is a heuristic one: sample, measure, then decide
which are the "hot spots" for I/O. Then spread those across the devices
available. There is a little bit more about it than just this. You
don't want to have a situation where you solve a single hot spot and then
end up with 50 new hot spots. Ie, the old problem of removing one
bottleneck and watch 50 others replace it. We have to be a little more
preemptive than just that. This is where knowledge of the application
and its patterns of use comes in helpful.
> more snippage
> requests, so location of table/index segments doesn't matter that much. My
> point is we shouldn't generalize it so much to say there is nothing we can do
> to control disk I/O regardless of the application.
Exactly. Of course we can. The following is just one technique.
> more snippage
> > yup, 100% -- always. In that case, what I would like is for my table to be in
> > MANY extents and my INDEX to be in MANY extents and these extents are spread
> > across disk 1 and disk 2 evenly (we do that -- when allocating an extent for a
> > segment we go from file to file). Now I have an even distribution of data
> > across the two devices.
> >
> > If the index is cached -- i don't get a hot disk (from all reads going to DATA)
> >
> > If the index is not cached -- i don't get a hot disk -- the IO is even and since
> > the reads are *random* reads anyway -- it matters NOT whether they are on 1 or
> > 1,000 disks.
Precisely. Very well explained indeed. Of course there are special
cases and exceptions. These must be addressed as needed, but in general
the "divide and conquer" approach for disk distribution is one of the
best.
Add in a good controller cache and/or SAN cache and you have the start of
a very smooth and fast I/O distribution. In fact, there are a few
variations of this distribution that work particularly well when used
with large h/w caches in the I/O subsystems. Add in the multiple buffer
caches available since version 8 and the possibilities are tremendous.
We can now partition the I/O cache for logical load as well as physically
apportion cache over disk arrays, which can themselves be distributed!
If anyone has an I/O performance problem nowadays, they'll have to work
hard to sustain it!
> more snippage
> >>Thank you. Exactly what I ahve been looking for. The ribbon has been tied, the
> >> card signed, and the envelope sealed.
Hehehe! Sounds like Xmas gift wrapping, Dan. Don't seal yet, there is
more to this I/O saga than just this. It is a work in progress thing.
Let's not create another "myth", OK?
Ś)
Jonathan Lewis
The thing that puzzles me about this point is
why no-one has come onto the market with
a drive that holds multiple sets of heads,
say every 90 degrees (pi/2 radians) around
the disc. Expensive, no doubt, but cuts
the rotational latency by a factor which would
probably get close to 4 (given the right s/w
and a mechanism for synching the position
of the disc.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Nuno Souto wrote in message
<3cca102d$0$15475$afc3...@news.optusnet.com.au>...
Nuno Souto
said (and I quote):
>
> why no-one has come onto the market with
> a drive that holds multiple sets of heads,
> say every 90 degrees (pi/2 radians) around
> the disc. Expensive, no doubt, but cuts
> the rotational latency by a factor which would
> probably get close to 4 (given the right s/w
> and a mechanism for synching the position
> of the disc.
>
They DID exist. Both IBM and Sperry at the very least had a model of
removable drive with multiple radial head sets. They were called
disk/drums, due to the response similarity with the old magnetic drum
devices. In fact, IIRC Sperry once sold one that had multiple heads
around the bottom platter which could read a sector in much less than a
full rotation. Can't remember the model number, this was ages ago.
With current technology at least two head movements would be dirt easy to
do. More, I dunno. There isn't just enough space in small modern disk
drives for the actuators. Of course, the trick would be to make them
very small, one per cylinder and static. Dunno if anyone is looking into
this.
simon
>
>
>The theory behind putting index and data on separate devices goes like this:
>
>"you read the index, then you read the data. We'd like to leave the head
>positioned in the index structure just so -- so that the next read on it doesn't
>require head movement". (or something like that).
>
I don't think that is the case.
Separate DATA and INDEX tablespaces because of fragmentation and
extent lifespan.
You could put these tablespaces on separate disks to spread disk I/O
in a MULTI USER environment.
It would be even better to spread both tablespaces over different
disks to spread disk I/O.
Simon (OCA)
Howard J. Rogers
lifespan". Quite how an index extent would live longer or lesser than its
corresponding table extent beats me. Maybe you could elaborate.
HJR
"simon" <only...@hotmail.com> wrote in message
news:cs7lcuoue1of1bsg7...@4ax.com...
D.Y.
> In article <f369a0eb.02042...@posting.google.com>, you said
> (and I quote):
>
> I'll jump in two with my $0.02 worth, given that not long ago I did the
> same on another thread on the same subject for the same reasons.
>
> > snippage
> > > >level, each I/O involves two types of activities:
> > > >1) moving electrons from disk head to memory. This is instantaneous if
> > > > you have enough bandwidth, and
>
>
> just one minor correction here, from the engineering point of view. The
> speed of movement of electrons in a solid (a metal conductor) is MUCH
> LESS than the speed of light in a vacuum (the top speed we see quoted so
> much). This is one of the two major reasons that miniaturisation is so
> essential for high processing speed in modern CPUs. The other of course
> is power dissipation.
>
Interesting observation, and a correct one. Speed of electrons, or EM
propagation has a lot to do with the structure of medium and surrounding
environment. Difficult to get an accurate value. A simplistic approach
may be to solve the Maxwell's equitions to fix the boundary conditions.
I'd be doing similar type of research if I didn't have to feed my family.
Interesting topic but I doubt it'll contribute much to this forum.
>
> > more snippage
> > > >2) moving disk head to where your data is. This is mechanical motion and
> > > > is tens of thousands of times slower. How far the disk head has to move
> > > > determines how slow your I/O is.
>
> and I'd add a third one. It wasn't very relevant many years ago, but it
> is today, in this day and age of sub-nanosecond CPU cycle speeds. And
> that is the rotational speed. It hasn't increased even by an order of
> magnitude in the last 20 years, while processing speed has by quite a
> few. I'm talking linear rotational speed, not angular speed.
>
High RPM creates more tension in the disk, and makes it harder to stop. This
could be why they keep the rotation speed low. But I am not sure.
> It is nowadays a relevant slow down whereas before it wasn't. It used to
> be referred to as "rotational latency". Thrown it in into the equation
> and the argument for splitting tables/indexes becomes even less relevant!
>
Hmm... can't see why low RPM makes it a good idea to have table/index segments
on the same disk. Will give it some thought, though.
I believe the stripping, I/O balancing, etc. without any consideration of
segment location is a statistically good solution for I/O problem for reasons
stated in some previous postings. "statistically good" meaning out of 10
applications 9 would benefit from such a configuration. Or the percentage could
even be higher and that wouldn't surprise me. But I am not ready to accept
that this is "one model fits all".
Getting back to the basics, to improve your I/O performance a few of the
things I normally consider are,
*the application needs to use the most efficient access paths
*get as many disks as possible to work for you at the same time
(there doesn't seem to be any disagreement on these two)
*make the average I/O as fast as possible
On this one, the responsible approach is to understand what determines the
speed of I/O and use your common sense to tune it when you think there is room
for improvement. And you need to understand your application to do this.
Of course there is cache, but that's not something I normally handle.
This a good thread. I've seen better discussion here than probably anywhere
else. Keep it going!
Nuno Souto
> propagation has a lot to do with the structure of medium and surrounding
> environment. Difficult to get an accurate value. A simplistic approach
> may be to solve the Maxwell's equitions to fix the boundary conditions.
> I'd be doing similar type of research if I didn't have to feed my family.
> Interesting topic but I doubt it'll contribute much to this forum.
Speed of light was used as an argument. It is far from that in a
conductor. Contrary to what you might think, it has been researched many
years ago. The average speed in a single copper conductor is less than
an order of magnitude higher than the speed of sound. In aluminium (the
conductor most used in ICs) it's even less. Surprised? Ask an
electronics engineer, they'll know about this: it's a RPITA! One of the
reasons the Crays were built like a doughnut.
> >
>
> High RPM creates more tension in the disk, and makes it harder to stop. This
> could be why they keep the rotation speed low. But I am not sure.
Actually, given that an old 3340-class disk spun at around 3500rpm and
had a platter around 15" wide, it's probably true that a modern 3.5" disk
spinning at 10000rpm will have a linear rotational speed smaller than the
old disks did in their outer cylinders! Haven't done the maths, but my
intuitive guess is that it wouldn't be much faster. Ultimately, this
linear speed defines how fast you can transfer bytes to/from disk.
Assuming nothing else limits this transfer.
>
> Hmm... can't see why low RPM makes it a good idea to have table/index segments
> on the same disk. Will give it some thought, though.
It doesn't. It just makes the whole argument for table/index split
irrelevant. There are just too many variables disturbing what was a nice
theory.
:-)
> even be higher and that wouldn't surprise me. But I am not ready to accept
> that this is "one model fits all".
Me neither! Once again, the bottom line for me: heuristics.
> *the application needs to use the most efficient access paths
Logical as well as physical. Back in the old days of hierarchical
databases, there were three distinct disciplines of DB Design: Schema
Design (where we mapped a data model to real record types), Logical
Design (where we looked at how many logical I/Os were needed to access
any given piece of data - also called the logical access path) and
Physical Design (where we looked at how many physical I/Os we could
squeeze out of the h/w to satisfy the logical I/O load).
Complex? Yes. Efficient? You bet! Nowadays, databases have too many
tables for this to be seriously considered. But if you analyze an
application and find that most of the daily activity is in half a dozen
tables, then it becomes seriously relevant again!
> *get as many disks as possible to work for you at the same time
> (there doesn't seem to be any disagreement on these two)
Exactly.
> *make the average I/O as fast as possible
> On this one, the responsible approach is to understand what determines the
> speed of I/O and use your common sense to tune it when you think there is room
> for improvement. And you need to understand your application to do this.
Exactly. First, we have to define what is an "average I/O" and if we
care about it! Like: there may be an average physical I/O, governed by
devices, device drivers, file systems, OS, DB. At what point do we
define the "I/O" to average it? Hard to say, really.
As you said: it's a whole that is more than the sum of the parts.
We have to look at the application and examine what is the total I/O
pattern. At various times. Then and only then can we determine what is
the "I/O" that we need to address, if any.
I'm reminded of a Uni professor here in Australia, who at the height
of the Y2K "problem" asked the simple question: "Who has actually
analyzed their systems to make sure there really IS a problem? And how?"
Instantly crucified by all the "merchants of doom", but crikey: he WAS
right!
>
> Of course there is cache, but that's not something I normally handle.
>
Well, cache is useful. When you have tuned everything out to the nth
degree, you can use the cache to give you that last little bit of
"smooth" I/O load. And applied to certain file systems, it can achieve
super I/O speeds. However, relying on it solely to solve general I/O
problems is a recipe for more problems. Regardless of what our friends
from EMC and others might say. Used correctly, it can help achieve
tremendous I/O rates. Used incorrectly, it's just another expensive
mistake.
> This a good thread. I've seen better discussion here than probably anywhere
> else. Keep it going!
Amen.
Dusan Bolek
> The thing that puzzles me about this point is
> why no-one has come onto the market with
> a drive that holds multiple sets of heads,
> say every 90 degrees (pi/2 radians) around
> the disc. Expensive, no doubt, but cuts
> the rotational latency by a factor which would
> probably get close to 4 (given the right s/w
> and a mechanism for synching the position
> of the disc.
For having one head pear 90 degrees, you need to have different head
move mechanism than nowadays, because you can't fit four todays
mechanisms on one disc side without crashes between them.
However, you can have two heads mounted from oposite directions. Even
with two heads I have doubts about fitting everything in 3.5" form
factor. Another issue can be a heat dissipation, which is already
problem with 7200+ rpm disk drives. Two engines (instead of one) for
moving heads and more complex electronics, can produce heat, which
will be difficult to eliminate without eliminating the drive. :-) I've
seen a lot of dead HDDs, because of heat, even with only one head per
disc side.
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.
Nuno Souto
(and I quote):
> problem with 7200+ rpm disk drives. Two engines (instead of one) for
> moving heads and more complex electronics, can produce heat, which
> will be difficult to eliminate without eliminating the drive. :-) I've
> seen a lot of dead HDDs, because of heat, even with only one head per
> disc side.
Hmmm, purely as speculation, really OT:
- make the disks wider (easier to dissipate the heat).
- Don't use conventional actuators. Use multiple thin film technology
heads on a half platter in a spiral pattern. Then twist the platter on
an axis off-centre from the real data platters, to reach all cylinders.
That's MOL how the old Sperry drive worked. Used two or three platters
to spread load. Should be easy as pie with today's tech.
Ganesh Raja
> "Titi" <thierry....@wanadoo.fr> wrote in message news:<3cbf2607$0$15182$626a...@news.free.fr>...
> > Hi,
> >
> > For a user, I have data and index in one tablespace.
> > I want to separate data and index ( good idea ??)
> >
> > How can do that ???
> >
> > Thanks in advance ...
> > ( oracle 8.1.7)
>
> No prob just rebuild your indexes using a predefined tablespace
> good idea to rebuild from time to time.
Here goes Another Myth ... Can U tell us why u need to Rebuild teh
Indexes from Time to Time...
Regards,
Ganesh R
D.Y.
> In article <f369a0eb.02042...@posting.google.com>, you said
> (and I quote):
> > Interesting observation, and a correct one. Speed of electrons, or EM
> > propagation has a lot to do with the structure of medium and surrounding
> > environment. Difficult to get an accurate value. A simplistic approach
> > may be to solve the Maxwell's equitions to fix the boundary conditions.
> > I'd be doing similar type of research if I didn't have to feed my family.
> > Interesting topic but I doubt it'll contribute much to this forum.
>
> Speed of light was used as an argument. It is far from that in a
> conductor. Contrary to what you might think, it has been researched many
> years ago. The average speed in a single copper conductor is less than
> an order of magnitude higher than the speed of sound. In aluminium (the
> conductor most used in ICs) it's even less. Surprised? Ask an
> electronics engineer, they'll know about this: it's a RPITA! One of the
> reasons the Crays were built like a doughnut.
I think I am going to disagree here. The speed of sound in air is 365m/s and
in solid (metal, earch, etc.) it's about an order of magnitude higher but I
don't remember the accurate figure (boy, how fast can one forget things
considering that I dedicated more than 2 years of my career in accoustics
research and many more in physics/engineering), and we know it doesn't take
that long for our digital data to travel to the other side of the world :)
D.Y.
to reduce index fragmentation (not tablespace fragmentation), and possibly
reduce the depth of the B tree, so that your application won't have to read
half empty blocks on index scans. It's a good idea, though, to set a nonzero
pctfree to avoid excesive index block splits on inserts and updates.
>
> Regards,
> Ganesh R
Howard J. Rogers
Bong! Nope, though I plead guilty to having recommended frequent rebuilds as
recently as last year.
First: when was the last time you ever saw an Oracle index with a height
greater than 3? It's extremely hard to achieve, actually, because Oracle's
re-balancing mechanism is pretty good. So 'reducing the depth of the B tree'
is not an issue for 99.5% of all indexes out there.
"Half empty blocks"? I hope you're not implying that empty space in a leaf
node can never be re-used, because it can. If you delete 'Bob' from the
table, the index on the name column has a deleted leaf node entry for 'Bob',
true enough, taking up space and being a waste of space. But if you were to
insert a record for 'Bruce', that deleted entry is cleared out, and the
space allocated to Bruce's index entry. Given a reasonably random pattern of
insert activity, it is extemely unlikely that you'll have half-empty blocks
sitting there for very long.
Third, a rebuild causes the index to be re-constructed nice and compactly,
with no spare space, true enough. That might give you a bit of a lift in
performance when reading that index immediately after the rebuild. But the
next piece of DML you perform is going to cause the index to start keeling
over again soon enough. At which point the index has to start re-acquiring
all the extents it lost as a result of the rebuild, but which it had already
gone to the trouble of acquiring before the rebuild. Extent acquisition is
not cheap. In fact, in dictionary managed tablespace, it's bloody expensive!
Therefore, the index actually has to repeat a lot of extremely expensive
dictionary-management activity that, had you not rebuilt it, it would not
have had to do. Performance can therefore actually be worse after a rebuild
than before. Of course, in locally-managed tablespace, that argument is
rather less strong than it used to be.
So, there's a balance to be struck. Tom Kyte once told me that he could
count the number of times he's rebuilt an index in his entire life on the
fingers (not thumbs) of both hands. That strikes me as a slightly
too-Puritan approach! But Jonathan Lewis has a line in his book which reads
(paraphrasing wildly) 'don't strive too officiously to achieve a mythical
100% efficiency for your indexes, because they'll never stay 100% efficient
for long' -meaning, regular rebuilds are a waste of time. So somewhere
between never and always is an appropriate rebuild frequency, but it's
definitely on the 'occasional' end of the spectrum.
Regards
HJR
simon
<d...@hjrdba.com> wrote:
direct quotes from oracle docs (8.1.6):
"Split indexes and tables into separate tablespaces to minimize disk
head movement and parallelize I/O. Both reads happen faster because
one disk head is on the index data and the other is on the table
data."
"You can create an index using an existing index as the data source.
Creating an index in this manner allows you to change storage
characteristics or move to a new tablespace. Rebuilding an index based
on an existing data source also removes intra-block fragmentation..."
lifespan has a lot to do with updates. lets say there is a bitmap
index on a CHAR(1) column. The table has a million rows and the whole
column is just one value. A series of updates occur and this column
now has a cardinality of 10.. then more updates occur and cardinality
goes back to 1. Now I admit this isn't a great application for bitmap
indexes but the lifespans will be very different.
simon
Howard Rogers
> On Sun, 28 Apr 2002 03:04:54 +1000, "Howard J. Rogers"
> <d...@hjrdba.com> wrote:
>
> direct quotes from oracle docs (8.1.6):
>
> "Split indexes and tables into separate tablespaces to minimize disk
> head movement and parallelize I/O. Both reads happen faster because
> one disk head is on the index data and the other is on the table
> data."
>
> "You can create an index using an existing index as the data source.
> Creating an index in this manner allows you to change storage
> characteristics or move to a new tablespace. Rebuilding an index based
> on an existing data source also removes intra-block fragmentation..."
>
> lifespan has a lot to do with updates. lets say there is a bitmap
> index on a CHAR(1) column. The table has a million rows and the whole
> column is just one value. A series of updates occur and this column
> now has a cardinality of 10.. then more updates occur and cardinality
> goes back to 1. Now I admit this isn't a great application for bitmap
> indexes but the lifespans will be very different.
>
True enough: it's a lousy application of bitmap indexes, which shouldn't be
in sight nor sound of that sort of DML! But that aside....
I still don't quite see what any of this has to do with wanting to separate
the index from the table however. Having done the subject to death recently,
it should be apparent that the changes to the index do not compete with the
changes to the table, since they are not done in parallel.
Your example is actually a brilliant one demonstrating why separation of
data and indexes is generally pointless: Given the horrible lack of
concurrency when updating bitmap indexes, any performance penalties you
might conceivably get from housing the index in the same tablespace as its
table will be utterly insignificant compared with the shambolic performance
occasioned by the update of the bitmap index itself.
Incidentally, this thread also discussed the, er, somewhat misleading nature
of the Oracle documentation for 8.1.6 -specifically, that paragraph you
quoted above. It was wrong when it was written, is wrong now, and finally
got corrected in the 9i documentation (in which version, of course, indexes
and heap tables haven't changed their nature at all) which is much less
precise about the issue, mentioning only that "contending I/O" in general
needs to be eliminated by creating separate tablespaces -they've dropped the
specific reference to tables v. indexes. And quite right, too.
Regards
HJR
D.Y.
You need not to.
>
> First: when was the last time you ever saw an Oracle index with a height
> greater than 3? It's extremely hard to achieve, actually, because Oracle's
> re-balancing mechanism is pretty good. So 'reducing the depth of the B tree'
> is not an issue for 99.5% of all indexes out there.
Ah, we are getting into another interesting discussion. I say "possibly" not
"always". Your quote of 99.5% sounds a little too high to me but you could
be correct. I heard more than one Senior DBAs say it always takes 3 disk reads
to retrieve the rowid. See, there are quite a few people out there who think
all the indexes have three levels in their B trees!
>
> "Half empty blocks"? I hope you're not implying that empty space in a leaf
> node can never be re-used, because it can. If you delete 'Bob' from the
> table, the index on the name column has a deleted leaf node entry for 'Bob',
> true enough, taking up space and being a waste of space. But if you were to
> insert a record for 'Bruce', that deleted entry is cleared out, and the
> space allocated to Bruce's index entry. Given a reasonably random pattern of
> insert activity, it is extemely unlikely that you'll have half-empty blocks
> sitting there for very long.
>
> Third, a rebuild causes the index to be re-constructed nice and compactly,
> with no spare space, true enough. That might give you a bit of a lift in
> performance when reading that index immediately after the rebuild. But the
> next piece of DML you perform is going to cause the index to start keeling
> over again soon enough. At which point the index has to start re-acquiring
> all the extents it lost as a result of the rebuild, but which it had already
> gone to the trouble of acquiring before the rebuild. Extent acquisition is
> not cheap. In fact, in dictionary managed tablespace, it's bloody expensive!
> Therefore, the index actually has to repeat a lot of extremely expensive
> dictionary-management activity that, had you not rebuilt it, it would not
> have had to do. Performance can therefore actually be worse after a rebuild
> than before. Of course, in locally-managed tablespace, that argument is
> rather less strong than it used to be.
In gereral, I find index rebuild make performance somewhat better if not much
better. Performance can be worse but those are rare exceptions and I haven't
seen one. If you set your parameters appropriately it normally won't happen.
Almost all the DBAs have rebuilt indexes. If they find it help performance
they keep doing it. Otherwise they stop. Don't take the phrase "half empty"
at its face value. I really meant to say fragmentation. As you explained,
index space can be reused. But it's not ALWAYS reused, nor is it NEVER reused
as is the impression one might get from reading the documentation. How
fragmented an index can be depends on the data. If the density of your index
key is more or less uniform you won't have much fragmentation. If your data is
highly skewed both ways, whether you use a regular or reversed key index you
are going to have significant fragmentation over time and rebuilding this type
of indexes will certainly help.
To find out what's the best thing to do you need to understand the access
pattern and data distribution of your application. That will often point you
in the right direction (assuming you understand the storage and access
mechanism of your database). This approach has helped me much more than simply
following some rules of thumb, or what we call "myths".
>
> So, there's a balance to be struck. Tom Kyte once told me that he could
> count the number of times he's rebuilt an index in his entire life on the
> fingers (not thumbs) of both hands. That strikes me as a slightly
> too-Puritan approach! But Jonathan Lewis has a line in his book which reads
> (paraphrasing wildly) 'don't strive too officiously to achieve a mythical
> 100% efficiency for your indexes, because they'll never stay 100% efficient
> for long' -meaning, regular rebuilds are a waste of time. So somewhere
> between never and always is an appropriate rebuild frequency, but it's
> definitely on the 'occasional' end of the spectrum.
That's the point. It's about BALANCE. And it's driven by your application.
>
> Regards
> HJR
Nuno Souto
> in solid (metal, earch, etc.) it's about an order of magnitude higher but I
> don't remember the accurate figure (boy, how fast can one forget things
> considering that I dedicated more than 2 years of my career in accoustics
> research and many more in physics/engineering), and we know it doesn't take
> that long for our digital data to travel to the other side of the world :)
'sOK. It's not transmitted only by wires to the other side of the world.
There is a lot of radio and optical transmission involved and those do
travel at near light speed in a vacuum. Go here:
http://www-sci.lib.uci.edu/HSG/Ref.html
and I'm sure somewhere in it there is the correct number. I have yet to
find a science subject that can't be answered in this site. :-)
Thomas Kyte
...
>>
>> Bong! Nope, though I plead guilty to having recommended frequent rebuilds as
>> recently as last year.
>
>You need not to.
>
I find people waste a HUGE amount of time and resources rebuilding indexes. It
is good to recant.
>>
>> First: when was the last time you ever saw an Oracle index with a height
>> greater than 3? It's extremely hard to achieve, actually, because Oracle's
>> re-balancing mechanism is pretty good. So 'reducing the depth of the B tree'
>> is not an issue for 99.5% of all indexes out there.
>
>Ah, we are getting into another interesting discussion. I say "possibly" not
>"always". Your quote of 99.5% sounds a little too high to me but you could
>be correct. I heard more than one Senior DBAs say it always takes 3 disk reads
>to retrieve the rowid. See, there are quite a few people out there who think
>all the indexes have three levels in their B trees!
>
Sr, smeener. It might take zero (indexes are pretty cachable).
>>
>> "Half empty blocks"? I hope you're not implying that empty space in a leaf
>> node can never be re-used, because it can. If you delete 'Bob' from the
>> table, the index on the name column has a deleted leaf node entry for 'Bob',
>> true enough, taking up space and being a waste of space. But if you were to
>> insert a record for 'Bruce', that deleted entry is cleared out, and the
>> space allocated to Bruce's index entry. Given a reasonably random pattern of
>> insert activity, it is extemely unlikely that you'll have half-empty blocks
>> sitting there for very long.
>>
>> Third, a rebuild causes the index to be re-constructed nice and compactly,
>> with no spare space, true enough. That might give you a bit of a lift in
>> performance when reading that index immediately after the rebuild. But the
>> next piece of DML you perform is going to cause the index to start keeling
>> over again soon enough. At which point the index has to start re-acquiring
>> all the extents it lost as a result of the rebuild, but which it had already
>> gone to the trouble of acquiring before the rebuild. Extent acquisition is
>> not cheap. In fact, in dictionary managed tablespace, it's bloody expensive!
>> Therefore, the index actually has to repeat a lot of extremely expensive
>> dictionary-management activity that, had you not rebuilt it, it would not
>> have had to do. Performance can therefore actually be worse after a rebuild
>> than before. Of course, in locally-managed tablespace, that argument is
>> rather less strong than it used to be.
>
>In gereral, I find index rebuild make performance somewhat better if not much
>better.
Interesting, how do you measure this? what approach do you use? what metrics
do you provide?
I've always asked people if they do this, no one ever seems to. How have you
quantified this in a measurable way?
> Performance can be worse but those are rare exceptions and I haven't
>seen one. If you set your parameters appropriately it normally won't happen.
>Almost all the DBAs have rebuilt indexes.
almost all dba's separate tables from indexes on difference devices.
many dba's think 1 extent is really cool.
tons of them think the most discriminating field needs to go first in an index
and so on....
> If they find it help performance
>they keep doing it. Otherwise they stop.
I find NEITHER to be true.
A) they never measure it
B) they *never* stop.
--
Nuno Souto
> tons of them think the most discriminating field needs to go first in an index
> and so on....
this is an interesting one. I can't for the life of me remember when
this was ever true (as in which version)? I've always measured it not to
be so in ALL versions of Oracle I worked with, since V4. Did I miss a
long series or is this another big myth from the start? It just doesn't
make sense at all to do it this way when one reads how concat indexes are
processed and stored.
> > If they find it help performance
> >they keep doing it. Otherwise they stop.
>
> I find NEITHER to be true.
>
> A) they never measure it
> B) they *never* stop.
too true.
unfortunately a lot of them do a lot by rote, rather than by need. number
of times I've been crucified at sites for saying that defrag to 1 extent
is a waste of time is not even fun! Since V6 it hasn't really been
needed. All we had to do back then was set the right values for
dc_used_extents and dc_free_extents and bingo: all objects could live
with a (reasonable!) number of extents without any impact on performance.
Problem was no-one ever changed the default values of these two, which
were ridiculously small. Hence the whole myth started. And kept going
all the way through 7.0, 7.1, etc etc, where these parameters were auto-
set by Oracle to huge values and the problem disappeared.
Niall Litchfield
> > If they find it help performance
> >they keep doing it. Otherwise they stop.
>
> I find NEITHER to be true.
>
> A) they never measure it
> B) they *never* stop.
True. Of course to measure it one has to know what the performance is in the
first place. The web content management system is slow today is kinda hard
to diagnose :-(.
--
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
******************************************
Dusan Bolek
> Hmmm, purely as speculation, really OT:
Nothing bad with speculation.
> - make the disks wider (easier to dissipate the heat).
that can be a problem because of standard form factor. It'll bad if
you super disc wouldn't fit in any case.
> - Don't use conventional actuators. Use multiple thin film technology
> heads on a half platter in a spiral pattern. Then twist the platter on
> an axis off-centre from the real data platters, to reach all cylinders.
Or if everything faills use ablative cooling. :-))))
> That's MOL how the old Sperry drive worked.
Sperry made a disc drives ? I know only few peaceful products from
them like bomb ranging system (UK and US bombers in WWII), ranging
system for cruise missiles and ICBMs.
... and of course tons of aviation equipment like speedometers,
artificial horizons and altitude meters (in unlucky cases also
fathometer).
> Used two or three platters to spread load.
Already done, if you use a screwdriver, you'll find that drive in your
PC has up to 2 disc (Seagate terminology) and up to 4 reading heads
(for 60+ maybe 80+ GB HDD depending on producer).
D.Y.
Good point. How could I forget Optics, another area where I spent a few years.
It's hard to find a good comparison without giving it much thought.
Daniel Morgan
C) They follow the advice they receive from Oracle Corporation.
It feels a bit uncomfortable when you criticize people for doing exactly what the
documentation produced by Oracle Corp. tells them to do. It is unrealistic to
expect developers and DBAs to go against advice Oracle Corporation itself
publishes; or to expect them, on their employers dime, to validate each and every
assertion Oracle makes.
For example:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/create.htm#998233
Where it states:
===================================================
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/O can
be distributed during installation of Oracle. Distributing I/O during installation
can reduce the need to distribute I/O later when Oracle is running.
There are several ways to distribute I/O when you install Oracle:
Redo log file placement
Datafile placement
Separation of tables and indexes
Density of data (rows per data block)
===================================================
A reasonable person looking at the above quote would see "Separation of tables and
indexes" and infer from that that they should separate tables and indexes.
I don't have any objection to being advised not to do this blindly. In fact it is
extremely valuable advice that is very much appreciated. And I am sure I speak for
many when I say "Thank you" for correcting the misinformation.
But to criticize people for following advice given by Oracle seems a bit unkind.
Daniel Morgan
Daniel Morgan
It was not entirely myth. I had an engagement about five years ago, it was a finance
system (I think it 8.0.5 on NT 4 w/ 1GB RAM), where deletes were taking substantial
amounts of time (10-15 minutes). I decreased the number of extents from tens of
thousands to a few dozen and deletes took only seconds.
Sometimes myths have a basis in fact though they may be misapplied.
Daniel Morgan
D.Y.
> says...
> >
>
> ...
>
> >>
> >> Bong! Nope, though I plead guilty to having recommended frequent rebuilds as
> >> recently as last year.
> >
> >You need not to.
> >
>
> I find people waste a HUGE amount of time and resources rebuilding indexes. It
> is good to recant.
>
> >>
> >> First: when was the last time you ever saw an Oracle index with a height
> >> greater than 3? It's extremely hard to achieve, actually, because Oracle's
> >> re-balancing mechanism is pretty good. So 'reducing the depth of the B tree'
> >> is not an issue for 99.5% of all indexes out there.
> >
> >Ah, we are getting into another interesting discussion. I say "possibly" not
> >"always". Your quote of 99.5% sounds a little too high to me but you could
> >be correct. I heard more than one Senior DBAs say it always takes 3 disk reads
> >to retrieve the rowid. See, there are quite a few people out there who think
> >all the indexes have three levels in their B trees!
> >
>
> Sr, smeener. It might take zero (indexes are pretty cachable).
Possible in some systems. My indexes are routinely GBs to dozens of
GBs in size so I can't always expect that to happen.
In a TB database, when your nightly batch only takes 4 hours now instead of 6
or 7 hours and the only change you made was rebuilding some huge indexes, people
naturally think defrag made a different. Your batch process gets slow again in
a month or so and the cycle starts over ... Certainly wouldn't rule out any
side effects of index rebuild that could have contributed to performance gain.
But I didn't give it enough thought.
>
> > Performance can be worse but those are rare exceptions and I haven't
> >seen one. If you set your parameters appropriately it normally won't happen.
> >Almost all the DBAs have rebuilt indexes.
>
> almost all dba's separate tables from indexes on difference devices.
> many dba's think 1 extent is really cool.
Wouldn't bother with making everything fit within 1 extent. But I sure don't
like to have too many big holes in my index blocks.
> tons of them think the most discriminating field needs to go first in an index
> and so on....
>
That has been how I create indexes until compression became available. Not
that I needed to do it for performance reasons. I just didn't see anything
wrong with it. I've tried hard to come up with a reason why many people use
the most selective field as the leading column. The only thing I can think
of is the search algorithm. If binary search is used to retrieve an index key,
it may require fewer CPU cycles when you hit the most selective column first.
If Oracle uses a clever algorithm, it may not make a difference at all. I hope
someone with the knowledge will explain.
>
> > If they find it help performance
> >they keep doing it. Otherwise they stop.
>
> I find NEITHER to be true.
>
> A) they never measure it
> B) they *never* stop.
If this is true then they need to stop to think.
Howard J. Rogers
>
> C) They follow the advice they receive from Oracle Corporation.
>
Which is generally good practice.
True enough. A reasonable person might well draw that conclusion. They might
also draw the conclusion that rollback segments can be housed anywhere you
fancy, since that particular quote is mysteriously silent on the
subject -yet we all know (I hope) that rollback I/O is a potential killer
unless housed in its own tablespace/datafile/disk.
I guess the point is that documentation is fallible, as are the people who
write it. Provided the message gets through various forums [books, here,
magazine articles, FAQs and so on] that point three in the above quote is
not strictly necessary (though, let's face it, it does no harm to split the
two, and has (as I keep saying) considerable merit from the point of view of
backup and other management), I don't think it particularly fair to
criticise Oracle for the odd line here and there in what is after all an
enormous quantity of documentation of a complex product.
Regards
HJR
Nuno Souto
(and I quote):
>
> them like bomb ranging system (UK and US bombers in WWII), ranging
> system for cruise missiles and ICBMs.
> ... and of course tons of aviation equipment like speedometers,
> artificial horizons and altitude meters (in unlucky cases also
> fathometer).
Arrrgh!!!! Sperry made EVERYTHING!
<sorry, an old "Spurryous Computer Systems boffin speaking here...>
They were one of the largest suppliers of advanced equipment to the Air
Force, the Navy and Nasa. They made everything from missile control
systems to advanced comms and general purpose computers. Then in the
early 80's they hired ex-IBM top brass who proceeded to dismantle the
company and merge the remains with Burroughs.
That was the birth of Unisys. And the start of making "competitive"
hardware with Intel stuff. Together with the other 5 million makers out
there in Taiwan-land. Makes a lot of long term sense: offload all the
technical nous that makes a company unique and become another anonymous
maker.
No surprise: they mostly vanished. But I'm quite sure the "management
geniuses" who presided over all this crap are nowadays very expensive
consultants. Or something.