查看SGA区剩余可用内存的方法

2/9/2008来源:Oracle教程人气:5893


  SQL> col OBJECT_NAME format a20
  SQL> col 自由空间百分比(%) format 90.99   --格式化,把小数点对齐为两位
  
  SQL> select name,
        sgasize/1024/1024        "Allocated(M)",
        bytes/1024            "自由空间(K)",
        round(bytes/sgasize*100, 2)   "自由空间百分比(%)"
     from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
     where f.name = 'free memory';
  
  NAME Allocated(M) 自由空间(K) 自由空间百分比(%)
  -------------------------- ------------ ----------- -----------------
  free memory 54.4491119 11619.457 20.84
  free memory 54.4491119 600 1.08
  free memory 54.4491119 17788 31.90
  
  以下为查看sys.v_$sgastat相关
  
  SQL> desc sys.v_$sgastat
   名称 空? 类型
   ----------------------------------------- -------- ---------------------
   POOL VARCHAR2(11)
   NAME VARCHAR2(26)
   BYTES NUMBER
  
  SQL> select sum(bytes) sgasize from sys.v_$sgastat; --SGA区总和
  
   SGASIZE
  ----------
   57094032
  
  SQL> select * from sys.v_$sgastat;
  
  POOL NAME BYTES
  ----------- -------------------------- ----------
   fixed_sga 70924
   db_block_buffers 16777216
   log_buffer 66560
  shared pool free memory 11935052
  shared pool miscellaneous 566128
  shared pool PLS non-lib hp 2096
  shared pool State objects 134572
  shared pool KGFF heap 6552
  shared pool character set object 58936
  shared pool KGK heap 17568
  shared pool db_files 370988
  
  POOL NAME BYTES
  ----------- -------------------------- ----------
  shared pool joxlod: in phe 4144
  shared pool KQLS heap 430828
  shared pool table columns 18400
  shared pool fixed allocation callback 320
  shared pool PL/SQL DIANA 371572
  shared pool enqueue_resources 96768
  shared pool PL/SQL MPCODE 61832
  shared pool trigger inform 520
  shared pool transactions 66900
  shared pool trigger defini 1392
  shared pool distributed_transactions- 180152
  
  POOL NAME BYTES
  ----------- -------------------------- ----------
  shared pool VIRTUAL CIRCUITS 112260
  shared pool view columns d 1072
  shared pool SYSTEM PARAMETERS 61336
  shared pool dictionary cache 296356
  shared pool message pool freequeue 231152
  shared pool joxlod: in ehe 52712
  shared pool character set memory 135888
  shared pool db_block_buffers 278528
  shared pool library cache 1132868
  shared pool Checkpoint queue 73764
  shared pool sql area 933812
  
  POOL NAME BYTES
  ----------- -------------------------- ----------
  shared pool PRocesses 47200
  shared pool sessions 147108
  shared pool joxs heap init 896
  shared pool transaction_branches 368000
  shared pool event statistics per sess 231840
  shared pool db_block_hash_bUCkets 98328
  shared pool file # translation table 65572
  large pool free memory 614400
  java pool free memory 18214912
  java pool memory in use 2756608
  
  已选择43行。