How to Force Oracle to Change a Query's Plan Without Using Hints

how to change oracle explain plan for sql query manually

There are too many answers, since your question is too broad. And you haven't even mentioned the DB version.

You said :

updating the plan id to new plan id will force the sql query to use
different explain plan.

No. The PLAN_HASH_VALUE is assigned by Oracle. It is a marker to relate an EXECUTION PLAN with the respective SQL_ID. If you want to force the OPTIMIZER to take a different execution plan of your choice, you could use HINTS in your query.

If you have pinned or preserved the execution plan for the SQL using stored outlines etc., also known as PLAN STABILITY, then you need to remove it, so that Oracle would be able to find a proper execution plan in it's next execution. And then, if required, you can stabilize the better plan as you think so.

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

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;

How to prevent parallel execution on query with no hint

Forcing a single query to use /*+ NO_PARALLEL */ is easy because it's a statement-level hint. There's no need to add query block names or figure out Oracle's internal names.

Add the NO_PARALLEL hint to the query:

begin
    dbms_sqltune.import_sql_profile(
        sql_text => 'select /*+ parallel */ * from dba_objects',
        profile => sqlprof_attr('no_parallel'),
        name => '0ftu9j02g81b0_noparallel'
    );
end;
/

Verify that the statement has a profile and does not run in parallel:

explain plan for select /*+ parallel */ * from dba_objects;
select * from table(dbms_xplan.display);

Results:

...

Note
-----
   - Degree of Parallelism is 1 because of hint
   - SQL profile "0ftu9j02g81b0_noparallel" used for this statement
   - this is an adaptive plan

Is there a way to make Oracle recalculate a query plan for each query invocation?

For Oracle 10g we would choose any table in the query and execute

GRANT SELECT ON table1 TO user1;

This would invalidate the plan of any query referencing this table. Of course you would want to choose a table which has minimal impact on other queries. See also this page for more information and a sample listing.

Oracle EXECUTE IMMEDIATE changes explain plan of query

It turns out that this is a known bug in Oracle 9i. Below is the text from a bug report.

Execute Immediate Gives Bad Query Plan [ID 398605.1]

Modified 09-NOV-2006     Type PROBLEM     Status MODERATED

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6
This problem can occur on any platform.

Symptoms
When a procedure is run through execute immediate the plan produced is a different than when procedure is run directly.

Cause
The cause of this problem has been identified and verified in an unpublished Bug 2906307.
It is caused by the fact that SQL statements issued from PLSQL at a recursive
depth greater than 1 may get different execution plans to those issued directly from SQL.
There are multiple optimizer features affected by this bug (for example _unnest_subquery,_pred_move_around=true)
HINTS related to the features may also be ignored.

This bug covers the same basic issue as Bug 2871645 Complex view merging does not occur for
recursive SQL > depth 1 but for features other than complex view merging.

Bug 2906307 is closed as a duplicate of Bug 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS.
It is fixed in 10.2

Solution
For insert statements use hint BYPASS_RECURSIVE_CHECK:
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

References
BUG:2871645 - COMPLEX VIEW MERGING DOES NOT OCCUR FOR RECURSIVE SQL > DEPTH 1
BUG:3182582 - SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS

---

Related Topics