Part No. A25584-1
Copyright ©1995 Oracle Corporation
Author: Rajesh Ragoobeer
Contributors: Henry Dai, Carlos Godinez, Karl Haas, Andrew Holdworth, Matthew Kagle, Peter Lai, Cindy Lim, Allan Packer, Make Sakayeda, Don Tersell, Paul Turner, Randy Urbano
This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.
If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:
Restricted Rights Legend
Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).
Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.
If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).
The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error-free.
ORACLE, CASE*Designer, CASE*Dictionary, Easy*SQL, SQL*Calc, SQL*DBA, SQL*Forms, SQL*Loader, SQL*Menu, SQL*Net, SQL*Plus, SQL*Report, SQL*ReportWriter, Pro*Ada, Pro*COBOL, Pro*FORTRAN, and Pro*Pascal are registered trademarks of Oracle Corporation. Cooperative Development Environment, Oracle7, Pro*C, Oracle*Mail, Oracle*Terminal, CASE*Generator, and PL/SQL are trademarks of Oracle Corporation. UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open Company Limited.
All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.
These programs were not developed for use in any nuclear, aviation, mass transit, medical, or any other inherently dangerous applications. It is the customer's responsibility to take all appropriate measures to ensure the safe use of such applications if the programs are used for such purposes.
The purpose of this guide is to help Sun users improve the performance and scalability of Oracle7 database server on Sun/Solaris systems.
This guide needs to be used in conjunction with the Oracle for UNIX Performance Tuning Tips (part number A22535-1) that covers generic UNIX tuning tips and other generic Oracle documentation that deals with Oracle tuning issues. This document expands on those tuning tips that require further Solaris specific information related to the use of Oracle7 on the Sun/Solaris 2 platforms.
Readers are also recommended to first consult the generic Oracle and Solaris tuning documentation before referring to this document. See the "Useful References" section at the end of this document.
The amount of effort you should put into tuning of each of the above areas with respect to your environment depends on your application and workload characteristics. However, in our experience the biggest gains (apart from application redesigning and SQL tuning) are achieved by reducing disk and network I/O.
Oracle7 takes full advantages of asynchronous I/O (AIO) provided by Solaris 2 for faster database access. The block diagram (Figure 1) below illustrates the AIO and the Logical Volume Manager (LVM) on the Sun/Solaris platform. AIO interleaves multiple I/O operations to improve I/O subsystem throughput. LVM reduces disk contention by striping data across multiple disk spindles (see the "Use a Logical Volume Manager" section for more details). AIO used together with LVM significantly improves RDBMS performance.
Figure 1 - 1. Asynchronous I/O in Solaris 2.4
On Solaris 2, AIO is enabled by default. AIO is supported for database files created on either file system partitions or raw devices.
Note: On Solaris 2, AIO for cooked or UFS (UNIX File System) files are done by employing threads to parallelize the disk requests. Refer to the "CPU Scheduling and Process Priorities" section on the implications of using AIO with UFS based datafiles.
With Solaris 2.4, AIO on raw devices is further enhanced by additional technology called KAIO (kernelized AIO). KAIO improves performance by reducing the context switch overhead. This added benefit is transparent to database users and requires no changes in Solaris or Oracle configuration. KAIO is supported on Solaris 2.3 via a patch obtainable from Sun.
KAIO on raw devices accompanied by LVM yields the best overall DBMS performance and scalability.
Depending on your initial configuration, your database file distribution, your system resource limitations, your overall system load, and most importantly, the nature of the workload, the performance benefit that you might see is roughly 0 - 50%.
Note: In Oracle 7.1, additional performance gains can be achieved by setting the init.ora parameter RECOVERY_PARALLELISM = N (where N is an integer greater than 1; usually set to the number of processors on the machine). Here multiple slave recovery processes are spawned to partition the task of applying the logfile changes to the database.
Depending on your database configuration, your system resource limitations, and your overall system load, the performance benefit (for database recovery only) that you might see is roughly 0 - 50%.
Tip No. 2: Use a Logical Volume Manager (LVM)
LVM provides the capability of striping data across multiple disk spindles to reduce disk contention. It is also transparent to ORACLE. Generally, in most environments one cannot exactly determine the characteristics a priori. Thus by using an LVM effectively one can spread the I/O more randomly, resulting in greater overall performance gains. We recommend using the LVM (also referred to as machine striping) over manual striping (striping without using LVM) for almost all workloads. The following logical volume manager products are currently available on Solaris 2:
Note: If the stripe size is not specified, it defaults to the cylinder size of the first disk encountered. This may be in the order of a megabyte on some disks, and may hurt ORACLE performance in some cases (especially workloads involving OLTP activity) since RDBMS I/O requests are typically smaller.
There are some issues to consider with LVM. First, avoid using partitions from the same physical disk if these partitions are likely to be accessed simultaneously. For example, since the datafiles and logfiles are accessed simultaneously most of the time, datafiles and logfiles should be striped on different sets of disks. Otherwise, this will result in worse performance due to the excessive disk-head seek activity. This degradation is even worse if the partitions on a disk are far apart from each other.
Second, for capacity planning, use partitions of uniform size when creating a logical/virtual disk. Using disk partitions of different sizes wastes disk space, since the sizes of the partitions in the logical disk is limited by the smallest partition.
Third, use additional LVM features to minimize the impact of the reduced effective mean time between failures (i.e. increased likelihood of failure) of a virtual disk. These high availability features include:
Fourth, make sure that the I/O activity is evenly dispersed across multiple disk spindles by using Solaris utilities like sar, iostat or other performance tools to identify any hot disks.
Performance gains from efficiently using the LVM can vary greatly depending on the LVM used and the characteristics of the workload. For decision support system (DSS) workloads we have experienced excellent scalability (0-500%). For OLTP type workloads or mixed workloads one can still expect significant performance gains ranging from a few percent to over 100%.
Tip No. 3: Using readv()
The readv() system call increases I/O throughput for sequential read activity by reducing the CPU overhead associated with buffer copying. On Solaris 2, we have observed good performance improvement when using readv() on datafiles created on UFS.
However, our experiments on Solaris 2 have revealed that readv() degrades performance when database files are created on raw partitions. We are currently working with Sun engineers to fix this problem. Therefore, readv() should not be used on databases where there are datafiles created on raw partitions.
By default readv() is not used by ORACLE i.e. the init.ora parameter USE_READV = FALSE. To use readv() set the init.ora parameter USE_READV = TRUE.
The expected performance gain when using readv() with UFS based datafiles is 10-20%. The performance degradation from using readv() on raw devices is observed to be 30-50%.
Tip No. 4: Use DB_FILE_MULTIBLOCK_READ_COUNT
The init.ora parameter DB_FILE_MULTIBLOCK_READ_COUNT usually yields better I/O throughput for large values. On Solaris 2, this parameter ranges from 1 to 512, but changing this beyond 32 usually does not provide any additional performance gain.
This parameter should be set so that DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT is greater than the LVM stripe size. This causes more disk spindles to be employed in satisfying an I/O request.
Tip No. 5: Using Raid Capabilities
RAID-5 enhances sequential read performance, but degrades overall write performance. Additional work is currently being done to more precisely quantify this performance gain.
Tip No. 6: Disk Geometry Considerations
The use of ZBR (zone bit recording) technology from Sun causes the outermost cylinders to be more efficiently utilized. This results in better overall performance when the outer cylinders are used compared to the inner cylinders.
Some users have seen 0-10% increase in I/O throughput for large I/O requests.
Tip No. 7: Using UFS vs. Raw Partitions
This topic generates a fair amount of debate from time to time. Some of the reasons for this include the following:
On lightly loaded systems, where there isn't heavy contention for UNIX buffers, sequential reads (especially repeated reads) benefit from UFS substantially. The reasons for this is twofold:
1. Presumably, filesystem read-ahead capability kicks in when it detects large sequential read accesses.
2. The data ends up in the UNIX kernel buffer cache which speeds up subsequent scans on the same object.
3. The logical block size for the filesystem is configured when you create the filesystem. This can be chosen to match with the Oracle database blocksize.
Another advantage is the opportunity to use the Solaris system call readv() which boosts sequential read performance. See the earlier tip on Using readv() for more detailed information.
Another major disadvantage becomes apparent on heavily loaded systems where contention for UNIX buffers end up becoming the main bottleneck. Some side-effects include:
1. More work to be done by the pagedaemon that flushes data from the filesystem buffer cache to disk, when it awakens at regular intervals.
2. More memory sacrificed to the UNIX buffer cache.
Yet another disadvantage is the inability to use KAIO on Solaris 2.4. See the "Use Asynchronous I/O" section for more detail.
On an overall basis however, our experience on the Sun Solaris platform, has revealed better overall performance and scalability using raw devices compared to filesystems, depending on the workload. Therefore, in a heavily utilized database configuration we tend to encourage users to use to raw devices.
$ dd if = /home/myoldUFSfile of = /dev/rdsk/mynewRAWdevice bs=32k
3. On configurations where memory is scarce, you may need to resize your UNIX buffer cache. See the "Memory and Paging" section for more information.
4. In the Solaris environment, we encourage the use symbolic links whenever possible since the name of the raw device can change under certain circumstances e.g. when reconfiguring disk peripherals or moving the database to a new hardware configuration. Please consult you Sun documentation for more information.
Please refer to the Oracle7 for Sun SPARC Solaris 2.x Installation & Configuration Guide for more on the operation issues involved in using raw devices.
Unfortunately, direct I/O is not currently implemented in Solaris 2.4. This may be implemented in the future and will go further in improving the I/O throughput with UFS based datafiles.
Binding certain processes to a processor could increase performance substantially. Processor binding is available and fully functional with Solaris 2.
Note: In the past, however, with Solaris 2.2, there were some problems with processor binding inheritance where the child processes of a bound process were not automatically bound. This has been fixed.
Processor binding on Solaris 2 is not done automatically. On a multi-processor machine, you need to explicitly bind a process to a processor by using the pbind command (see man pages). Only the super-user or the Oracle owner can bind an Oracle7 process to a processor. Since, pbind is inherited by the child processes this leads to the following considerations:
Warning: If you are using UFS (with Solaris 2) or raw devices (with Solaris 2.3 and earlier), together with asynchronous I/O, you should never bind the database writer process (dbwr) since the asynchronous I/O employs threads to parallelize the write requests. Therefore, binding the dbwr process to a certain processor will cause all the writes to be serviceable only by one processor! This will lead to a degradation in performance. This can be somewhat alleviated with the ability to bind a process to a group of processors (processor group affinity), a feature that may be available in future releases of Solaris.
However, if you are using raw devices (with Solaris 2.4 and later) together with asynchronous I/O, there is no problem with binding the database writer process. This is due to asynchronous I/O for raw devices being kernelized (KAIO) on Solaris 2.4 onwards. resulting in the parallel asynchronous write operations being done in the Solaris kernel.
In the absence of processor affinity, we recommend binding the various Oracle background processes (except dbwr) to different processors and leaving one processor free to service dbwr. The guarantees the dbwr a processor on which to execute and at the same time allows dbwr to float freely to the other processors as well if it becomes CPU bound.
Note: Processor binding is a very complicated issue and should be dealt with care. Processes bound to a processor cannot migrate to different processors even if the latter are free. This might adversely affect the application performance. Because of this, an environment of homogenous applications with balanced load is more suitable for processor binding.
The binding of a process to a processor is not exclusive, i.e. under certain circumstances the processor is free to execute other processes as well.
The general rules of processor binding are to provide higher priority applications with a relatively bigger share of CPU times, to maintain the process context for a longer period, and to evenly distribute the application load across the available processors.
For instance, let's say you have 100 SQL*Forms applications on various client machines communicating via SQL*Net V1 to an Oracle7 instance on a 4-processor server. Of the 100 applications, say applications 1-10 have higher priority. A binding scheme would be to bind the Oracle server processes for the 10 high priority applications to processor 0, and distribute the servers for the rest of the applications evenly across processors 1, 2, and 3. To do so, you would first start 4 orasrv processes (one for each processor) on 4 different ports:
% tcpctl port 1256 start & [1] 9807 % tcpctl port 1257 start & % tcpctl port 1257 start & [1] 9812 % tcpctl port 1258 start & [1] 9833 % tcpctl port 1259 start & [1] 9850
Next you would bind each of the listeners to the 4 processors respectively:
% pbind -b 0 9807 % pbind -b 1 9812 % pbind -b 2 9833 % pbind -b 3 9850
Applications 1-10 will then connect to the database through port 1256, applications 11-40 through port 1257 and so on. For example, for applications 41-70:
% setenv TWO_TASK T:dragon/1258:oracle % runform
Since the orasrv process listening on port 1258 is bound to processor 2, all Oracle servers for applications 41-70 will be bound to processor 2 automatically.
A similar strategy can be adopted for SQL*Net V2. The performance benefit that is possible is 0-15%. Scalability is enhanced between 20-30% in heavily loaded systems with large number of CPUs.
Processor binding of Oracle processes might have negative effect on the performance of non-Oracle applications running on the same machine. Careful tuning is strongly recommended.
Depending on your configuration, your system resource limitations, and your overall system load, the performance benefit that you might see is 0-15%.
Tip No. 10: Tune Spin Count on Multi-processor Systems to Reduce Latch Contention
Making a process goes to sleep after making an initial attempt to acquire a resource turns out to be a costly operation. Alternatively, a process could spin after making the initial attempt, and then go to sleep after the second attempt. This strategy results could yield better performance but could incur more CPU overhead.
The default value of SPIN_COUNT init.ora parameter for Oracle7 for Solaris 2 is 2000 which is adequate for most applications. Increasing SPIN_COUNT usually results in greater CPU utilization. Setting SPIN_COUNT to zero (no spinning) is more appropriate for single-processor machines or machines where the CPU utilization it high. We do not usually recommend changing this on Solaris.
Tip No. 11: Real-Time Scheduling
In addition to the normal UNIX scheduler on Solaris, real-time scheduler is also provided for real-time applications in general. Under certain circumstances, e.g. OLTP workloads involving short running transactions, bumping up the priority of a set of processes to real-time may deliver a performance boost.
Warning: We do not recommend using real-time priority for the Oracle RDBMS or applications in a production environment since the performance can be severely degraded if done haphazardly or incorrectly. Only in very limited situations should this option be considered:
1. A real-time process control applications where this feature may be essential for the proper functioning of the applications.
2. In some environments where the durations of the transactions are well-known and configurable.
The Solaris 2 process scheduler is priority based. A process with higher priority will be scheduled and run before any lower priority processes. Solaris 2 supports three priority classes: timesharing, system, and real-time. By default, all real-time processes have higher priorities than any system process, and all system processes have higher priorities than any timesharing process.
The table below, taken from Sun documentation, illustrates the scheduling policy and priorities. By default, all user (application) processes are in the timesharing class and system processes (page daemon, etc.) are in the system class. Real-time class is introduced so that critical processes can always get the CPU as soon as it can be run, even if processes in other classes are ready to run or, to some extent, already running. Oracle performance can be improved by putting the Oracle processes in the real-time class.
| Scheduling Order | Global Priority | Scheduler Class |
| First | 159 . 100 | Real-Time |
| 99 . 60 | System | |
| Last | 59 . 0 | Timesharing |
Attention: As long as there is a real-time process ready to run, no system process or timesharing process runs. Other real-time processes can run only if they have a higher priority. Real-time processes managed carelessly can have a dramatic negative effect on the performance of non-real-time processes and might result system panic. Putting Oracle processes in the real-time class can seriously affect the performance of other processes that are not in real-time class, including the system processes. Also, real-time scheduling should only be considered, if the Oracle database files are built on raw devices otherwise the pagedaemon, which is in the system class (whose priority cannot be changed), will have a large amount of work to do (since more pages in the UNIX buffer cache could require flushing), thus negatively affecting real-time operation.
The user command priocntl (see man page) can be used to move a process between timesharing and real-time classes. In order to change the class of a process to real-time, the user invoking priocntl must have super-user privilege. Priority classes are inheritable. Another words, child processes by default belong to the same priority classes as their parent processes do.
You can use the ps command with the -c option to find out what priority class a process belongs to. The class information is under CLS column of the ps output.
Examples:
The following example shows how you can put Oracle processes in the real-time class.
1. Find out the process ID of the current shell:
% echo $$24272. Become super-user and put the shell in the real-time class:
# priocntl -s -c RT -i pid 24273. Start up Oracle instance and SQL*Net listener processes.
Since the shell is in the real-time class, all Oracle background processes and the listener processes will be in the real-time class as well due to class inheritance. Furthermore, all Oracle server processes spawned by the listener will be in the real-time class as well.
It is more difficult to put application processes in the real-time class since usually they do not collectively belong to a single parent process. Here are some of the ways you can put processes in the real-time class (see man page for priocntl for more options):
1. To put SQL*Plus processes with process IDs 3482 and 3483 in real-time class:
# priocntl -s -c RT -i pid 3482 34832. To put the Oracle processes owned by user `orausr' with user ID 8888 in the real-time class:
# priocntl -s -c RT -i uid 8888Note: This command will put all processes owned by user `orausr' in the real-time class, not just the Oracle processes.
3. To put the Oracle processes owned by users in group `oragrp' with group ID 435 in the real-time class:
# priocntl -s -c RT -i gid 435Note: This command will put all processes owned by users in group `oragrp' in the real-time class, not just the Oracle processes.
4. To start a SQL*Plus process in the real-time class:
# priocntl -c RT -e sqlplus
Adjusting Priority within Real-Time Class
You can also adjust priorities within the real-time class. You can use this feature to give certain Oracle processes higher priorities than others within the real-time class. Again, you should fully understand the effects before proceeding.We have experienced 10-15% gains in performance for some OLTP workloads.
Memory and Paging
Tip No. 12: Controlling Buffer-Cache Paging ActivityExcessive paging activity hurts performance significantly. This becomes a more significant issue with databases created on UFS files. In this situation, a large number of SGA data buffers may also have analogous UFS buffers containing the most frequently referenced data. The behavior of the daemon that flushes filesystem buffer caches to disk can have a significant impact on performance due to the frequency with which it is woken and the amount of work that it has to accomplish. This could pose to be an I/O bottleneck resulting in lower overall system throughput.
In Solaris 2, the kernel parameters TUNE_T_FSFLUSHR and AUTOUP control the behavior of this daemon, called fsflushr. At regular intervals, fsflushr will go through a fraction of the page structures every TUNE_T_FSFLUSHR and cycle through all the page structures in autoup seconds. For example, if TUNE_T_FSFLUSHR is set to 60 and autoup to 180, then the fsflushr will look at 1/3 of the buffer cache every time it wakes up, every 60 seconds. The whole buffer cache will be flushed in 180 seconds. The values you pick will depend on how much you wish to alleviate the paging activity on your system.
Depending on the nature of the workload this could yield a performance improvement of 0 to 15%.
Tip No. 13: Configuring Swap Space
Inadequate swap space usually results in the system hanging or having abnormally slow response times. On Solaris 2, swap space can be dynamically configured on raw partitions and/or filesystem files. The amount of swap space to configure depends on the amount of physical memory present and the swap space requirements of your applications.
The general rule of thumb is to configure this to be 2 to 3 times the amount of physical memory for almost all workloads.
Tip No. 14: Setting the Database Blocksize
The Oracle database blocksize can be configured for better I/O throughput. In the Solaris 2, the logical blocksizes of the filesystems depend on the way the filesystem has been generated. This is usually 2K, 4K or 8K. If the Oracle database is UFS based then the block size should be some multiple of this. On Solaris, the DB_BLOCK_SIZE ranges from 2K (default) to 8K. (current maximum). Similarly for databases on raw partitions the Oracle database block size should be some multiple of the operating system physical block size (512 bytes on Solaris).
We recommend a smaller Oracle database blocksizes (2K or 4K) for OLTP or mixed workload environments, and larger blocksizes (8K) for DSS type workloads.
You may experience overall performance improvements ranging from 0 to 5%.
Tip No. 15: Reduce Redo Log Buffer Latch Contention
In order to reduce contention for redo log buffer latches, one can reduce the time the latch is held and in a multiprocessor environment, simply add more latches. The former can be achieved by reducing the value of the init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE and the latter by changing the value of the init.ora parameter LOG_SIMULTANEOUS_COPIES. By default LOG_SIMULTANEOUS_COPIES is set to the number of CPUs a machine has available. We recommend setting this to anywhere between its default value to two times of the default value i.e. twice as the number of CPUs available and reducing the LOG_SMALL_ENTRY_MAX_SIZE to zero.
The performance gains depend on amount of redo information generated by transactions and your system utilization.
Tip No. 16: Tuning the Archiver Buffers
Dedicating more buffers and larger buffers for archiving changes to the database. Overconfiguring these values could result in a faster archiver but at the expense of degraded overall system performance
The init.ora parameters LOG_ARCHIVE_BUFFER_SIZE (default 4) can be bumped up to a maximum of 128. The default for LOG_ARCHIVE_BUFFERS is 4.
Tune this carefully, so that overall performance of normal database activity is not degraded drastically. This may result in performance improvements in the archiving process from 0 to 20%. Some users have reported larger improvements.
Tip No. 17: Using Intimate Shared Memory (ISM)
Sun's Intimate Shared Memory feature allows different processes attaching to the same shared memory address to share the same page table. This avoids page table stealing and thrashing and thus enhances database performance. ISM is supported for Sun-4m and Sun-4d systems only. Use the Solaris command arch -k to determine the architecture of the machine you are running on.
Currently on Solaris 2, the ISM feature is incorporated in the operating system kernel and is enabled by default on Solaris 2.3 onwards. Applications still need to explicitly use this feature to take advantage of it. Oracle7 on Solaris takes full advantage of this feature by default (i.e. the init.ora parameter USE_ISM=TRUE by default). Setting USE_ISM=FALSE turns off this facility but may degrade performance. Turning on this feature on architectures that do not support this facility does not have any effect.
Depending on your initial configuration, your system resource limitations, and your overall system load, the performance benefit that you might see is roughly 0 - 200%
Tip No. 18: Tuning the SGA Size
The maximum size of a shared memory segment SHMMAX is constrained by the datatype that is used to represent this parameter, which an integer in our case resulting in a maximum size of 2 Gigabytes for a single shared memory segment. The other constraint is the amount of virtual memory addressable by the architecture.
Therefore the constraining factor in using multiple shared memory segments for the SGA becomes amount of physical memory that current hardware architectures can support. Obviously, we would not like to see the Oracle SGA paged or swapped out because of inadequate main memory. For a large number of simultaneous database users, larger SGAs are recommended. Very large SGAs (in the order of gigabytes) may require relocation of the SGA base address. Please refer to the Oracle7 for Sun SPARC Solaris 2.x Installation & Configuration Guide for more information on configuring the SGA.
The init.ora parameters that have the greatest effect on the SGA size are DB_BLOCK_BUFFERS and SHARED_POOL_SIZE. Bumping up these parameters depends on the amount of memory you can sacrifice to the SGA.
Tip No. 19: Tuning the UNIX Buffer Cache
The Solaris filesystem buffer cache plays an important part in performance when UFS based datafiles are used. Since the purpose of the UNIX buffer cache is to reduce disk access frequency, if this cache is too small, then disk utilization will increase and potentially saturate one or more disks. (See also the tip for Controlling Buffer-Cache Paging Activity above, for the implications of increasing this parameter.) On the other hand, if this is too large then precious memory is being wasted.
The UNIX buffer cache on Solaris 2 can be configured via the kernel parameter bufhwm. Bufhwm is the maximum amout of physical memory, in kilobytes, that can be used by the I/O buffers; it limits the number of buffers that can exist at any time. By default, up to 2% for the system memory is used, this can be increased to 20%. In general, if the buffer hit ratio is low (less than 90%, see sar -b), increasing bufhwm could help. If maintaining a high buffer hit ratio is not critical, decreasing bufhwm will make more physical memory available. Refer to your Solaris documentation for more information on this topic.
The performance gain cannot be quantified easily since it depends on the degree of multiprogramming and I/O characteristics of the workload.
Tip No. 20: I/O Buffers and SQL*Loader
For high speed data loading (e.g. using SQL*Loader direct path option in addition to loading data in parallel), the CPU spends most of its time waiting for I/O operations to complete. By increasing the number of buffers one can usually push the CPU utilization harder thereby increasing overall throughput.
The number of buffers (SQL*Loader parameter BUFFERS) will depend on the amount of memory you have available and how hard you wish to push CPU utilization. Please refer to Oracle7 Server Utilities User Guide V7.1 for information on adjusting the file processing options string for the BUFFERS parameter.
Your performance gains will depend on your CPU utilization and the degree of parallelism that you use when loading. Please refer to the appropriate chapters in the Oracle7 Server Documentation Addendum REL 7.1 for more details on parallelising the load operation and the Oracle7 Server Utilities User Guide V7.1 for more generic information on the SQL*Loader utility.
Network/IPC Tuning
Tip No. 21: Use out-of-band breaks with SQL*Net TCP/IPOn Solaris 2, the TCP/IP networking software supports the mechanism utilized by SQL*Net TCP/IP for out-band breaks. With SQL*Net V1 the orasrv program is set up to negotiate out-band breaks by default. This is also true for the SQL*Net V2 listener process.
Depending on how often SQL*Net communication is taking place between a client and server, the improvement in performance with using out-band breaks is 0 - 20% over using in-band breaks. Some users have seen even bigger improvements.
Tip No. 22: Use the Post-Wait Driver
The post-wait driver reduces the overhead incurred by the more expensive use of semaphore operation for interprocess communication. The post-wait driver is not yet implemented for ORACLE on Solaris although efforts are currently underway towards implementing this feature. At this time we cannot fully quantify the performance improvement, other than to say that it will reduce the overhead incurred by high interprocess communication activity common on OLTP databases. More extensive experiments need to be completed to determine the performance gains more precisely under different workloads.
Tip No. 23: TCP/IP Performance Issues
Solaris 2.4 has sped up network calls considerably, compared to Solaris 2.3. These are ultimately realized as performance gains and improved scalability of workloads that incur high network traffic.
The packet size utilized internally by SQL*Net V2 on Solaris 2 is 2K by default. The packetsize utilized by the underlying network layer (TCP/IP) is 1K for most installations. This packet size is usually equal to or is some multiple of the underlying TCP/IP packet size/s, otherwise this may generate unnecessary network overhead. This SQL*Net packet size or buffer size can be manipulated by specifying a size in bytes in the SQL*Net connect string. However, we do not recommend that users override this SQL*Net buffer size explicitly because:
1. There have been no hard cases where this clearly provides a significant boost in network performance.
2. Sometimes, this may even result in significant performance degradation.
Additional Tuning Tips
This section provides additional performance tuning tips that may not be covered in the Oracle for UNIX Performance Tuning Tips. The tips are mostly Sun specific.Tip No. 24: Compiling Pro*C Programs
Using SC3.0.1 C compiler from Sunsoft, some compiler flags may improve the performance of Oracle applications written using Pro*C or Oracle OCI interface e.g.
Tip No. 25: Some Useful Performance Monitoring and Tuning Tool/Utilities
There is a plethora of monitoring tools available on Solaris:
Solaris 2 kernel: /etc/system
LVM: Online Disksuite 2/3/4.x: md.tab
LVM: Veritas Volume Manager: use the vxprint utility
Oracle7 for Sun SPARC Solaris 2 Installation & Configuration Guide (Part# A14783)
Oracle7 Server Administrators Guide (Part# 6694-70-1292)
Oracle7 Server Concepts Manual (Part# 6693-70-1292)
Oracle7 Server SQL Language Reference Manual (Part# 778-70-1292)
Oracle7 Server Documentation Addendum Rel 7.1 (Part# A12042)
Oracle7 Server Utilities User Guide Rel 7.1 (Part# A12389)
Oracle7 Server Application Developer's Guide (Part# 6695-70-1292)
Solaris 2.3 Answerbook
SPARCompiler C 3.0 Answerbook
SPARCworks/SPARCcompiler 3.0 Answerbook
Sun Performance Tuning Overview (Part# 801-4872-07) October 1993
Sun Performance and Tuning: SPARC and Solaris (ISBN 0-13-149642-3)
EMAIL: suninfo@us.oracle.com FAX: (415) 506-7223 (Attention: SUNINFO) MAIL: Suninfo Sun Products Division (Performance Group) Oracle Corporation 500 Oracle Parkway Box 659407 Redwood Shores CA 94065, USA