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 sql> col component for a30 sql> col oper_type for a20 sql> col oper_mode for a20 sql> select component,oper_type,oper_mode,start_time,end_time,target_size,final_size from v$sga_resize_ops order by start_time desc; COMPONENT OPER_TYPE OPER_MODE START_TIME END_TIME TARGET_SIZE FINAL_SIZE ------------------------------ -------------------- -------------------- ------------------- ------------------- ----------- ---------- shared pool STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 285212672 285212672 large pool STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 16777216 16777216 java pool STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 16777216 16777216 streams pool STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 16777216 16777216 DEFAULT buffer cache INITIALIZING 2018-07-04 10:37:10 2018-07-04 10:37:10 1795162112 1795162112 ASM Buffer Cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 RECYCLE buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 DEFAULT 2K buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 DEFAULT 4K buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 DEFAULT 8K buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 DEFAULT 16K buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 COMPONENT OPER_TYPE OPER_MODE START_TIME END_TIME TARGET_SIZE FINAL_SIZE ------------------------------ -------------------- -------------------- ------------------- ------------------- ----------- ---------- DEFAULT 32K buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 KEEP buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 0 0 DEFAULT buffer cache STATIC 2018-07-04 10:37:10 2018-07-04 10:37:10 1795162112 1795162112
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,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),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
LOG BUFFER sql> select * from v$sgainfo where name='Redo Buffers'; NAME BYTES RESIZEABL ------------------------------ ---------- --------- Redo Buffers 4964352 No sql> select 4964352/1024/1024 from dual; 4964352/1024/1024 ----------------- 4.734375 sql> select 20*1024*1024 from dual; 20*1024*1024 ------------ 20971520 sql> alter system set log_buffer=20971520 scope=spfile; System altered. sql> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sql> startup ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2215064 bytes Variable Size 520094568 bytes Database Buffers 1593835520 bytes Redo Buffers 21741568 bytes Database mounted. Database opened. sql> select * from v$sgainfo where name='Redo Buffers'; NAME BYTES RESIZEABL ------------------------------ ---------- --------- Redo Buffers 21741568 No
