pga workarea and ora-04030

 

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

 

14:30:44 SQL> /

 

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).