ProActive and Reactive Indexing

ProActive Approach

It is possible to anticipate the necessary indexes While designing the database tables and relations. Based the nature of data and cardinality an expert data architect can design the correct indexes.

The types of indexes required for an Online Transaction systems ( OLTP Systems ) will be different from those required for Reporting Applications or Decision support Systems ( DSS Systems).

The number of records fetched or number of blocks required to be scanned in an OLTP System is very few. Normally OLTP applications are based on the user input to either query or update the record for a single key value. So fetching the table record based on the index scan will have least block reads and CPU time.

The primary key will allow the application developers to use a unique number to represent data record. The concept of primary key is required to implement referential integrity constraints or foreign keys.

The reporting queries of DSS Systems has to read the entire table in order to do sorting or aggregate function or perform join operations. If more than 25% of data in a table is to be selected then a full table scan can be chosen instead of an index scan.

Based on the percent of table data scanned either index scan or full table sacn can be used. The performance optimizer can choose the best plan if the table statistics are updated and the optimizer mode is set to either cost based or choose. If the queries contain either sorting or aggregate functions, then all the required data needs to be read either using a full table scan or an index range scan.

For tables that contain large text columns or if the average row length is high, then scanning the table invloves fetching large number of blocks and this may involve more disk reads. If all columns required for the query is present in an index then the table fetch can be avoided. This may reduce the amount of physical reads considerably.

Reactive Indexing Strategy

If the type of data or volume of data is not known while designing the datamodel, then it is not easy to predict the optimal indexes. When the database is running in production environment it will be possible to obtain statistics about the database volume and usage. By querying the data dictionary views it is possible to obtain most of the information related to performance.

The following information about the database will be useful in performance optimization

Information from Static Data Dictionary views

Information from Dynamic Performace views

Using v$sql_plan_statistics_all view

v$sql_plan_statistics_all view can be used to obtain statistics regarding the index usage and associated costs.
In order to enable statistics collection for this view TIMED_STATISTICS should be set TRUE and STATISTICS_LEVEL should be set to ALL.
V$STATISTICS_LEVEL lists the status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter. 

--  TOP 10 UNIQUE SCAN indexes ordered by physical reads

select * from(
select OPERATION,OPTIONS,OBJECT_OWNER,OBJECT_NAME,
sum(EXECUTIONS) EXECUTIONS,
sum(OUTPUT_ROWS) OUTPUT_ROWS,
sum(CR_BUFFER_GETS+CU_BUFFER_GETS) BUFFER_GETS,
sum(DISK_READS) DISK_READS,
sum(DISK_WRITES) DISK_WRITES,
sum(ELAPSED_TIME) ELAPSED_TIME
from v$sql_plan_statistics_all
where operation = 'INDEX' and options = 'UNIQUE SCAN'
GROUP BY OPERATION,OPTIONS,OBJECT_OWNER,OBJECT_NAME
ORDER BY DISK_READS DESC)
WHERE rownum<11;

My Oracle Debug Guide