How to change the execution plan if you can not change the SQL (Part 1)

Before Change:

SQL> select first_name,last_name from hr.employees where employee_id=:id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    19 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    19 |     0   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=TO_NUMBER(:ID))

After Change:

SQL> select first_name,last_name from hr.employees where employee_id=:id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    19 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    19 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPLOYEE_ID"=TO_NUMBER(:ID))
Note
-----
   - outline "EMP_QUERY_BY_ID" used for this statement

Steps to create/edit the stored outline:

1. Get the SQL_ID, child_number, hash_value from cursor

select sql_id,child_number,hash_value, sql_text from v$sql where sql_text like 'select %first_name,last_name from hr.employees%'and sql_text not like '%sql_id%';

2. Create stored outline (this procedure works from 10g onwards)

declare

v_hash_value number := 1505724922;

v_child_number number := 0;

begin

dbms_outln.create_outline(v_hash_value,v_child_number,'DEFAULT');

end;

/

select * from dba_outlines;

-- Rename the outline to user friendly name

alter outline SYS_OUTLINE_13111421182148801 rename to EMP_QUERY_BY_ID;

3. Create private stored outline

create private outline PRIV_EMP_QUERY_BY_ID from EMP_QUERY_BY_ID;

4. Edit the hints

select * from ol$hints;

-- Original hint value is INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))

update ol$hints set hint_text='FULL(@"SEL$1" "EMPLOYEES"@"SEL$1") ' where hint#=6 and ol_name='EMP_QUERY_BY_ID';

commit;

execute dbms_outln_edit.refresh_private_outline('PRIV_EMP_QUERY_BY_ID');

5. Test the private stored outline

alter session set use_private_outlines=true;

alter session set use_stored_outlines=false;

-- necessary testing and plan verification here

6. Publish the stored outline

create or replace outline EMP_QUERY_BY_ID from private PRIV_EMP_QUERY_BY_ID;

alter session set use_stored_outlines=true;

alter session set use_private_outlines=false;
drop private outline PRIV_EMP_QUERY_BY_ID;