![如何了解当前SGA内存分布情况?](https://linux22.com/image/521862/%E5%A6%82%E4%BD%95%E4%BA%86%E8%A7%A3%E5%BD%93%E5%89%8DSGA%E5%86%85%E5%AD%98%E5%88%86%E5%B8%83%E6%83%85%E5%86%B5%EF%BC%9F.png)
我如何获得当前的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 博客,但没有提供此类信息的查询。
答案1
好像select * from v$sgainfo ;
答案2
如果您可以以 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 /