如何获得Oracle 10G上当前的 SGA内存分配？
像这样的东西：
AME SIZE_MB RES -------------------------------- ---------- --- Maximum SGA Size 396 No Shared Pool Size 320 Yes Startup overhead in Shared Pool 32 No Streams Pool Size 32 Yes Java Pool Size 24 Yes Buffer Cache Size 16 Yes Granule Size 4 No Redo Buffers 2,79 No Fixed SGA Size 1,21 No Free SGA Memory Available 0 Large Pool Size 0 Yes 我在Marko Sutic的Oracle博客中find了这张表，但没有提供这种信息的查询。
看起来像select * from v$sgainfo ;
如果可以以sysdba身份连接，则可以使用以下查询显示隐藏的参数。
MEG GIG PARAMETER DESCRIPTION ------ ------ ----------------------------------- ----------------------------------------------------------------- .0 .0 __large_pool_size Actual size in bytes of large pool .0 .0 __streams_pool_size Actual size in bytes of streams pool .0 .0 __shared_io_pool_size Actual size of shared IO pool 128.0 .1 __java_pool_size Actual size in bytes of java pool 512.0 .5 db_recycle_cache_size Size of RECYCLE buffer pool for standard block size buffers 2336.0 2.3 __shared_pool_size Actual size in bytes of shared pool 3072.0 3.0 db_keep_cache_size Size of KEEP buffer pool for standard block size buffers 3808.0 3.7 __db_cache_size Actual size of DEFAULT buffer pool for standard block size buffer 6240.0 6.1 __sga_target Actual size of SGA 8192.0 8.0 __pga_aggregate_target Current target size for the aggregate PGA memory consumed
该查询如下所示：
SYS AS SYSDBA> SELECT i.instance_name instance, 2 -- b.ksppstvl "Session_Value", 3 c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig, 4 -- above is instance_value 5 a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION" 6 FROM 7 x$ksppi a, 8 x$ksppcv b, 9 x$ksppsv c, 10 v$instance i 11 WHERE 12 a.indx = b.indx 13 AND 14 a.indx = c.indx 15 AND 16 (a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size') 17 and a.ksppinm not in ('__oracle_base') 18 and c.ksppstvl not in ('TRUE','FALSE') 19 order by 3 20 /