jump to navigation

Deleted Index Entries Part V (Trouble) July 1, 2008

Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes.
Tags:
14 comments

As discussed in Part IV, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled in subsequent index block split operations. This of course is a good thing as it helps to keep the index as small and efficient as possible without having to perform relatively expensive index maintenance activities such as index rebuilds, coalesces or shrinks.

However, an important issue is that although the effectively empty index block is free and available to be reused, it remains in place in it’s current logical location within the index structure until it’s been recycled. It’s only logically unlinked and re-positioned within the index structure at the time of the block being recycled.

This simple demo highlights this issue.

First create a table and associated index and populate it with a 10000 rows:

SQL> CREATE TABLE test_1 (id NUMBER, name VARCHAR2(30));

Table created.

SQL> INSERT INTO test_1 SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX test_i ON test_1(id);

Index created.

Next delete most of the rows to so that we have a number of index blocks that contain nothing but deleted index entries which can potentially be subsequently recycled:

SQL> DELETE test_1 WHERE id between 1 and 9990;

9990 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE INDEX test_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21        9990

If we now run a select statement that forces the use of the index and which only returns the 10 remaining rows, the high number of logical I/Os highlights how Oracle still accesses the now emptied index blocks during the index range scan operation:

SQL> SELECT /*+ index (test_1) */ * FROM test_1
          WHERE id BETWEEN 1 and 10000;

Execution Plan

--------------------------------------------
| Id| Operation                   | Name   |
--------------------------------------------
|  0| SELECT STATEMENT            |        |
|  1|  TABLE ACCESS BY INDEX ROWID| TEST_1 |
|* 2|   INDEX RANGE SCAN          | TEST_I |
--------------------------------------------

Statistics
----------------------------------------------
  0 recursive calls
  0 db block gets
 25 consistent gets
  0 physical reads
  0 redo size
577 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
 10 rows processed

If there’s a significant time lag between index blocks being emptied and subsequently recycled, there could be some scenarios where accessing many of these effectively empty index blocks can be problematic.

Classic case is when we’re interested in the minimum value of a specific column. Oracle can potentially use an index to navigate to the first index leaf block in the index structure to quickly find the minimum value of an indexed column. However, if the first or left-most index leaf block contains no current index entries, Oracle will be forced to navigate to the next leaf block and if it’s also empty, to the next leaf block and so on until it finally comes across the first non-deleted index entry.

Similar demo, but this time lets make the table somewhat bigger for effect:

SQL> CREATE TABLE ziggy (id NUMBER, value VARCHAR2(30)) ;

Table created.

SQL> INSERT INTO ziggy SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <=1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX ziggy_i ON ziggy(id);

Index created.

Let’s now deleted 1/2 the index entries, all those on the left-hand side of the index structure:

SQL> DELETE ziggy WHERE id <=500000;

500000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>’BOWIE’, tabname=>’ZIGGY’, estimate_percent=> null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

We now run the following simply little SELECT statement featuring the MIN function:

SQL> SELECT MIN(id) FROM ziggy;

MIN(ID)
-------
 500001

Execution Plan
--------------------------------------------
| Id| Operation                  | Name    |
--------------------------------------------
|  0| SELECT STATEMENT           |         |
|  1|  SORT AGGREGATE            |         |
|  2|   INDEX FULL SCAN (MIN/MAX)| ZIGGY_I |
--------------------------------------------

Statistics
   0 recursive calls
   0 db block gets
1115 consistent gets
   0 physical reads
   0 redo size
 412 bytes sent via SQL*Net to client
 396 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   1 rows processed

Notice the high numbers of consistent gets, 1115 !! Wow.

Until the empty index blocks get recycled, all those effectively empty index blocks will make this MIN select statement run poorly. Classic example where an index rebuild (or coalesce or shrink) would be benefical to improve the performance of this specific query.

SQL> ALTER INDEX ziggy_i REBUILD ONLINE;

Index altered.

SQL> SELECT MIN(id) FROM ziggy;

MIN(ID)
-------
 500001

Execution Plan
-------------------------------------------
| Id| Operation                  | Name   |
-------------------------------------------
|  0| SELECT STATEMENT           |        |
|  1|  SORT AGGREGATE            |        |
|  2|   INDEX FULL SCAN (MIN/MAX)| ZIGGY_I|
-------------------------------------------

Statistics
  0 recursive calls
  0 db block gets
  3 consistent gets
  0 physical reads
  0 redo size
412 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed
 

