How to Decide if column should be indexed?

Many people ask these Questions,
Q. How to identify which column is candidate for Index creation?
Q. How to determine index will really help to perform query faster?
Q. Which columns should be indexed in oracle?
Q. How to identify index will give benefit to optimizer to generate better access path?
Q. How do i know when to create index on column?

Answer for all above Questions is,
SQL Tuning Advisor

Q. How ???
Ans. Let’s check below test case for the same. Below method/approach one can use to diagnose
 problem and resolve performance issue.

I have below Query which is running poorly.

SELECT * FROM TXN_MASTER,TXN_DETAIL
WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
AND TXN_MASTER.STATUS ='F'
AND TXN_MASTER.TXN_DATE <=sysdate
AND TXN_MASTER.FILE_NO is null;

Check the below explain plan for the same.

SELECT STATEMENT, GOAL = ALL_ROWS   Cost=115161 Cardinality=2 Bytes=582
NESTED LOOPS                        Cost=115161 Cardinality=2 Bytes=582
NESTED LOOPS                         Cost=115161 Cardinality=2 Bytes=582
TABLE ACCESS FULL Object owner=TSTSCHEMA Object name=TXN_MASTER                      Cost=115160 Cardinality=2 Bytes=196
INDEX UNIQUE SCAN Object owner=TSTSCHEMA Object name=PK01_TXN_DETAIL             Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TSTSCHEMA Object name=TXN_DETAIL       Cost=1 Cardinality=1 Bytes=193

My Pre-checks to diagnose problem area.
1) Check joining conditions are proper.
2) Check of statistics for both the tables.
3) Check of indexes created for columns which used in where clause.

Findings,
1) Joining conditions are proper. No extra condition require to join both the tables.
2) Statistics of table TXN_DETAILis stale.
3) Indexes are not present for column STATUS, TXN_DATE, FILE_NO

Now, let’s check SQL Tuning Advisor. What are the recommendations provided by advisor.

