This article shows you how to use the statistics in the report generated by the UTLBSTAT and UTLESTAT scripts to fine tune your database for optimal performance.

Successful Oracle7 database performance tuning involves the monitoring of numerous database parameters. The UTLBSTAT and UTLESTAT scripts provided by Oracle can assist in this effort by summarizing the operating state of your database in a single report. You can then use this report for fine-tuning your database performance and for preventive maintenance of your Oracle7 databases. These two scripts provide information about database memory objects, including library cache, dictionary cache, latch usage, file I/O, and rollback statistics. In this article, I show you how to interpret the report generated by UTLBSTAT and UTLESTAT scripts, and I reveal ways in which you can use these statistics to improve your database performance.

Set Up

Oracle Server version 7.3.2 was used for the purpose of the article. The UTLBSTAT and UTLESTAT scripts are located in the ORACLE_HOME/rdbms/admin directory. The first script, UTLBSTAT.SQL, creates a set of tables and views and populates them with the statistics in the database at that time. The object names contain the word "begin" - for example, STATS$BEGIN_FILE. The second script, UTLESTAT.SQL, which is run at a later time, creates objects whose name contains the word "end" - for example, STATS$END_STATS. It populates these objects with ending database performance statistics and generates a report that contains the changes in the statistics during the interval between the run times for the UTLBSTAT.SQL and UTLESTAT.SQL scripts. Both of the scripts should be run under USERID SYS from SQL*Plus, Server Manager, or an Enterprise Manager SQL Worksheet. You can also run these scripts after connecting as INTERNAL in Server Manager.

You should set the Oracle7 initialization parameter TIMED_STATISTICS to TRUE before you run these scripts, and you should run the scripts only after the database has been running for a period of time. If you run them immediately after the database startup, the buffer cache will not be loaded with representative application data, and the statistics generated will likely not be valid for database performance analysis. Also, if the database is shut down in the middle of the execution of these scripts, the statistics are no longer valid. Statistics having negative values indicate that the database has been shut down and restarted.

When the scripts should be run depends on the type of application being run. For example, if you run several batch jobs every evening, execute UTLBSTAT before the batch jobs start, and execute UTLESTAT after the batch jobs complete. In another scenario, if users of your system use an application from 8 a.m. to 8 p.m., you may run the UTLBSTAT at 8 a.m. and UTLESTAT at 8 p.m. You can automate the UTLBSTAT and UTLESTAT runs using operating system schedulers. The scripts generate the report in a file named report.txt. This file is located in the $ORACLE_HOME/rdbms/admin directory.

Both UTLBSTAT and UTLESTAT scripts create temporary objects in the SYSTEM tablespace. Frequent runs of these scripts may fragment the SYSTEM tablespace. To avoid such fragmentation, you can copy these scripts and modify them so that they create their temporary objects in a tablespace other than the SYSTEM tablespace. Another useful change to the scripts would be to include database links in the FROM clause of the SELECT statements to enable the scripts to run in remote databases and help gather remote database performance statistics.

Interpreting the Report Output

The statistics generated by these scripts are classified in the following sections: library cache statistics, overall system statistics,

system-wide wait events, latch statistics, rollback statistics, instance initialization parameters (for reference), dictionary cache statistics, and file and tablespace I/O statistics. The date and time when the scripts are run are also included in the report. Some of the statistics are grouped at a per-transaction level, per-logon level, and per-second level. At the end, the report shows the Oracle7 Server version information. The following sections show you how to interpret these statistics to achieve better database performance. The statistic itself is shown in italics; the report generated by the scripts is shown in Listing 1 .

Library Cache Statistics

The library cache consists of shared SQL and PL/SQL areas. These statistics indicate if shared SQL statements are being reparsed because of insufficient memory allocated to the library cache. The pins column indicates the number of times an item was executed. The reloads column shows the number of misses. The ratio of reloads to pins is the percentage of executions that resulted in reparsing. If the get hit ratio or pin hit ratio is less than 90 percent, or if the ratio of reloads to pins is more than one percent, you should increase the memory allocated to the library cache by increasing the initialization parameter SHARED_POOL_SIZE.

Overall Statistics

The DBWR checkpoints indicates the number of checkpoint messages that were sent to database writer during the course of the report. This does not mean that so many checkpoints were performed, because if a second checkpoint is issued while the first is active, the first one stops and the second checkpoint starts. If checkpoints occur frequently, they can degrade the database's performance. During the checkpoints, data blocks are written to disk, causing additional I/O. You can lower the frequency of the checkpoints by making the initialization parameter LOG_CHECKPOINT_INTERVAL larger than the redo log file size. Thus checkpoints are performed only during log switch. If the number of checkpoints is reduced, the performance of normal database operations improves, although recovery after an Oracle7 instance failure takes longer. If the database initialization parameter CHECKPOINT_PROCESS is set to TRUE, then a checkpoint process would update the headers of data files during checkpoints. This would relieve the log writer of the responsibility to update data file headers during checkpoints and can thus improve the performance of transaction logging in an OLTP environment.

Cluster key scan block gets and cluster key scans provide information about clusters in the database. 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 needs to be analyzed for row chaining. When a cluster is created, the SIZE parameter determines the number of cluster keys per block; the default is one. If the SIZE parameter is not specified correctly, the rows for one cluster key may not fit properly in the data block. Therefore, it is important that you check the SIZE parameter of the cluster.