Now the consistent gets have dramatically reduced from 1115 to just 3 making the select statement run noticeably faster.

Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.

Deleted Index Entries Part IV (Breaking Glass) June 25, 2008

Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.
6 comments

Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries.

In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty of index entries or it may contain just deleted index entries.

Once an index block has no current index entries, Oracle places the block on the segment freelist and is now a candidate block to be recycled and reused elsewhere within the index structure after a subsequent index block split operation.

When recycled, the index block becomes “unattached” from its current location within the logical index structure and is reallocated elsewhere within the logical index structure as the new index block in an index block split operation.

Any previously deleted index entries are removed and the contents of the index block are replaced with new index entries associated with its new logical location within the index structure.

A simple little demo to illustrate this process.

First, I create a simple table and associated index and populate it with a 10000 rows:

SQL> CREATE TABLE test_empty_block (id NUMBER, name VARCHAR2(30));

Table created.

SQL> INSERT INTO test_empty_block SELECT rownum, ‘BOWIE’ FROM dual
     CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

SQL> CREATE INDEX test_empty_block_idx ON test_empty_block(id);

Index created.

I next delete the vast majority of the rows, leaving only a handful behind that are likely only found in the last one or maybe two leaf blocks within the index. All the other index leaf blocks therefore only contain nothing but deleted index entries:

SQL> DELETE test_empty_block WHERE id between 1 and 9990;

9990 rows deleted.

SQL> COMMIT;

Commit complete.

If we look at some statistics, we’ll find we have lots of deleted row entries that are all found in leaf blocks that are totally empty, except perhaps the right most leaf block within the index:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21        9990 

We next insert a bunch of new rows into the table, but importantly, all these new rows have index entry values that are greater than the previous values. Therefore, all these new index entries will be inserted into the right most side of the index structure and not into the index where we have nothing but the previously deleted index entries. 

Oracle will need to allocate new index leaf blocks to accommodate these new index entries, but from where will Oracle get these new index blocks ?

SQL> INSERT INTO test_empty_block SELECT rownum+20000, ‘ZIGGY’
     FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

If we now look at the index statistics, we notice something very interesting:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21           0

The number of deleted leaf entries has disappeared back to zero and the number of allocated leaf blocks has remained the same at 21.

Oracle has both removed the previously deleted index entries and has also recycled all the previously empty leaf blocks and reused them again to accommodate the new index entries. The index is effectively the same size as it was previously even though we’ve added new values that were greater than the previously deleted values.

So index blocks that are totally empty or contain nothing but deleted index entries become “free” again, are placed on the freelist within the index segment and can be reused or recycled again somewhere else within the logical index structure at some later point in time.

Again, yet another example of Oracle cleaning out these unwanted deleted index entries for us.

However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.

But that’s a topic for another day.

Deleted Index Entries Part III (Slip Away) June 23, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths.
5 comments

Another little post while I look after some unwell munchkins …

I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block.

Another example of Oracle automatically removing deleted index entries is that associated with a variation of delayed block cleanout. If an Oracle index block with deleted index entries is written to disk before the associated transaction performing the index delete operation is committed, the next time the index block is accessed, Oracle will not only clean out the transaction details from the index block (such as the lock byte) but the deleted index entries themselves may also be cleaned out as well.

This scenario is most likely to occur during large or long running transaction operations (such as batch operations) where many rows are likely to be accessed and/or modified and the associated modified index blocks may get aged out of the buffer cache and written to disk before the transaction ends via the COMMIT.

Note this delayed clean out does not require the index block to be accessed via a subsequent DML operation, even a simple SELECT statement will be sufficient to perform the necessary clean out of deleted index entries.

To illustrate this behaviour, basically create a table with a bunch of rows, deleted some of them but flush the buffer cache prior to issuing the commit on the delete.

SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));

Table created.

SQL> CREATE INDEX del_stuff_i ON del_stuff(id);

Index created.

SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

Next, deleted say 1/2 of the rows from the table.

SQL> DELETE del_stuff WHERE mod(id,2) = 0;

500 rows deleted.

At this point, we flush the associated blocks to disk to simulate a large or long running transaction is which blocks may be aged from the buffer cache and written to disk before the COMMIT is performed.

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

Session altered.

in 9i, or since 10g:

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

It’s only at this point after the blocks are flushed to disk that the COMMIT is performed.

SQL> COMMIT;

Commit complete.

Once the index blocks are subsequently accessed, we notice the deleted index entries may have already been cleaned out …

