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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
