Study Brief: Oracle8 Performance Tuning
by Michael Ritacco, Oraclenotes.com

An Overview of Tuning

Tuning a Oracle database is a iterative process that should begin with a quantifiable objective. Without a clear and measurable tuning goal your tuning efforts my be unnoticeable or even futile.

Measurable Tuning Goals

  • Database Availability
  • Response Times
  • Database Hit Percentages
  • Memory Utilization

The universal tuning goals are: Have SQL statements access the least amount of Oracle blocks possible, when a Oracle block is needed it is cached in memory, users share as much application code as possible, when I/O is necessary it is as fast as possible, users never have to wait for resources held by other users, and backups and other administration is completed quickly and unnoticed.

Tuning Steps

  • Tune the Design
  • Tune the Application
  • Tune Memory
  • Tune I/O
  • Tune Contention
  • Tune Operating System

Oracle Alert, Trace Files and Events

Alert Log

The alert log is a file that contains a chronological log of messages and errors. You can check the Alert log to detect internal errors (ORA 600) and block corruption errors (ORA-1578), Monitor database operations like CREATE DATABASE, STARTUP, SHUTDOWN, RECOVER, ARCHIVELOG, it also lists the values of all non-default initalization parameters at instance startup. The init.ora parameter BACKGROUND_DUMP_DEST sets the location for the alert log file. Since there is no parameter to control the size of the alert log it must be manually controled otherwise it can get as large as disk space allows.

The init.ora parameter BACKGROUND_DUMP_DEST also controls the location of the Background Processes Trace Files.


User Trace Files

A user trace file contains statistics for traced SQL statements for the session, and is very useful for SQL tuning. Oracle creates user trace files on a per server process basis either at the Session or Instance Level. To enable instance level tracing set the init.ora parameter SQL_TRACE=TRUE. For session level use the SET_SQL_TRACE_IN_SESSION procedure or ALTER SESSION SET sql_trace=TRUE. The init.ora parameter USER_DUMP_DEST defines the location where trace files will be created, and the parameter MAX_DUMP_FILE_SIZE specifies in O/S blocks the size limit of trace files.

Oracle Wait Events

  • V$EVENT_NAME
    • free buffer wait
    • latch free
    • buffer busy wait
    • db file sequential read
    • db scattered read
    • db file parallel read
    • undo segment tx slot
    • undo segment extension

Views for Event Statistics

  • V$SYSTEM_EVENT
  • V$SESSION_EVENT
  • V$SESSION_WAIT:
    Setting the init.ora parameter TIMED_STATISTICS=TRUE will retrieve the values in the WAIT_TIME column. Although a small amount of overhead is generated by the TIMED_STATISTICS parameter it may be good idea (in some cases) to keep it set to TRUE indefinitely.
    WAIT_TIME
    Value
    Explanation
    > 0
    the sessions last wait time.
    = 0
    session is currently waiting.
    = -1
    value is less then 1/100 of a sec.
    = -2
    system cannot provide timing info.

Utilities & Dynamic Performance Views

The V$ performace views are based on the information held in the X$ tables, owned by SYS, populated at instance startup, cleared at shutdown, and are first available when the instance is in either NOMOUNT or MOUNT. To get a full listing of all available V$ views you can query V$FIXED_TABLE.

If you require the V$ views to show timing information you must set TIMED_STATISTICS= TRUE.

Session Related Views

  • V$LOCK
  • V$SESSTAT
  • V$SESSION_EVENT
  • V$SESSION_WAIT
  • V$SESSION

Database/ Instance Related Views

  • V$WAITSTAT
  • V$PROCESS
  • V$SYSTEM_EVENT

Memory Related Views

  • V$SYSSTAT
  • V$ROWCACHE
  • V$LIBRARYCACHE
  • V$BUFFER_POOL_STATISTICS

I/O Related Views

  • V$FILESTAT

Contention Related Views

  • V$LATCH
  • V$ROLLSTAT
  • V$WAITSTAT

General System Views

  • V$SYSTEM_EVENT
  • V$SGASTAT
  • V$EVENT_NAME
  • V$STATNAME

