Solved

Analyze bstat/estat

Posted on 1998-05-26
2
1,003 Views
Last Modified: 2008-03-10
I would like to have additional info on how to analyze the results from utlbstat/utlestat at oracle 7.2.3
0
Comment
Question by:hhi
2 Comments
 
LVL 3

Accepted Solution

by:
mgokman earned 30 total points
Comment Utility
Oracle Performance tuning manual as well as numerous books on Oracle tuning can give you the answer. Utlstats produce a report with the info that in most cases is sufficient to identify major performance problems such as too many physical i/o, improper i/o balance among physical drives, improper sizing of your shared pool, etc. To be able to use the info from this report, you need to understand how different components of Oracle server and its cconfiguration affects its performance. This is explained very well in the books I mentioned.
0
 

Expert Comment

by:adema
Comment Utility
Found the following info (support.oracle.com)

 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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to tune this sql query 61 99
levels for reporting 5 48
query in Oracle forms Builder 2 25
history tablespace temp usage 2 15
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now