SGA相關的幾個動態效能檢視

總結一些SGA的相關的動態效能檢視：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> select table_name from dict where table_name like 'V$SGA%';

TABLE_NAME
------------------------------
V$SGA
V$SGAINFO
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
......

1.V$SGA這個檢視包括了SGA的的總體情況，只包含兩個欄位：name（SGA記憶體區名字）和value（記憶體區的值，單位為位元組）。它的結果和show sga的結果一致，顯示了SGA各個區的大小
SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              2072776
Variable Size         721424184
Database Buffers      637534208
Redo Buffers           14700544

SQL> show sga

Total System Global Area 1375731712 bytes
Fixed Size                  2072776 bytes
Variable Size             721424184 bytes
Database Buffers          637534208 bytes
Redo Buffers               14700544 bytes

2.V$SGASTAT這個檢視比較重要，其中記錄了SGA的各個pool和區的統計資訊，包含三個欄位：Name（SGA記憶體區的名字）；Bytes（記憶體區的大小，單位為位元組）；Pool（這段記憶體所屬的記憶體池）。
SQL> desc v$sgastat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 POOL                                               VARCHAR2(12)
 NAME                                               VARCHAR2(26)
 BYTES                                              NUMBER

顯示當前shared pool的空閒位元組:
SQL> select pool,name,bytes from v$sgastat
  2  where name='free memory' and pool='shared pool';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 169431928

3.V$SGAINFO的作用基本和V$SGA一樣，只不過把Variable size的部分更細化了一步
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2072776 No
Redo Buffers                       14700544 No
Buffer Cache Size                 637534208 Yes
Shared Pool Size                  268435456 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                    167772160 Yes
Streams Pool Size                         0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 1375731712 No
Startup overhead in Shared Pool    83886080 No
Free SGA Memory Available         268435456

4.V$SGA_DYNAMIC_COMPONENTS
這個檢視記錄了SGA各個動態記憶體區的情況，它的統計資訊是基於已經完成了的，針對SGA動態記憶體區大小調整的操作。
SQL> desc v$sga_dynamic_components;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPONENT                                          VARCHAR2(64)
 CURRENT_SIZE                                       NUMBER
 MIN_SIZE                                           NUMBER
 MAX_SIZE                                           NUMBER
 USER_SPECIFIED_SIZE                                NUMBER
 OPER_COUNT                                         NUMBER
 LAST_OPER_TYPE                                     VARCHAR2(13)
 LAST_OPER_MODE                                     VARCHAR2(9)
 LAST_OPER_TIME                                     DATE
 GRANULE_SIZE                                       NUMBER

SQL> select component,current_size,oper_count,granule_size from v$sga_dynamic_components;

COMPONENT                      CURRENT_SIZE OPER_COUNT GRANULE_SIZE
------------------------------ ------------ ---------- ------------
shared pool                       268435456          0     16777216
large pool                         16777216          0     16777216
java pool                         167772160          0     16777216
streams pool                              0          0     16777216
DEFAULT buffer cache              637534208          1     16777216
KEEP buffer cache                         0          0     16777216
RECYCLE buffer cache                      0          0     16777216
DEFAULT 2K buffer cache                   0          0     16777216
DEFAULT 4K buffer cache                   0          0     16777216
DEFAULT 8K buffer cache                   0          0     16777216
DEFAULT 16K buffer cache                  0          0     16777216
DEFAULT 32K buffer cache                  0          0     16777216
ASM Buffer Cache                          0          0     16777216

5.V$SGA_DYNAMIC_FREE_MEMORY
這個檢視只有一個欄位就是用來表示SGA當前可以用於調整各個的空閒區域，也就是sga_max_size - sga中各個pool或區域設定大小的綜合。
SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
   268435456

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-250491/，如需轉載，請註明出處，否則將追究法律責任。