To collect performance statistics for an instance over a defined period of time use the ULTBSTAT.SQL and UTLESTAT.SQL scripts. Prior to running these scripts you must set TIMED_STATISTICS=TRUE either by setting it in the init.ora or dynamically with the command: ALTER SYSTEM SET TIMED_STATISTICS=TRUE.

NOTE:
These scripts connect as SYSDBA and will create temporary tables/views in the SYSTEM tablespace. It would be a good idea to change SYS's default tablespace before running ULTBSTAT.SQL and UTLESTAT.SQL. Don't forget to change it back to SYSTEM when your done!

Also if the instance is shut down before both ULTBSTAT.SQL and UTLESTAT.SQL have had a chance to complete, you must run both scripts again.

The ULTBSTAT.SQL script is responsible for creating the BEGIN and END tables, and for collecting the inital statisitic from the V$ views.

At the end of specified time-period UTLESTAT.SQL is run to collect and populate the statistics in the END tables. The script then creates DIFFERENCE tables to hold the results of the BEGIN table statistics [subtracted] from the END table statistics. From the DIFFERENCE tables a file REPORT.TXT is generated.

REPORT.TXT Contains:

  • Time period the report covers
  • System Statistics
  • Wait Event Statistics
  • Library Cache Statistics
  • Dictionary Cache Statistics
  • Rollback Statistics
  • I/O Statistics
  • Latch Statistics
  • Buffer Busy Wait Statistics

Oracle provides additonal (GUI) applications or "Oracle Packs" that extend the funtions of OEM to include monitoring and tuning of performace related issues.

  • Tuning Pack
    • Oracle Expert: makes recommendations, and writes the scripts to configure and tune your database.
    • Tablespace Manager: can coalesce and defrag your tablespaces.
    • SQL Analyze: For tuning application SQL.

  • Diagnostics Pack
    • Performance Manager:shows real-time graphs of the most commonly used performance categories: memory, contention, I/O, instance. The information is based on the same V$ views we discussed before.
    • Lock Manager: shows all locks for all current sessions, sessions blocking access, and allows you to kill blocking sessions.
    • TopSessions: monitors the top resource intensive user sessions.
    • Oracle Trace: monitors performance of user applications.

Tuning Considerations for Applications

Online Transaction Processing (OLTP):
Describes databases that are continuously growing via intensive INSERT and UPDATEs. Tuning aspects should focus on speed, availability, quick recovery time, and high concurrency. A good example of a OLTP system would be a Bank ATM machine.

Decision Support System (DSS):
Describes databases that are used to analyze large amounts of data into understandable reports. Management utilizes these reports to set the strategies and direction for their organization. DSS systems usually perform queries on large amounts of data, and heavily use full table scans in the process.

Multipurpose Databases (Hybrid)
Describe databases that rely on multiple parameter files to allow the same database to perform the functions of both OTLP (usually by day) and DSS (usually by night). This is accomplished by restarting the database with the appropriate parameter file at the specified time.

B-Tree Indexes

  • Best used on high-cardinality columns
  • Feature Row-level locking
  • Bad with OR queries
  • Updates are inexpensive
  • require more storage then Bitmap indexes
  • Good for OLTP

Bitmap Indexes

  • Best used on low-cardinality columns
  • Segment level locking
  • Good for OR queries
  • Require less storage then B-Tree
  • Good in DSS/Data Warehouse environments

Reverse Key Indexes

  • Great for use with Sequences
  • Not good for statements that specify ranges

SQL Tuning

Rule-Based optimizer uses a ranking system that is a syntax driven to determine access paths.

Cost-Based optimizer examines each statement identifying all possible access paths to the data then calculates the resource cost of each access path choosing the least expensive.

Star Queries are automatically used by the cost-based optimizer if the fact table, lookup tables, and concatentated index exist.

Hash Joins are used in data warehouse where one very large table is joined to a smaller table.

Explain Plan

SQLTRACE

TKPROF is used to format a SQLTRACE session into a readable report.

Autotrace

Operating System Tuning

Swapping: the memory space of the entire process is removed from memory and put on disk.

