Index Optimization Scripts

Information regarding index usage and performance statistics can be obtained from dynamic performance views. By looking at the queries it will be possible to find the queries that are doing full table scan and the ones that can benefit from new indexes. The following data dictionary views are useful for the data analysis. The shared pool keeps the most recent SQL statements, plan and statistics in the cache.

To list SQL statements in Shared pool
This can be ordered by number executions, cpu time or disk reads
select sorts, executions, parse_calls, disk_reads, buffer_gets, command_type, cpu_time, elapsed_time from v$sql;

Queries having full table scan of the employee table in scott schema
select sorts, executions, parse_calls, disk_reads, buffer_gets, command_type, cpu_time, elapsed_time
from v$sql where (address, hash_value, child_number) IN(
select address, hash_value, child_number from v$sql_plan where
object_owner='SCOTT' and object_name='EMPLOYEE' and operation='TABLE ACCESS' and options='FULL');

Get Expensive queries
First find the expensive queries in terms of CPU Time or disk reads. Next get the plan of the query. The column plan_hash_value is a Numerical representation of the SQL plan. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same ( instead of comparing the two plans line by line ). Values can be computed by grouping plan hash values.

Find the expensive plan hash value
select * from( select plan_hash_value, sum(cpu_time), sum(disk_reads), sum(elapsed_time)
from v$sql group by plan_hash_value order by 3 desc) where rownum<2;

For the above plan_hash_value, say p1, get addres and hash_value of one SQL
select * from(select address, hash_value, child_number from v$sql where plan_hash_value=p1) where rownum<2;

Using the above address and hash_value query the v$sql_plan to get the execution plan for the above query
select * from v$sql_plan where (address, hash_value, child_number) IN
(select * from(select address, hash_value, child_number from v$sql where plan_hash_value=p1) where rownum<2);

Monitoring Index Usage

There are cases where the existing indexes are not used. This can increase the database writes for tables with heavy transactions. You can use the view v$object_usage to monitor index usage. The view displays statistics about index usage gathered from the database. Monitor for sufficient number of days. All indexes that have been used at least once can be displayed in this view.

Set index monitoring
alter index monitoring usage;

To turn off monitoring
alter index nomonitoring;

To generate monitoring scripts for all indexes
select 'alter index '||index_name||' monitoring usage;' from user_indexes where index_type='NORMAL';

To turn off monitoring for all indexes
select 'alter index '||index_name||' nomonitoring;' from user_indexes where index_type='NORMAL';

Find unused indexes
select index_name, table_name, monitoring, used from v$object_usage where monitoring='YES' and used='NO';

Choosing the index type

A bitmap index is more suitable where there are very few distinct values.
The following query can be used to get a list of indexes where the leading column has very few distinct values. Before running this query the tables or schema has to be analyzed with "COMPUTE STATISTICS" and the option 'for all columns'

select * from( select a.index_name,
round(b.num_distinct*1000/b.sample_size) cardinality, b.num_distinct
from user_ind_columns a, user_tab_col_statistics b
where a.table_name=b.table_name and a.column_name=b.column_name
and a.column_position=1 and nvl(b.sample_size,0)>100)
where cardinality<2 and num_distinct<6;

Partitiond global indexes

As the number rows in the table increases the height of the index tree and also the index maintenance cost increases. Partitioning helps to reduce the size of the index tree. Even for a table that is partitioned, partitiond global indexes can be created for the indexes that are not based on the table partition key.

Consider the sales table with millions of rows. A partitioned index has to be created based on customer_id. It is decided to have one million rows per index partition.

The following query can be used to select range boundaries for index partition

select * from( select customer_id, rownum r1 from
(select customer_id from sales order by 1) )
where mod(r1,1000000)=0 order by r1;

0 1

1500000 2

3500000 3

6000000 4

8000000 5

15000000 5

Create the partitioned index
create index sales_customerid_idx on sales(customer_id)
GLOBAL PARTITION BY RANGE(customer_id)
(PARTITION P1 VALUES LESS THAN( 1500000 ) tablespace ts1,
PARTITION P2 VALUES LESS THAN ( 3500000 ) tablespace ts1,
PARTITION P3 VALUES LESS THAN ( 6000000 ) tablespace ts1,
PARTITION P4 VALUES LESS THAN ( 8000000 ) tablespace ts1,
PARTITION P5 VALUES LESS THAN ( 15000000 ) tablespace ts1,
PARTITION PN VALUES LESS THAN ( maxvalue) tablespace ts1
);