Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Oracle Tuning - Using BSTAT and ESTAT

Oracle Tuning - Using BSTAT and ESTAT

From: Daniel Druker <ddruker_at_us.oracle.com>
Date: Mon, 18 May 1992 04:21:55 GMT
Message-ID: <1992May18.042155.21712@oracle.us.oracle.com>

In tuning Oracle databases, I've found these tips invaluable. First read the advanced performance tuning guide to get some background, then use the information below to help interpret the results. If you follow these directions, you may be able to achieve major performance improvements.

This document is one of several hundred bulletins provided by Oracle Customer support. Supported customers can call them and get faxes or mail of the bulletins at no charge. Reprinted without permission. - Dan  

Database Performance Tuning with BSTAT/ESTAT Kim Powell
Oracle Corporation  

Abstract
One of the biggest responsibilities of a DBA is to ensure that the Oracle database runs at its best. Oracle RDBMS V6.0 is highly tunable. For this reason, the database can be monitored and adjusted to increase its performance. This presentation discusses many undocumented issues of how to make use of BSTAT and ESTAT SQL scripts to improve performance within the Oracle database system. The following type of contention will be discussed in detail: File I/O, rollback segments, redo log, latch, data block, and LRU chain.  

Introduction
Bstat/Estat is a set of sql scripts located under your RDBMS directory that are useful for capturing a snapshot of system wide database performance statistics.  

Bstat.sql creates a set of tables and views in your sys account which contain a beginning snapshot of database performance statistics. The table names which are listed below contain the word "begin" to indicate beginning statistics:  

        View/Table Name          Description
        ---------------          -----------
        stats$begin_stats        General System stats from v$sysstat
        stats$file_view          View of File I/O statistics
        stats$begin_file         Table of File I/O Stats from stats$file_view
        stats$begin_latch        Latch statistics from from v$latch
        stats$begin_roll         Rollback segment statistics from v$rollstat
        stats$begin_kqrst        Dictionary Cache Stats from table x$kqrst
        stats$dates              Table containing beginning vdate and time
 

Estat.sql creates a set of tables in your sys account which contain a ending snapshot of the database performance statistics. The table names, which are listed below, contain the word "end" to indicate ending statistics.  

        Table Name              Description              
        ----------              -----------
        stats$end_stats         General System stats v$sysstat
        stats$end_file          Table of File I/O Stats from stats$file_view
        stats$end_latc          Latch Statistics from v$latch
        stats$end_roll          Rollback Segment Stats from v$rollstat
        stats$end_kqrst         Dictionary Cache Stats from x$kqrst
 

Additionally, estat creates a set of tables in your sys account which contain the difference between the beginning statistics and the ending statistics. The table names are listed below:  

        Table Name              Description               
        ----------              -----------
        stats$stats             General System Statistics
        stats$file              File I/O Statistics
        stats$latches           Latching Statistics
        stats$roll              Rollback Segment Statistics
        stats$kqrst             Dictionary Cache Statistics
        stats$dates             Table containing ending date and time
 

Finally, estat creates a report in the current directory with the database performance statistics. The report is divided into the following sections:  

        Section                        
        --------                
        System Wide Stat Totals 
        File I/O Stats
        Latch Statistics
        Rollback Segment Stats
        Dictionary Cache Stats
        Init.ora Parameters
        Date/Time of bstat/estat
 

The following steps should be followed to execute bstat/estat:  

        o  Determine database activity to be monitored
        o  Move to the bstat/estat.sql directory
        o  Logon to SQL*DBA
        o  Issue the command @bstat
        o  Run application to be monitored
        o  Issue the command @estat
 

Bstat/Estat should only be run after the database has been running for a period of time. If bstat/estat is run immediately after database startup, the buffer cache will not be loaded and the statistics generated will not be valid for database performance analysis.  

If the database is shutdown in the middle of executing bstat/estat the statistics are no longer valid since the V$ tables are initialized at shutdown and startup. If negative values are present for statistics other than "Current_" statistics, the database has been shutdown and started during the execution of bstat/estat.  

In order for all of the statistics to be populated the init.ora parameter TIMED_STATISTICS must be set to true. Setting this parameter causes slight performance degradation but is needed for the "_Time_" statistics.  

