Hi, I have posted a problem before
which I can only solve with a workaround but because I'm not getting satisdactory answers from Oracle I'm trying
alternatives. -
problem is a batch pl/sql package which ends
with ora-4030 -
batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4 recently -
setting pga_aggegrate_target=0 and workarea_size_policy=manual solves the error -
This is the only batch which results in errors Due to recent posts I have
tried smm_max_size set to 100Mb and I still get this
error. All of the following is done with _smm_max_size set and first setting pga_aggegrate_target=50M and workarea_size_policy=auto Monitoring v$sql_workarea_active
leads me a max. use of 532Kb. The figures below didn't
change during the batch I only saw temporarily another
workarea for the same sid OPTYPE ACTTIME
WA_SIZE EXP_SIZE
ACT MAXMEM PASS TEMPSEG
TBLSP ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
------------------------------- GROUP
BY ( 1378396893 532480 532480 532480 532480
0 NAME
VALUE UNIT ----------------------------------------------------------------
---------- ------------ aggregate PGA target parameter
104857600 bytes aggregate PGA auto target
6553600 bytes global memory bound
104857600 bytes total PGA inuse 1105825792
bytes total PGA allocated
1129529344 bytes maximum PGA allocated
1135382528 bytes total freeable PGA
memory 458752
bytes PGA memory freed back to OS
1303117824 bytes total PGA used for auto workareas
737280 bytes maximum PGA used for auto workareas 1163264
bytes total PGA used for manual workareas
0 bytes NAME
VALUE UNIT ----------------------------------------------------------------
---------- ------------ maximum PGA used for manual workareas
16384 bytes over allocation count
979 bytes processed
3141169152
bytes extra bytes read/written
0 bytes cache hit percentage
100 percent a)
Why do I see manual workarea used despite workarea_size_policy=auto ? It is
a test environment with just me and a developer on it b)
Total pga used
reports as 737Kb and total pga allocated finishes on
1.1Gb How can I relate this to the workarea? c)
What's the exact connection with the _smm_max_size? When
I increase the pga_aggregate_target to 2Gb and the smm_max_size also the
program fails around the following numbers from pgastat QL>
/ NAME
VALUE UNIT ----------------------------------------------------------------
---------- ------------ aggregate PGA target parameter
2147483648
bytes aggregate PGA auto target
1895003136 bytes global memory bound
2097152000 bytes total PGA inuse
41918464 bytes total PGA allocated
1137232896 bytes maximum PGA allocated
1137249280 bytes total freeable PGA
memory
1074987008 bytes PGA memory freed back to OS
131072 bytes total PGA used for auto workareas
0 bytes maximum PGA used for auto workareas
0 bytes total PGA used for manual workareas
0 bytes NAME
VALUE UNIT ----------------------------------------------------------------
---------- ------------ maximum PGA used for manual workareas
2347008 bytes over allocation count
0 bytes processed
1603424256 bytes extra bytes read/written
6708224 bytes cache hit percentage
99.58 percent d)
What is remarkable that auto workareas are now
on 0, the manual stuff might be by another testuser,
the total pga_allocated is just a little bit higher. I am completely confused by now and I
hope you can shed some light on this. Regards, Jeroen |
- Re: pga workarea and ora-04030 Jeroen van Sluisdam
- Re: pga workarea and ora-04030 Jonathan Lewis
- Re: pga workarea and ora-04030 Tanel Poder
- RE: pga workarea and ora-04030 Jeroen van Sluisdam
- Re: pga workarea and ora-04030 Jonathan Lewis
- Re: pga workarea and ora-04030 Ryan
- Re: pga workarea and ora-04030 Jonathan Lewis
- Re: pga workarea and ora-04030 Ryan
- RE: pga workarea and ora-04030 Bobak, Mark
- Re: pga workarea and ora-04030 Tim Gorman
- RE: pga workarea and ora-04030 Jeroen van Sluisdam