Oracle & Starcraft

bulk fetch limit可以限制每次bulk collect into的行数，这在fetch一个大cursor时非常有必要，因为它能减少pga的内存使用量。

我们来看一下加了limit子句和不加limit的pga使用率的区别

首先看一下不执行语句时的pga使用率

[oracle@csdbc oracle]$ sqlplus taobao/taobao

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 7 13:59:40 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
exit
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL 9I>select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and (a.name like ‘%pga%’ or a.name like ‘%uga%’);
NAME VALUE
—————————————————————- ———-
session uga memory 142468
session uga memory max 142468
session pga memory 286044
session pga memory max 286044

当限制每次fetch 10万条时的pga使用率

[oracle@csdbc oracle]$ sqlplus taobao/taobao
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 7 14:00:53 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL 9I>declare
2 cursor c1 is select nick from bmw_users;
a dbms_sql.varchar2s;
3 4 begin
5 open c1;
6 loop
7 fetch c1 bulk collect into a limit 100000;
8 exit when c1%notfound;
9 end loop;
10 close c1;
end;
11 12 /
PL/SQL procedure successfully completed.

select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and (a.name like ‘%pga%’ or a.name like ‘%uga%’);
exit
PL/SQL procedure successfully completed.
SQL 9I>SQL 9I>
NAME VALUE
—————————————————————- ———-
session uga memory 77004
session uga memory max 142468
session pga memory 5502408
session pga memory max 5502408

当不限制条数时的pga使用率

[oracle@csdbc oracle]$ sqlplus taobao/taobao
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 7 14:00:53 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL 9I>declare
cursor c1 is select nick from bmw_users;
2 3 a dbms_sql.varchar2s;
4 begin
5 open c1;
loop
fetch c1 bulk collect into a;
6 7 8 exit when c1%notfound;
9 end loop;
10 close c1;
11 end;
12 /
select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and (a.name like ‘%pga%’ or a.name like ‘%uga%’);
exit
PL/SQL procedure successfully completed.
SQL 9I>SQL 9I>
NAME VALUE
—————————————————————- ———-
session uga memory 77004
session uga memory max 142468
session pga memory 117558392
session pga memory max 117558392

最后来看一下每次fetch一千条的pga使用率

[oracle@csdbc oracle]$ sqlplus taobao/taobao
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 7 14:00:53 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL 9I>declare
2 cursor c1 is select nick from bmw_users;
a dbms_sql.varchar2s;
3 4 begin
5 open c1;
6 loop
7 fetch c1 bulk collect into a limit 1000;
8 exit when c1%notfound;
9 end loop;
10 close c1;
end;
11 12 /
PL/SQL procedure successfully completed.
SQL 9I>select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and (a.name like ‘%pga%’ or a.name like ‘%uga%’);

NAME VALUE
—————————————————————- ———-
session uga memory 142468
session uga memory max 142468
session pga memory 360268
session pga memory max 360268