A dirty buffer is a data block buffer that has been modified but not written permanently to the database. Dirty buffer inspected is the number of times a foreground process encounters a dirty buffer that has aged out via the least recently used (LRU) queue while the process is looking for the buffer for reuse. This number should be zero if the database writer is keeping up with the foreground processes. If this number is high, check the number of database writers. Normally the number of database writers should be twice the number of disk drives on the system. If asynchronous I/O is available at your operating system level, it is better to use that rather than multiple database writers, because it requires less overhead and less processes. When using asynchronous I/O, Oracle performs parallel writes without using multiple database writers. Asynchronous I/O can be set by setting initialization parameter ASYNC_WRITE. This parameter is operating-system-dependent.

Free buffer requested is the total number of free buffers needed in order to create or load a block. Free buffer inspected is the number of buffers skipped in the buffer cache by a user process in order to find a free buffer. If this value is high, it means that there are too many modified blocks, and you should increase the buffer cache. Free buffer waits (in the system-wide wait event section) is the number of times that processes needed a free buffer and one was not available. Write complete waits (in the system-wide wait event section) is the number of times a process waited for the database writer to write a current block before making a change to a buffer. If the value of free buffer waits or write complete waits is high or showing growth, consider increasing the initialization parameter DB_BLOCK_WRITE_BATCH, which specifies the number of blocks that should be written to the disk at one time.

Consistent changes indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Applications that produce a great deal of consistent changes can consume a lot of resources. Consistent gets indicates the number of times a consistent read was requested for a database block. DB block changes indicates the total number of changes made to all of the database blocks in the SGA that were a part of an UPDATE or DELETE operation. This statistic shows, on a per-transaction basis, the rate at which database buffers are being modified. It is a rough indication of the total database work being performed. DB block gets is the number of blocks accessed in the buffer by INSERT, UPDATE, DELETE, or SELECT FOR UPDATE statements. The sum of the values of consistent gets and db block gets is known as the number of logical reads.

The physical reads statistic stores the number of I/O requests to the operating system to retrieve a database block from the disk subsystem. It is the number of requests for a block that resulted in physical I/O. This is a buffer cache miss. Physical writes is the number of I/O requests to the operating system to write a database block to the disk subsystem. The bulk of the writes are performed by either the DBWR or the LGWR server background process.
The ratio between logical reads and physical reads should be greater than 90 percent:

Ratio = logical reads\(logical reads + physical reads)

If this ratio is less than 90 percent, too many buffer cache misses are occuring. To correct the problem, consider increasing the buffer cache by increasing the value of the initialization parameter DB_BLOCK_BUFFERS.

Opened cursors cumulative is the total number of cursors that were opened during the execution of the UTLBSTAT and UTLESTAT scripts. A cursor is opened for each SQL statement parsed into a context area. If the cursors are reused, the performance improves, because there is no need to reparse SQL statements. If opened cursors cumulative is high, check for applications opening many cursors. An application should close the cursor after the SQL statement completes. A high opened cursors cumulative ratio can also indicate poorly designed application statements - for example, statements that do not use bind variables. Consequently, shared SQL is not possible because the server can only reuse shared SQL statements that are identical in syntax. For more details about application tuning, refer to Roger Snowden's article "Application Tuning, Part 1" in the January/February 1997 issue (page 10).

Recursive calls indicate data dictionary cache misses and segment extensions. The Oracle database maintains tables used for internal processing. When the database needs to make a change to these tables, the database internally generates a SQL statement. These internal SQL statements also generate recursive calls. In general, recursive calls should be less than four per process, and the ratio of recursive calls to user calls should be less than 10 percent. If the value of this statistic is high, you should tune the data dictionary cache and re-create segments such as tables, indexes, temporary segments, and rollback segments with a storage clause that has a few large extents. Data dictionary cache misses can be reduced by increasing shared pool area. If it is not possible to recreate database objects, you should identify the objects that are extending frequently and increase the value of their NEXT storage parameters to ensure that the next extent is created large, which will prevent subsequent extensions. It is also possible that for rollback segments, OPTIMAL parameter might be set wrong such that the rollback segment is constantly growing and shrinking, thus causing the extensions. Set the OPTIMAL parameter for the rollback segment so that it does not shrink often.

The redo entries statistic increments each time an Oracle user process copies redo entries into the redo log buffer. The server creates space by performing a log switch. If the server's log files are small compared to the size of the SGA or the commit rate of the workload, the server may have problems logging ongoing transactions. When the server performs a log switch, Oracle must ensure that it writes to disk all committed dirty buffers before switching to a new log file. If the server has a large SGA full of dirty buffers and small redo log files, a log switch must wait for the database writer to write dirty buffers to the disk before continuing. Redo sync writes is the number of times that the server writes redo log changes from the log buffer to disk.

Redo small copies is the number of redo entries that are smaller than the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE. Oracle writes redo entries smaller than LOG_SMALL_ENTRY_MAX_ SIZE using the redo allocation latch; the server writes larger entries using copy latches. On single CPU computers, there is no copy latch because only one process can be active at one time. In this case, all redo entries are copied on the redo allocation latch, regardless of size. On multiple CPU computers, the redo log buffer can have multiple redo copy latches. In this case, if the redo log size is more than LOG_SMALL_ENTRY_MAX_SIZE, a copy latch is obtained and redo log data is copied into the redo log buffer using the copy latch. Multiple redo log copy latches enable multiple processes to copy entries to the redo log buffer concurrently and hence result in better performance. To use more copy latches, decrease LOG_SMALL_ENTRY_MAX_SIZE. Set the initialization parameter LOG_SIMULTANEOUS_COPIES equal to the number of CPUs to achieve better copy latch concurrence. There is only one redo allocation latch per instance, and the number of redo copy latches is the same as the number of CPUs on the machine. Multiple processes can hold a redo copy latch. The hit ratio for redo copy and redo allocation latches should be more than 90 percent (refer to the Latch Statistics section of this article for these ratios).

