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.com, http://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');