In this Document
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: firstname.lastname@example.org
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 (https://support.oracle.com)
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 - https://support.oracle.com)
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 188.8.131.52 available on AIX
Above is 184.108.40.206 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 10.2.0.4 as well, and where they
differ from those for Oracle DB 11gR2, patches specific to Oracle DB 10.2.0.4 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 (220.127.116.11)
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 10.2.0.4 and 11gR2, as well as recent suggestions and open issues.
Specifications for kernel settings and Oracle large page usage are:
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.
LOCK_SGA = FALSE
· 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 10.2.0.4 with Oracle patch 7226548 will also use 64 KB pages for the SGA.
LOCK_SGA = TRUE
· 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
(http://www.ibm.com/developerworks/forums/servlet/JiveServlet/download/747-313057-14417535-361419/Oracle_Tuning_on_AIX.pdf 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 10.2.0.4, 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 10.2.0.4 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.
· $ export LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K oracle
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:
· 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 a.group#=b.group#;
· 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
This section outlines the minimum values applicable to network 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)
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).
· LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K $ORACLE_HOME/bin/oracle.
· 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 10.2.0.4 and 11gR2
AIX fixes for Oracle 10.2.0.4 and 11gR2 follow. Note that all AIX 6.1 patches apply to these Oracle versions.
· IZ88711: BIND64 CORES WITH -BLAZY OPTION ON AIX61
· IZ91983: LOCKL PERFORMANCE ISSUE
· IZ94396: THERE IS A TIMING ISSUE BETWEEN THE SYNC DAEMON AND A MAPPED FILES APPLIES TO AIX 6100-06
· IZ97088: SMT4 scheduling fix / improvement included in AIX 6.1 patchset 8
· IV10172: WAITPROC IDLE LOOPING CONSUMES CPU
· IV06194: SRAD LOAD BALANCING ISSUES ON SHARED LPARS
· IV10259: MISCELLANEOUS DISPATCHER/SCHEDULING PERFORMANCE FIXES
· 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
. 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 10.2.0.4
· 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-18.104.22.168.7.
· Oracle patch Bug 6784747 - WASTED SPACE - KOH-KGHU CALL ALLOCATION
Patches for Oracle 11gR2
· Bug 10411618: ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS - Note: 22.214.171.124.2 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 126.96.36.199 than 10.2.0.4
· 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 188.8.131.52 (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)
· Bug 13354348: UNACCOUNTED GAP BETWEEN ELAPSED TO CPU TIME ON 11.2 IN AIX (fixed in 184.108.40.206)
· Oracle 220.127.116.11, 18.104.22.168 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 22.214.171.124 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 126.96.36.199. 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.
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 (http://www.redbooks.ibm.com/). For more information on this Technical Note, please send your questions to the IBM Oracle International Competency Center at email@example.com
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-1130503/，如需转载，请注明出处，否则将追究法律责任。