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.
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.
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 .
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.
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. 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
XXXX". ROWIDLONG 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.
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.
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_. 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
MAX_SIZESHARED_POOL_AREA.
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.
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.
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.
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;
|