Performance Tuning WebWizard

Oracle RDBMS Tuning for AIX

Use this WebWizard to tuning your Oracle RDBMS running on an IBM pSeries with AIX. This WebWizard assumes you are a Database Administrator (DBA).

Note these are the AIX specific tuning tips. See also the UNIX general tuning tips.

We take no credit for this information as its taken from the Oracle Manuals.

Tip 1 - Use Asynchronous I/O

In the init.ora configuration file set: use_async_io=true

Then set the minservers and maxservers using SMIT->Devices->Asynchronous I/O->Change/Show Characteristics of Asynchronous I/O (or just type smit aio) to:

MaxServers = 10 * number of disks
MinServers = MaxServers /2

This is likely to increase performance by 6 - 8%.

Tip 2 - Use Parallel Recovery

In the init.ora configuration file set: recovery_parallelism=[number of CPUs but not less than 2]

This is likely to increase recovery processing by 0 - 50%.

Tip 3 - Use Logical Volume Manager

To spread out the data across disks you could use Oracle files or the AIX LVM. It is strongly recommended that the LVM is used. Striping data across disks is very effective as it makes full use of the disks in usage terms, makes excellent use of read ahead for sequential I/O and spreads I/O evenly (better perforamnce). For striping use the following:

Stripe unit size 64KB
max_coalesce 64KB
minpgahead 2
maxpgahead 16
Note: the striped LV size must be a multiple number of the drives used.
Note: striped data and log must be on different sets of dicks.

Note: AIX LVM does not currently allow striping and mirroring at the same time.

This is likely to increase performance by 0 - 500%.

Tip 4 - Use readv()

In the init.ora configuration file set: use_readv=TRUE

This effectively ask the AIX Kernel not to buffer reads (particularly JFS files) and should increase performance.

Note this can make performance worse so test this firsts.

Tip 5 - Use db_file_multiblock_read_count

In the init.ora configuration file set: db_file_multiblock_read_count=[8 or 16]

This should be set to db_block_size*db_file_multiblock_read_count is greater than the LVM stripe size.

Tip 6 - Use JFS or Raw Partitions

This is a well worn subject with agruments on both sided.

JFS - If your database is not I/O bound i.e. you do lots of computation on data retrieved the JFS will be nice because its simpler to administer and backup/recover.
Raw partitions/raw disks/raw logical volume - otherwise use these for performance.

Moving to Raw Disks is likely to increase performance by 0 - 50%.

Tip 7 - Direct I/O

Not implemented on AIX.

Tip 8 - Use Write Behind

Disable the AIX feature by setting the AIX parameter using: vmtune -c 0

Note to set the AIX parameter back to normal: vmtune -c 8

This might not be suitable unless the machine is solely a database server.

Tip 9 - Tune Sequential Read Ahead

The Virtual Memory Manager spots sequential reading of files by watching the access pattern. After a number of reads in order are noticed, it will attempt to read upto maxphahead blocks of the file in adavance. By default these are:

minpgahead 2
maxpgahead 8

These can be increased to increase sequential reading of data using: vmtune -r 512 -R 1024

Keep the numbers powers of 2.

Tip 10 - Tune Disk I/O Pacing

Disk I/O pacing is an AIX feature that stops disk I/O intension applications flooding the CPU. This is done with low and high water marks via: smit->System Environment->Change/Show Characteristics of OS.

Be careful as this can hurt performance if not set correctly.

Tip 11 - Using RAID

Raid can improve read performance but is slow in write. For performance never use RAID as random I/O is typically 4 times slower.

Tip 12 - Disk Geometry Considerations

On AIX you can place data of particular parts of the disk. The middle part of the disk being the fastest as it reduces seek times.

This may increase performance by 10%

Tip 13 - Use Processor Binding on SMP

Certain processes can be locked to run on a particular CPU. This increases level 1 cache hits but the process cannot then float to unused CPU's if its CPU is busy. Use the bindprocessor command.

Note: do not use bind processor on AIX 3