Paging: a block of memory is removed from real memory and placed in virtual memory (disk).

PRE_PAGE_SGA=TRUE will cause Oracle to load all SGA pages into memory at instance startup.

Tuning the Shared Pool

The SGA is composed of the Library cache, Data dictionary cache (row cache), and the user global area (UGA) if MTS is used.

Remember that tuning the shared pool is more important then the buffer cache because a miss in the shared pool is more costly. Your tuning focus should be placed on the library cache due to the LRU algorithm of the data dictionary cache. Basically, the data dictionary holds data in memory longer so an acceptable library cache hit ratio will guarantee a properly tuned data dictionary cache.

The SHARED_POOL_SIZE parameter controls the overall size of the shared pool.

Library Cache

  • Holds parsed SQL statements and PL/SQL blocks, allowing statements already in the cache to be reused by other users without Oracle having to reparse.
  • Bind variables (in an OLTP system) encourage SQL reuse rather then constants.
  • Use smaller PL/SQL packages instead of large anonymous blocks.
  • Reduce fragmentation by pinning large objects at startup.
  • V$LIBRARYCACHE: gives statistics on the library cache. The GETHITRATIO column should be in the high 90's. RELOADS shows the number of times an object was dropped from the library cache due to lack of space. PINS shows the number of executions of a SQL statement/procedure. The ratio of Reloads to Pins should be 1% or less (ideal value is 0), if higher increase shared pool size.
  • V$DB_OBJECT_CACHE shows the amount of shareable memory used by a cached object.

Data Dictionary Cache

  • Holds definitions of dictionary objects
  • V$ROWCACHE shows the hit/miss ratio for the dictionary cache.
  • The gethitratio should be less then 15%, if higher increase shared pool size.
  • If report.txt shows a high GET_MISS/GET_REQ ratio, increase shared pool size.
  • Default of 50 open cursors per user.

User Global Area (UGA)

  • Use V$SESSTAT and V$STATNAME to show space usage of all MTS users.
  • Use V$MYSTAT for the space usage of your connection.

Each user will need 250 bytes in the shared pool per open cursor.

SHARED_POOL_RESERVED_SIZE: specifies how much of the shared pool you want to set aside for large objects (the reserved list).

SHARED_POOL_RESERVED_MIN_ALLOC: Objects smaller that the value specified by this init.ora parameter will not be allowed on the reserved list.

Use the DBMS_SHARED_POOL.KEEP/UNKEEP procedure to either pin or remove pinned objects in the shared pool.

Tuning the Buffer Cache

The buffer cache holds copies of data blocks that can be shared by all users. The init.ora parameter DB_BLOCK_BUFFERS specifies the number of blocks for the buffer cache.

To find the size of the buffer cache:(DB_BLOCK_BUFFERS*DB_BLOCK_SIZE). Remember that DB_BLOCK_SIZE is specified at database creation and cannot be changed without recreating the database.

The three states of Buffer Cache Blocks:

  • Free Buffers: have same image on disk and in memory.
  • Dirty Blocks: must be written to disk before reuse, have different image in memory then on disk.
  • Pinned Buffers: blocks that are currently being accessed.

DBWR is the background process that writes dirty block buffers to the data files. DBWR is signaled to write for the following events: Dirty list exceeds its Size Threshold, a server process search threshold was exceeded, the LGWR signals a checkpoint, or a DBWR 3 second time-out occured.

Increase the buffer cache when:

  • The buffer cache hit ratio is less than 90%.
  • There is enough memory for other processes, no undue page faulting.
  • The previous increase of DB_BLOCK_BUFFERS was effective.
  • If the wait statistic (V$SYSSTAT) for free buffer inspected (shows the number of buffers skipped to find a free buffer) is high or increasing in value.

Tools

  • V$SYSSTAT and V$SESSTAT are used to calculate the cache hit ratio.
  • V$SYSSTAT shows the buffer cache hit ratio.

ADDING BUFFERS

  • Set init.ora parameter DB_BLOCK_LRU_EXTENDED_STATISTICS to the number of buffers you plan to add.
  • Then query V$RECENT_BUCKET after running the database for a period of time to get the estimated performance statistics of a larger buffer cache.
  • The COUNT column will show the additional (hits) on the buffers.

