How to find minimal set of hints leading to better execution plan?

Dealing with query performance degradation coming from suboptimal execution plans is a task every DBA knows. Thanks to very good Oracle instrumentation, we have several options to deal with the problem, both in terms of analysis, as well as introducing the correct solution. If you are able to get the proper plan for example from DBA_HIST_SQL_PLAN or from different “versions” of the same database available in various types of environments (DEV, TEST, etc.), your life should be much easier.

I experienced such a situation recently, when the query in PREPROD environment, was using much less resource-intensive plan, comparing to the one in PROD. As a temporary workaround, I wanted to introduce this better plan in PROD. It was not possible to use baselines in this case, so I decided to try using SQL patch. The main problem was – how to find minimal set of hints leading to better execution plan?

Please find below example query, I prepared to show how easy it is, when you already have access to “bad” and “good” plan. To make things more interesting – the query is joining table with the view, which is further joining few other tables.

select e.dep, e.full_name
from t_dep d
inner join v_emp e on d.dep = e.dep
where d.bunit = 12
and (e.is_senior = 1 or e.is_manager = 1)
and e.building = 'HQ';

Bad plan looked like below:

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                  |       |       |   243 (100)|          |
|   1 |  NESTED LOOPS OUTER                      |                  |    31 |  2310 |   243   (4)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                     |                  |    27 |  1593 |   243   (4)| 00:00:01 |
|*  3 |    HASH JOIN                             |                  |    27 |  1296 |   243   (4)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                    | EMP_DATA         |    99 |  1696 |   231   (4)| 00:00:01 |
|   5 |     NESTED LOOPS                         |                  |  1356 | 42036 |    12   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                        |                  |  1356 | 42036 |    12   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_DEP            |     6 |   100 |     6   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | T_DEP_PK         |     6 |       |     5   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN                  | EMP_PK           |     1 |       |     0   (0)|          |
|  10 |      TABLE ACCESS BY INDEX ROWID         | EMP              |   249 |  4233 |     1   (0)| 00:00:01 |
|* 11 |    INDEX UNIQUE SCAN                     | EMP_INFO_PK      |     1 |    12 |     0   (0)|          |
|* 12 |   INDEX UNIQUE SCAN                      | EMP_INFO_PK      |     1 |    12 |     0   (0)|          |
-------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
      11039  recursive calls
          0  db block gets
      35422  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
      11035  sorts (memory)
          0  sorts (disk)
          4  rows processed

Better plan was not so different in terms of operations, but runtime statistics showed significantly lower resource consumption:

------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                  |       |       |  1368 (100)|          |
|   1 |  NESTED LOOPS OUTER                     |                  |    31 |  2310 |  1368   (2)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                    |                  |    27 |  1593 |  1368   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                  |    27 |  1296 |  1368   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                  |  1356 | 42036 |    12   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_DEP            |     6 |   100 |     6   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | T_DEP_PK         |     6 |       |     5   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID        | EMP              |   249 |  4233 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN                 | EMP_PK           |     1 |       |     0   (0)|          |
|*  9 |     TABLE ACCESS BY INDEX ROWID         | EMP_DATA         |     1 |    18 |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN                  | EMP_DATA_PK      |     1 |       |     0   (0)|          |
|* 11 |    INDEX UNIQUE SCAN                    | EMP_INFO_PK      |     1 |    12 |     0   (0)|          |
|* 12 |   INDEX UNIQUE SCAN                     | EMP_INFO_PK      |     1 |    12 |     0   (0)|          |
------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         57  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          4  rows processed

Because I had access to both plans, I was able to get the Outline Data of the better one by running:

select * from table(dbms_xplan.display_awr('6w0sxjf88xhyu', 1530982804, format => 'ADVANCED'));

