正文文本
Technical Notes
IBM Oracle International Competency Center (ICC)
January 14, 2020 email address: ibmoracle@us.ibm.com
IBM Power Systems, AIX and Oracle Database 10g & 11.2.0.3
Performance Considerations
Introduction
This document is intended to provide information, suggestions and website links to assist with
IBM Power Systems running in an Oracle Database 10g and 11g environment. The primary
focus is AIX with Oracle Database versions 10.2.0.4 and 11gR2 with POWER7 & POWER8
systems. Power Systems concerns can be minimized by following standard practices which
applies to all POWER server generations. The document also includes some information
related to Oracle Real Application Clusters (RAC).
Table 1, below, shows links to important documents to become familiar with.
Please Read First - Oracle and IBM References
Oracle Architecture and http://www.ibm.com/suppo AIX tuning reference guide
Tuning on AIX v2.20 rt/techdocs/atsmastr.nsf/We available on Techdocs
bIndex/WP100883
Oracle Real Application http://www.oracle.com/tech RAC tuning and configuration
Clusters on IBM AIX – Best network/database/clusterwa Guide
practices in memory tuning re/overview/rac-aix-system-
and configuring for system stability-131022.pdf
stability
Oracle Real Application http://www.oracle.com/tech RAC and VLAN deployment
Clusters (RAC) and Oracle network/database/clusterwa guide
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
1
Clusterware Interconnect re/overview/interconnect-
Virtual Local Area Networks vlan-06072012-1657506.pdf
(VLANs) Deployment
Considerations
Managing Raw Disks in AIX (MOS) Doc ID 1445870.1 My Oracle Support (MOS)
to use with Oracle Automatic
https://support.oracle.com
Storage Management (ASM)
(Registration required)
AIX Disk Queue Depth http://www- IO queuing for disk device
Tuning for Performance 03.ibm.com/support/techdo driver and adapter device driver
cs/atsmastr.nsf/WebIndex/
TD105745
Oracle Database Cross http://www.ibm.com/suppo Cross-platform migration
Platform Migration to AIX rt/techdocs/atsmastr.nsf/We reference guide available on
bIndex/WP101822 Techdocs
Must Read for Oracle 11g
Oracle Database Online http://www.ibm.com/suppo AIX fixes and Oracle patches
Patching on AIX rt/techdocs/atsmastr.nsf/We that need to be used with the
bIndex/WP102085 Oracle Database Online
patching feature
Oracle’s USLA HEAP http://www.ibm.com/suppo Addresses increased per-process
patches available on AIX rt/techdocs/atsmastr.nsf/We memory consumption
bIndex/WP102066
Oracle Database 11.2.0.3 http://www- Oracle database 11.2.0.3 may
with AIX 7.1 or AIX 6.1 03.ibm.com/support/techdocs run slow with high system time
using 1TB Segment Alias /atsmastr.nsf/WebIndex/WP1 with 1 TB unshared segment
02172 aliasing enabled
Oracle Database 11.2.0.3 http://www.ibm.com/suppo 11.2.0.3 Certification
Available on AIX rt/techdocs/atsmastr.nsf/We information
bIndex/WP102016
Oracle DB & RAC 11gR2 on http://www.ibm.com/suppo 11gR2 Planning and
IBM AIX: Tips and rt/techdocs/atsmastr.nsf/We implementing
Considerations bIndex/WP101176
Oracle DB & RAC 10gR2 on http://www.ibm.com/suppo 10gR2 Planning and
IBM AIX: Tips and rt/techdocs/atsmastr.nsf/We implementing
Considerations bIndex/WP101089
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
2
Additional IBM Information
Review Recent ICC Flashes http://www.ibm.com/suppo Technical sales support
on Techdocs – the Technical rt/techdocs/atsmastr.nsf/We database
Sales Library b/Techdocs
Guide to Multiple Page Size http://www.ibm.com/system Useful information on large
Support on AIX 5L Version s/resources/systems_p_os_ai page configuration
5.3 x_whitepapers_multiple_pa
ge.pdf
Large page size Review Oracle Large Page Larger page size has often
Usage section for provided performance
specifications improvements with Oracle
JAVA Performance on http://www.ibm.com/system Information about migrating
POWER7 - Best Practice s/power/hardware/whitepap JAVA applications from
ers/java_perf.html POWER5/6 to POWER7
Latest POWER Firmware http://www14.software.ibm. It is recommended to be at the
Release Levels com/webapp/set2/sas/f/powe most current firmware level.
r5cm/power7.html
Oracle RAC and DB Support MOS Doc ID 1594347. Oracle RAC and single instance
Tools Bundle support tools
Improving ASM Disk MOS Doc ID 1608549.1 Helpful ASM related
Discovery Time Best information
Practices
Table 1: Suggested Power and Oracle considerations
Oracle DB 11gR2 Standard Practices for IBM AIX
This section is a summary of standard practices for standalone Oracle DB 11gR2 instances on
IBM AIX 6.1, AIX 7.1 and Power Systems. With the exception of references to Symmetric
Multi-threading, quad-threaded mode (SMT4), all of the standard 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 standard 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.
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
3
Detailed Oracle/AIX standard practices documents, including the Oracle Architecture and
Tuning on AIX version 2.20, are available on IBM Techdocs (http://www-
03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883 ).
Detailed instructions for installing and configuring Oracle 11gR2 RAC with ASM are provided
in Release Notes: Oracle Real Application Cluster 11g Release 2 (11gR2) 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.
Memory
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 AIX 6.1 AIX 6.1 AIX 7.1 AIX 7.1
Value Default Restricted Default 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
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
4
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% 90 80 No 90 No
esid_allocator 1* 0 No 1 No
Table 2 Kernel settings for Oracle
* 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 Oracle.
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.
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
5
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
management.
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
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/74
7-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.
64 KB Size
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. AME by default uses 4k page size.
$ export
LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K
oracle
CPU
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.
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
6
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. Processor folding = On
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
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
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
7
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; may be required in older AIX /Oracle
levels) 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 parameters aio_fsfastpath and posix_aio_fsfastpath accelerate CIO. It is
enabled by default in AIX 6.1 and 7.1.
AIX 6.1, JFS2 with 11.2.0.2 and higher Bug notice:
Bug 9310972 - ENHANCEMENT: INTRODUCING O_CIOR FLAG WHEN
OPENING DATAFILES IN AIX 6.1. With AIX 6.1, IBM introduced a new
open flag O_CIOR which is same as O_CIO, but this allows subsequent open
calls without CIO. The advantage of this enhancement is that other applications
like cp, dd, cpio, dbv can access database files in read only mode without having
to open them with CIO.
Starting with Oracle 11.2.0.2 when AIX 6.1 is detected, Oracle will use O_CIOR
option to open a file on JFS2.Therefore you should no longer mount the
filesystems with mount option "-o cio".
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 control files.
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 binaries, fixed in 11.2.0.2.
Please reference “The Recent suggestions and open issues” section below for
more information.
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
8
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.
For clustered ASM (e.g. RAC) configurations, SCSI reservation must be disabled
on all ASM hdisk and hdiskpower devices (e.g. reserve_policy=no_reserve). The
standalone use of ASM, hdisks and hdiskpower devices does not need to have
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
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
9
Source:
http://docs.oracle.com/cd/E18283_01/server.112/e16102/asminst.htm#CHDB
BIBF
Network
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 (pls
note all udp settings are specific for RAC):
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.
Miscellaneous Specifications
AIXTHREAD_SCOPE=S (set in Oracle profile)
ulimits (smit chuser or edit /etc/security/limits to create a stanza for Oracle)
-1 (unlimited) for everything except core
Maximum number of PROCESSES allowed per user (smit chgsys)
maxuproc >= 2048; 16 KB is a commonly suggested value for Oracle Environment
variables
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
10
LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K
$ORACLE_HOME/bin/oracle. (preferred approach)
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.
AME by default will use 4k page size instead of 64k page size. The use of 4KB page
size may impact performance. AME certification on 11gR2 versions was done with
4kpage size.
Disk and Adapter Resources
Hdisk – lsattr –El hdisk<>
Queue depth might vary among default of 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 some 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
As of AIX 5.3, the optimal setting for LTG size is dynamically calculated during
the varyonvg process and does not need to be manually set. The varyonvg '-M'
parameter should not be used as it will over-ride the dynamically calculated LTG
size. It is recommended that all hdisks within a given VG have the same
'max_transfer' (and other attribute) values. In order to change hdisk attribute
values, any associated filesystems should be unmounted and the VG varied off.
FC Adapter – lsattr –El fcs<> and fcstat -e
max_xfer_size should be increased from default 1MB to 2MB. The default
adapter DMA memory size is 16 MB which increases to 128 MB when a non
default max_xfer_size is used. Larger DMA size can be important for
performance with many concurrent large block I/Os.
num_cmd_elems might need to be increased if fcstat -e reports a persistent non-
zero value for No Command Resource Count. Please verify with your storage
provider possible limits and recommended storage best practices before changing
num_cmd_elems.
If fcstat –e reports a persistent, non-zero value for No DMA Resource Count
contact support.
Live Partition Mobility (LPM)
Oracle Miscount timer - default of 30 seconds can be extended to 60 seconds if
required
Use of Dedicated Adapter recommended instead of SEA
Network bandwidth if minimal can cause restrictions
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
11
LPM with RAC Reference on IBM Techdocs - WP102094
IBM PowerVM Virtualization Introduction and Configuration SG 24-7940
Performance
Potential Oracle Database Performance Issue – Oracle Table‘x$ksmsp’
Issue:
“If customers are using any query that accesses the table: 'x$ksmsp' they should disable
such queries..
Oracle provides the use of x$ksmsp to allow customers to give a listing of the RAM heap
to see how; free space is allocated within the shared pool, the sizes of available chunks on
the freelist for the shared pool and RAM.
Unfortunately, this leads to a myriad of issues including; system hangs, heap issues
(locking) etc in production systems and selecting from x$ksmsp on a production system
is to be avoided.
This basically does the following for each subheap in the pool, it will:
1) Grab the shared pool latch for that subheap
2) Walk through ALL chunks of memory in that subheap calling back to ksmspc to
extract information about the chunk of memory
3) Release the shared pool latch for that subheap.
Even on a minimal sized shared pool this means you are holding the shared pool latch for
a significant amount of time which then blocks anyone needing to perform any shared
pool memory operation that requires that latch.”
Precautions:
1) It is NOT recommended to run queries on X$KSMSP when the database instance is
under load.
2) Performance of the database will be impacted, especially currently with very large
SGAs.
3) Bug 14020215 was filed for ORA-600 errors and unplanned outages running queries
directly on X$KSMSP.
4) There is a view, X$KSMSP_NWEX, in later versions of 11g that is safer to use for
investigation of memory usage.
5) Oracle STRONGLY recommends you not run these queries unless specifically
requested by Oracle Support to do so.
Recommend:
Suggest checking via a crontab entry running a query against a variety of X$ tables -
eliminating any query against the x$ksmsp resolves such latch contention issues.
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
12
Standalone Database and RAC Miscellaneous Tips and Reference
Troubleshooting Oracle RAC Tips
Collect and review ‘Last Gasp’ file
http://www.oracle.com/technetwork/database/rac-aix-system-stability-131022.pdf (pg 7)
Review CRS Logs for events with timestamps
OSWatcher - network
AWR Report
RDA - collects Oracle logs
Performance
- Oracle Redo Logs and Control Files – placement on separate file systems
- Apply N-Apply Bundle Patch or 11.2.0.4 (includes Bundle Patch)
- OCSSD Bin – Process & Thread Priority
Oracle Timers
- Network Heartbeat 1 per/sec with 30 sec default timeout
- CSS Voting Disk Heartbeat 1per/sec 27 sec default timeout
- AIX Driver read/write 30 Second timeout
- Oracle Miscount value – default 30 sec, used 60 sec for LPM large lpar’s successfully then
changed back to 30 sec
- Oracle CRS OCSSD – default 200 seconds is timeout value
- Note: ORAchk replaces the popular RACcheck tool - (Doc ID 1268927.2 in MOS)
AIX fixes for Oracle 10gR2 and 11gR2
Some of the common AIX fixes for Oracle 10g and 11g follow. The APAR number will be unique to
the specific AIX TL level with the most current TL level providing a rollup of the earlier TL & SP
levels. The APAR's listed may apply to either Oracle dbase version. If an APAR is not listed for a TL,
then the fix is already included in that TL, or that TL is not vulnerable to the problem. The minimum
recommended AIX levels currently are: AIX 6.1 TL09 (terminal release) and AIX 7.1 TL03. Please see
the appendix for older TL APAR information.
AIX 6.1 TL08 & TL09 TL08 APAR TL09 APAR
SMT4 PERFORMANCE ENHANCEMENTS Base IV10656
WAITPROC IDLE LOOPING CONSUMES CPU Base IV10657
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
13
SRAD LOAD BALANCING ISSUES ON SHARED LPARS Base IV10652
MISCELLANEOUS DISPATCHER/SCHEDULING Base IV10651
PERFORMANCE FIXES
64K PAGING TAKING PLACE WHEN AVAILABLE Base IV23604
SYSTEM RAM EXISTS
SYSTEM CRASH IN AS_FORK_ALIAS IF Base IV26735
ESID_ALLOCATOR IS ENABLED
SHLAP64 UNABLE TO PROCESS ORACLE REQUEST Base IV35888 Base IV35888
LEADING TO KERNEL HANG
UDP MULTICAST BUG MAKES ORACLE RAC Base IV30297 Base IV30219
UNSTABLE, INCLUDING NODE EVICTIONS
THREAD_CPUTIME() RETURNS INCORRECT VALUES SP03 IV38778 Base IV36225
DISABLE MULTICAST LOOPBACK FOR MPING Base IV34046
symptom: Dropping packet due to direction
mismatch. Was expecting r but got s.
AIX 7.1 TL02 TL02 APAR
WAITPROC IDLE LOOPING CONSUMES CPU Base IV11460
SRAD LOAD BALANCING ISSUES ON SHARED LPARS Base IV11991
MISCELLANEOUS DISPATCHER/SCHEDULING Base IV11988
PERFORMANCE FIXES
ADDRESS SPACE LOCK CONTENTION ISSUE Base IV11455
SYSTEM CRASH IN AS_FORK_ALIAS IF Base IV23735
ESID_ALLOCATOR IS ENABLED
REDUCE EARLY WORKING STORAGE PAGING Base IV26731
UDP MULTICAST BUG MAKES ORACLE RAC Base. IV35893
UNSTABLE, INCLUDING NODE EVICTIONS SP02
THREAD_CPUTIME() RETURNS INCORRECT VALUES Base IV30318
DISABLE MULTICAST LOOPBACK FOR MPING SP03 IV38960
symptom: Dropping packet due to direction
mismatch. Was expecting r but got s.
ADD ABIILITY TO REORDER TOC SYMBOLS IN LIMITED Base IV48898
CIRCUMSTANCES
VMM RELALIAS LOCKING TUNABLE TO IMPROVE Base IV34380
MMAP/UNMAP PERFORMANCE
AIX 7.1 TL03 (con’t) TL03 APAR
NETWORK ERFORMANCE DEGRADATION ON FC5899 SP01 IV58687
(AUSTIN) ADAPTER APPLIES TO AIX 7100-03
XMGC NOT TRAVERSING ALL KERNEL HEAPS. SP01 IV53587
APPLIES TO AIX 7100-03 14/04/17 PTF PECHANGE
UDP MULTICAST: SHORT PACKET FOR SOME SP01 IV33047
LISTENERS. APPLIES TO AIX 7100-03
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
14
DATA PTR INCORRECTLY INCREMENTED IN UDP SP01 IV34454
RECEIVE. APPLIES TO AIX 7100-03
TATX/LOOKUPS/FILE OPENS APPLIES TO AIX 7100-03 SP01 IV44289
POSSIBLE STARVATION OF LARGE I/OS UNDER SP01 IV44347
HEAVY WORKLOAD APPLIES TO AIX 7100-03
PORT/DEVND FC5899 DRIVER HOG CPU WHEN SP01 IV60218
ENTSTAT ON CLOSED /60052
UDP SEND PERFORMANCE ENHANCEMENTS APPLIES SP01 IV54257
TO AIX 7100-03
DISABLE MULTICAST LOOPBACK FOR MPING Base IV36204
symptom: Dropping packet due to direction
mismatch. Was expecting r but got s.
Non Critical APAR’s
AIX 6.1 Non Critical TL08 APAR TL09 APAR
TCP RETRANSMIT PROCESSING IS VERY SLOW Base IV14524
SYSTEM CRASH DUE TO FREED SOCKET WHEN Base IV19341
SOCKETPAIR() CALL USED
DISABLE MULTICAST LOOPBACK FOR MPING Base IV36225
symptom: Dropping packet due to direction
mismatch. Was expecting r but got s.
LPARSTAT -H AND -H WILL NOT SHOW HYPERVISOR Base IV53394
STATISTICS APPLIES TO AIX 6100-09
CAT /PROC/SYS/FS/JFS2/MEMORY_USAGE MAY SP05 IV57785 SP03 IV54359
RETURN INVALID ARGUMENT
UMOUNT FAILS WITH DEVICE BUSY ERROR EVEN Base IV46203 Base IV39905
WITHOUT ACTIVE PROCESS APPLIES TO AIX 6100-08
JAVA WON'T INSTANTIATE IF PROT_NONE USED FOR Base IZ55237
SHARED MMAP REGION
SYSTEM HANGS OR CRASHES WHEN APP USES Base IV21846
SHARED SYMTAB CAPABILITY. APPLIES TO AIX 6.1-08
LINK FAILS WITH UNDOCUMENTED COMPILER FLAG Base IV39893
AND THREAD-LOCAL STG
RUNTIME LINKING FAILED TO BIND THE BSS SYMBOL Base IV39856
EXPORTED FROM MAIN
A SPECIAL-PURPOSE LINKER FLAG WORKS Base IV42840
INCORRECTLY.
HANG UNDER SOME CIRCUMSTANCES WHEN A C++ Base IV64454 Base IV63322
DTOR UNLOADS LIBRARIES
ATTEMPT FAILED.OPENED STATE DOES NOT RETRY Base IV38695 Base IV37549
NPIV LOGIN, IF FIRST APPLIES TO AIX 6100-09
AIX 7.1 Non Critical TL02 APAR
TCP RETRANSMIT PROCESSING IS VERY SLOW Base IV15184
MULTICAST UDP PACKETS NOT DELIVERED TO ALL Base IV16417
LISTENERS IN WPAR ENV
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
15
SYSTEM CRASH DUE TO FREED SOCKET WHEN Base IV19357
SOCKETPAIR() CALL USED
CAT /PROC/SYS/FS/JFS2/MEMORY_USAGE MAY SP05 IV56930
RETURN INVALID ARGUMENT
UMOUNT FAILS WITH DEVICE BUSY ERROR EVEN Base IV40102
WITHOUT ACTIVE PROCESS APPLIES TO AIX 7100-01
SYSTEM HANGS OR CRASHES WHEN APP USES Base IV21878
SHARED SYMTAB CAPABILITY. APPLIES TO AIX 7100-
01
LOADING 5.3 TLS ENABLED LIBS BY 5.2 APPS CAUSED Base IV30320
CORE DUMP IN 32B APPLIES TO AIX 7100-01
LINK FAILS WITH UNDOCUMENTED COMPILER FLAG Base IV40005
AND THREAD-LOCAL STG APPLIES TO AIX 7100-01
RUNTIME LINKING FAILED TO BIND THE BSS SYMBOL Base IV39987
EXPORTED FROM MAIN APPLIES TO AIX 7100-01
HANG UNDER SOME CIRCUMSTANCES WHEN A C++ Base IV64248
DTOR UNLOADS LIBRARIES
ATTEMPT FAILED.OPENED STATE DOES NOT RETRY Base IV38879
NPIV LOGIN
AIX 7.1 Non Critical (con’t) TL03 APAR
A SPECIAL-PURPOSE LINKER FLAG WORKS Base IV42782
NCORRECTLY
CAT /PROC/SYS/FS/JFS2/MEMORY_USAGE MAY SP3 IV55030
RETURN INVALID ARGUMENT
UMOUNT FAILS WITH DEVICE BUSY ERROR EVEN Base IV40079
WITHOUT ACTIVE PROCESS
ADD ABIILITY TO REORDER TOC SYMBOLS IN LIMITED Base IV44690
CIRCUMSTANCES APPLIES TO AIX 7100-03
HANG UNDER SOME CIRCUMSTANCES WHEN A C++ Base IV63130
DTOR UNLOADS LIBRARIES.APPLIES TO AIX 7100-03
ATTEMPT FAILED.OPENED STATE DOES NOT RETRY Base IV37484
NPIV LOGIN, IF FIRST APPLIES
Oracle PSU Information on MOS
Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID
1454618.1)
Recommended N-Apply Bundle #3 for AIX 11.2.0.3. with critical fixes (Patch 20165285)
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
16
Last Updated Jan 21, 2015
Release Oracle 11.2.0.3.9
Platform IBM AIX on POWER Systems (64-bit)
Recommended for Oracle Database 11.2.0.3.0
The following prerequisite patches should be installed in the order shown before installing this patch.
1 17540582 DATABASE PATCH SET UPDATE 11.2.0.3.9 (INCLUDES CPUJAN2014)
1 19121548 DATABASE PATCH SET UPDATE 11.2.0.3.12 (INCLUDES CPUOCT2014)
Bugs Resolved by This Patch
11689561 HASH JOIN SLOWER IN 11.2 COMPARED TO 10.2 ON AIX
12596494 GENERALLY HIGHER CPU USAGE IN 11.2.0.2 THAN 10.2.0.4
12865682 PERFORMANCE ISSUES DUE TO BYTE-SWAPPING
13400729 SCATTERED READS SLOWER IN 11.2 THAN 11.1
13443029 ONLINE PATCHING / HOT PATCHING: USE SHRSYMTAB TO REDUCE USLA HEAP
USAGE
13494030 SLOW PROCESS STARTUP WITH ONLINE PATCHES ON AIX
13940331 VALUE FOR SETTING THREAD SCHEDULING IS INCORRECT IN SLTSTSPAWN
14764540 SGA ATTACH ADDRESSES SHOULD BE AT 1TB BOUNDARY
14799449 ORA-7445 [SSKGDS_SNM()+592] AND INSTANCE TERMINATED BY PMON
16036950 N-APPLY PATCH 11.2.0.3 FOR CRITCAL AIX BUGS BUNDLE #1
Oracle 10gR2 and 11.2.0.3 Bug Fixes AIX Related
Bug # Generic/AIX Description Version Fixed in Patch Ref. MOS Note id.
affected Available
7226548 AIX enables 64 KB and 16 MB large page support 10.2.0.4 11.1 10.2.0.4 282036.1
10.2.0.5
7568734 AIX AIX: Sporadic spikes of 'log file sync' on AIX with 10.2.0.4 10.2.0.5 10.2.0.4 34592.1
heavy commit concurrency
6784747 AIX Wasted memory in koh-kghu allocations. Free 10.2.0.4 10.2.0.5 10.2.0.4
extents of memory might not get used (Power7
customers should apply this patch, included in
10.2.0.5 patchset)
10411618 Generic Add different wait schemes for mutex waits. 11.2.0.1 12.1 11.2.0.1 727400.1
Note - 11.2.0.2.2 PSU breaks this patch and 11.2.0.2 11.2.0.3 11.2.0.2 10411618.8
additional patch (12431716) is required
13443029 AIX Oracle 11.2.0.2 & 11.2.0.3 requires USLA heap 11.2.0.2 11.2.0.2
10190759 patch Bug 13443029 (requires AIX 6.1 TL07 SP2 11.2.0.3 11.2.0.3
or AIX 7.1 TL01 SP2 OR Bug 10190759 (disables
hot patching)
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
17
12740358 Generic DBMS_UTILITY.FORMAT_CALL_STACK is slower 11.2.0.2 12.1 11.2.0.2 1450540.1
than 10g 11.2.0.3 11.2.0.3
9842771 Generic Wrong SREADTIM & MREADTIM stats in 11.2.0.1 11.2.0.3 11.2.0.1
AUX_STAT$ 11.2.0.2 11.2.0.2
12596494 AIX Generally higher CPU usage in 11.2.0.2 11.2.0.2 11.2.0.4 11.2.0.2 1392633.1
11.2.0.3
12412983 AIX AIX 'asynch descriptor resize' wait not 11.2.0.2 12.1 11.2.0.2 1318709.1
neccesary on AIX 11.2.0.3 11.2.0.3
11689561 AIX Hash join consumes more Processor in IBM AIX 11.2.0.2 11.2.0.4 11.2.0.2
11.2.0.3 11.2.0.3
13877328 AIX Database performance slows down steadily 11.2.0.3 See MOS 1467807.1
over time note for
work-
around
13840529 Generic Database hang on cache buffer chains and row 11.2.0.3 See MOS 1467807.1
cache objects note for
work-
around
9495594 Generic Performance degradation running anonymous 11.2.0.1 11.2.0.3 1378094.1
PL/SQL blocks 11.2.0.2
13354348 AIX Unaccounted gap between elapsed to CPU time 11.2.0.2 11.2.0.3
in 11.2 on AIX
13846587 AIX AWR report shows enlarged "DB Time" values 11.2.0.3 12.1 11.2.0.3
& TKPROF report show enlarged execute CPU
times
12980183 Generic SPM cannot reproduce execution plans 11.2.0.2 11.2.0.2
11.2.0.3 11.2.0.3
13004894 Generic Wrong results with SQL TRACE/10046 11.2.0.3 11.2.0.3 1320966.1
12363485 Generic Wrong result using union all 10.2.0.5 12.1 10.2.0.5 1320966.1
11.2.0.1 11.2.0.1
11.2.0.2 11.2.0.2
10269193 Generic Wrong results with outer join and CASE 11.2.0.1 12.1 11.2.0.1
expression optimization 11.2.0.2 11.2.0.2
9910484 Generic SPM uses excessive space in SYSAUX 11.1.0.7 12.1 11.1.0.7
11.2.0.1 11.2.0.1
11.2.0.2 11.2.0.2
10242967 Generic ORA-6502 from 11.1.0.7 11.1.0.7
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE 11.2.0.2 11.2.0.2
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
18
13377816 Generic Excessive Memory use by DIAG / DIA0 11.1.0.7 11.1.0.7
11.2.0.1 11.2.0.1
11.2.0.2 11.2.0.2
11.2.0.3 11.2.0.3
13551402 Generic High "log file parallel write" and "log file sync" 11.2.0.2 12.1
after upgrading to 11.2 with Veritas/Symantec 11.2.0.3 11.2.0.4
ODM
13940331 AIX AIX: OCSSD threads are not set to the correct 11.2.0.2 12.1 10.2.0.5, 1427855.1
priority 10.2.0.4 11.2.0.2,
10.2.0.5 11.2.0.3
16910419 AIX HAIP FAILED TO START: HAIP failed to start 11.2.0.3 11.2.0.3.
after applied patch 16842566 on top of PSU6 6
10627020 AIX GPFS w/RAC using Asynch I/O - ORA-1115, 11.2.0.2 12.1 10.2.0.5,
ORA-1110, ORA-27091 ORA-27072 IBM AIX 10.2.0.4 11.2.0.2,
RISC SYSTEM/6000 ERROR: 4: INTER 10.2.0.5 11.2.0.3
14764540 AIX SGA ATTACH ADDRESSES SHOULD BE AT 1TB 11.2.0.3 11.2.0.3 14764540.8
BOUNDARY 1528081.1
14799449 AIX INSTANCE TERMINATED BY PMON AFTER 11.2.0.3 12.2 11.2.0.3
SIGSEGV ON [SSKGDS_SNM()+592]
16078109 AIX PL/SQL APPLICATION RUN SLOWER AFTER 11.2.0.2.0 11.2.0.2.0
INCREASING CPU CORES FROM 26 TO 42 11.2.0.2.2 11.2.0.2.2
11.2.0.3 11.2.0.3
16697958 AIX OSYSMOND.BIN FAILS ON A PERFSTAT SYSTEM Ref: APAR IV24576
CALL WHEN LPAR HAS A VIRTUAL INTERFACE -
CHM process osysmond.bin fails -ERROR:
errno=14
18072720 AIX OSYSMOND.BIN OCCURS EXCESSIVE 11.2.0.3
ERECV/POLL SYSCALL ON 11.2.0.3.6 -
osysmond.bin high CPU usage- node was
rebooted due to lack of system resource.
14251087 ORA-27072: FILE I/O ERROR, ERROR: 4: 10g & 11g
INTERRUPTED SYSTEM CALL – fixed in 12.1
11902008 SMON CRASH WITH ORA-600 [KCBGCUR_3]
DURING TX RECOVERY 10.2.0.4. is fixed in
11.2.0.4.
18153848 GI_HOME SHOWING 11.2.0.3.8 AFTER
SUCCESSFUL INSTALLATION OF PSU 9 - doc
error fixed in PSU10
16005924 ORA-38754: FLASHBACK DATABASE NOT 11.2.0.3
STARTED; REQUIRED REDO LOG IS NOT
AVAILABLE
Doc ID Warning: Heavy Swapping Observed on System
1452790.1 after Upgrade to 11.2.0.3
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
19
Merged 11.2.0.3 Bug Fixes
12412983 AIX AIX 'asynch descriptor resize' wait not
necessary on AIX
13400729 AIX Scattered reads slower in 11.2 than 11.1
OR
14478927 Merge request on top of database PSU
11.2.0.3.3 for bugs above two bus (12412983 &
13400729)
11689561 Hash join consumes more CPU in IBM AIX
12596494 Generally Higher CPU Usage in 11.2.0.2 than
10.2.0.4. Reference Merge Patch 13947840
OR
13947840 Merge Request on top of 11.2.0.3.0 for bugs
11689561 12596494
13877328 Database performance slows over a period of
time. This may not apply to you now, but you
need to be aware of this. Please review the bug
text
13840529 Database hang on cache buffer chains and row
cache objects. Review bug text for workaround
13004894 10046 trace changes results of gl query with
patch 9776940.
13743357 PSRC Query returns wrong results on first
execution after starting instance
OR
17063116 Merge request on top of database PSU
11.2.0.3.7 for bugs 13004894 13743357
Merged 11.2.0.4 Bug Fixes
16901346 BUG 16901346 - OSYSMOND PROCESS TAKING
ALMOST 5% OF CPU BECAUSE OF HIGH OPEN
FDS COUNT
17733927 BUG 17733927 - CSS CLIENTS TIMEOUT UNDER
HEAVY CONNECTIVITY LOADS ON AIX
OR
19202720 Merge patch is:
BUG 19202720 - MERGE REQUEST ON TOP OF
11.2.0.4.0 FOR BUGS 16901346 17733927
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
20
11.2.0.4 & 12c Bug Fixes
17294810 BUG 17294810 - PUTTING THREAD_CPU TIMER
IMPLEMENTATION BACK TO 11.2.0.4
17501296 After upgrade to 11.2.0.4 unable to delete any 11.2.0.4
rows from table with context index due to
error: "PLS-00306
18066615 Bug 18066615 : WRONG RESULTS AFTER 11.2.0.4
UPGRADE TO 11.2.0.4
19181582 DEADLOCK BETWEEN LG0N ON 'LGWR WORKER
GROUP ORDERING'
17551261 ORA-904 WHILE RUNNING SELECT QUERY 11.2.0.4
which is in 11.2.0.4. and fixed in terminal
release of 12.1.01
14602788 HIGH CPU CONSUMPTION BY QMON PROCESS
AFTER UPGRADE TO 11.2.0.4 fixed in 12.1
AQ:11GQ: Q00* PROCESS SPIN IN BOTH
INSTANCES
14275161 ORA-7445 [EVAOPN3()+135] INSERTING FROM 11.2
PL/SQL AFTER UPGRADE TO 11.2.0.4 fixed in
12.1 - ORA-600 [RWOIRW: CHECK RET VAL] ON
CTAS
18828868 Patch EXPLOSION IN AQ BACKGROUND SLAVES 11.2.0.3
RESULTING IN ORA-18/ORA-20
18194267 AIX-11.2.0.4-SIHA:ORAAGENT.BIN MEMORY 11.2.0.4
LEAK
18828868 Patch EXPLOSION IN AQ BACKGROUND SLAVES 11.2.0.4
RESULTING IN ORA-18/ORA-20
18261183 Patch GRID INFRASTRUCTURE ORAAGENT.BIN 11.2.0.4
GENERATES <DEFUNCT> THREADS
17802407 ORA-7445 [QERIXFETCHFASTFULLSCAN+8212] 11.2.0.4
AFTER UPGRADE TO 11.2.0.4
13807031 Patch RMAN DUPLICATE DOES NOT FOLLOW 11.2.0.4
SYMBOLIC LINKS FOR REDOLOG FILES IN 11.2 -
fixed in 12.1
Recent suggestions and open issues
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
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
21
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. The AIX Active System Optimizer (ASO) has the
capability to automatically/dynamically tune Hardware Prefetch. (as of AIX 7.1 TL2
SP1 and AIX 6.1 TL8 SP1).
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 11.2.0.3 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 11.2.0.2. 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 global "no" parameter list) 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. Our recommendation is to use rfc1323=1
(Oracle recommendation), but to ensure that "IV13121: TCP RETRANSMIT
PROCESSING IS VERY SLOW 12/05/30 PTF PECHANGE" (or equivalent) is
applied. rfc1323 is default for the ISNO values set by the interface device driver, for
VETH and 10Gbit it is enabled (and for NFS enabled by default).
Additional Information
This Technical Note was authored by IBM’s Sanjay Ruprell and Wolfgang Tertel. For more information
on this Technical Note, please send your questions to the IBM Oracle International Competency Center at
ibmoracle@us.ibm.com.
External website reference for this document:
This document is available externally on the IBM Techdocs website with Doc ID: WP102171
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
22
Appendix
OCSSD Bin – Process & Thread Priority
Oracle RAC ocssd.bin, cssdagent, cssdmonitor, and osysmond.bin all (and should) have priority (PRI)
of ‘0’ and a scheduling policy (SCH) of ‘-‘ as well as all threads for ocssd.bin, cssdagent, and
cssdmonitor also have a PRI ‘0’.
The threads are often overlooked and forgotten. The main process maybe correct but the threads
associated with those processes also need to run (inherit) at those elevated priorities.
Known 11.2.0.3 OCSSD Bug 13940331
Priority should be 0 and not 60 (see PRI column in data).
Please ensure the ocssd threads are also running at “0” and “—“?
ps -p `ps -ef | grep ocssd.bin | grep -v grep |awk '{print $2}'` -mo THREAD
Bug 13940331 - AIX: OCSSD threads are not set to the correct priority (Doc ID 13940331.8)
On AIX has found that the threads created for OCSSD were not inheriting the correct priority this
may cause evictions when the machine is running under high workload.
The processes show the functions "ntevque->netevpque->poll" in their call stack and may hang.
EXAMPLE:
svp0090bdc:root# ps -p `ps -ef | grep ocssd.bin | grep -v grep | awk '{ print
$2 }'` -mo THREAD
USER PID PPID TID ST CP PRI SC WCHAN F TT
BND COMMAND
oracle 8454212 11141280 - A 0 0 35 *
10240103 - - /opt/GRID/app/grid/11.2.0.3/bin
- - - 53412031 S 0 0 1
f1000f0a10032f40 8410400 - - -
- - - 67174423 S 0 60 1
f1000f0a10040140 8410400 - - -
- - - 69402727 S 0 60 1
f1000f0a10042340 8410400 - - -
- - - 69730365
S 0 60 1 - 418400 - - -
- - - 70189183 S 0 60 1
f1000f0a10042f40 8410400 - - -
- - - 71958629
Z 0 60 1 - c00001 - - -
- - - 74317843 S 0 60 1
f1000f0a10046e40 8410400 - - -
- - - 76742815 S 0 60 1
f1000f0a10049340 8410400 - - -
- - - 82182299 S 0 0 1
f1000f0a1004e640 8410400 - - -
- - - 82313403
Z 0 60 1 - c00001 - - -
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
23
- - - 85000239
Z 0 60 1 - c00001 - - -
- - - 92143809
Z 0 60 1 - c00001 - - -
- - - 104005671
Z 0 0 1 - c00001 - - -
- - - 40894877 S 0 60 1
f1000f0a100a7040 8410400 - - -
- - - 41156961 S 0 60 1
f1000f0a100a7440 8410400 - - -
- - - 42992033
S 0 60 1 - 418400 - - -
- - - 43188529
Z 0 60 1 - c00001 - - -
- - - 43254061 S 0 60 1
f1000f0a100a9440 8410400 - - -
- - - 43319609
Z 0 60 1 - c00001 - - -
11g to 12c Upgrade
Heavy paging observed after upgrading DB 11.2.0.3.4 and AIX 6100-09-01-1341 to DB 12.1.0.1 and
AIX 7100-03-03-1415. Both Oracle and IBM Support have arrived at the same conclusion that additional
memory is recommended or the workload rebalanced to reduce paging which impacted system
performance.
Out of Standard Support TL APAR’s for AIX 6.1 & 7.1
AIX 6.1 TL04 APAR TL05 APAR TL06 APAR
BIND64 CORES WITH -BLAZY OPTION ON AIX61 SP9 IZ89302 SP5 IZ8930 SP5 IZ89514
0
SP4 IZ88711
LOCKL PERFORMANCE ISSUE SP6 IZ91983
THERE IS A TIMING ISSUE BETWEEN THE SYNC SP5 IZ9048 SP4 IZ94396
DAEMON AND A MAPPE 3
SMT4 PERFORMANCE ENHANCEMENTS SP5 IZ97088
WAITPROC IDLE LOOPING CONSUMES CPU SP8 IV0111 SP7 IV06197
1
SRAD LOAD BALANCING ISSUES ON SHARED LPARS SP8 IV0619 SP7 IV06196
4
MISCELLANEOUS DISPATCHER/SCHEDULING SP8 IV1106 SP7 IV10259
PERFORMANCE FIXES 8
ADDRESS SPACE LOCK CONTENTION ISSUE SP8 IV1086 SP7 IV03903
1
64K PAGING TAKING PLACE WHEN AVAILABLE SP4 IZ71191 Base IZ7160 Base IZ72650
SYSTEM RAM EXISTS 3
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
24
SYSTEM CRASH IN AS_FORK_ALIAS IF SP9 IV23852
ESID_ALLOCATOR IS ENABLED
REDUCE EARLY WORKING STORAGE PAGING SP10 IV27655
64K PAGING TAKING PLACE WHEN AVAILABLE Base IZ71191 Base IZ7160 Base IZ72650
SYSTEM RAM EXISTS 3
LSUSER MAY FAIL IF THERE IS A NON-EXISTANT SP10 IZ97619 SP06 IZ9752 SP05 IZ96256
MEMBERNETGROUP 5
SHLAP64 UNABLE TO PROCESS ORACLE REQUEST SP11 IZ86799 SP07 IZ8825 SP06 IV04047
LEADING TO KERNEL HANG 8
THREAD_CPUTIME() RETURNS INCORRECT VALUES Base IV35038
AIX 6.1 TL07 APAR
BIND64 CORES WITH -BLAZY OPTION ON AIX61 Base IZ88880
LOCKL PERFORMANCE ISSUE Base IZ94177
THERE IS A TIMING ISSUE BETWEEN THE SYNC Base IZ91991
DAEMON AND A MAPPE
SMT4 PERFORMANCE ENHANCEMENTS Base IZ96715
WAITPROC IDLE LOOPING CONSUMES CPU SP2 IV10172
SRAD LOAD BALANCING ISSUES ON SHARED LPARS SP2 IV10173
MISCELLANEOUS DISPATCHER/SCHEDULING SP2 IV10292
PERFORMANCE FIXES
ADDRESS SPACE LOCK CONTENTION ISSUE SP2 IV10606
SYSTEM CRASH IN AS_FORK_ALIAS IF SP5 IV23850
ESID_ALLOCATOR IS ENABLED
REDUCE EARLY WORKING STORAGE PAGING SP6 IV26272
LSUSER MAY FAIL IF THERE IS A NON-EXISTANT Base IZ96441
MEMBERNETGROUP
SHLAP64 UNABLE TO PROCESS ORACLE REQUEST Base IZ97712
LEADING TO KERNEL HANG
THREAD_CPUTIME() RETURNS INCORRECT VALUES Base IV30712
VMM RELALIAS LOCKING TUNABLE TO IMPROVE Base IV31584
MMAP/UNMAP PERFORMANCE
AIX 7.1 TL0 APAR TL01 APAR
BIND64 CORES WITH -BLAZY OPTION ON AIX61 SP3 IZ89165
LOCKL PERFORMANCE ISSUE Base IZ9674
1
THERE IS A TIMING ISSUE BETWEEN THE SYNC SP3 IZ94598 Base IZ9496
DAEMON AND A MAPPED FILE 3
SMT4 PERFORMANCE ENHANCEMENTS SP3 IZ96658 Base IZ9721
8
WAITPROC IDLE LOOPING CONSUMES CPU SP5 IV09133 SP2 IV1048
4
SRAD LOAD BALANCING ISSUES ON SHARED LPARS SP5 IV09114 SP2 IV1080
2
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
25
MISCELLANEOUS DISPATCHER/SCHEDULING SP5 IV11176 SP2 IV1080
PERFORMANCE FIXES 3
ADDRESS SPACE LOCK CONTENTION ISSUE SP5 IV11177 SP2 IV1079
1
64K PAGING TAKING PLACE WHEN AVAILABLE SP3 IZ84452
SYSTEM RAM EXISTS
SYSTEM CRASH IN AS_FORK_ALIAS IF SP7 IV23960 SP5 IV1126
ESID_ALLOCATOR IS ENABLED 1
REDUCE EARLY WORKING STORAGE PAGING SP8 IV26581 SP6 IV2701
4
64K PAGING TAKING PLACE WHEN AVAILABLE Base IZ84452
SYSTEM RAM EXISTS
LSUSER MAY FAIL IF THERE IS A NON-EXISTANT SP03 IZ96373 Base IZ9674
MEMBERNETGROUP 5
SHLAP64 UNABLE TO PROCESS ORACLE REQUEST SP06 IZ92569 Base IZ9792
LEADING TO KERNEL HANG 3
THREAD_CPUTIME() RETURNS INCORRECT VALUES Base IV35196 Base IV3486
9
Non Critical APAR’s
AIX 6.1 Non Critical TL04 APAR TL05 APAR TL06 APAR
TCP RETRANSMIT PROCESSING IS VERY SLOW SP8 IV18483
FILE.ATION OVERFLOW REPORTED IN ERROR WHILE SP8 IV1057 SP7 IV10539
LINKING 6
SYSTEM CRASH IN NETINFO_UNIXDOMNLIST SP11 IV00634 SP7 IZ9735 SP6 IZ97166
3
MULTICAST UDP PACKETS NOT DELIVERED TO ALL SP9 IV1956 SP8 IZ89950
LISTENERS IN WPAR 9
SYSTEM CRASH DUE TO FREED SOCKET WHEN SP9 IV16603
SOCKETPAIR()
IOCP GETMULTIPLECOMPLETIONSTATUS() NEVER SP6 IZ74508 SP2 IZ7622 Base IZ74932
RETURNS 7
Unmapped DS3/4/5K LUN may block I/O to other SP5 IZ86957
LUNs in Array.
CRASH IN NETINFO_UNIXDOMNLIST WHILE SP11 IV00634 SP7 IZ9735 SP6 IZ97166
RUNNING NETSTAT 3
AIX 6.1 Non Critical (con’t) TL07 APAR
TCP RETRANSMIT PROCESSING IS VERY SLOW SP4 IV14297
SYSTEM CRASH @GET_NET_INFO+000278 SP4 IV08486
FILE.ATION OVERFLOW REPORTED IN ERROR WHILE SP4 IV09580
LINKING
SYSTEM CRASH IN NETINFO_UNIXDOMNLIST Base IZ99445
MULTICAST UDP PACKETS NOT DELIVERED TO ALL SP4 IV08682
LISTENERS IN WPAR ENV
SYSTEM CRASH DUE TO FREED SOCKET WHEN SP5 IV21128
SOCKETPAIR() CALL USED
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
26
CRASH IN NETINFO_UNIXDOMNLIST WHILE Base IZ99445
RUNNING NETSTAT
TOPAS/NMON FAILS WITH ASSERT FAILURE FOR SP06 IV24576
VIRTUAL INTERFACES APPLIES TO AIX 6100-07 (Ref
Bug 16697958)
UMOUNT FAILS WITH DEVICE BUSY ERROR EVEN Base IV39754
WITHOUT ACTIVE PROCESS APPLIES TO AIX 6100-08
SHLAP PROCESS FAILS WHEN SHARED SYMBOL Base IV28319
TABLE FEATURE IS USED. APPLIES TO AIX 6100-07
LINK FAILS WITH UNDOCUMENTED COMPILER FLAG Base IV39104
AND THREAD-LOCAL STG
RUNTIME LINKING FAILED TO BIND THE BSS SYMBOL Base IV33433
EXPORTED FROM MAIN
AIX 7.1 Non Critical TL0 APAR TL01 APAR
TCP RETRANSMIT PROCESSING IS VERY SLOW SP8 IV20595 SP4 IV1312
1
SYSTEM CRASH @GET_NET_INFO+000278 SP4 IV0879
7
FILE.ATION OVERFLOW REPORTED IN ERROR WHILE SP4 IV0954
LINKING 1
SYSTEM CRASH IN NETINFO_UNIXDOMNLIST SP4 IV06032 Base IZ9963
6
MULTICAST UDP PACKETS NOT DELIVERED TO ALL SP8 IV26418 SP4 IV1625
LISTENERS IN WPAR ENV 0
SYSTEM CRASH DUE TO FREED SOCKET WHEN SP7 IV21131 SP5 IV2123
SOCKETPAIR() CALL USED 5
Unmapped DS3/4/5K LUN may block I/O to other SP3 IZ86419
LUNs in Array.
CRASH IN NETINFO_UNIXDOMNLIST WHILE SP4 IV06032 Base IZ9963
RUNNING NETSTAT 6
UMOUNT FAILS WITH DEVICE BUSY ERROR EVEN Base IV3794
WITHOUT ACTIVE PROCESS APPLIES TO AIX 7100-01 0
ORACLE ASM SLOW TO START UP APPLIES TO AIX IV01369 Base
7100-01
JAVA WON'T INSTANTIATE IF PROT_NONE USED FOR IV16737 Base IV3885
SHARED MMAP REGION APPLIES TO AIX 7100-01 7
SYSTEM HANGS OR CRASHES WHEN APP USES Base IV2111
SHARED SYMTAB CAPABILITY. APPLIES TO AIX 7100- 6
01
SHLAP PROCESS FAILS WHEN SHARED SYMBOL TABLE Base IV2892
FEATURE IS USED. APPLIES TO AIX 7100-01 5
LOADING 5.3 TLS ENABLED LIBS BY 5.2 APPS CAUSED Base IV35283 Base IV3505
CORE DUMP IN 32B APPLIES TO AIX 7100-01 7
LINK FAILS WITH UNDOCUMENTED COMPILER FLAG Base IV42032 Base IV3913
AND THREAD-LOCAL STG APPLIES TO AIX 7100-01 6
RUNTIME LINKING FAILED TO BIND THE BSS SYMBOL Base IV4141
EXPORTED FROM MAIN APPLIES TO AIX 7100-01 5
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
27
A SPECIAL-PURPOSE LINKER FLAG WORKS Base IV4507
INCORRECTLY. APPLIES TO AIX 7100-01 2
ADD ABIILITY TO REORDER TOC SYMBOLS IN LIMITED Base IV45073
CIRCUMSTANCES APPLIES TO AIX 7100-01
ATTEMPT FAILED.OPENED STATE DOES NOT RETRY Base IV41362
NPIV LOGIN
IBM Oracle ICC Technical Notes
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171
© Copyright 2015, IBM Corporation
28