Depending on the type of applications run it may be beneficial to start the database with different init.ora files. To determine if this is beneficial run bstat/estat for different applications. Included below are examples of when bstat/estat may be executed:  

        o Activity:     Several batch jobs are run every evening
          Execution:    Execute Bstat before the batch jobs and execute
                        estat after the batch jobs are complete
        o Activity:     Maximum processes access database from 1 - 4pm
          Execution:    Execute bstat at 12:59pm and estat at 4:01pm
System Wide Statistic Totals
System Wide Statistic Totals are generated from the table v$sysstat which contains general database system statistics. Included below is the heading for this section of the report:  
Statistic                       Total   Per Trans
------------------------------- ------- -----------
 
        Column Name        Description
        -----------        -----------
        Statistic          Name of the system wide statistic
        Total              Total number of statistic operations
        Per Trans          Total number of statistic operations/user commits
 

Outlined below are several of the system wide statistics which can be used to analyze database performance:  

Buffer Busy Waits reflects contention for buffers containing undo blocks, undo segment headers, data blocks, and segment headers. If the total buffer busy waits/logical reads (defined below) > 10% , there is buffer contention and the following query should be run to determine if there is rollback or data block contention:  

        Select class, sum(count) total_waits
        from sys.v$waitstat
        where operation = 'buffer busy waits'
        and class in ('undo block, 'undo segment header', 'data block',
        'segment header') group by class;
 
 

If total waits is high for undo block and undo segment header classes, additional rollback segments should be created to reduce rollback segment block buffer contention.  

If the total waits is high for the data block and segment header classes, additional free lists should be created to reduce data block contention. To add additional free lists increase the init.ora parameter FREE_LIST_PROC and drop and recreate the tables with contention. It is not recommended that this parameter be increased higher than needed for avoiding contention.  

Cluster Key Scan Block Gets is the number of cluster blocks accessed. Cluster Key Scans is the number of scans processed on cluster blocks. If the ratio of Cluster Key Scan Block Gets to Cluster Key Scans is greater than one, the rows for one cluster key are stored in multiple data blocks and the cluster should be analyzed for row chaining.  

The Size parameter specified during the Create Cluster command determines the number of cluster keys per block, with the default being one. If this parameter is not specified correctly rows for one cluster key may not fit into one data block or there may be wasted space in the data block. If all of the data for one cluster key does not fit in one block, additional I/O mush occur to access the data. Reference the DBA Guide to determine how to calculate the SIZE parameter for the Create Cluster command.  

Cumulative Opened Cursors is the total open cursors which were opened during the execution of bstat/estat. A cursor is opened for each SQL statement which is parsed into a context area. Performance is improved if cursors are reused since the SQL statements will be not need to be reparsed. If a cursor will not be reused, it is best to close the cursor when the SQL statement completes. To optimize cursor usage in the Oracle Precompilers and SQL*Forms reference the corresponding sections in the Performance Tuning Guide.  

Consistent Gets is the number of blocks accessed in the buffer cache for queries without the select for update clause. DB block gets is the number of blocks accessed in the buffer cache for insert, update, delete, and select for update operations.  

        Consistent Gets + DB Block Gets = Logical Reads  

