Re: workarea_size_policy=auto and performance efficiency [was: Re:
Richard, Excellent testing approach! Thanks so much! I'll try it... -Tim on 10/3/03 6:50 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, Good questions. As you mention, the memory Oracle says and thinks it's released and what it actually releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1): ls -l /proc/1685047 where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively hogged until the session closes as you describe. However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process. Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused. I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating). What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial. Maybe others have similar real life experiences to share ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 3:44 AM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, Good questions. As you mention, the memory Oracle says and thinks it's released and what it actually releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1): ls -l /proc/1685047 where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively hogged until the session closes as you describe. However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process. Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused. I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating). What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial. Maybe others have similar real life experiences to share ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 3:44 AM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, Good questions. As you mention, the memory Oracle says and thinks it's released and what it actually releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1): ls -l /proc/1685047 where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively hogged until the session closes as you describe. However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process. Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused. I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating). What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial. Maybe others have similar real life experiences to share ? Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 3:44 AM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions. As
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions. As I mentioned before, we now experience no disks sorts whatsoever. In our environment, automatic workspace management has been ideal. We have a large number of sessions most of which perform workspace operations at some stage but not concurrently in any significant numbers. Thereby, we have managed to both improve the efficiency of workspace operations by allowing sessions to acquire the necessary memory as required while at the same time dramatically reducing overall memory consumption. Best of both worlds !! Cheers - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:39 AM Richard, I take it that your two points are...shall we say...enhancement requests, not current functionality? :-) Following up on the discussion of space-efficiency and tabling (for the moment) my questions about the performance-efficiency side of things. Yes, there certainly is an element of performance-efficiency to space-efficiency if it keeps you from swapping... ..anyway... Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even pretended to give memory back for the duration of the session, depending on the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. The hash and bitmap workareas have never had this functionality, as near as I can tell. So, I think that you're absolutely correct that sessions using WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it for a long time, essentially until they disconnect. Is this correct? Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered, the P_A_T algorithms only occur upon allocation of workarea memory. Is there any additional logic
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Answer inline. On Wed, 2003-10-01 at 13:44, Tim Gorman wrote: Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? If they are, they're doing that in a very, very strange way. Here is why: -bash-2.05b$ nm oracle|grep -i malloc 09fd1184 T CD_malloc 0a1555ec T drpamalloc 09c3a01a T eoj_malloc 09c3a096 T eoj_malloc2 09c3a236 T eoj_stmt_malloc 0931f890 t gslcsqbr_Malloc 09c6ff6c T gslummMalloc 0a19c8f4 T gxthmAlloc 09394844 T kdibmalloc 096a3068 t kgaz_nzmalloc 095d6c3e T kpmalloc 082846c8 T ksrmalloc 096224fc T kudmmalloc 096224a0 t kudmmalloc_nls 09f30250 T LhtqmAlloc 09f312fc T lmmfsmalloc 09ecfaa0 T lmmmalloc 09ecfee4 T lmmomallocblklist 09ed17cc T lmmstmalloc 09f51df0 T LpxmemAlloc U malloc@@GLIBC_2.0 0a2749f4 d malloc_str.11457 0a274a34 d malloc_str.11466 09c97af4 T nsgrFMAlloc 09d16af4 T nzos_nc_malloc 09cfbfb8 T nzumalloc 09955d44 t pbjomalloc 099bc2a6 t pcidma_xmalloc 09bb1c08 t pihtimalloc 09bc3e72 t pihtmackg_malloc_ckg 09bbdf9a t pitcimalloc 09bb4c44 t piurimalloc 09a5a780 T primalloc 08e9b3e0 T qerbmAllocateX 08f2b788 T qerjmAllocateX 08eb618c T qerrmAllocateX 0902101a T qksmmAllocCContext 090212be T qksmmAllocSharedWorkArea 098847cc t qmuXmlMemAlloc 098d7344 T qmxlsCreateFromAllocBuf 083205b6 t rfcmalloc 09812298 t skgmhmalloc 09c1a548 T sktsfMalloc 09d1e9ca T smAllocateCertData 098e9ab2 t sskgm_vlmallocwpe 09d3bd36 T T_malloc 09d7e96a T U_MemPoolMalloc 09d7ea32 T U_MemPoolMallocAndCopy 09d7e9cc T U_MemPoolMallocAndZero 09be69a2 T wpepstm_STMalloc -bash-2.05b$ Note that oracle is using malloc from glibc ( U malloc@@GLIBC_2.0 means that symbol malloc is undefined in the oracle code but is imported from glibc). Furthermore, letter T means that the symbol is defined in the code and that points to the entry of type text (i.e. code). So, not only oracle uses malloc from glibc, but they also define malloc galore. I believe that they've elevated the use of malloc to the new levels and turned it into so called ora-7445 black art. I should read marketing materials instead of looking into program itself. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: workarea_size_policy=auto and performance efficiency [was: Re:
I remember from somewhere in Ixora, that Oracle does only free() without calling brk() with a negative value to actually release the memory (probably you even can't do it in every circumstance), so the memory will remain used untill process exits or dies. OTOH, Oracle server processes are meant for continous use anyway, thus the memory is probably reused soon again. And if it is not ever used again, it will probably be paged to disk anyway where it doesn't disturb anyone. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 8:44 PM Richard, Thanks for the detailed explanation! As a C programmer of some 20 years, I can only assume that Oracle has done away with the use of the malloc(), free(), etc UNIX library calls and is now calling the UNIX system call brk() directly? It was the underlying heap-extent management in the standard malloc library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS. Because if Oracle is continuing to call malloc() and free(), then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't. Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past. Thanks in advance! -Tim on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maxi mum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions. As I mentioned before, we now experience no disks sorts whatsoever. In our environment, automatic workspace management has been ideal. We have a large number of sessions most of which perform workspace operations at some stage but not concurrently in any significant numbers. Thereby, we have managed to both improve the efficiency of workspace operations by allowing sessions to acquire the necessary memory as required while at the same time dramatically reducing overall memory consumption. Best of both worlds !! Cheers - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:39 AM Richard, I take it that your two points are...shall we say...enhancement requests, not current functionality? :-) Following up on the discussion of space-efficiency and tabling (for the moment) my questions about the performance-efficiency side of things. Yes, there certainly is an element of performance-efficiency to space-efficiency if it keeps you from swapping... ..anyway... Using
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, I would suggest there are two key advantages to using automatic workspace management. The first and perhaps most important is that yes, unlike the manual method by which sessions cling onto memory, automatic workspace management can deallocate the tuneable portion of the PGAs (those previously set with *_AREA_SIZE parameters) when no longer required. This means that the overall memory consumption used during peak periods (when memory is possibly a problem) is likely to be less as the average memory used per session is likely to be lower due to this deallocation process (although it does somewhat depend on both the size and concurrency of these operations). On a key production database at my current site, the vast majority of the 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite most having a substantially larger pga_max_mem due to previous workspace activity (as evidenced in v$process). This overall reduction in memory consumption is measurable at between 1-2G which for us was significant as we were pushing our memory limit previously. Secondly, as memory is more effectively returned, Oracle/we can be both more generous and more flexible in how much memory each session temporarily consumes. With manual tuning, after setting the (say) SAS to (say) 10M, what if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest there are quotas in how much a particular session can consume depending on workload (eg. 5% limit for serial operations, etc.), the maximum memory that can be safely consumed by a session could be somewhat higher. If too many operations require a onepass/multipass executions, then the P_A_T should obviously be reviewed. However although the P_A_T setting kinda provides a safety net for memory consumption, if you have few concurrent, largish workarea operations, you could set the P_A_T to be somewhat higher than perhaps desirable (if reached) knowing it won't in fact be reached because of the low concurrency of these operations. This then increases the maximum memory capacity for each session in a controlled manner, knowing that this memory won't be hogged by the sessions. As I mentioned before, we now experience no disks sorts whatsoever. In our environment, automatic workspace management has been ideal. We have a large number of sessions most of which perform workspace operations at some stage but not concurrently in any significant numbers. Thereby, we have managed to both improve the efficiency of workspace operations by allowing sessions to acquire the necessary memory as required while at the same time dramatically reducing overall memory consumption. Best of both worlds !! Cheers - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:39 AM Richard, I take it that your two points are...shall we say...enhancement requests, not current functionality? :-) Following up on the discussion of space-efficiency and tabling (for the moment) my questions about the performance-efficiency side of things. Yes, there certainly is an element of performance-efficiency to space-efficiency if it keeps you from swapping... ...anyway... Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even pretended to give memory back for the duration of the session, depending on the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. The hash and bitmap workareas have never had this functionality, as near as I can tell. So, I think that you're absolutely correct that sessions using WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it for a long time, essentially until they disconnect. Is this correct? Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered, the P_A_T algorithms only occur upon allocation of workarea memory. Is there any additional logic around de-allocation, possibly when the server process has finished using the workarea? Perhaps there is logic to de-allocate before beginning another operation requiring? Or do server processes hold onto workarea memory forever here as well? I'm prepared to accept P_A_T as the best thing since LMT, but so far I don't see it. At least not for all circumstances (as with LMT). I see it as a good thing in memory-constrained environments, but in environments with plenty of RAM I see it so far as a possible source of unnecessary instability with no upside. Thanks! -Tim on 9/29/03 5:10 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical).
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just took 75Mb, so if you think about it much longer, the total amount in use might grow and then I might only be able to give you 50% of your request! [server process]: OK! OK! I'll take it. (goes off and sadly mallocs only 75Mb of sort space in private memory)... Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever. I'd like 100Mb, so that's what I'll allocate... I mean, other than anthropomorphizing the whole thing, is this the general gist of it? Obviously, since the instance isn't a process and I'm not aware of another background process dedicated to this kind of thing, I'd say that it is a tally kept someplace in the SGA that is latched and updated by each server process in kind, but I thought the idea of a dialogue more amusing... :-) If this is the case, then if I have a server which is not constrained for memory, then why should I be concerned about space-efficiency? I tend to visit 2-3 different companies/organizations per week on a regular basis, and while I do find plenty of under-sized servers laboring under over-sized Oracle instances, I just as often find over-sized servers with acres of RAM, in which I'm certain entire DIMMs have never felt a volt of electricity. Typical example is a customer I started at two weeks ago, with 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance is demanding about 4 Gb of virtual memory, primarily due to PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of untouched RAM on this thing! Customer is being hosted by Oracle Apps hosting company and I
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just took 75Mb, so if you think about it much longer, the total amount in use might grow and then I might only be able to give you 50% of your request! [server process]: OK! OK! I'll take it. (goes off and sadly mallocs only 75Mb of sort space in private memory)... Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever. I'd like 100Mb, so that's what I'll allocate... I mean, other than anthropomorphizing the whole thing, is this the general gist of it? Obviously, since the instance isn't a process and I'm not aware of another background process dedicated to this kind of thing, I'd say that it is a tally kept someplace in the SGA that is latched and updated by each server process in kind, but I thought the idea of a dialogue more amusing... :-) If this is the case, then if I have a server which is not constrained for memory, then why should I be concerned about space-efficiency? I tend to visit 2-3 different companies/organizations per week on a regular basis, and while I do find plenty of under-sized servers laboring under over-sized Oracle instances, I just as often find over-sized servers with acres of RAM, in which I'm certain entire DIMMs have never felt a volt of electricity. Typical example is a customer I started at two weeks ago, with 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance is demanding about 4 Gb of virtual memory, primarily due to PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of untouched RAM on this thing! Customer is being hosted by Oracle Apps hosting company and I
RE: workarea_size_policy=auto and performance efficiency [was: Re:
Hi Tim and Richard Is there another modification to the process as follows [server process foote]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance ellison]: OK, what do you need? [server process foote]: Um, I'd like 100Mb, please? Mr Bowie has released a *lot* of records and I need them in date order, by record label and sleeve colour, grouped by NME review rating [instance ellison]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [server process gorman]: I'm done sorting!... [instance ellison]: shutup Tim you'll have to wait I'm talking to Mr Foote about the man who fell to earth damnit. Or in other words is there not a latch on this tally of allocated memory? This is a real question. Niall But I like the conversation idea anyway -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Foote Sent: 29 September 2003 14:30 To: Multiple recipients of list ORACLE-L Subject: Re: workarea_size_policy=auto and performance efficiency [was: Re: Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just took 75Mb, so if you think about it much longer, the total amount in use might grow and then I might only be able to give you 50% of your request! [server process]: OK! OK! I'll take it. (goes off and sadly mallocs only 75Mb of sort space in private memory)... Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Richard, I take it that your two points are...shall we say...enhancement requests, not current functionality? :-) Following up on the discussion of space-efficiency and tabling (for the moment) my questions about the performance-efficiency side of things. Yes, there certainly is an element of performance-efficiency to space-efficiency if it keeps you from swapping... ...anyway... Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even pretended to give memory back for the duration of the session, depending on the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. The hash and bitmap workareas have never had this functionality, as near as I can tell. So, I think that you're absolutely correct that sessions using WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it for a long time, essentially until they disconnect. Is this correct? Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered, the P_A_T algorithms only occur upon allocation of workarea memory. Is there any additional logic around de-allocation, possibly when the server process has finished using the workarea? Perhaps there is logic to de-allocate before beginning another operation requiring? Or do server processes hold onto workarea memory forever here as well? I'm prepared to accept P_A_T as the best thing since LMT, but so far I don't see it. At least not for all circumstances (as with LMT). I see it as a good thing in memory-constrained environments, but in environments with plenty of RAM I see it so far as a possible source of unnecessary instability with no upside. Thanks! -Tim on 9/29/03 5:10 AM, Richard Foote at [EMAIL PROTECTED] wrote: Hi Tim, There are couple of parts of the conversation we've missed out ;) Firstly, the server process when talking to the P_A_T instance should have said, What the hell is going on here, what do you mean I can't have my full 100M, this keeps on happening and it's just good enough. Get a bloody DBA to increase the P_A_T now because it's bloody obvious that the damn thing is set too low . (especially if the load you describe is typical). Secondly, the server process when talking to the non P_A_T should have said upon receiving the memory, ha, thanks, and guess what, no one else can have this memory back until I decide to rack off, and no I don't care if you're running short of memory, bugger ya, page for all I care These are very important parts of the conversion !! At the site I currently work at, we had 12G of memory which at peak load was just about running out. We have 1000-1200 sessions with (generally) only a small number active at a time but the sum of the PGAs was considerable and the major contributor. We had a number of disk sorts occurring although the SAS kept the number within acceptable limits. After setting the P_A_T, we now have a comfortable buffer of free memory (generally sitting around 1G), disk sorts have disappeared entirely (in four months, we've had 2 disk sorts) and hash joins have improved considerably. Based on my experience, P_A_T is the best thing Oracle has introduced since LMT !! Cheers Richard Foote - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 6:59 AM Referencing the article mentioned in this thread, I'd also like to understand exactly what is meant by the phrase [PGA_AGGREGATE_TARGET] leads to a more efficient use of RAM memory? From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance]: OK, what do you need? [server process]: Um, I'd like 100Mb, please? [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [another server process]: I'm done sorting! I've released the 100Mb I was using! Thanks... [instance]: OK, so now it is 149 other server processes using 1.19Gb at the moment. So, you wanted 100Mb? Well, since the amount in use is over 50% of the target, I have to scale your request back by 25%, so I'll let you take 75Mb [server process]: Well, OK. My execution plan was originally devised under the assumption that I'd have 100Mb of sort space in memory, but... [instance]: Hey pal! Take it or leave it! Someone else just
Re: workarea_size_policy=auto and performance efficiency [was: Re:
Hi! From what I've been able to determine about this functionality, efficient merely means space-efficient, not performance-efficient (i.e. Fewer cycles? Smarter cycles?). Is this correct? Does anyone know of anything in WORKAREA_SIZE_POLICY=AUTO which improves performance over WORKAREA_SIZE_POLICY=MANUAL? Yeah, my understanding is as well, that the performance improvements of automatic work area sizing policy over manual one is that when you don't have enough spare memory, Oracle is just able to calculate the best amount of work area memory under current circumstances - keeping operations running optimally, while not grabbing too much memory from others. There are columns ESTIMATED_OPTIMAL_SIZE and ESTIMATED_ONEPASS_SIZE in V$SQL_WORKAREA, thus when doing a sort/hash/etc operation, server process knows whether it would be reasonable to allocate few more megs of memory for an operation or wouldn't it help much. Optimal or workarea size estimation can be done either using CBO statistics or actual execution statistics if previous execution statistics for given SQL are still in library cache. So, using 10M for sort area instead of 8M isn't that helpful if it doesn't reduce number of passes in sort, thus it might be reasonable to leave this extra 2M for another smaller operation, which could benefit more from that. I set PGA_AGGREGATE_TARGET to 2,5G in my home computer, but only about 3-4MB of memory was used for my large sort for example. There are parameters _smm_max_size and _smm_min_size for setting boundaries for automatic Please correct me if I'm wrong, but I think the algorithm for WORKAREA_SIZE_POLICY=AUTO can be characterized something like: [snip] Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes: [server process]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever. I'd like 100Mb, so that's what I'll allocate... Manual allocation is probably much simpler and more lightweight operation, just note that Oracle is allocating sort area dynamically, in sizes of standard database block size or it's multiples. I mean, other than anthropomorphizing the whole thing, is this the general gist of it? Obviously, since the instance isn't a process and I'm not aware of another background process dedicated to this kind of thing, I'd say that it is a tally kept someplace in the SGA that is latched and updated by each server process in kind, but I thought the idea of a dialogue more amusing... :-) I guess there actually is no dialogue between processes when allocating work area, it's just the server process reading memory usage from SGA, calculating best allocation size based on memory usage and work area estimate, allocating memory and writing updated usage information back. There are some latches called SQL memory manager something, they might be the ones for keeping track workarea usage. Note that according to concepts guide, automatic work area policy works only for dedicated server connections, shared servers will still use old *_area_size parameters since their work areas are mostly stored in SGA anyway (with few exceptions like retained sort area etc). So this might have been the catch in the OCP exam.. If this is the case, then if I have a server which is not constrained for memory, then why should I be concerned about space-efficiency? I think space efficiency isn't that much of a problem in OLTP environment with lot's of extra memory. Then you just set your _areas to few megs and they probably won't ever be fully utilized. But in OLTP/OLAP mix or OLAP you can't just set everyones work area maximums to 500MB and hope for the best. Playing around with logon triggers session level work areas gets complicated as well.. I tend to visit 2-3 different companies/organizations per week on a regular basis, and while I do find plenty of under-sized servers laboring under over-sized Oracle instances, I just as often find over-sized servers with acres of RAM, in which I'm certain entire DIMMs have never felt a volt of electricity. Typical example is a customer I started at two weeks ago, with DRAMs always have volts of electricity, hundreds of millions of times in a second ;-) 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance is demanding about 4 Gb of virtual memory, primarily due to PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of untouched RAM on this thing! Yeah, I totally agree with you, a lot of companies have the spare money for buying a server 10x more powerful they need, instead of spending tiny amount of money tuning their databases/applications or doing an evaluation on their resource needs. Of course, in this situation I could recommend that PGA_AGGREGATE_TARGET be resized to 16-20Gb (as indicated in sizing advice in docs), but how would this functionality help performance in contrast to just generously setting SORT_AREA_SIZE et
Re: workarea_size_policy=auto and performance efficiency [was: Re:
I set PGA_AGGREGATE_TARGET to 2,5G in my home computer, but only about 3-4MB of memory was used for my large sort for example. There are parameters _smm_max_size and _smm_min_size for setting boundaries for automatic One more note on these two parameters - setting them didn't work in my 9.2.0.4 DB on Windows :( Tanel. -- 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).