REMOVING BUFFERS

  • First shutdown the instance and set initialization parameter DB_BLOCK_LRU_STATISTICS=TRUE.
  • Run the database through a normal period of operation.
  • Query V$CURRENT_BUCKET to get an estimate of the hit ratio with a smaller buffer cache.

By using Multiple Buffer Pools a DBA may be able to increase performance of the buffer cache by finely controling how objects are accessed. Oracle8 supports three different buffer pools, but remember that the total for all pools must equal the value of DB_BLOCK_BUFFERS.

  • RECYCLE:Flushes blocks as soon as the transaction completes. Size by finding out the number of data blocks used by each object.
  • KEEP: holds all objects in memory for as long as possible.
  • DEFAULT: the standard buffer pool that will always exist, blocks will be stored here by default.

To enable the KEEP or RECYCLE buffer pools you must define the init.ora parameter BUFFER_POOL_KEEP/RECYCLE.

  • Allocate the number of buffers to each pool (minimum 50 buffers for each assigned LRU latch).
  • Allocate the number of LRU latches to each pool from DB_BLOCK_LRU_LATCHES (number of latches for instance).
  • Example: BUFFER_POOL_RECYCLE (buffers:2000, lru_latches:2)

V$CACHE is used to monitor buffer pool blocks by object when using Oracle parallel server. The view is created with the catparr.sql script.

Use the CACHE clause when creating lookup tables used by many users. Minimize buffer cache crowding by setting the CACHE_SIZE_THRESHOLD parameter to limit the number of blocks cached for each table. Default is 1/10th the value of DB_BLOCK_BUFFERS.

Look for a buffer busy waits event, in V$SYSTEM_EVENT or V$SESSION_WAIT to find out if there have been waits for buffers.

Tuning the Redo Log Buffer

The redo log buffer is a circular buffer. The LOG_BUFFER parameter controls the size of the redo log buffer and the size must be a multiple of the OS block size.

V$SESSION_WAIT shows the "log buffer space" event which indicates waiting for space in the redo log buffer due to lack of log switching.

V$SYSTEM_EVENT shows the event "log file switch completion" which identifies the waits because of log switches.

The ratio of redo log requests to redo log entries should not be more then
1 in 5000.

Reducing Redo log entries

  • SQL*Loader: On database in NOARCHIVELOG mode, no redo is generated when direct path loading is used.
  • SQL*Loader: Use the NOLOGGING attribute to disable logging on a database in ARCHIVELOG mode.
  • Direct Load: use the NOLOGGING option.
  • SQL statements: Use the NOLOGGING attribute when creating or altering objects. Example: CREATE TABLE....AS SELECT

Two kinds of Redo Log Latches regulate processes that need to write to the redo log buffer. The redo allocation latch controls the allocation of space for redo log buffer entries. To use space in the redo log buffer a user must first posses the redo allocation latch. The redo copy latch is used in multi-CPU environments only. Its purpose is to write data that exceeds a specified size parameter for the redo allocation latch. This allows multiple CPU machines to have multiple processes copying entries in the redo log buffer concurrently.

Database Confiquration and I/O Issues

Your database should have no less then six different types of tablespaces:

  • System
  • Indexes
  • Temporary
  • Rollback segments
  • Tables
  • Very large objects

The SYSTEM tablespace should only contain data dictionary, triggers, and packages.

DB_FILE_MULTIBLOCK_READ_COUNT parameter determines the maximum number of database blocks read in a single I/O.

DB_BLOCK_CHECKPOINT_BATCH defines the maximum number of blocks DBWR can write in a single batch during a checkpoint. An increase in this value can speed up checkpoint times, but a large value can give a poor response time.

Setting LOG_CHECKPOINTS_TO_ALERT=TRUE enters beginning and ending times of checkpoints in the alert.log.

V$FILESTAT: is used to monitor database I/O.

