Oracle
如何找出目前的 SGA 記憶體分佈?
如何獲取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 部落格上找到了這張表,但沒有提供此類資訊的查詢。
好像
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 /