Sorts (disks) is the number of times that the database created a temporary segment to perform sorting on disk. Oracle creates temporary segments when there is not enough room in memory to complete the sort. Sorts (memory) is the number of times a sort was executed in the memory. Sort (rows) is the total number of rows sorted. If sorts (disk) is high, increase the initialization parameter SORT_AREA_SIZE. Also, if possible, modify the application to perform fewer sorts. SQL statements with a GROUP BY or ORDER BY clause, DISTINCT operator, UNION/INTERSECT/MINUS statements, sort-merge joins, and index-creation statements result in sorting. By default, a tablespace is permanent in nature. The objects created in such a tablespace remain in the tablespace unless deleted by a user. Oracle7.3 introduced a tablespace called TEMPORARY, which can only have temporary objects in it. Objects in the TEMPORARY tablespace are periodically removed by the Oracle Server.

Table fetch by rowid should be high. This statistic 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 way to access data and should be used wherever possible. Table fetch continued row is the number of rows that are chained to another block. It indicates that additional I/O must be performed to access the entire row. The table fetch continued row value should be lower than 10 percent of the table fetch by rowid statistic. If the table fetch continued row value is high, reduce the chaining of rows by re-creating the table with proper storage parameters or reinserting the chained rows. You cannot avoid row-chaining in a table with LONG columns. Chaining can also occur in update-intensive applications in which the storage parameter PCTFREE is not set correctly for a table's or index's data blocks. In such cases, you should modify the PCTFREE parameter for objects containing chained rows.

Table scan blocks gotten indicates the number of database blocks Oracle scanned in order to get data. During scanning operations, each row is retrieved sequentially, and every block encountered during the scan increments this statistic. Compare this statistic to consistent gets to get a feeling for how much of the consistent read activity results from scanning. The table scan rows gotten statistic shows the number of database rows processed to get the data.

Table scans (long tables) is the number of full table scans performed on tables with more than four database blocks. If the number of full table scans is much greater than zero on a pertransaction basis, you should tune the application to use Oracle indexes effectively. If more than 20 percent of the rows from a table are returned, indexes should be used on long tables. Table scans (short tables) is the number of full table scans performed on tables with fewer than four database blocks. On short tables, full table scans result in better performance than index scans. The total number of full table scans that occur during the execution of the UTLBSTAT and UTLESTAT scripts is the sum of table scans (long tables) and table scans (short tables).

User calls is the number of times a call is made to the server. If possible, you should reduce the number of calls to the server. You can use array processing in applications to reduce the number of calls to the server. Parse count shows the number of times a SQL statement was parsed. The parse count statistic counts the number of times the user called "parse" function from either the OCI or the Oracle precompiler. You can find the number of calls to the server per parse by dividing parse count by user calls.

User commits is the number of times that users have performed a COMMIT. When a user commits a transaction, the redo information must be written to disk. This redo information reflects the changes made to database blocks. Transaction commits represent the closest thing to a user transaction rate. User rollbacks shows the number of times that users have explicitly performed a transaction rollback and when the server has implicitly performed a transaction rollback because of some type of an error. Write requests indicates the number of times the database writer takes a batch of dirty buffers and writes them to disk.

System-Wide Wait Events

This section of the report lists various wait events specific to the Oracle7 Server. For each statistic, the total time taken by waits and the average time per wait are shown. Free buffer waits and buffer busy waits statistics do not appear in the report generated by Oracle Server version 7.3. However, they do appear in the report generated by previous versions. Free buffer waits stores the number of times a free buffer was requested in the SGA but none were available. Free buffers are buffers that are not being used by current transactions. If the SGA is full of dirty buffers and the database writer cannot write them to disk, then the value of free buffer waits will increase. Update-intensive applications that use small indexes or hash clusters may run the risk of having an entire SGA full of dirty buffers that a database writer cannot keep up with. In such cases, consider increasing the server's DB_BLOCK_BUFFERS initialization parameter.

A high buffer busy waits value indicates a predominantly I/O-bound application. In a single-instance mode, a high value indicates contention for database blocks. In an Oracle parallel server environment, this indicates pinging (competition between nodes for database blocks). The buffer busy wait ratio is buffer busy waits divided by logical reads (logical reads equals the sum of the values of consistent gets and db block gets). If this ratio is greater than four percent, you can discover the type of buffer waits with the following SQL statement:

SELECT class, SUM(counts) waits FROM v$waitstat
WHERE class IN ("undo header", "undo block",
"data block")
GROUP BY class;

If waits are high for "undo header" or "undo block," there are waits for rollback segments, so you should increase the number of rollback segments. If waits are high for "data block," increase the number of freelists for the tables that are involved in many INSERT operations.

This section of the report also lists SQL*Net messages received from clients, SQL*Net messages sent to clients, and SQL*Net activities using database links to remote databases.

Latch Statistics

The hit ratio for all latches should be more than 90 percent. If the contention for cache buffer lru is high, increase the initialization parameter DB_BLOCK_WRITE_BATCH. If the hit ratio for enqueues is low, increase the initialization parameter ENQUEUE_RESOURCES. If there is contention for the redo allocation latch, decrease the parameter LOG_SMALL_ENTRY_SMALL_
MAX_SIZE. Row cache object latches occur when a process is updating an entry in the dictionary cache. Session allocation latches occur when a process allocates a new session. If the hit ratio is low for any of these parameters, increase the shared pool area. The no wait hit ratio column shows the percentage of no wait latch requests that were satisfied immediately. This ratio should be close to one; if it is not, increase SHARED_POOL_AREA.

