Home : Oracle Certified Professional (OCP): Performance Tuning Exam Guide

Revision Notes

July 2001 - Work in progress.

Tuning Overview

Who tunes, why tune and how much?  Everybody tunes!  Tune to keep your system running smoothly and avoid problems.  Tune with specific goals in mind.  Things you might want to tune are response time, database availability, hit percentages (disclaimer: this is not the best way to tune your database), memory utilization.  Your goals should be for SQL to access the least number of blocks to get to data.  Cache frequently used blocks in memory.  Share SQL and code among users.  Perform reads and writes quickly.  Ensure backups to not interfere with operations.

Tuning Steps

1. Design

2. Application

3. Memory

4. I/O

5. Contention

6. Operating System

Brain Dump

I only had one question on the exam from this section.  It had something to do with the tuning steps.  If you remember the acronym you will have no trouble.

Oracle Alert and Trace Files

Information can be culled from a couple different types of files; alert files, background process trace files and user trace files.  

The alert log is created when the instance is started.  If you delete it, no trouble, the next time Oracle needs to write to it is will create one (this may not be true in all versions of 8i and above).  You should check the file frequently (actually you should automate this with a script) and check for ORA-600 errors (internal Oracle errors, require a call to Oracle support in many cases).  You can also see non-default parameters, startup and shutdown, log switches and more.  The file is placed in the directory specified by BACKGROUND_DUMP_DEST.

If an background process has a problem it will dump a trace file to the BACKGROUND_DUMP_DEST also.  The name of the file will be in the format <SID>_<process name>_<PID>.

User trace files containing the SQL executed by the session can be captured using SQL Trace.  Three ways to turn SQL Trace are:

