The first good practice around the environmental aspects of Oracle is to use Optimal Flexible Architecture (OFA) standards to lay out your database. This provides for a logical as well as a physical setup and should even be used under Stripe and Mirror Everything (SAME) setups. The OFA principles break down simply to: isolate redo, rollback, temp, data and index files as much as possible. For SAME configurations use the proper stripe width and depth or performance will suffer (it must be a factor of db_block_size*db_file_multiblock_read_count).
The next critical environmental good practice is to verify proper operating system set points. For example, in Unix/Linux environments, set shared memory and semaphores properly. Shared memory segments should be set to at least the desired System Global Area (SGA) size + 10% but not more than ½ total memory. Set semaphores to (n1 + n2 + n3…)+(2* nx) where n1…n3 are the semaphores for each instance on the box and nx is the largest value from any instance on the box.
For Unix and Linux set swap area size to at least the size of the largest expected SGA or to a multiple of the size of physical memory.
For Windows, set proper background/foreground settings and virtual memory, and set the server for application serving and not file serving.
Another frequently overlooked good practice is to utilize file system tunings. Some examples of this are: use asynchronous I/O (where appropriate, verify OS and file system support this) and turn off write-ahead caching. Tied in with this is the good practice of reducing buffering at the OS level. Generally speaking the closer to the disk you can place the buffer (for read operations) the better off you are. With more and more shops running Linux on commodity-priced Intel boxes, you may also need to consider tuning the IDE interface (if you use IDE drives for data or temporary areas). On Linux the hdparm command is used to both view and set the IDE parameters and I have found that they are usually set inefficiently by default installations.
With the environment comes the responsibility for such things as disk controllers. Disk controllers should utilize multiple channels and use channel load balancing, for example, use Veritas fastpath. As was said above, use proper stripe width and depth -- a good practice is to set stripe width (amount of each stripe on each disk) to at least db_block_size*db_file_multiblock_read_count. Set stripe depth (number of disks in the stripe set) to at least (expected maximum IO/sec)/90 for RAID1+0 or (expected maximum IO/sec)/50 for RAID5.
The DBA should always review all provided disk array configurations for proper stripe width and depth. For example EMC has provided preconfigured arrays in the past set at 8K stripe width, but this size is not acceptable for Oracle applications.
The environment extends beyond disks and memory. The connectivity to the outside world comes from the network. Use the fastest Network Interface Cards (NICs) possible. In order to gain from fast NICs, you must tune TCP buffers to attain maximum throughput; on Unix and Linux this is accomplished through setting the proper buffers (for example on Linux setting the values for /proc/sys/net/ipv4/tcp_wmem and /proc/sys/net/ipv4/tcp_rmem can make a significant difference in performance, setting as high as 4 meg are possible).
The other side of this is tuning the Oracle packet sizes through the use of TDU and SDU settings. These can be set as high as 32K and can provide instant performance increases for data-intensive operations across the network.
For Oracle RAC (which uses the Unsecure Data Packet protocol) tune the UDP buffers; these are set in a similar manner to the TCP buffers. You should also utilize the CLUSTER_INTERCONNET setting for multiple NIC card systems and RAC to ensure that the proper NIC is being utilized for the cluster interconnect.
Some general good practices for the environment are:
- Do not place non-Oracle applications on Oracle servers.
- Minimize placing multiple databases on a single server (unless the server has been properly sized and configured for multiple instances).
- Do not place applications with different maintenance windows and backup recovery requirements in the same database.
Return to Mike Ault's Oracle "good practices."
About the author
Mike Ault is an Oracle database specialist at Quest Software and a recognized Oracle expert with over 16 years' experience as an Oracle DBA and consultant in a variety of industries and companies. A prolific author, Mike has published over 20 Oracle-related books including Oracle Administration and Management, Oracle DBA OCP ExamCram and Oracle10g Grid and RAC. He is a regular contributor to trade publications including Oracle magazine, and frequently presents at major Oracle conferences such as IOUG.
