oracle 9i pga使用控制

oracle 9i pga使用控制

我們知道從9i開始oracle提供了自動管理pga的功能，從此我們可以不管sort_area_size，hash_area_size等等煩人的設定，也不用再去計算繁瑣的pga大小。但是怎麼控制每個session使用pga的大小呢，會不會出現一個session用完所有pga，而其他session得不到空間的狀況嗎？
帶著這個疑問我們可以來做一個實驗。

實驗的步驟很簡單，初始pga_aggregate_target設定為100m，我們做一個排序的大查詢，然後查看pga的使用空間。然後修改pga_aggregate_target為1g，在做一個大查詢，查看pga使用空間。

SQL> startup pfile=/test/inittest.ora
ORACLE instance started.

Total System Global Area 2710294504 bytes
Fixed Size                   454632 bytes
Variable Size             805306368 bytes
Database Buffers         1903992832 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100000000
 

SQL> conn test/test
Connected.
SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                   243208
session pga memory max                                            243208
這裡可以看到初始串連時pga使用了240k左右

SQL> set autotrace trace;

SQL> select title from test order by starts,ends,title,zoo,city;
2997531 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=129273 Card=2932520 Bytes=173018680)
   1    0   SORT (ORDER BY) (Cost=129273 Card=2932520 Bytes=173018680)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (FULL) OF 'TEST' (Cost=84738 Card=2932520 Bytes=173018680)

Statistics
----------------------------------------------------------
        475  recursive calls
         75  db block gets
     930726  consistent gets
     962505  physical reads
         60  redo size
  113681365  bytes sent via SQL*Net to client
    2198688  bytes received via SQL*Net from client
     199837  SQL*Net roundtrips to/from client
         37  sorts (memory)
          1  sorts (disk)
    2997531  rows processed

SQL> set autotrace off;
SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                   457496
session pga memory max                                            6355736

做完一個大查詢，pga的使用峰值是達到了6m左右，這其中可能包含了一部分pga自己的開銷
關閉資料庫，修改pga_aggregate_target為1g，再重啟資料庫
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Jul 18 11:46:53 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=/test/inittest.ora
ORACLE instance started.

Total System Global Area 2710294504 bytes
Fixed Size                   454632 bytes
Variable Size             805306368 bytes
Database Buffers         1903992832 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                   250792
session pga memory max                                            381864

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1000000000
SQL> set autotrace trace;
SQL> select title from test order by starts,ends,title,zoo,city;
2997531 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=112995 Card=2932520 Bytes=173018680)
   1    0   SORT (ORDER BY) (Cost=112995 Card=2932520 Bytes=173018680)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (FULL) OF 'TEST' (Cost=84738Card=2932520 Bytes=173018680)

Statistics
----------------------------------------------------------
       3740  recursive calls
         28  db block gets
     931577  consistent gets
     955856  physical reads
         60  redo size
  113681365  bytes sent via SQL*Net to client
    2198688  bytes received via SQL*Net from client
     199837  SQL*Net roundtrips to/from client
         77  sorts (memory)
          1  sorts (disk)
    2997531  rows processed

SQL> set autotrace off;
SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                   456568
session pga memory max                                            50263928

可以看到這次大查詢使用了近50m的pga空間，為什麼第一次只使用了6m呢，這是因為oracle會限制每個session使用pga的最大空間，
每個session可以使用到最大至5%pga的總空間，所以第一次可以使用到5m左右的空間，加上pga自己的開銷，使用了一共6m。第二次
由於總pga大小擴大了，所以session使用的pga大小也差不多是5%的總pga大小.oracle很好的控制了pga的使用，避免了一個session
佔用大部分pga的情況。
不過，還存在一種特例，在並行操作的情況下session pga可以使用到30%總pga空間。