NOTE: The following results do not consistently occur if the index consists of just a single block (the root block is a “special” case),  but does appear to be more consistent if the index has a blevel of one or more (as in the demo) and as would be more typical with indexes involved in long running transactions.

A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
------- ----------- ---------------
    500           0               0

Note: There are no deleted index entries, none. They’ve already been cleaned out.

 

A treedump will show the following:

 

—– begin tree dump

leaf: 0x1402e4a 20983370 (0: nrow: 500 rrow: 500)

—– end tree dump

 

Note: It only shows 500 rrow and 500 nrow values, clearly highlighting there are no deleted index entries.

 

A partial index block dump will show the following:

 

kdxlende 0

 

The deleted index entry count kdxlende is 0, with no deleted index entries existing in the block. None.

 

All the deleted index entries have already been cleaned out, with not a subsequent DML operation in sight.

 

So yes, again Oracle can clean out deleted index entries as part of it’s general processing so that the need to do so manually via an index rebuild, coalesce or shrink is a somewhat rare occurrence.

 

But wait, there’s still more cases to come when Oracle will simply automatically remove deleted index entries entries …

Deleted Index Entries – Part I (Let It Be) June 8, 2008

Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.
9 comments

Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle.

Generally speaking, when an index entry is deleted, Oracle doesn’t physically remove the index entry, it’s simply marked as deleted. It’s another case of Oracle putting off what could be an expensive operation for the current transaction and leaving any potential clean up operations to future processes. However, there’s often some confusion whether these deleted index entries remain “deadwood” within the index structure or whether they are somehow cleaned out later and the space potentially reused by subsequent inserts in the relevant index block.

To set the scene, we begin by creating a very simple scenario. Here we create a little table and associated index, insert a single row, commit it and then delete and commit the row afterwards. We can then have a bit of a look around to see how this deleted index entry is recorded by Oracle.

 SQL> CREATE TABLE test_delete (id NUMBER, name VARCHAR2(10));

Table created.

 

SQL> CREATE INDEX test_delete_idx ON test_delete (name);

 

Index created.

 

SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> DELETE test_delete WHERE id = 1;

 

1 row deleted.

 

SQL> COMMIT;

 

Commit complete.

 

We begin by looking at statistics related to the deleted index entries within the INDEX_STATS view.

 

SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;

 

Index analyzed.

 

SQL> SELECT lf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

 

   LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ----------- ---------------
         1           1              17 

 

So yes, the one and only index entry is a deleted index entry.

 

We can also see how many current deleted entries we have by looking at an index tree dump of the index.

 

SQL> SELECT object_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX’;

 

 OBJECT_ID
----------
     61198

 

SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 61198‘;

 

Session altered.

 

Following is the index tree dump generated by the above operation.

 

—– begin tree dump
leaf: 0x14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump

 

We notice that the rrow count which is the number of non-deleted index row entries is 0 but the nrow count which is the total index row entries, including deleted entries is 1. Therefore, yes the index currently consists of just the one deleted index row entry.

 

We can also view the deleted index details by performing a dump of the associated index block.

 

SQL> SELECT file_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX’;

 

   FILE_ID   BLOCK_ID
---------- ----------
         5       2257

 

SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2257;

 

 

System altered.

Below is an  extract from the above index block dump:

     Itl                    Xid                                    Uba                Flag  Lck            Scn/Fsc

0×01   0×0000.000.00000000  0×00000000.0000.00  —-      0  fsc 0×0000.00000000

0×02  0×0008.024.0000075b  0x00804e29.0078.0b  –U-      1  fsc 0×0011.00000000

  ……

kdxlende 1

kdxlenxt 0=0×0

kdxleprv 0=0×0

kdxledsz 0

kdxlebksz 8036

row#0[8021] flag: —D–, lock: 2, len=15

col 0; len 5; (5):  42 4f 57 49 45

col 1; len 6; (6):  01 40 10 0a 00 00

 

From the above, kdxlende 1 is a count of the deleted index entries. The index entry has a D flag set, signifying that the index entry has been deleted. Also note that the index entry was locked and deleted by the ITL entry associated with ITL number 2.

 

So yes, when we perform a delete that results in the deletion of an index row entry, the deleted index entry is marked as deleted but is not physically cleaned out at the time of the delete. All the above checks confirm this current state of the index.

 

The key question is therefore, are these deleted index entries ever reused/removed, or are they forever “deadwood” that would require a periodic rebuild of the indexes to clean out ?

 

Answer coming soon …

