There are few situations where we know the execution plan of a particular query can be improved with use of proper hint, But if we are not allowed to change the application query in any way(we can’t even add a hint also),then how to achive this?

Example:

For the below query, we want to force it to use parallel 4 Hint execution plan, But without changing the query.

select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);

09:33:13 select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

  13899072

Elapsed: 00:00:17.29

 select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID  g5ch3zx56jawc, child number 1
-------------------------------------
select count(*) from bigtab where weight in ( select distinct( weight)
from bigtab)

Plan hash value: 1484132691

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |       |       |       | 50544 (100)|          |
|   1 |  SORT AGGREGATE     |        |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN SEMI    |        |    10M|   258M|   248M| 50544   (1)| 00:10:07 |
|   3 |    TABLE ACCESS FULL| BIGTAB |    10M|   129M|       | 12861   (1)| 00:02:35 |
|   4 |    TABLE ACCESS FULL| BIGTAB |    10M|   129M|       | 12861   (1)| 00:02:35 |
--------------------------------------------------------------------------------------

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

   2 - access("WEIGHT"="WEIGHT")

Note
-----
   - dynamic sampling used for this statement (level=2)


26 rows selected.

Elapsed: 00:00:00.03

Currently the query is taking around 17 seconds.
Lets create a baseline for this query.

SQL > variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'g5ch3zx56jawc');09:37:49 SQL >

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05


SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:37:35   2  09:37:35   3  09:37:35   4  09:37:35   5


09:38:02 SQL > 09:38:02   2  09:38:02   3  09:38:02   4  09:38:02   5
SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxd856ccc5f YES
                               tab)

SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxdc33b09c9 YES
                               tab)


Elapsed: 00:00:00.01

We can see two plans got loaded. Lets disable these existing plans.

09:41:16 SQL > exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =  > 'SQL_a868c91ef7af57ad',-
                                              plan_name = >'SQL_PLAN_ahu693vvuypxd856ccc5f',-
                                              attribute_name= >'enabled',-
                                              attribute_value= >'NO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
09:42:49 SQL > exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle = >'SQL_a868c91ef7af57ad',-
                                              plan_name = > 'SQL_PLAN_ahu693vvuypxdc33b09c9',-
                                              attribute_name= > 'enabled',-
                                              attribute_value= >'NO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
09:43:02 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:43:05   2  09:43:05   3  09:43:05   4  09:43:05   5

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
                               tab)

SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
                               tab)


Elapsed: 00:00:00.01

Lets run the the same query with parallel hint and get the execution plan.

09:33:37 SQL > select /*+ parallel (4) */ count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);
  13899072

Elapsed: 00:00:07.41
09:33:57 SQL > select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID  gq322uk9152yg, child number 0
-------------------------------------
select /*+ parallel (4) */ count(*) from bigtab where weight in (
select distinct( weight)  from bigtab)

Plan hash value: 1244248794

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       |       |       | 13342 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |    26 |       |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    26 |       |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |    26 |       |            |          |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN SEMI       |          |    10M|   258M|    62M| 13342   (1)| 00:02:41 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL| BIGTAB   |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,01 | PCWC |            |
|* 13 |          TABLE ACCESS FULL| BIGTAB   |    10M|   129M|       |  3568   (1)| 00:00:43 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

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

   5 - access("WEIGHT"="WEIGHT")
   9 - access(:Z>=:Z AND :Z<=:Z)   13 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 4 because of hint


38 rows selected.

Elapsed: 00:00:00.02

So for the query with parallel hint, you can get these two details from the above execution plan.

SQL ID: gq322uk9152yg
Plan hash value: 1244248794

Now load these two values to the existing sql handle id SQL_a868c91ef7af57ad

09:43:02 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:43:05   2  09:43:05   3  09:43:05   4  09:43:05   5

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
                               tab)

SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
                               tab)


Elapsed: 00:00:00.01



09:47:45 SQL > execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id = > 'gq322uk9152yg',-
                                                      plan_hash_value => 1244248794,-
                                                      sql_handle=>'SQL_a868c91ef7af57ad');
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06



09:50:43 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:50:49   2  09:50:49   3  09:50:49   4  09:50:49   5

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxd6e73d422 YES
                               tab)

SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
                               tab)

SQL_a868c91ef7af57ad           select count(*) from bigtab where weight in ( select distinct( weight)  from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
                               tab)


Elapsed: 00:00:00.01

We can see a new plan has been enabled for this sql. This is the parallel hinted plan.

Lets check the explain plan of the orginal query and verify whether it is using the parallel hinted execution plan or not.

09:50:49 SQL > select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

  COUNT(*)
----------
  13899072

Elapsed: 00:00:10.98
09:51:25 SQL > explain plan for
09:51:32   2   select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

Explained.

Elapsed: 00:00:00.03
09:51:35 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));09:51:47   2

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
Plan hash value: 1244248794

----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 | 00:00:15 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |          |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN SEMI       |          |  3847K| 00:00:15 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          |  3847K| 00:00:08 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 |  3847K| 00:00:08 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          |  3847K| 00:00:08 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| BIGTAB   |  3847K| 00:00:08 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          |  3847K| 00:00:08 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 |  3847K| 00:00:08 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          |  3847K| 00:00:08 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| BIGTAB   |  3847K| 00:00:08 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------

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

   5 - access("WEIGHT"="WEIGHT")

Note
-----
   - Degree of Parallelism is 4 because of hint
   - SQL plan baseline "SQL_PLAN_ahu693vvuypxd6e73d422" used for this statement

30 rows selected.

Elapsed: 00:00:00.03

Now it is using the parallel hint execution plan, even if we haven’t used that hint in the query.