I/O slaves can be used with DBWR, LGWR, ARCH, and backup processes to simulate asynchronous I/O. Use the following parameters to spawn the I/O slaves:

  • DBWR_IO_SLAVES
  • LWGR_IO_SLAVES
  • ARCH_IO_SLAVES
  • BACKUP_DISK_IO_SLAVES
  • BACKUP_TAPE_IO_SLAVES

Turn asynchronous I/O on or off using DISK_ASYNCH_IO or TAPE_ASYNCH_IO.

Multiple DBWR processes allow you to parallelize the gathering of data to the writing of data with I/O slaves. To specify multiple DBWR processes use:

  • DB_WRITER_PROCESSES (up to 10 processes, DBW0-DBW9)
  • DB_FILE_SIMULTANEOUS_WRITES specifies the number of simultaneous write by DBWR.

Set LOG_CHECKPOINT_INTERVAL to a number of OS blocks that is larger then the size of your redo log files. This ensure that checkpoints only occur at log switches.

LOG_CHECKPOINT_BATCH sets the time between checkpoints. Set this value to a value that facilitates the checkpoint completing before the next log switch.

 

Using Oracle Blocks Efficiently

Units of Storage

  • Blocks: smallest unit of space. Consists of one or more contiguous OS blocks.
  • Extents: a logical unit of storage consisting of a number of contiguous data blocks.
  • Segments: a set of extents that hold all data for a specific logical structure: a table,a index, etc.

Set extent size to a multiple of 5*DB_FILE_MULTIBLOCK_READ_COUNT

DB_BLOCK_SIZE: can only be set at database creation and determines the minimum I/O unit for data file reads. OLTP favors small blocks, DSS favors large blocks.

PCTFREE: sets the minimum percentage of data blocks to be reserved for future updates to rows that already exist in the block.

PCTUSED: sets the minimum percentage of used block space before it is put back on the free list.

Any changes made to PCTFREE or PCTUSED on exisiting tables will only affect future DML statements on that table.

Chaining (PCTUSED) is when a row is too large to fit into a single empty block. Oracle then stores the data in a chain of multiple data blocks.

Migration (PCTFREE) is caused by an update statement that increases the size of the row so that it no longer fits in its data block. Oracle finds a new data block with enough space to store the row, however a pointer remains in the original location pointing to the new location. This causes additional I/O because Oracle must now check two block locations.

The high water mark can be changed by the TRUNCATE command.

Optimizing Sorts

The following operations require Oracle to perform a sort: Creating an Index, ORDER BY, GROUP BY, DISTINCT, UNION, INTERSECT and MINUS.

SORT_AREA_SIZE is the init.ora paramter that allocates the sort space to the PGA in a dedicated server connection or the UGA in a MTS environment.

Sorts are performed in memory if the size of the sort is smaller then the value of SORT_AREA_SIZE. Sort operations that are larger will be split into a number of smaller "sort runs", written out to temporary segments on disk while the remainder of the "sort runs" are completed. The completed "sort runs" are then finally merged together and returned to the user.

SORT_AREA_RETAINED_SIZE parameter specifies the size the sort area with shrink to after a sort. The default value is set to equal SORT_AREA_SIZE.

SORT_AREA_SIZE = SORT_AREA_RETAINED_SIZE unless you are using MTS.

When a process needs sort space it looks for available extents in the SEP. The Temporary Sort segment is created by the first sort and dropped when the database is closed.

To avoid sorts use NOSORT clause when creating Indexes, use UNION ALL in place of UNION.

V$SORT_USAGE: displays temporary segment information, and the users using the temporary segments.

V$SORT_SEGMENT: shows information about every sort segment all of Temporary tablespaces. The following columns are found in this view:

  • CURRENT_USERS: Number of active users.
  • TOTAL_EXTENTS:Total number of extents.
  • USED_EXTENTS: Extents currently used by sorts.
  • EXTENT_HITS: Number of times an unused extent was found in the pool.

V$SYSSTAT: shows the number of all sorts (memory, disk, rows)

SORT RATIOS

  • The ratio of disk sorts to memory sorts should be <5%
  • If the ratio is higher increase the value of SORT_AREA_SIZE