Primary Keys and Non-Unique Indexes (What’s Really Happening ?) June 4, 2008

Posted by Richard Foote in Constraints, Oracle General, Oracle Indexes, Oracle Myths, Primary Key.
34 comments

Based on this OTN thread, it appears there may still be folk out there that think Oracle uses Unique Indexes to police a Primary Key (or Unique Key) constraint. This is of course not necessarily true as since 8.0 and the introduction of Deferrable Constraints, Oracle can just as easily police a PK (or UK) constraint with a Non-Unique Index.

To determine whether a new PK value currently exists or not, Oracle can almost (although not quite) just as easily perform an index look-up using a Non-Unique Index as it can with a Unique Index. Simple index look-up, is the value there, yes or no, proceed as appropriate.

Indeed, I’ve already discussed on these here pages various differences between using a Unique and a Non-Unique Index:

https://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/

https://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/

https://richardfoote.wordpress.com/2007/12/30/differences-between-unique-and-non-unique-indexes-part-iii/

However, for those that may never have seen a case where creating a PK constraint has not created a Unique index, I thought it might be an idea to just go through a number of different scenarios when this is all quite possible.

The first example is when there’s already an existing non-unique index that Oracle can use. Oracle will not (and indeed can not) create a Unique Index where an identical Non-Unique index already exists.

SQL> create table bowie as select rownum id, ‘BOWIE’ text from dual connect by level <= 10000;

 

Table created.

 

SQL> create index bowie_i on bowie(id);

 

Index created.

 

SQL> alter table bowie add constraint bowie_pk primary key(id);

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =  ‘BOWIE_I’;

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

A subtle difference to this scenario is where there’s an existing Non-Unique index but it only has the leading columns the same as the PK constraint to be. Again, even if there are additional columns within the index, the fact the leading columns match the PK constraint means Oracle can still use the index to police the constraint as the index must be in the same logical order of these leading columns.

For example:

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_i;

 

Index dropped.

 

SQL> create index bowie_i on bowie(id, text);

 

Index created.

 

SQL> alter table bowie add constraint bowie_pk primary key(id);

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name = ‘BOWIE_I’;

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    TEXT     NONUNIQUE BOWIE_PK P
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

Another example is when the PK constraint is created as being DEFERRABLE. This means the constraint could be used to defer the policing of the constraint until the time of the COMMIT, rather than at the time of the DML statement. As such, for a period of time during a transaction, there could actually be duplicate values for the PK. Not only will Oracle create a Non-Unique index for such a Deferrable constraint, indeed Oracle MUST create a non-unique index. The existance of a previously created Unique Index will actually prevent Oracle from creating PK constraint as deferrable.

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_i;

 

Index dropped.

 

SQL> alter table bowie add constraint bowie_pk primary key(id) deferrable;

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE’;

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_PK   ID       NONUNIQUE BOWIE_PK P

Of course, another way to create a Non-Unique Index when defining a PK constraint is to simply create the specific index at the same time as the PK constraint. You can get pretty tricky with the create table syntax these days.

For example:

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_pk;

 

Index dropped.

 

SQL> alter table bowie add constraint bowie_pk primary key(id) using index (create index bowie_i on bowie(id));

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE’;

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

The next time you read or hear mentioned Oracle uses a Unique Index to police a PK (or UK) constraint, you now know it might not necessarily be the case.

Extended SQL Tracing Presentation (Your Possible Pasts) May 30, 2008

Posted by Richard Foote in Oracle General, Oracle Myths, Oracle Opinion, Performance Tuning.
3 comments

I’ve recently dug up an old presentation I did for the local Oracle User Group a number of years ago:

Yet Another Presentation On Extended Tracing

It’s slightly dated but has some useful general information on the subject of diagnosing performance issues and Extended SQL Tracing in Oracle that some may find useful, so I thought it might be worth posting it here. I stripped out most of the formatting so that the resultant file will be smaller to download.

Extended SQL Tracing has been absolutely invaluable over the years in diagnosing and trouble-shooting performance related issues. I had the very good fortune of attending a Hotsos Diagnosing Oracle Performance course in Sydney around 2003 (at the same time when Guy Sebastian won the first Australian Idol, ooops, probably shouldn’t have admitted remembering that) with Cary Millsap and Gary Goodman. It was a really fantastic training course which focused on the whole subject of “Method R” and using Extended SQL Tracing to diagnose performance issues. I also remember Steve Adams attending as well and thinking to myself, this guy really  knows his Oracle …

