Windows平臺下不能建立資料庫連線出錯分析(2)

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