> 1) can you think of any other reason a cursor could be
> invalidated other than DDL change?

I can't think of any. There're ways to purge a given cursor (in 10.2.0.4 and up), but not 
invalidate it. Only DDLs invalidate cursors, including recursive DDLs such as those on behalf of 
dbms_stats.gather_xxx_stats (unless no_invalidate is set to true or auto set to true). Note that 
the reverse is not true; not all DDLs invalidate cursors, e.g. alter table shrink space does not 
invalidate a cursor and does not update last_ddl_time although it commits your transaction 
(http://yong321.freeshell.org/oranotes/DDLsNotUpdatingLast_ddl_time.txt).

> 2) Does the reload occur in situations where the Heap 0 is
> still available in cache and the Heap 6 is loaded again
> (execution plan)? Following that thought - if the heap 0 is
> also flushed out of cache, then it would be considered a
> "load" as the cache doesn't have any information of its
> past existence in shared pool. Am I right in my
> understanding ?

[Update 2011-06]
I think the correct way to say is that the parent cursor (not in v$sql* but in v$db_object_cache or 
x$kglob) stays across DDL but the child cursor releases its memory (but not in 11g). The child 
cursor remembers its basic info (hash value, sql text etc) by way of its parent. The reload allocates 
memory for the child heaps 0 and 6.
[end of Update 2011-06]

The counters for "load" and "reload" are actually not consistently maintained in various places, 
v$librarycache, v$db_object_cache, v$sql[stats], and the word "load" in event 'library cache load 
lock' (see Steve Adams's book). V$sql* views simply lump them together to the loads column. Your 
understanding is the ideal. But it's better to use some actual stats in Oracle to prove.

[Update 2011-06]
Shared pool memory usage of cursors from 9i to 11g:

select kglobhs0, kglobhs6, kglobt16, kglobt09 from x$kglob where kglnaobj = 'select * from testparse';

That shows sizes of heaps 0 and 6. (Not sure what kglobt16 is. It's included in v$sql for sharable_mem 
calculation, but not in v$sqlarea because v$sqlarea already counts it, though only once though for all 
children, into x$kglcursor_child_sqlid.kglobhs0.)

Tanel says when kglobt09 (child number) is 65535, the row is for parent cursor. Indeed, unless the 
library cache object is not a cursor, kglhdpar equals kglhdadr when kglobt09 is 65535; vice versa.
[Update 2015-10]
In 12c, the new view x$kglcursor_parent shows parents exclusively.

Run these two SQLs  first:
create table testparse (x int);
select * from testparse;
Then
In 9i (9.2.0.6), after grant select on testparse to dba, v$sql and v$sqlarea still have the row but 
columns representing heap 6 change to 0 or NONE or N. Kglobhs0 and kglobhs6 for the child cursor in 
x$kglob show that heap0 and heap6 memory drop to 0. (kglobt16 remains.)

In 10g (10.2.0.1), after the DDL, v$sql and v$sqlarea no longer have the row. Kglobhs0 and kglobhs6 
for the child cursor in x$kglob are the same as in 9i.

In 11g (11.1.0.6), after the DDL, *only* 2 columns in v$sql and v$sqlarea change: object_status 
changes from VALID to INVALID_UNAUTH and invalidations increases by 1. Kglobhs0 and kglobhs6 for 
child cursor in x$kglob do not change. It's undocumented that a DDL in 11g can no longer release 
memory used by the child cursor, although the cursor is invalidated and will be re-parsed when 
used again.

In all three versions, kglobhs0 and kglobhs6 for the *parent* cursor, i.e. its heap0 and heap6 
memory, in x$kglob do not change after the DDL.


Parsing time is more consistent in 11g than 10g, but the speed is the same.
...
[end of Update 2011-06]

> 3) Can you confirm if "An Invalidation causes a reload" OR
> "A Reload causes an invalidation" OR they are not related at
> all. They could happen exclusive to each other. Can you please
> share the situations when reload and/or invalidation could occur?

If I need to choose, I choose the first one ("An Invalidation causes a reload"). The second doesn't 
make sense. The situation? Isn't that what you already know? A DDL causes invalidation. Then when a 
process needs that cursor, it reloads it.

> 4) Is it possible that child_number=0 could be flushed out,
> but child_number=3 could still be present in cache? If so, what
> would subsequent children compare against

Do you suspect child 0 has some special meaning? (It doesn't represent the parent cursor.) Or you 
think the child numbers should always be sequentially assigned and higher numbered children should 
be aged out first?

> 5) what would be the explanation for child_number=0 to have
> values of Y in the v$sql_shared_cursor. I thought that all the
> child_number > 0 would use the child_number=0 as the reference
> to compare against.

Interesting question. I don't know the answer. I checked my database and found some child 0 cursors 
with 'Y' under TOP_LEVEL_RPI_CURSOR and some under ROLL_INVALID_MISMATCH. I have to think about it. 
If you ask somewhere else or open a Tar with Oracle, please pass the answer to me once you find it.