If you haven’t already, I would strongly recommend checking out the Optimizing Oracle Performance book by Cary Millsap with Jeff Holt, an excellent read.

And Guess what. Using Extended SQL Tracing actually works !!

Indexes and Sorts (Chant Of The Ever Circling Skeletal Family) May 26, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths.
7 comments

In my previous post regarding Reading 100% of Data via an Index, one of the examples I described was the scenario where an index was used to avoid a sort.

Index entries are always logically stored in the same order as the indexed columns (except of course when using a Reverse Key Index). Therefore if an index range scan is used to retrieve data, the data is returned in indexed column order. Sorts are relatively expensive operations, so if data needs to be retrieved in a specific order, the CBO can use the fact indexes return sorted data to its advantage and can potentially avoid performing the sort operation.

However, there’s a common misconception that if an appropriate index exists, Oracle will always use the index to avoid the sort. This is simply not true. Oracle will only use the index to avoid a sort if the costs of doing so is less than other alternatives. It could well be that the cost of performing say a Full Table Scan plus an associated sort might well be less than performing a really expensive and inefficient index range scan without the need for a sort. It depends on the relative costs of each possible option.

This Indexes and Sorts Part I Demo sets up a scenario and shows how an index can be used to select 100% of all rows in a table. However, note that the index in question has an excellent Clustering Factor with the rows stored in the table in basically the same order as that of the index. An index range scan of the whole table in this case is relatively efficient in that Oracle only needs to generally access each specific block the once. Therefore reading the entire table via an index and thus preventing the sort operation has a lesser cost than a potentially more efficient FTS but with the additional sorting overhead.

One of the more common reasons why an index is ignored when it possibly appears it should be used is due to the possibility of NULL values in the result set. As previously discussed, index entries that consist of nothing but NULLs are not indexed. Therefore an attempt to read all values from a table that has the potential to include NULL values may not use an associated index as the index may not reference all the required rows. The query either needs to be modified to exclude NULL values or the column(s) need to have a NOT NULL constraint for an index to be considered in this scenario.

This Indexes and Sorts Part II Demo shows how an index can not be used to eliminate a sort operation as the query result set could potentially return NULL values as the associated column does not have a NOT NULL constraint. By rewriting the query to exclude NULL values, the index is subsequently used by the CBO to retrieve all data, thus eliminating the sort operation.

Another perhaps more common reason why an index is not used to retrieve data in the index order, thus eliminating the need for a sort is that the cost of using the index to retrieve the necessary data is too costly and the savings in not performing the sort don’t outweigh the additional overheads of using the inefficient index. If an index has a very poor Clustering Factor, it can be extremely expensive for the CBO to use the index to retrieve data as most visits to the table requires a different table block to be accessed. In an extreme scenario, it may be necessary to read and re-read the same table block as many times as there are rows in the block and it may require as many distinct table block visits as there are rows in the table to retrieve all necessary rows via an index.

The cost of reading the table and the number of table block visits calculated by the CBO when using an index range scan is basically the selectivity of the query multiplied by the Clustering Factor of the index. Therefore the Clustering Factor is a crucial variable in deciding whether it’s actually worth using the index to prevent the sort or whether it’s actually less costly to use an alternative access path, such as perhaps an efficient FTS and subsequent sort.

The Indexes and Sorts Part III Demo is similar to the previous Part I demo except it uses a table that is ordered differently thus making the associated index result in a dreadful Clustering Factor. Consequently, the same query that previously used the index and no sort suddenly uses a FTS and a subsequent sort to read the entire table. However, even a query that only retrieves 10% of the data still uses a FTS and a subsequent sort. In fact even a query that retrieves just 1% of the data performs a FTS and subsequent sort. In this specific example, it wasn’t until approximately 0.11% of data was retrieved that the CBO decided it was cheaper to use the index and eliminate the sort than to use a FTS and subsequent sort.

The key message here is that yes, an index can be used to retrieve data in the specific order of the index and thus eliminate a sort operation. However, it will only do so if the cost of reading the necessary data via the index is less than alternative access paths, including the feared FTS, plus associated sort. If an index has a poor Clustering Factor, it is less likely to be considered as a method of eliminating a sort operation. 

Read-Only Table Before 11g (A Day In The Life) May 15, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Opinion, Read Only.
9 comments

An excellent question by fellow Aussie Chris Muir on this OTN Thread reminded me of a little trick I picked up in my travels.

Basically the question is how can one make a table read-only before 11g ?

