玉面飞龙的BLOG

通过设置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错误的发生