平台：WinXP SP2
数据库版本：10.2.0.1

注：下列有些视图内容较多，就没有列出详细内容

1.V$SGA
#SGA使用情况的概要信息

sys@TEST>select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              1248504
Variable Size         109052680
Database Buffers      150994944
Redo Buffers            7139328

2.V$SGASTAT
#SGA使用情况的详细信息

3.V$SGAINFO
#SGA各个组件的大小信息，还可以看到哪些组件可以动态reszie

sys@TEST>select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1248504 No
Redo Buffers                        7139328 No
Buffer Cache Size                 150994944 Yes
Shared Pool Size                  100663296 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  268435456 No
Startup overhead in Shared Pool    37748736 No
Free SGA Memory Available                 0

4.V$SGA_CURRENT_RESIZE_OPS
#当前正在进行的SGA resize操作

5.V$SGA_RESIZE_OPS
#最后400个SGA resize操作的历史

6.V$SGA_DYNAMIC_COMPONENTS
#SGA各个组件的动态信息，这些信息基于已完成的SGA resize操作

7.V$SGA_DYNAMIC_FREE_MEMORY
#可用于将来resize的空闲SGA内存，可以理解为sga_maxe_size与sga_target的差

sys@TEST>col sga_max_size for a20
sys@TEST>col sga_target for a20
sys@TEST>select a.value sga_max_size,b.value sga_target,a.value-b.value diff
from v$parameter a,v$parameter b
where a.name='sga_max_size' and b.name='sga_target';

SGA_MAX_SIZE         SGA_TARGET                 DIFF
-------------------- -------------------- ----------
268435456            251658240              16777216

sys@TEST>select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
    16777216

8.V$SGA_TARGET_ADVICE
#根据负载对SGA的使用情况给出建议。从下面的查询可以看出目前256M的设置基本是合理的

sys@TEST>select * from V$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       256               1          525                   1               21378
       128              .5          976               1.859               52265
       192             .75          547              1.0419               22567
       512               2          520               .9905               21378
       384             1.5          520               .9905               21378
       448            1.75          520               .9905               21378
       320            1.25          520               .9905               21378