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

http://blog.itpub.net/12361284/viewspace-250491/