You can use this feature to bind the main Oracle processes to different CPUs with good effect. Also, if the SQL*Net listener is bound its forked off servers for use connection are also bound.

This may increase performance by 15%

Tip 14 - Spin Count on SMP

This can reduce Oracle internal latch contention. In the init.ora configuration file the default is: spin_count=2000

Increasing this means the process will spin longer waiting for the process on other CPUs to free the latch so it can continue. Setting this to 0 can help on single CPU machines or when CPU usage is very high.

Tip 15 - Process Priority

Warning: getting this wrong may crash your machine.

Only the root user can set this using the setpri() system call.

Increasing the priority (reducing the number) can improve performance if there are lots of runnable processes on the machine. Oracle provide a setorapri command to do this: setorapri 39

This may increase perforamnce by 15%

Tip 16 - Buffer Cache Paging

For JFS database there can be a copy of the SGA block in the Buffer Cahce too. This can effect performance and cause I/O bottlenecks. There are four AIX buffer cache tuning parameters:

minfree - below this page stealing starts trying to reclaim memory pages
maxfree - above this page stealing stops
minperm - minimum number of pages allocated to File I/O
maxperm - maximum number of pages allocated to File I/O

Increase minfree and maxfree so that read ahead pages do not reduce free pages to zero and there is alway free memory.

Tip 17 - File Buffer cache

You can adjust the minperm and maxperm (see tip 16) to effect the size of the buffer cache resources.

This depend so much on the workload and I/O characterists of your database that its difficult to recommend particular values. Try: vmtune -p 30 -P 60

The defaults are 20 and 80.

Tip 18 - Paging Space

Never run out of paging space. Two to three time RAM is typical. Use: lsps -a to determine the size and use of paging.

Tip 19 - Block Size

db_blcok_size=4096 for small, JFS and OLTP/mixed workload databases
db_block_size=8192 for raw lare DSS workload

Tip 20 - Redo Buffer Latch

Set the following init.ora file parameters:

log_small_entry_max_size= 0
log_simultaneous_copies=[2 times the number of CPUs]

Tip 21 - Archiver Buffers

The log_archive_buffer_size effects the perforamcne of the archiver which is used to copy log files to other resources so they can be reused later. Set the init.ora file parameter:

log_archive_buffer_size=[upto 128]
log_archive_buffer=[default of 4]

Over doing this can degrade performance but may give 20% better performance.

Tip 22 - SGA Size

The SGA must not be paged or swapped out. The amount of memory that can be allocated to the SGA depends on:

high numbers of users need more SGA
actual RAM available
if the machine is a DB server or stand alone

Set the init.ora parameters:

db_block_buffers

shared_pool_size

For example as a rough guide:

System type Stand alone Server only
OLTP 30% of RAM 40% to 60% of RAM
DSS 40% to 70% of RAM 50% to 80% of RAM

Tip 23 - SQL*Loader I/O Buffers

While loading data with SQL*Loader it ends up waiting for the I/O to complete. Increasing the BUFFERS parameter will improve load performance.

Tip 24 - Out-of-bound breacks with SQL*Net TCPIP

This is the communications use between client and server and by default is switched on (orasrv does this).

This may increase perfromance by 20%

Tip 25 - Post-Wait Kernel Extension

This reduces the overhead of semaphore operations.
Make sure the correct version is installed.

This may increase performance by 20%

Tip 26 - TCPIP

SQL*Net V2 uses 2KB packet sizes. The underlying packet size is 1KB for most installations.
This can be changed with SQL*Net connection string parameters.

It is not recommended to change this as it can degrade perforamnce.

Tip 27 - Compliling Pro*C

Use the right optimisation level

Compile for the right chipset:

-qarch=COM for the common mode (i.e. runs on everything)
-qarch=PWR for POWER only machines
-qarch=PWRX for POWER2 only machines
-qarch=PPC for POWERPC only machines

Go to the monitor WebWizard to find out the current performance

System type	Stand alone	Server only
OLTP	30% of RAM	40% to 60% of RAM
DSS	40% to 70% of RAM	50% to 80% of RAM