首页 > 数据库 > Oracle > IBM POWER7 AIX and Oracle Database performance considerations-10g&11g_1507249.1

IBM POWER7 AIX and Oracle Database performance considerations-10g&11g_1507249.1

Oracle 作者:rongshiyuan 时间:2014-03-27 10:51:01 0 删除 编辑
IBM POWER7 AIX and Oracle Database performance considerations -- 10g & 11g (Doc ID 1507249.1)

In this Document


Applies to:

AIX - Version 6.1 and later
3rd-Party Hardware
Oracle Database Products
IBM AIX on POWER Systems (64-bit)
This document is intended to provide clients with advice and website links to assist with IBM Power Systems running in an Oracle environment. It also includes information specific to POWER7 in an Oracle environment.
Additional links for further reading is also provided is provided inside this PDF doc:


This document is intended to provide clients with advice and website links to assist with IBM Power Systems running in an Oracle environment. It also includes information
specific to POWER7 in an Oracle environment. IBM is not aware of any POWER7-specific Oracle issues at the time of this writing. Most issues that show up on POWER7 are
the result of not following best practices advice that applies to all Power Systems generations.

The information also includes discussions about Oracle Non-Real Application Clusters (NON-RAC) and RAC.


Document is generated by:

IBM Oracle International Competency Center (ICC)
August 22, 2012 email address:

Doc location: 


This document is intended to provide clients with advice and website links to assist with IBM Power Systems running in an Oracle environment. It also includes
information specific to POWER7 in an Oracle environment. IBM is not aware of any POWER7-specific Oracle issues at the time of this writing. Most issues that show up
on POWER7 are the result of not following best practices advice that applies to all Power Systems generations.

The information also includes discussions about Oracle Non-Real Application Clusters (NON-RAC) and RAC.

Below, shows links to important documents to become familiar with.

Must read first - Oracle and IBM references

Oracle Readme/Release Notes Main Page: The Readme/Release Notes are dynamic documents updated online with new information 

Above is the main Oracle page from which you can select an Oracle product for specific information on a given platform (installation information, AIX patches,
tuning, implementation suggestions, etc.)

Oracle 11gR2 Readme/Release Notes 

Above is the Oracle? Database Release Notes 11g Release 2 (11.2) for IBM AIX on POWER Systems (64-Bit) Part Number E23560-03

Oracle 10gR2 Readme/Release Notes 

Above is the Oracle? Database Release Notes 10g Release 2 (10.2) for AIX Part Number B19074-15

Minimum Software Versions and Patches Required to Support Oracle Products on IBM Power Systems