1. Set the SQL_TRACE = TRUE in the parameter file (your system will not be a happy camper, turn it off as soon as you don't need it).

2. Use the ALTER SESSION SET SQL_TRACE = TRUE command.

3. Use the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure. It accepts three parameters SID, SERIAL# and TRUE or FALSE.  The third parameter tells Oracle so turn on or off SQL tracing for the specified session.

Trace files could grow out of control and eat up all your disk space so you need to know how to control them.

USER_DUMP_DEST - That's where they will go.

MAX_DUMP_FILE_SIZE - This is how big trace files will get..  Specified as number of OS blocks.

Brain Dump

I had two questions from this section.  One was knowing where the alert log was (BACKGROUND_DUMP_DEST). I can not recall what the other was.

Utilities and Dynamic Performance Views (Incomplete)

There are a whole bunch of views called the DBA views (you should already know that!) that you can get information from.  When you analyze tables and indexes the information in these views is updated.  You can look at DBA_TABLES and DBA_TAB_COLUMNS for table information.  You can look in DBA_CLUSTERS for..you guessed it...cluster information.  Other pertinent views are DBA_INDEXES, DBA_IND_COLUMNS, DBA_HISTOGRAMS, INDEX_HISTOGRAMS.

Next you need to know about the V$ views.  There are a whole bunch of these and you can even look them all up in the Oracle Reference Manual. You can also list all of the tables using the V$FIXED_TABLE view.  These views are actually not views at all, they are synonyms for V_$ views!  These views are actually based on the X$ tables.  These tables are not usually queried directly unless you are really smart like Steve Adams!  These views and tables are populated with data when the instance starts and cleared when it is shutdown.  Much of the information in these objects will be useless unless you turn on timed statistics by setting the TIMED_STATISTICS parameter to TRUE in the parameter file.  There is a little added overhead for the database when this is true but not enough to worry about so set it to true!

Really important views!

• V$EVENT_NAME

• V$SYSSTAT

• V$SYSTEM_EVENT

• V$SESSION_WAIT

• V$WAIT

• V$LOCK

• V$SESSION

• V$SESSTAT

• V$LATCH

• V$ROWCACHE

• V$LIBRARYCACHE

Brain Dump

Three questions on this part of the exam.  One of the questions was very particular about which sections are in a report.txt file.  The question asked to select 3 of 6 choices.  A few were obviously on the report like rollback segments and buffer busy waits.  The trouble is if you miss just one you get the whole question wrong.  I suggest you study the report.txt and memorize all of the major sections.  There was a question in which the answer was V$SYSTEM_EVENT or V$SESSION_WAIT so be very familiar with these and the other major views I listed.

Tuning the Shared Pool

The shared pool can be broken into a few major components.

• library cache - Stores shared SQL and PL/SQL.
• data dictionary cache - Stores information about the database dictionary objects.
• User Global Area (UGA) - Stores information about multi-threaded objects.

You should focus on tuning the library cache hit ratio.  This is the percentage of time that SQL or PL/SQL is found in the cache when requested.  A miss on this type of object is more expensive than a typical miss in the buffer cache.  The size of the shared pool is set using the SHARED_POOL_SIZE parameter in the parameter file.  The library cache uses an LRU algorithm to manage the SQL and PL/SQL statements in the cache.  The goal is to store them as long as possible so you avoid re-parsing the statements.  Oracle converts the SQL to the numeric value of it's ASCII text then performs a hash function on it to determine if a current SQL statement matched one in the cache.

Tuning Goals for the Library Cache:

1. Reduce misses and keep parse to a minimum.
   
   When a statement does not exist in the library cache it must be parsed.  This can be avoided by use of very generic code and bind variables.  You can also enlarge the shared pool witch will allow more SQL to be stored there.  Finally you can reduce the changes to schema objects referenced by SQL so that the SQL in the pool does not become invalid.
   
2. Avoid fragmentation.
   
   You must always have enough contiguous free space in the library cache to store statements being parsed.  You can ensure this by allocating reserved space for large memory requirements.  Pin frequently used large objects in memory.  Use small PL/SQL packaged functions instead of anonymous PL/SQL blocks.  Reduce UGA consumption of MTS based connections.

You should know what GETS, PINS and RELOADS are.  You can look at these values in the V$LIBRARYCACHE table which stores this information about various types of objects in the cache.  GETS are the total number of lookups for an object.  PINS are total number of reads or executions of the type of object.  RELOADS are the number of times the object had to be re-parsed because it was not found in the cache.  The types of objects are determined by the NAMESPACES column of the view.  NAMESPACE values that deal with SQL and PL/SQL; SQL AREA, TABLE/PROCEDURE. BODY, TRIGGER.  NAMESPACE values that deal with Oracle dependency maintenance; INDEX, CLUSTER, OBJECT, PIPE.

There are a number of views and tools you can use to evaluate library cache performance.

• V$SGASTAT - You can look at total memory used by all SGA structures.  Pay special attention to "free memory".  You should not see a lot of free memory in the SGA if your library cache is sized correctly.  Free memory is a sign of fragmentation. Bigger is not always better!
• V$LIBRARYCACHE - GETS, PINS and RELOADS for each of the different NAMESPACES in the library cache.
• V$SQLAREA - Statistics for all shared cursors and the first 1000 characters of each statement.
• V$SQLTEXT - Full SQL of each statement broken into multiple lines.
• V$DB_OBJECT_CACHE - Database objects cached.
• UTLBSTAT/UTLESTAT (Statspack) - Both contain information about library cache performance.
• There are a number of parameter settings that can affect cache performance; SHARED_POOL_SIZE, OPEN_CURSORS, SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME, CURSOR_SHARING.

You can also use the V$LIBRARYCACHE view to get the GETHITRATIO of each namespace.  Oracle recommends that the percentage be in the high 90's (not everyone agrees!).  Your first solution should be to try to tune the application code.  You can often find the offending SQL by examining the V$SQLAREA and V$SQLTEXT views.  You can calculate the overall reloads to pin hits ratio by dividing the total number of reloads in V$LIBRARYCACHE by the total number of pins.  Total reloads should not be more than 1% of total pins.

select sum(reloads)/sum(pins) from v$librarycache;

If this value is greater than 1% there could be two reasons:

1. Frequently used shared parsed areas are being aged out too quickly due to lack of space.
2. Shared parsed areas are becoming invalidated too frequently.  You can check to see how often an object is becoming invalidated by looking at the INVALIDATIONS column of the V$LIBRARYCACHE view.

To size the library cache you need to define the total space required for stored objects like packages and views.  Define the total memory used by typical SQL statements.  Reserve space for large memory requirements.  Pin frequently used objects in memory.  Convert large anonymous objects to stored packaged functions. 

To determine the sizes of various objects you can use the SHAREABLE_MEM column of either the V$DB_OBJECT_CACHE view or V$SQLAREA view depending on the type of object.  Use the former stored objects like packages and views and the latter for SQL.

select sum(shareable_mem) from v$db_object_cache;

select sum(shareable_mem) from v$sqlarea;

Prior to attempting to determine this information you should set you SHARED_POOL_SIZE very large and run the application for a while.  This will give you a good idea of your size requirements.  You should also include about 250 bytes per user per cursor open.  This can be determined by running the following SQL statement.

select sum(250 * users_opening) from v$sqlarea;

To configure an area of the shared pool for large objects you need to configure the SHARED_POOL_RESERVERD_SIZE. Oracle recommends you set this 10% of the SHARED_POOL_SIZE.  The default is 5%.  It can not be larger than 50%.  You can tune the reserved pool by using the V$SHARED_POOL_RESERVED view.  Some columns are valid only if the parameter is set correctly and others are always correct.

FREESPACE - Total amount of free space on the reserved list.

AVG_FREE_SPACE - Average size of free memory on the reserved list.

MAX_FREE_SIZE - Largest amount of free space on the reserved list.

REQUEST_MISSES - Total number of times a request could not be satisfied because there was not enough room on the reserved list.

The above columns are only accurate when the parameter has been set correctly.  The others are always correct.

REQUEST_FAILURES - Total number of failures.

LAST_FAILURE_SIZE - Size of last failure.

ABORTED_REQUEST_THRESHOLD - Minimum size of a request which signals an ORA-4031 error without flushing objects.

ABORTED_REQUESTS - Number of requests that signaled an ORA-4031 error without flushing objects.

LAST_ABORTED_SIZE -  Last size of the request that returned an ORA-4031 error without flushing objects from the LRU list.

You can use the ABORTED_REQUEST_THRESHOLD procedure of the DBMS_SHARED_POOL package to set the ABORTED_REQUEST_THRESHOLD.  This limits the amount of the shared pool to flush before issuing an ORA-4031 error.  The reserved size is too small when the REQUEST_FAILURES value is greater than zero and growing.  If this occurs you should increase the size of the SHARED_POOL_SIZE and SHARED_POOL_RESERVED_SIZE accordingly.  You should decrease the SHARED_POOL_RESERVED_SIZE if REQUEST_MISS is zero and not increasing or FREE_MEMORY is more than 50% of the SHARED_POOL_RESERVED_SIZE minimum.

You should try to locate large objects and pin them in the shared pool at database startup or right after you issue the ALTER DATABASE FLUSH SHARED_POOL command.  (Flushing the shared pool does not remove pinned objects!) You can locate the large objects by looking in the V$DB_OBJECT_CACHE view.  You can pin the objects using the DBMS_SHARED_POOL.KEEP procedure.

select * 
  from 
       v$db_object_cache 
 where sharable_mem > 10000 
   and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
   and KEPT=‘NO’;

In the "real world" you will want to automate this so it occurs whenever the database is restarted.  Some of the common things to pin are;

• Frequently used large procedure objects like DBMS_UTILITY and DBMS_STANDARD.

• Sequences

• Triggers

You can use the UNKEEP procedure to un-pin objects.  You need to run the dbmspool.sql script as user SYS if the package is not installed.

If you decide you need to clean up some anonymous PL/SQL blocks you will need to know how to identify them.  This can be done by using the following SQL.

select sql_text from v$sqlarea where command_type = 47 and length(sql_text) > 500;

If you have trouble identifying an anonymous block you can put a comment in it and query the V$SQLAREA to locate it after executing it.  Get the address and hash value and use the DBMS_SHARED_POOL.KEEP procedure to pin it in memory.

Let's take a look at some of the parameter associated with the shared pool.

OPEN_CURSORS - Per session limit as to the maximum number of cursors a user can have open at one time.

CURSOR_SPACE_FOR_TIME - Set to either true of false.  Determines if shared sql areas are aged out if cursors are still referencing them.  Defaults to false.  Only use this if you have an excellent hit ratio on your library cache and reloads is consistently zero.

SESSION_CACHED_CURSORS - Helps in cases where session repeatedly parse the same statements.  This is common in Forms applications.  Setting this parameter causes closed cursors to be cached within the session.  Check the "session cursor cache hits" to "parse count" in the V$SESSTAT view for a session to determine if your setting is efficient.  If the number of parses is high in comparison to total hits then consider setting this parameter.  It will require additional memory resources.

CURSOR_SHARING - Can be set to "EXACT" or "FORCE".  This parameter when set to FORCE will force statements that differ in some literals, but are otherwise identical, to share a cursor.  This is beneficial in applications where you can not modify the code.  In some DSS applications this setting could have unexpected results so in that case make sure you use stored outlines.

To monitor the data dictionary you will use the V$ROWCACHE view.  Three columns of importance are:

• PARAMETER - Category of dictionary item.

• GETS - Total requests for information on this category.

• GETMISSES - Requests resulting in a cache miss.

There is no parameter to directly size the dictionary cache aside from the SHARED_POOL_SIZE parameter.  You can expect a lot of misses when the database is initially started since there is no information in memory yet.  However, as the database has been used you should expect very few misses on you data dictionary cache.  Oracle recommends that you do not experience misses more than 15% of the time.  Of course if the total number of gets is very low you should not be concerned with a miss rate higher than this.  This information is also reported when using UTLBSTAT/UTLESTAT and statspack.

The PGA is comprised of stack space, user session data and cursor states.  The latter two are called the UGA.  In dedicated server mode the UGA is inside the PGA.  In multi-threaded server mode it is inside the shared pool.  The total memory requirement for this will not really increase, it will just change the area it is allocated to.  You should consider this when sizing the shared pool.  You can determine how much memory is being used by totaling the "session uga memory max" amount in the V$SESSTAT view.

select 
      SUM(value) ||'bytes' "Total max memory"
from 
      V$SESSTAT, V$STATNAME
where 
      name = 'session uga memory max'
  and v$sesstat.statistic# = v$statname;

As of Oracle 8 there is another area of the SGA (outside the shared pool) that you can configure called the large pool.  It is configured using the LARGE_POOL_SIZE parameter.  If configured it will be used with the following.

• I/O server processes.  DBWR_IO_SLAVES

• RMAN backup and restore operations.

• Session memory when in multi-threaded server mode.

• Parallel query messaging. Oracle will allocate parallel execution buffers from the large pool instead of the shared pool.  This is only true then the PARALLEL_AUTOMATIC_TUNING parameter is set to true.

The minimum size of the large pool is 600K.  The max is at least 2GB but is operating system specific.  If Oracle fails to get enough space in the large pool when requested (assuming it has been configured) then log archiving will fail and return an error, RMAN will write a message to the alert file and not use I/O slaves for the operation and if running MTS a ORA-4031 error will be returned.  The large pool does not have an LRU list.  The size of the large pool can be determined using the V$SGASTAT view.

select * from v$sgastat where pool = 'large pool';

If PARALLEL_AUTOMATIC_TUNING is true the large pool size will be computed automatically.

Tuning the Buffer Cache

The buffer cache stores copies of database blocks from the data files.  They are read from the data files into the buffer cache by server processes and written back to the data files by database writer (DBWR).  To remain efficient this is frequently done using multiple blocks in a single write.  The size of the buffer cache is determined by the DB_BLOCK_BUFFERS setting.  You can multiply the number of these buffers by the block size to determine overall size.  You could also query V$SGASTAT to determine the size.  The buffer cache may contain multiple copies of a single block but only one will be current.  Read consistent views of a block are often obtained using the information stored on a rollback segment.  These blocks are managed using two lists.  The LRU (Least Recently Used) list and the dirty list.  The LRU helps to keep the most recently accessed blocks of data in memory.  The dirty list contains a pointer to blocks that have been modified but not written to disk.  Blocks can be in one of three states; pinned, dirty, or free.  Pinned blocks are currently being accessed.  Dirty blocks have been modified and need to be written to the data files.  Free blocks are available for use.

How the server process reads a block.

1. Uses a hash function to determine if the block is in the cache.

2. If not in the cache then it searches the LRU list for a free block.

3. While searching dirty blocks are put on the dirty list.

4. If after some threshold a free block can not be found then DBWR is signaled to write out the dirty blocks.

5. When a free block is found the block from disk is read into it.

6. If the block is not consistent then the server rebuilds an earlier version of it using the current block and rollback segments.

How does DBWR know to write the dirty blocks to the data files?

1. Server process finds that the dirty list has exceeded it's size threshold.

2. Server process can not find a free block on the LRU list within the search threshold.

3. 3 second timeout.

4. LGWR (Log Writer) signals DBWR during a checkpoint.

5. After a ALTER TABLESPACE OFFLINE or ALTER TABLESPACE BEGIN BACKUP command.

6. When an object is dropped all dirty buffers for that object are flushed.

7. In OPS (Oracle Parallel Server) the LCKn (Lock) process signals writes when a block is pinged for another instance.

8. After a clean shutdown (NORMAL, TRANSACTIONAL, and IMMEDIATE).

Oracle suggests that you attempt to achieve a 90% cache hit ratio in your buffer cache.  Gaja Vaidyanatha author of Oracle Performance Tuning 101 would disagree with this recommendation.  I would agree with Gaja, but for the sake of the test we need to know that in an OLTP environment Oracle says 90% is the way to go.  I will not go into Gaja's reasoning, you will need to buy the book.  There are a couple of ways you can improve the cache hit ratio in the buffer cache.  I also suggest reading Cary Millsap's paper "Why a 99% Buffer Cache Hit Rate is NOT Ok" (free registration is required).

• Increase memory by increasing DB_BLOCK_BUFFERS.

• Use multiple buffer pools (KEEP and RECYCLE).

• Cache tables in memory.

• Bypass the buffer pool for sorting and parallel reads when possible.

Only increase DB_BLOCK_BUFFERS when the previous increase actually improved performance.  Also only increase it when you have sufficient memory.  You should also know that just because Oracle say physical I/O took place doesn't really make it so.  In many cases the information is stored in the OS cache or disk cache.  However these additional caches can add overall overhead to operations.

The cache hit ratio is: 1-(physical reads/( db block gets + consistent gets))*100

This ratio can be determined using statistics in either V$SYSSTAT or V$SESSTAT.  You can also use the V$BUFFER_POOL view to get information about the different buffer pools.  The V$BH view will tell you what blocks are held in cache.  I prefer to use the V$BUFFER_POOL_STATISTICS view to calculate my hit rates for each buffer pool.  Prior to Oracle 8.1.6 you had to run catperf.sql to install it.

SELECT 
      1 - (phy.value / (cur.value + con.value)) "CACHE HIT RATIO"
FROM  
      v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE 
      cur.name = ’db block gets’
  AND con.name = ’consistent gets’
  AND phy.name = ’physical reads’;

Basically you should be careful when increasing or decreasing DB_BLOCK_BUFFERS to measure the change in you hit ratios.  Don't increase the size of any memory component if you paging and swapping on the OS.  Finally be aware that plenty of things can be wrong and cause your buffer cache hit ratio to be inflated like lots of full table scans on a cached table.

Oracle 8 gives us the ability to configure multiple buffer pools.  You can optionally configure a KEEP and a RECYCLE buffer pool.  The KEEP buffer pool is used to keep objects in memory that are likely to be used again.  This is good for small lookup tables.  The RECYCLE pool is used to cache blocks that are very unlikely to be used again.  The standard pool is the DEFAULT pool and it always exists.  The additional pools can be configured using the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters. Both parameters allow you to specify a specific number of buffers from DB_BLOCK_BUFFERS to allocate to each pool. You can also specify the number of LRU latches to use from the DB_BLOCK_LRU_LATCHES parameter.

BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:1)
BUFFER_POOL_RECYCLE=(BUFFERS:2000,LRU_LATCHES:3)

