Re: v$sql vs v$sqlarea difference ??
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 29 Dec 2008 18:55:17 -0800 (PST)
> *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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
