[Milberg09] Chapter 17. Tuning AIX for Oracle

Chapter 17. Tuning AIX for Oracle

Thischapter provides an overview of running Oracle on AIX. We'll drill downinto the many aspects of tuning AIX to run Oracle, examining memory,CPU, and I/O (both disk and network). We'll discuss in detail theVirtual Memory Manager and the tuning commands used to tune memory forOracle. I'll go over some of the tools you can use to analyzebottlenecks and make changes to the system. Last, we'll look at acouple of Oracle tools that can help you with performance tuning.

Becausemany of the AIX tuning commands and parameters have changed in recentyears, Oracle has changed, too. Changes have also been made to toolssuch as the Oracle Enterprise Manager (OEM). As you'll see, thisimportant utility is one you should definitely add to your repertoireand take the time to learn.

17.1. Memory

Aswe discussed in earlier chapters, the AIX Virtual Memory Managerservices all memory requests from the system, not just virtual memory.When RAM is accessed, the VMM must allocate space even when plenty ofphysical memory remains on the box. This point confuses both DBAs andsystems administrators at times.

The VMM works using a process called early allocation of paging spaceby partitioning segments into pages. These pages can be either RAM orpaging space (virtual memory stored on disk). At the same time, itmaintains a free list of unallocated page frames, which are used tosatisfy page faults. The VMM's page-replacement algorithm assigns pageframes and determines exactly which virtual memory pages currently inRAM will have their page frames brought back to the free list.

TheAIX operating system will use all available memory, other than memorythat is configured to be unallocated — in other words, the free list.Obviously, administrators prefer to use physical memory rather thanpaging space when the physical memory is available.

VMMclassifies memory segments into two categories: persistent segments andworking segments. Persistent segments use file memory, and workingsegments use computational memory. What does this mean to us? It's thecomputational memory that is used when your SQL queries access theOracle database. These are working segments. They have no realpermanent location and will terminate when the process is completed.

Onthe other hand, file memory uses persistent segments that do havepermanent locations on the disks. Persistent segments remain in memory,usually until the pages are stolen or the database is recycled. Again,you want the file memory paged to disk but not the computational memory.

Howdo you tune the system? One critical parameter is the TranslationLookaside Buffer (TLB). Applications such as Oracle exploit atremendous amount of virtual memory, so by using large pages you canincrease performance substantially. Increasing the size of the TLB letsthe system map more virtual memory, resulting in a lower miss rate forapplications, such as Oracle, that use a lot of virtual memory. Thiscategory includes both online transaction processing and data warehouseapplications.

Oracleemploys large pages for its System Global Area (SGA) because it is theSGA that really dominates virtual memory. To reiterate, in AIX 5.3 andlater releases, you use vmo to tune; earlier releases used vmtune.

The following vmo command uses the lgpg_size and lgpg_regions parameters to allocate 16,777,216 bytes to provide large pages, with 256 actual large pages:

# vmo -r -o lgpg_size=16777216 lgpg_regions=256

At the same time, with Oracle Database 10g, make sure the LOCK_SGA Oracle initialization parameter is set to TRUE so that Oracle requests large pages when allocating shared memory.

By far, the two most important vmo settings are minperm and maxperm.These parameters determine whether the system favors computationalmemory or file memory. The first thing to do here is make sure the lru_file_ repageparameter is set to 0. This parameter, which was introduced in ML1 ofAIX 5.3, determines whether the page-stealing algorithm should considerVMM repage counts and dictates the type of memory it should steal.

The default value for lru_file_repage is 1, so we need to change this setting using vmo:

# vmo -o lru_file_repage=0

Setting lru_file_repage to 0

Setting lru_file_repage to 0 tells the VMM that you want to steal only file pages and not computational pages. Because this behavior will change if numperm is less than minperm or greater than maxperm, we should also set maxperm high and minperm very low. (Years ago, before the introduction of the lru_file_repage parameter, we used to make maxperm low. If you did this now, you would stop the application caching programs that are currently running.)

Let's change the relevant parameters:

# vmo -p -o minperm%=5
# vmo -p -o maxperm%=90
# vmo -p -o maxclient%=90

You also want to take a look at minfree and maxfree. When the pages on the free list fall below minfree,the VMM will start to steal pages, something you don't want to havehappen until you've beefed up the free list by upping the number in maxfree. Use these values:

vmo -p -o minfree=960
vmo -p -o maxfree=1088

17.2. CPU

Let'sstart our discussion of CPU performance and Oracle with symmetricmultithreading (SMT). This important POWER5 innovation provides theability for a single physical processor to concurrently dispatchinstructions from several hardware threads. In AIX 5L Version 5.3, adedicated partition created with one physical processor is configuredas a logical two-way by turning on SMT. With Oracle, you should alwayshave SMT on:

# smtctl

This system is SMT capable.
SMT is currently enabled.
SMT boot mode is not set.
SMT threads are bound to the same virtual processor.
proc0 has 2 SMT threads.
Bind processor 0 is bound with proc0
Bind processor 1 is bound with proc0

A couple other important concepts to keep in mind:

• Processor affinity lets processes run on specific processors. You can actually correlate specific processes with running processes.

• The nice and renice commands change the priority of running processes. It is not recommended to renice Oracle processes.

17.3. Asynchronous I/O Servers

AsynchronousI/O (AIO) determines whether Oracle waits for I/O to complete beforestarting new processing. What AIO does is let the system continueprocessing while I/O completes in the background. Performance improvessignificantly because processes can run at the same time that I/O isgoing on. However, if tuned improperly, AIO can significantly degradethe overall performance of writes on the I/O subsystem.

You can use the iostat or nmon command to monitor the AIO subsystem. Let's fire up iostat:

# iostat -A 1 5

System configuration: lcpu=2 drives=2 ent=0.25 paths=2 vdisks=2
aio: avgc avfc maxgc maxfc maxreqs avg-cpu: %user %sys %idle %iowait %physc %entc
        0    0   312     0    4096            3.1  7.1  89.8     0.0    0.0  16.7
Disks:        %tm_act     Kbps      tps    Kb_read    Kb_wrtn
hdisk1            0.0      0.0      0.0          0          0
hdisk0            0.0      0.0      0.0          0          0

    

The following parameters are used to monitor the AIO subsystem for the specified interval:

• avgc — Average global AIO request count per second

• avfc — Average fastpath request count per second

• maxgc — Maximum global AIO request count since the last time this value was fetched

• maxfc — Maximum fastpath request count since the last time this value was fetched

• maxreqs — Maximum number of AIO requests allowed

In the preceding example, AIO servers are not a system bottleneck.

17.4. Concurrent I/O

ConcurrentI/O (CIO), introduced in AIX 5.2, is an important system capabilitythat you should use in your Oracle environment. Similar to itspredecessor, direct I/O, CIO lets file system I/O bypass the VMM andtransfer data directly to disk from the user's buffer. CIO permitsmultiple threads to read and write data concurrently to the same file,letting users read and write simultaneously.

To turn on CIO, mount your file systems using the cio flag:

# mount -o cio /orafilesystem

Elements to consider when using CIO include:

• Raw devices — Although some Oracle DBAs like to create raw logical volumes for their data (and there is little argument about the performance benefit of doing so), in most cases this functionality is too difficult to administer, and I've found that the Unix administrators can talk the Oracle DBAs out of this one. With the advent of CIO, I would not use raw logical volumes unless performance is the driving factor behind everything you're doing and you have a staff that can manage the complexities inherent in this type of environment.

• Spreading the wealth — The more spindles you have, the more you should spread your wealth around. The more adapters you have, the more your performance will also increase. In addition, try to keep indexes and redo logs off the same volumes as your data.

• Storage area network (SAN) — Make sure you spend time looking at your SAN. Optimizing the hardware will help you more than anything you can do at the operating system level.

17.5. Oracle Tools

Let's look now at two Oracle-specific tools that can help you with your AIX administration.

17.6. Statspack

Statspackis an Oracle performance diagnosis tool that I highly recommend Unixadministrators learn to use. Once you have it set up and configured,which you do using SQL after Oracle is installed, it's not thatcomplicated to use.

Statspack provides two basic collection options: level and threshold. The level parameter controls the type of data collected from Oracle. The threshold parameter acts as a filter for the collection of SQL statements into the status summary tables.

To install Statspack, simply log on to the system as Oracle, start up sqlplus, and then follow the steps as instructed:

SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 18  19:21:21  2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: system as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> execute
SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Oracle Enterprise Manager
choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can store
temporary data (e.g., for sort work areas). Specifying the SYSTEM tablespace
for the user's temporary tablespace will result in the installation FAILING,
as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's temporary tablespace.

17.7. Oracle Enterprise Manager
The Oracle Enterprise Manager (OEM) is a very useful and productive toolthat I've used for years. To use this Web-based utility, you need tomake sure you let it run when installing Oracle or creating a databaseusing the Oracle dbca utility. After the database is created, turn on OEM with this command:
$ emctl start dbconsole
Then enter the following in your browser to access the tool:
http://lpar21ml16ed_pub:5505/em
There is so much you can monitor and tune within OEM that whole books existon this utility. If you are working in an Oracle environment, this is amust-use system tool.
Figure 17.1 shows the graphical OEM display.
Figure 17.1. Oracle Enterprise Manager