The minimum number of buffers that can be allocated to each pool is 50 times the number of LRU latches. Objects are assigned to a particular buffer pool using the buffer pool clause when the object is created or using an ALTER command.  If you use the ALTER command existing buffers in cache remain where they are.  Each buffer pool is assigned by segment so a partitioned object could have different segments in different pools.

CREATE TABLE blah (col1 NUMBER) STORAGE (BUFFER_POOL KEEP);

ALTER TABLE blah STORAGE (BUFFER_POOL RECYCLE);

You can determine how to size your KEEP buffer pool by analyzing statistics on the objects you intend to keep there.  The sum up the bytes column for the objects in DBA_TABLES, DBA_INDEXES or DBA_CLUSTERS.  You need to monitor the objects and the hit ratios over time to determine of the KEEP size should be increased or decreased.  Remember the goal of the KEEP buffer pool is to "keep" blocks available in memory.

The goal of the recycle buffer pool it to eliminate used blocks from memory as quickly as possible.  If the RECYCLE pool is too small required blocks may be aged out before they can be use so you want to make sure it is large enough.  You can size this pool using the physical reads statistics from a tracing tool or summing the total blocks used in cache by the object.  The V$CACHE view will help you determine how many blocks are being used by and object.  It is created by the catparr.sql script.  This view is actually intended to be used with OPS and it creates a number of other views that are useless unless you are running OPS.  To determine the number of blocks used by the objects you plan to put in the RECYCLE pool do the following.

