oraclepga:oracle pga使用控制方法来源: 发布时间:星期六, 2008年9月27日 浏览:2次 评论:0
我们知道从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 [oracle@wanghai4 oracle]$ sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production _disibledevent=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 0
相关文章
读者评论
发表评论 |