Re: any single serial session will never get more than 5% of pga_aggregate_target
Hi! You may want to set _smm_max_size parameter to higher value (see my recent post about pga_aggregate_target), but I think setting your sessions pga management temporarily to manual + setting sort_area_size, as Jared and Jonathan already recommended, is better solution for your current issue. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 11:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -Original Message- Bobak, Mark Sent: Friday, December 26, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Jeffrey, If you are simply looking at a conversion from 8i to 9iR2, and you're not going to be implementing a bunch of new 9i features (that could affect PGA usage), then it's probably reasonably straightforward. You can look at 'session pga memory' and 'session pga memory max' statistics in V$SESSTAT to get an idea of current PGA memory consumption in 8i. The 'max' statistic is probably more interesting in terms of total aggregate memory consumption. Also, consider that any single serial session will never get more than 5% of pga_aggregate_target. For parallel operations, total is limited to 30%. So, if you have lots of sessions, each with modest requirements, that will not be a factor. If, on the other hand, your database supports just a few connections, and each has significant PGA memory requirements, then you may need to consider that limitation. Hope that helps, -Mark -Original Message- From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED] Sent: Fri 12/26/2003 2:34 PM To: Multiple recipients of list ORACLE-L Cc: Subject: pga_aggregate_target Starting an upgrade project for 8i to 9i R2. How can I come up with a reasonable starting value for the pga_aggregate_target parameter? Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. 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 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: any single serial session will never get more than 5% of pga_aggregate_target
You might experiment with; alter session set workarea_size_policy = manual; Jared Roger Xu [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 01:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any single serial session will never get more than 5% of pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -Original Message- Bobak, Mark Sent: Friday, December 26, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Jeffrey, If you are simply looking at a conversion from 8i to 9iR2, and you're not going to be implementing a bunch of new 9i features (that could affect PGA usage), then it's probably reasonably straightforward. You can look at 'session pga memory' and 'session pga memory max' statistics in V$SESSTAT to get an idea of current PGA memory consumption in 8i. The 'max' statistic is probably more interesting in terms of total aggregate memory consumption. Also, consider that any single serial session will never get more than 5% of pga_aggregate_target. For parallel operations, total is limited to 30%. So, if you have lots of sessions, each with modest requirements, that will not be a factor. If, on the other hand, your database supports just a few connections, and each has significant PGA memory requirements, then you may need to consider that limitation. Hope that helps, -Mark -Original Message- Sent: Fri 12/26/2003 2:34 PM To: Multiple recipients of list ORACLE-L Cc: Starting an upgrade project for 8i to 9i R2. How can I come up with a reasonable starting value for the pga_aggregate_target parameter? Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. 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: any single serial session will never get more than 5% of pga_aggregate_target
For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. 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: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- 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).
