Oracle7 Administrator's Reference for UNIX

Contents Index Home Previous Next

Tools for Planning and Tuning a Database

This section briefly describes tools to assess your database performance and requirements.

See Also: Your UNIX-specific documentation for detailed explanations of these tools.

TKPROF and EXPLAIN PLAN Utilities

The TKPROF and EXPLAIN PLAN utilities format an output file from the SQL trace facility.

Use TKPROF to:

Use EXPLAIN PLAN to determine which index to use for a query. The proper use of indexes can provide significant performance gains.

See Also: Oracle7 Server Tuning for a full explanation of SQL trace and TKPROF/EXPLAIN PLAN.

Sample TKPROF Output

Figure 1 - 1. Sample Output from the TKPROF Utility

Server Manager

Oracle Server Manager provides a set of system monitors to collect and inspect a range of performance statistics. You can monitor runtime statistics, file I/O, latches, database locks, user sessions, and tablespace fragmentation.

For example, the Server Manager File I/O display lists all the database files accessed by the Oracle Server, along with the statistics reflecting their I/O activity.

Figure 1 - 2. Sample Server Manager Output

See Also: Oracle Server Manager User's Guide for instructions for interpreting the displays.

The utlbstat and utlestat Utilities

The utlbstat and utlestat SQL scripts monitor Oracle database performance and tune Shared Global Area (SGA) data structures. You can also use the scripts to capture snapshots of database performance statistics.

The utilities create tables to help identify problem areas. For example, the latch statistics table shows latch contention and helps you tune the SPIN_COUNT parameter.

Before you run these scripts, set the TIMED_STATISTICS initialization parameter . The scripts are in the $ORACLE_HOME/rdbms/admin directory. Allow time for the database to stabilize before running the scripts.

To initiate the collection of statistics, enter:

$ cd $ORACLE_HOME/rdbms/admin
$ svrmgrl
SVRMGR> CONNECT INTERNAL
SVRMGR> CONNECT SYS/password
SVRMGR> @utlbstat

The utlbstat utility creates a set of tables and views in the SYS account. These tables contain the following snapshots of the state of the system at the beginning of the performance test.

View/Table Name Description
STATS$BEGIN_STATS General stats from v$sysstat
STATS$FILE_VIEW View of File I/O statistics
STATS$BEGIN_FILE Table of File I/O statistics from STATS$FILE_VIEW
STATS$BEGIN_LATCH Latch statistics from v$latch
STATS$BEGIN_ROLL Rollback segment statistics from v$rollstat
STATS$BEGIN_KQRST Dictionary Cache statistics from X$KQRST
STATS$DATES Table with beginning date
Table 1 - 4. Tables Created by the utlbstat Script

To end the collection of statistics, run the utlestat script. Like utlbstat, utlestat creates a set of tables and views in the SYS account. These tables contain a snapshot of the system at the end of the performance test. Table 1 - 5 lists the tables showing the end state of the system.

View/Table Name Description
STATS$END_STATS General stats from V$SYSSTAT
STATS$END_FILE Table of File I/O statistics from STATS$FILE view
STATS$END_LATCH Latch statistics from V$LATCH
STATS$END_ROLL Rollback segment statistics from V$ROLLSTAT
STATS$END_KQRST Dictionary cache statistics from X$KQRST
Table 1 - 5. Tables Created by the utlestat Script

The utlestat utility creates a set of tables in the SYS account that outline the differences between the beginning state and end state statistics. Table 1 - 6 lists the database tables created.

View/Table Name Description
STATS$STATS General system statistics
STATS$FILE File I/O statistics
STATS$LATCHES Latching statistics
STATS$ROLL Rollback segment statistics
STATS$KQRST Dictionary cache statistics
STATS$DATES Table with ending date
Table 1 - 6. Tables Created by utlestat

Finally, utlbstat produces a report in the current directory with performance statistics for each of the tables listed in Table 1 - 6. The report also includes an init.ora parameter listing, and the date of utlbstat and utlestat execution.

The vmstat Utility

The vmstat utility reports process, virtual memory, disk, paging, and CPU activity on BSD UNIX, depending on the switches you supply with the command.

Sample vmstat Swapping Activity Display

The following statement displays a summary of system activity every five seconds:

% vmstat -S 5 9

Figure 1 - 3 shows sample output from the vmstat command.

Figure 1 - 3. Sample Output from the vmstat Command

The w column indicates the number of potential processes that have been swapped out (written to disk). If the value is not zero, swapping is occurring and your system has a memory shortage problem. The si and so columns indicate the number of swap-ins and swap-outs per second, respectively. Swap-outs should always be zero.

The sar Command

The sar command is available on UNIX System V for monitoring swapping, paging, disk, and CPU activity, depending on the switches you supply with the command.

Sample sar Paging Activity Display

The following statement displays a summary of paging activity every ten seconds:

$ sar -p 10 10

Figure 1 - 4 shows sample sar -p output.

Figure 1 - 4. Sample Output from the sar Command

The iostat Utility

The iostat utility reports terminal and disk activity on BSD UNIX. The report from iostat does not include disk request queues, but it shows which disks are busy. This information is valuable when you need to balance I/O loads.

Sample iostat Display

Figure 1 - 5 shows the output from iostat to display a summary of system activity every 5 seconds:

Figure 1 - 5. Sample Output from the iostat Command

The pstat Utility

The pstat -s utility reports information about swap space usage on BSD UNIX. A shortage of swap space can result in the system stopping or slow response time.

Sample pstat Display

Figure 1 - 6. Sample Output from the pstat -s Command

The swap Utility

The swap -l utility reports information about swap space usage on UNIX System V. A shortage of swap space can result in the system hanging and slow response time.

Sample swap Display

Figure 1 - 7. Sample Output from the swap -l Command

The V$SYSTEM_EVENT & V$SESSION_WAIT Tables

The Oracle V$SYSTEM_EVENT table displays information about kernel activity and helps identify potential problem areas in the database. The V$SESSION_WAIT table provides more detail on the events monitored. The parameters these tables measure are described in Oracle7 Server Concepts and Oracle7 Server Reference.

Sample V$SESSION_WAIT Display

Figure 1 - 8 shows sample output from the V$SESSION_WAIT table (time in hundredths of seconds).

Figure 1 - 8. Sample Output from V$SESSION_WAIT Display

Contents Index Home Previous Next