In my case it looked like:

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
(... list of OPT_PARAMs here... )
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$T3R1DE33")
      MERGE(@"SEL$39847920")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$39847920")
      MERGE(@"SEL$D43SW882")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$D43SW882")
      MERGE(@"SEL$88EDW7D2")
      OUTLINE(@"SEL$EE4HG838")
      ANSI_REARCH(@"SEL$6")
      OUTLINE(@"SEL$88EDW7D2")
      MERGE(@"SEL$WE3DK302")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$64S3KSM3")
      ANSI_REARCH(@"SEL$3DD983S2")
      OUTLINE(@"SEL$WE3DK302")
      MERGE(@"SEL$23D39DK2")
      OUTLINE(@"SEL$3DD983S2")
      ANSI_REARCH(@"SEL$5")
      OUTLINE(@"SEL$4D22KD92")
      ANSI_REARCH(@"SEL$28DKE99A")
      OUTLINE(@"SEL$23D39DK2")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$28DKE99A")
      ANSI_REARCH(@"SEL$4")
      OUTLINE(@"SEL$32D7DK21")
      ANSI_REARCH(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      INDEX_RS_ASC(@"SEL$T3R1DE33" "D"@"SEL$1" ("T_DEP"."BUNIT" "T_DEP"."UN"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$T3R1DE33" "D"@"SEL$1")
      INDEX_RS_ASC(@"SEL$T3R1DE33" "E"@"SEL$2" ("EMP"."DEP"))
      INDEX_RS_ASC(@"SEL$T3R1DE33" "ED"@"SEL$2" ("EMP_DATA"."DEP"))
      INDEX(@"SEL$T3R1DE33" "EL"@"SEL$5" ("EMP_LEVEL"."ID"))
      INDEX(@"SEL$T3R1DE33" "EI"@"SEL$3" ("EMP_INFO"."DEP" "EMP_INFO"."DEP_UNIT"))
      INDEX(@"SEL$T3R1DE33" "EI1"@"SEL$4" ("EMP_INFO"."DEP" "EMP_INFO"."DEP_UNIT"))
      LEADING(@"SEL$T3R1DE33" "D"@"SEL$1" "E"@"SEL$2" "ED"@"SEL$2" "EL"@"SEL$5" "EI"@"SEL$3"
              "EI1"@"SEL$4")
      USE_NL(@"SEL$T3R1DE33" "E"@"SEL$2")
      USE_NL(@"SEL$T3R1DE33" "ED"@"SEL$2")
      USE_NL(@"SEL$T3R1DE33" "EL"@"SEL$5")
      USE_NL(@"SEL$T3R1DE33" "EI"@"SEL$3")
      USE_NL(@"SEL$T3R1DE33" "EI1"@"SEL$4")
      END_OUTLINE_DATA
  */

At first sight, it looks a little bit complicated, but in principle if you add complete Outline Data to your query, you should get exactly the plan you were looking for.

select /*+ ...full outline here...*/ e.dep, e.full_name
from t_dep d
inner join v_emp e on d.dep = e.dep
where d.bunit = 12
and (e.is_senior = 1 or e.is_manager = 1)
and e.building = 'HQ';

Of course creating SQL patch with full Outline Data would be a little bit messy ;), so the most difficult part is to analyze it to end up with the minimal set of hints needed to enforce better plan. In this case it was even more difficult, as there was a view involved. To change access path of the object referenced in the view (but not directly in query text), you need to hint the view. For testing purposes, you can use WITH SQL clause, because this allows you to change view definition only in your session (and is faster than recreating the view each time).

with empview as (
select /*+ use_nl(ed) */ e.dep,
         e.full_name,
... -- the rest of view definition is here
)
select /*+ leading(d e) */ e.dep, e.full_name
from t_dep d
inner join v_emp e on d.dep = e.dep
where d.bunit = 12
and (e.is_senior = 1 or e.is_manager = 1)
and e.building = 'HQ';

This allowed to get better plan, but because I wanted to apply SQL patch without changing view definition, I needed a way to put set of hints only in query text. Query block is the feature one can use in such cases. To get query block names generated for the query, again output of DBMS_XPLAN needs to be checked:

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

   1 - SEL$T3R1DE33
   4 - SEL$T3R1DE33 / ED@SEL$2
   7 - SEL$T3R1DE33 / D@SEL$1
   8 - SEL$T3R1DE33 / D@SEL$1
   9 - SEL$T3R1DE33 / E@SEL$2
  10 - SEL$T3R1DE33 / E@SEL$2
  11 - SEL$T3R1DE33 / EI@SEL$3
  12 - SEL$T3R1DE33 / EI1@SEL$4

Using above information, I was able to reference object using ED alias in the view definition, directly in the query text, as below:

select /*+ leading(d e) use_nl(@SEL$T3R1DE33 ED@SEL$2) */ e.dep, e.full_name
from t_dep d
inner join v_emp e on d.dep = e.dep
where d.bunit = 12
and (e.is_senior = 1 or e.is_manager = 1)
and e.building = 'HQ';

The only thing left was to create SQL patch:

declare
   v_sql clob;
begin
   select sql_text into v_sql from dba_hist_sqltext where sql_id='6w0sxjf88xhyu';
   sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => v_sql,
      hint_text => 'leading(d e) use_nl(@SEL$T3R1DE33 ED@SEL$2)',
      name      => 'T_DEP_V_EMP SQL patch');
end;
/

SQL patch is very useful feature, allowing DBAs to change SQL statements without changing application code, but one should not forget that it is always a workaround, even though this was made more “official” in Oracle 12cR2 with new public API and DBMS_SQLDIAG.CREATE_SQL_PATCH function.

Advertisements

4 thoughts on “How to find minimal set of hints leading to better execution plan?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s