Re: v$sql vs v$sqlarea difference ??

> *But i am still not clear, someone told me v$sql may contains 
> objects not exists in Shared pool, which i am also not clear,

That's not possible. Where did you read that?

Beginning with 10g, the definition of v$sqlarea is no longer a simple sum over 
child cursors' stats. V$fixed_view_definition says it's a straight query 
against 
x$kglcursor_child_sqlid while v$sql is against x$kglcursor_child, although 
x$kqfdt says they're both based on x$kglob. You can find that numbers don't 
always add up right any more in v$sqlarea for given SQLs:

In 10.2.0.4:

SQL> select hash_value, version_count from v$sqlarea where version_count > 100 
order by 1;

HASH_VALUE VERSION_COUNT
---------- -------------
1340709931           150
1769112893           123
2667277860           259
3922085829           275
4209591192           101

SQL> select hash_value, count(*) from v$sql where hash_value in
  2  (1340709931,1769112893,2667277860,3922085829,4209591192)
  3  group by hash_value order by 1;

HASH_VALUE   COUNT(*)
---------- ----------
1340709931         53
1769112893         27
2667277860        259
3922085829        275
4209591192         37

Another difference is that regardless Oracle version, v$sql includes kglobt16 
to calculate sharable_mem while v$sqlarea does not.

Yong Huang


      
--
http://www.freelists.org/webpage/oracle-l

Other related posts: