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