The thread mentions a number of possibilities, some better than others. I thought I might just mention this possible solution involving Materialized Views. There are various alternatives based on this basic idea, this is just a simple example.

First create and populate a table we want to convert to Read-Only.

SQL> create table bowie_ro (id number, name varchar2(20));

Table created.

SQL> alter table bowie_ro add primary key (id);

Table altered.

SQL> insert into bowie_ro values (1, ‘Bowie’);

1 row created.

SQL> insert into bowie_ro values (2, ‘Ziggy’);

1 row created.

SQL> insert into bowie_ro values (3, ‘Major Tom’);

1 row created.

SQL> commit;

Commit complete.

Next, rename the table to a another name.

SQL> rename bowie_ro to bowie_temp;

Table renamed.

Next, create a materialized view called the original name based on the renamed table.

SQL> create materialized view bowie_ro
2 refresh on demand complete
3 as select * from bowie_temp;

Materialized view created.

Next, drop the orignal table.

SQL> drop table bowie_temp;

Table dropped.

We can now see and select the table as we could previously.

SQL> select * from bowie_ro;

ID         NAME
———————–
1          Bowie
2          Ziggy
3          Major Tom

However, you now can’t perform DML on the table, making it effectively read-only …

SQL> insert into bowie_ro values (4, ‘Thin White Duke’);
insert into bowie_ro values (4, ‘Thin White Duke’)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

You may of course need to add a few grants, constraints or indexes here or there but the table is now effectively read-only without the need of a read-only tablespace or other trick as mentioned in the OTN thread.

Back to indexes accessing all rows in a table soon :)

Index Scan or Full Table Scan: The “Magic” Number (Magic Dance) May 12, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion.
30 comments

What seems like ages ago, I listed 8 things you may not have known about indexes. Although I’ve since written about many of the 8 items, I’ve yet to address the last item listed:

8. An index can potentially be the most efficient and effective may to retrieve anything between 0% and 100% of the data from a table.

A few recent posts on OTN reminded me that perhaps it’s about time I wrote something on this topic.

Generally, the question that’s commonly asked is at what point or at what percentage of data does Oracle no longer consider the use of an index and judges the Full Table Scan (FTS) as the most efficient method to retrieve the data from a table.

Basically, what’s the “magic number”, is it 1% of data, 2%, 5%, 7.5%, 15%, 42%, 50% ???

The answer unfortunately is that there is no such magic number or percentage, it all entirely depends. The way I often answer this question is by simply stating I can very easily come up with a scenario where a FTS is the most cost effective method to retrieve 1% of the data. Equally, I can very easily come up with a scenario where an index is the most cost effective method to retrieve 99% of the data.

Like I said, there is no magic number, it entirely depends on a whole list of different factors and variables.

To start, I thought I might go through the example of how a 1% cardinality result is best achieved via a FTS, highlighting why and how the Cost Based Optimizer comes to such a decision.

I’ll use a simple little scenario with nice simple numbers to make the mathematics nice and easy to follow :)

OK, let’s assume we have a table that has 10,000,000 rows. The table uses 100,000 table blocks to store these rows and so we have on average 100 rows per block. With an 8K block size, we’re basically looking at a table with an average row size of about 80 bytes.

Let’s say this table has an associated index with approximately 20,000 leaf blocks required to store the index entries for a particular column and the index has a blevel of 2 (or a height of 3). This basically means we can store approximately 500 index entries per block and the average index entry is about 16 bytes or so in length.

The indexed column has 100 distinct values which are evenly distributed such that each distinct value has approximately 100,000 occurrences each. The column has no NULL values.

Let’s say we write a query based on the indexed column and we’re interested in just one of the possible 100 values or approximately 1% of the data in total. For example:

SELECT * FROM bowie_table WHERE code = ‘ABCDE’;

Does the CBO choose the index or does it chose the FTS ?

Well, let’s first cost the index access path.

We begin by reading the root block and the intermediate branch block for a cost of 2.

We also need to read approximately 1% of all the index leaf blocks in order to access all the index entries of interest. So that’s 20,000 (leaf blocks) x 0.01 = 200 leaf blocks in total.

So the total cost of reading just the index is 202.

Next comes the interesting bit. How many of the 100,000 table blocks do we need to access in order to read just 1% of the data (i.e. 100,000 rows) ?

Well, the answer depends entirely on the Clustering Factor of the index or to put it another way, in how well ordered the rows in the table are in relation to the index. If the index column values of interest are all very well clustered together in the table, then we can access the required rows by visiting fewer blocks than if the index column values are evenly and randomly distributed throughout the table.

