Use hints for SQL query with SQL Plan Baseline in Oracle

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);

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.