Oracle Performance and Tuning (1 of 3)

  1. Tuning Overview
    1. List the roles associated with the database tuning process
    2. The DBA, the application developer, management, the system administrator, the network administrator.

    3. Define the steps associated with the tunning process
    4. Considered on the ROI (Return on Investment) outline here are the tunning recommendation in order:

        1. Do a proper logical design: In practice this often means more tables with fewer rows per table. In turn, this means the capability for faster searching. The fewer rows that are stored and must be searched through, regardless of the search techinque, the quicker you'll be able to find what you're looking for.
        2. Do a proper physical design : Separate the datafiles (tablespaces ) into different disks to avoid I/O contention , use striping.
        3. Redesign if necessary Sometimes the best way to correct the database without extensive tuning efforts is a re-analysis and redesign. Consider redesign when the initial design was hurried, or when multiple databases need to be integrated.
        4. Write Efficient Application Code: If some SQL code uses an inefficient seach or sort routine, despite the best efforts of the Oracle optimizer, the application will run slowly.
        5. Rewrite code if necessary: If application code efficiency comes into question and resource and management permit , re-analyze and re-write the code.
        6. Tune the database memory structures Oracle can offer substantial improvments through the tuning of its database buffer cache. Also the shared pool caches SQL code via the library cache component and caches the data dictonary component through ,obviously ,data dictonary cache. The redo log buffer is a separatly tunable area in the SGA.
        7. Tune OS memory Structures if necessary : The SWAP area can become a bottleneck since it functions as an OS temporary storage, user temporary storage, and the OS virtual memory backing store. The SA and DBA musht work together so that the OS provides enough shared memory and semaphores to give the Oracle processes enough breathing room to operate efficiently.
        1. Tune Database I/O Database I/O is affected by both the RDBMS and the OS of course, but tuning the I/O means relocating logical and physical structures to reduce contention. If this point in tunning is reached you will have already tuned the database buffer cache. Now the main focus will be to simply adjust the physical design. You physically do more redesiging , if necessary with I/O in mind exclusively.
        2. Tune OS I/O if necessary The OS fullfils all write and reads requests by all processes, including the Oracle background procceses DBWR and LGRW. As OS typically buffers these requests, performs reads and writes, and then returns the acknowlegment and data back to the process upon completion.

          File system are data structures that contain metadata about the files they manage, suche as the location of each files starting sector address, its sector length, its directory tree location, its attributes (permissions,size,timestamps,etc). In UNIX, file systems have their own logical block sizes, which correspond to something greater than or equal to a physical block size (512 bytes), usually 8KB by default. The oracle Block size should be at least 8KB or a multiple of it,such as 16KB.
        3. Other importatn OS and Oracle I/O tuning issues include: read-ahead capabilities, asynchronous I/O, multiblock reads, RAID stripe sizes, disk geometry issues, controlles issues,and many more.

        4. Tune the network if necessary A saturated network can cancel out imporvments made by database tuning.
        5. Tune the clients if necessary
        6. Consider more exotic solutions Oracle Multithreaded Server ("MTS") , transaction processing (TP) monitors, Oracle Parallel Query and other parallel capabilities, Oracle's clustering capabilities, Oracle's bitmapped indexing, MPP machines, solid state disks, memory-resident (RAM) disks, hardware accelerators and queuing systems.
    1. Identify tuning goals
    2. There are different ways of determining the goals of a performance tuning effort, consider the application type, also sampling the database on various quantative measaurs is further defining the tuning goals:

      1. Throughput : Work per unit time , as measured by transactions per second; higher is obviously better.
      2. Response Time: The time it takes for an application to respond, measured in milliseconds or seconds, lower is better.
      3. Wall Time : The elapsed time a program takes to run, lower is better.

      In most systems throughput and response time run counter to one another as tuning goals. If response time is high (bad), throughput might be high (good). If throughput is low (bad) then response time might be low (good).

      Typically OLTP systems want low response time or high throughput, in terms of transaction on the application needs. A DSS wants low response time , and a batch system normally wants lower wall times.

      Always consider the two central tuning goals :

        1. Maximize your return on invesetment Invest your time and effort wisely by working on the problems most likely to yield the most improvement.
        2. Minimize contention : Bottlenecks are characterized by delays and waits;eliminate or reduce these whenever possible.

      Also consider these general-purpose tuning goals :

        1. Minimize the number of blocks that need to be accessed; review and rewrite code as necessary.
        2. Use caching, buffering and queueing whenever possible to compensate for the electromechanical disadvantage ( memory is faster than disk);prefetch
        3. Minimize data transfer rates ( the time it takes to read or write data); fast disks,RAID and parallel operations help do this.
        4. Schedule Programs to run as noncompetetively as possible, they might run concurrently and yet still be noncompetetive for the most part.
    3. List the strengths of different database configurations for recoverability.
    4. The two main configurations for running a database are ARCHIVELOG and NOARCHIVELOG. Assuming a recovery operation is needed the following scenarios could arise:

      If the database is in ARCHIVOLOG there are two possibilities:

        1. Complete-Recovery : The database is restored and recovered through the applicaion of ALL redo information (this includes both the online and archived redo log files) since the last backup. This type of recovery is performed normally when one of more data files or control files are damaged, the damaged files are recovered using al the redo information generated since the last full backup.
        2. Incomplete-Recovery: In this scenario the database is restored and recovered through the application of only SOME of the redo information generated since the last backup. This type of recovery is normally used when an on-line redo log file is lost due to hardware failure or a certain user requires to backup to a certain point in time,in simpler terms, an incomplete recovery only rollsback certain transactions without the need to involve ALL the redo-log files.

      If the database is in NOARCHIVELOG:

      You benefit from not having to save all the on-line redo log files, on heavily accesed systems this type of configuration alleviates disk-space usage,because in ARCHIVELOG every on-line redo log file is eventually backed-up; these archived redo-log files can easily fill up a WHOLE disk (10-12 GB) in a matter of hours. However the disadvantage of running this configuration is that in the event of a failure your only means of backup is by your last backup tape, Oracle cannot help in this case because it does not have a history of previous redo-log files , the ones that are ARCHIVED in ARCHIVELOG mode.

  1. Oracle Alert Log and Trace Files
    1. Describe the location and usefullnes of the Alert Log
    2. The alert log records the commands and command results of major events in the life of the database,e.g. Tablespace creation, redo log switches, recovery operations, and data base startups.

      This file is located at the location specified by the parameter BACKGROUND_DUMP_TEST in the init.ora file.

    3. Describe the location and usefullness of the background and user processes dump files.
    4. When a background processes is terminated or abnormally aborts an operation, it usually produces a trace file containing an error message causing the failure. Dumps of the current process stacks, currently executing cursors, and many other information pertinent to the problem. A background dump test is saved at the background_dump_test parameter in the init.ora file, these processes are commonly known as "detached process dumps". A user dump test is saved to the directory specified in the parameter user_dump_test in the init.ora file, these processes are of cours known as "user process dumps".

      It is more important to collect these files and forward them to Oracle Support as they may help resolve the problem. The background processes also write an entry to the alert.log file for the database.

  2. Utilities and Dynamic Performance Views
    1. Collect analysis through
      1. Available Dynamic troubleshooting and performance views.
      2. All the Oracle products such as server manager,enterprise manager, rely on the V$ dynamic performance views. These views are grouped into instance, database, memory, disk, user, session, and contention aspects of performance they are based on the internal X$ base tables. The available V$ views are at the table V$FIXED_TABLE to get a listing of the V$ views.

        The V$ views are called dynamic because they are populated at instance startup and are truncated at shutdown. The V$ views also form the basis of the standart Oracle tuning scripts, UTLBSTAT/UTLESTAT, which query them using SQL scripts and format the output that is returned. Therefore, if UTLBSTAT/UTLESTAT do not give you what you want, you can use Server Manager and the V$ views to either supplement or supplant those utilities.

      3. The UTLBSTAT/UTLESTAT report output
      4. This is the most commonly used diagnostic utility. The DBA runs UTLBSTAT before running his or her application or simulation. The utlbstat script builds the beginning tables necessary to collect and store the performance data. The the DBA run utlestat which builds the ending tables and the difference tables, computes the performance differences (deltas) between the utlbstat run and this utlestat run, formats the data output data ( including comments and some explanations ), and writes it to the default file, report.txt . This file must be interpreted by the DBA, either directly or inderectily ( by taking some of the output values given and using them as inputs into simple formulas ).

        Interpretation of this data means comparing these final figures to more or less established guidelines, keeping the ROI startegy in mind, and categorizing the findings as acceptable or not for that given area of performance.

      5. Oracle wait events
      6. Appropriate Enterprise Manager tuning tools
      7. Enterprise Manager "performance pack" is extremly useful.The components in the performance pack help analyze your logicaland physical design. They also monitor locks, a variety of performance issues (throughput, redo, rollback,I/O, memory,etc),the top user sessions with regard to resource consumption, you tablespace storage (data files,fragmentation,etc), and application events through tracing.

    2. Define the latch types
    3. Latches are used to control access to shared structures. Latches are implemented using semaphores at the OS level. Latches are locks that are held for a very small amount of time.

      Before a process gets access to a shared structure protected by a latch, it has to first acquire the latch. This latch could currently be free (that is, no other process is accessing the latch) in which case the processes gets the latch immediatly. This processes will hold the latch for the period of time it requires and will then relinquish the latch. In case the latch is already acquired by another process,the process has two options:

        1. Acquire the latch in immediate mode If this ocurrs and the latch is already being used by another process (as is the case ), the process will not wait to acquire the latch, it will continue by taking a different action.
        2. Acquire the latch in willing to wait mode If the process fails to acquire the latch in the first try, it will wait and try again. If the system has multiple CPUs, this unsuccesful process will start spinning on the latch and try to acquire it. THe number of times the process spins on the latch is defined by the parameter spin_count in the init.ora file. With every spin, it will try to acquire the latch, if it does not , it will continue to try to acquire the latch until the spin_count parameter in the init.ora file is reached. After that the processes will go to sleep for a specified amount of time, wake up again, and repeat the aforementioned cycle.
        3. To view latches the following "views" are used :

        4. V$latch : This view conatins all important statistics related to the performance of various latch on the system.
        5. V$latchholder : If the system is currently having latch contention problems , this view can be used to determine which session is currently holding the latch.

      The Important latches:

      There are aprox. 52 types of latches on an Oracle installation. However the follwing latches are of significant importance in any tuning job.

      1. cache buffers lru chain latch : This latch is responsible for protecting the access paths to db block buffer in the buffer cache. The buffer cache size defined by the parameter db_block_buffers resides in the SGA and contains a cached copy of data read from data files.
      2. The buffer cache is organized in two lists: the dirty list and the LRU list. The dirty list contains the buffers that have been modified but not written to the disk yet. The LRU list is comprised of the pinned buffers, the dirty buffers that have not yet been moved to the dirty list, and the free buffers. The pinned buffer are buffers that are currently accessed by other processes. The dirty buffers contain buffers that are to be written to the disk, and they then subsequently get moved to the dirty list. The free buffers are the buffers that are available for use.

        When a process needs to read data from the disk that is not already in the cache, it needs a free buffer to read the new data. It scans the LRU list to check for free buffers. If there are excessive requests for free buffers in the buffer cache, tere will be high access to the LRU list causing contention for the cache buffer LRU chain. The contention for this latch can be minimized with the parameter db_block_lru_latches in the init.ora file. By increasing this paramater, the contention for this latch can be minimized. The maximum value for this parameter is double the numberof CPUs

        The basic reason for contention for this latch is a high request for free buffers. You can optimize the SQL statments to minimize the high demand for free buffers or increase the db_block_buffer parameter to increase the number of free buffers available on the system. NOTE: The SGA must fit into contiguous chunks of real memory, so if the buffer cache is enlarged you must ensure there is enough contiguos memory available on the system to service the increase.

      3. redo allocation AND redo copy latches : These latches control the access to the redo log buffer. When a process requires writing to the redo log buffer, one of these latches is to be acquired by the process. If the size of the redo log information written to the redo log buffer is less thant the log_small_entry_max_size parameter, the process will use the redo allocation latch. If the size is greater than this value, the process is copied using the redo copy latch .
      4. A quick way to check whether there is any contention on the redo log buffer is to check where there are any waits associated with writing to the redo log buffer. This can be done using the V$sysstat view :

         
        select name,value
        from v$sysstat
        where name = 'redo log space requests'
        

        The size of the redo log buffer will have to be increased if the number of waits is too high.

        Contention for the redo allocation latch : The contention for the redo allocation latch can be reduced on a multi-CPU system by forcing the process to use the redo copy latch instead. Because there can be multiple redo copy latches, the copy will be done more efficiently. The number of redo copy latches is defined by the parameter LOG_SIMULTANEOUS_COPIES The maximum number of available latches on the system is double the number of CPUs.For a single CPU system, this value is 0, and the redo allocation latch will be used. If there is a contention for the redo allocation latch , the value of log_small_entry_max_size can be decreased from its current value so thar redo copy latch is used.

        Contention of the redo copy latch : If the system is facing contention for the redo copy latch , it can be decreased by either increasing the value of log_small_entry_max_size ( so that the redo allocation latch is used) or increasing the value of log_simultaneous_copies (so that it increases the number of redo copy latches available).

        The init.ora parameter log_entry_prebuild_threshold can be increased so that the data that is written to the redo log buffer is grouped and written out. By increasing the parameter, a number of write operations can be grouped so that they can be written out in one operation, thereby reducing requests for these latches and thus contention.

      5. Library Cache Latch : This latch is primarly concerned with the control of access to the libarary cache. The library cache inclusdes the Shared SQL area, private SQL areas, PL/SQL procedure packages, and other control structures.Shared SQL area contains SQLs that are shared among multiple sessions. By increasing the sharing of these SQLs, contention to this latch can be avoided. Contention for this latch occurs when there is a lot of demand for space in the library cache. Very high parsing on the system and heavy demand to open a new cursor because of low sharing among processes are some of the common causes of contention on this latch.
      6. Contention for this latch can be avoided by using code that can be shared by multiple sessions. This can be done by typing the code with the same conventions (all capitals for DML key words, or just the first letter a capital ) to the parsing engine even a one lower-upper case letter will generate a different hah value. Even putting more spaces in a select statement causes the hash to be different.

        Using bind variables: Using bind variables prevents multiple copies of the same select statement from being shared in the same pool.e.g:

          select sal from employee where emp_if := emp_id; 
        

        Pinning frequently used objects like procedures and packages. The advantage is that these objects will never be flushed out of the shared pool.These objects can be identified by :

                      select name, executions
        	      from V$db_object_cache
        	      where executions > <threshold value>
        	      order by 2 desc;
        

        And in order to pin these objects in the shared pool.

                      dbms_share_pool.keep('object name','P');
        

        To check the object in the shared pool that are not pinned :

         
                      select name,type,kept,sharable_mem
        	      From v$db_object_cache
        	      Where kept = 'NO'
        	      Order by shareable_mem desc;