In fact, in the worst possible cases scenario, if the Clustering Factor is appalling and has a value close to the number of rows in the table (10,000,000), we may actually need to visit each and every block in the table as each block has an average of 100 rows per block and we want on average 1% or one of these rows from each and every table block.

In the best possible case scenario, with the column values perfectly clustered together and with a Clustering Factor approaching the number of blocks in the table (100,000), we may get away with only having to visit 1% of all the table blocks or just 1,000 of them.

So the Clustering Factor is a crucial variable in how costly it would be to read the table via the index. The actual table access costs therefore are simply calculated as being the selectivity of the query (0.01 in our case) multiplied by the Clustering Factor of the associated index. 

In this example, the Clustering Factor is indeed appalling with a value of 10,000,000 and the table access costs are therefore calculated as 0.01 x 10,000,000 = 100,000.

So the total costs of using the index is 202 (for the index related costs) + 100,000 (to access the rows from the table) = 100,202 in total.

So what are the costs associated with the FTS ?

Well, the FTS has a number of advantages over the index scan. Firstly, as Oracle needs to process all the blocks, it can retrieve all the necessary rows by reading a specific table block just the once. However, with the index scan, Oracle may possibly need to access a specific table block multiple times in some scenarios. 

Secondly, as Oracle knows it has to read each and every block, Oracle can do so with a larger “bite of the pie” each time via multiblock reads, knowing it’s not wasting resources as all blocks need to be processed anyways. Index access reads perform single block I/Os whereas a FTS can perform muiltblock I/Os at a time. In this specific example, let’s assume the effective multiple read value is 10, remember, we want to keep the arthmetic nice and simple …

Finally, a FTS can be performed in parallel, even if the table itself isn’t partitioned, which means the overall response times can be further improved and the CBO can reduce its “costs” accordingly. In this example, we won’t worry about parallel query.

So the costs of a FTS in our example is basically 1 (for the segment header) + 100,000 (table blocks) / 10 (the effective multblock read value) = 1+10,000 = 10,001.

So that’s roughly an overall cost of 100,202 for the index vs. 10,001 for the FTS.

The results are not even close with the FTS winning hands down and that’s for just 1% of the data …

A couple of final little points for now.

Firstly, the cost of just reading 1 block (for the single block index reads) vs. 10 blocks (for the multiblock FTS reads) may actually differ somewhat as multiblock reads are doing more “work” with it’s associated I/O. By default, with no parameters set and with no system statistics, the CBO will cost each I/O as being the same. More about how to possibly adjust this another time.

Also, by default the CBO will assume all associated I/Os are physical I/Os and will cost them accordingly, even if the BCHR is nice and high and the index access path in question might be accessed within (say) a nested loop join where the likelihood of many of the index related I/Os in particular being cached is very high.  More on this at another time as well.

But for now, just note how in this relatively trivial example, the following factors came into play when determining the potential costs of this query:

  • Selectivity of the query
  • Data distribution with regard to the actual occurrences of the required data
  • Number of table blocks (below the high water mark)
  • Number of leaf blocks
  • Index Height
  • Average number of rows per table block
  • Average number of leaf entries per leaf block
  • Clustering Factor
  • Caching characteristics of index and table
  • Effective multiblock read count
  • Relative cost of single vs. multiblock I/Os
  • Parallelism

All of which contribute to make any single “magic number” by which Oracle will no longer consider using an index but another fairy tale in the Oracle book of myths and folklore …

Indexes In Their Own Tablespace: Recoverability Advantages (Get Back) May 2, 2008

Posted by Richard Foote in Backup and Recovery, Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion, Tablespace Management.
5 comments

Thought I might share some thoughts regarding recoverability issues with regard to having indexes separate and stored in their own tablespace.

I’ve already discussed here how the loss of an index only tablespace would be a catastrophic event, with the database in dire straights until the indexes are recovered. Therefore the faster we can recover from the situation, the faster we can make DML statements work again, the faster we can prevent Full Table Scans from crippling database performance, the faster we can return the database to a functional state again, the better for our users and for our sanity.

One of the advantages of having indexes separate from tables and stored in their own tablespace is that we have a number of different recovery options available to us. Rather than having to perform a full tablespace or data file recovery, we can potentially simply just rebuild all the impacted indexes. Providing the base tables are available and have not been impacted by whatever catastrophic event has befallen the index tablespace, we can rebuild the indexes (in another tablespace if necessary). This will hopefully be a more simplistic, efficiently and most importantly faster method of recovering all our impacted indexes than performing an actual database recovery.

