Abnormal result by “show sga” command in 11g

一个有趣的现象。在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的显示结果正确了(有一些细微的误差,可以忽略不计)。

3 thoughts on “Abnormal result by “show sga” command in 11g

  1. 还有这片日志的Title是不是应该是”Wrong result by “show SGA” command in 11g”而不是PGA呀?

    ps:我这里提交评论的时候会提交上去,但是会显示一个connection rset by peer…刷新下又好了~~奇怪~~

Leave a Reply

Your email address will not be published. Required fields are marked *