Re: pga workarea and ora-04030

The workarea_policy stuff does not apply to things like pl/sql tables, only to tuneable memory. Given that you don't have the
problem when you disable p_a_t and w_p,
it may be that there is some buggy event occurring where the workarea_policy code is being infringed by an abuse of pga memory.

You could try setting up test cases where you use a pl/sql loop to build a pl/sql table. Make it a procedure with an input parameter that is the table size, and see how big the table has to before the procedure crashes. Fiddle with the p_a_t, and w_p (they can be set separately) to see if the crash point moves.

This may give you (or Oracle Corp) some clues.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 06, 2004 2:54 PM

>
> 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: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).