But will it really be more simplistic, efficient and faster ? The recovery advantages with having indexes in their own tablespace are often exaggerated. Let’s first take a look at an example scenario.

Let’s assume we have an index only tablespace that stores all the indexes for our application. Let’s say we have 100G worth of indexes. In a physically separate table only tablespace, let’s say we have a total of 200G worth of table data which is approximately double that of the index tablespace. Generally speaking, it’s common for indexes to not use the same amount of storage as the tables as typically not all columns are indexed. Of course it’s possible for a specific column to be indexed several times and for the index storage to exceed table storage in some cases, but not typically. There may of course be some free space in these tablespaces but let’s assume free space is minimal.

So we have 100G of indexes and 200G of tables.

Let’s also assume there’s on average 2 indexes per table, if only to keep the following arithmetic nice and simple :) Of course some tables may have many more indexes, some may just have the one index and in some rare examples there may be no indexes at all.

Now, it’s important to note that building a new index is actually a very expensive exercise. Oracle has to read all the data blocks in the base table, it has to sort the data in the order of the index entries, it has to create the index segment and write the index data, while generating undo and redo in the process.

Now that’s a lot of work …

However, in this scenario, we need to do this work for each and every index that’s in our stuffed index tablespace. Not only that, but we also need a script that can identify each of our impacted indexes, that generates the necessary index rebuild scripts (to another tablespace if necessary) and that handles any necessary constraint related issues.

In this specific scenario, we have to make Oracle and the database processes basically perform the following amount of work:

  • Read approximately 400G of table related data. As we have an average of 2 indexes per table, we have to basically read each and every table an average of 2 times to build their related indexes. That’s 2 x 200G = 400G.
  • Sort approximately 100G worth of index related data. Sorting is a really expensive, relatively slow process and we have 100G worth of index data that needs to be sorted.
  • Write and create approximately 100G of index related segments

Note we also have to generate Data Dictionary related changes, we have to generate a bunch of undo related changes and we also (although optionally) generate lots and lots of redo.

In short, the database is being absolutely hammered during this whole process and it will take a loooong time to complete.

And this is meant to be the easy, efficient and above all fast method of recovering our indexes ?

So what is the alternate recovery strategy that this method of “simply” rebuilding all indexes is meant to protect us from.

Well, with a damaged tablespace, we basically need to perform a tablespace level recovery, restoring “just” the 100G worth of data files and applying any associated redo logs since our last backup. Depending on our backup and recovery strategy, we may actually reduce the redo logs being applied by applying incremental or cumulative backups as well.

Instead of the database slowly and laboriously having to read, process and write 6 or 7 times the amount of data (in our scenario), we can use the OS to much more efficiently copy across the index related data files.

Instead of having to script the rebuilding of all impacted indexes, literally a couple of RMAN commands will basically automatically completely restore and recover the impacted index tablespace for us.

In the scenario when only a specific data file or mount point within the tablespace has been problematic, the implications of attempting to recover the situation by simply rebuilding the indexes gets worse, much worse.

Firstly, if we are so inclined, we need to identify which indexes have at least one extent within the damaged portion of the index tablespace. We then need to entirely rebuild all these indexes, regardless of how much of the index may actually remain undamaged with other extents in undamaged portions of the index tablespace. This all takes resources, resources, resources and time, time, time.

We can’t just rebuild a part of an index (unless it’s partitioned of course) but we can recover a part of a tablespace. We can simply recover the damaged part of the tablespace, restoring and recovering just the specific data file or files, again potentially with just a few simple RMAN commands.

With small databases with small amounts of data, the time it takes to rebuild all indexes in an application may be acceptable for the business. However, in larger database environments, the extra time and resources required to rebuild large amounts of index data compared to other recovery strategies would be totally and completely unacceptable.

An exercise for those who store indexes in a separate tablespace, in large part because of the recoverability advantages. On a QA system or equivalent copy of your production database environment, go through a real exercise of attempting to recover your indexes by rebuilding them and actually time how long such a recovery process takes. Then repeat the exercise by recovering the database using a conventional database recovery technique and time the differences.

You may just come to the conclusion that rebuilding indexes may not be such a fast and efficient recovery process in many scenarios after all …

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:

  1. 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
  2. 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.

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.

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.
17 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.

Follow

Get every new post delivered to your Inbox.

Join 1,688 other followers