一个有趣的现象。在11.1.0.6版本的数据库中show sga的显示结果并不正确。

KAMUS@orcl11g> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.06
KAMUS@orcl11g> show sga

Total System Global Area  380817408 bytes
Fixed Size                  1333340 bytes
Variable Size             289408932 bytes
Database Buffers           83886080 bytes
Redo Buffers                6189056 bytes
KAMUS@orcl11g> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 364M
KAMUS@orcl11g> select sum(bytes) from v$sgastat;

SUM(BYTES)
----------
 271768244

Elapsed: 00:00:00.03
KAMUS@orcl11g> select value from v$pgastat where name='total PGA allocated';

     VALUE
----------
  80623616

Elapsed: 00:00:00.01
KAMUS@orcl11g> select 380817408/1024/1024 from dual;

380817408/1024/1024
-------------------
         363.175781

Elapsed: 00:00:00.01

在show sga命令中显示Total System Global Area大小实际上是所有Memory的大小，包括了SGA+PGA，也就是memory_target参数的值。

将11g的新参数memory_target禁用，单独设置SGA和PGA，再看一下。

SYS@orcl11g> alter system set memory_target=0 scope=spfile;

System altered.
SYS@orcl11g> alter system set sga_target=271768244 scope=spfile;

System altered.
SYS@orcl11g> alter system set pga_aggregate_target=113246208 scope=spfile;

System altered.

修改完毕以后，重新启动数据库实例。再次用show sga命令显示SGA大小。

KAMUS@orcl11g> show sga

Total System Global Area  272011264 bytes
Fixed Size                  1332612 bytes
Variable Size             180357756 bytes
Database Buffers           88080384 bytes
Redo Buffers                2240512 bytes
KAMUS@orcl11g> select sum(bytes) from v$sgastat;

SUM(BYTES)
----------
 272013276

Elapsed: 00:00:00.10

这次show sga的显示结果正确了（有一些细微的误差，可以忽略不计）。