Is there a hint to generate execution plan ignoring the existing one from shared pool?

Question

Is there a hint to generate execution plan ignoring the existing one from the shared pool?

Answer 1

I think there is no hint indicating that Oracle shall find a new execution plan everytime it runs the query.

This is something we'd want for select * from mytable where is_active = :active, with is_active being 1 for very few rows and 0 for maybe billions of other rows. We'd want an index access for :active = 1 and a full table scan for :active = 0 then. Two different plans.

As far as I know, Oracle uses bind variable peeking in later versions, so with a look at the statistics it really comes up with different execution plans for different bind varibale content. But in older versions it did not, and thus we'd want some hint saying "make a new plan" there.

Oracle only re-used an execution plan for exactly the same query. It sufficed to add a mere blank to get a new plan. Hence a solution might be to generate the query everytime you want to run it with a random number included in a comment:

select /* 1234567 */ * from mytable where is_active = :active;

Or just don't use bind variables, if this is the problem you want to address:

select * from mytable where is_active = 0;

select * from mytable where is_active = 1;

Answer 2

There is not a hint to create an execution plan that ignores plans in the shared pool. A more common way of phrasing this question is: how do I get Oracle to always perform a hard parse?

There are a few weird situations where this behavior is required. It would be helpful to fully explain your reason for needing this, as the solution varies depending why you need it.

1. Strange performance problem. Oracle performs some dynamic re-optimization of SQL statements after the first run, like adaptive cursor sharing and cardinality feedback. In the rare case when those features backfire you might want to disable them.
2. Dynamic query. You have a dynamic query that used Oracle data cartridge to fetch data in the parse step, but Oracle won't execute the parse step because the query looks static to Oracle.
3. Misunderstanding. Something has gone wrong and this is an XY problem.

Solutions

The simplest way to solve this problem are by using Thorsten Kettner's solution of changing the query each time.

If that's not an option, the second simplest solution is to flush the query from the shared pool, like this:

--This only works one node at a time.
begin
    for statements in
    (
        select distinct address, hash_value
        from gv$sql
        where sql_id = '33t9pk44udr4x'
        order by 1,2
    ) loop
        sys.dbms_shared_pool.purge(statements.address||','||statements.hash_value, 'C');
    end loop;
end;
/

If you have no control over the SQL, and need to fix the problem using a side-effect style solution, Jonathan Lewis and Randolf Geist have a solution using Virtual Private Database, that adds a unique predicate to each SQL statement on a specific table. You asked for something weird, here's a weird solution. Buckle up.

-- Create a random predicate for each query on a specific table.
create table hard_parse_test_rand as
select * from all_objects
where rownum <= 1000;

begin
  dbms_stats.gather_table_stats(null, 'hard_parse_test_rand');
end;
/

create or replace package pkg_rls_force_hard_parse_rand is
  function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
end pkg_rls_force_hard_parse_rand;
/

create or replace package body pkg_rls_force_hard_parse_rand is
  function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
  is
    s_predicate varchar2(100);
    n_random pls_integer;
  begin
    n_random := round(dbms_random.value(1, 1000000));
    -- s_predicate := '1 = 1';
    s_predicate := to_char(n_random, 'TM') || ' = ' || to_char(n_random, 'TM');
    -- s_predicate := 'object_type = ''TABLE''';
    return s_predicate;
  end force_hard_parse;
end pkg_rls_force_hard_parse_rand;
/

begin
  DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test_rand', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse_rand.force_hard_parse', 'select');
end;
/

alter system flush shared_pool;

You can see the hard-parsing in action by running the same query multiple times:

select * from hard_parse_test_rand;
select * from hard_parse_test_rand;
select * from hard_parse_test_rand;
select * from hard_parse_test_rand;

Now there are three entries in GV$SQL for each execution. There's some odd behavior in Virtual Private Database that parses the query multiple times, even though the final text looks the same.

select *
from gv$sql
where sql_text like '%hard_parse_test_rand%'
    and sql_text not like '%quine%'
order by 1;