Concepts:
Most of cases in real time production environment performance is a major issue. Apart from Oracle bug or Configuration issue, major performance issues because of bad design queries or in-efficient execution plan of the queries. So most of cases Index scan will give you relief from high cost based queries. But some time indexes requires maintenance / to be rebuild / re-create to avoid the performance bottlenecks because of indexes only.
So, In an OLTP system, index space is often greater than the space allocated for tables, and fast row data access is critical for sub-second response time. Following are basic index structures:
a) B-tree indexes: This is the standard tree index that Oracle has been using since the earliest releases.
b) Bitmap indexes: Bitmap indexes are used where an index column has a relatively small number of distinct values (low cardinality). These are super-fast for read-only databases, but are not suitable for systems with frequent updates.
c) Bitmap join indexes: This is an index structure whereby data columns from other tables appear in a multi-column index of a junction table.
In addition to these index structures we also see interesting use of indexes at runtime. Here is a sample of index-based access plans:
a) Nested loop joins: This row access method scans an index to collect a series of ROWIDs.
b) Index fast-full-scans: This is a "multi-block read" access where the index blocks are accessed via a "db file scattered read" to load index blocks into the buffers. Please note that this method does not read the index nodes.
c) Star joins: The star index has changed in structure several times, originally being a single-concatenated index and then changing to a bitmap index implementation. STAR indexes are super-fast when joining large read-only data warehouse tables.
d) Index combine access: This is an example of the use of the index_combine hint.
Index information:
A common question for new DBAs that where we can gather index information. Oracle collects all index information for execution plan. Not only index all object information can be gathered using dba_ or v$ views.
The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an 'analyze index xxx validate structure' command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.
Inside Oracle b-tree indexes - why rebuild ?
There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing debate about rebuilding of indexes for improving performance. Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should "rarely" be rebuilt. Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building. Here are the pros and cons of the issue:
Arguments for Index Rebuilding. Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.
Arguments against Index Rebuilding. Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding. They claim that a reduction in Logical I/O should be measurable, and if there were any benefit to index rebuilding, someone would have come up with "provable" rules.
To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:
To rebuild Index ( From Oracle blog) - Click Here
CLUSTERING_FACTOR :
This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.
Click here - More about Clustering factor
INDEX HEIGHT :
As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.
BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large blocksizes (db_32k_block_size) because the index will spawn less.
Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
"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 Oracle9i and above are worth your investigation and trials in your own database."
PCT_USED :
This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.
Are there criteria for index/table rebuilding?
The short answer is no, there is no 100% complete, definitive list. But, here are some things to start with:
Index levels > 3
Pct_used < 75%
More than 20% of the rows have been deleted (space is not automatically reused)
Index is becoming unclustered, and performance is degrading (causing increases in number of blocks to be read) – while unloading, resorting, and reloading the data in a table may provide better performance, this is an additional maintenance activity that needs to be performed, and can be difficult to keep the rows in their proper sequence, if the table gets a lot of insert/update/delete activity.
Oracle Myths:
Useful Links:
Most of cases in real time production environment performance is a major issue. Apart from Oracle bug or Configuration issue, major performance issues because of bad design queries or in-efficient execution plan of the queries. So most of cases Index scan will give you relief from high cost based queries. But some time indexes requires maintenance / to be rebuild / re-create to avoid the performance bottlenecks because of indexes only.
So, In an OLTP system, index space is often greater than the space allocated for tables, and fast row data access is critical for sub-second response time. Following are basic index structures:
a) B-tree indexes: This is the standard tree index that Oracle has been using since the earliest releases.
b) Bitmap indexes: Bitmap indexes are used where an index column has a relatively small number of distinct values (low cardinality). These are super-fast for read-only databases, but are not suitable for systems with frequent updates.
c) Bitmap join indexes: This is an index structure whereby data columns from other tables appear in a multi-column index of a junction table.
In addition to these index structures we also see interesting use of indexes at runtime. Here is a sample of index-based access plans:
a) Nested loop joins: This row access method scans an index to collect a series of ROWIDs.
b) Index fast-full-scans: This is a "multi-block read" access where the index blocks are accessed via a "db file scattered read" to load index blocks into the buffers. Please note that this method does not read the index nodes.
c) Star joins: The star index has changed in structure several times, originally being a single-concatenated index and then changing to a bitmap index implementation. STAR indexes are super-fast when joining large read-only data warehouse tables.
d) Index combine access: This is an example of the use of the index_combine hint.
Index information:
A common question for new DBAs that where we can gather index information. Oracle collects all index information for execution plan. Not only index all object information can be gathered using dba_ or v$ views.
The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an 'analyze index xxx validate structure' command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.
Inside Oracle b-tree indexes - why rebuild ?
There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing debate about rebuilding of indexes for improving performance. Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should "rarely" be rebuilt. Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building. Here are the pros and cons of the issue:
Arguments for Index Rebuilding. Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.
Arguments against Index Rebuilding. Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding. They claim that a reduction in Logical I/O should be measurable, and if there were any benefit to index rebuilding, someone would have come up with "provable" rules.
To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:
To rebuild Index ( From Oracle blog) - Click Here
CLUSTERING_FACTOR :
This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.
Click here - More about Clustering factor
INDEX HEIGHT :
As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.
BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large blocksizes (db_32k_block_size) because the index will spawn less.
Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
"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 Oracle9i and above are worth your investigation and trials in your own database."
PCT_USED :
This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.
Are there criteria for index/table rebuilding?
The short answer is no, there is no 100% complete, definitive list. But, here are some things to start with:
Index levels > 3
Pct_used < 75%
More than 20% of the rows have been deleted (space is not automatically reused)
Index is becoming unclustered, and performance is degrading (causing increases in number of blocks to be read) – while unloading, resorting, and reloading the data in a table may provide better performance, this is an additional maintenance activity that needs to be performed, and can be difficult to keep the rows in their proper sequence, if the table gets a lot of insert/update/delete activity.
Oracle Myths:
- The vast majority of indexes do not require rebuilding
- Oracle B-tree indexes can become "unbalanced" and need to be rebuilt
- Deleted space in an index is "deadwood" and over time requires the index to be rebuilt
- If an index reaches "x" number of levels, it becomes inefficient and requires the index to be rebuilt
- If an index has a poor clustering factor, the index needs to be rebuilt
- To improve performance, indexes need to be regularly rebuilt
Useful Links:
