Share This:

If you have SQL statements that are running poorly in an Oracle 12c database and their Explain Plans are showing "... SQL Plan Directive used for this statement" under "Note" you may want to look into turning off the SQL Plan Directive (SPD) and checking performance after.

 

At a recent customer site it was noticed that the SQL below (truncated here) had an Execution Plan, also shown below, that was extremely sub-optimal with a Cost in excess of 7.5 BILLION.

 

QUERY

SELECT b1.C1 || '|' || NVL(b2.C1, ''), b1.C2, b1.C3, b1.C4, b1.C5, b1.C6, b2.C7, b1.C8, b2.C260100001, b1.C301019600, b1.C260100002, b2.C260100004, b2.C260100006, b2.C260100007, b2.C260100009, b2.C260100010, b2.C260100015, b2.C230000009, b2.C263000050, . . . b1.C530014300, b1.C530010200, b2.C810000272, b1.E0, b1.E1, b2.C1

FROM T525 b1 LEFT JOIN T3973 b2 ON ((b1.C400129200 = b2.C400129200) OR (b2.C400129200 = b1.C179))

 

EXECUTION PLAN

Plan hash value: 3755947183

 

--------------------------------------------------------------------------------------------------

| Id | Operation                 | Name            | Rows   | Bytes |TempSpc| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

| 0  | SELECT STATEMENT          |                 | 197K   | 2068M |       | 7929M (1)  | 86:02:37 |

| 1  |  NESTED LOOPS OUTER       |                 | 197K   | 2068M |       | 7929M (1)  | 86:02:37 |

|* 2 |   HASH JOIN               |                 | 98925  | 72M   | 24M   | 163K (1)   | 00:00:07 |

| 3  |    TABLE ACCESS FULL      | T457            | 98357  | 23M   |       | 1011 (1)   | 00:00:01 |

| 4  |    TABLE ACCESS FULL      | T476            | 3528K  | 1722M |       | 73424 (1)  | 00:00:03 |

| 5  |   VIEW                    | VW_LAT_F1632550 | 2      | 20394 |       | 80158 (1)  | 00:00:04 |

|* 6 |    TABLE ACCESS FULL      | T3973           | 2      | 1138  |       | 80158 (1)  | 00:00:04 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------- 

2 - access("B1"."C179"="B2"."C179")

6 - filter("B2"."C400129200"="B2"."C400129200" OR "B2"."C400129200"="B2"."C179")

 

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

  - 1 Sql Plan Directive used for this statement

 

The same SQL in a few other environments (customer's and BMC's internal ones) showed a better plan that used indexes and ran much faster.

 

The "good" plans were missing the "Note" above that mentions (a) dynamic sampling used and (b) 1 SQL Plan Directive used for the SQL.

 

This indicated that it was possibly the SQL Plan Directive that was responsible for the poor Execution Plan.

 

We checked the database for SPDs associated with the 2 objects above (T525 and T3973) and found that there were 4 table-level directives.

 

SQL> select * from dba_sql_plan_dir_objects where owner = 'ARADMIN' and object_name = 'T3973';

 

DIRECTIVE_ID        OWNER   OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE    NOTES

2625473566913189414 ARADMIN T3973       C400129200     COLUMN

9853893946733075077 ARADMIN T3973       C7             COLUMN

9853893946733075077 ARADMIN T3973                      TABLE          <obj_note><equality_predicates_only>YES</equality_predicates_only>

                                                                      <simple_column_predicates_only>YES</simple_column_predicates_only>

                                                                      <index_access_by_join_predicates>NO</index_access_by_join_predicates>

                                                                      <filter_on_joining_object>NO</filter_on_joining_object></obj_note>

6009259810806618512 ARADMIN T3973                      TABLE          <obj_note><equality_predicates_only>NO</equality_predicates_only>

                                                                      <simple_column_predicates_only>NO</simple_column_predicates_only>

                                                                      <index_access_by_join_predicates>NO</index_access_by_join_predicates>

                                                                      <filter_on_joining_object>YES</filter_on_joining_object></obj_note>

2625473566913189414 ARADMIN T3973                      TABLE          <obj_note><equality_predicates_only>YES</equality_predicates_only>

                                                                      <simple_column_predicates_only>YES</simple_column_predicates_only>

                                                                      <index_access_by_join_predicates>NO</index_access_by_join_predicates>

                                                                      <filter_on_joining_object>NO</filter_on_joining_object></obj_note>

3274712412944615867 ARADMIN T3973                      TABLE          <obj_note><equality_predicates_only>NO</equality_predicates_only>

                                                                      <simple_column_predicates_only>NO</simple_column_predicates_only>

                                                                      <index_access_by_join_predicates>NO</index_access_by_join_predicates>

                                                                      <filter_on_joining_object>NO</filter_on_joining_object></obj_note>

 

We the disabled the SPDs using the following commands (the long number in each command is the SQL Directive Id from above):

 

      exec dbms_spd.alter_sql_plan_directive(9853893946733075077,'ENABLED','NO');

   exec dbms_spd.alter_sql_plan_directive(6009259810806618512,'ENABLED','NO');

   exec dbms_spd.alter_sql_plan_directive(3274712412944615867,'ENABLED','NO');

   exec dbms_spd.alter_sql_plan_directive(2625473566913189414,'ENABLED','NO');

 

The result was the Execution Plan that was faster and  which was observed in all the other environments.

 

--------------------------------------------------------------------------------------------------------------------

| Id | Operation                                | Name            | Rows  | Bytes |TempSpc | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------------------

| 0  | SELECT STATEMENT                         |                 | 197K  | 2068M |        | 756K (1)   | 00:00:30 |

| 1  | NESTED LOOPS OUTER                       |                 | 197K  | 2068M |        | 756K (1)   | 00:00:30 |

|* 2 |  HASH JOIN                               |                 | 98925 | 72M   | 24M    | 163K (1)   | 00:00:07 |

| 3  |   TABLE ACCESS FULL                      | T457            | 98357 | 23M   |        | 1011 (1)   | 00:00:01 |

| 4  |   TABLE ACCESS FULL                      | T476            | 3528K | 1722M |        | 73424 (1)  | 00:00:03 |

| 5  |  VIEW                                    | VW_LAT_F1632550 | 2     | 20394 |        | 6 (0)      | 00:00:01 |

| 6  |  CONCATENATION                           |                 |       |       |        |            |          |

| 7  |    TABLE ACCESS BY INDEX ROWID BATCHED   | T3973           | 1     | 569   |        | 3 (0)      | 00:00:01 |

|* 8 |     INDEX RANGE SCAN | I3973_400129200_1 | 1               |       |       |        | 2 (0)      | 00:00:01 |

| 9  |    TABLE ACCESS BY INDEX ROWID BATCHED   | T3973           | 1     | 569   |        | 3 (0)      | 00:00:01 |

|* 10|     INDEX RANGE SCAN | I3973_400129200_1 | 1               |       |       |        | 2 (0)      | 00:00:01 |

--------------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

--------------------------------------------------- 

  2 - access("B1"."C179"="B2"."C179")

  8 - access("B2"."C400129200"="B2"."C179")

  10 - access("B2"."C400129200"="B2"."C400129200")

       filter(LNNVL("B2"."C400129200"="B2"."C179"))

 

As one can see there are no longer any SPDs.

 

There are numerous ways to not have SQL Plan Directives affect query performance.

 

Database Level: (a) set optimizer_features_enable = '11.2.0.4'.

                    NOTE: This will disable ALL 12c optimizer features

                (b) set optimizer_adaptive_features = FALSE.

                    NOTE: This disables ALL 12c adaptive features and

                          that may be too wide an option

 

SQL Directive Level: exec dbms_spd.alter_sql_plan_directive(<insert directive_id here>,

                     ’ENABLED’,’NO’);