Happy New Year Everyone I hope your holidays were filled with joy and happiness!
I was thinking that a good way to start out the New Year would be to provide some information on how to improve the performance of your Visualizer data base – which is the heart of your BMC Performance Assurance Capacity Planning/Performance Management system. Now, bear in mind, I am not a Data Base Administrator (DBA), so you may have to enlist the help of your friendly DBA to accomplish these recommendations. I hope you will find this useful. This information can also be found in our Knowledge base at KA362099 Performance Assurance Visualizer Re-indexing data base tables provides performance improvement (requires support login).
Re-Indexing Visualizer Data Base Tables to Improve Performance
Is your Manager/Automator data base population creeping up on your SLA availability window? To improve the Visualizer data base performance, ask your DBA to plan and schedule a regular re-indexing of the following tables, which are heavily used by Performance Assurance in the Visualizer data base. There are other tables, but these are the ones that will benefit most.
Events of data population, summarizing and deleting old performance data will perform delete, update, and insert actions in the Visualizer data base. As Oracle indexes are not self-balancing, they will become fragmented after a large number of INSERTs and DELETEs, which may lead to significant performance degradation.
How can you tell if it there will be any benefit?
Ask your DBA run an Oracle AWR (Automatic Workload Repository) report which can be generated through Oracle Enterprise Manager. Things to look at are Index Performance degradation or Very High physical read bytes; physical read total bytes; physical write total bytes values in AWR report (With High Magnitude of data alteration, which leads fragmentation).
Indexes are Oracle data base objects that provide a fast, efficient method of retrieving data from data base tables. The physical addresses of required rows can be retrieved from indexes much more efficiently than by reading the entire table. Effective indexing usually results in significant improvements to SQL performance too.
Oracle's default index structure is B*-tree, which stands for "Balanced tree." It has a hierarchical tree structure. At the top is the header. This block contains pointers to the appropriate branch block for any given range of key values. The branch block points either to another branch block, if the index is big, or to an appropriate leaf block. Finally, the leaf block contains a list of key values and physical addresses (ROWIDs) of rows in the data base.
Oracle data bases experience a huge benefit from periodic index rebuilding. Oracle recognized this benefit of index rebuilding when the Oracle 9i (10g/11g) online index rebuild feature made it possible to rebuild an Oracle index while the index is being updated.
Index Rebuild/Coalesce advantagePerformance radically improves after an index rebuild especially:
1. When a index rebuild is combined with a table reorganization (using the dbms_redefinitionpackage). This is especially useful when the data is accessed via index range scans and when the table is re-sequenced into index-key order (using single-table clusters, or via a CTAS with an order by clause).
2. When a heavily-updated index is rebuilt. In highly volatile data bases in which table and column values change radically, periodic index rebuilds will reclaim index space and improve the performance of index range scans.
"Index rebuilds are low risk - Because the new index is created in temporary segments, Oracle will never destroy the old index until the new index has been created.
"Index rebuilds are unobtrusive - Oracle indexes can be rebuilt online without interruption to availability and DML activity.
"Index rebuilds are cheap - The cost of the duplicate disk to store a new index tree is negligible, as are the computing resources used during a rebuild. Many Oracle professionals forget that unused server resources can never be reclaimed, and servers depreciate so fast that the marginal cost of utilizing extra CPU and RAM are virtually zero.
The image below (Ref; http://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes002.htm) illustrates the effect of an ALTER INDEX REBUILD or COALESCE on the index. Before performing the operation, the first two leaf blocks are 50% full. This means you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second.
When should one perform a rebuild? [Oracle Metalink Document ID 182699.1]
Oracle has 4 main features with regard to its internal maintenance of indexes that makes index rebuilds such a rare requirement.
1. 50-50 blocks split and self-balancing mechanism
2. 90-10 block split mechanism for monotonically increasing values
3. Reusability of deleted row space within an index node
4. Reusability of emptied nodes for subsequent index splits.
We hope you have found this article useful. Let us know.