Re: any single serial session will never get more than 5% of pga
Notes in-line 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: Sunday, December 28, 2003 12:39 AM Hi Jonathan, I'm not sure what you really think about this new feature! I view the feature as a positive step forward. Instead of a DBA having to guess an artificially low limit on the sort_area_size because (say) 1200 users might be connected to a machine with 4GB of memory, you now give Oracle a directive like: I have 1.5GB available for sort operations; please be as generous as you can when the demand for memory is low, and ration it carefully when the demand is high. In theory, this ensures that more processes get in-memory sorting because there is a known spare capacity - in practice, the algorithms and options for over-ride will, no doubt, evolve over time. Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? Yes If yes, then what does it have to do with the work policy? Nothing - but since the O/S used to take care of the problem by paging out unused memory there was little point in fixing something which wasn't totally broken. On the other hand, if you are trying to operate a policy of maximising the amount of memory you give to a session, based on your estimates of expected data volume, it makes sense to use code that allows a session to de-allocate memory properly. I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Now, if the techies on Redwood Shores could get the concepts of hungry and greedy into the code, perhaps we wouldn't have to do any more tuning ever again ;) Regards, Waleed -- 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: any single serial session will never get more than 5% of pga
Notes in-line 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 11:39 PM To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes. Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of punishing other sessions. Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Should as in 'you think this happens already' (it doesn't) or should as in 'the way the code ought to have been written in the first place' (it tried, but most unix libraries didn't implement the calls) ? The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather than doing a nested loop join. In the old days, the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database. Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: any single serial session will never get more than 5% of pga
Hi Jonathan, I'm not sure what you really think about this new feature! Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? If yes, then what does it have to do with the work policy? I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Regards, Waleed -Original Message- Sent: Saturday, December 27, 2003 3:09 AM To: Multiple recipients of list ORACLE-L pga Notes in-line 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 11:39 PM To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes. Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of punishing other sessions. Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Should as in 'you think this happens already' (it doesn't) or should as in 'the way the code ought to have been written in the first place' (it tried, but most unix libraries didn't implement the calls) ? The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather than doing a nested loop join. In the old days, the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database. Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L 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
RE: any single serial session will never get more than 5% of pga
To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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
Waleed, Please feel free to determine the answers to those questions. :) Jared Khedr, Waleed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 03:39 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: any single serial session will never get more than 5% of pga To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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).
