Large Memory Footprints on AIX

Connor Mcdonald-一位Oracle极客为我们分享了一个AIX平台上11g独享服务进程内存占用过量的问题,该问题最后被确认为Bug”11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I”,相关文档如下:

 

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

Bug 9796810: 11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I

Bug 10190759: PROCESSES CONSUMING ADDITIONAL MEMORY DUE TO ‘USLA HEAP’

可以看到上述问题仅发生在从9i/10g升级到11g后,作为一个已确认的升级Bug值得我们大家去关注;最近几年这样的升级会越来越多,同时希望该Bug能在11.2.0.3中修复。

实际上我在10.2.0.3上就遇到过类似的Process Large Footprints问题:用户在打上一个one-off patch[6110331]后单个server process的rss量明显上升,主机的内存使用量大幅提高,虽然这个问题同样提交了SR,但最后没有确认为Bug;用户试图询问Oracle GCS关于rss上升的原因,但语焉而不详。

Search Criteria:AIX 11.2

Memory Footprint For Dedicated Server Processes More Than Doubled After 11g Upgrade On AIX Platform (Doc ID 1246995.1)

1. Have you installed the patch 10190759 ?

Review the note:
Memory Footprint For Dedicated Server Processes More Than Doubled After 11g Upgrade On AIX Platform (Doc ID 1246995.1)

If you have not installed the patch ?
–>>there is one available for 11.2.0.2.0, 11.2.0.2.2, 11.2.0.2.3

If you need me to review the patches you have installed you can upload the opatch listing?

opatch lsinventory -patch -detail

2. If you have already installed the patch 10190759 then

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.
Patch Name or Number: 10190759

 

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.

This is a known bug: BUG:10190759 – PROCESSES CONSUMING ADDITIONAL MEMORY DUE TO ‘USLA HEAP’

Install  Patch: 10190759


Posted

in

by

Tags:

Comments

3 responses to “Large Memory Footprints on AIX”

  1. admin Avatar
    admin

    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 Avatar
    admin

    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 Avatar
    admin

    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

Leave a Reply

Your email address will not be published. Required fields are marked *