1. Tune the buffer cache with the RECYCLE pool disabled.

2. Run catparr.sql script to populate the V$CACHE view.

3. Run the following query to determine how many blocks are used by each object at peak hours.
   
   SELECT 
   owner#, name, count(*) blocks
   FROM 
   v$cache
   GROUP BY 
   owner#, name;
   

4. Sum the blocks for all objects and divide by 4 because it is assumed only 1/4'th of the blocks will be active at any one time.

You can use the V$SESS_IO view to get I/O statistics by session.  It is expected that all blocks for an object in the RECYCLE pool will incur a physical read.  You will need to use the V$BUFFER_POOL_STATISTICS to monitor the hit ratios when using multiple buffer pools.  How do you know what objects to place in which buffer pool?  The guidelines are:

• KEEP pool if the object's blocks are repeatedly accessed and the segment size is less than 10% of the default buffer pool size.

• RECYCLE pool when blocks are not reused outside a transaction and the segment size if more than twice the default buffer pool size.

The V$BUFFER_POOL view returns a row for each buffer pool.  It also tells you the number of LRU latches and the number of blocks allocated to the buffer.  Many of the DBA_* views contain information about which pool the object is set to (DBA_TABLES, DBA_INDEXES...).

There are some other buffer pool performance indicators you should be aware of.  You should monitor the number of "free buffer inspected" in the V$SYSSTAT view.  This is the number of buffers that needed to be skipped when a server process was scanning the LRU for a free buffer.  A buffer is skipped when the block is either pinned or dirty.  However, this could also be an indication that DBWR can not keep up with the demands to write dirty blocks to disk.  V$SYSTEM_EVENT and V$SESSION_WAIT are also excellent views for diagnosing troubles.  One event to monitor is the number of "buffer busy waits" which means a process has been waiting for a buffer to become available.  Another one is "free buffer waits" which is when a server process has to wait because is could not find a free buffer.  Remember if you don't have TIMED_STATISTICS on these views are useless.

