Intelligent Oracle DBA Maintenance

Oracle provides table and index maintenance tools (Oracle 10g online reorganization) for a very good reason.  To keep Oracle running super-fast, Oracle chose not to incur the overhead of coalescing table rows and restructuring indexes during peak update times.  That?s why we have the DBA maintenance utilities.  The trick is knowing when to use the Oracle 10g DBA tools.

Oracle 10g and beyond has offered some huge improvements in Oracle indexing, especially related to the detection of missing indexes and materialized views (The SQLAccess Advisor) and the automation of index histogram detection for the SQL optimizer.  We also see these improvements to table maintenance in Oracle 10g:

Oracle Database 10g includes the following online data reorganization enhancements:

• Online table redefinition enhancements

• Easy cloning of indexes, grants, constraints, etc.    

• Convert from LONG to LOB online

• Allow unique index instead of primary key

• Change tables without recompiling stored procedures

• Online segment shrink

Despite all of the great automated tools, the Oracle DBA must still perform routine table and index maintenance to keep highly active databases performing at peak levels.

It?s been widely shown that rebuilding tables and indexes improves the speed of queries, and there has been a great debate about the benefits of rebuilding Oracle indexes.  There are two schools of thought on this important issue, and both sides make strong opposing arguments, leaving the DBA confused:

• Oracle Index Rebuilding is a waste of time - Some claim that indexes are always self-balancing and rarely need rebuilding.  Even after an Oracle index rebuild, they say that SQL query performance is rarely any faster.

• Index Rebuilds improve performance - Others note that indexes on tables with high DML (SQL inserts, updates and deletes) will be heavily fragmented, with lots of empty blocks and a sub-optimal access structure.  They claim to see huge performance improvements after rebuilding a busy Oracle index.

On the surface, both stances sound like good arguments, but we have to dig deeper to fully understand index maintenance.  Let?s take a logical approach to the issue of Oracle index rebuilding and start with these assertions:

• It?s about I/O - If SQL performance is faster after an index rebuild, it?s because the query does fewer index block reads.  This should be evident in the consistent gets (logical reads, from the data buffer) and physical reads, which are calls to the disk spindle, that may (or may not, if your disk has a RAM buffer) result in a physical disk read.

• Only some index access methods will benefit - Index Fast Full scans and some Index Range Scans will run faster after a rebuild.  Just like a full-table scan takes a long time when it reads a table with many empty blocks, reading a range of a sparse index will result in excessive logical reads, as empty index nodes are accessed by the SQL query execution.  Index problems will not improve after a rebuild, since index fragmentation does not matter when queries drop-down through the index.

• Oracle Indexes can get ?clogged? with empty and near-empty index blocks ? As massive deletes take place, large chunks of an index are ?logically deleted?, meaning that they are passed over by the pointers, but still remain in the structure.  Because the empty blocks (remember why you cannot specify PCTUSED for an index?) remain, block-by-block scans (any scan effected by db_file_multiblock_read_count) and some index range scans will perform less reads, and hence, less I/O and faster performance.

Indexes: Self-balancing or not?

The question about whether Oracle indexes are self-balancing is largely a matter of semantics.  As rows are added to an empty table, Oracle controls the addition of same-level blocks (called ?splitting?) until the higher level index node is unable to hold any more key-pointer pairs.  When the index can no longer split (because the owner block is full), Oracle will spawn a whole new index level, keeping the index tree in perfect logical and physical balance.

However, deletes are a different story.  Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete.  Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty.  For example, here is an Oracle index before a massive delete. (Figure 1)

Figure 1 ? A physical index after a massive row delete 

Now after a massive delete, the physical representation of the index is exactly the same because the empty data blocks remain.  However, the logical internal pointer structure is quite unbalanced, because Oracle has routed around the dead leaf nodes (Figure 2).

Figure 2 ? The logical pointer structure of an index after a massive row delete