Physical Reads is the number of request for a block that caused a physical I/O. The ratio between logical reads and physical reads should be greater than 70%.  

        (Logical Reads / (Logical Reads + Physical Reads) = Hit Ratio  

If the hit ratio is lower than 70%, increase the init.ora parameter DB_BLOCK_BUFFERS which increases the number of data block buffers in the SGA. Reference the Performance Tuning chapter Tuning Memory Allocation to determine what DB_BLOCK_BUFFERS should be set to.  

DBWR Checkpoints is the number of checkpoints messages that were sent to DBWR. During a checkpoint there is a slight decrease in performance since data blocks are being written to disk which causes I/O. If the number of checkpoints is reduced, the performance of normal database operation improves but recovery after instance failure is slower.  

To reduce the number of checkpoints increase the init.ora parameter LOG_CHECKPOINT_INTERVAL. If this parameter is set to a size (bytes) larger than the size of the redo log, a checkpoint is performed during each log switch. To increase the number of checkpoints and decrease database recovery time decrease the init.ora parameter LOG_CHECKPOINT_INTERVAL.  

DBWR Free Low is the number of times DBWR is invoked because a user process found at least DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list. The dirty list holds modified (dirty) buffers that have not been written to disk. DBWR Free Needed is the number of times DBWR is invoked because a user process scanned DB_BLOCK_MAX_SCAN_CNT buffers without finding a free one. DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies the number of unavailable buffers a process should scan before signaling DBWR to write dirty buffers from the buffer cache to disk.  

If DBWR Free Needed is a non zero value the init.ora parameter DB_BLOCK_WRITE_BATCH should be increased. This parameter specifies the number of blocks which should be written to disk at one time. This parameter should only be increased until the statistics Write Complete Waits and Write Wait Time show growth. Write Complete Waits is the number of times a process waited for DBWR to write a current block before making a change to a buffer.  

Enqueue Timeouts indicates the number of times that an enqueue lock was requested and was not granted immediately. If this parameter is greater than zero, increase the init.ora parameter ENQUEUE_RESOURCES.  

Free Buffers Inspected is the number of buffers skipped in the buffer cache in order to find a free buffer. Free Buffer Requested is the total number of free buffer needed in order to create/load a block. Free Buffer Scans is the total number of time the LRU list was scanned to find a free buffer. Free Buffer Waits is the number of times processes needed a free buffer and one was not available. If Free Buffer Waits/Free Buffer Scans > 10%, increase init.ora DB_BLOCK_WRITE_BATCH.  

Recursive Calls occur because of cache misses and segment extension. In general if recursive calls is greater than 4 per process, the data dictionary cache (described below) should be optimized and segments should be rebuilt with storage clauses that have a few large extents. Segments include tables, indexes, rollback segment, and temporary segments.  

Redo Logs Space wait is the number of times that a process waits for space in the redo log buffer. If this parameter is a non-zero value, the init.ora parameter LOG_BUFFER should be increased to add additional space to the redo log buffer.  

Redo Chunk Allocations is the number of times an instance required more space in the current online redo log file. The init.ora parameter LOG_ALLOCATION determines the number of redo log blocks allocated each time additional space is requested. In single instance mode this parameter should be set to a value larger than the size of the redo logs.  

Redo Entries Linearized is the number of times a redo entry was pre-built before trying to obtain a latch in order to write into the redo buffer. If there is contention for the redo allocation latch or redo copy latch, the init.ora parameter REDO_ENTRY_PREBUILD_THRESHOLD should be increased to size greater than the average redo entry size.  

The following calculation can be performed to determine the avg size of a redo entry:  

        Redo Size / Redo Entries = Average Size of Redo Entries  

Redo Small Copies is the total number of redo entries with fewer bytes than specified by the init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE. These entries are written in the redo buffer under the protection of the redo allocation latch. If Redo Small Copies / Redo Entries is greater than 10%, the init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE should be decreased to a size smaller than the average redo entry size.  

Redo Writer Latching Time is the time needed by the process writing redo to obtain and release each copy latch. If this time is high, the timeouts for the redo allocation and copy latches should be analyzed.  

Sorts (disk) is the number of times that Oracle created a temporary segment to perform sorting on disk. Temporary segments are created when there is not enough room in memory to complete the sort. Sorts (Memory) is the number of times that a sort was executed in the memory location defined by the init.ora parameter SORT_AREA_SIZE. Sort (rows) is the total number of rows sorted.  

If Sorts (disk) is high, increase the init.ora parameter SORT_AREA_SIZE or modify the application to perform fewer sorts. Sorts are done for the following database operations: Index Creation, SQL statements with a GROUP BY or an ORDER BY clause, SQL statements with a DISTINCT operator, Sort merge JOINs, and UNION/INTERSECT/MINUS statements.  

Table Scans (long tables) is the total number of full table scans performed on tables with more than 5 db_blocks. If the number of full table scans is greater than 0 on a per transaction the application should be tuned to effectively use Oracle indexes. Indexes should be used on long tables if more than 10-20% of the rows from the table are returned.  

Table Scans (short tables) is the number of full table scans performed on tables with less than 4 db_blocks. It is optimal to perform full table scans on short tables rather than using indexes. Table Scans
(long tables) plus Table Scans (short tables) is equal to the number
of full table scans performed during the execution bstat/estat.  

Table Scan Blocks Gotten and Table Scan Rows Gotten respectively are the number of blocks and rows scanned during all full table scans. To determine on average the number of rows gotten per block for all full table scans:  

        Table Scan Rows Gotten/Table Scan Blocks Gotten  

To determine the approximate number of rows gotten for short and long table scans:  

        Table Scans (short) X 4 blocks = Blocks Scanned (short)  

        Table Scan Blocks Gotten -  Blocks Scanned (short) = Blocks
                Scanned (long)
 

Table Fetch by Rowid is the number of rows which were accessed by a rowid. This includes rows that were accessed using an index and rows that were accessed using the statement where rowid = 'xxxxxxxx.xxxx.xxxx'. Rowid is the fastest path to a row and should be used whenever applicable.  

Table Fetch by Continued Row is the number of rows which are continued or chained to another block. If this number is high additional I/O must be performed in order to read the entire row. Row chaining cannot be avoided for tables with long columns. To determine if a table has row chaining:  

        o  Logon on SQL*DBA session
        o  Select * from v$sesstat and note down Table Fetch by
                Continued Row parameter
        o  Perform index range scan against table in question
        o  Select * from v$sesstat and note down Table Fetch by
                Continued Row parameter and compare to previous value
 

User Calls is the number of times a call is made to the Kernel. Parse Count indicates the number of times a SQL statement was parsed. The number of calls to the Kernel should be reduced if possible. The performance Tuning Guide indicates how to setup array processing to reduce the number of calls to the Kernel. To calculate the number of calls to the Kernel per parse perform the following calculation:  

        Parse Count / User Calls = Avg calls per parse    

File I/O Statistics
File I/O statistics are generated from the view stats$file. The header for this section is included below:  

TABLE_SPACE                    FILE_NAME                                        
------------------------------ ------------------------------------------------ 
PHYS_READS PHYS_BLKS_RD PHYS_RD_TIME PHYS_WRITES PHYS_BLKS_WR PHYS_WRT_TIM ------------ ------------ ------------ ------------ ------------ --------------  
        Column            Description
        ----------        -----------
        Table_Space       Name of the data file's tablespace
        File_Name         Name of the data file
        Phys_Reads        Number of physical reads from the database file
        Phys_Blks_rd      Number of blocks read from the database file
        Phys_Rd_Time      Time to read blocks (Timed_statistic must be set)
        Phys_writes       Number of physical writes to the database file
        Phys_blks_wr      Number of physical blocks written to database file
        Phys_wrt_tim      Time to write blocks (Timed_statistic must be set)
 
 

File I/O should be spread evenly across multiple disk drives. In general redo logs should be located on disks that do not contain database files, tables should be located on different disks than their associated indexes, large tables and indexes should be striped across several disks, active database files files should not be located at opposite ends of the disk, and the most active database files should be located on the highest throughput disks.  

The init.ora parameter DB_FILE_MULTI_BLOCK_READ_COUNT can be set to increase the number of blocks read during a single read. Increasing this parameter reduces I/O when full table scans are being performed.    

Latches Statistics Successes/Timeouts
Latch Statistics are generated from the view v$latch. The header for this section is included below:  

NAME                       WAITS     IMMEDIATE TIMEOUTS  NOWAITS   SUCCESSES
-------------------------- --------- --------- --------- --------- ---------
                                                                       
        Column          Description                                    
        ----------      -----------
        Name            Name of the latch
        Waits           Number of requests for the latch
        Immediate       Number of latches that were obtained immediately
        Timeouts        Number of request for a latch that was unsuccessful
        Nowaits         Number of requests for the latch
        Successes       Number of requests for a latch that was successful
 

Waits, Immediate, and Timeouts capture the latches which are willing to wait for another latch to be freed. Nowaits and Successes are for latches which are not willing to wait for another latch to be freed (Similar to the lock table in exclusive mode nowait except for latches).  

Immediates plus timeouts may not always equal waits since a request for a latch can timeout multiple times before being successful. If the ratio of timeouts to total is greater than 10% or successes to total is less than 90%, init.ora parameters may need to be modified. Included below is a description of of the latches:  

If contention of the cache buffer lru chain is high, increase the init.ora parameter DB_BLOCK_WRITE_BATCH.  

Enqueues is determined by the init.ora parameter ENQUEUE_RESOURCES. If the ratio of timeouts to immediates is greater than 10%, increase this init.ora parameter ENQUEUE_RESOURCES.  

Redo Allocation latch governs the allocation of space in the redo log buffer. There is only one redo allocation latch per instance. If contention for this latch is greater than 10%, the init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE should be decreased. This init.ora parameter determines the maximum number of bytes of redo which can be written to the redo buffers using the redo allocation latch. If this init.ora parameter is lowered a redo copy latch is used.  

Redo Copy latch controls user processes writing redo entries to the redo buffers. Multiple users can hold a redo copy latch. If contention is greater than 10%, increase the init.ora parameters LOG_SIMULTANEOUS_COPIES and LOG_ENTRY_PREBUILD_THRESHOLD.  

LOG_SIMULTANEOUS_COPIES is the maximum number of redo copy latches that can be held at one time. This parameter should be set to be twice the size of the init.ora parameter CPU_COUNT. Please note that the init.ora parameter CPU_COUNT should not be modified.  

LOG_ENTRY_PREBUILD_THRESHOLD determines the maximum size of a redo entry to prebuild before the copy to the log buffer. Increasing this parameter reduces the time that the redo copy latch is held. This parameter should not be modified if it is a single processor environment or there is memory contention  

Row Cache Objects latches occur when a process is updating an entry in the dictionary cache. Session Allocation latches are obtained when a process allocations a new sessions. If there is a high number of timeouts for these parameters, the dictionary cache should be tuned. Tuning the dictionary cache is discussed later in the paper.    

Rollback Segment Statistics
Rollback Segment Statistics are generated from the view v$rollstat. Included below is the header for this section:  

TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES

------------------- ------------------- ------------------- ------------------- 
        Column          Description                                    
        ----------      -----------
        Trans_Tbl_Gets  The number the rollback segment header is accessed
        Trans_Tbl_Waits The number of waits for the rollback segment header
        Undo_Bytes_Wr   The number of types written to the rollback segment
        Segment_Size_By The size of the rollback segment in bytes
 

If the ratio of Trans_tbl_waits to Trans_tbl_gets is greater than 5%, additional rollback segments should be added to the database. In general, rollback segments should be the same size and created with a large number of small extents.    

Data Dictionary Cache Statistics
The data dictionary cache statistics are generated from the table X$kqrst. Included below is the header for this section:  

NAME                 GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS CUR_USAG
-------------------- -------- -------- -------- -------- -------- --------
 
        Column          Description                                    
        ----------      -----------
        Name            Dictionary cache name
        Get_Req         Total number of requests for object
        Get_Miss        Total number of object information not in cache
        Scan_Req        Total number of scan requests
        Scan_miss       Total number of scan misses
        Mod_reqs        
        Cur_Usag        Total number entries for dictionary cache object
 

The size of the dictionary cache is determined by the init.ora parameters beginning with "DC_". Each init.ora parameter should be set to the maximum number of concurrent accesses for a particular object by all user processes. For example, if there are 5 users each accessing 5 of the same tables, dc_tables should be set to at least 25. If ratio of number of get_miss to get_req is > 10% the appropriate init.ora "DC_" parameter should be increased.    

Init.ora Parameters
This section of the report contains a list of of the init.ora parameters and that were in effect during the execution of bstat/estat. The output is generated by issuing the command SHOW PARAMETERS from SQL*DBA.    

Date/Time
This section lists the date and time that bstat/estat was executed.    

References  

(1) Oracle RDBMS Performance Tuning Guide 6.0
 

(2) Oracle RDBMS Database Administrators Guide 6.0
 

(3) Tuning Oracle with INIT.ORA Parameters


RTSS Bulletin Board                                      Bulletin #100955.451



Daniel Druker
Senior Consultant
Oracle Corporation                    


| Dan Druker                    |  work 415.506.4803                          |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Sun May 17 1992 - 23:21:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US