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.