This type of ?sparse? index is typical of an index on highly-active tables with large-scale inserts, deletes and updates.  We may have thousands of empty or near-empty index blocks, and several Oracle execution plans will run longer on this type of ?sparse? index:

• Index Range Scans ? Index range scans that must access many near-empty blocks will have excessive I/O compared to a rebuilt index.

• Index Fast Full Scans ? Because you can delete 70% of an index and still have the same number of data blocks, a full-index scan might run many times slower before it is rebuilt.

Because SQL must visit the sparse blocks, out SQL will take longer to execute.

Why do I have to re-build indexes?

From a software engineering perspective, it?s impossible to make a database with physically self-balancing blocks.  For example, imagine a bulk delete that removes 250,000 rows from a table, and that each index block contains 1,000 pointers.  Each index block may contain hundreds of pointers to other index nodes (depending on the symbolic key size and the blocksize).

If we wrote the index software to re-balance the physical tree whenever a index block became empty, the bulk delete operation could take hundreds of time longer to execute.  Oracle has made a deliberate decision not to coalesce near-empty blocks and re-balance physical blocks solely for performance reasons.

In our example, to be physically self-balancing we would have to re-balance the physical tree 250 times during our bulk delete and we would also have huge overhead when coalescing nearly empty blocks, shifting their pointers to nearly-full blocks.  It?s much more efficient to rebuild the index once, after the bulk delete. In fact, many shops that perform massive bulk operations in indexes tables will remove the indexes first, delete and update the rows, and then re-build the indexes afterward.

Rebuilding Indexes

When Oracle rebuilds an index, it sweeps the index nodes in LOGICAL orders, chasing the pointer chains and placing the new index into the designated tablespace as temporary segments.  The DBA controls the free space for node inserts with the PCTFREE parameters, which dictates how much room in the index block is reserved for future updates.  For example, if we know that the table will have 50% more rows added at a later time, we could set PCTFREE 50 and leave half of each index free to accept new entries without splitting or spawning.

Remember, you control the shape of your index tree with two techniques:

• PCTFREE ? Setting PCTFREE to a higher value will leave more space within each index block, sometimes creating a more vertical index tree, especially when using a small block size (e.g. 2k). For example, setting PCTFREE=70. means to leave 70% of the data block empty, in anticipation of future growth. (Figure 3)

• Index Blocksize ? Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.  This was proven by Robin Schumacher in his book ?Oracle Performance Troubleshooting?:

?As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache.  Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracleand above are worth your investigation and trials in your own database.?

Schumacher suggests using multiple blocksizes and putting all indexes (and tables that experience full-table scans because of the requirements of db_file_multiblock_read_count) into a 32k blocksize.  This results in a flatter index tree with fewer levels. (Figure 4)

             Figure 4 ? An index within a large blocksize

WARNING:  Using multiple blocksizes effectively requires expert-level Oracle skills and an intimate knowledge of your I/O landscape.  While deploying multiple blocksizes can greatly reduce I/O and improve response time, it can also wreak havoc in the hands of inexperienced DBA's.  Using non-standard blocksizes is not recommended for beginners.

Detractors of scheduled index rebuilding say that indexes should only be re-built when you have a clear test case that the rebuild will reduce logical I/O and/or physical I/O for SQL queries.  However, many shops with downtime maintenance windows will schedule periodic rebuilding because it is a low risk operation (e.g. the index will not be replaced unless it is successfully re-built).

All DBA?s acknowledge that database maintenance of a part of the job and they use tools such as the Oracle online redefinition utility (dbms_redefinition package) to periodically rebuild Oracle tables and indexes online, while the database continues to receive updates.

But how does the DBA perform maintenance on a 24x7 database?  Oracle 10g has the exciting online redefinition utility that uses Oracle replication techniques to allow the DBA to reorganize a table and its indexes while the database continues to accept updates. (Figure 5)

Even as Oracle 10g improves the tools, it?s still up to the subjective judgment of the DBA to decide when it is necessary to reorganize Oracle tables and indexes.