Chapter 17. Tuning AIX for OracleThis chapter provides an overview of running Oracle on AIX. We'll drill down into the many aspects of tuning AIX to run Oracle, examining memory, CPU, and I/O (both disk and network). We'll discuss in detail the Virtual Memory Manager and the tuning commands used to tune memory for Oracle. I'll go over some of the tools you can use to analyze bottlenecks and make changes to the system. Last, we'll look at a couple of Oracle tools that can help you with performance tuning. Because many of the AIX tuning commands and parameters have changed in recent years, Oracle has changed, too. Changes have also been made to tools such as the Oracle Enterprise Manager (OEM). As you'll see, this important utility is one you should definitely add to your repertoire and take the time to learn. 17.1. MemoryAs we discussed in earlier chapters, the AIX Virtual Memory Manager services all memory requests from the system, not just virtual memory. When RAM is accessed, the VMM must allocate space even when plenty of physical memory remains on the box. This point confuses both DBAs and systems administrators at times. The VMM works using a process called early allocation of paging space by partitioning segments into pages. These pages can be either RAM or paging space (virtual memory stored on disk). At the same time, it maintains a free list of unallocated page frames, which are used to satisfy page faults. The VMM's page-replacement algorithm assigns page frames and determines exactly which virtual memory pages currently in RAM will have their page frames brought back to the free list. The AIX operating system will use all available memory, other than memory that is configured to be unallocated — in other words, the free list. Obviously, administrators prefer to use physical memory rather than paging space when the physical memory is available. VMM classifies memory segments into two categories: persistent segments and working segments. Persistent segments use file memory, and working segments use computational memory. What does this mean to us? It's the computational memory that is used when your SQL queries access the Oracle database. These are working segments. They have no real permanent location and will terminate when the process is completed. On the other hand, file memory uses persistent segments that do have permanent 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. How do you tune the system? One critical parameter is the Translation Lookaside Buffer (TLB). Applications such as Oracle exploit a tremendous amount of virtual memory, so by using large pages you can increase performance substantially. Increasing the size of the TLB lets the system map more virtual memory, resulting in a lower miss rate for applications, such as Oracle, that use a lot of virtual memory. This category includes both online transaction processing and data warehouse applications. Oracle employs large pages for its System Global Area (SGA) because it is the SGA that really dominates virtual memory. To reiterate, in AIX 5.3 and later 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 computational memory or file memory. The first thing to do here is make sure the lru_file_ repage parameter is set to 0. This parameter, which was introduced in ML1 of AIX 5.3, determines whether the page-stealing algorithm should consider VMM 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 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 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 have happen until you've beefed up the free list by upping the number in maxfree. Use these values: vmo -p -o minfree=960 17.2. CPULet's start our discussion of CPU performance and Oracle with symmetric multithreading (SMT). This important POWER5 innovation provides the ability for a single physical processor to concurrently dispatch instructions from several hardware threads. In AIX 5L Version 5.3, a dedicated partition created with one physical processor is configured as a logical two-way by turning on SMT. With Oracle, you should always have SMT on: # smtctl A couple other important concepts to keep in mind:
17.3. Asynchronous I/O ServersAsynchronous I/O (AIO) determines whether Oracle waits for I/O to complete before starting new processing. What AIO does is let the system continue processing while I/O completes in the background. Performance improves significantly because processes can run at the same time that I/O is going on. However, if tuned improperly, AIO can significantly degrade the 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:
Code View:
Scroll
/
Show All
# iostat -A 1 5 The following parameters are used to monitor the AIO subsystem for the specified interval:
In the preceding example, AIO servers are not a system bottleneck. 17.4. Concurrent I/OConcurrent I/O (CIO), introduced in AIX 5.2, is an important system capability that you should use in your Oracle environment. Similar to its predecessor, direct I/O, CIO lets file system I/O bypass the VMM and transfer data directly to disk from the user's buffer. CIO permits multiple 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:
17.5. Oracle ToolsLet's look now at two Oracle-specific tools that can help you with your AIX administration. 17.6. StatspackStatspack is an Oracle performance diagnosis tool that I highly recommend Unix administrators learn to use. Once you have it set up and configured, which you do using SQL after Oracle is installed, it's not that complicated 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:
Code View:
Scroll
/
Show All
SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 18 19:21:21 2008 |
|
|
