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');