You can use the CACHE clause to force Oracle to store the data blocks associated with an object at the MRU end of the LRU list after a FTS (full table scan).  The default behavior is to send blocks to the least recently used end of the list.  Use this on small lookup tables.  Ensure you don't CACHE too many objects and use too much space in the buffer cache.  To cache a table:

1. Create the table with the CACHE clause.
2. Alter the table with the CACHE clause.
3. Use the CACHE hint in the query.

LRU latches regulate the LRU lists.  The number of LRU latches in the buffer pool defaults to 1/2 the number of CPU's.  You should ensure there are enough latches that the server processes don't have to contend for a latch.  Each latch must manage at least 50 buffers.  You goals for tuning the latches should be to reduce contention and balance the number of latches between CPU's.  You should also set at least one DBWR process for each latch.  The specific latch that you need to monitor is called the "cache buffers lru chain".  You can obtain information about this latch in the V$LATCH and V$LATCHNAME views.  You can set the number of latches by configuring the DB_BLOCK_LRU_LATCHES parameter.  Unless you have configured additional buffer pools it is unlikely you will benefit from more than one latch per CPU.  Using the V$LATCH view you can compare the number of sleeps to gets.  The "sleeps" value tells you how many times a process had to sleep because it could not acquire the latch.  The "gets" value returns the number of times the latch was requested.

SELECT name, sleeps/gets "LRU Hit%"
FROM v$latch
WHERE name ='cache buffers lru chain';

If the rate is less than 99% consider increasing the number of latches.  Two methods to determine the number of latches are:

1. Number of CPU's * 6
2. Number of buffers/50

When inserts occur on an object the server process must access the free list to see what blocks are available for insert.  If you do not have enough free lists on an object that experiences frequent inserts then contention on the free list will occur.  Increasing the number of free list will not really be helpful on single CPU machine.  The number of free lists can only be set for an object when it is created.  If you want to change the number of free lists you will need to re-create the object.  Your tuning goal for free lists should be to minimize contention.

You can use the V$SESSION_WAIT, V$SYSTEM_EVENT and V$WAITSTAT views to detect free list contention.  You can use the DBA_SEGMENTS view to determine which segments need to be recreated to increase the number of free lists.  

To query V$WAITSTAT:

SELECT class, count, time
FROM v$waitstat
WHERE class = 'segment header';

To query V$SYSTEM_EVENT:

SELECT event, total_waits
FROM v$system_event
WHERE event = 'buffer busy waits';

To reduce "buffer busy waits" on:

1. Data Blocks: Tune PCTFREE and PCTUSED parameters. Check for right-hand indexes (indexes that are inserted into the same point by numerous processes).  Increase INITRANS and reduce the number of rows per block.
2. Segment Header: Use free lists or free lists groups.
3. Free list blocks: Add more free lists.  If running OPS make sure each instance has it's own free list group.

You will need to query V$SESSION_WAIT (it can be a very confusing view until you get the hang of it) to determine the actual object experiencing free list contention. You can determine the FILE, BLOCK and ID by executing the following query when contention is occurring.

SELECT s.segment_name, s.segment_type, s.freelists, w.wait_time,
w.seconds_in_wait, w.state
FROM dba_segments s, v$session_wait w
WHERE w.event ='buffer busy waits'
AND w.p1 = s.header_file
AND w.p2 = s.header_block;

Re-create the object with increased free lists to reduce contention.

Tuning the Redo Log Buffer

When changes are made in the database the server process writes those changes to the redo log buffer.  Log Writer (LGWR) will then write the contents of the buffer out to the redo log files.  This is a circular buffer so obviously you want to have the contents written out to the redo logs before it fills up.  Otherwise the server processes have to wait.  You size the log buffer using the LOG_BUFFER parameter.  The starting size is usually 4 times the block size.  The larger the log buffer the less frequent is will need to write and the less I/O that will occur.  Every time a commit occurs the contents of the buffer are flushed to disk.  In this case a larger buffer will do little to help you.  The tuning goal for the log buffer is to ensure there is enough space in for the server processes.

If a server process begins to wait on space in the log buffer because LGWR can not write out the information fast enough a "log buffer space" wait occurs.  You can determine which session is waiting by looking at the V$SESSION_WAIT view.

select sid, event, seconds_in_wait, state
from v$session_wait
where event = 'log buffer space';

One reason LGWR might not be able to keep up is disk contention.  Ensure you redo log files are placed correctly to reduce contention.  Two other statistics you can look at are in the V$SYSSTAT view.

"redo_buffer_allocation_retries" - Number of times a process waited for space in the buffer to copy over entries that have been written to disk.

"redo log space requests" - Occurs when the active log file is full and the server is waiting for a log switch to occur so that it can continue.

SELECT name, value
FROM v$sysstat
WHERE name = 'redo buffer allocation retries';

The guidelines for checking the redo log buffer are as follows.  Ensure there are no "log buffer space" waits in V$SESSION_WAIT.  If there are consider increasing the size of the buffer or moving the redo log files to a faster disk configuration.

