Large Memory Footprints on AIX

Comments

1. admin says

   2011/03/18 at 12:54

   Memory Footprint For Dedicated Server Processes More Than Doubled After 11g Upgrade On AIX Platform [ID 1246995.1]

   Applies to:
   Oracle Server – Enterprise Edition – Version: 11.2.0.1 and later [Release: 11.2 and later ]
   IBM AIX on POWER Systems (64-bit)
   Symptoms
   o After upgrade to Oracle release 11g (11.2.0.1.0 or 11.2.0.2.0), the machine hung due to excessive memory utilization increase for each dedicated server process.

   o svmon on oracle OS process id shows size of USLA, User-Space Loader Assistant, heap of about 7M bytes running Oracle release 11.2.0.1.0 on AIX platform:

   svmon -P PID – where PID is an Oracle process id

   Oracle Release -> (work USLA heap times 4k pages size)

   11.2.0.1.0 -> 7M bytes
   11.1.0.7.0 -> 60KB
   10.2.0.4.0 -> 420KB

   Changes
   Upgrade to Oracle release 11g (11.2.0.1.0 or 11.2.0.2.0) from an earlier release 10g or 9i running on AIX platform.
   Cause
   This is bug 10211065 which is set to duplicate of base bug 9796810 and 10190759:

   Bug 10211065.-P Base Bug 9796810
   Abstract: MEMORY FOOTPRINT FOR DEDICATED SERVER PROCESSES MORE THAN DOUBLED AFTER 11G UGP

   Bug 9796810.-P Base Bug 10190759
   Abstract: 11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I

   Bug 10190759.-P
   Abstract: PROCESSES CONSUMING ADDITIONAL MEMORY DUE TO ‘USLA HEAP’

   Oracle process consumes significantly more memory compared to earlier
   releases. Utilizing the svmon command on AIX it can be seen that most of
   this memory additional memory is under “Work USLA Heap” category.

   Solution
   If you find the memory footprint on your oracle processes has significantly
   increased compared to previous releases and you are running on the AIX
   platform you have likely encountered this issue.

   To verify if you are hitting this issue, you can collect following information:

   a) Choose one PID for a oracleSID process then execute:

   svmon -P PID
   genld -ld | more | /PID
   procldd PID
   procmap PID

   b) cd $ORACLE_HOME/bin
   ls -lL oracle
   ldd oracle

   The additional memory seen allocated to oracle processes in the 11.2 release
   is a consequence of the additional link options added to the oracle link
   line, -bexpfull and -brtllib. The two link options were specifically added
   in 11.2.0.1 to support the online patching feature.

   Changes in the make file have been implemented such that you
   can relink without these options (-bexpfull and -brtllib) to avoid
   additional memory overhead incurred by adding these options.
   These changes are available via a one-off patch.

   To check for Patch Availability from My Oracle Support:

   1) Enter Patch Name or Number: 10190759
   2) Enter Platform: IBM AIX on POWER Systems (64-bit)

   Note that the default behavior to install the patch, opatch apply, or link oracle
   is WITHOUT the options brtllib and bexpfull:

   make -f ins_rdbms.mk ‘EXP_OPTS=$(EXP_OPTS_OFF)’ ioracle
   or
   make -f ins_rdbms.mk ioracle

   To relink oracle with these options you can execute the following on the command line to accomplish this:

   make -f ins_rdbms.mk ‘EXP_OPTS=$(EXP_OPTS_ON)’ ioracle

   The fix provided here is only seen as interim solution for the problem until such time
   as a fix is provided for the run time loader on AIX by IBM.

   回复
2. admin says

   2011/03/18 at 12:56

   Bug 9796810: 11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I
   Hdr: 9796810 11.2.0.1 RDBMS 11.2.0.1 UNKNOWN PRODID-5 PORTID-212 10190759
   Abstract: 11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I

   *** 06/09/10 12:54 pm ***
   —-
   SR3-1826943511

   PROBLEM:
   ——–
   While testing 11g, the customer observed that dedicated server processes
   using significantly more memory on 11g compared to previous versions (9i and
   10g).

   Using svmon to monitor process memory segment after initial DB connection,
   was able to observe that typical USLA heap size jumped from ~0.4M in 10g to
   7.0M in 11g. Able to observe this behavior on several 11g DBs

   They will not be able to upgrade 1,000+ user production DB to 11g due to
   increased memory footprint of 11g

   DIAGNOSTIC ANALYSIS:
   ——————–
   This appears to be an AIX specific issue.

   The following three bugs look similar to this, the first bug being the
   closest match:

   Bug 8785428 looks like a candidate although it was logged against 11.1.0.7.
   We need more information to at least determine this. A new bug will guide
   towards

   Bug 9651455 may also be relevant here but the suggested workaround/fix of it
   may not apply to this situation as we have not identified the culprit in the memory growth.

   Bug 8914790 is fixed in 11.2 so it doesn’t seem to apply here.

   回复
