Here is a page I downloaded before, I forget where it come from. Share with you all. I think this is a very good document for you.
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
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:
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_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=[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:
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/611609/viewspace-580626/，如需转载，请注明出处，否则将追究法律责任。