Re: max 5% of pga_aggregate_target for a single serial session
Your sample query would only have needed one active area. Another thought: although the maximum you are allowed is 150M, Oracle may decide that there is no point in giving it to you because it won't improve your performance significantly, but it might benefit other people if some of the memory is held back. For example: To get an optimal (in-memory only) sort, you need 200M. To get a one-pass sort you need 90M. Your limit is 150M. Oracle may decline to give you the extra 60 M past the memory required for a one-pass sort, because whatever memory you get you still have to write and re-read the whole data set to disc, so the extra 60M won't change things significantly. 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: Wednesday, December 31, 2003 4:14 PM select BELNR,count(*) from sapr3.bsis group by BELNR order by BELNR This was the SQL running at that time. -Original Message- Sent: Tuesday, December 30, 2003 5:44 PM To: Multiple recipients of list ORACLE-L 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 -- 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).
Re: max 5% of pga_aggregate_target for a single serial session
Thanks, I found it from the materials. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 8:19 AM Chapter 9, page 33 - Cat-Hash-strophes in the seminar notes. (The page number may have changed a little). If you have a plan like this, all the leading tables have been hashed into memory at the same time. HASH JOIN TABLE ACCESS (FULL) OF TABLE_4 HASH JOIN TABLE ACCESS (FULL) OF TABLE_3 HASH JOIN TABLE ACCESS (FULL) OF TABLE_2 TABLE ACCESS (FULL) OF TABLE_1 It is true that Oracle only works out the cost of joining two objects at a time - but the actual execution can pass results from stage to step before a step is completed, just like the NL join. In this example, the 10053 trace showed (if I recall correctly): calc 1: join t2 to t1 with swap_join_inputs calc2: join t3 to result1 with swap_join_inputs calc3: join t4 to result2 with swap_join_inputs 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: Wednesday, December 31, 2003 12:19 AM 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. -- 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).
RE: max 5% of pga_aggregate_target for a single serial session
select BELNR,count(*) from sapr3.bsis group by BELNR order by BELNR This was the SQL running at that time. -Original Message- Sent: Tuesday, December 30, 2003 5:44 PM To: Multiple recipients of list ORACLE-L 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 ACTUALMAX 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 target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 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 workareas91521024 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 percentage100 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). For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This
max 5% of pga_aggregate_target for a single serial session
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 ACTUALMAX 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 target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 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 workareas91521024 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 percentage100 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).
Re: max 5% of pga_aggregate_target for a single serial session
90 Meg was all it needed? Roger Xu [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 12:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:max 5% of pga_aggregate_target for a single serial session 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 ACTUALMAXPASS TEMPSIZE -- --- -- -- -- -- -- -- 13 164400567529966336 29966336 24232960 91504640 1 470712320 SQL select * from v$pgastat; NAME VALUE UNIT -- aggregate PGA target parameter 3221225472 bytes aggregate PGA auto target2861061120 bytes global memory bound104857600 bytes total PGA inuse 62332928 bytes total PGA allocated188590080 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 workareas91521024 bytes total PGA used for manual workareas0 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).
RE: max 5% of pga_aggregate_target for a single serial session
no. it used 800 MB of tempspace in the end. (also see the tempsize column output from the query of the v$sql_workarea_active view) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 30, 2003 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: max 5% of pga_aggregate_target for a single serial session90 Meg was all it needed? "Roger Xu" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 12:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:max 5% of pga_aggregate_target for a single serial sessionHi,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 Ithink a session would have approximately 150MB work areabefore temp space is needed (5% of 3GB).But I did a test, it only used 90MB max. Anyone has a explanation?Thanks,Roger XuSQL 1 select sid2 ,ACTIVE_TIME3 ,WORK_AREA_SIZE4 ,EXPECTED_SIZE expected5 ,ACTUAL_MEM_USED actual6 ,MAX_MEM_USED max7 ,NUMBER_PASSES pass8 ,TEMPSEG_SIZE tempsize9 from v$sql_workarea_active; SID ACTIVE_TIME WORK_AREA_SIZE EXPECTED ACTUAL MAXPASS TEMPSIZE-- --- -- -- -- -- -- -- 13 1644005675 29966336 29966336 24232960 91504640 1 470712320SQL select * from v$pgastat;NAME VALUE UNIT -- aggregate PGA target parameter 3221225472 bytesaggregate PGA auto target 2861061120 bytesglobal memory bound 104857600 bytestotal PGA inuse 62332928 bytestotal PGA allocated 188590080 bytesmaximum PGA allocated 188590080 bytestotal freeable PGA memory 81330176 bytesPGA memory freed back to OS 1677459456 bytestotal PGA used for auto workareas 20333568 bytesmaximum PGA used for auto workareas91521024 bytestotal PGA used for manual workareas0 bytesmaximum PGA used for manual workareas 0 bytesover allocation count 0bytes processed 3.4667E+10 bytesextra bytes read/written 0 bytescache hit percentage 100 percent16 rows selected.This email has been scanned for all viruses by the MessageLabs EmailSecurity System. For more information on a proactive email securityservice working around the clock, around the globe, visithttp://www.messagelabs.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Roger XuINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). 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
Re: max 5% of pga_aggregate_target for a single serial session
Your global memory bound statistic from v$pgastat says that max work area size is 100M. Maybe this 5% rule doesn't apply with large pga_aggregate_targets. The documentation claims that this value can be adjusted during db workload, so you might want to try to run your operation several times in a row and see which amount is used for workareas then. But as an alternative (as written few times earlier here), you can use _smm_max_size for manually increasing this global memory bound and allowing larger workareas. Note that pga target calculation algorithms seem to be quite complicated, thus you might not see the expected result right after first execution, workarea sizes seem to be continuously changing for same query, even though nothing hash changed in data (except internal workarea execution statistics). It seems that currently less than 30MB is used for your one-pass workarea operation. I don't trust this MAX value in v$sql_workarea_active that much, because in my brief tests on 9.2.0.4/W2k the workarea usage always jumped quite high for a short time in beginning of operation, but then quickly decreased back to smaller value until it finished. Try to set _smm_max_size to 200M (parameter is set in kilobytes) and see whether any more memory gets used (monitor WORK_AREA_SIZE instead of MAX). If not, then maybe optimizer QESMM (query execution service memory manager) knows that throwing extra 100MB of memory won't help your operation anyway, since it will still remain an one-pass operation (which means that intermediate results have to be stored in temp tablespace once). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10: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 ACTUALMAX 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 target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 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 workareas91521024 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 percentage100 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and
Re: max 5% of pga_aggregate_target for a single serial session
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 ACTUALMAX 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 target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 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 workareas91521024 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 percentage100 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).
Re: max 5% of pga_aggregate_target for a single serial session
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 ACTUALMAX 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 target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 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 workareas91521024 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 percentage100 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
Re: max 5% of pga_aggregate_target for a single serial session
Chapter 9, page 33 - Cat-Hash-strophes in the seminar notes. (The page number may have changed a little). If you have a plan like this, all the leading tables have been hashed into memory at the same time. HASH JOIN TABLE ACCESS (FULL) OF TABLE_4 HASH JOIN TABLE ACCESS (FULL) OF TABLE_3 HASH JOIN TABLE ACCESS (FULL) OF TABLE_2 TABLE ACCESS (FULL) OF TABLE_1 It is true that Oracle only works out the cost of joining two objects at a time - but the actual execution can pass results from stage to step before a step is completed, just like the NL join. In this example, the 10053 trace showed (if I recall correctly): calc 1: join t2 to t1 with swap_join_inputs calc2: join t3 to result1 with swap_join_inputs calc3: join t4 to result2 with swap_join_inputs 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: Wednesday, December 31, 2003 12:19 AM 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. -- 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).
