show sga解析

一、show sga

1.初始参数

sga_max_size500M
sga_target0(手工设置)
db_cache_size200M
shared_pool_size52M
java_pool_size4M
large_pool_size4M
streams_pool_size4M
log_buffer 6980608

此时SGA应有229M左右的空间未分配。

2.show sga

SQL> show sga;

Total System Global Area 524288000 bytes(=sga_max_size)
Fixed Size 1297700 bytes
Variable Size 306184924 bytes

(=shared_pool_size+java_pool_size+large_pool_size+streams_pool_size+SGA未分配的空间)
Database Buffers 209715200 bytes

(=db_cache_size+db_keep_cache_size+db_recycle_cache_size+db_nk_cache_size)
Redo Buffers 7090176 bytes(=log_buffer)

二、Fixed Size

fixed SGA是SGA的一部分，随着数据库、操作系统的版本而变化。It is compiled into database.它包含了一系列的指针，指向SGA的其它组件、变量(存储各种参数)。fixed SAG通常是非常小的且不由我们控制。可以把它认为是SGA的引导区，Oracle通过它来查找SGA的其它区域。

三、Variable Size

下面的例子说明了Fixed Size包含了SGA未分配的空间。

1. 环境同上所述

db_cache_size:200M
Variable Size:292M

2.将db_cache_size从200M改为300M，再观察

SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
239075328===>修改前有228M

SQL> alter system set db_cache_size=300M scope=memory;

系统已更改。

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 300M

SQL> show sga

Total System Global Area 524288000 bytes
Fixed Size 1297700 bytes
Variable Size 201327324 bytes===>比306184924减少了100M
Database Buffers 314572800 bytes===>比209715200增加了100M
Redo Buffers 7090176 bytes

SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
134217728===>比239075328减少了100M

也就是DB_CACHE_SIZE新增的100M来自Variable Size

3. v$sgastat

SQL> select pool,sum(bytes) from v$sgastat
2 group by pool;

POOL SUM(BYTES)
------------ ----------
322960676===>约等于Database Buffers
java pool 4194304
streams pool 4194304
shared pool 54544016===>约等于shared_pool_size
large pool 4194304