玉面飞龙的BLOG

UGA memory consumed by Session_Cached_Cursors

July 28th, 2007 | Categories: Boring | Tags:

通过设置session_cached_cursors实现Soft Soft Parse，Session会将多次（大于3次）执行的SQL Cursor缓存在UGA中，这需要占用一定量的UGA内存。

如下测试代码测试，因缓存游标而具体消耗了多少UGA.

#
#测试使用MTS模式连接，版本Oracle9.2.0.5
#
#主要参考UGA的统计数据，MTS下PGA的统计与该参数设置无关
#
SQL> select * from v$statname where name like '%uga%';
STATISTIC# NAME
---------- --------------------------------------------
   15 session uga memory
   16 session uga memory max

SQL> select * from v$statname where name like '%pga%';
STATISTIC# NAME
---------- --------------------------------------------
   20 session pga memory
   21 session pga memory max
#
#数据库默认session_cached_cursors=0
#
SQL> conn binzhang/binzhang@mts9205
Connected.

SQL> select * from v$sesstat where STATISTIC# in (15,16,20,21) and sid=10;
   SID STATISTIC# VALUE
---------- ---------- ----------
   10 15 77336
   10 16 77336
   10 20 274536
   10 21 274536

SQL> declare
2 a number;
3 begin
4 for j in 1..4 loop
5 for i in 1..1000 loop
6 execute immediate 'select count(*) from t where id='||i into a;
7 end loop;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> /

   SID STATISTIC# VALUE
---------- ---------- ----------
   10 15 77336
   10 16 93400
   10 20 274536
   10 21 274536
#
# session uga memory保持不变，Session并没有缓存任何closed cursor
#
SQL> alter session set session_cached_cursors=1000;
Session altered.

SQL> declare
2 a number;
3 begin
4 for j in 1..4 loop
5 for i in 1..1000 loop
6 execute immediate 'select count(*) from t where id='||i into a;
7 end loop;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> /

   SID STATISTIC# VALUE
---------- ---------- ----------
   10 15 1025112
   10 16 1025112
   10 20 274536
   10 21 274536

#
#session uga memory有明显增加，是因为缓存了1000个closed cursor (session cursor cache count)
#

经过测试，uga memory的增加和SQL Text的长度没有关系。UGA中只是缓存着必要的游标指针和SQL HASH VALUE等信息

可以类似测试session_cached_cursor为20，50，100等常用数值下,uga memory的增加，计算需要的free memory的大小,防止因为内存短缺而引起ORA-4031和ORA-4030错误的发生