Step by Step Implementation of SQL Profiles in Oracle

Original Problematic Query:

SELECT *
FROM TEMP_TAB A2,TEMPOFF A1
WHERE A2.TEMP1=A1.TEMP1
AND (A2.TEMP13=1 OR A2.TEMP13=0 OR A2.TEMP13=0)
AND A2.STEMPDATE>=:B3
AND A2.STEMPDATE<:b2 a2.TEMPTEMP5<='' and=''>000
AND A2.TEMPTEMP5<>000
AND A2.TEMPTEMP5<>0
AND A2.TEMPTEMP5<>0
AND A1.TEMPTEMP6=:B1
AND A1.TEMPTEMP6<>0
GROUP BY A1.TEMPTEMP6,A1.TEMPTEMP7,TRUNC(A2.STEMPDATE,'MONTH')


===================
Plan of above query:

Plan hash value: 2803934278

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |       |       | 18133 (100)|          |       |       |
|   1 |  HASH GROUP BY                    |              |    15 |   720 | 18133  (39)| 00:00:47 |       |       |
|*  2 |   FILTER                          |              |       |       |            |          |       |       |
|*  3 |    HASH JOIN                      |              |   781K|    35M| 17906  (38)| 00:00:47 |       |       |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| TEMPOFF        |   104 |  1352 |    54   (0)| 00:00:01 |       |       |
|*  5 |      INDEX RANGE SCAN             | TEMPOFF_TEMPTEMP6    |   104 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ITERATOR      |              |  9176K|   306M| 17549  (37)| 00:00:46 |   KEY |   KEY |
|*  7 |      TABLE ACCESS FULL            | TEMP_TAB |  9176K|   306M| 17549  (37)| 00:00:46 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1<>0 AND :B3<:b2))
   3 - access("A2"."TEMP1"="A1"."TEMP1")
   5 - access("A1"."TEMPTEMP6"=:B1)
       filter("A1"."TEMPTEMP6"<>0)
   7 - filter((INTERNAL_FUNCTION("A2"."TEMP13") AND "A2"."STEMPDATE">=:B3 AND "A2"."TEMPTEMP5"<>0 AND
              "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."STEMPDATE"<:b2))


===================

Rationale: As the filter at line 7 in plan is using Full Table Scan (FTS) of partition(s) of TEMPH.TEMP_TAB table, and it is likely that index access would speed up the query.(Also we know that the earlier optimal runs of the query were using index), so we need to enforce index access at line 7.


Table structure:

SQL> desc TEMPH.TEMP_TAB
 Name                                                  Null?    TEMP13
 ----------------------------------------------------- -------- ------------------------------------
 TEMP1                                                   NOT NULL NUMBER
 TEMPTEMP5                                                   NOT NULL NUMBER
 STEMPDATE                                                 NOT NULL DATE
 BTEMPTEMP7                                                  NOT NULL NUMBER
 TEMPCOUNT                                                         NUMBER
 TMPTEMP13                                                  NOT NULL NUMBER
 TMPSUID                                                          NUMBER
 TEMP8_TEMP10_NO                                                 NUMBER


========================

Existing Composite indexes on table TEMPH.TEMP_TAB:

1-      PK_TEMP_TAB(STEMPDATE,TEMP1,TEMP13,BTEMPTEMP7,TEMPTEMP5)
2-      STEMPH_TEMP11(TEMP1,STEMPDATE,TEMP13,TEMPTEMP5)
3-      STEMPH_SSTO (TEMPTEMP5,STEMPDATE,TEMP13,TEMP1)
4-      TEMP_TAB_TEMP8_TEMP10_NO (TEMP8_TEMP10_NO,STEMPDATE) --> not applicable because columns are not in the filter
5-      TEMP_TAB_SHH_TEMP9EMPTEMP5_KEY(SHH_TEMP9EMPTEMP5_KEY,STEMPDATE)    --> not applicable because columns are not in the filter

====================

Filter from the plan on lin 7 using FTS:

filter((INTERNAL_FUNCTION("A2"."TEMP13") AND "A2"."STEMPDATE">=:B3 AND "A2"."TEMPTEMP5"<>0 AND
              "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."STEMPDATE"<:b2))

The columns of TEMPH.TEMP_TAB table used in this filter are TEMP13,STEMPDATE,TEMPTEMP5.

Anyway, here we know that TEMPH.STEMPH_TEMP11 index should be used from the TEMPHorical runs and also the test run with bind variable values confirms that.

Now we have to force this query to use the index TEMPH.STEMPH_TEMP11. We can do it with hints, but since we cannot TEMP10 the query, so we are employing the SQL Profile here to enforce the usage of Index.

Now comes implementation of SQL profile:

In order to get string for the SQL profile, first run the query with the hint, and then get the plan with outline information, and then from outline information just get the string, as shown  below:

SQL> set lines 100
SQL> set pages 100
SQL> set timing on
SQL> SELECT /*+index(s STEMPH_TEMP11)*/ *
  2  FROM TEMPH.TEMP_TAB S, TEMPOFF O
  3  WHERE S.TEMP1 = O.TEMP1 AND S.TEMP13 IN (1, 3, 5) AND S.STEMPDATE >= to_date('11/01/11','mm/dd/yy') AND S.STEMPDATE < to_date('12/01/11','mm/dd/yy')
  4  AND S.TEMPTEMP5 NOT IN (0, 112, 0 , 0)
  5  AND (O.TEMPTEMP6 = 0 ) AND O.TEMPTEMP6 NOT IN (0) GROUP BY O.TEMPTEMP6, O.TEMPTEMP7, TRUNC (S.STEMPDATE, 'MONTH');


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED +ALLSTATS'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / S@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / O@SEL$1
   8 - SEL$1 / S@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("TEMPOFF"."TEMPTEMP6"))
--->>     INDEX(@"SEL$1" "S"@"SEL$1" ("TEMP_TAB"."TEMP1" "TEMP_TAB"."STEMPDATE" "TEMP_TAB"."TEMP13" "TEMP_TAB"."TEMPTEMP5")) <<------ SQL PROFILE String
      LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1")
      USE_NL(@"SEL$1" "S"@"SEL$1")
      END_OUTLINE_DATA
  */


==================================


-->> From the outline section above, get the string and create SQL profile for problem query as follows:

begin dbms_sqltune.import_sql_profile (sql_text => 'SELECT * FROM "TEMP_TAB" "A2","TEMPOFF" "A1" WHERE "A2"."TEMP1"="A1"."TEMP1" AND ("A2"."TEMP13"=1 OR "A2"."TEMP13"=3 OR "A2"."TEMP13"=5) AND "A2"."STEMPDATE">=:B3 AND "A2"."STEMPDATE"<:b2 "a2"."TEMPTEMP5"<='' and=''>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A1"."TEMPTEMP6"=:B1 AND "A1"."TEMPTEMP6"<>0 GROUP BY "A1"."TEMPTEMP6","A1"."TEMPTEMP7",TRUNC("A2"."STEMPDATE",''MONTH'')',
profile => sqlprof_attr('INDEX(@"SEL$1" "A2"@"SEL$1" ("TEMP_TAB"."TEMP1" "TEMP_TAB"."STEMPDATE" "TEMP_TAB"."TEMP13" "TEMP_TAB"."TEMPTEMP5"))'),
name=>'FIX_1',
force_match=>false);
end;
/


Now check the latest plan for the session running the query and verify that index is being used in plan.

Afterwards, the profile can be dropped as:

exec dbms_sqltune.drop_sql_profile('FIX_1');