Indexes In Their Own Tablespace: Availabilty Advantages (Is There Anybody Out There?) April 28, 2008
Posted by Richard Foote in Backup and Recovery, Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion, Tablespace Management.17 comments
I’ve already discussed here some reasons why performance is not particularly improved by simply separating indexes from tables and storing them in a different tablespace. It sounds like it might be helpful from a performance perspective but when one digs down a little, it turns out any so-called performance benefits are questionable to say the least.
However, performance is only one reason why it’s claimed storing indexes in their own tablespace is beneficial. There are wondrous advantages to database availability and database recovery options if only indexes are stored in their own tablespaces. The loss of all indexes due to a catastrophic disaster in the index tablespace means that the database tables themselves are all still potentially available.
This sounds as if there might be a number of advantages with this strategy, right ?
Well it means for a start that none of the “real” data has been lost. If we store indexes away from the parent tables and we only lose the index tablespace, the table tablespace could possible be totally unaffected by this loss. This potentially suggests a number of things:
- The Database will still be functional. Yes it might run a little slower without indexes but at least with the tables still available, we can still perform our business critical operations until the indexes have been fixed as the actual tables are unaffected
- We don’t actually have to perform a database recovery to get us out of this mess. So long as all the tables are still available, we can simply recover the situation by rebuilding all the indexes from the problematic tablespace. This will hopefully be more simplistic, more efficient and most importantly faster than having to perform an actual database recovery
This all sounds perfectly reasonable …
Today, I’m just going to briefly mentioned some thoughts on the first point, the second point I’ll discuss another day.
I guess the key question here (pun fully intended) is just how important and vital are indexes to the “normal” operation of a database? Is a database effectively operational if we were to lose all our indexes, is an application still effective and operational if we were to lose all indexes belonging to the application? If by storing indexes in their own tablespace, do we get availability benefits if we were to lose only the index related tablespace?
All good questions to ask and ponder about every now and then.
Let’s be clear I’m not discussing the loss or corruption of a single (or handful) of indexes. If a specific index gets corrupted for whatever reason, yes we could recover the index by (say) making the index unusable and rebuilding the index. However, we can do this whether the specific problematic index in question was stored with or separate from the parent table so the scenario doesn’t really differ much.
No, one of the (so-called) benefits of storing indexes in their own tablespace is that if we have a catastrophic issue with the index tablespace, we only lose a whole bunch of indexes. No tables are impacted, just all the indexes stored in the tablespace. However, just how well will business critical operations function without indexes in our database …
The suggestion is that things will just be a lot slower. We’ll have lots of Full Table Scans where previously we had nice efficient index related scans, but at least data can be viewed and manipulated as the actual tables themselves will still be available. Yes things will be slower and less than ideal but better than if we had stored tables and indexes together because in this scenario we would have lost both indexes and tables making the database effectively useless until recovered.
Well let’s setup a really simple scenario and see how things fair without indexes …
First, we create a simple little “parent” test table and populate it with a few rows:
SQL> create table bowie_1 (id number, name varchar2(20));
Table created.
SQL> insert into bowie_1 values (1, ‘Bowie’);
1 row created.
SQL> insert into bowie_1 values (2, ‘Ziggy’);
1 row created.
SQL> insert into bowie_1 values (3, ‘Floyd’);
1 row created.
SQL> commit;
Commit complete.
Next, we create a simple little “child” table and populate it with a few rows:
SQL> create table bowie_2 (id number, fk_value number);
Table created.
SQL> insert into bowie_2 values (1,1);
1 row created.
SQL> insert into bowie_2 values (2,1);
1 row created.
SQL> insert into bowie_2 values (3,2);
1 row created.
SQL> insert into bowie_2 values (4,3);
1 row created.
SQL> insert into bowie_2 values (5,3);
1 row created.
SQL> commit;
Commit complete.
We now add a Primary Key to the parent table which will create for us an index. Note this is the only index in this demonstration which is stored in a separate tablespace to the table:
SQL> alter table bowie_1 add constraint bowie_1_pk primary key(id) using index tablespace users;
Table altered.
Next we create a Foreign Key in our child table. Note this table doesn’t actually have a Primary Key (rare, not recommended but possible) and the Foreign Key has no associated index:
SQL> alter table bowie_2 add constraint bowie_2_fk foreign key(fk_value) referencing bowie_1(id);
Table altered.
Finally, we take the index tablespace offline to simulate a problematic index related tablespace:
SQL> alter tablespace users offline;
Tablespace altered.
OK, the setup is now complete. Let’s see what life is like without our poor little index. First, let’s perform a simple query on our parent table. I’ve hinted the query to make the CBO use the index which the CBO is of course likely to do with most of our queries on most of our tables (and if the CBO doesn’t want to use the index for a specific query, the loss of an index is not going to be an issue then anyways):
SQL> select /*+ index */ * from bowie_1 where id = 1;
select /*+ index */ * from bowie_1 where id = 1
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: ‘C:\ORACLE\ORADATA\FLOYD\USERS01.DBF’
Now the error one may get if the index was simply corrupted or if there’s a problem or corruption at the hardware level may differ but the overall ramification will be the same. Queries that the CBO deems should use a “problematic” index will simply fall over. This is not exactly a good thing from an availability perspective …
How about inserting a new row in the parent table:
SQL> insert into bowie_1 values (4, ‘IGGY’);
insert into bowie_1 values (4, ‘IGGY’)
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: ‘C:\ORACLE\ORADATA\FLOYD\USERS01.DBF’
Oh yeah, that’s right. We have an index that also needs to be inserted as well. Not just any index mind you, but an index that is used to police the uniqueness of the associated PK constraint. Yes, if the problem was at the hardware level, the error message will differ but the ramifications will be the same. We will not be able to insert into the table unless the index is dropped and we can’t drop the index unless the PK constraint is dropped as well.
How about an insert into the other table that doesn’t even have an index:
SQL> insert into bowie_2 values (6, 1);
insert into bowie_2 values (6, 1)
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: ‘C:\ORACLE\ORADATA\FLOYD\USERS01.DBF’
Oh for goodness sake, what now !! Well the table has a FK that points to the table with the problematic index and we need to check to ensure the FK value actually exists in the parent table. How do we perform such a check, why by using the index on the PK column of course and yep, the index can’t currently be used. So unless we drop the FK constraint, we’re stuffed here as well …
Perhaps life isn’t so sweet without these indexes after all …
What if we make the index unusable first rather than it be simply “unavailable” or “damaged” for whatever reason:
SQL> alter index bowie_1_pk unusable;
Index altered.
Well, providing we’re setup to skip unusable indexes:
SQL> show parameter skip
NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean TRUE
We can at least now make our queries run without the use of any problematic indexes:
SQL> select /*+ index */ * from bowie_1 where id = 1;
ID NAME
———- ——————–
1 Bowie
If this table contained 100M rows, it might of course take a long long long time and if we had too many users performing too many Full Table Scans, the entire database might of course scream to a thudding halt, but yes at least we’ve now got our queries working to the point of ignoring unusable indexes.
But is a database (or application or part thereof) that performs nothing but Full Table Scans really a scenario we want to be in? Does this really help to justify the separating of indexes from our tables ? Hummm, not sure about that one …
What about our DML operations now the index is unusable, do these at least function to some degree ?
SQL> insert into bowie_1 values (4, ‘IGGY’);
insert into bowie_1 values (4, ‘IGGY’)
*
ERROR at line 1:
ORA-01502: index ‘BOWIE.BOWIE_1_PK’ or partition of such index is in unusable state
That’s a no for our first parent table example …
SQL> insert into bowie_2 values (6, 1);
insert into bowie_2 values (6, 1)
*
ERROR at line 1:
ORA-01502: index ‘BOWIE.BOWIE_1_PK’ or partition of such index is in unusable state
And that’s also a no for our child, FK table example. Oracle still needs to use the problematic PK related index to police the value in our FK column.
So what kind of database environment are we left with when the indexes from our index only tablespace becomes problematic, even with all our tables totally intact.
Well, until we make the indexes unusable, all index related queries will be falling over all over the place with database related errors. Once we go through a process of identifying all problematic indexes and making them all unusable, we’re left with a database environment that’s performing Full Table Scans all over the place. Just imagine how long it’ll now take to find the customer details of that 10G table. Just imagine the user experience on the database when that 6 table join query can only be performed with Full Table Scans. Just imagine your user concurrent activity with no associated indexes available …
The good news of course is that the tables will at least get no bigger as all inserts will fail, all deletes will fail and many of the updates will fail, except on all those tables that have no Primary Key and no Unique Key and no Foreign Key. Ummm, just how many tables do you have that have no PK or UK or FK constraint ? Not many right …
Losing an index only tablespace would be a catastrophic event, one that would ruin the day of not only the poor DBA having to recover from such a scenario but also any poor user needing to access an impacted application.
One might even argue things could be better if a tablespace containing both tables and indexes was lost if it resulted in another tablespace containing other tables and indexes still being available as at least some table/indexes would be accessible and usable in a viable manner.
Regardless, in either scenario, the database/tablespace/datafile would need to be recovered ASAP to stop user complaints flooding the help desk.
Of course having indexes in their own tablespace will help us recover from such a catastrophic scenario in a more simplistic, efficient and ultimately faster manner, right ?
Well, unfortunately, maybe not. I’ll get around to discussing this issue sometime soon …
Separate Indexes From Tables, Some Thoughts Part 1.5 (Think For Yourself) April 23, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Tablespace Management.5 comments
Based on the discussions on whether there’s any performance benefit in storing indexes separately from tables, I’ve had a number of questions on how can one actually determine exactly how an index range scan is performed. How for example can one determine whether an index I/O is following by a table I/O in a serial manner.
I thought therefore it might be worth going through an example of the process one can use to find these things out for oneself. This is the same basic process I use to determine much of Oracle’s internal behaviour. Simply take an extended trace of the specific operation and study the resultant trace file …
First step is to create a sample table. The table and index doesn’t need to be particularly sophisticated, simple is usually best:
SQL> create table bowie_test (id number, value number, name varchar(20)) tablespace bowie_ts parallel 1;
Table created.
SQL> create index bowie_test_id_i on bowie_test(id) tablespace users parallel 1;
Index created.
SQL> create index bowie_test_value_i on bowie_test(value) tablespace users parallel 1;
Index created.
The first index will be an index on a monotonically increasing value, the second index is on a randomly generated value. Notice the indexes have been created in a separate tablespace to the table so we can easily determine the associated data files.
We next need to just check which data files belong to which tablespace:
SQL> select tablespace_name, file_id from dba_data_files where tablespace_name in (‘BOWIE_TS’, ‘USERS’);
TABLESPACE_NAME FILE_ID ------------------------------ ---------- USERS 4 BOWIE_TS 7 BOWIE_TS 8
Notice how the USERS tablespace which stores the indexes has a data file ID of 4 …
Next we populate the table, the ID column with a monotonically increasing value and the VALUE column with a randomly generated number:
SQL> insert into bowie_test select rownum, dbms_random.value(0,1000000), ‘David Bowie’ from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE_TEST’, cascade=>true, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
Next, we flush the buffer cache to ensure all subsequent I/Os at least start off by being physical I/Os:
SQL> alter system flush buffer_cache;
System altered.
We now start an extended trace of the subsequent operations in the session:
SQL> set autotrace traceonly
SQL> alter session set events ‘10046 trace name context forever, level 12’;
Session altered.
Now, let’s perform a large index range scan, using the random column index. Note we use a hint to ensure the execution plan uses the index:
SQL> SELECT /*+ index(b bowie_test_value_i) */ * FROM bowie_test b WHERE value BETWEEN 100000 and 200000;
99611 rows selected.
Finally, we end the extended trace of the session:
SQL> alter session set events ‘10046 trace name context off’;
Session altered.
Following are sample lines from the generated trace file. Let’s begin by looking at the trace file, searching for the query we’ve just executed:
PARSING IN CURSOR #1 len=99 dep=0 uid=88 oct=3 lid=88 tim=27706931411 hv=2642872055 ad=’23a7d548′ sqlid=’7aynpuafsf1rr’
SELECT /*+ index(b bowie_test_value_i) */ * FROM bowie_test b WHERE value BETWEEN 100000 and 200000
END OF STMT
PARSE #1:c=0,e=12923,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=27706931405
BINDS #1:
EXEC #1:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=27706931577
WAIT #1: nam=’SQL*Net message to client’ ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=12446 tim=27706931615
WAIT #1: nam=’db file sequential read’ ela= 14972 file#=4 block#=23148 blocks=1obj#=80423 tim=27706946845
WAIT #1: nam=’db file sequential read’ ela= 10979 file#=4 block#=30204 blocks=1obj#=80423 tim=27706957960
WAIT #1: nam=’db file sequential read’ ela= 33756 file#=4 block#=24802 blocks=1obj#=80423 tim=27706991842
WAIT #1: nam=’db file sequential read’ ela= 16408 file#=7 block#=121331 blocks=1obj#=80422 tim=27707008593
FETCH #1:c=0,e=77078,p=4,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=27707008734
WAIT #1: nam=’SQL*Net message from client’ ela= 2099 driver id=1413697536 #bytes=1 p3=0 obj#=80422 tim=27707010906
WAIT #1: nam=’db file sequential read’ ela= 1661 file#=7 block#=123430 blocks=1obj#=80422 tim=27707012707
WAIT #1: nam=’SQL*Net message to client’ ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=80422 tim=27707012765
WAIT #1: nam=’db file sequential read’ ela= 10203 file#=7 block#=120965 blocks=1obj#=80422 tim=27707023006
WAIT #1: nam=’db file sequential read’ ela= 13488 file#=7 block#=122222 blocks=1obj#=80422 tim=27707036715
WAIT #1: nam=’db file sequential read’ ela= 16737 file#=7 block#=124918 blocks=1obj#=80422 tim=27707053604
WAIT #1: nam=’db file sequential read’ ela= 15888 file#=7 block#=120896 blocks=1obj#=80422 tim=27707069597
WAIT #1: nam=’db file sequential read’ ela= 11684 file#=7 block#=125491 blocks=1obj#=80422 tim=27707081569
…
Notice how the first 3 physical I/Os are related to the index, the root block, the intermediate branch block and the first leaf block of interest. Note the file number tells us this, as does the obj#. Also notice how each I/O only reads the one block and how each block is random and not consecutive to the previously read blocks (as illustrated by the block# value).
Notice how we next have a whole series of I/Os that correspond to the table. Again we can tell by the different file number and by the new obj#. Notice how they’re all also 1 block I/Os and how they’re all random, non consecutive physical I/Os. There are actually approximately 150 such table related I/Os until we finally reach the next index related I/O:
…
WAIT #1: nam=’db file sequential read’ ela= 8374 file#=4 block#=30216 blocks=1obj#=80423 tim=27708496278
WAIT #1: nam=’db file sequential read’ ela= 13180 file#=7 block#=122271 blocks=1 obj#=80422 tim=27708509565
WAIT #1: nam=’db file sequential read’ ela= 13942 file#=7 block#=124230 blocks=1 obj#=80422 tim=27708523576
WAIT #1: nam=’db file sequential read’ ela= 10766 file#=7 block#=122566 blocks=1 obj#=80422 tim=27708534450
WAIT #1: nam=’db file sequential read’ ela= 9414 file#=7 block#=124035 blocks=1 obj#=80422 tim=27708543976
WAIT #1: nam=’db file sequential read’ ela= 10800 file#=7 block#=125604 blocks=1 obj#=80422 tim=27708554874
WAIT #1: nam=’db file sequential read’ ela= 13215 file#=7 block#=121903 blocks=1 obj#=80422 tim=27708568145
WAIT #1: nam=’db file sequential read’ ela= 12977 file#=7 block#=124409 blocks=1 obj#=80422 tim=27708581221
WAIT #1: nam=’db file sequential read’ ela= 13834 file#=7 block#=121260 blocks=1 obj#=80422 tim=27708595150
…
Notice how this next index block which relates to the next leaf block is only a single block I/O that again is not at all consecutive to the previously read index block. In short, it’s just another random I/O.
This is then again followed by a whole series of randomly accessed table related blocks.
This whole sequence is then repeated many times for a whole series of different index leaf block, none of which are consecutive to the previously read index blocks. Here are a few of the following index related blocks reads, in between each we had a whole series of table related I/Os:
…
WAIT #1: nam=’db file sequential read’ ela= 16580 file#=4 block#=26300 blocks=1 obj#=80423 tim=27709706388
…
WAIT #1: nam=’db file sequential read’ ela= 13769 file#=4 block#=30113 blocks=1 obj#=80423 tim=27711127979
…
WAIT #1: nam=’db file sequential read’ ela= 12267 file#=4 block#=16476 blocks=1 obj#=80423 tim=27712662102
…
WAIT #1: nam=’db file sequential read’ ela= 7692 file#=4 block#=30603 blocks=1 obj#=80423 tim=27713801853
…
Note that the next “logical” block in an index structure is not necessarily the next “physical” block in the index segment. None of these are consecutive, they’re all effectively random Index related I/Os …
The fact all these I/Os are performed in this specific, serial sequence and the fact all these I/Os are effectively random is one of the main reasons why separating indexes from tables makes no real difference to performance.
That and the fact of course that while this select was being performed, there were concurrently a whole series of other I/Os being requested at the same time by all the other concurrent sessions in this database environment that potentially conflicted with the I/O requests in my session.
I’ll leave it to the reader to perform the same tests again with the monotonically increasing index to see how things can be somewhat different for well clustered indexes. However, contention between sessions and different processes still makes separating indexes in these more promising scenarios ultimately futile as well.
OT: My Top 10 Albums Of All Time (Rock ‘n’ Roll With Me) April 21, 2008
Posted by Richard Foote in Music.20 comments
Thought I might post something a little different from the usual technical stuff and share my favourite Top 10 albums of all time. My rules are basically an artist can only have the one album and it can’t include any compilations or greatest hits albums. Interestingly, 5 of the 10 can be considered “concept” albums of sorts, so I’m obviously a sucker for a good theme. So here goes …
10. 
I love Coldplay and all three of their albums. They write really good songs which don’t need to be over produced to bring out their magic. They’re also a really good “band” with each member bringing a special ingredient to their music. This is one of those albums where once you’ve heard a track, you’re a little torn as you want to hear the track again but you also want to hear the next track as well. Always a sign of a good album. I prefer this album of the 3 as it just seems to have a bit more range. From the frantic pace of Politik to the mellowness of Green Eyes, it just has catchy songs one after the other. Britpop gets no better than In My Place, Clocks and The Scientist. The album as a whole has a dreamy, spacey quality that just sucks you in. Highlight: Clocks
9. 
Australia has produced some really great, gutsy bands but none better than the Oils. Although both of their followup albums, “Red Sails In The Sunset” and “Diesel and Dust” are also truly great albums, this is their masterpiece. Much of the album is political in nature, but it’s performed with such passion and drive that you just want to jump up and down with them. Although more than 25 years old, many of the themes and issues are probably more relevant now than they were back then, a quality of several of the albums on my list. However, it’s the actual song writing that’s so impressive, with classics such as “Read About It”, “Power and the Passion” and “US Forces” sounding just as fresh and vital as always. The production is also amazing adding to the atmosphere and raw power of the whole recording. Highlight: Maralinga
8. 
This album is really important to me. I first heard to it back at the time when Abba and disco were the “in thing”. However, this really opened up my eyes (and ears) to the fact dark themes are also worth writing and singing about and that there was this whole other world of music that you couldn’t readily find on the radio or on Top of the Pops. I remember thinking just how different and amazing this was and how much more real and rebellious it was than much of the punk stuff that was making its mark. It’s a unique album and incredibly influential and remarkably recorded in 1967 during the so-called Summer of Love. With a backdrop of some of Andy Warhol’s favourite themes; drugs, sex and death, John Cale’s soundscapes, Maureen Tucker’s drumming, Lou Reed’s manic guitar, Sterling Morrison’s throbbing bass and the unique flat tones of Lou Reed and Nico’s vocals simply sound amazing. A stunning musical statement that in many ways has never quite been matched. Highlight: Heroin.
7. 
When I was about 12 or 13, I was really sick with a bad infection and had to spend 10 long days in bed. My Dad popped down to the local library and got me a few books and this new Sci-Fi musical tape to cheer me up a bit. So started my love for this album. This album has everything, a great story, fantastic music, brilliant performances from a great cast and the incredible voice of Richard Burton holding it all together. The key though is the music and the huge production which sounds so BIG. Jeff Wayne combines hard rock with a musical theatre sound which transports the listener on this incredible, thrilling journey. I still love it. Uuuuuullllaaaaaaa !! Highlight: The Eve Of The War.
6. 
There is no one quite like Iggy Pop and there’s nothing quite like an Iggy Pop concert. The guy has so much energy, it’s simply impossible to replicate it on a record (although Raw Power comes close). I love The Stooges and I love everything he’s done since but for me, Lust For Life, recorded during his Berlin period with Bowie, is where he recorded the perfect Iggy Pop album. From the driving, addictive rhythm of the opening title track made famous on the Trainspotting soundtrack, it includes such gems as Sweet Sixteen, Some Weird Sin, The Passenger and Success. Although some of the themes are a little dark (Tonight for example is world’s apart from the version re-recording by Bowie years later), there is a general sense of optimism which makes this a personal favourite. The photo of Iggy on the cover is a classic. Highlight: The Passenger.
5. 
The Beatles are without doubt the most influential band there’s ever been and their music is just as catchy, interesting, moving and relevant today as it was 40 years ago. Picking just one of their albums is no easy task but perhaps only because I get two albums worth of material, the so-called White Album is my selection. Full of truly classic material, this for me is The Beatles at their best although sadly it also represents the beginning of the end in so many ways as well. They’re still experimenting and pushing the boundaries but manage to include songs such as Back In The USSR, Dear Prudence, While My Guitar Gently Weeps, Happiness Is A Warm Gun, Birthday, Helter Skelter, the list just goes on and on. The only downside was having to get up all the time to change the records over after each side, thank goodness for MP3 players these days. Tempted to place this at No. 9 on the list … Highlight: While My Guitar Gently Weeps
4. 
I feel like I’m cheating a little as Roger Waters features heavily on another of my Top 10, but this is a solo effort and so passes. Inspired by the book “Amusing Ourselves To Death” by Neil Postman, the album is a contempt filled analysis of how TV has made society numb to the horrors the TV pictures so often portray. As a “monkey” randomly switches between different TV channels, wars become nothing more than another form of entertainment to be “enjoyed” with a bunch of friends over a few beers and popcorn. However, it’s not only just the lyrics that are so powerful here, the music is just as important and captivating. Featuring the superb Jeff Beck on many of the tracks, Perfect Sense, The Bravery Of Being Out Of Range, It’s A Miracle and the title track are just some examples of where the music shines just as bright. They sound incredible live in concert too BTW. Sadly, the whole concept behind the album is probably more relevant today than its ever been. I’ve no doubt if David Gilmour featured on this album, Amused To Death would be considered by many as a Pink Floyd masterpiece. Pink Floyd however are coming up soon enough. Highlight: Perfect Sense.
3. 
Both “The Bends” and “Kid A” would both be worthy members of my top 10 list, but OK Computer is the Radiohead masterpiece. A collection of gems loosely based on how computers, transportation and technology generally have all contributed to make humans just that little bit less human, this album is simply one great track after another. The overall production is also so good and lush that each individual track genuinely sounds like a mini epic in it’s own right (except perhaps Paranoid Android which sounds like a huge epic). Thom Yorke’s singing is brilliant throughout with Exit For a Film for example sending shivers upon every listen, but the overall soundscapes produced by the other band members make this an excellent “sounding” album. Paranoid Android is undoubtedly a musical Ben-Hur but Airbag, Exit Music, Let Down, Karma Police, Climbing Up The Walls and Lucky are all grand in their own ways as well. As for the other tracks, well No Surprises there, they’re bloody good as well. Even the much maligned Fitter Happier fits in perfectly within the overall mood and ambiance of the whole piece. Highlight: Paranoid Android.
2. 
Boy, I could have picked any number of Pink Floyd albums but in the end, The Wall just pips some of the others. Written primarily by Roger Waters and somewhat autobiographical, the album tells the “story” of the slow decline and disintegration of poor Pink, a rock star with too many mental scars to cope with stardom. Building “Walls” between himself and his past ultimately only makes matters worse and is perhaps the albums key message. However, as ever, it’s the music that really counts and here Pink Floyd manage to (just) work together to produce a collection of classic tracks. Although Roger Waters clearly dominates, David Glimour’s general contributions are vital in the context of making the whole album the musical masterpiece that it is. Another Brick In The Wall, Young Lust, Hey You, Comfortably Numb and Run Like Hell for example are all about as good as it gets. It’s an album best enjoyed from start to finish with a pair of headphones, a comfortable lounge, a cold beer and 80 or so free minutes to reminisce where you were when you first heard Another Brick In The Wall. Highlight: Comfortably Numb.
1. 
Of course, it had to be a David Bowie album, but picking the one was not easy. Hunky Dory almost got there but in the end, if I had to get rid of all my music and keep just the one album, this would be it. This remarkably was the very first album I ever bought and the fact it’s my favourite is pretty amazing really, if only for it passing the test of time so well. Although this was the album that finally brought Bowie fame, it was as much his amazing stage act and remarkable appearance that brought him so much attention. Which in some ways distracts slightly from what is ultimately a fantastic, straight up rock ‘n’ roll album. Based in some future time when the Earth only has 5 years before some catastrophe destroys all life, it loosely tells the story of an alien, “Messiah” rock star who manages to give people something worth living for before the pressure of stardom and human weaknesses brings it all crashing down. Five Years is probably the best opening and Rock ‘n’ Roll Suicide the best ending to any album, but the collection of songs in between are just as amazing. Moonage Daydream highlights just what a great guitarist and right-hand man Mick Ronson was, Starman is a song that probably generated the best Top Of The Pops performance by any artist, whereas songs such as Hang On To Yourself, Star, Ziggy Startdust and Suffragette City are probably some of the best rock songs ever written. Surprisingly for such futurist themes, it doesn’t feature any synthesisers, only standard rock instrumentation and yet it sounds so fresh as if it’s still based on music that will come sometime in the future. May Ziggy play guitar for many more years to come !! Highlight: Moonage Daydream.
If you haven’t listened to any of these albums before, do yourself a favour and give them a go 🙂
Separate Indexes From Tables, Some Thoughts Part II (There There) April 18, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion, Tablespace Management.10 comments
In Part I, I discussed how separating indexes from tables won’t likely improve performance as:
- Oracle moves from reading index blocks to table blocks in a sequential manner
- Most of the associated I/Os are likely to be random anyways
- Multi-User environments would result in disk contention regardless
That being said, why is it then some sites claim performance improvements after separating indexes from tables ? Previously, performance was sluggish however after moving indexes into a separate tablespace, performance appears to have picked up. Clearly then, moving indexes into a separate tablespace does improve performance, even if common sense might suggest otherwise.
Well, not quite. Here’s a scenario that’s not entirely uncommon …
Currently, an application has both tables and indexes in the same tablespace. The tablespace consists of various datafiles distributed across (say) 4 physical devices. Most database waits are I/O related with both db file sequential reads and db file scattered reads featuring heavily in performance metric reports. However, I/O performance is somewhat average with slow I/O related wait times and performance is generally suffering as a result.
Maybe, just maybe, the problem is due to having tables and indexes in the same tablespace. Perhaps if we separate the indexes away from the tables, contention will reduce, I/O wait times will decrease and database performance might improve as a result.
So we create a shining new, index only tablespace spread across (say) 4 additional physical disks and rebuild all our indexes in this new tablespace. To our relief, thankfully, performance has indeed improved. Average I/O wait times have been reduced and overall database performance has improved as a result. Despite what folks like that Richard Foote dude claims, here is clear proof and evidence of performance indeed improving, purely and simply by just separating indexes from their tables.
All we did was pull the wings off the fly and now it won’t take off after clapping our hands. Clear proof that flies go deaf when you pull off their wings …
There are of course two additional, potentially significant events that have also occurred other than just the indexes being separated from the tables.
The first one is that not only have all the indexes been moved to another tablespace, but all indexes have also been rebuilt as a consequence. Now, I’m the last person to get all excited about indexes being rebuilt, however as I’ve gone to great lengths to document, there are rare scenarios when indexes can get fragmented and may benefit from a rebuild. By moving indexes into a new tablespace, we’ve effectively rebuilt all the indexes, the (say) 99% where it wouldn’t have mattered but also the (say) 1% where it may have improved things. We have also rebuilt those indexes where there may be some temporary improvement until the index starts to flesh itself out again.
As a result, there could be all manner of related changes to execution plans and performance generally, especially related to larger index range scans and index fast full scans.
It’s not the indexes being separate from the tables that’s making some difference here, it’s the fact all the indexes have been rebuilt (especially those that were badly fragmented and accessed by large index scans).
The fly isn’t really deaf …
However, the far more significant difference we’ve also made is that we have of course just introduced 4 new physical devices into our database infrastructure. As a result, we may have significantly enhanced our I/O bandwidth and possibly reduced I/O related contention issues. All the general I/O activity related to indexes that was occurring on our initial 4 disk table/index tablespace have all been removed and are now occurring on our new, separate 4 disk index only tablespace.
But that’s a good thing right, that’s what we wanted to achieve ?
Not quite.
In the index range scan scenario I discussed in Part I, just note how few of the overall I/Os were related to the index. In larger index range scans where in theory separating indexes might improve performance, very few of the related physical I/O activity is actually attributed to indexes. The index would have to have an extremely low (and rare) clustering factor for index costs to be significant. In most “randomly” distributed index scans, there’s significantly more table related physical I/O activity than index activity.
By moving just the indexes into these new physical devices, we’ve just moved a whole bunch of segments that as a group incur relatively low physical I/O related activity while leaving together all those that result in the majority of physical I/Os.
Yes. we’ve reduced contention and I/O demands on the initial tablespace but as whole, we haven’t done it very well at all. Yes, we’ve reduced contention and perhaps improved performance, but we could have done it so much better. Yes, it appears separating indexes from tables has improved performance but has it really …
It’s not the separating of indexes from tables that’s improved performance, it’s the fact we’ve introduced 4 new disks and we’ve shifted some of the I/O activity away from the initial tablespace.
The fly isn’t deaf after all …
As an example, previously we had 100% of related I/O activity in the initial table/index, 4 disk tablespace. However, only (say) 20% of the activity was actually related to the indexes, 80% was attributed to all the tables. By moving all the indexes into the new, 4 disk index only tablespace, we therefore reduce the load on the initial tablespace by 20%. We now have 80% of the I/O load on 4 disks and just 20% on the other 4 disks. Yes, performance might improve as a result but we could do so much better. Currently, 4 of the disks have 20% of all segment related load on them and the other 4 disks have just 5% of all associated load.
Instead, if only we either added the 4 disks to the other 4 disk set and striped both tables and indexes across all 8 disks or moved and distributed both indexes and tables into the new 4 disk set, we might have been able to distribute load much more evenly across all 8 disks with approximately 12.5% load across each one.
By doing so, we may have improved performance by an even better and more significant amount. Conversely, by separating indexes into their own tablespace, we may actually be hurting general database performance because database performance is not optimal due to the uneven distribution of I/O related activities.
Of course, there’s a very easy way to confirm this. Look at the statistics in V$FILESTAT or look at a statspack report and carefully study the physical I/O activity in the table only and index only tablespaces and compare the results. Just how evenly distirubuted are the I/O related workloads …
Yes, there are scenarios where distributing individual segments here or there may be beneficial but the overall objective is generally to try and even out disk/spindle workloads as much as possible. Separating all indexes blindly is typically a very poor method of trying to achieve this.
If an individual query is not likely to improve by having an index in a separate tablespace and if separating indexes results in a non-uniform distribution of physical I/O activity, then you may want to start questioning whether it’s all really worth it.
Of course, database recoveries will be simplified by having indexes in their own tablespace, right ?. Ummm, I’ll tackle that myth next …
Separate Indexes From Tables, Some Thoughts Part I (Everything In Its Right Place) April 16, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion, Tablespace Management.19 comments
Although by no means as common as it once was, there’s still some who believe separating indexes in a different tablespace from their parent tables somehow improves performance.
The theory goes that by having indexes in their own tablespace, we can reduce overall contention issues and thereby improve the overall performance of the database.
Here are some thoughts for consideration for those who might be so inclined …
First, let’s just have a little look at the behaviour of a “typical” large scale index range scan, using an index with a height of say 3.
We first begin by accessing the root block of the index. This is a single block read which for many high accessed indexes would typically be cached and not result in a physical I/O. Next we read an intermediate branch block. This is also a single block read and is also likely to be cached if the index is heavily accessed. Regardless, it’s another index related I/O. Next we finally reach and read the first index leaf block containing the start of the index entries of interest. Again, it’s a single block I/O and again it’s index related.
So far we’ve performed 3 “random”, single block I/Os of index related blocks. If the index were in a separate tablespace, all the action would only be on the index tablespace thus far.
We next read our first table block containing the first row referenced by the first index entry of interest. This yet again is a single block I/O that could potentially be any block within the table. If the table were in a separate tablespace from the index, we would still need to perform a physical I/O (assuming the block isn’t already cached) on a “random” block within the table tablespace. If the table were in the same tablespace as the index, we again need to perform a physical I/O on a random table block. Still no difference thus far.
We next (very likely) reference the same index leaf block to determine the second row of interest. Note this block will almost certainly still be cached as it’s just been accessed. Therefore, if the index were in the same or different tablespace to the table, still no difference as there’s no associated physical I/O.
We then read the second table block of interest via a single block I/O. Unless this index has a very good clustering factor, we’re likely to read a totally different table block that could be any other block within the table. It’s extremely unlikely therefore to be the block that is physically contiguous to the block previously read. Only if the index were very well clustered, could it possibly be the same block as previously read or possibly the next logical block in the table.
However, in all these scenarios, having the table in a separate tablespace still makes no difference at this stage. We either need to perform another physical I/O on the table or we perform just a logical I/O. Even in the extremely unlikely case the next block read is physically contiguous to the previous block read, it would still be contiguous whether the index was separate or not and not be impacted by the index read activity thus far. Again, thus far it makes no real difference having the index in a separate tablespace.
We go back to the same index leaf block to determine the next row of interest and then access the next table block, which for a large randomly distributed table is again likely to be another different block. The point being we’re accessing the index and the table in a sequential fashion, reading the index, then reading the table. Reading the index and then reading the table again.
For small index scans, the index leaf block in question is likely to be the same single leaf block as a leaf block can potentially store hundreds of index entries (depending of course on block size, index row size and where within the index leaf block we logically begin to read the index entries of interest). So for small scans, it’s not going to have any real impact having indexes in a separate tablespace as we’re basically reading a few index related blocks followed by the table related blocks.
The table blocks are likely to be different blocks in a randomly distributed, poorly clustered index or possibly (although more rarely) a small sample of blocks in a well clustered index. However, in either scenario, if if we need to access just the one leaf block, it makes no difference whether the index is in a separate tablespace or not, the I/Os and so-called contention are the same regardless.
In some scenarios, Oracle can perform a prefetch step whereby it orders the index entries based on the rowids to first determine which table blocks need to be accessed, thus preventing the same table block having to be re-read several times. However, again, it makes no difference thus far if the index is in a separate tablespace or not as the I/O requirements are the same regardless.
In larger index range scans however, we might need to visit the next logical index leaf block or indeed subsequently many such index leaf blocks. Note each leaf block contains a pointer (referred to as kdxlenxt in a block dump) so Oracle can directly access the next index leaf block. If our index were in a separate tablespace and making the HUGE assumption that there’s thus far been no other activity in the index tablespace, the disk head may not have moved from where it left off after reading the last leaf block. With the indexes and tables coexisting in the same tablespace, we have very likely moved on from this location with any subsequent table related I/O activity.
Maybe now at last, finally we have a benefit in having indexes in their own tablespace …
However, reading the next index leaf block is again a single block read and most importantly is not necessarily “physically” contiguous to the previous leaf block. Remember, index leaf blocks split as part of their natural growth and the new block allocated is simply the next block available in the index freelist. Therefore the next logical index leaf block in an index structure could physically be virtually anywhere within the extents allocated to the index. When we read the next “logical” index leaf block, it does not necessarily mean it’s the next “physical” block within the index segment. It’s likely just another random, single block I/O.
That being the case, again we have no benefit in the index being in a separate tablespace. In both scenarios, we have to go scanning the disk looking for the physical location of the next index leaf block (again assuming the index leaf block isn’t already cached). This activity needs to be performed whether the index is in it’s own tablespace or not.
When we move back to read the next table block based on the first index entry from the newly accessed index leaf block, again, it’s extremely unlikely the next table block accessed will be the next contiguous block from the previously read table block. So again, we very likely need to go a hunting for the next table block on disk, regardless of it being in a separate tablespace from the index. Again, separating indexes from tables makes no real difference.
So not only do we move between index and table in a sequential manner but the actual blocks read within both the index and the table are likely to be totally random, non contiguous, single block reads.
That being the case, what are the performance benefits of storing indexes and tables separately ? How does storing indexes and tables separately actually reduce contention when most physical I/Os in both index and table segments are effectively random, single block reads ?
Now this example has just been a single index scan, performed by one user on just one index and table. The benefits therefore of separating indexes and tables even in a single user environment are somewhat “dubious”.
However, how many environments only have the one user. Not many. Most environments have lots of users, some with many hundreds, some with many thousands of concurrent users . All these users are potentially performing concurrent I/O operations, not only potentially on these very same tables and indexes but on lots of different tables and lots of different indexes within our table and index tablespaces. Even if index leaf blocks were to be physically contiguous in some cases (such as monotonically increasing indexes where this is more likely), by the time we’ve read the index leaf block, processed and read all the associated table blocks referenced by the index leaf block, the chances of there being no subsequent physical activity in the index tablespace due to another user session is virtually nil. We would still need to re-scan the disk to physically access the next index leaf block (or table block) anyways.
Add to the mix the fact many sites now use SANS, NAS, ASM etc. and what might appear to be one contiguous file could actually be physically split and spread all over the place. The whole notion of what is actually physically contiguous and what isn’t is blurred anyways.
The next time someone suggests separating indexes from table improves performance, you may just want to ask a couple of little questions; why and how ?
However, I’ll next discuss how indeed performance can improve by storing indexes in a separate tablespace. But just like our mad scientist thinking flies with no wings go deaf, I’ll explain how the performance improvement is not actually directly related to the indexes being separate from the tables.
I’ll also discuss how database recoveries are not typically helped by having indexes in a separate tablespace as often suggested.
Most Influencial Person In My Career (I Am…I Said) April 10, 2008
Posted by Richard Foote in Oracle Blog, Oracle Opinion, Richard's Musings.14 comments
I previously listed four of the people who have probably had the biggest impact on me as a DBA.
However, the single most important person in my career as a DBA, the person who’s had the biggest impact in all my various successes and failures throughout my career, is undoubtedly the one and only Richard Foote.
Congratulations Slater 🙂
Now I’m not suggesting for one minute I’m as capable or knowledgeable as the four previously listed, indeed I can say with some confidence that I’m not, but there’s no question that at the end of the day, I’m ultimately responsible for being the DBA (and indeed the person generally) I am today.
Influences are of course very important, but it’s up to the individual to ensure all influences (good and bad) become positive experiences. It’s entirely up to the individual to take those influences and to find the drive, the energy, the motivation and the enthusiasm to be as successful, as capable, as knowledgeable and as competent as they can be.
Or indeed as “successful” as one ultimately wants to be because all these things are measured and mean something different to each individual. The scale that really matters, the best measurement to determine the level or standard or confidence that one has achieved is ultimately happiness. When you walk into the office each morning, how do you feel about yourself? You really don’t need to be world’s best Oracle expert (or in any subject matter or profession) to feel good about yourself, to feel you’re heading in the right direction and that you’re at a stage in your career, your work-life journey, where you want to be. To feel like you’re a bloody good and successful DBA.
If you’re “happy” with where you are, congratulations, because you’re the one that’s had the biggest impact and influence in your “success”. If you’re not happy, if you’re not satisfied with where you’re at, if you feel you’re behind where you really want to be, if you’re not the Oracle DBA you want to be (or developer, or pilot or porn star, or whatever), the good news, the really exciting and positive news, is that it’s entirely in your own hands to turn things around.
I spend a good portion of my life at work. I probably spend as much time talking to my work colleagues as I do talking to my own family. I certainly spend more time working on Oracle databases than I do working in my garden or playing football or losing at computer games against the kids or watching David Bowie DVDs. Therefore, it’s really important to me that I enjoy what I do at work and that I’m as good at my job as I can reasonably hope to be. How much I enjoy my work is very much related to how confident I feel in my capabilities and in how much I continue to learn and grow in my abilities. Ultimately, I’m directly responsible for it all…
After I do a presentation or talk, people often ask how do I know all that stuff, how come I know so much about bloody indexes, where did I pick up all that 10g/ 11g stuff. It’s no secret, I spend a lot of energy researching, experimenting and investing time into learning more and more about that which I’m responsible for; which is lots of Oracle databases that have lots of important information for lots of people.
The four people I mentioned as influences certainly have had a big impact in how I approach my learning and my work generally, in how I attempt to better myself, in my drive to test things for myself, in how I view what’s possible and what’s important. However, they can’t actually put things in my brain; they can’t force me to spend hours determining how the behaviour of bitmap indexes changed in 10g, or make me spend hours practicing different types of database recoveries with and without RMAN, or make me start this Blog or make me research and write a 2 day index internals seminar, etc.
That’s all up to me.
How I deal with failure, how I learn (or not) from mistakes, how I determine right from wrong (at so many levels) how I handle criticism, how I admit and respond to errors, how I judge and police values and how I actually absorb and turn influences and feedback into positive experiences is also totally and entirely up to me as an individual as well.
The key point I want to make is that when discussing influences and who has contributed and had an impact in your successes and in your career, ultimately the person who has had the biggest impact is you.
Top 5 Most Influencial DBAs In My Oracle Career (“Heroes”) April 8, 2008
Posted by Richard Foote in Oracle General, Oracle Opinion, Richard's Musings.19 comments
When I was at OpenWorld last year, I was asked by a couple of people a question that’s been asked of me quite a number of times before. Who has been the biggest influence in my career as an Oracle DBA, just who has had the biggest impact in shaping the Oracle DBA I am today.
It’s actually a really difficult question to answer because it first assumes I actually know exactly what sort of DBA I am, which I’m not sure is entirely the case. It also assumes that “this shape” is fixed, which it isn’t. I literally learn new things about Oracle on a daily basis so I’m continually evolving and developing and “growing” as a DBA.
The answer I generally give surprises most when I give it but when I explain my reasoning, it generally makes sense and they accept where I’m coming from. So I thought I might share the top 5 Oracle DBAs who have most shaped and influenced this Oracle DBA I am today.
Reducing what is overall quite a massive list of influences to just 5 is a really really difficult process, but these 5 are probably the most influential in not just what I actually “know” about Oracle, but more importantly, how I actually go about continually learning and growing and developing as a DBA.
Four of them in no particular order are:
Steve Adams. I’ve had the pleasure of meeting Steve a number of times and the most important thing he taught me was just how much I actually didn’t know about Oracle !! Initially, I looked at Oracle as simply being this “car” if you like, that had an “engine” and had a thing you did to switch it on and a thing you turned to make it go where you want and if you did these things every now and then, this “car” ran that little bit better (or so it seemed). However Steve made me realise that Oracle was actually made of lots and lots of little parts and that an “engine” was actually made of lots of different components that worked together and the more you knew how these components actually worked and interacted, the easier and more effective one would be in tuning and finding what might be at fault. However, he didn’t just know that this bit was a “starting motor”, he went way way down into knowing what all the little bits ‘n’ pieces were that made up the “starting motor” and the “distributor” and the “CD player” and pretty well every part of the whole “car” !! And not just for this model of Oracle, but for pretty well all models dating back to almost when Oracle began.
Steve really opened up my eyes into appreciating all that there really was to potentially learn about Oracle, the importance of having some understanding of the nuts ‘n’ bolts to be effective and that no matter what, I will never, ever, stop learning and relearning how Oracle actually works. I will never have the knowledge that Steve has about Oracle, I will likely never get close but he gave me the drive and ambition to at least try. He’s also a fellow Aussie so deserves additional bonus points 😉
Tom Kyte. I’ve only met Tom once very briefly at OpenWorld last year. However, I feel like I know him so well thanks to his fabulous Ask Tom website. Tom has probably taught me more about Oracle itself than just about anyone but he’s also taught me something far more important as well. Tom taught me the importance of “proof”, how to demonstrate and actually “show” how Oracle works and functions. Rather than just saying 1+1=2, he can actually demonstrate that 1+1=2, why it’s so and give me a script that I can run and test and modify so I can learn why and how 1+1=2. Most things in Oracle can be illustrated in this manner and these skills have been a huge influence on not only what to believe, but in how to determine and investigate things for myself. If someone claims 1+1=3 but doesn’t have the capabilty to show why it’s so, then in my experience there’s a very good chance that 1+1 doesn’t actually equal 3 afterall.
Also, Tom’s books are among the best Oracle books I’ve read and really showed me what a good Oracle book actually looks like. Basically Tom taught me “how to fish” and I’ve been catching fish in the Oracle Ocean for most of my career thanks in large part to Tom.
Jonathan Lewis. I had the pleasure of meeting Jonathan and showing him the wonders that is sunny Canberra a few years ago. Jonathan, like Tom and Steve knows more about Oracle and the internal workings of Oracle than I will ever hope to know. The Oracle knowledge this man has is amazing.
The core, the brain even of the Oracle database is the CBO and very often when Oracle is “sick”, it’s directly related to the CBO not doing what it should be doing or not doing what you think it should be doing. Jonathan reminds me very much of a really really good doctor or surgeon who not only is able to quickly diagnose a specific problem with one quick glance of a “medical chart” but is able to get in and successfully perform the necessary surgical procedure with no fuss, ensuring the patient makes a quick and successful recovery.
Jonathan taught me the importance of correctly diagnosing a problem in order to apply an appropriate solution. He also highlighted just how complex the CBO really is, how important it is to actually understand how the CBO (and Oracle in general) works and why it’s vital to correctly understand and interpret the various costs and behaviours in order to apply an appropriate solution. Unless you understand the hows, the whats and the whys, unless you really understand the problem, you’re not really in a position to apply an appropriate solution. Jonathan never guesses, rarely assumes and if he does, it’s an educated guess and he’ll explain his reasoning for making any such assumptions.
This “discipline” of his and his process in diagnosing a problem has been extremely important in determining how I look at a problem. If I don’t know what’s actually going on, if I don’t understand the root cause of a problem, then how can I expect to solve it successfully.
Cary Millsap. A few years ago, I attended a class of Cary’s in Sydney where I had the opportunity in turn to explain the wonders that is Rugby League over a few beers. He’s notable also as being one of the very few people to fully appreciate my (somewhat infamous) “Rupert The Rat” joke …
During my time at Oracle, one class I never particularly enjoyed teaching was the Performance Tuning course as I felt really uncomfortable with the contents and the manner in which the topic of database tuning was addressed. Method C wasn’t a process I ever felt comfortable teaching. Although I had been focusing on the wait interface for quite some time, for me, Cary’s (and Jeff Holt’s) book Optimizing Oracle Performance was the first time I read a book specifically on performance tuning where I said, “Yes, yes that’s it”. Cary’s book and his teachings so perfectly articulate the importance of knowing exactly where time is being spent when poor response times are problematic, so one can focus on a solution that will actually make a difference. Again, understanding “what” the problem is by knowing where all the time is being spent. Again, the “don’t guess when you can know” principle. It’s a process I successfully apply again and again in diagnosing database problems and Cary’s focus on Method R has been very influential. Besides, anyone who appreciates Rupert The Rat deserves a special mention 🙂
The final person in my Top 5 list is most certainly the most important and significant in determining how I’ve evolved over the years into the DBA I am today. It’s possibly a somewhat controversial choice and there may be some who would possibly disagree. However, in my opinion, this person should likely be in everyone’s Top 5 list (big call I know) so I’ll leave the identity of this last person for my next post.
So When Does An Oracle B-Tree Index Increase In Height ? (Almost Grown) April 3, 2008
Posted by Richard Foote in Index Height, Index statistics, Oracle General, Oracle Indexes, Oracle Myths.60 comments
So when does an Oracle B-Tree index actually increase in height ?
I’ve basically been asked this same question a number of times over the past few days with regard to the discussions on indexes and different block sized tablespaces, so I thought it might be worth quickly sharing the answer to a wider audience.
Imagine a new, empty table and a corresponding new, empty index. At this stage, the index structure basically consists of one, empty block. The index has a BLEVEL of 0 (from DBA_INDEXES) and a HEIGHT of 1 (from INDEX_STATS), yes it can be confusing 😉 This block is basically the Root block of the index as it’s the first (and currently only) block to be accessed during an index scan, but at this stage is used to also store the actual index entries as well (and so can kinda be viewed as being a Leaf block as well).
We now start to insert rows into the table and thus row entries into the index. These index entries basically consist of the indexed column(s) and its corresponding ROWID, and are sorted based on the indexed column values.
Eventually, this single index block will fill; Oracle simply can’t add any more index entries into it. Now comes the fun bit.
When Oracle wants to insert a new index entry but it can’t as this Root index block is full, Oracle will allocate two new index blocks. If the new index entry is the maximum value currently to be indexed, Oracle will move all the index entries from the full block and put it into one of the new index blocks and place the new index entry into the other block. This is known as a 90-10 index block split.
If the new index entry isn’t the maximum value, Oracle will place the lower 1/2 valued index entries into one new block and the other 1/2 into the other new block. This is known as a 50-50 index block split.
These two new blocks are now the new leaf blocks in the index structure.
The contents of the previously single filled block is now totally replaced with pointers to the two new blocks. This block therefore remains the Root block in the index structure. These pointers basically consist of the Relative Block Address (RBA) to the new index blocks and a value which represents the lowest indexed value found in the specific referenced leaf block. These indexed values in the Root block are now used by Oracle as the method by which it can navigate the index structure to find the specific index leaf block containing a required indexed entry.
The index has just increased in height and now has a BLEVEL of 1 and a HEIGHT of 2.
As we continue to add more rows into the table, we add more index entries into our 2 leaf blocks. Eventually they will fill again and will again perform either a 90-10 or 50-50 block split depending on the new index value to be inserted. With a non Root block split, only one additional index block is allocated and the index entries are distributed between the full and new index block. Each time a leaf block splits in a BLEVEL 1 index, a new entry is also added into the Root block to point to the new Leaf block.
Once we have enough Leaf blocks, the Root block will again eventually fill. At this point, Oracle will again allocate two new blocks and distribute the contents of the Root block into these two new blocks, again 90-10 or 50-50 depending on the new indexed value to be inserted. The contents of the Root block is now totally replaced with pointers to these 2 new “Branch” blocks which of course in turn now contain the pointers to the Leaf blocks.
The index has again increased in height and we now have an index with a BLEVEL of 2 and a HEIGHT of 3.
As the leaf blocks continue fill and split, a new entry is added to the corresponding Branch block each time. When these Branch blocks fill and split, a new entry is added to the Root block. When the Root block eventually fills, it will again allocate 2 new blocks and so the index grows in height again.
So basically, an index increases in height whenever the index Root block splits and the two new allocated blocks result in a new level within the index structure. Note the index Root block remains the same throughout the entire life of the index, no matter the index height.
Note also a Root block split is the only time an index increases in height. Therefore, the number of levels between the Root block and any/all of the Leaf blocks is always and must always be the same. Hence, an Oracle B-Tree index is always structurally height balanced, always.
April Fools (I Got You Babe) !! April 1, 2008
Posted by Richard Foote in Humour, Oracle Blog, Richard's Musings.2 comments
Firstly, thank you for all the comments and emails, I’m very touched !!
Those of you that know me realise I have a naughty little streak in me that likes to get out and have some fun every now and then. April 1st is usually one of those days.
I feel a little guilty in posting my “Announcement” on 31 May GMT, however it was well and truly the morning of April 1 here in sunny Canberra, Australia, Australian Eastern Summer Time. So it’s well within the official April Fools rules of conduct. Time zones are a funny thing.
The first subtle clue that this “Announcement” was not quite what it might appear was in the title, Announcing Finale, as it has the same initials as April Fools. However, the far more obvious clue was within the post itself, with the first letter in every paragraph spelling April Fools. I modified the post to highlight how it was sitting there all along. Hee hee 😉
I must say I had thought the reference to my future career plans in the adult film industry might be stretching things a tad (pun fully intended), that and the fact I must of sounded totally demented, but it seems that many saw this as some kind of confirmation that I was really moving on !! I guess with a name like Dick Foote, anything is possible I guess.
Congratulations to those who picked it and sorry to those of you who I caught in the net 🙂
However, let me clearly state I have absolutely no intention of retiring and that this Blog will be around for quite some time yet.
I guess it only leaves me with one more thing to say, “April Fools” everyone !!
Richard Foote's Oracle Blog 