Sure, I have two users OPS$TKYTE and SCOTT.  they both have EMP tables.  They both ran some queries.  When I look in v$sqlarea:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sql_text, version_count from v$sqlarea where sql_text like 'select * from emp%';

SQL_TEXT                                 VERSION_COUNT
---------------------------------------- -------------
select * from emp                                    1
select * from emp e1                                 2
select * from emp e2                                 2
select * from emp, dept where emp.deptno             1
 = dept.deptno

select * from emp@ora815.us.oracle.com               1


I see 2 versions of select * from emp e1, select * from emp e2. I only see the query once but I know there are two versions.  So, I goto v$sql:


ops$tkyte@ORA817DEV.US.ORACLE.COM> select sql_text, parsing_user_id, parsing_schema_id from v$sql where sql_text like 'select * from emp%';

SQL_TEXT                                 PARSING_USER_ID PARSING_SCHEMA_ID
---------------------------------------- --------------- -----------------
select * from emp                                      0                 0
select * from emp, dept where emp.deptno              28                28
 = dept.deptno

select * from emp e2                                  28                28
select * from emp e2                                   0                 0
select * from emp@ora815.us.oracle.com                28                28
select * from emp e1                                  28                28
select * from emp e1                                   0                 0

7 rows selected.

And I can see why.  The select * from emp e2 queries where parsed by different users, with different effective (parsing schema id) ids.  They are the same text -- totally different queries.

 

ops$tkyte@ORA920LAP> create table t ( x int );
Table created.

ops$tkyte@ORA920LAP> select * from t test;
no rows selected

ops$tkyte@ORA920LAP> select first_load_time from v$sql where sql_text = 'select * from t test';

FIRST_LOAD_TIME
-------------------
2003-09-22/21:42:38

ops$tkyte@ORA920LAP> alter table t add y int;
Table altered.

ops$tkyte@ORA920LAP> select * from t test;
no rows selected

ops$tkyte@ORA920LAP> select first_load_time from v$sql where sql_text = 'select * from t test';

FIRST_LOAD_TIME
-------------------
2003-09-22/21:42:38


Ok, so what does this show?  well, nothing directly but -- obviously, "select * from t test" was invalidated and then rebuilt -- but the load time didn't change (and I type fast, but not that fast)...

so, what if "select * from t test" took 5 times the amount of space?  something might have to go.

it could be that the statements are in v$sql -- but is invalidated -- so not really in the shared pool truly.  So, they actually get parsed and loaded up -- flushing other stuff out as well.  (statements do not fall out of v$sql when invalidated...) 

That has the defintion of each - now, now for the demo ;)

ops$tkyte@ORA10GR2> alter system flush shared_pool;
System altered.
 
ops$tkyte@ORA10GR2> create table t ( x int );
Table created.
 
ops$tkyte@ORA10GR2> alter session set optimizer_mode=all_rows;
Session altered.
 
ops$tkyte@ORA10GR2> select * from t;
no rows selected
 
ops$tkyte@ORA10GR2> exec dbms_lock.sleep(3);
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> alter session set optimizer_mode=first_rows;
Session altered.
 
ops$tkyte@ORA10GR2> select * from t;
no rows selected
 
ops$tkyte@ORA10GR2> select first_load_time, last_load_time from v$sql where sql_text = 'select * from t';
 
FIRST_LOAD_TIME     LAST_LOAD_TIME
------------------- -------------------
2006-02-10/18:00:02 2006-02-10/18:00:02
2006-02-10/18:00:02 2006-02-10/18:00:05

<b>first load time is the load time of the parent.  for the first query (which is also the "parent" of all queries that look like select * from t), we have a first_load_time = last_load_time, it was last "optimized" when it was first loaded.

For the second query, 3 seconds later with a different optimizer environment, we have a first_load_time that is consistent (we expect it to be) with the first - it is the parents load time - but the last_load_time, the last time IT was optimized is 3 seconds later.

Let's add an index, to invalidate the existing cursors and causing them to be hard parsed next time:</b>

 
ops$tkyte@ORA10GR2> create index t_idx on t(x);
Index created.
 
ops$tkyte@ORA10GR2> exec dbms_lock.sleep(3);
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> alter session set optimizer_mode=all_rows;
Session altered.
 
ops$tkyte@ORA10GR2> select * from t;
no rows selected
 
ops$tkyte@ORA10GR2> exec dbms_lock.sleep(3);
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> alter session set optimizer_mode=first_rows;
Session altered.
 
ops$tkyte@ORA10GR2> select * from t;
no rows selected
 
ops$tkyte@ORA10GR2> select first_load_time, last_load_time from v$sql where sql_text = 'select * from t';
 
FIRST_LOAD_TIME     LAST_LOAD_TIME
------------------- -------------------
2006-02-10/18:00:02 2006-02-10/18:00:08
2006-02-10/18:00:02 2006-02-10/18:00:11

<b>Now, note that the first_load_time stayed the same - as long as the statement doesn't entirely flush out of the shared pool - it will stay that way.

However, the create index we just did caused the last_load_time for the first query to change - we just re-optimized that plan.  Likewise for the second.

We can read that as:

the first time we think we saw 'select * from t' was 2006-02-10/18:00:02.  The last time we hard parsed (and hence optimized) the first child cursor was 2006-02-10/18:00:08.  The second child cursor was last optimized on 2006-02-10/18:00:11.

</b>
 

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from scott.emp where 1=0;
  3  begin
  4          open c;
  5          close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, first_load_time from v$sql where sql_text = 'SELECT * FROM SCOTT.EMP WHERE 1=0';

SQL_TEXT
-------------------------------------------------------------------------------
FIRST_LOAD_TIME
-------------------
SELECT * FROM SCOTT.EMP WHERE 1=0
2013-03-25/10:33:11


ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from scott.emp where 1=0;
  3  begin
  4          open c;
  5          close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, first_load_time from v$sql where sql_text = 'SELECT * FROM SCOTT.EMP WHERE 1=0';

SQL_TEXT
-------------------------------------------------------------------------------
FIRST_LOAD_TIME
-------------------
SELECT * FROM SCOTT.EMP WHERE 1=0
2013-03-25/10:33:15

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from scott.emp where 1=0;
  3  begin
  4          open c;
  5          close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, first_load_time from v$sql where sql_text = 'SELECT * FROM SCOTT.EMP WHERE 1=0';

SQL_TEXT
-------------------------------------------------------------------------------
FIRST_LOAD_TIME
-------------------
SELECT * FROM SCOTT.EMP WHERE 1=0
2013-03-25/10:34:17


ops$tkyte%ORA11GR2> REM  connect /
ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from scott.emp where 1=0;
  3  begin
  4          open c;
  5          close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, first_load_time from v$sql where sql_text = 'SELECT * FROM SCOTT.EMP WHERE 1=0';

SQL_TEXT
-------------------------------------------------------------------------------
FIRST_LOAD_TIME
-------------------
SELECT * FROM SCOTT.EMP WHERE 1=0
2013-03-25/10:34:17