If "redo allocation retries" are more than 1% of the total "redo entries"  you might have a problem.  This could be caused by a small log buffer, frequent checkpointing, or frequent log switching.  You can modify any of these events to tune the buffer.

SELECT name, value
FROM v$sysstat
WHERE name IN ('redo buffer allocation retries',
'redo entries');

The LOG_BUFFER size must be a multiple of the OS block size.  There are some other ways you can diagnose log buffer performance.

• Ensure there is no disk contention and redo logs are on fast disks and separate from other data files.
• If you are experiencing a lot of "log file switch completion" waits then the cause of the waits are log file switches.  Consider increasing the size of the redo log files.
  
  select event, total_waits, time_waited, average_wait
  from v$system_event
  where event like 'log file switch completion%';
  
• DBWR might not have completed checkpointing a file when LGWR needs it again and LGWR has to wait.
  
  Look for the "CHECKPOINT NOT COMPLETE" value in the alert.log file.  Check the "log file switch (checkpoint incomplete)" waits in V$SYSTEM_EVENT.  This indicates log file switch waits due to waiting on checkpoints to complete.  Tune your checkpoints using the LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT parameter.  Check the size and number of redo log groups.
  
• ARCH (Archiver) might not be able to get an redo log archived in the amount of time it is required again.  This would certainly indicate you need to either increase the size of the redo log groups or add more groups.  You can check the "log file switch (archiving needed)" value in V$SYSSTAT which would indicate this problem.  You could increase the number of LOG_ARCHIVE_MAX_PROCESSES to help resolve this problem.
• DB_BLOCK_CHECKSUM could be set to true which would add more overhead to the process of writing out to the redo log files.

On way to fix redo log buffer contention is simply to reduce the amount of redo generated by your database.  Some ways to do this are:

• SQL*Loader direct path loading when the database is in NOARCHIVELOG mode.
• SQL*Loader direct path loading when the database is in ARCHIVELOG mode but the NOLOGGING attribute it set on the object.
• Direct load inserts using the NOLOGGING option. Applies to tables, indexes and tablespaces.  Only minimal logging occurs.  The NOLOGGING option is specified when the object is either created or altered.  Usually the attribute is set before the load  and reset after the load.  If you a media failure occurs before the next backup the objects modified could be corrupted.

A very common misconception is that the NOLOGGING attribute will cease logging for all operations on an object.  This is not true.  The NOLOGGING option will only stop redo for specific operations (CREATE TABLE...AS SELECT, CREATE INDEX and some others).  It will have no effect on standard DELETE, INSERT and UPDATE statements.

Database Configuration and I/O Issues

 Common sense should tell you to spread the files associated with your database across different disks and controllers to maximize I/O throughput and reduce contention.  Some guidelines regarding the creation of tablespaces are:

• Ensure the SYSTEM tablespace is only of data dictionary objects.  Do not allow users to create objects on the SYSTEM tablespace!
• Create locally managed tablespaces.
• Split tables and indexes into separate tablespaces.
• Create separate tablespaces for rollback segments.
• Store large database objects in their own tablespace.
• Create at least on temporary tablespace.

There are numerous benefits to LM (Locally Managed) tablespaces.

• Fewer concurrency problems.
• More extents without reducing performance.
• No beehive fragmentation trouble.

If a table contains LOBS or LONGS it should be placed on it's own tablespace.

Some guidelines for distributing the data in the data files are.

• Put the redo log files on a separate disks with no other activity.
• Consider striping large tables across data files.
• Don't put unrelated Oracle files that require I/O on any disks containing Oracle files.
• Consider putting your data files on raw and block devices.  On raw devices reads and writes are done at the character level.   On block devices they are done at the block level.

Another good way to balance I/O is by striping the data.  This can be accomplished either by manually striping the objects across more than one data file or by using RAID.  If you configure OS striping you will need to ensure you select the correct strip size.  The size should be a multiple of the DB_FILE_MULTIBLOCK_READ_COUNT.  Striping by hand is most beneficial when performing numerous parallel query operations and full table scans on an object.  Striping by hand is also very labor intensive.

Full tables scans (FTS) can also have a significant impact on I/O.  You can use the following query to determine if a large number of full table scans are taking place.  A number of rows will be returned. 

SELECT name, value FROM v$sysstat
WHERE name LIKE '%table scans%';

Two of the values directly relate to FTS.

• "table scans (long tables)"
• "table scans (short tables)"

If the number of long tables is high then a large number of the total lookups where not index range scans.  The optimizer uses either the lack of an index or the DB_FILE_MULTIBLOCK_READ_COUNT to determine when to perform an FTS.  This parameter determines how many blocks can be read in a single I/O during FTS.  If the cost to perform is lower than using an index range scan then the cost based optimizer (CBO) will perform a FTS.  The bigger you set this value the more likely the CBO will lean towards a FTS. A common myth is that FTS are always slower then index range scans.  This is not always true!  This parameter can be altered at the session level using the ALTER SESSION command.  The maximum size for this value is OS specific and is limited by the maximum unit of I/O that the OS can perform.  Some other factors included in determining the total number of I/O's that will occur during an operation are the table size and whether parallel query options are being used. You can use the V$SESSION_LONGOPS view to monitor the performance of long running FTS operations.  If you want to track this information from an application you can use the DBMS_APPLICATION_INFO package's SET_SESSION_LONGOPS procedure to populate the view from an application.

You can measure the amount of I/O that is occurring using the V$FILESTAT view, UTLBSTAT/UTLESTAT or Statspack.  The V$FILESTAT view measures physical reads, physical writes, blocks written, blocks read, read time and write time.  The last to only work if TIMED_STATISTICS are set to true.  You can join the view to V$DATAFILE and DBA_DATA_FILES to get more information about each individual file.  If you are using the report.txt file to examine file stats, look for uneven distribution of I/O.

