Oracle Performance Tools and Solutions

Optimize IT Costs and Existing Assets

Improve Business Peformance, Response Times, Query Throughput

Oracle Index Tips

1. The selectivity of an index helps the cost-based optimizer determine the best possible execution path. Increasing selectivity by creating composite indexes, where possible, can increase the performance of SQL statements.
2. Selectivity is just one part of the performance equation. The efficiency of an index is also affected by the distribution of the data, so it is important to make sure that the related rows are well ordered.
3. Keeping in mind that the values of indexed columns are stored in the index, a well ordered composite index can be used to satisfy a query without accessing the table. A well designed composite index can greatly reduce the amount of I/O required to satisfy a query.
4. Because every index on a table slows INSERTs, UPDATEs, and DELETEs your goal when designing an index should be (at least in part) to reduce the number of single block reads as much as possible.
5. The clustering factor of an index is a measure of its ordered-ness. Indexes with a high clustering factor require more data blocks to be visited thereby increasing the amount of I/O required for an operation. If an index’s clustering factor is low (relative to the number of leaf blocks) there is a greater chance that the data blocks required to satisfy a statement will be resident in memory.

Index Hunting

On your Oracle 10.1 and higher databases, run the query shown below to find indexes that are good candidates for being dropped (or rebuilt). This query identifies the Top 10 indexes with low selectivity and a high clustering factor to leaf block ratio. If you drop (or rebuild) these indexes you should see reduced I/O and CPU utilization. Happy hunting!

Let's make it easy for you. Right Click, Download, and SAVE AS... DropIndexMagic.sql

Optimize Performance :: GET STARTED!!!

• Brother-Owl™ captures, analyzes, and fixes application SQL and Oracle indexes. DBAs and developers can use this tool to fix SQL in production or as part of the testing process for new code. Brother-Owl gives expert advice on how to tune SQL, Oracle objects, use Oracle hints, or advise you on Index effectiveness. The product allows "What If" analysis on a variety of changes. Most importantly, all of this work can be done without impacting your production systems.


• Brother-Wolf™ reorganizes business-critical databases while applications remain running and fully available to business users. Brother-Wolf quickly and easily identifies the objects that are most active and in need of reorganization, then schedules reorganization jobs for those objects. By ptimizing performance and reclaiming wasted disk space, Brother-Wolf reduces the eed for additional storage expenditures.
  
• Brother-Eagle® for real-time database activity monitoring.


• DOWNLOAD a FREE TRIAL and discover the value of working together with DBI
  
• Contact DBI and we can have a chat about how we can best help you.