Performance Improvement Tips for Oracle on UNIX


  1. Restart your database. 
  2. Run your application.
  3. Run tkprof against the tracefile created by your application:

tkprof <tracefile> <outputfile> EXPLAIN=username/passwd

  1. Look at formatted output of trace command and make sure that your

      SQL statement is using indexes correctly.  Refer to DBA guide for a list

      of rules that the oracle optimizer uses when choosing a path for a SQL statement. 

                 <<<<<< OUTPUT OF TKPROF FILE  >>>>>>> 

count   = number of times OPI procedure was executed 

cpu     = cpu time executing in hundredths of seconds 

elap    = elapsed time executing in hundredths of secs 

phys    = number of physical reads of buffers (from disk) 

cr      = number of buffers gotten for consistent read 

cur     = number of buffers gotten in current mode (usually for update) 

rows    = number of rows processed by the OPI call 


select * from emp where empno=7369  

            count     cpu    elap    phys      cr     cur    rows 

Parse:          1       0       0       0       0       0 

Execute:        1       0       0       0       0       2       0 

Fetch:          1       0       0     219     227       0       2 

Execution plan: 



select empno from emp where empno=7934  

            count     cpu    elap    phys      cr     cur    rows 

Parse:          2       0       0       0       0       0 

Execute:        2       0       0       0       0       2       0 

Fetch:          2       0       0       0       2       0       2 

Execution plan: 



When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query.  Above is an example of the same query run twice.  The first time the optimizer chose to do a full table scan on the table EMP.  The second time an index was created called ALEX_INDX.  The optimizer chose to use the index.  On a table 

with 1000 rows this should have resulted in a faster query.  

Oracle uses a rule-based optimizer for its SQL statements.  When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly.  You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning.  Page 19-17 of the V6 DBA guide has a listing of the query paths that the optimizer will use ranked by speed. 

Other considerations for application tuning may include the actual design of your application.  A common problem found in menu5/forms30 applications is the way in which one calls the other.  Make sure that menu5 is calling forms30 directly and not through a UNIX system call.  Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine. 

 2. RDBMS Tuning 

The default init.ora database configuration file is inadequate for large 

RDBMS's.  Tools for tuning the database are: 

1. SQLDBA * Monitor 

2. Bstat/Estat  

3. V$ database tables.  

By looking at SQLDBA * Monitor one can get a good idea of what is happening to the database at that moment.  The first place to look is the IO display. This will display the CACHE HIT RATIO.  The cache-hit ratio is the ratio of hits to misses in the SGA for data.  The higher the ratio, the more data is being found in the SGA.  This means that oracle will not have to do as many disk reads to retrieve information, thus saving time and CPU processing. 

While a hit ratio of 1.00 would be ideal, it is more realistic to aim for achieving a hit ratio of about .80.  To raise the ratio, one can adjust the init.ora parameter DB_BUFFERS.  Raising the number of database buffers in memory will increase the SGA and also increase the cache-hit ratio.  You can also extrapolate DB_BUFFER values by setting the init.ora parameter 

DB_BLOCK_LRU_EXTENDED_STATISTICS equal to the additional number of DB_BUFFERS you wish to add.  You will then be able to see the additional number of cache hits that would occur if you add X number of database buffers.  For more information on this you can refer to pg 3-20 of the database performance tuning guide.   

After looking at the cache-hit ratio and adjusting the number of database buffers accordingly you can analyze FILE IO.  This screen 

in SQLDBA * Monitor will show IO to each oracle datafile.  It allows you 

to identify two problem areas: 

1. Table scans (indicated by a low number of reads/s & high number of blks/R)

2. Heavily used tablespaces  

Table scans indicate that your SQL statements might not be tuned correctly.  If your query is returning less than 15% of the rows of a table you should be using indexes to query the tables.  Secondly you will also be aware of which tablespaces are used heavily and perhaps consider moving them to a separate disk to balance the IO.

The Rollback screen can be very useful on an update or insert-heavy system.  Whenever you are changing or inserting new data, you write the changes to a rollback segment until they are committed. If you have 30 transactions and 1 rollback segment, you will have contention for that rollback segment.  This will incur a performance penalty.  To make sure that this is not happening, verify that every rollback segment has a maximum of 4 active transactions using it during heavy updating of the database.  For more sizing information on rollback segments refer to Metalink note 102424.36. 

The table V$ROWCACHE also contains useful information for tuning.  The following select statement can determine whether or not any of the init.ora parameters beginning with DC_XXXX should be raised.  These parameters control the size of various data dictionary caches. 

SELECT parameter,gets,getmisses,count,usage FROM sys.v$rowcache;


