> 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 ?

I think somewhere I said DDL only flushes heap 6 in 9i and before, and it flushes heaps 0 and 6 in 
10g. But now I'm not sure. I was only observing what's in v$sql. 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. That's something I need to 
do too.

> 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.