Rollback Statistics

The undo_segment statistic identifies the rollback segment number to which the line refers. Trans_tbl_gets is the number of rollback segment header requests. Trans_tbl_waits is the number of rollback segment header requests that resulted in waits. Undo_bytes_written is the number of bytes written to the rollback segment. Segment_size_bytes is the size of the rollback segment in bytes; this column has only the ending value. Xacts is the current number of active transactions. Shrinks is the number of shrinks the rollback segment had to perform in order to stay within the OPTIMAL size. Wraps is the number of times a rollback segment entry wrapped from one extent to another. Non-zero values of shrinks and wraps indicates that the rollback segment is expanding and shrinking to the optimal setting. If rollback segments are shrinking and wrapping too frequently, you should redesign the rollback segment to reflect the type of transactions being performed against the database. This would involve recreating the rollback segments with appropriate values for the NEXT and OPTIMAL storage parameters.

If the ratio of trans_tbl_waits to trans_tbl_gets is greater than five percent, add additional rollback segments in the database. In general, rollback segments should be equal in size and created with a large number of small extents. If you have large batch job transactions or large snapshot group refreshes, you might want to create a few larger rollback segments and assign them to these types of transactions.

Dictionary Cache

The dictionary cache needs to be tuned if the ratio of the number of get_miss to get_req is greater than 10 percent. You may need to increase the size of the shared pool with the parameter SHARED_POOL_SIZE. The count column shows the setting of that cache parameter in the database, and the current_usage column shows the number of current entries in that cache.

File I/O Statistics

This section of the report has I/O information at every database file and tablespace level. The information includes the number of physical reads from the data file (reads), the number of blocks read from the data file (blks_read), the time to read blocks (read_time), the number of physical writes to the data file (writes), the number of physical blocks written to the data file (blks_wrt), and the time to write blocks (write_time). File I/O should be spread evenly across multiple disk drives. In general, tables should be located on different disks from their associated indexes, large tables and indexes should be striped across several disks, active data files should be located on the highest throughput disks, and redo logs should be located on disks that do not contain database data files.

As seen from the report shown in Listing 1, the disk drive data04 has the maximum physical I/O, which is much more than the other disk drives. If a particular disk drive has more physical I/O than other disk drives, you should spread data from that disk across other disk drives. Also, in the report, the tablespace po_hdr has substantially more physical reads than other tablespaces, which indicates that you should give more tuning attention to objects in this tablespace as well as to applications accessing these objects.

You can set the initialization parameter DB_FILE_MULTI_BLOCK_READ_COUNT to increase the number of blocks read during a single read. Increasing this parameter reduces I/O when full table scans are performed. The report also generates I/O distribution summed at the tablespace level.

Achieving Robust Performance

UTLBSTAT and UTLESTAT are a set of SQL scripts useful for capturing a snapshot of system-wide database performance statistics. The statistics generated from these scripts can help you optimize the performance of your Oracle7 databases. These two scripts can gather a snapshot of your database performance over a given period of time and help you fine-tune the numerous database parameters for optimal database performance. As DBAs become more overwhelmed with database performance gathering statistics, newer tools have automated this task to some extent. One such product is Oracle Expert, which is part of the Oracle Enterprise Manager Performance Pack. It automates collection, analysis, interpretation, and implementation of tuning data.

Listing 1

