Re: workarea_size_policy being ignored

In article <1103729936.925781.111420_at_c13g2000cwb.googlegroups.com>, Tom says...
>
>We do use a lot of procedure and package calls, and it could be the
>nature of the benchmark execution that results in memory not being
>freed up. But I thought that sort_area_size limits the amount of
>memory for a connection. In fact, Metalink Note 30918.1 specifically
>states that "Multiple allocations never occur. Only one memory area of
>SORT_AREA_SIZE exists for each user process at any time."
>
>Tom
>

it's wrong. but you can have many many many sort area retained sizes going on.

AND it only controls "sort area size", you can have as many sort area retained sizes as you have open cursors. (demo in a moment...)

AND it definitely does not control the 'data segment' memory used by plsql -- global variables, it only looks at the sort/hash workareas.

Consider (sort_area_retained_size = default, so sort area size is retained...)

ops$tkyte_at_ORA9IR2> alter session set workarea_size_policy = manual; Session altered.  

ops$tkyte_at_ORA9IR2> alter session set sort_area_size = 5242880; Session altered.  

ops$tkyte_at_ORA9IR2> @mystat 'session pga memory' ops$tkyte_at_ORA9IR2> set echo off  

NAME                                VALUE
------------------------------ ----------
session pga memory                 294556
session pga memory max             294556
 

ops$tkyte_at_ORA9IR2> variable x refcursor
ops$tkyte_at_ORA9IR2> declare
  2 l_rec all_objects%rowtype;
  3 begin
4 open :x for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11;
  5 fetch :x into l_rec;
  6 end;
  7 /  

PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2> @mystat2
ops$tkyte_at_ORA9IR2> set echo off  

NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory                4300764    4006208
session pga memory max            4300764    4006208

So, that sort took about 4m of RAM (and we've kept our sort area retained size by simply fetching row 1 and not fetching anymore, but not closing the cursor either)  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> @mystat 'session pga memory'
ops$tkyte_at_ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory                4300764
session pga memory max            4366300
 

ops$tkyte_at_ORA9IR2> variable y refcursor
ops$tkyte_at_ORA9IR2> declare
  2 l_rec all_objects%rowtype;
  3 begin
4 open :y for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11;
  5 fetch :y into l_rec;
  6 end;
  7 /  

PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2> @mystat2
ops$tkyte_at_ORA9IR2> set echo off  

NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory                8298460    3932160
session pga memory max            8298460    3932160
 

Another 4m allocated -- another retained area....

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> @mystat 'session pga memory'
ops$tkyte_at_ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory                8298460
session pga memory max            8363996
 

ops$tkyte_at_ORA9IR2> variable z refcursor
ops$tkyte_at_ORA9IR2> declare
  2 l_rec all_objects%rowtype;
  3 begin
4 open :z for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11;
  5 fetch :z into l_rec;
  6 end;
  7 /  

PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2> @mystat2
ops$tkyte_at_ORA9IR2> set echo off  

NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory               12296156    3932160
session pga memory max           12296156    3932160
 

ops$tkyte_at_ORA9IR2>

And again......

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation