I've alway understood that joining occurs always in two steps, first two tables, then their result (row source) with next table and so on, so there's no need for more than 2 hash tables for example?
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, December 31, 2003 1:44 AM > > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