As stated in the earlier the location and size of the redo log groups should be carefully considered.  Ensure you are not experiencing too many switches.  Ensure that different members of each group are placed on different disks.  Ensure that the redo logs are not competing with other resources for this disks.  Watch the "log file parallel write" value in V$SYSTEM_EVENT.  Any of these types of wait could indicate a possible I/O problem with the log files.  V$LOG and V$LOGFILE are the views you would use to obtain information about the redo log files.  You can use raw devices for log files. 

One more thing to note is that if you set DBWR_IO_SLAVES to a value greater than zero then Oracle will automatically set the number of I/O slaves for LGWR to 4.

Tuning the archiver (ARCH) process is also important to managing I/O.  There are a couple different things you can do.

• Ensure archiving is taking place on a disk that is not competing for other resources (LGWR, data).
• Force Oracle to use more archiving processes by issuing the ALTER SYSTEM LOG ARCHIVE ALL command.
• Edit a number of the archiving related parameters.
• Increase the number of redo log groups.

You can set the LOG_ARCHIVE_MAX_PROCESSES parameter to allow Oracle to increase the number of ARCH processes when demand on the database is heavy.  The V$ARCHIVE_PROCESSES view returns information about each of the archiver processes.  The number of I/O slaves used by the archiver process is also 4 when the DBWR_IO_SLAVES parameter is a value greater than zero.  Archive logs cannot be created on raw devices.  Other parameter you could configure are LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n (Enterprise Edition only).  The V$ARCHIVED_LOG view returns information about the archive logs from the control file.  To obtain information about the status of all archive log destinations for the current instance use V$ARCHIVE_DEST.

Tuning your checkpointing interval can also help you manage I/O.  Checkpoints cause all of the dirty blocks in the buffer cache to be written to disk and all of the data files headers are synchronized.  This is a fairly write intensive process so you want to make sure that checkpoints do not occur to frequently.  When do checkpoints occur?

• Every time a log switch occurs.  You can increase the log file sizes or number of redo log groups to ensure checkpoints have time to complete before another switch is required.  Also larger files will mean less frequent checkpoints.
• As defined by FAST_START_IO_TARGET.
• As defined by either LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT.
• As defined by DB_BLOCK_MAX_DIRTY_TARGET.

If you would like to have checkpoints logged in the alert logs set the LOG_CHECKPOINTS_TO_ALERT parameter to TRUE.  The more frequent the checkpoints occur the greater the performance impact.  However, longer intervals between checkpoints mean longer instance recovery times.

You can simulate asynchronous I/O when it is not available by deploying I/O slaves.  I/O slaves are configured by setting the DBWR_IO_SLAVES parameter.  These slaves are primarily configured for the database writer process but archiver, log writer and backup processes can also use these slaves.  Usually you do not use IO slaves when asynchronous I/O is available.  I/O slaves can also be configured for tape devices setting the BACKUP_TAPE_IO_SLAVES to TRUE.  To turn on asynchronous I/O configure the DISK_ASYNC_IO and TAPE_ASYNC_IO parameters.  It is usually best to leave these parameter to their default of TRUE.

You could instead opt to configure multiple DBWR processes by setting the DB_WRITER_PROCESSES parameter.  You can not configure additional database writer processes and I/O slaves.  You can configure up to 10 processes.  This parameter is useful for SMP systems with a large number of CPUs.  You can use the DB_BLOCK_MAX_DIRTY_TARGET parameter to influence when DBWR writes.  This parameter specifies the number of buffers than can be dirty and defaults to all of the buffers in the buffer cache.  This should of course never happen because a number of other events will trigger DBWR to write.  

Using Oracle Blocks Efficiently

You should by this point in your Oracle studies have a good idea of how Oracle manages space.  Objects are contained in segments which are made up of extents which are made up of blocks.  The blocks in an extent must be contiguous.  However, extents within a segment do not have to be contiguous.  When a segment needs to grow it is accomplished by allocating an new extent to the object.  Whenever this occurs a number of recursive operations result as Oracle manages the allocation of the extent.  This is called dynamic extension and should be avoided.  You can avoid this by:

• Using locally managed tablespaces
• Sizes segments correctly
• Monitoring and pre-allocating extents

Ideally you allocating enough space to an object so that it does not need to obtain more for al least a year when using dictionary managed tablespaces.  Locally managed tablespaces manages it's own extents using a bitmap in each data file to keep track of used and free extents.  Rollback is not generated for these types of operations.  You can also configure the LMT to use a uniform extent size. 

Oracle recommends setting extent sizes to a multiple of 5 * MULTI_BLOCK_READ_COUNT because extents are allocated on five-block bounderies (not sure if this is still true in latest version!).   

Another way to tune the use of Oracle blocks is to minimize the total number of blocks that need to be read to obtain data.  This means packing more rows into your blocks, avoiding row migration and using a larger block size.  The size is set when you create the database and this can not be easily changed.  The default is 2K or 4K and can go up to 64K.  This should always be a multiple of the OS block size.  Oracle recommends that the OS I/O size be equal to or greater than the Oracle block size.  In general you should use a smaller block size for OLTP environments and larger block sizes in DSS environments.  

Smaller Block Sizes:

• Pros: Less block contention, good for small rows, good for random access.
• Cons: Large overhead, less rows per block, more index blocks need to be read.

Larger Block Sizes:

• Pros: Less overhead, good for sequential access, good for large rows, better for index reads.
• Cons: Greater chance of block contention, uses more space in buffer cache.

You should already be very familiar with the PCTFREE and PCTUSED settings.  These could also appear on the tuning exam.  You should also know that Oracle very rarely compresses the free space in a block if rows are deleted.  Only certain circumstances will trigger this action.  The reason this is not common is because Oracle would constantly be attempting to coalesce free space in the block.  The two situations where this would occur are:

