It is possible for a single session to require more than one sort or hash area at a time. For example a 4 table hash could require 3 in-memory hash tables (and therefore use 3 x hash_area_size in the days before pga_agg_target).
Possibly your session used 150MB, but had multiple areas open at once, of which the largest was 90MB.- are the definitions of the columns completely unambiguous, or is there room for error in interpreting their use ? 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, December 30, 2003 8:59 PM > Hi, > > First of all, thank you to all answered my last question. > Now I have another question related to my last one. > In my system, pga_aggregate_target is set to 3GB and I > think a session would have approximately 150MB work area > before temp space is needed (5% of 3GB). > But I did a test, it only used 90MB max. Anyone has a explanation? > > Thanks, > > Roger Xu > > SQL> > 1 select sid > 2 ,ACTIVE_TIME > 3 ,WORK_AREA_SIZE > 4 ,EXPECTED_SIZE expected > 5 ,ACTUAL_MEM_USED actual > 6 ,MAX_MEM_USED max > 7 ,NUMBER_PASSES pass > 8 ,TEMPSEG_SIZE tempsize > 9 from v$sql_workarea_active; > > SID ACTIVE_TIME WORK_AREA_SIZE EXPECTED ACTUAL MAX PASS TEMPSIZE > ---------- ----------- -------------- ---------- ---------- ---------- --- ------- ---------- > 13 1644005675 29966336 29966336 24232960 91504640 1 470712320 > > SQL> select * from v$pgastat; > > NAME VALUE UNIT > ---------------------------------------- ---------- ------------ > aggregate PGA target parameter 3221225472 bytes > aggregate PGA auto target 2861061120 bytes > global memory bound 104857600 bytes > total PGA inuse 62332928 bytes > total PGA allocated 188590080 bytes > maximum PGA allocated 188590080 bytes > total freeable PGA memory 81330176 bytes > PGA memory freed back to OS 1677459456 bytes > total PGA used for auto workareas 20333568 bytes > maximum PGA used for auto workareas 91521024 bytes > total PGA used for manual workareas 0 bytes > maximum PGA used for manual workareas 0 bytes > over allocation count 0 > bytes processed 3.4667E+10 bytes > extra bytes read/written 0 bytes > cache hit percentage 100 percent > > 16 rows selected. > > ________________________________________________________________________ > This email has been scanned for all viruses by the MessageLabs Email > Security System. For more information on a proactive email security > service working around the clock, around the globe, visit > http://www.messagelabs.com > ________________________________________________________________________ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Roger Xu > 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). -- 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).
