Re: How to force Oracle generate a new execution plan
Date: Fri, 11 Nov 2016 08:36:36 +0100
Message-ID: <CAJu8R6jULazzNcqPXpWC+Vr-ZekmwUkfpVuACBj+RjzA-LFV7A_at_mail.gmail.com>
Hello,
One thing I have played with in TEST environment but never in running system (yet) is the following
SQL> create table t as select rownum n1, rownum*2 n2 from dual connect by level <=100;
SQL> select count(1) from t where n1 = 10;
SQL> select count(1) from t where n1 = 10;
SQL> select * from table(dbms_xplan.display_cursor);
SQL> select
sql_id, child_number, executions, invalidations, object_status
from
gv$sql
where
sql_id = '41jpjk91rv6a1';
SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS OBJECT_STATUS
- ------------ ---------- ------------- -------------------
41jpjk91rv6a1 0 2 0 VALID
- Invalidating the cursor using dbms_sqldiag,
declare
v_sql_text clob;
begin
select sql_fulltext into v_sql_text from gv$sqlarea where sql_id = '41jpjk91rv6a1' and rownum=1;
sys.dbms_sqldiag_internal.i_create_patch(v_sql_text, 'xxxxx', 'invalidate_cursor');
sys.dbms_sqldiag.drop_sql_patch('invalidate_cursor');
end;
/
SQL> select
2 sql_id, child_number, executions, invalidations, object_status
3 from
4 gv$sql
5 where
6 sql_id = '41jpjk91rv6a1';
SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS OBJECT_STATUS
- ------------ ---------- ------------- -------------------
41jpjk91rv6a1 0 2 1 INVALID_UNAUTH
SQL> select count(1) from t where n1 = 10;
SQL> select
2 sql_id, child_number, executions, invalidations, object_status
3 from
4 gv$sql
5 where
6 sql_id = '41jpjk91rv6a1';
SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS OBJECT_STATUS
- ------------ ---------- ------------- -------------------
41jpjk91rv6a1 0 1 1 VALID
Notice that although a new child cursor n°0 has been hard parsed (execution = 1) Oracle is still keeping, in its external x$table, that this cursor
has already been invalidated (invalidations = 1)
Best regards
Mohamed Houri
2016-11-11 0:46 GMT+01:00 Carlos Sierra <carlos.sierra.usa_at_gmail.com>:
> Eriovaldo,
>
> Consider storing your values on a global temporary table and join it.
>
> If for whatever reason that were not possible, please find the SQL_ID and
> send me a SQLd360 for it. I would help you then to identify WHY your plan
> performs poorly. You do not want to “force” a new plan on every execution.
>
> Carlos
>
>
>
> On Nov 10, 2016, at 2:12 PM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> wrote:
>
> Hello,
>
> I have a dynamic query that is mounted by a java application.
> This query uses IN (:b1, :b2, :b3 ... :b1000)
>
> When the application submit the sql statment using until 20 bind
> variables, the return is fast.
> When the application submit the sql statment using more than 20 bind
> variables, the return is slow, very slow. Here It maybe using a certain bad
> execution plan.
> The limit of bind variables is 1000.
>
> 1.) How can I do to force Oracle always generate a new execution plan and
> not reuse the plan that is in cache ?
> 2.) Is there a way to clear a execution plan for a specific sql_id ? (I
> have the sql_id that supose is with the bad execution plan).
>
> Is there any hint to do it ?
>
> I cannot use:
> alter system flush BUFFER_CACHE;
> alter system flush SHARED_POOL;
> because I will clear all execution plans of the instance. I would like to
> solve only 1 sql_id.
>
>
> Regards
> Eriovaldo
>
>
>
-- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Member of Oraworld-team <http://www.oraworld-team.com/> Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri> -- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 11 2016 - 08:36:36 CET