• An INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row.
• The free space is so fragmented that a new row cannot be inserted into a contiguous section of the block.

The PCTUSED parameter is irrelevant if a table does not experience any deletes.  PCTFREE+PCTUSED should always be less than 100.  The closer this value is to 100 the more likely contention for the free list will occur (this is debatable!).  Oracle has some complicated formulas for calculating these values on a table.  In the real world however it is always best to perform tests with sample data to find the best settings.  If you set PCTFREE too low then updates will force row migration to occur.  When you change these settings the changes are only applied to new blocks not existing ones.

It is important that you know how to detect row migration.  The easiest method is to run the ANALYZE table COMPUTE STATISTICS command on a table.  After running the command you can check the CHAIN_CNT column of DBA_TABLES to determine the number of migrated and chained rows.  You can compare this against the NUM_ROWS column to determine what percentage of the rows are causing a problem.  You can also detect chained/migrated rows by monitoring the "table fetch continued row" statistic in V$SYSSTAT or report.txt.

To determine which rows are experiencing a problem with migration in an object use the ANALYZE command with the LIST CHAINED ROWS option.  To use this option you need to create a table to store the information gathered by the ANALYZE command.  This table can be created by running the utlchain.sql script.  It will create a table called CHAINED_ROWS.  After identifying the rows you should copy them to another table, delete them from the original table, and then copy them back in to the original table.  Of course this will only fix the problem of migrated rows, not chained.

You need to understand how the HWM (high water mark) on a table works.  Full table scans always scan up to the high water mark.  Space above the mark can be reclaimed by running the ALTER TABLE tablename DEALLOCATE UNUSED command.  The high water mark is incremented in 5 blocks increments. Other than recreating the object the only way to reset the HWM is to use the TRUNCATE command (not DELETE).

By using the ANALYZE object COMPUTE STATISTICS command you can gather a number of important statistics.  The DBA_TABLES view contains the following columns.

• NUM_ROWS - The number of rows in the table.
• BLOCKS - Total number of blocks below the HWM.
• EMPTY_BLOCKS - Total number of block above the HWM (never been used blocks).
• AVG_SPACE - Average amount of free space in the blocks below the HWM.
• AVG_ROW_LEN - Average row length including overhead.
• CHAIN_CNT - Number of chained or migrated rows.

Oracle supplies a package called DBMS_SPACE.  This package allows you to analyze segment growth and space requirements.  The package contains two procedures.

1. UNUSED_SPACE - Returns information about unused space in an object.
2. FREE_BLOCKS - Returns information about the free blocks in an object.

Indexes that experience a lot of insert/delete activity may need to be rebuilt occasionally.  You can determine if an index needs to be rebuilt by running the ANALYZE INDEX indexname VALIDATE STATISTICS command.  This will populate a view called INDEX_STATS that contains information about the index.

• LF_ROWS - Number of values in the index.
• LF_ROWS_LEN - Sum in bytes of the length of all values.
• DEL_LF_ROWS - Number of values deleted from the index.
• DEL_LF_ROWS_LEN - Length of all deleted values.

If more than 20% of the indexes rows are deleted you may want to rebuild the index.  This can be accomplished using the ALTER INDEX indexname REBUILD command.  All of the storage options are available so you can change the extent allocation, tablespace and other characteristics of the index.  You can reduce the total time it takes to rebuild the index by using the PARALLEL option.  You could also use the NOLOGGING option during index creation.  If you do not want the attribute to be configured as NOLOGGING permenently on the object use the UNRECOVERABLE option.  The UNRECOVERABLE option will be going away in later versions of Oracle. Using this method is usually faster than dropping and re-creating the index because it uses the fast full scan feature.  This means it reads all index blocks using multiblock I/O and then discards the branch blocks.  In Oracle 8i you can perform these operations while current transactions are processing on the base tables.

Optimizing Sort Operations

The following types of operations require sorting.

• Index creation.
• Parallel insert operations involving index maintenance.
• ORDER BY and GROUP BY clauses.
• DISTINCT values clause.
• UNION, INTERSECT and MINUS operations.
• Sort-merge joins.  Used when joining tables on columns with no indexes.
• ANALYZE command execution.

The amount of memory to allocate to each process is defined by the SORT_AREA_SIZE in the initialization file.  However, this can be changed at the session level using the ALTER SESSION command.  When the sort requires more space than this the following events occur.

• The sort is split into smaller pieces.  Each piece is called a sort run and sorted individually.
• The sort runs are stored in temporary segments on disk.
• All of the different sort runs are merged together to form the final result.  This may also be done if a number of different merge phases if required.

The SORT_MULTIBLOCK_READ_COUNT parameter can be used to force Oracle to read a larger section of each sort run from disk to memory during the merge pass.  If the system is experiencing a high rate of I/O during a large sort and there is very little CPU usage, consider increasing this parameter.  The memory allocated for the sort comes from the PGA when the server is a dedicated server.  When it is a multi-thread server the memory comes from the SGA.  

When a sort occurs by a single server process the amount of memory consumed by the sort is the amount specified by SORT_AREA_SIZE (assuming the sort needs all of the memory) and two times the SORT_AREA_RETAINED_SIZE for the join sorts.  If the query is parallelized use the following calculations.

• SORT_AREA_SIZE * 2 * Degree of parallelism
• (Possibly) SORT_AREA_RETAINED_SIZE * Degree of parallelism * number of sorts above 2

In testing Oracle has demonstrated that the optimal size for SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE is 1MB when using parallel operations.  In all cases these values should be set the same unless you are short on memory or running in MTS (Multi-Threaded Server) mode.

Tuning Rollback Segments

Monitoring and Detecting Lock Detection

SQL Issues and Tuning Considerations for Different Applications

Managing a Mixed Workload

Tuning with Oracle Expert

Multithreaded Server Tuning Issues