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@xxxxxxxxx>:
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@xxxxxxxxx>
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