When large sorts are necesary sort runs can be written directly to disk by setting the SORT_DIRECT_WRITES to TRUE/AUTO. When using direct writes the buffer cache is bypassed and each sort uses its own memory buffers. The size and number of sort memory buffers can be controlled by using the SORT_WRITE_BUFFERS and SORT_WRITE_BUFFERS_SIZE parameters.

SORT_WRITE_BUFFERS sets the number of buffers allocated to a direct write sort. The value can be between 2 and 8.

SORT_WRITE_BUFFERS_SIZE sets the size of the allocated buffers for a direct write sort. The values can be 32KB to 64 KB.

Each serial sort will need the following memory:
(SORT_WRITE_BUFFERS * SORT_WRITE_BUFFERS_SIZE) + SORT_AREA_SIZE

Each parallel sort will need the following memory:
(SORT_WRITE_BUFFERS * SORT_WRITE_BUFFERS_SIZE) + SORT_AREA_SIZE) *2* (Degree of parallelism)

Note:
Memory allocated to direct write buffers should be < 1/10th the amount allocated to SORT_AREA_SIZE.

Temporary Tablespaces

  • Set PCTINCREASE to 0
  • INITIAL and NEXT should be multiples of SORT_AREA_SIZE.

Join V$SESSION and V$SORT_USAGE to get information on current active disk sorts. Remember that the USER column of V$SORT_USAGE is you, the user performing the sort is the username from V$SESSION.

Tuning Rollback Segments

Rollback segments are used for transaction rollback, transaction recovery, read consistency.

Rollback Segment Header Contention

  • The ratio of (sum) WAITS to the (sum) of GETS should be 0. Any non zero value in the WAITS column of V$ROLLSTAT, UNDO HEADER column of V$WAITSTAT, or "undo segment tx slot" event of V$SYSTEM_EVENT is an indication of contention.
  • If the ratio of the sum of WAITS to the sum of GETS is greater then 5%, create more rollback segments.
  • If the ratio of waits for any class is greater then 1%, create more rollback segments.

A transaction table is in the header each rollback segment.

OLTP systems should have (1) one rollback segmetn for every (4) four users. do not exceed 50 rollback segments.

The "ORA-01555: snapshot too old (rollback segment too small)" error, means a rollback segment is too small for a given transaction to complete and that it has overwritten itself. You should increase the number of rollback segments or assign the transaction a large rollback segment.

For large batch jobs assign a large rollback segment with the first command using:
SET TRANSACTION USE ROLLBACK SEGMENT "biggie_size_rbs" ;

Very large transactions may fail if:

  • The rollback segment has reached MAXEXTENTS.
  • There is no space left in the tablespace for the rollback segment to expand.

All rollback segments should be the same size. Set Next=INITIAL and PCTINCREASE=0. To reduce the likelihood of dynamic extent extension set MINEXTENTS=20. The OPTIMAL setting of rollback segments should be set to the value needed for normal usage.

DELETEs use the most rollback space followed by UPDATEs (size depends on number of columns), then INSERTs. LOBs do not use rollback segment space for changes.

V$TRANSACTION shows the current transactions using rollback segments and the amount of rollback information a transaction is creating.

Monitoring and Detecting Lock Contention

The inti.ora parameter ROW_LOCKING controls the level of locking.

Locks are held until a transaction terminates, COMMIT, or a ROLLBACK is issued. PMON is resposible for removing and cleaning up locks.

DML locks at the row level.

Oracle maintains all locks as enqueues. The enqueues keep track of users waiting for locks, the lock mode needed by the user, and the order of lock requests(first come, first served).

Causes of Lock Contention

  • Applications are using unnecessarily high locking levels.
  • Transactions are longer then necessary.
  • Users are not committing changes.
  • Other applications are imposing higher locking levels.

To kill user sessions that are holding a locks need by other users, query V$SESSION and get the SERIAL# and SID (session ID). Then, issue the command:ALTER SYSTEM KILL SESSION serial #, sid.

V$LOCK displays detailed information about locks. V$LOCK_OBJECT can also be used to monitor lock conditions.

DEADLOCKS

  • A deadlock is recorded in a trace file specified by USER_DUMP_DEST.
  • When a lock is deadlock is detected (ORA-00060) by the Oracle server the statement that detected the deadlock is rolled back. The remainder of the transaction must be manualy rolled back.

