SGA SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ sga_target big integer 2G SQL> show parameter sga_max_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ sga_max_size big integer 2G SQL> select name,value/1024/1024 as "SIZE (MB)" from v$sga; NAME SIZE (MB) ------------------------------ ---------- Fixed Size 2.11244965 Variable Size 320.000832 Database Buffers 1712 Redo Buffers 4.734375 DB BUFFER CACHE SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_block_size integer 8192 SQL> select * from v$sgainfo where name='Granule Size'; NAME BYTES RESIZEABL ------------------------------ ---------- --------- Granule Size 16777216 No SQL> select 16777216/8192 from dual; -- granule 中包含 2048 个 buffer 16777216/8192 ------------- 2048 SQL> select * from v$sgainfo where name='Buffer Cache Size'; NAME BYTES RESIZEABL ------------------------------ ---------- --------- Buffer Cache Size 1795162112 Yes SQL> select 1795162112/1024/1024 from dual; 1795162112/1024/1024 -------------------- 1712 SQL> select 1795162112/8192 from dual; --db_cache 中包含 219136 个 buffer 1795162112/8192 --------------- 219136 SQL> select 219136/2048 from dual; --db_cache 由 107 个 granule 组成 219136/2048 ----------- 107 SHARED POOL SQL> select * from v$sgainfo where name='Shared Pool Size'; NAME BYTES RESIZEABL ------------------------------ ---------- --------- Shared Pool Size 285212672 Yes SQL> select 285212672/1024/1024 from dual; 285212672/1024/1024 ------------------- 272 SQL> column indx heading "indx | indx num" SQL> column kghlurcr heading "recurrent|chunks" SQL> column kghlutrn heading "transient|chunks" SQL> column kghlufsh heading "flushed|chunks" SQL> column kghluops heading "pins and|releases" SQL> column kghlunfu heading "ora-4031|errors" SQL> column kghlunfs heading "last error|size" SQL> select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs from x$kghlu where inst_id=userenv('Instance'); indx recurrent transient flushed pins and ora-4031 last error indx num chunks chunks chunks releases errors size ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 3541 8077 0 22813 0 0 SQL> show parameter shared_pool_reserved_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ shared_pool_reserved_size big integer 14260633 SQL> col ksppinm for a35 SQL> col ksppstvl for a20 SQL> col ksppdesc for a80 SQL> select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx and ksppinm = '_shared_pool_reserved_min_alloc'; KSPPINM KSPPSTVL KSPPDESC ----------------------------------- -------------------- -------------------------------------------------------------------------------- _shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area of shared pool SQL> select free_space,avg_free_size,used_space,request_failures,last_failure_size from v$shared_pool_reserved; FREE_SPACE AVG_FREE_SIZE USED_SPACE REQUEST_FAILURES LAST_FAILURE_SIZE ---------- ------------- ---------- ---------------- ----------------- 10146416 724744 16704568 0 0 SQL> col ksppinm for a35 SQL> col ksppstvl for a20 SQL> col ksppdesc for a50 SQL> select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx and ksppinm = '_kghdsidx_count'; KSPPINM KSPPSTVL KSPPDESC ----------------------------------- -------------------- -------------------------------------------------- _kghdsidx_count 1 max kghdsidx count SQL> select 'shared pool('||nvl (decode (to_char(ksmdsidx),'0','0-Unused',ksmdsidx),'Total')||'):'subpool,round(sum(ksmsslen)/1048576,2) "SIZE(MB)" 2 from x$ksmss where ksmsslen > 0 3 group by rollup(ksmdsidx) order by subpool asc; SUBPOOL SIZE(MB) ------------------------------ ---------- shared pool(0-Unused): 64 shared pool(1): 208 shared pool(Total): 272 SQL> select subpool,name,round(sum(bytes)/1048576,2) "FREE SIZE(MB)" from (select 'shared pool (' || decode (to_char (ksmdsidx),'0','0-Unused',ksmdsidx) || '):' subpool,ksmssnam name, ksmsslen bytes from x$ksmss where ksmsslen>0 and lower(ksmssnam) like lower ('%free memory%')) group by subpool,name order by subpool asc,sum(bytes) desc; SUBPOOL NAME FREE SIZE(MB) ------------------------------ ------------------------------ ------------- shared pool (0-Unused): free memory 64 shared pool (1): free memory 48.41
来源: http://www.bubuko.com/infodetail-2670622.html