------------- ------- ----------  ---------- -----

dc_usernames  134     5           50         5 

dc_columns    11772   288         300        300 

The parameter dc_usernames has a very low number of cache misses (GETMISSES).  We have allocated 50 username entries (COUNT) and are only using 5 (USAGE).  There is no need to raise the value of dc_usernames.  

We have allocated 300 DC_COLUMNS and we are using all 300.  In addition, the number of GETMISSES or cache misses are high for the parameter DC_COLUMNS.  In this case, it is recommended to increase the value of DC_COLUMNS to reduce the number of GETMISSES or cache misses.  A cache miss can be a very expensive operation for oracle since it means that the data requested was not in cache and thus a recursive call had to be made. For more information on this you can refer to pg 3-13 of the v6 performance tuning guide. 

3. UNIX System Analysis 

It is useful to subdivide UNIX system analysis into three subcategories: 

  1. Memory 
  2. CPU 
  3. IO 


One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory.  In this case, a quick review of UNIX memory management is useful to see what effect lack of RAM can have on performance.  A UNIX machine has virtual memory: the total addressable memory range.  Virtual memory is composed of RAM, DISK and SWAP space.

Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM.  

How does UNIX use SWAP space? It uses two memory management policies: swapping and paging.  Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device.  This frees up a large amount of RAM.  Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device.  Only a tiny portion of a process might actually be "paged out" to a SWAP device.  While swapping frees up memory - it is slower than paging.  Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously.  Most UNIX systems today use a combination of paging and swapping to manage memory.  Generally, you will see the following behavior: 

  • System lightly used: no paging or swapping occurs. 
  • System under a medium load: paging occurs as RAM memory runs low 
  • System under a very heavy load: paging stops and swapping begins.

When analyzing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly.  This indicates a system with a healthy amount of memory available.  To analyze paging and swapping, use the following commands. Commands used in Berkeley UNIX based systems will be marked as BSD.  Commands used in ATT system V will be marked as ATT. 

1. vmstat 5 5 (BSD) 

procs    memory      page      disk       faults      cpu 

r b w  avm  fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id 

0 0 0   0  1088 0  2  2  0  1  0  0  0  0  0  0  26 72 24 0  1  98  

Note: There are NO pageouts (po) occurring on this system.  There are also 1088 * 4k pages of free RAM available (4 Meg).  It is OK and normal to have page out (po) activity.  You should get worried when the number of page  ins (pi) starts rising.  This indicates that you system is starting to page. 

2. pstat -s (BSD) 

12112k allocated + 3252k reserved = 15364k used, 37280k available 

Note: pstat will also give you the amount of RAM and SWAP currently available on your machine. 

3. sar -wpg 5 5 (ATT) 

09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s 

         atch/s  pgin/s ppgin/s  pflt/s  vflt/s slock/s 

         pgout/s ppgout/s pgfree/s pgscan/s %s5ipf 

09:54:34    0.00     0.0     0.00     0.0     12 

            0.00     0.22    0.22     0.65    3.90    0.87 

            0.00     0.00    0.00     0.00    0.00 

Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout). 

4. sar -r 5 5 (ATT) 

  10:10:22 freemem freeswp 

  10:10:27     790    5862 

This will give you a good indication of how much free swap and RAM you have on your machine.  There are 790 pages of memory available and 5862 disk blocks of SWAP available. 

2. CPU 

Once you have monitored your systems available memory you will want to make sure the the CPU(s) are not being overloaded.  Here is some general information about how processes get allocated CPU time.  UNIX is a multi-processing operating system.  That means that a UNIX machine has to manage and process multiple user processes simultaneously. UNIX does this in the same way that people wait in line to buy groceries. 

When a process is ready to be processed by a CPU it will be placed on the waiting line or RUN-QUEUE.  This is a queue of processes waiting to be run.  Obviously there are limits within which one wants to keep the RUN-QUEUE size.  Another factor of interest is the percentage of time the the CPU spends in user mode, system mode, or idle mode.  Some commands that determine whether or not there is a CPU resource problem occurring: 

1. vmstat 5 5 (BSD) 

procs  memory             page             disk       faults     cpu 

r b w  avm fre  re at  pi  po  fr  de  sr d0 s1 d2 s3 in  sy  cs us sy id 

0 0 0   0  1088 0  2   2   0   1   0   0  0  0  0  0  26  72  24  0  1 98 

Note: The CPU is spending most of its time in IDLE mode (id).  That means that the CPU is not being heavily used at all! There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE. 

2. sar -qu 5 5 (ATT) 

  10:58:02 runq-sz %runocc swpq-sz %swpocc 

              %usr    %sys    %wio   %idle 

  10:58:07     2.8     100                 

                 0       2       4      94 

