Saturday, November 16, 2013

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

How to swap the stored outline:

Bad query example: 
select first_name,last_name from hr.employees where employee_id=:id;
Good query example:
select /*+ FULL(@"SEL$1" "EMPLOYEES"@"SEL$1") */ first_name,last_name from hr.employees where employee_id=:id;

Question: How to swap the stored outline to make sure bad query using explain plan which is currently used by good query?

Step 1: find the hash value:

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%';

Step 2: create stored outline for both query

declare
v_hash_value number := 2050501980;
v_child_number number := 0;
begin
dbms_outln.create_outline(v_hash_value,v_child_number,'DEFAULT');
end;
/
declare
v_hash_value number := 1505724922;
v_child_number number := 0;
begin
dbms_outln.create_outline(v_hash_value,v_child_number,'DEFAULT');
end;
/

-- rename to user friendly username per output from dba_stored_outlines;
alter outline SYS_OUTLINE_13111501234368503 rename to EMP_QUERY_BY_ID_INDEX;
alter outline SYS_OUTLINE_13111501235747604 rename to EMP_QUERY_BY_ID_FULL;

Step 3: Swap the hints and hintcount for both stored outlines:

update outln.ol$hints set ol_name=decode(ol_name,'EMP_QUERY_BY_ID_INDEX','EMP_QUERY_BY_ID_FULL','EMP_QUERY_BY_ID_FULL','EMP_QUERY_BY_ID_INDEX')
where ol_name in ('EMP_QUERY_BY_ID_INDEX','EMP_QUERY_BY_ID_FULL')

update outln.ol$ set hintcount=decode(hintcount,6,6,6,6)
where ol_name in ('EMP_QUERY_BY_ID_INDEX','EMP_QUERY_BY_ID_FULL');

commit;

Step 4: Verify it works

alter session set use_stored_outlines=true;