February 17, 2012
As I mentioned in a previous blog article, I placed an order for the book “Oracle Database 11gR2 Performance Tuning Cookbook“, mostly because I was curious how the book would compare with the “Oracle Database 11g Performance Tuning Recipes” book (my review of that book was a total of about 35 typewritten pages in length), and some of the other Oracle performance related books that I have reviewed.
After placing my order for the book, I took a quick look at a couple of the book’s preview pages on Amazon, downloaded the sample chapter from the book publisher – chapter 10 “Tuning I/O”, and spent a bit of time reading the sample chapter (making comments in the margins of the printed pages). I circled the following paragraph (sorry for the long quote – I want to make certain that the context of the quote is not lost), found on page 422 of the book (page 17 in the PDF download):
“We can also distribute tables and related indexes on different disks, to obtain performance gain in both read and write operations. If we have tables and indexes on the same disk, we need to read and write in two different places on the same disk. By dividing the work between two disks, we can perform an index range scan faster than when the index and the table are on the same disk.”
What, if anything, is wrong with the above quote from the book? For some reason, the acronym MASE came to mind when I read the above paragraph.
The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.
Charles Hooper's Oracle Notes 
I think it should be SAME.
You’d better ask “what, if anything, is correct in the above quote” 🙂
Timur,
I did not see your response before I posted the comment below.
The MASE acronym is explained in the first AskTom article. In short (as I understand), RAID 0+1 is SAME, RAID 10 is MASE.
The MASE acronym might seem a bit strange. While I have never regularly read threads on AskTom, I recall first seeing the MASE acronym in the first of the following threads:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:359617936136
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:66890198713552
I believe that the current location of the article mentioned in the above threads is here:
Click to access opt-storage-conf-130048.pdf
The AskTom threads also draw an important distinction between RAID 0+1 and RAID 10 (that comment may have greater significance to those who download the sample book chapter).
(Edit: Clicking the links to the AskTom threads will not work – WordPress apparently does not like the structure of the links – copy and paste those links into a new browser window.)
The only thing wrong with this quote is the time. This principle was declared by dr. Who, on his visit to 70’s and 80’s. Today, we no longer operate in terms of disks. LUN is not a disk. It is a slice or shadowed, striped or CRC-protected device which contains many disks over which the DBA has no control. This statement is no longer valid, since the age of flint axes, mammoth hunting and doing backup on floppy disks. Heck, today’s kids don’t even know what the “sneaker net” is or what is the airspeed velocity of an unladen swallow.
e.g
1)Data are modified in buffer cache and dirty blocks are written asynchronously by DBWR process, so I think it doesn’t matter whether table and index are on the same disk or not…
2)
when oracle does range scan it has to;
a) navigate to the leaf block (usually 0-3 single block reads )
b) read a leaf block (first 1 single block read)
c) for every corresponding rowid read a table block (single block read – it goes to the disk only if block is not in buffer cache)
d) go to next leaf block (see b)
All these operations are single block reads so I think (but I’m not sure) that separation of index and table to 2 disk will make no difference when performing single range scan..
As strange as it may seem, I not only agree with what has been stated so far (what has been stated has been much more thoroughly thought out than my comment of a circled paragraph), but I also think that there are a couple of additional items that could be stated about concurrency (how many active processes are performing work). The final line in Timur’s comment seems to be appropriate. Mladen’s comment touches on some important points that I thought about a bit while reading the chapter… what is the date on the book, does the book pre-date the Optimal Storage Configuration Made Easy paper (I think that it was published in 2000) that was linked above, does the book pre-date ASM?. Radoslav’s comment is much more fully developed than what I had in mind when I took pen to paper to circle the paragraph.
It you downloaded the sample chapter, you might have a couple of additional thoughts. One of the thoughts that I had while reading the sample chapter is this: how do you decide what needs to be changed, and how do you verify that the change improved performance. The book states, make these changes – for instance, take a heavily used tablespace offline (in the included screen capture, only the SYSTEM, SYSAUX, and UNDO tablespaces showed much activity), issue SQL commands to rename the datafiles, move the datafiles with operating system commands, and then bring the tablespace back online. Were there any warnings in this section of the book?
The following recursive logic seemed to be implemented in the chapter:
Statement: Do this to improve performance.
Reader Question: How does this improve performance?
Answer: By making the suggested change performance will be improved.
My review of the book should be interesting.
What does Oracle write in their performance tuning guides?
8.2.2 Manually Distributing I/O
…
One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. It is not sufficient to separate a data file simply because the data file contains indexes or table data. The decision to segregate a file should be made only when the I/O rate for that file affects database performance.
8.2.3 When to Separate Files
…
The decision to segregate files should only be driven by I/O rates, recoverability concerns, or manageability issues. (For example, if your LVM does not support dynamic reconfiguration of stripe width, then you might need to create smaller stripe widths to be able to add n disks at a time to create a new stripe of identical configuration.)
Andreas,
Good idea to check the documentation. In general, a book must offer a good reason to disagree with the documentation, when the two sources state different information as fact. I searched through the documentation to find the source of your documentation quote:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/iodesign.htm#CHDJABEA (11.2 disagrees with the book)
http://docs.oracle.com/cd/B19306_01/server.102/b14211/iodesign.htm#sthref528 (10.2 disagrees with the book)
http://docs.oracle.com/cd/A97630_01/server.920/a96533/iodesign.htm#23329 (9.2 disagrees with the book)
http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/ch20_io.htm#7181 (8.1.7 agrees with the book)
http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67775/ch20_io.htm#7181 (8.1.5 disagrees with the book)
Hi Charles,
Several years ago Richard Foote has blogged about that
http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/
http://richardfoote.wordpress.com/2008/04/18/separate-indexes-from-tables-some-thoughts-part-ii-there-there/
Thought that I still have not encoutered a customer site where tables and indexes are placed into the same tablespace. When I asked why you’ve implemented such a kind of design standard, the local DBAs said “It for performance”. The above Richard Foote article is prooving that this separation will not improve performance. This tablespace sepration might have a reason to be implemented but this reason will not be (up to the actual data base release 11gR2) ”For improving Performance” .
Mohamed,
Thank you for linking to the two articles – those are exceptionally well written, and address the concurrency issue that I mentioned in a previous comment.
Mohamed, the old lore takes a very long time to wither and die, even when it’s patently untrue and completely inapplicable to the modern situation. You can’t separate indexes to a separate disk, primarily because storage administrators do not operate with the disks and will not even discuss disks with the DBA. They operate in terms of devices. You ask the storage administrator for a device of certain size and characteristics. Size is usually expressed in GB, characteristics are something like RAID 0+1, RAID 5 and that’s about it. The only thing that you, as a DBA, know about the devices are their size and RAID level. You also know the throughput of the HBA attached to the machine hosting your database.
The simple answer is thus that you cannot separate tables and disks on the separate drives, because you have no access to the drive level. There is more. Speaking theoretically, what benefits could you reap from separating indexes and data to separate devices? The benefits may be twofold: concurrency and the parallelism of I/O. You have no control over the disk layout, so that argument is automatically invalid. Concurrency deserves few words. In the old world of 7.3 and 8i databases, where did the concurrency gains come from? Well, most of the concurrency gains came from the fact that those releases were using mostly dictionary tablespaces, which have handled free space in the data dictionary. With table and index in the same tablespaces, there was a distinct possibility that if both were allocating space at the same time, they could synchronize over the same extent, with one waiting for another. Extents were allocated in transactions and transactions were locking things and waiting for locks. Fortunately for us DBA people, tablespaces no longer use dictionary table, the EXT$ table is now completely gone. Oracle uses bitmaps instead and bitmaps are handled in altogether different fashion, not using SQL transactions. Oracle 11.2.0.3 even disallows nostalgia:
SQL> create tablespace test extent management dictionary datafile size 100M;
create tablespace test extent management dictionary datafile size 100M
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
Elapsed: 00:00:00.02
SQL>
No reason given. If SQL*Plus could use emoticons, I am sure that the command above would cause disdain. Concurrency benefits stemming from the being sure that the allocation will not wait for the same space are long gone. With the characteristics of physical I/O being unknown and guaranteed at certain level and concurrency benefits gone, the old advice about separating indexes and data is no longer valid. Unrelated to that, if you want to endear yourself to a SAN vendor, ask him about IOPS (I/O Operations Per Second) and tell him that you want to test that with a mixture of random/sequential I/O, characteristic for the databases. That does have something to do with optimizing I/O to your data and indexes.
Mladen,
Thanks for sharing the detailed explanation.
I think that I have an explanation for the ORA-12913. If the SYSTEM tablespace is created as a locally managed tablespace, then it is not possible to create dictionary managed tablespaces in the database. If the SYSTEM tablespace is created as dictionary managed, then it is possible to create other dictionary managed tablespaces – even though dictionary managed tablespaces are a deprecated feature. If I remember correctly, as of Oracle Database 10.1, DBCA creates the SYSTEM tablespace as a locally managed tablespace. The 10.2 documentation seems to be a bit more clear on this topic than does the 11.2 documentation.
From the 11.2 documentation: http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_5004.htm#SQLRF53866
From the 10.2 documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm#sthref491
=== You can’t separate indexes to a separate disk, primarily because storage administrators do not operate with the disks and will not even discuss disks with the DBA. They operate in terms of devices. You ask the storage administrator for a device of certain size and characteristics. Size is usually expressed in GB, characteristics are something like RAID 0+1, RAID 5 and that’s about it. The only thing that you, as a DBA, know about the devices are their size and RAID level. ===
As an aside, I’m fascinated by the way many people who post on topics Oracle assume that the entire Oracle universe consists of mega-corps and mega-orgs with similarly mega staffs, mega resources, and extreme specialization of duty. I’ve used and supported Oracle databases at midsized manufacturing firms where my duties included being Director of IS; managing the ERP systems and business processes; making hardware and software decisions and purchases; selecting, purchasing, unboxing, mounting, plugging in, configuring, and attaching the storage system; acting as defacto DBA; managing my huge staff of 2 (this was a $100 million company); and at one of our locations filling the water cooler. Despite the fact that my staff was small and my budget not much bigger I still had the responsibility of providing good response time at reasonable cost to over 100 users processing large-volume transactions, and I certainly had the ability (and the responsibility) to direct certain components of a system to certain disk(s) as necessary.
sPh
sPh,
It has been a couple of years – hopefully, all is going well for you.
I think what Mladen was saying is that there are a *lot* of reasons why the suggestion found in this section of the book is invalid – the mega-corps just have a couple of additional reasons. The advice in this section of the book needs a lot of work to address any database instance with more than a single session connected.
[…] Found also a nice discussion from Charles Hooper blog about the tablespace split or not: https://hoopercharles.wordpress.com/2012/02/17/tables-and-their-indexes-should-be-located-on-differen… […]