Note: The CPU is spending most (94%) of its time in idle mode.  This CPU is not being heavily used at all.  Generally it is alright to have a CPU that is 0% idle as long as the RUN-QUEUE is not too large. In this case the run queue is 100% occupied (there is always a process waiting to be run) and there are an average of 2.8 processes waiting to be run in the RUN-QUEUE.  You

want to keep the RUN-QUEUE under 5-6 for a single CPU machine.  If the run queue gets any larger that those values you will see some performance degradation.  Two solutions to this are: 

  1. Obtain a faster processor 
  2. Use more CPU's. 

Avoid overloading your CPU.  Response time on your machine will suffer if it is overloaded.  Try to keep the run queue 100% occupied and have less that 6 processes waiting to be run for one CPU.  This changes as you add more CPU's or a faster CPU.  You may also want to avoid the CPU spending most of its time (more than 50%) in system mode. This may indicate that you are spending too much time in kernel mode servicing interrupts, swapping processes etc. 

3. I/O 

The last step in analyzing your UNIX machine is taking a look at IO.  After  having looked at SQLDBA monitor to see which datafiles are being used heavily you may also want to take a look at what UNIX says about file IO.  These commands are for analyzing file IO on file systems.  

1. iostat -d 5 5(BSD) 

           sd1           sd3  

    bps tps msps  bps tps msps  

     1   0  0.0    4   0  0.0  

iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek. In the example above, both SD1 and SD3 are not used heavily at all.  BPS rates over 30 indicate heavy usage of a particular disk.  If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks. 

2. sar -d 5 5 (ATT) 

09:17:20  device         %busy     avque   r+w/s  blks/s avwait  avserv 

09:17:26  iop0/pdisk010  472.45    1.18       9     107   39.26  512.66 

          iop0/pdisk000   18.43    2.66       8     132   31.36   24.10 

          iop0/pdisk020  317.08    1.11      11     165   31.95  294.02 

          iop0/pdisk021  590.88    1.34      27     518   96.26  219.96 

          iop0/pdisk040   34.94    1.64      18     113   43.70   19.58 

          iop0/pdisk041   45.33    1.17      20      79    3.73   22.89 

Note: The "-d" option reports activity for each block device, The following is an explanation on the output.  %busy, avque - portion of time device was busy servicing a transfer request, average number of requests outstanding during that time; r+w/s, blks/s - number of data transfers from or to device, number of bytes transferred in 512-byte units; avwait, avserv - average time (in ms) that transfer requests wait idly on queue, and average time to be serviced.  There is a relationship between the number of blocks transferred per second and the average wait time. You can use this to identify which disks are heavily used and which are


3. sar -b 5 5 (ATT) 

15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s 

15:53:12       0       2      90       1       2      38       0       0 

Note: The "-b" option indicates the overall health of the IO subsystem.  The %rcache should be greater than 90% and %wcache should be greater than 60%.  If this is not the case, your system may be bound by disk IO.  The sum of  bread, bwrit, pread, and pwrit gives a good indicator of how well your file  subsystem is doing.  The sum should not be greater than 40 for 2 drives and 

60 for 4-8 drives.  If you exceed these values, your system may be IO bound.  For more information on this refer to pg 2-22 of the Oracle V7 technical  reference guide. 

When analyzing disk IO, make sure that you have balanced the load on your system.  Here is a "wish" list of steps for designing a disk layout for Oracle:

  1. Make sure that your logfiles and archived logfiles are NOT on the  same disk as your datafiles.  This is a basic safety precaution  against disk failure.
  2. Put your files on raw devices.
  3. Allocate one disk for the User Data Tablespace.
  4. Place Rollback, Index, and System  Tablespaces on separate disks.
  5. Consider using Raid level 5 disk striping to stripe your datafiles  across separate disks.  You can also use the above-mentioned UNIX  commands to monitor the IO on your system and identify problem areas. 

Other tools for system administratation are platform-dependent.  For example, HP and SEQUENT provide a facility called MONITOR that gives much of the above information in a graphical format.  ATT SVR 4 also provides a product called GSAR (graphical sar).  Look in your operating system (OS) documentation for additional monitoring commands.  As a further 

reference, consider "SYSTEM PERFORMANCE TUNING" published by O'Reilly and  Associates.  

Conclusion: There are many ways to approach Oracle performance issues.  A structured approach like the one discussed above will allow the system administrator to systematically analyze her system and identify any problem areas.  Once this has been accomplished, measures can be taken to correct problems.  Performance is subjective, so find out what is expected.