SVRMGR> SVRMGR> set charwidth 12 Charwidth 12 SVRMGR> set numwidth 10 Numwidth 10 SVRMGR> Rem Select Library cache statistics. The pin hit rate should be high. SVRMGR> select namespace library, 2> gets, 3> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3) 4> gethitratio, 5> pins, 6> round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) 7> pinhitratio, 8> reloads, invalidations 9> from stats$lib; LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI ------- ---- ---------- ---- ---------- ------- ---------- BODY 34 1 34 1 0 0 CLUSTER 0 1 0 1 0 0 INDEX 855 1 855 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 1002 1 1148 1 0 0 SQL AREA 6818 .988 26910 .991 64 0 TABLE/PROCED 1675 .98 2111 .95 74 0 TRIGGER 15 1 15 .8 3 0 8 rows selected. SVRMGR> SVRMGR> set charwidth 27; Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12 SVRMGR> Rem The total is the total value of the statistic between the time SVRMGR> Rem bstat was run and the time estat was run. Note that the estat SVRMGR> Rem script logs on as "internal" so the per_logon statistics will SVRMGR> Rem always be based on at least one logon. SVRMGR> select n1.name "Statistic", 2> n1.change "Total", 3> round(n1.change/trans.change,2) "Per Transaction", 4> round(n1.change/logs.change,2) "Per Logon", 5> round(n1.change/(to_number(to_char(end_time, 'J'))*60*60*24 - 6> to_number(to_char(start_time, 'J'))*60*60*24 + 7> to_number(to_char(end_time, 'SSSSS')) - 8> to_number(to_char(start_time, 'SSSSS'))) 9> , 2) "Per Second" 10> from stats$stats n1, stats$stats trans, stats$stats logs, stats$dates 11> where trans.name='user commits' 12> and logs.name='logons cumulative' 13> and n1.change != 0 14> order by n1.name; Statistic Total Per Transact Per Logon Per Second ---------------------------- ------ ------------ --------- ---------- CPU used by this session 23765 42.36 49.93 2.13 CPU used when call started 23765 42.36 49.93 2.13 CR blocks created 223 .4 .47 .02 DBWR buffers scanned 82969 147.89 174.3 7.45 DBWR checkpoints 31 .06 .07 0 DBWR free buffers found 81489 145.26 171.2 7.32 DBWR lru scans 222 .4 .47 .02 DBWR make free requests 220 .39 .46 .02 DBWR summed scan depth 82969 147.89 174.3 7.45 DBWR timeouts 3649 6.5 7.67 .33 SQL*Net roundtrips to/from 8880 15.83 18.66 .8 SQL*Net roundtrips to/from 25 .04 .05 0 background timeouts 11451 20.41 24.06 1.03 bytes received via SQL*Net 1306099 2328.16 2743.91 117.31 bytes received via SQL*Net 3017 5.38 6.34 .27 bytes sent via SQL*Net to c 7004630 12485.97 14715.61 629.12 bytes sent via SQL*Net to d 3069 5.47 6.45 .28 calls to get snapshot scn: 15356 27.37 32.26 1.38 calls to kcmgas 648 1.16 1.36 .06 calls to kcmgcs 201 .36 .42 .02 calls to kcmgrs 17658 31.48 37.1 1.59 change write time 100 .18 .21 .01 cleanouts and rollbacks - c 49 .09 .1 0 cleanouts only - consistent 10 .02 .02 0 cluster key scan block gets 173622 309.49 364.75 15.59 cluster key scans 102206 182.19 214.72 9.18 commit cleanout failures: b 2 0 0 0 commit cleanout failures: c 18 .03 .04 0 commit cleanout number succ 1364 2.43 2.87 .12 consistent changes 292 .52 .61 .03 consistent gets 3155269 5624.37 6628.72 283.39 cursor authentications 198 .35 .42 .02 data blocks consistent read 292 .52 .61 .03 db block changes 28324 50.49 59.5 2.54 db block gets 28848 51.42 60.61 2.59 deferred (CURRENT) block cl 1201 2.14 2.52 .11 enqueue conversions 109 .19 .23 .01 enqueue releases 2661 4.74 5.59 .24 enqueue requests 3026 5.39 6.36 .27 enqueue timeouts 371 .66 .78 .03 execute count 12298 21.92 25.84 1.1 free buffer inspected 4476 7.98 9.4 .4 free buffer requested 92459 164.81 194.24 8.3 immediate (CR) block cleano 59 .11 .12 .01 immediate (CURRENT) block c 97 .17 .2 .01 logons cumulative 476 .85 1 .04 logons current -1 0 0 0 messages received 898 1.6 1.89 .08 messages sent 898 1.6 1.89 .08 no work - consistent read g 1972867 3516.7 4144.68 177.19 opened cursors cumulative 5798 10.34 12.18 .52 opened cursors current -6 -.01 -.01 0 parse count 6931 12.35 14.56 .62 parse time cpu 251 .45 .53 .02 parse time elapsed 418 .75 .88 .04 physical reads 88945 158.55 186.86 7.99 physical writes 5364 9.56 11.27 .48 process last non-idle time29727511992 52990217.45 62452756.29 2669975.93 recursive calls 74631 133.03 156.79 6.7 recursive cpu usage 104 .19 .22 .01 redo blocks written 4235 7.55 8.9 .38 redo entries 14193 25.3 29.82 1.27 redo size 3870954 6900.1 8132.26 347.67 redo small copies 14193 25.3 29.82 1.27 redo synch time 714 1.27 1.5 .06 redo synch writes 454 .81 .95 .04 redo wastage 387103 690.02 813.24 34.77 redo write time 3369 6.01 7.08 .3 redo writes 713 1.27 1.5 .06 rollbacks only - consistent 176 .31 .37 .02 session connect time 29727511992 52990217.45 62452756.29 2669975.93 session logical reads 3180236 5668.87 6681.17 285.63 session pga memory 631404728 1125498.62 1326480.52 56709.6 session pga memory max 631851520 1126295.04 1327419.16 56749.73 session uga memory 1048360 1868.73 2202.44 94.16 session uga memory max 6621768 11803.51 13911.28 594.73 sorts (disk) 14 .02 .03 0 sorts (memory) 2963 5.28 6.22 .27 sorts (rows) 964245 1718.8 2025.72 86.6 summed dirty queue length 22 .04 .05 0 table fetch by rowid 931242 1659.97 1956.39 83.64 table fetch continued row 444 .79 .93 .04 table scan blocks gotten 275985 491.95 579.8 24.79 table scan rows gotten 2095727 3735.7 4402.79 188.23 table scans (long tables) 436 .78 .92 .04 table scans (short tables) 645 1.15 1.36 .06 total number commit cleanou 1386 2.47 2.91 .12 user calls 9339 16.65 19.62 .84 user commits 561 1 1.18 .05 user rollbacks 7 .01 .01 0 write requests 1067 1.9 2.24 .1 91 rows selected. SVRMGR> SVRMGR> SVRMGR> set numwidth 27 Numwidth 27 SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger SVRMGR> Rem than the value of the db_block_write_batch init.ora parameter, SVRMGR> Rem then consider increasing the value of db_block_write_batch and SVRMGR> Rem check for disks that are doing many more IOs than other disks. SVRMGR> select queue.change/writes.change "Average Write Queue Length" 2> from stats$stats queue, stats$stats writes 3> where queue.name = 'summed dirty queue length' 4> and writes.name = 'write requests'; Average Write Queue Length --------------------------- .02061855670103092783505155 1 row selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 32; Charwidth 32 SVRMGR> set numwidth 13; Numwidth 13 SVRMGR> Rem System wide wait events for non-background processes (PMON, SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one of SVRMGR> Rem these is a context switch which costs CPU time. By looking at SVRMGR> Rem the Total Time you can often determine what is the bottleneck SVRMGR> Rem that processes are waiting for. This shows the total time spent SVRMGR> Rem waiting for a specific event and the average time per wait on SVRMGR> Rem that event. SVRMGR> select n1.event "Event Name", 2> n1.event_count "Count", 3> n1.time_waited "Total Time", 4> round(n1.time_waited/n1.event_count, 2) "Avg Time" 5> from stats$event n1 6> where n1.event_count > 0 7> order by n1.time_waited desc; Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- SQL*Net message from client 9213 10346654 1123.05 rdbms ipc message 369 1113003 3016.27 pipe get 2563 1102020 429.97 db file sequential read 32230 12011 .37 db file scattered read 8022 5701 .71 SQL*Net more data to client 2351 1863 .79 log file sync 81 714 8.81 SQL*Net more data from client 222 603 2.72 write complete waits 25 443 17.72 rdbms ipc reply 5 262 52.4 control file sequential read 933 138 .15 SQL*Net message from dblink 25 51 2.04 latch free 17 12 .71 SQL*Net message to client 9212 11 0 SQL*Net break/reset to client 19 7 .37 SQL*Net message to dblink 25 0 0 16 rows selected. SVRMGR> SVRMGR> SVRMGR> Rem System wide wait events for background processes (PMON, SMON, etc) SVRMGR> select n1.event "Event Name", 2> n1.event_count "Count", 3> n1.time_waited "Total Time", 4> round(n1.time_waited/n1.event_count, 2) "Avg Time" 5> from stats$bck_event n1 6> where n1.event_count > 0 7> order by n1.time_waited desc; Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- Null event 45166 11110814 246 rdbms ipc message 11901 4409720 370.53 pmon timer 3699 1113405 301 smon timer 37 1110037 30001 log file parallel write 714 3370 4.72 db file parallel write 1067 2219 2.08 log file sync 56 791 14.13 db file scattered read 50 19 .38 db file sequential read 22 18 . 82 latch free 1 1 1 rdbms ipc reply 5 1 .2 11 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 18; Charwidth 18 SVRMGR> set numwidth 11; Numwidth 11 SVRMGR> Rem Latch statistics. Latch contention will show up as a large value for SVRMGR> Rem the 'latch free' event in the wait events above. SVRMGR> Rem Sleeps should be low. The hit_ratio should be high. SVRMGR> select name latch_name, gets, misses, 2> round(decode(gets-misses,0,1,gets-misses)/decode(gets,0,1,gets),3) 3> hit_ratio, 4> sleeps, 5> round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS" 6> from stats$latches 7> where gets != 0 8> order by name; LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS ------------------ --------- --------- ---------- ------ ----------- cache buffer handl 178 0 1 0 0 cache buffers chai 5411780 3 1 3 1 cache buffers lru 97093 9 1 9 1 dml lock allocatio 2477 0 1 0 0 enqueue hash chain 5786 0 1 0 0 enqueues 8932 0 1 0 0 global transaction 18 0 1 0 0 global tx free lis 2 0 1 0 0 global tx hash map 5 0 1 0 0 ktm global data 37 0 1 0 0 latch wait list 10 0 1 0 0 library cache 126769 4 1 4 1 library cache load 212 0 1 0 0 list of block allo 1311 0 1 0 0 messages 26150 0 1 0 0 modify parameter v 476 0 1 0 0 multiblock read ob 16544 0 1 0 0 process allocation 103 0 1 0 0 redo allocation 20938 0 1 0 0 row cache objects 65150 0 1 0 0 sequence cache 378 0 1 0 0 session allocation 3527 0 1 0 0 session idle bit 19613 0 1 0 0 session switching 176 0 1 0 0 shared pool 19799 1 1 2 2 sort extent pool 37 0 1 0 0 system commit numb 20748 0 1 0 0 transaction alloca 15640 0 1 0 0 undo global data 3438 0 1 0 0 user lock 394 0 1 0 0 30 rows selected. SVRMGR> SVRMGR> set numwidth 16 Numwidth 16 SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not SVRMGR> Rem wait for the latch to become free, it immediately times out. SVRMGR> select name latch_name, 2> immed_gets nowait_gets, 3> immed_miss nowait_misses, 4> round(decode(immed_gets-immed_miss,0,1,immed_gets-immed_miss)/ 5> decode(immed_gets,0,1,immed_gets), 6> 3) 7> nowait_hit_ratio 8> from stats$latches 9> where immed_gets != 0 10> order by name; LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO ------------------ ----------- ------------- ---------------- cache buffers chai 2878917 41 1 cache buffers lru 107170 69 .999 library cache 21 0 1 process allocation 103 0 1 row cache objects 20 0 1 5 rows selected. SVRMGR> SVRMGR> Rem Buffer busy wait statistics. If the value for 'buffer busy wait' in SVRMGR> Rem the wait event statistics is high, then this table will identify SVRMGR> Rem which class of blocks is having high contention. If there are high SVRMGR> Rem 'undo header' waits then add more rollback segments. If there are SVRMGR> Rem high 'segment header' waits then adding freelists might help. Check SVRMGR> Rem v$session_wait to get the addresses of the actual blocks having SVRMGR> Rem contention. SVRMGR> select * from stats$waitstat 2> where count != 0 3> order by count desc; CLASS COUNT TIME ------------------ ---------------- ---------------- 0 rows selected. SVRMGR> SVRMGR> SVRMGR> set numwidth 19; Numwidth 19 SVRMGR> Rem Waits_for_trans_tbl high implies you should add rollback segments. SVRMGR> select * from stats$roll; UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS WRAPS ------------ -------------- --------------- ------------------ ------------------ ----- ------- ----- 0 56 0 0 237568 0 0 0 2 218 0 28096 12648448 0 0 0 3 235 0 161937 8429568 1 0 0 4 347 0 520908 8429568 0 0 0 5 235 0 176206 8429568 0 0 0 6 283 0 550542 8429568 -1 0 0 7 239 0 165986 8429568 0 0 0 8 225 0 38598 12648448 0 0 0 9 225 0 45176 8429568 0 0 0 9 rows selected. SVRMGR> SVRMGR> set charwidth 39 Charwidth 39 SVRMGR> Rem The init.ora parameters currently in effect: SVRMGR> select name, value from v$parameter where isdefault = 'FALSE' 2> order by name; NAME VALUE --------------------------------------- --------------------------------------- always_anti_join NESTED_LOOPS audit_trail NONE background_dump_dest /app/oracle/admin/cert01/bdump cache_size_threshold 150 checkpoint_process TRUE compatible 7.3.2 control_files /data01/Oracle/cert01/control01.ctl, /d core_dump_dest /app/oracle/admin/cert01/cdump cpu_count 1 db_block_buffers 1500 db_block_lru_latches 1 db_block_size 8192 db_domain world db_files 256 db_name cert01 db_writers 10 distributed_lock_timeout 180 distributed_transactions 61 dml_locks 500 enqueue_resources 2520 gc_db_locks 1500 gc_freelist_groups 50 gc_releasable_locks 1500 global_names TRUE job_queue_interval 30 job_queue_processes 2 log_archive_format _%s.log log_buffer 6062080 log_checkpoint_interval 100000 log_simultaneous_copies 0 max_dump_file_size 10240 max_enabled_roles 30 mts_max_dispatchers 0 mts_max_servers 0 mts_servers 0 mts_service cert01 open_cursors 450 optimizer_mode RULE processes 450 remote_login_passwordfile NONE rollback_segments r01, r02, r03, r04, r05, r06, r07, r08 sequence_cache_entries 50 sessions 500 shared_pool_size 12000000 snapshot_refresh_interval 360 snapshot_refresh_processes 1 sort_area_retained_size 524288 sort_area_size 524288 sort_direct_writes AUTO temporary_table_locks 500 timed_statistics TRUE transactions 550 transactions_per_rollback_segment 71 user_dump_dest /app/oracle/admin/cert01/udump 54 rows selected. SVRMGR> SVRMGR> set charwidth 15; Charwidth 15 SVRMGR> set numwidth 8; Numwidth 8 SVRMGR> Rem get_miss and scan_miss should be very low compared to the requests. SVRMGR> Rem cur_usage is the number of entries in the cache that are being used. SVRMGR> select * from stats$dc 2> where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0; NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG --------------- -------- -------- -------- -------- -------- ----- --------- dc_tablespaces 58 0 0 0 0 13 12 dc_free_extents 1546 65 16 0 93 83 71 dc_segments 530 15 0 0 47 537 516 dc_rollback_seg 740 0 0 0 0 18 11 dc_used_extents 47 16 0 0 47 53 23 dc_tablespace_q 1 1 0 0 1 4 3 dc_users 9016 0 0 0 0 49 45 dc_user_grants 8560 0 0 0 0 78 43 dc_objects 2527 8 0 0 0 1480 1477 dc_tables 2947 1 0 0 0 499 493 dc_columns 8423 23 583 1 0 3891 3887 dc_table_grants 2251 4 0 0 0 4582 4580 dc_indexes 363 0 269 0 0 345 314 dc_constraint_d 300 0 45 0 0 480 479 dc_constraint_d 106 0 26 0 0 123 32 dc_synonyms 150 1 0 0 0 172 171 dc_usernames 542 1 0 0 0 40 37 dc_object_ids 292 0 0 0 0 514 510 dc_sequences 32 0 0 0 24 14 13 dc_sequence_gra 6 0 0 0 0 135 26 dc_tablespaces 16 1 0 0 16 9 5 dc_database_lin 4 0 0 0 0 13 11 22 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 80; Charwidth 80 SVRMGR> set numwidth 10; Numwidth 10 SVRMGR> Rem Sum IO operations over tablespaces. SVRMGR> select 2> table_space||' ' 3> table_space, 4> sum(phys_reads) reads, sum(phys_blks_rd) blks_read, 5> sum(phys_rd_time) read_time, sum(phys_writes) writes, 6> sum(phys_blks_wr) blks_wrt, sum(phys_wrt_tim) write_time, 7> sum(megabytes_size) megabytes 8> from stats$files 9> group by table_space 10> order by table_space; TABLESPACE READS BLKS_READ RD_TIME WRITES BLKS_WRT WRI_TIME MBYTES ------------------ ----- --------- ------- ------ -------- -------- ------ APP_AUDIT_DATA 29 29 27 2 2 8 89 BOM_DATA 991 2577 387 0 0 0 105 BOM_INDX 9 9 22 0 0 0 63 BRPS_DATA 105 105 61 0 0 0 26 DSK_IMAGE 3 3 2 0 0 0 315 DSK_INBOX_FWRD 12 12 16 0 0 0 1 DSK_OBJ 9 9 13 3 3 5 105 DSK_OBJ_INDEX 27 27 37 0 0 0 21 DSK_RTG_HDR 3 3 0 0 0 0 31 DSK_RTG_INOUT_BOX 3 3 0 0 0 0 2 DSK_RTG_OBJ 3 3 0 0 0 0 105 DSK_RTG_OBJ_INDEX 6 6 17 0 0 0 210 NSR_DATA 5465 21861 4201 247 247 4066 157 NSR_INDX 805 805 751 27 27 59 262 OMMAIL_DATA 27 27 39 3 3 8 16 OMMAIL_INDEX 19 19 23 6 6 26 5 PO_DTL 2584 20588 1322 0 0 0 105 PO_HDR 22064 24110 5541 0 0 0 105 PROCURE_DATA 1715 1768 975 31 31 134 315 PROCURE_INDX 1157 1282 713 1 1 2 315 Q_DATA 115 134 141 191 191 825 16 REPLICATION 0 0 0 0 0 0 10 REPORT_SNAPSHOTLOG 75 75 51 0 0 0 52 ROLLBACK 154 154 325 1573 1573 9023 210 SYSTEM 4198 14570 2648 956 956 2835 315 TEMPORARY 97 651 53 2328 2328 69137 210 VENDOR 8 29 16 0 0 0 4 WORKSPACE 95 95 72 0 0 0 210 28 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 48; Charwidth 48 SVRMGR> set numwidth 10; Numwidth 10 SVRMGR> Rem I/O should be spread evenly accross drives. A big difference between SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on. SVRMGR> select table_space, file_name, 2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time, 3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time, 4> megabytes_size megabytes 5> from stats$files order by table_space, file_name; TABLESPACE FILENAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES ------------------ ----------------------------------------------- ----- --------- --------- ------ -------- ---------- --------- APP_AUDIT_DATA /data03/Oracle/cert01/app_audit_data01.dbf 29 29 27 2 2 8 89 BOM_DATA /data01/Oracle/cert01/bom_data01.dbf 991 2577 387 0 0 0 105 BOM_INDX /data02/Oracle/cert01/bom_indx01.dbf 9 9 22 0 0 0 63 BRPS_DATA /data02/Oracle/cert01/brps_data01.dbf 105 105 61 0 0 0 26 DSK_IMAGE /data04/Oracle/cert01/dsk_image01.dbf 3 3 2 0 0 0 315 DSK_INBOX_FWRD /data03/Oracle/cert01/dsk_inbox_forward01.dbf 12 12 16 0 0 0 1 DSK_OBJ /data04/Oracle/cert01/dsk_obj01.dbf 9 9 13 3 3 5 105 DSK_OBJ_INDEX /data02/Oracle/cert01/dsk_obj_index01.dbf 27 27 37 0 0 0 21 DSK_RTG_HDR /data04/Oracle/cert01/dsk_rtg_hdr01.dbf 3 3 0 0 0 0 31 DSK_RTG_IN_OUT_BOX /data04/Oracle/cert01/dsk_rtg_in_out_box01.dbf 3 3 0 0 0 0 2 DSK_RTG_OBJ /data03/Oracle/cert01/dsk_rtg_obj01.dbf 3 3 0 0 0 0 105 DSK_RTG_OBJ_INDEX /data04/Oracle/cert01/dsk_rtg_obj_index01.dbf 6 6 17 0 0 0 210 NSR_DATA /data01/Oracle/cert01/nsr_data01.dbf 5465 21861 4201 247 247 4066 157 NSR_INDX /data02/Oracle/cert01/nsr_indx01.dbf 790 790 711 27 27 59 157 NSR_INDX /data02/Oracle/cert01/nsr_indx02.dbf 15 15 40 0 0 0 105 OMMAIL_DATA /data02/Oracle/cert01/ommail_data01.dbf 27 27 39 3 3 8 16 OMMAIL_INDEX /data03/Oracle/cert01/ommail_index01.dbf 19 19 23 6 6 26 5 PO_DTL /data04/Oracle/cert01/po_dtl01.dbf 2584 20588 1322 0 0 0 105 PO_HDR /data04/Oracle/cert01/po_hdr01.dbf 22064 24110 5541 0 0 0 105 PROCURE_DATA /data03/Oracle/cert01/procure_data01.dbf 1715 1768 975 31 31 134 315 PROCURE_INDX /data02/Oracle/cert01/procure_indx01.dbf 1157 1282 713 1 1 2 315 Q_DATA /data03/Oracle/cert01/q_data01.dbf 115 134 141 191 191 825 16 REPLICATION /data01/Oracle/cert01/replication01.dbf 0 0 0 0 0 0 10 REPORT_SNAPSHOTLOG /data04/Oracle/cert01/report_snapshotlog01.dbf 75 75 51 0 0 0 52 ROLLBACK /data03/Oracle/cert01/rollback01.dbf 154 154 325 1573 1573 9023 210 SYSTEM /data01/Oracle/cert01/system01.dbf 4198 14570 2648 956 956 2835 315 TEMPORARY /data01/Oracle/cert01/temporary01.dbf 97 651 53 2328 2328 69137 210 VENDOR /data04/Oracle/cert01/vendor01.dbf 8 29 16 0 0 0 4 WORKSPACE /data01/Oracle/cert01/workspace01.dbf 95 95 72 0 0 0 210 29 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 25 Charwidth 25 SVRMGR> Rem The times that bstat and estat were run. SVRMGR> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time, 2> to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time 3> from stats$dates; START_TIME END_TIME ------------------------- ------------------------- 28-feb-97 13:20:19 28-feb-97 16:25:53 1 row selected. SVRMGR> SVRMGR> set charwidth 75 Charwidth 75 SVRMGR> Rem Versions SVRMGR> select * from v$version; BANNER ---------------------------------------------------------------- Oracle7 Server Release 7.3.2.3.0 - Production Release PL/SQL Release 2.3.2.3.0 - Production CORE Version 3.5.2.0.0 - Production TNS for HPUX: Version 2.3.2.1.0 - Production NLSRTL Version 3.2.2.0.0 - Production 5 rows selected. SVRMGR> SVRMGR> SVRMGR> spool off;

Back to text