3. admin says

   2011/03/18 at 12:56

   Large Memory Footprints and Oracle9i Tuning on HP-UX

   You may see a significant increase in memory allocation while running applications with an Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2) ) executable, compared to memory allocation with an Oracle8i executable on HP-UX. There are two potential causes for this issue:

   * The Oracle initialization parameter CURSOR_SPACE_FOR_TIME is changed from the default value FALSE to TRUE.
   * Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) changes the default setting for virtual memory data pages from D (4KB) to L (1GB) on HP-UX.

   Persistent Private SQL Areas and Memory When a user submits a SQL statement to Oracle to be processed, Oracle automatically performs the following memory allocation steps:

   1. Oracle checks the shared pool present in the Oracle SGA (Shared Global Area) to see if a shared SQL area already exists for an identical statement. If a shared SQL area exists, then Oracle uses that shared SQL area to execute subsequent new instances of the statement. If a shared SQL area does not exist, then Oracle allocates a new shared SQL area in the shared pool for the SQL statement.

   2. Oracle also allocates a private SQL area on behalf of the user session.

   Private SQL areas contain data such as bind information and runtime memory structures for processed SQL statements. Private SQL areas are also where a parsed statement and other information for statement processing are kept. A cursor is a handle or name for a private SQL area; the cursor indicates that the private SQL area associated with the cursor remains in use.

   Each user that submits the same SQL statement has a cursor that uses a single shared SQL area. Thus many private SQL areas can be associated with the same shared SQL area. If a user session is connected through a dedicated server, then private SQL areas are located in the server process PGA (Process Global Area). However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

   The Oracle initilaization parameter CURSOR_SPACE_FOR_TIME specifies whether a SQL cursor can be deallocated from the library cache to make room for a new SQL statement. When this parameter is set to TRUE, then Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) can only deallocate a shared SQL area from an Oracle library cache when all application cursors associated with the SQL statement are closed. Setting the parameter CURSOR_SPACE_FOR_TIME to TRUE also prevents the deallocation of private SQL areas associated with open cursors, thus making the user’s private SQL area persistent.

   Setting the CURSOR_SPACE_FOR_TIME initilaization parameter to TRUE, offers the following advantages:

   * It accelerates SQL execution calls, because each active cursor’s SQL area is present in memory and never aged out.

   * It improves application performance, as Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) is allowed to bypass the procedure to verify if a shared SQL area is in the library cache. By retaining private SQL areas between SQL statement executions, it also helps to save cursor allocation and initialization time.

   Setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME to TRUE in Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) causes the following disadvantages:

   * It increases the memory requirements of user processesdue to an increased memory allocation for the persistent private SQL areas, in comparison to Oracle8i.

   * It significantly increases cursor memory, according to lab tests with Oracle Applications, which leads to larger memory allocations for Oracle9i shadow processes in comparison with Oracle8i.

   If you keep or change the value of CURSOR_SPACE_FOR_TIME parameter to FALSE, be aware that you can experience degraded overall SQL execution and performance. Setting CURSOR_SPACE_FOR_TIME to FALSE can result in rapid deallocationof shared SQL areas from the library cache.

   Default Large Virtual Memory Page Size By default, the Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) executable uses the largest virtual memory page size setting allowable by HP-UX for allocating process-private memory. It is designated by the value “L” (largest), and is currently 1 GB on HP-UX 11i. This value is set as one of the LARGE_PAGE_FLAGS options while linking an Oracle executable.

   When the virtual memory page size is set to “L”, the HP-UX operating system allocates the available process-private memory to pages of 1MB, 4MB, 16MB and so on, until it reaches the 1 GB limit, or it reaches the total amount of allocated memory. If you allocate enough memory to the Oracle PGA (Process Global Area) for the OS to be able to allocate memory in larger data page size units, then the OS allocates the maximum page size at once. For example, if you allocate 48MB for the Oracle PGA, then your system may have either 3 pages each of 16MB, or a series of pages in unit sizes with the smaller multiples — for example, 4 (1MB) pages, 3 (4MB) pages and 2 (16MB) pages. If you allocate 64MB to the PGA, then the OS will allocate one page of 64MB, as the data page unit size multiple matches the available memory.

   In general, large memory pages yield better application performance by reducing the number of virtual memory translation faults that need to be handled by the operating system. This makes available more CPU resources for the application. Large pages help to reduce the total number of data pages needed to allocate the process-private memory. This reduction decreases the likelihood of Translation Lookaside buffer (TLB) misses at the processor level.

   However, if applications are constrained in memory and tend to run a very large number of processes, then this drastic page size increase may lead processes to indicate large memory allocations, followed by an “out of memory” error. In those cases, you must lower the page size to a value between the “D” (default) size of 4K and the “L” (largest) size of 1GB.

   The tradeoff for reducing the page size is a greater probability of TLB misses, higher CPU utilization, and a performance overhead.

   Lab tests show that with the lowest page size (4KB) setting, CPU utilization is as high as 20%. With the highest setting of “L”, the memory consumption is 50% higher than that with a “4M” setting. In cases where the system shows memory constraints, Oracle Corporation recommends that you set the page size appropriately for a particular type of application, within the constraints of available memory resources.

   For example, an application that has problems with the “L” setting may show reasonable performance with a 4MB virtual memory page setting.

   Tuning Recommendations Oracle Corporation recommends the following steps to address tuning for the increased memory allocation required for Persistent Private SQL Areas and Large Virtual Memory Page Sizes:

   * Keep the value for CURSOR_SPACE_FOR_TIME to TRUE, unless the system indicates library cache misses while running the application. In that case, the shared pool may be small enough to hold the SQL areas for all concurrent open cursors.

   * Decrease the virtual memory data page size for the Oracle9i Release 1 (9.0.1) (and Oracle9i Release 2 (9.2)) executable as necessary. Alter the the page size setting with the following command:

   /usr/bin/chatr +pd newsize $ORACLE_HOME/bin/oracle

   where the variable newsize represents the new value of the virtual memory page size.
   Display the new setting with the chatr command:

   /usr/bin/chatr $ORACLE_HOME/bin/oracle

   回复