Oracle Tuning - Using BSTAT and ESTAT
Date: Mon, 18 May 1992 04:21:55 GMT
Message-ID: <1992May18.042155.21712_at_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 _at_bstat
o Run application to be monitored
o Issue the command _at_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
- Dan
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 Mon May 18 1992 - 06:21:55 CEST