Friday, August 16, 2013

Database -- How to force a sql statement to be hard parsed

Forcing a sql to be hard parsed everytime.. Unlike MS SQL Server, there is no direct Oracle function to do this...

There are some ways to make Oracle to hard parse a sql everytime it runs. These ways like changing the stats etc.. are inconvenient. Adaptive cursor sharing can deal with it, but it s a 11g feature.

The most applicable solution will be Oracle RLS..

By using Oracle RLS, the queries can be modified on the fly, so indirectly make Oracle to hard parse them..


Demo: -- Reference: http://oracle-randolf.blogspot.comhttp://dioncho.wordpress.com
create table t1 as select * from all_objects where rownum <= 1000; 
exec dbms_stats.gather_table_stats(user, 't1'); drop sequence s1; 
create sequence s1 cache 1000; 
create or replace package pkg_rls_hard_parse 
is 
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2; 
end pkg_rls_hard_parse;
 / 
create or replace package body pkg_rls_hard_parse is 
function force_hard_parse (in_schema varchar2, in_object varchar2) 
return varchar2 
is 
s_predicate varchar2(100); 
n_random pls_integer; 
begin 
select s1.nextval || ' = ' || s1.currval into s_predicate from dual; 
return s_predicate; 
end force_hard_parse; 
end pkg_rls_hard_parse;
exec dbms_rls.add_policy (user, 't1', 'hard_parse_policy', user, -'pkg_rls_hard_parse.force_hard_parse', 'select');

2 comments :

  1. thx sir but

    why we need to do that ?

    ReplyDelete
  2. This may be needed so rarely. in case where you want to say the optimizer something like "peek the bind variables again in every execution"
    Consider the cases where you have skewed data. I mean the cases where your shared sql (explain plan) doesn't perform good everytime /for every execution.

    ReplyDelete