Pushing oracle hints in subqueries using SQL Profiles

As an oracle DBA, you may have come across situations where you are left with no other options of tuning the query because of certain application dependencies and then you have to push hints through outlines, sql patch or sql profiles. I had to deal with an interesting situation where it becomes quite tricky while pushing the hints.

First of all as a performance tuning DBA, this question should always come in your mind that why do I have to use hints at all? Why oracle optimizer is not able to pick the optimal execution plan at the first place. But there are always cases when optimizer can't simply pick right driving table, or joing order or index due to various constraints. In my case, I was dealing with nearly 2000 lines of code, with 40 WITH clause subquery blocks and each subquery had 14 tables getting joined together. Due to business urgency, code change was not an option. And in this case, first thing you need to make sure that your stats are accurate, you have histograms on the columns containing skewed data and you have extended stats on the columns combinations determined by dbms_stats.seed_col_usage() procedure and you have selective indexes.

Even after that, with large number of tables and temporary table transformation due to query sub factoring, optimizer may end up picking undesired driving table or unselective index. And things become more worse when due to present of hierarchies, optimizer has to do cardinality guess which doesn't fit accurate. I will discuss on these topics in more detail in separate blogs.

So challenge here was to determine query blocks where optimizer didn't pick right driving table and ended up in doing partition range all. Thankfully, you can use format=>OUTLINE in dbms_xplan.display method to see the optimizer outline hints. From there you can check for LEADING hint and find out which block is not using desired driving table in LEADING hint. Once you have these, you can arrange your tables as per the desired format in LEADING hint and all you have to do is to create a SQL profile or patch.  Here is the code I used to tune required queries -

DECLARE
    sql_text clob        ;                  
    description varchar2(1000):='Temporary sql patch for BI report';
    name varchar2(1000)       :='Sql_Patch';
    output   varchar2(1000);
    sqlpro_attr SYS.SQLPROF_ATTR;
    l_count number:=0;
begin

select outline_hints
bulk collect
into
sqlpro_attr
from
(select 'LEADING(@"SEL$1" "T597999"@"SEL$1" "T1841902"@"SEL$1" "T543491"@"SEL$1" "T572337"@"SEL$1" "T1841904"@"SEL$1" "T547300"@"SEL$1")' as outline_hints from dual
union all
select 'LEADING(@"SEL$2" "T597999"@"SEL$2" "T1841902"@"SEL$2" "T543491"@"SEL$2" "T572337"@"SEL$2" "T1841904"@"SEL$2" "T547300"@"SEL$2")' as outline_hints from dual
union all
select 'LEADING(@"SEL$3" "T597999"@"SEL$3" "T1841902"@"SEL$3" "T543491"@"SEL$3" "T572337"@"SEL$3" "T1841904"@"SEL$3" "T547300"@"SEL$3")' as outline_hints from dual
union all
select 'LEADING(@"SEL$4" "T597999"@"SEL$4" "T1841902"@"SEL$4" "T543491"@"SEL$4" "T572337"@"SEL$4" "T1841904"@"SEL$4" "T547300"@"SEL$4")' as outline_hints from dual);

for l1 in
(select a.sql_id, c.sql_text, a.FORCE_MATCHING_SIGNATURE from sys.wrh$_sqlstat A, dba_hist_snapshot B, sys.wrh$_sqltext C
       where B.BEGIN_INTERVAL_TIME >= trunc(sysdate) - 7 AND
             A.SNAP_ID = B.SNAP_ID and A.instance_number = B.instance_number and
             A.PARSING_SCHEMA_NAME='XXRPT_READONLY' and
             C.SQL_ID=A.SQL_ID and
             ((ROUND(((a.ELAPSED_TIME_DELTA/1000000)/a.EXECUTIONS_DELTA),5) > 60 and a.EXECUTIONS_DELTA <> 0) OR
              (ROUND((a.ELAPSED_TIME_DELTA/1000000),5) > 60 and a.EXECUTIONS_DELTA = 0))
             ) loop
if DBMS_LOB.INSTR(l1.SQL_TEXT, 'T547300.ACCT_PERIOD_END_DT_WID = 10120140831000.0', 1, 1) > 0 or
   DBMS_LOB.INSTR(l1.SQL_TEXT, 't547300.acct_period_end_dt_wid = t597999.row_wid', 1, 1) > 0 or
   DBMS_LOB.INSTR(l1.SQL_TEXT, 't547300.acct_period_end_dt_wid = 10120140831000.0', 1, 1) > 0 or
   DBMS_LOB.INSTR(l1.SQL_TEXT, 'T547300.ACCT_PERIOD_END_DT_WID = T597999.ROW_WID', 1, 1) > 0
 then
      dbms_output.put_line(l1.sql_id ||'-'||  l1.FORCE_MATCHING_SIGNATURE);
    begin
      l_count:= l_count + 1;
      output      := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
      SQL_TEXT => l1.sql_text,
      PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(sqlpro_attr),
      NAME => name || l_count,      
      DESCRIPTION => description,    
      CATEGORY => 'DEFAULT',
      CREATOR => 'SYS',
      VALIDATE => TRUE,
      TYPE => 'PATCH',
      FORCE_MATCH => TRUE,
      IS_PATCH => TRUE);
     dbms_output.put_line(output);
   exception when others then
    dbms_output.put_line('SQLERRM-' || sqlerrm);
   end;
end if;
end loop;
end;
/

In the above code, I am passing the collection of hints in  sqlpro_attr which is collection type , converting the set of hints into XML format and passing it into DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE.

For finding the problematic SQL I am using dbms_lob.instr to check for matching text having elapsed time more than 60 seconds and creating SQL Profile on the text. As you can see that there are four subquery blocks (SEL$1, SEL$2,SEL$3,SEL$4) resulted in poor performance, so all my LEADING hints are referring to those blocks with the desired order of tables to be joined.

And thats it, so no matter how big the query is, as long as you know the optimal join order and subquery block having issues, you can pass as many hints using the method I mentioned above to create SQL profiles on hinted SQL Text.

Let me know your thoughts or if you have any questions.