Thursday, August 25, 2016

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>


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
WaaawwTremendous 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.
-------------------------------------------------------------------------------
SQL>


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.

Hope this article will help you. Please provide your comments on this. Also feel free to ask any question for the same.


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
Post a Comment