总结一些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