Before i show my test result, i should tell you the
fact that you shouldn't use sql trace & tkprof to
identify whether your SQL cursor is invalidated. Why?
SQL trace-ed query generates additional child cursor.
SQL trace is a typical case of unshareable SQL
statements. V$SQL.invalidations is the more
appropriate way to do it.
dion i dont know much in detail about it why not to use sql trace and tkprof for invalidated.
i have done a small test case where i found invalidation but its not auto.
session 1
---------
SQL> drop table t
2 /
Table dropped.
SQL> create table t as
2 select 1 rn,object_name from all_objects
3 /
Table created.
SQL> update t set rn=2 where rownum<=10
2 /
10 rows updated.
SQL> commit
2 /
Commit complete.
SQL> select count(*),rn
2 from t
3 group by rn
4 /
COUNT(*) RN
---------- ----------
38962 1
10 2
SQL> create index t_rn_ndx on t (rn)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent=>null,method_opt=>'f
or all indexed columns')
PL/SQL procedure successfully completed.
SQL> variable v1 number
SQL> exec :v1:=2
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=true
2 /
Session altered.
SQL> select *
2 from t
3 where rn=:v1
4 /
RN OBJECT_NAME
---------- ------------------------------
2 DUAL
2 DUAL
2 SYSTEM_PRIVILEGE_MAP
2 SYSTEM_PRIVILEGE_MAP
2 TABLE_PRIVILEGE_MAP
2 TABLE_PRIVILEGE_MAP
2 STMT_AUDIT_OPTION_MAP
2 STMT_AUDIT_OPTION_MAP
2 MAP_OBJECT
2 RE$NV_LIST
10 rows selected.
SQL> exec :v1:=1
PL/SQL procedure successfully completed.
before executing i know if i collect stats from other session then parsed one plan will be invalidate and it should parse again but it will not due to default true invalidation.
gathering stats from other session with default
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent=>null,method_opt=>'f
or all indexed columns')
PL/SQL procedure successfully completed.
back again to session 1
SQL> select *
2 from t
3 where rn=:v1
4 /
.
.
.
.
.
.
38962 rows selected.
SQL> alter session set sql_trace=false
2 /
Session altered.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
BEGIN :v1:=1; END;
call count cpu elapsed disk query current rows
------
----------
----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------
----------
----------
total 2 0.00 0.03 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
********************************************************************************
select *
from t
where rn=:v1
call count cpu elapsed disk query current rows
------
----------
----------
Parse 2 0.04 0.47 0 0 0 0
Execute 2 0.00 0.07 0 0 0 0
Fetch 2601 0.17 0.09 0 5447 0 38972
------
----------
----------
total 2605 0.21 0.64 0 5447 0 38972
Misses in library cache during parse: 1<---reusing the plan
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
38962 TABLE ACCESS BY INDEX ROWID T (cr=5442 pr=0 pw=0 time=116941 us)
38962 INDEX RANGE SCAN T_RN_NDX (cr=2675 pr=0 pw=0 time=38979 us)(object id 49256)
********************************************************************************
what we see stats collection from other session does not in validate the parsed one plan
Next scenario
-------------
SQL> conn scott/tiger
Connected.
SQL> drop table t
2 /
Table dropped.
SQL> create table t as
2 select 1 rn,object_name from all_objects
3 /
Table created.
SQL> update t set rn=2 where rownum<=10
2 /
10 rows updated.
SQL> commit
2 /
Commit complete.
SQL> select count(*),rn
2 from t
3 group by rn
4 /
COUNT(*) RN
---------- ----------
38962 1
10 2
SQL> create index t_rn_ndx on t (rn)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent=>null,method_opt=>'f
or all indexed columns')
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=true
2 /
Session altered.
SQL> variable v2 number
SQL> exec :v2:=2
PL/SQL procedure successfully completed.
SQL> select *
2 from t
3 where rn=:v2
4 /
RN OBJECT_NAME
---------- ------------------------------
2 DUAL
2 DUAL
2 SYSTEM_PRIVILEGE_MAP
2 SYSTEM_PRIVILEGE_MAP
2 TABLE_PRIVILEGE_MAP
2 TABLE_PRIVILEGE_MAP
2 STMT_AUDIT_OPTION_MAP
2 STMT_AUDIT_OPTION_MAP
2 MAP_OBJECT
2 RE$NV_LIST
10 rows selected.
now i go to other session and collect stats with
exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent => null,method_opt=>'for all indexed columns', no_invalidate=>FALSE);
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent => null,method_opt=>
'for all indexed columns', no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
Come to session 1 again
-----------------------
SQL> exec :v2:=1
PL/SQL procedure successfully completed.
SQL> select *
2 from t
3 where rn=:v2
4 .
SQL> /
.
.
.
.
.
.
.
.
.
.
.
38962 rows selected.
SQL> alter session set sql_trace=false
2 /
Session altered.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
BEGIN :v2:=2; END;
call count cpu elapsed disk query current rows
------
----------
----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------
----------
----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
********************************************************************************
select *
from t
where rn=:v2
call count cpu elapsed disk query current rows
------
----------
----------
Parse 2 0.04 0.40 0 0 0 0
Execute 2 0.00 0.18 0 0 0 0
Fetch 2601 0.04 0.06 0 2774 0 38972
------
----------
----------
total 2605 0.09 0.65 0 2774 0 38972
Misses in library cache during parse: 2<----not reusing 2 time parsing
Optimizer mode: ALL_ROWS
Parsing user id: 57
********************************************************************************
m i missing something?
Khurram
