| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: lib cache & sql area in V$SGASTAT
Rahul,
My understanding is that "sql area" from v$sgastat also includes private SQL areas, each of which corresponds to one open cursor. Since your "sql area" takes almost twice as much memory as your "library cache", you probably have quite a number of entries in v$open_cursor. (Looking at count(*) from v$open_cursor is crude; you really need to look at the sum of persistent_mem and runtime_mem in v$sqlarea for those open cursors). One of our databases right now only has 2 open cursors and the v$sgastat shows:
shared pool library cache 12792628 shared pool sql area 12465316
, the second number smaller than the first.
Oracle Concepts manual sometimes is confusing. It says SGA contains shared pool, which contains library cache, which contains private SQL areas. But later it says private SQL areas reside in PGA (for dedicated server configuration). We have to understand this to mean that logically private SQL areas belong to library cache, but physically reside in PGA.
My understanding could be quite wrong. Let's wait till Steve Adams and other gurus read your post!
Yong Huang
yong321_at_yahoo.com
yhuang_at_indigopool.com
you wrote:
List,
when selecting * from v$sgastat it shows
library cache and sql area separately !!
------------+----------+-----------+------------
library cache 24300368 sql area 47234680
library cache contains shared namespaces..which INCLUDES sql/plsql/ and object definitions , right ?
so..what does "sql area" contain ??
TIA Rahul
 |
 |