Contention Issues

Contention exists when multiple server processes are attempting to acquire the same memory latches. Note: You should tune the buffer cache and I/O prior to tuning latches.

Latch Types:
Found in V$LATCH

  • IMMEDIATE
  • WILLING-TO-WAIT

The DBA can tune the following areas:

  • Redo copy latch: used when writing to the redo log buffer.
  • Redo allocation latch: after the redo copy latch has been acquired, the redo allocation latch is used to allocate space in the redo log buffer for writing. When the space has been allocated the latch is released. Then the redo copy latch is used to write to the redo log buffer.

    - LOG_SMALL_ENTRY_MAX_SIZE: parameter that specifies the size of the largest copy to the log buffers without using the redo copy latch.

    - LOG_SIMULANEOUS_COPIES: parameter that sets the number of redo copy latches for the instance. Maximum is 2*(Number of CPUs)

    - Latch contention should be less than 1%.
  • LRU latch: LRU latches regulate the buffer cache LRU lists. Each LRU latch controls at least 50 buffers.

    - DB_BLOCK_LRU_LATCHES: init.ora parameter that sets the number of LRU latches for the instance.

    - V$LATCH
    - V$LATCHNAME
  • Free list: are used to determine what blocks are available for inserts in an object.

    - If the hit ratio of LRU latches is less then 99% increase the number of latches with DB_BLOCK_LRU_LATCHES. NOTE: To find the optimum setting take the (total number of block buffers divided by 50)

    - DBA_EXTENTS     and DBA_SEGMENTS are used to identify objects which need an increased number of free lists.     NOTE: free lists cannot be added dynamically therefore the object must be dropped, and recreated using the FREELISTS keyword to add free lists. Remember the number of CPUs is also a factor.

    - V$SESSION_WAIT, V$WAITSTAT, V$SYSTEM_EVENT are used to diagnose free list contention.
    - Query V$SESSION_WAIT and V$WAITSTAT to determine if free list contention exists.

Tuning with Oracle Expert

Oracle Expert (a component of the Tuning Pack extension of OEM) provides automation of Routine tuning, Focused tuning, and What-if performance tuning.

For Oracle Expert to operate it must have the SELECT ANY TABLE privilege for the database that stores the Oracle Expert repository.

Oracle Expert performs its activities in what is called a tuning session. The tuning session defines the scope of tuning and contains both collected and analyzed data.

The three scopes for tuning sessions:

  • Application level: examines SQL statements and access methods determinine what indexes are needed, and generates the appropriate SQL to make the right changes.
  • Instance level: examines SGA, sort, parallel parameters, operating system and I/O parameters.
  • Structure level: examines storage parameters and placement of data files.

Oracle Expert cannot collect environmental data. You must enter it manually or import it from an .XDL file.

Types of database class data:

  • Database: data gathered from the X$ tables and V$ views.
  • Instance: SGA, Sort, I/O, OS, Parallel
  • Schema: tables, columns, indexes, and constraints.
  • Environment: system data, server attributes like RAM, CPUs, disks, page size.
  • Workload: describes the "normal" database transaction (nature and frequency). This data can be gathered by Oracle Trace then imported.
  • Rules: used to tell Oracle Expert how to analyze collected data. Modify rules to limit, adjust, or override a given recommendation.
  • Control Parameters: are used to set the workload class to: OLTP, DSS, or BATCH. This setting helps Oracle Expert optimize its recommendations to your particular environment.

Oracle Expert Reports

  • Session Data Report details all information collected for the tuning session. The Analysis Report describes the rationale for all recommendations.
  • Implementation Files:assist in making the appropriate changes to improve database performance. Just replace your init.ora parameter values with the ones generated by Oracle Expert.

  

About Oraclenotes.com  |  Advertise  |  Contribute   |  Disclaimer 
© Copyright 1999 - 2004 Oraclenotes.com. All Rights Reserved.
Use of this website signifies your agreement to the Terms of Use.
Not affliated or endorced by Oracle Corporation.