Windows平台下不能建立数据库连接出错分析(2)

本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明

http://blog.itpub.net/post/11/5160

-- 在减少sga后可以建立更多的连接

SQL> alter system set sga_max_size=1000m scope=spfile;

系统已更改。

SQL> alter system set db_cache_size=500m scope=both;

系统已更改。

SQL> shutdown abort

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 1048576000 bytes

Fixed Size                   792200 bytes

Variable Size             518252920 bytes

Database Buffers          528482304 bytes

Redo Buffers                1048576 bytes

数据库装载完毕。

数据库已经打开。

SQL>

-- 运行连接测试脚本

-- 查看统计信息

> SELECT * FROM V$PGASTAT;

aggregate PGA target parameter                     2097152000 bytes

aggregate PGA auto target                          1829569536 bytes

global memory bound                                 104857600 bytes

total PGA inuse                                      65129472 bytes

total PGA allocated                                 160458752 bytes

maximum PGA allocated                               166881280 bytes

total freeable PGA memory                                   0 bytes

PGA memory freed back to OS                                 0 bytes

total PGA used for auto workareas                           0 bytes

maximum PGA used for auto workareas                         0 bytes

total PGA used for manual workareas                         0 bytes

maximum PGA used for manual workareas                       0 bytes

over allocation count                                       0

bytes processed                                       2792448 bytes

extra bytes read/written                                    0 bytes

cache hit percentage                                      100 percent

已选择16行。

> select server,count(*) from v$session group by server;

DEDICATED        218

-- 再次运行连接测试脚本

-- 查询统计信息

> SELECT * FROM V$PGASTAT;

aggregate PGA target parameter                     2097152000 bytes

aggregate PGA auto target                          1782429696 bytes

global memory bound                                104857600 bytes

total PGA inuse                                     112276480 bytes

total PGA allocated                                  269494272 bytes

maximum PGA allocated                             290007040 bytes

total freeable PGA memory                                   0 bytes

PGA memory freed back to OS                                 0 bytes

total PGA used for auto workareas                      131072 bytes

maximum PGA used for auto workareas                    491520 bytes

total PGA used for manual workareas                         0 bytes

maximum PGA used for manual workareas                       0 bytes

over allocation count                                       0

bytes processed                                      15297536 bytes

extra bytes read/written                                    0 bytes

cache hit percentage                                      100 percent

已选择16行。

> select server,count(*) from v$session group by server;

DEDICATED        417

>

当前包括oracle后台进程在内已建立的会话有417个,远远高于此前的76个。

1.         结论

前段时间看一个帖子问到为何增大了db_cache_size会造成ora-12540错误,原因就在于pga和sga均在进程oracle.exe所使用的内存空间分配,’蛋糕’就那么大,sga分多了,当然pga就少一点了.

2.         附录

V$PGASTAT中几个列的含义:

(a)   aggregate PGA auto target : 在自动模式下oracle所能用于work area的pga内存总数.

(b)    total PGA used for auto workarea: 系统使用的实际可调整的pga内存.

(c)    total PGA in used: 正在使用的pga内存总数.

参考文档:

Oracle9i Automatic PGA Memory Management

连接测试脚本见:

http://blog.itpub.net/post/11/3357