Without changing the application code use hints for SQL with SQL Plan Baseline

Example of using SQL Plan baseline to change execution plan for the Query:

1. Create the object used in testing

SQL> create table object_test as select object_id from dba_objects;
Table created.

SQL> CREATE INDEX idx_object_test ON object_test(object_id);
Index created.

2. Check the execution plan of the SQL Query:

SQL> set autotrace traceonly
SQL> set line 200 pages 200
SQL> select * from object_test where object_id >= 100;

73322 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2586440291
------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |   457 |  5941 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OBJECT_TEST |   457 |  5941 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID">=100)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5042  consistent gets
        163  physical reads
          0  redo size
    1378239  bytes sent via SQL*Net to client
      54391  bytes received via SQL*Net from client
       4890  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73322  rows processed

3. Check the SQL ID and load the plan in SQL BASELINE

SELECT sql_id, sql_fulltext FROM v$sql WHERE  sql_text LIKE 'select * from object_test%';

SQL_ID        SQL_FULLTEXT
------------- -------------------------------------------------
7zbd2fs08473m select * from object_test where object_id >= 100



----- Load the SQL plan in SQL Baseline:
DECLARE
   cnt NUMBER;
BEGIN
   cnt := sys.dbms_spm.load_plans_from_cursor_cache(sql_id=>'7zbd2fs08473m');
END;
/

4. Check the SQL Baseline view for plan:

col sql_handle for a20
col plan_name for a30
col enabled for a5
col sql_text for a50
SELECT b.sql_handle, b.plan_name, b.enabled,b.sql_text
FROM   dba_sql_plan_baselines b, v$sql s
WHERE  s.sql_id='7zbd2fs08473m'
AND    s.exact_matching_signature = b.signature;
 
 
SQL_HANDLE           PLAN_NAME                      ENABL SQL_TEXT
-------------------- ------------------------------ ----- --------------------------------------------------
SQL_22d1e2cc48a12c25 SQL_PLAN_25ng2tj4a2b15018d1b45 YES   select * from object_test where object_id >= 100

5. Disable the current plan so that we enable anther one

-- Disable the plan

DECLARE
  cnt NUMBER;
BEGIN
  cnt := sys.dbms_spm.alter_sql_plan_baseline(
               sql_handle=>'SQL_22d1e2cc48a12c25', 
               plan_name=>'SQL_PLAN_25ng2tj4a2b15018d1b45', 
               attribute_name=>'enabled', attribute_value=>'NO');
END;
/

6. Generate the plan with hint which you want to use:

SQL>set autotrace traceonly
SQL> select /*+NO_INDEX(object_test) */ * from object_test where object_id >= 100;
73322 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3385093125

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   457 |  5941 |    32   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| OBJECT_TEST |   457 |  5941 |    32   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID">=100)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4997  consistent gets
          0  physical reads
          0  redo size
    1378239  bytes sent via SQL*Net to client
      54391  bytes received via SQL*Net from client
       4890  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73322  rows processed
	  


--- Check the SQL ID 
SELECT sql_id, plan_hash_value, sql_fulltext FROM  v$sql WHERE  sql_text LIKE 'select /*+NO_INDEX(object_test)%';

SQL_ID        PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- --------------------------------------------------------------------------------
asbdug5sb5szb      3385093125 select /*+NO_INDEX(object_test) */ * from object_test where object_id >= 100

7. Use the hint plan for the above SQL statement by adding the plan in SQL plan baseline with using sql handle: SQL_22d1e2cc48a12c25

DECLARE
 cnt NUMBER;
BEGIN
 cnt := sys.dbms_spm.load_plans_from_cursor_cache(
               sql_id=>'asbdug5sb5szb', 
               plan_hash_value=>'3385093125', 
               sql_handle=>'SQL_22d1e2cc48a12c25');
END;
/

-- Verified that plan is added and enabled:

SELECT b.sql_handle,  b.plan_name, b.enabled --,b.sql_text
FROM   dba_sql_plan_baselines b, v$sql s
WHERE  s.sql_id='7zbd2fs08473m'
AND    s.exact_matching_signature = b.signature;

SQL_HANDLE           PLAN_NAME                      ENABL
-------------------- ------------------------------ -----
SQL_22d1e2cc48a12c25 SQL_PLAN_25ng2tj4a2b15018d1b45 NO
SQL_22d1e2cc48a12c25 SQL_PLAN_25ng2tj4a2b15c59f2783 YES

8. Test the query:

SQL> select * from object_test where object_id >= 100
73322 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3385093125

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   457 |  5941 |    32   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| OBJECT_TEST |   457 |  5941 |    32   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID">=100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4997  consistent gets
          0  physical reads
          0  redo size
    1378239  bytes sent via SQL*Net to client
      54391  bytes received via SQL*Net from client
       4890  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73322  rows processed
	  
Note
-----
   - SQL plan baseline "SQL_PLAN_25ng2tj4a2b15c59f2783" used FOR this statement

9. Drop the SQL plan baseline if not needed:

set serveroutput on
var res number
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_22d1e2cc48a12c25','SQL_PLAN_25ng2tj4a2b15018d1b45');
exec dbms_output.put_line('Number of plans dropped: ' || :res);