My Oracle Support (MOS) ID 282036.1 (
(registration required)

Oracle Architecture and Tuning on AIX v2.20 

AIX tuning reference guide available on Techdocs

Oracle Real Application Clusters on IBM AIX –Best practices in memory tuning and configuring for system stability 

Above is a RAC tuning and configuration Guide

RAC tuning and configuration Guide Oracle Real Application Clusters (RAC) and Oracle Clusterware Interconnect Virtual Local Area Networks (VLANs) Deployment Considerations 

Above is a RAC and VLAN deployment guide 

Managing Raw Disks in AIX to use with Oracle Automatic Storage Management (ASM)

MOS ID 1445870.1 My Oracle Support (MOS -


Platform Migration to AIX 

Above is a cross-platform migration reference guide available on Techdocs


Must Read for Oracle 11gOracle Database Online Patching on AIX

Oracle Database Online Patching on AIX 

Above are AIX fixes and Oracle patches that need to be used with the Oracle Database Online patching feature

Oracle’s USLA HEAP patches available on AIX 

Addresses increased per-process memory consumption


Oracle Database available on AIX 

Above is Certification information


Oracle DB & RAC 11gR2 on IBM AIX: Tips and Considerations 

Above is 11gR2 Planning and implementing.


Oracle DB & RAC 10gR2 on IBM AIX: Tips and Considerations 

10gR2 Planning and implementing

Additional IBM Information 

Review Recent ICC Flashes on Techdocs – the Technical Sales Library 

Technical sales support database


Guide to Multiple Page Size Support on AIX 5L Version 5.3 (note: general support for 5.3 ended April 2012) 

Useful information on large page configuration



Large page size Review Oracle Large Page Usage for Larger page size has often provided performance

Existing environment statistics capture -- customers should consider creating a historical comparison baseline to aid in
solving a perceived change in performance, for example, when a new system seems slower than the old one.

Customer should determine what is most applicable to capture in their environment (that is, AWR or PerfPMR types of reports)

JAVA Performance on POWER7 - Best Practice 

Information about migrating JAVA applications from POWER5/6 to POWER7


Oracle DB 11gR2 best practices for IBM AIX

This section is a summary of best practices for standalone Oracle DB 11gR2 instances on IBM AIX 6.1 and 7.1 and POWER7. With the exception of references to Symmetric
Multi-threading, quad-threaded mode (SMT4), all of the best practices apply to POWER6 as well. While the primary focus is on standalone Oracle 11gR2 with
filesystem-based storage, standalone Automatic Storage Management (ASM) is also addressed. These best practices apply to Oracle DB as well, and where they
differ from those for Oracle DB 11gR2, patches specific to Oracle DB are noted.

Detailed Oracle/AIX best practices documents, including Damir Rubic’s Oracle Architecture and Tuning on AIX version 2.20, are available on IBM Techdocs: 

Detailed instructions for installing and configuring Oracle 11gR2 RAC with ASM are provided in Release Notes: Oracle Real Application Cluster 11g Release 2 (
On IBM AIX, available from Oracle. The details of ASM installation and configuration in the document are mostly applicable to standalone ASM as well.

The following pages discuss memory, CPU, I/O, network, and miscellaneous settings. In addition, we list the AIX APARs required for Oracle 11gR2, the Oracle patches for
11gR2 on AIX 6.1 and 7.1, the Oracle patches for and 11gR2, as well as recent suggestions and open issues.


Specifications for kernel settings and Oracle large page usage are:

Kernel Settings

Kernel settings are listed in Table 2. These are commonly suggested values.


Parameter           Proposed Value              AIX 6.1 Default        AIX 6.1 Restricted        AIX 7.1 Default            AIX 7.1 Restricted

minperm%                3                                   3                           No                               3                                 No

maxperm%              90                                 90                          Yes                             90                               Yes

maxclient%              90                                 90                          Yes                             90                               Yes

strict_maxclient          1                                   1                           Yes                              1                               Yes

strict_maxperm          0                                   0                           Yes                              0                               Yes

lru_file_repage           0                                   0                           Yes                            N/A                               N/A

lru_poll_interval        10                                  10                           Yes                            10                               Yes

minfree                 960                                 960                           No                            960                                No

maxfree               1088                              1088                            No                          1088                               No

page_steal_method    1                                    1                          Yes                                1                              Yes

memory_affinity         1                                    1                          Yes                                1                              Yes

v_pinshm                  0                                    0                            No                                0                               No

lgpg_regions              0                                    0                            No                                0                               No

lgpg_size                   0                                   0                             No                               0                                No

maxpin%                  80                                 80                            No                               90                               No

esid_allocator              1*                                 0                            No                                 1                               No

* The default value of 1 for esid_allocator enables terabyte segment aliasing, reducing addressing lookasides. This value may be set to 1 in AIX 6.1 and is suggested for

In general, AIX support suggests AIX 7.1 defaults for Oracle.

Three noticeable changes from AIX 6.1 to AIX 7.1 are:

· The elimination of the lru_file_repage tunable
· The default value of 90 for maxpin%, increased from 80% in AIX 6.1

Oracle Large Page Usage

Spécifications for Oracle large page usage are:

· AIX 6.1 and 7.1 support three or four page sizes, depending on the hardware: 4 KB (default), 64 KB (medium), 16 MB (large), and 16 GB(huge). All four page sizes are
  supported by Power Systems from POWER5+ firmware level 240-202 onward.

· Page sizes 64 KB and 16 MB have been shown to benefit Oracle performance by reducing kernel lookaside processing to resolve virtual to physical addresses.
  Oracle 11g uses 64 KB pages for dataspaces by default.


· This is the default. This means that the SGA is not pinned in memory.

· AIX performance support generally suggests not to pin SGA.

· Automatic Memory Management (AMM) will use 64 KB pages for SGA if memory is available.

· This is the suggested value, since it has been found that 64 KB pages yield nearly the same performance benefit as 16 MB pages and require no special

· Oracle with Oracle patch 7226548 will also use 64 KB pages for the SGA.


· AIX parameters to enable pinned memory and 16 MB large pages:

  vmo –p –o v_pinshm=1 (allows pinned memory—requires reboot)

  vmo –p –o lgpg_size=16777216 –o gpg_regions=number_of_large_pages where number_of_large_pages=INT[SGA size -1)/16MB)]+1

  (above courtesy of Damir Rubic, IBM ATS)

· Capabilities required to allow Oracle to use 16 MB large pages (implement as root):

#chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle

( and Oracle My Oracle Support note: 372157.1).

· AMM can be used with pinned 16 MB pages, provided the formula for calculating the number of large pages (above) is modified to number of large pages=memory_max_target+1.

· With Oracle, patch 7226548 is also required in order to use 16 MB pinned pages.

Using 64 KB pages for data, text, and stack regions (applies to Oracle as well)

· 64 KB page size for data, text, and stack regions is useful in environments with a large (for example. 64 KB+) SGA and many online transaction processing
  (OLTP) users. For smaller Oracle instances, 4 KB is sufficient for data, text, and stack.

· 64 KB page use for data, text, and stack is implemented separately from 64 KB pages for the SGA, and is done by means of an environment variable exported on
   behalf of the Oracle user.



CPU specifications are:

· Symmetric multi-threading (SMT) mode: POWER7 supports SMT4, and this is the AIX default. AIX/Oracle performance support encourages starting with the default.

· Virtual processor folding: This is a feature of Power Systems in which unused virtual processors are taken offline until the demand requires that they be activated. The
  default is to allow virtual processor folding, and this should not be altered without consulting AIX support.

· Specific to POWER7 SMT4: Certain Oracle 11G parameters, including DB_WRITER_PROCESSES and PARALLEL_MAX_SERVERS, are partly derived from CPU_COUNT, and CPU_COUNT is equal by default to the number of logical CPUs.          CPU_COUNT automatically adjusts to changes in virtual processor count and to SMT mode, up to three times the value on startup. Note that, when migrating from single-threaded platforms to Power Systems, or from  POWER5 or        POWER6  to POWER7 with SMT4, the value of CPU_COUNT will also increase, affecting DB_WRITER_PROCESSES, PARALLEL_MAX_SERVERS, and other dependent parameters. Queries that are sensitive to a degree of  parallelism might       change behavior as a result of migration to POWER7. We suggest reviewing the PARALLEL_MAX_SERVERS parameter after migration, but to set DB_WRITER PROCESSES to default.


I/O specifications are:

· If ASM is not used, max interpolicy striping (also known as pp spreading or poor man’s striping) is suggested when logical volumes are created. To get the most benefit from spreading physical partitions across the LUNs, use a small     physical partition size, for example, 32 MB or 64 MB.

Async I/O is used even with Concurrent I/O (CIO)

· With AIX 6.1 and 7.1, start with the asynchronous I/O defaults. With AIX 6.1, there is a new implementation of AIO. AIO kernel extensions are loaded at system boot (always loaded), AIO servers stay active as long as there are service
requests, and the number of AIO servers is dynamically increased or reduced based on demand of the workload. The aio_server_inactivity parameter defines after how many seconds idle time an AIO server will exit. AIO tunables are now
based on logical CPU count, and hence it is usually not necessary to tune minservers, maxservers, and maxreqs as in the past.

· Note that in AIX 6.1, there are two tunables for minservers and maxservers, aio_minservers/aio_maxservers for legacy threads, and posix_aio_minservers/posix_aio_maxservers for posix threads. Oracle uses legacy threads.

· Only increase aio_maxservers or posix_aio_maxservers with ioo –p –o (the default is 30 per logical CPU) if pstat -a | fgrep aio or ps -k | fgrep aio show that you are continually using maxservers.

· Oracle parameter (init.ora)

· disk_asynch_io = TRUE (default value)

Buffered file I/O on JFS2

· The default filesystemio_options=ASYNC

· In this case all data spaces, redo log file systems, and control file systems are using the kernel buffers rather than writing directly to disk.

· In this case, it does not matter whether redo log file systems and control file systems are 512 b or 4 KB block size file systems.

· Oracle on AIX/Power best performance is, however, usually achieved using CIO (though there are exceptions).

Concurrent I/O (CIO) on JFS2

· Set the Oracle parameter filesystemio_options=SETALL, or mount the filesystems (other than dump devices) with the CIO option. It is not necessary to
both SETALL and mount filesystems with the CIO option, although no harm is done either way. Metalink note: 272520.1 indicate that mounting with CIO is
needed, while IBM believes it is not needed. IBM is working with Oracle to fix the metalink note.

· If using CIO with SETALL, CIO mount or both, you must create separate file systems for redo logs and control files (or a single filesystem for both), with an
agblksize of 512 rather than the default 4 KB.

· The ioo parameter fs_fastpath accelerates CIO. It is enabled by default in AIX 6.1 and 7.1.

IBM mount advice for database files:

· Data files: Use CIO filesystemio_options=SETALL, and default agblksize (4k); mount with no options.

· Redo logs: Create with agblksize of 512 and mount with no options. With SETALL, IBM is doing direct I/O for Redo logs.

· Control files: Create with agblksize of 512 and mount with no options. With SETALL, IBM is doing direct I/O for Redo logs.

· Archive logs: Mount -o rbrw . Do not use CIO; use the jfs2 rbrw option 

· Dumps: Mount –o rbrw

· The mount option noatime, suggested for Oracle 10g, is no longer required

IOO tunables j2_nBufferPerPagerDevice and j2_dynamicBufferPreallocation:

· Do not change these unless there is a high delta in vmstat –v external pager filesystem I/Os blocked with no fsbuf. If this value is high, first increase j2_dynamicBufferPreallocation from 16 (16k slabs) to 32; monitor. If increasing
  this does not help, then consider raising the value of j2nBufferPerPagerDevice which is the starting value for dynamic buffer allocation.

· See help pages for information about these parameters. Do not change AIX 6.1 or 7/1 restricted tunables without the advice from IBM AIX support. In AIX 6.1, j2_nBufferPerPagerDevice is a restricted tunable, while   j2_dynamicBufferPreallocation is not.

Here are some default values for three ioo parameters:

· j2_dynamicBufferPreallocation=128

· numfsbufs=1024 (legacy jfs)

· maxpgahead=16 (legacy jfs)

ASM considerations for standalone Oracle 11gR2:

· For identifying, renaming, and securing ASM raw devices, see Managing Raw Disks in AIX to use with Oracle Automatic Storage Management (ASM).

· ASM will use asynchronous I/O by default, so filesystemio_options=ASYNC (default) is appropriate.

· In the standalone use of ASM, unlike RAC, hdisks and hdiskpower devices do not need to have Small Computer System Interface (SCSI) reservation disabled.

The following initialization parameters need to be adjusted for ASM:

· Add 16 to the value of processes

· Add an additional 600 KB to the value of large pool size

· Add to shared pool size the aggregate of the values returned by these queries:

· SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;

· SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b WHERE;

· SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE WHERE status=’ONLINE’;

· For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool, plus 2 MB · For disk groups using normal redundancy, every 50 GB of space needs 1 MB
of extra shared pool, plus 4 MB

· For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool, plus 6 MB

· Source: 


This section outlines the minimum values applicable to network configurations.

Kernel configurations

These values are generally suggested for Oracle, and can be considered as starting points:

· sb_max >= 1MB (1048576) and must be greater than maximum tpc or udp send or recvspace (if you are using RAC and very large udp_recvspace, you might need to increase sb_max)

· tcp_sendspace = 262144

· tcp_recvspace = 262144

· udp_sendspace = db_block_size * db_file_multiblock_read_count

· udp_recvspace= 10 * (udp_sendspace)

· rfc1323 = 1 (see Recent suggestions and open issues)

· Ephemerals (non-defaults suggested for a large number of connecting hosts or a high degree of parallel query; also to avoid install-time warnings)

· tcp_ephemeral_low=9000

· tcp_ephemeral_high=65500

· udp_ephemeral_low=9000

· udp_ephemeral_high=65500

Jumbo frames are Ethernet frames larger than the standard maximum transmission unit (MTU) size of 1500 bytes. They can be up to 9000 bytes. They are used to reduce the
number of frames to transmit a given volume of network traffic, but they only work if enabled on every hop in the network infrastructure. Jumbo frames help to reduce
network and CPU overheads.


Other specifications follow:

ulimits (smit chuser or edit /etc/security/limits to create a stanza for Oracle)

· -1 (unlimited) for everthing except core

Maximum number of PROCESSES allowed per user (smit chgsys)

· maxuproc >= 2048; 16 KB is a commonly suggested value for Oracle Environment variables

· AIXTHREAD_SCOPE=S (set in Oracle profile).


· There is an option to ldedit the Oracle binaries so they use 64 KB pages directly. Note that whenever a patch is applied or an Oracle relink is performed, this ledit will have to be performed again.

· # ldedit –btextpsize=64k –bdatapsize=64k –bstackpsize=64k $ORACLE_HOME/bin/oracle.

Disk and adapter resources

· Hdisk – lsattr –El hdisk<>

· Queue depth might vary among 8, 16, 20, and 24, depending on the storage vendor. A queue depth of 2 on SAN devices usually indicates a driver mismatch, but is the default for Hitachi HDS on AIX and should be increased to 8 as a
starting point. Queue wait and queue overflow detected through iostat –Dl might indicate a need to increase queue depth.

· max_transfer might need to be adjusted upward depending on the largest I/O requested by Oracle

· A typical starting point for Oracle on AIX is 0x100000

· FC Adapter – lsattr –El fcs<> and fcstat -e

· max_xfer_size should at least equal max_transfer at the hdisk level.

· num_cmd_elems might need to be increased if fcstat -e reports a persistent nonzero value for No Command Resource Count.

· If fcstat –e reports a persistent, non-zero value for No DMA Resource Count contact support.

AIX fixes for Oracle and 11gR2

AIX fixes for Oracle and 11gR2 follow. Note that all AIX 6.1 patches apply to these Oracle versions.




· IZ97088: SMT4 scheduling fix / improvement included in AIX 6.1 patchset 8




· IV03903: Address space lock contention issue

· IZ76101: Scale Light Load borrowing (Multi-SRAD Scaling issue) included in AIX 6.1 patchset 8

· IV11857: Slow startup of AIO processes (workaround: use kernelized AIO, or increase aio_minservers and set aio_server_inactivity to 86400.

· IZ71987 (AIX6.1), IZ67445 (AIX 5.3 TL12): Paging space growth may occur unexpectedly with 64 KB (medium) pages enabled

· IV11261 - SYSTEM CRASH IN AS_FORK_ALIAS IF ESID_ALLOCATOR IS ENABLED: only applies to cases in which large segment aliasing is enabled using the esid_allocator vmo parameter and, in addition, a 32-bit application such as Java is
being used

. IZ96441: LOCKL PERFORMANCE ISSUE: Oracle ASM takes a long time to initialize when there are lots of users configured.

. IZ97923: SHLAP64 UNABLE TO PROCESS ORACLE REQUEST LEADING TO KERNEL HANG: shlap64 unable to process Oracle request leading to kernel hang

. IV26272: REDUCE EARLY WORKING STORAGE PAGING: Paging of computational storage pages is occurring when a high percentage of memory is being used for the file cache.

Oracle patches to check in the context of AIX

Oracle patches to check in the context of AIX are:

Patches for Oracle

· Oracle patch 7226548: enables 64 KB and 16 MB large page support

· Bug 7568734 AIX: Sporadic spikes of 'log file sync' on AIX with heavy commit concurrency

· Bug 6784747: Wasted memory in koh-kghu allocations. Free extents of memory might not get used


Patches for Oracle pre-

· Oracle patch Bug 6784747 - WASTED SPACE - KOH-KGHU CALL ALLOCATION

Patches for Oracle 11gR2

Mutex Wait

· Bug 10411618: ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS - Note: PSU breaks this patch and additional patch (12431716) is required

· Master Note: WAITEVENT: "library cache: mutex X" [ID 727400.1]

· Bug 12740358: DBMS_UTILITY.FORMAT_CALL_STACK is still slower than 10 GB. This issue can be observed in AIX as a high number of system calls – millions – to functions like “sigaction()”.

· Bug 9842771: Wrong SREADTIM and MREADTIM statistics in AUX_STATS$

· Bug 12596494: Generally higher CPU Usage in than

· Bug 12412983 - AIX -"asynch descriptor resize" wait not necessary on AIX; Please also review Metalink note id - 1318709.1 - Things to check when seeing long log file sync wait times

· Bug 11689561: Hash join consumes more CPU in IBM AIX. The issue is fixed in (future patchset)

· Bug 13877328: Database performance slows down steadily over time (Reference Metalink Note 1467807.1: Performance diminishes over time on AIX 7.1 due to Large Segment Aliasing)

· Bug 13840529: Database hang on cache buffer chains and row cache objects (generic bug affecting all platforms)

· Bug 9495594: PERFORMANCE DEGRADATION RUNNING ANONYMOUS PLSQL BLOCKS IN 11.2 VS 10.2 (generic bug affecting all platforms)


· Oracle, required USLA Heap patch: · Bug 13443029: (requires AIX 6.1 TL07 SP2 or AIX 7.1 TL01 SP2)


· Bug 10190759 (disables hot patching)

Recent suggestions and open issues

Recent suggestions and open issues are:

· Hardware prefetch: The POWER7 chip has the ability to recognize streaming memory access patterns with a unit stride or stride N, and initiate the dcbt, or dcbtst prefetch instructions automatically. It controls how aggressive the hardware will
prefetch (that is, how many cache lines will be prefetched for a given reference), and is controlled by the Data Streams Control Register (DSCR). For Oracle and JAVA workloads, due to irregular memory accesses, it has sometimes been found beneficial to disable hardware streaming memory prefetch. Aggressive prefetching can inhibit performance because the prefetched lines might not be referenced. The workaround is to turn off Hardware Prefetch using dscrctl -b -n -s 1 (courtesy of Dale Martin, IBM ATS).

· Large segment aliasing allows each memory segment lookaside buffer to address up to 1 TB of memory, reducing segment lookaside buffer faults and improving memory access. This is enabled by default on AIX 7.1, and is enabled using "vmo –p –o esid_allocator=1" in AIX 6.1. A recently discovered problem with Oracle 11GR2 and large segment aliasing concerning a related tunable, shm_1tb_unsh_enable is currently being addressed. This problem is specific to Oracle on AIX 6.1 and AIX 7.1 and is addressed in Bug 13877328.

· noatime file system mount option: The Oracle/AIX bug (9548634: IBM/AIX: EXPENSIVE GETCWD() CALLS FROM SNLFNCDIR() ) which necessitated the mount option has been fixed in The current suggestion is to use the noatime
mount option for the file system hosting the Oracle binaries only, and only on Oracle database versions 10.2.0.x and 11.1.0.x.

· rfc1323=1 is a long-standing network tuning suggestion for Oracle on AIX, although the default remains 0 in AIX 6.1 and 7.1. A network retransmission latency issue has recently been discovered when rfc1323=1 on AIX 6.1 TL6 and APAR IV00755 is also present.

Additional Information

This Technical Note was authored by Peter Barnett, Sanjay Ruprell and Wolfgang Tertel, all from IBM. This document will be a chapter in a planned new publication from the IBM ITSO organization. It will be published as an ITSO Redbook ( For more information on this Technical Note, please send your questions to the IBM Oracle International Competency Center at





来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量