When the child cursor is invalidated, does the next execution of the sql - don't produce the new child cursor? if so what does invalidation mean in v$sql ?
rajesh@ORA11G> drop table emp purge;
Table dropped.
rajesh@ORA11G> create table emp as select * from scott.emp;
Table created.
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'emp');
PL/SQL procedure successfully completed.
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
14 rows selected.
rajesh@ORA11G> select child_number,invalidations from v$sql
2 where sql_id ='g59vz2u4cu404';
CHILD_NUMBER INVALIDATIONS
------------ -------------
0 0
1 row selected.
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'emp',no_invalidate=>false);
PL/SQL procedure successfully completed.
rajesh@ORA11G> select child_number,invalidations from v$sql
2 where sql_id ='g59vz2u4cu404';
CHILD_NUMBER INVALIDATIONS
------------ -------------
0 1
1 row selected.
rajesh@ORA11G> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
14 rows selected.
rajesh@ORA11G> select child_number,invalidations from v$sql
2 where sql_id ='g59vz2u4cu404';
CHILD_NUMBER INVALIDATIONS
------------ -------------
0 1
1 row selected.
rajesh@ORA11G>
The documentation for V$SQL says for INVALIDATIONS: "Number of times this child cursor has been invalidated"
So just because a child has been invalidated, doesnt mean it must be discarded entirely, it simply means existing information about the child cannot be used, it must reloaded on next invocation:
SQL> drop table emp purge;
Table dropped.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'emp');
PL/SQL procedure successfully completed.
SQL> set serveroutput off
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select loads, child_number,invalidations from v$sql where sql_id ='g59vz2u4cu404';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
1 0 0
SQL> exec dbms_stats.gather_table_stats(user,'emp',no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select loads, child_number,invalidations from v$sql where sql_id ='g59vz2u4cu404';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
1 0 1
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select loads, child_number,invalidations from v$sql where sql_id ='g59vz2u4cu404';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
2 0 1
Even if I totally change the table, the need for another child is not there (assuming no-one else isnt in the middle of using it)
SQL> drop table emp purge;
Table dropped.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select loads, child_number,invalidations from v$sql where sql_id ='g59vz2u4cu404';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
3 0 2
SQL> alter table emp add ( blah int);
Table altered.
SQL> alter table emp drop column empno;
Table altered.
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select loads, child_number,invalidations from v$sql where sql_id ='g59vz2u4cu404';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
4 0 3
To expand on this, I re-did this exercise using upper case for the SQL, and then fired up another session and ran:
SQL> declare
2 rc sys_Refcursor;
3 x int;
4 begin
5 open rc for SELECT COUNT(*) FROM EMP;
6 fetch rc into x;
7 dbms_lock.sleep(30);
8 close rc;
9 end;
10 /
PL/SQL procedure successfully completed.
thus the first child is *in use* for at least 30 seconds. I than added a column to EMP, whilst that was running
SQL> alter table emp add ( blah3 int);
Table altered.
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
15
SQL> select loads, child_number,invalidations from v$sql where sql_id ='79bg9cb9a77v2';
LOADS CHILD_NUMBER INVALIDATIONS
---------- ------------ -------------
2 0 1
1 1 1
and notice another child IS created, because the first one cannot be touched (its in use)
Hope this helps.