1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Server Tuning

Discussion in 'Server Administration and Options' started by Julian, Aug 17, 2008.

  1. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Oracle Server Tuning - Part 1

    OVERVIEW OF TUNING OF AN ORACLE PRODUCTION INSTANCE


    The Oracle server is a sophisticated and highly tunable software product. Whether you are designing or maintaining a system, you should set specific performance goals so that you know when to tune. The most effective method to tune the system is as follows:
    1. Consider performance when designing the system
    2. Tune the operating system hardware and software
    3. Identify performance bottlenecks
    4. Determine the cause of the problems
    5. Take corrective actions.
    Tuning a production system

    Each performance problem has a unique cause, and you may need to query several dynamic performance views to find the cause of some problems (like v$session_wait view lists the events that cause all users and system sessions to wait.). Performance problems tend to fall into one of these categories:
    1. CPU
    2. Memory
    3. I/O
    4. Contention for latches or other structures
    CPU tuning

    Checking system CPU utilization:

    You have a CPU problem if idle time and time waiting for I/O are both close to zero at a normal or low workload. In UNIX system you can check whether your system is suffering from CPU problem through
    sar –u command
    In NT system, this can be checked through the system performance monitor.

    Whether the system is suffering from paging/swapping, can be checked by
    vmstat command in UNIX system.

    Checking Oracle CPU utilization:

    1. Reparsing SQL statement

    Execute the following query

    Code (Text):

    Select *
    From v$sysstat
    Where name in ( 'parse time CPU' , 'parse time elapsed' , 'parse count');
     
    Where,

    Response time = service time + wait time
    Response time = elapsed time
    Service time = CPU time
    Elapsed time – CPU time = wait time
    Wait time / parse count = average wait time per parse

    The average wait time should be extremely low.
    Parsing can be reduced by utilizing the initialization parameter SESSION_CACHED_CURSORS

    2. Inefficient SQL statement

    Execute the following query

    Code (Text):

    Select buffer_gets, sql_text, parse_calls, executions
    From v$sqlarea
    Order by parse_calls
     
    The sql statements with high number of buffer gets may be inefficient.

    I will talk about Tuning Memory Allocation in Part II of this article.
     
  2. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Oracle Server Tuning: Part 2

    Hi everyone, sorry for the delay, was occupied with work! :(

    So continuing,

    Tuning Memory Allocation

    Steps for tuning Memory Allocation

    Tuning the Operating System

    You should begin tuning memory allocation by tuning operating systems with these goals

    1. To reduce paging and swapping

    Your Operating system may store information in any of these places
    Real memory, virtual memory, expanded storage, disk
    The movement of information from one location to another is called paging or swapping. Excessive paging or swapping indicates that new information is often moved into memory. You should either increase the total memory on your system or decrease the amount of memory you have allocated.

    2. Tuning the system Global Area

    Since the purpose of the System Global Area (SGA) is to store data in memory for faster access, the SGA should always be contained in main memory. You can cause Oracle to read entire SGA into memory when you start the instance by setting the value of the initialization parameter PRE_PAGE_SGA to YES. This setting may increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

    3. User Memory Allocation
    On some operating systems, you may have control over the amount of physical memory allocated to each user. Be sure all users are allocated enough memory to accommodate the resources they need to use their application with Oracle. By sharing executable images among users, you can reduce the amount of memory required by each user.

    Tuning Private SQL and PL/SQL Areas

    Tuning private SQL areas involves identifying unnecessary parse calls made by your application and then reducing them. To reduce parse calls, you may have to increase the number of private SQL areas that your application can have allocated at once.

    To identify unnecessary parse calls, run your application with the SQL trace facility enabled. For each SQL statement in the trace output, examine the count statistic for the Parse step. This statistic tells you how many times your application makes a parse call for the statement. This statistic includes parse calls that are satisfied by access to the library cache as well as parse calls that result in actually parsing the statement.
    Be sure that your application can open enough private SQL areas to accommodate all of your SQL statements. If you allocate more private SQL areas, you may need to increase the limit on the number of cursors permitted for a session. You can increase this limit by increasing the value of the initialization parameter OPEN_CURSORS. The maximum value for this parameter depends on your operating system. The minimum value is 5.

    Tuning the Shared Pool

    Tuning the Library Cache

    The library cache contains shared SQL and PL/SQL areas. The Library Cache can be tuned by the following processes

    1. Examining library cache activity
    2. Reducing library cache misses
    3. Speeding access to shared SQL and PL/SQL areas in the library cache

    Execute the following query
    Code (Text):

    Select sum(pins)  "Executions",
    Sum(reloads)  "Cache Misses While Executing"
    From v$librarycache;
     
    The ratio of the total reloads to the total pins should be near zero. If the ratio is more than 1% then library cache misses should be reduced by either allocating additional memory for the library cache ( by increasing the value of the initialization parameter SHARED_POOL_SIZE ) or by writing identical SQL statement whenever possible.

    If you have no library cache misses, you may still be able to speed execution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME. This parameter specifies when a shared SQL area can be deallocated from the library cache to make room for a new SQL statement. The default value of this parameter is FALSE, meaning that a shared SQL area can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. The value of TRUE means that a shared SQL area can only be deallocated when all application cursors associated with its statement are closed. Setting the value of the parameter to TRUE saves Oracle a small amount of time and may slightly improve the performance of execution calls.

    Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if there are library cache misses on execution calls or he amount of memory available to each user for private SQL areas is scarce.

    Tuning the Data Dictionary Cache

    Execute the following query
    Code (Text):

    Select sum(gets)  "Data Dictionary Gets",
    Sum(Getmisses)  "Dictionary Cache Get Misses"
    From v$rowcache;
     
    For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%. If this ratio continues to increase above this threshold while your application is running, you should consider increasing the amount of memory available to the data dictionary cache. To increase the memory available to the cache, increase the value of the initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter varies depending on your operating system.

    Tuning Reserve space for Shared Pool

    As an initial value, make SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. The default value of SHARED_POOL_RESERVED_SIZE is adequate.
    Query v$shared_pool_reserved view

    1. Shared_pool_reserved_size is too small when
    Request_failures > 0
    2. Shared_pool_reserved_size is too large when
    Request_Miss = 0 or not increasing
    Free_memory => 50% of SHARED_POOL_RESERVED_SIZE
    3. Shared_pool_size is too small when
    Request_failures > 0 and increasing

    Last_failure_size < SHARED_POOL_RESERVED_MIN_ALLOC

    Tuning The Buffer Cache

    Execute the following query
    Code (Text):

    Select name, value
    From v$sysstat
    Where name in ('db block gets', 'consistent gets' , 'physical reads');
     
    Calculate the hit ratio for the buffer cache with this formula:
    Hit Ratio = 1 - ( physical reads / (db block gets + consistent gets) )
    If your hit ratio is low, say less than 70%, then you may want to increase the number of buffers in the cache to improve performance. To make the buffer cache larger, increase the value of the initialization parameter DB_BLOCK_BUFFERS.

    Tuning The Redo Log Buffer

    Execute the following query
    Code (Text):

    Select name, value
    From v$sysstat
    Where name in ('redo log space requests', 'redo entries');
     
    Calculate the efficiency with the following formula:
    Space Requests Ratio = Redo log space requests / Redo entries
    If this ratio is greater than 1:5000, then increase the size of redo log buffer( by properly setting the initialization parameter LOG_BUFFER expressed in bytes and must be a multiple of DB_BLOCK_SIZE )until the space request ratio stops falling.

    Tuning Sorts

    Some applications cause Oracle to sort data. The default sort area size is adequate to hold all the data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, then you may want to increase the sort area size.

    Execute the following query
    Code (Text):

    Select name, value
    From v$sysstat
    Where name in ('sorts(memory)', 'sorts(disks)' ) ;
     
    If a significant number of sorts require disk I/O to temporary segments, then your application's performance may benefit from increasing the size of the sort area. In this case, increase the value of the initialization parameter SORT_AREA_SIZE. The maximum value of this parameter varies depending on your operating system.

    If you increase the size of your sort area, you may consider decreasing the retained size of the sort area, or the size to which Oracle reduces the sort area if its data is not expected to be referenced soon. To decrease the retained size of the sort area, decrease the value of the initialization parameter SORT_AREA_RETAINED_SIZE. A smaller retained sort area reduces memory usage but causes additional I/O to write and read data to and from temporary segments on disk.
    You can optimize sort performance of sorts by specifying a tablespace as TEMPORARY upon creation (or subsequently altering that tablespace) and performing the sort in that tablespace.

    If memory and temporary space are abundant on your system, and you perform many large sorts to disk, you can set the initialization parameter SORT_DIRECT_WRITES to increase sort performance. When this parameter is set to TRUE, each sort will allocate several large buffers in memory for direct disk I/O. You can set the initialization parameters SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE to control the number and size of these buffers. The sort will write an entire buffer for each I/O operation. The Oracle process performing the sort writes the sort data directly to the disk, bypassing the buffer cache. The default value of SORT_DIRECT_WRITES is AUTO. When the parameter is unspecified or set to AUTO, Oracle automatically allocates direct write buffers if the SORT_AREA_SIZE is at least ten times the minimum direct write buffer configuration. The memory for the direct write buffers is subtracted from the sort area, so the total amount of memory used for each sort is still SORT_AREA_SIZE. Setting SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE has no effect when SORT_DIRECT_WRITES is AUTO.

    Setting SORT_DIRECT_WRITES to TRUE causes each Oracle process that sorts to allocate memory in addition to that already allocated for the sort area. The additional memory allocated is calculated as follows:
    SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE

    The minimum direct write configuration on most platforms is two 32K buffers (2 * 32K) , so direct write is generally allocated only if the sort area is 640K or greater. With a sort area smaller than this, direct write will not be performed. Ensure that your operating system has enough free memory available to accommodate this increase. Also, sorts that use direct writes will tend to consume more temporary segment space on disk. One way to avoid increasing memory usage is to decrease the sort area by the amount of memory allocated for direct writes. Note that reducing the sort area may increase the number of sorts to disk, which will decrease overall performance.

    A good rule of thumb is that the total memory allocated for direct write buffers should be less than one-tenth of the memory allocated for the sort area. If the minimum configuration of the direct write buffers is greater than one-tenth of your sort area, then you should not trade sort area for direct write buffers.
     
  3. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Oracle Server Tuning: Part 3

    Tuning I/O


    The performance of many software applications is inherently limited by disk I/O. Tuning I/O can help performance if a disk containing database files is operating at its capacity. It is important to tune I/O after following the memory allocation recommended earlier.

    Reducing disk contention

    Disk contention occurs when multiple processes try to access the same disk simultaneously. Examine disk access to database files through the dynamic performance table V$FILESTAT.

    Execute the following query
    Code (Text):

    Select name, phyrds, phywrts
    From v$datafile df,  v$filestat fs
    Where df.file# = fs.file#
     
    The total I/O for a single disk is the sum of PHYRDS and PHYWRTS for all the database files managed by the Oracle instance on that disk. Determine this value for each of your disks. Also determine the rate at which I/O occurs for each disk by dividing the total I/O by the interval of time over which the statistics were collected.

    Steps to be taken for distributing I/O

    1. Separate datafiles and redo log files on different disks.
    2. Separate, or "stripe", table data on different disks.
    3. Separate tables and indexes on different disks.
    4. Reduce disk I/O not related to Oracle.

    Tuning Contention

    Contention occurs when multiple processes try to access the same resource simultaneously. Contention causes processes to wait for access to various database structures.

    Reducing Contention for Rollback Segments

    Execute the following query
    Code (Text):

    Select class, count
    From v$waitstat
    Where class in ('system undo header', 'system undo block',
    'undo header', 'undo block');
     
    Compare the number of waits for each class of block with the total number of requests for data over the same period of time. You can monitor the total number of requests for data over a period of time with this query:
    Code (Text):

    Select sum(value)
    From v$sysstat
    Where name in ( 'db block gets', 'consistent gets' ) ;
     
    If the number of waits for any class is greater than 1% of the total number of requests, you should consider creating more rollback segments to reduce contention.

    Reducing contention for redo log buffer latches

    Access to the redo log buffer is regulated by latches. The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. The maximum size of a redo entry that can be copied on the redo allocation latch is specified by the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE. The value of this parameter is expressed in bytes. The minimum, maximum, and default values vary depending on your operating system.

    If the redo entry is too large to copy on the redo allocation latch, the user process must obtain a redo copy latch before copying the entry into the buffer. The number of redo copy latches is determined by the initialization parameter LOG_SIMULTANEOUS_COPIES. The default value of LOG_SIMULTANEOUS_COPIES is the number of CPUs available to your Oracle instance.

    Monitor the statistics for the redo allocation latch and the redo copy latches over a period of time with the following query:
    Code (Text):

    Select ln.name, gets, misses, immediate_gets, immediate_misses
    FROM v$latch l, v$latchname ln
    WHERE ln.name IN ('redo allocation', 'redo copy')
    AND ln.latch# = l.latch# ;
     
    From the output of the query, calculate the wait ratio for each type of request.

    Contention for a latch may be affecting performance if either of these conditions is true:

    1. if the ratio of MISSES to GETS exceeds 1%
    2. if the ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_GETS and IMMEDIATE_MISSES exceeds 1%

    If either of these conditions is true for a latch, try to reduce contention for that latch.

    Most cases of latch contention occur when two or more Oracle processes concurrently attempt to obtain the same latch.

    Reducing Contention for the Redo Allocation Latch

    To reduce contention for the redo allocation latch, you should minimize the time that any single process holds the latch. To reduce this time, reduce copying on the redo allocation latch. Decreasing the value of the LOG_SMALL_ENTRY_MAX_SIZE initialization parameter reduces the number and size of redo entries copied on the redo allocation latch.

    Reducing Contention for Redo Copy Latches

    On multiple-CPU computers, multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The default value of LOG_SIMULTANEOUS_COPIES is the number of CPUs available to your Oracle instance.

    If you observe contention for redo copy latches, add more latches. To increase the number of redo copy latches, increase the value of LOG_SIMULTANEOUS_COPIES. It can help to have up to twice as many redo copy latches as CPUs available to your Oracle instance.

    Reducing LRU Latch Contention

    Contention for the LRU latch can impede performance on symmetric multiprocessor (SMP) machines with a large number of CPUs. You can specify the number of LRU latches on your system with the initialization parameter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the desired number of LRU latches. To select the appropriate value for DB_BLOCK_LRU_LATCHES, consider the following:

    1. The maximum number of latches is twice the number of CPUs in the system. For example, DB_BLOCK_LRU_LATCHES can range from 1...2*CPUs.

    2. A latch should have no less than 50 buffers in its set (for small buffer caches there is no added value if you select a larger number of sets). The size of the buffer cache determines a maximum boundary condition on the number of sets.

    3. Do not create multiple latches when Oracle runs in single process mode. Oracle automatically uses only one LRU latch in single process mode.

    4. If your workload on the instance is large, then you should have a higher number of latches. For example, if you have 32 CPUs in your system, choose a number between half the number of CPUs (16) and actual number of CPUs (32) in your system.