Step 1: Create Tuning Task.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 l_sql VARCHAR2(4000);
3 l_sql_tune_task_id VARCHAR2(100);
4 BEGIN
5 l_sql := 'SELECT * FROM TXN_MASTER,TXN_DETAIL
6 WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
7 AND TXN_MASTER.STATUS =''F'''||
8 'AND TXN_MASTER.TXN_DATE <=sysdate
9 AND TXN_MASTER.FILE_NO is null
10 ';
11
12 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
13 sql_text => l_sql,
14 user_name => 'TSTSCHEMA',
15 scope => DBMS_SQLTUNE.scope_comprehensive,
16 time_limit => 60,
17 task_name => 'My_Qry_tuning_Tsk1',
18 description => 'Tuning task for an PM Query.');
19 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
20 END;
21 /
l_sql_tune_task_id: My_Qry_tuning_Tsk1
PL/SQL procedure successfully completed

Step 2: Execute Tuning Task created in above step.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1 COMPLETED

Step 3: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : My_Qry_tuning_Tsk1
Tuning Task Owner : TSTSCHEMA
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 08/24/2016 10:58:28
Completed at : 08/24/2016 10:59:21
-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID : 44b0kssyj6npj

SQL Text : SELECT * FROM TXN_MASTER,TXN_DETAIL
WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
AND TXN_MASTER.STATUS ='F'AND
TXN_MASTER.TXN_DATE <=sysdate
AND TXN_MASTER.FILE_NO is null

FINDINGS SECTION (3 findings)

1- Statistics Finding
---------------------
Optimizer statistics for table "TSTSCHEMA"."TXN_DETAIL" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'TSTSCHEMA', tabname =>
'TXN_DETAIL', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.

2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 77.79%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'My_Qry_tuning_Tsk1', task_owner => 'TSTSCHEMA', replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 5 will improve its response time
77.79% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.04% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.

Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0

Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0

3- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.

create index TSTSCHEMA.IDX$$_61A90001 on
TSTSCHEMA.TXN_MASTER("FILE_NO","STATUS","TXN_DATE");

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

SQL Tuning Advisor shown three recommendations.
1) Gather Stats for table TXN_DETAIL.
2) Use Parallel Query Execution
3) Create Index for three columns i.e. STATUS, TXN_DATE, FILE_NO

1st and 3rd recommendations we have identified as problem in our basic analysis above. SQL Tuning
 Advisor has suggested the same and also provided % of benefits we get after implementing it.
Here I have implemented on 1st & 3rd recommendations which I has diagnosed and will check how it
 works.
I have executed below statement which is provided in above recommendations number 1.

execute dbms_stats.gather_table_stats(ownname => 'TSTSCHEMA', tabname => 'TXN_DETAIL',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO', cascade => TRUE);=

Now, check Explain Plan if any difference.

SELECT STATEMENT, GOAL = ALL_ROWS      Cost=115161 Cardinality=2 Bytes=582
NESTED LOOPS                           Cost=115161 Cardinality=2 Bytes=582
NESTED LOOPS                           Cost=115161 Cardinality=2 Bytes=582
TABLE ACCESS FULL Object owner=TSTSCHEMA Object name=TXN_MASTER            Cost=115160 Cardinality=2 Bytes=196
INDEX UNIQUE SCAN Object owner=TSTSCHEMA Object name=PK01_TXN_DETAIL            Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TSTSCHEMA Object name=TXN_DETAIL        Cost=1 Cardinality=1 Bytes=193

There is no difference found in Explain Plan.
Now, Let’s Run SQL Tuning Advisor again to check his recommendation.

Step 1: Execute Tuning Task created in above step.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1 COMPLETED

Step 2: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;

SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : My_Qry_tuning_Tsk1
Tuning Task Owner : TSTSCHEMA
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_25749
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 08/24/2016 11:45:40
Completed at : 08/24/2016 11:46:20

-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID : 44b0kssyj6npj
SQL Text : SELECT * FROM TXN_MASTER,TXN_DETAIL
WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
AND TXN_MASTER.STATUS ='F'AND
TXN_MASTER.TXN_DATE <=sysdate
AND TXN_MASTER.FILE_NO is null

FINDINGS SECTION (2 findings)

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 77.79%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'My_Qry_tuning_Tsk1', task_owner => 'TSTSCHEMA', replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 5 will improve its response time
77.79% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.04% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.

Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution

--------------------------------------------
Weekly DB time (in sec) 0

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index TSTSCHEMA.IDX$$_61A90001 on
TSTSCHEMA.TXN_MASTER("FILE_NO","STATUS","TXN_DATE");

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

See, Now SQL Tuning Advisor shown two recommendations.
1) Use Parallel Query Execution
2) Create Index for three columns i.e. STATUS, TXN_DATE, FILE_NO

Now Let’s create Index as below only for two columns i.e. STATUS, TXN_DATE which is most important
 filter criteria of given query. We will not create index on FILE_NO, this is because we know that FILE_NO
 is not much important filter criteria.

Create bitmap index ind_patboutSTATUS on pm1222day0.TXN_MASTER(“STATUS”);
(Bitmap index created because this field contains single character “F” & ”S” only.)

Create index ind_patbouttxn_date on pm1222day0.TXN_MASTER(“TXN_DATE”);
(Normal index created.)

Now, let’s check the Explain Plan

SELECT STATEMENT, GOAL = ALL_ROWS         Cost=5 Cardinality=2 Bytes=582
NESTED LOOPS                              Cost=5 Cardinality=2 Bytes=582
NESTED LOOPS                              Cost=5 Cardinality=2 Bytes=582
TABLE ACCESS BY INDEX ROWID BATCHED Object owner=TSTSCHEMA Object name=TXN_MASTER Cost=4 Cardinality=2 Bytes=196
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE Object owner=TSTSCHEMA Object name=IND_PATBOUTSTATUS
INDEX UNIQUE SCAN Object owner=TSTSCHEMA Object name=PK01_TXN_DETAIL           Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TSTSCHEMA Object name=TXN_DETAIL       Cost=1 Cardinality=1 Bytes=193

Waaaww…Tremendous changes in CPU Cost and plan is using index ind_patboutSTATUS.Now let’s check SQL Tuning Advisor again.

Step 1: Execute Tuning Task created in above step.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1 COMPLETED

Step 2: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : My_Qry_tuning_Tsk1
Tuning Task Owner : TSTSCHEMA
Workload Type : Single SQL Statement
Execution Count : 4
Current Execution : EXEC_25752
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 08/24/2016 12:10:25
Completed at : 08/24/2016 12:10:28
-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID : 44b0kssyj6npj
SQL Text : SELECT * FROM TXN_MASTER,TXN_DETAIL
WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
AND TXN_MASTER.STATUS ='F'AND
TXN_MASTER.TXN_DATE <=sysdate
AND TXN_MASTER.FILE_NO is null
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------

Great, there are NO recommendations now. We have completed this test case successfully.

Take away from this article.

- Identify root cause of poor SQL performance.
- Identify how much benefits we get from indexes before creating it in production.
- Usage of SQL Tuning Advisor.
- Step by step approach to implement SQL tuning Advisor recommendations.