Oracle Performance Tuning, 2nd Edition By Mark Gurry & Peter Corrigan 2nd Edition November 1996 1-56592-237-9, Order Number: 2379 964 pages, $47.95 US

Chapter 10.
Diagnostic and Tuning Tools

In this chapter: MONITOR: Monitoring System Activity Tables SQL_TRACE: Writing a Trace File TKPROF: Interpreting the Trace File EXPLAIN PLAN: Explaining the Optimizer's Plan ORADBX: Listing Events ANALYZE: Validating and Computing Statistics UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots Other Oracle Scripts Some Scripts of Our Own Oracle Performance Manager

This chapter describes a number of Oracle database monitoring and diagnostic tools that help you to examine system and database statistics so that you can tune more effectively. Chapter 9, Tuning a New Database, introduced the memory and disk tuning issues that you can address with these tools. Chapter 11, Monitoring and Tuning an Existing Database, shows how you can use the tools in specific tuning situations. For complete information about tuning tools and their options, consult standard Oracle documentation.

These are the major tools:

MONITOR

A SQL*DBA facility that lets you look at various system activity and performance tables

SQL_TRACE

A utility that writes a trace file containing performance statistics

TKPROF

A utility that translates the SQL_TRACE file into readable output and can also show the execution plan for a SQL statement

EXPLAIN PLAN

A statement that analyzes and displays the execution plan for a SQL statement

ORADBX

An undocumented tool that allows you to track a running process and create a trace file in the same format as the SQL_TRACE trace file. You can then run TKPROF against the trace file to obtain the execution plan details, as well as disk I/O, parsing, and CPU usage.

ANALYZE

A statement that compiles statistics for use by the cost-based optimizer to construct its execution plan. The statement also produces other useful information that can be used to detect chained rows and help with capacity planning.

UTLBSTAT (begin) and UTLESTAT (end)

Scripts that produce a snapshot of how the database is performing from the time you start UTLBSTAT until you run UTLESTAT

Oracle scripts

A number of additional diagnostic and tuning scripts provided by Oracle

Custom scripts

A number of diagnostic and tuning scripts that we have developed ourselves

Enterprise Manager/Performance Pack

An Oracle product introduced with Oracle7.3 that provides some excellent tuning tools, including Oracle Performance Manager, Oracle Trace, and Oracle Expert, which are documented in Appendix D, Oracle Performance Pack.

Diagnostic and tuning tools are also available for the various operating systems that support Oracle. For example, in a UNIX environment, you might use iostat to look at disk activity in your system. In VMS, you might use MON PAGE to examine memory. For information about these system-specific tools, consult your operating system documentation.

In addition to the standard Oracle and operating system facilities, every database administrator develops his or her own set of handy scripts and modified utilities. As was mentioned above, we've included a few of our own favorites in this chapter. We encourage you to save any diagnostic and tuning scripts that you develop in your own system toolbox so that they will be available next time you need them. If you think Oracle DBAs or other users could benefit from what you've learned about improving system performance, we encourage you to send us a copy, and we'll include the best scripts and other tools in the next edition of this book.

The tools described in this chapter help you to identify potential and real database problems. By using them on a regular basis to monitor system activity and performance, you can detect when a potential problem is becoming a real one and when a real problem is turning into a true disaster. You'll notice that some of the tools overlap in function. Choose the tools and options that best suit your style and your system, and use them on a regular basis to monitor system, memory, and disk usage. Things can change rapidly in a dynamic system like Oracle.

MONITOR: Monitoring System
Activity Tables

The SQL*DBA MONITOR facility allows you to monitor activity and performance in your system by looking at the views of a variety of read-only system performance tables that are held in memory. The way you use this facility depends on your particular platform, but its function is consistent across platforms. If you are using a command line interface (e.g., VMS), you'll type a command line in response to the SQLDBA prompt, such as

    SQLDBA> MON FILES

to display information about file activity. If you're running a GUI (e.g., Macintosh), you'll select a MONITOR function, such as Files, from a pull-down menu. Table 10-1 shows the available MONITOR displays.

This chapter and Chapter 11 show how you can use the MONITOR facility to look at memory and disk performance. For complete information about that facility and how you invoke it in your own system, refer to the Oracle Database Administrator's Guide.

Table 10-1:

Monitor Option	Description
FILE IO	Read/write activity for database files in the system. You can select an individual file or set of files. Pay particular attention to the "Request Rate" columns.
SYSTEM IO	Logical and physical reads in writes, cumulative figures, and interval.
LATCH	Internal latches in the system. Pay particular attention to "No Wait Request Misses," which must be kept as low as possible.
LOCK	Locks in the system. We recommend that you use the locking scripts instead because for a reasonably sized system, it is impossible to see all of the locks on the screen at once.
PROCESSES	Process IDs for Oracle and operating system, username, terminal, and executing program.
ROLLBACK SEGMENTS	Extents, transactions, size, writes, and waits for rollbacks. Pay attention to extents (should be as low as possible) and waits (should not be any).
STATISTICS	Run-time statistics on system or session use and performance (includes user, enqueue, cache, and redo).
TABLE	Table statistics. You can display an individual table and its owner.
SESSION	Session ID for user ID, process ID, session status, username, and most recent SQL statement executed.
CIRCUIT	(Multithreaded server only) Status, currently active queue, number of messages, and total bytes transferred for each path.
DISPATCHER	(Multithreaded server only) Total messages, bytes, idle time, busy time, and load.
LIBRARYCACHE	Parts of the cache and the hit ratio. Pay particular attention to the "Gets" ratio, which must be kept as close to 1 as possible.
QUEUE	(Multithreaded server only) Current number of messages in dispatcher queue, total messages, and average wait time.
SHARED SERVER	(Multithreaded server only) Requests from server, idle time, and load (which should be kept balanced).
SQLAREA	SQL statements being executed and contained in cache. Pay attention to whether almost identical statements are in the cache.

NOTE: All MONITOR displays have a default of 5 seconds, which means that the information in the tables is refreshed every 5 seconds. To change the default, issue the following command:

	    		SQLDBA > MON CYCLE number

NOTE: where number is the number of seconds, in the range 1 to 3600.   

SQL_TRACE: Writing a Trace File

The SQL trace facility writes a trace file containing performance statistics for the SQL statements being executed. These include:

• Number of parses, executes, and fetches performed
• Various types of CPU and elapsed times
• Number of physical and logical reads performed
• Number of rows processed
• Number of library cache misses

This trace file provides valuable information that you can use to tune your system. You should be sparing about running it, however. When you have globally enabled SQL_TRACE by setting the appropriate parameters in the INIT.ORA file, your overall response times are likely to degrade as much as 20% to 30%, as well as quickly filling your disk with trace files.

The exact form of the trace file name written by SQL_TRACE is system dependent, but usually it is in the form filename.TRC. You can read this file directly, but it is best to run the TKPROF utility against it to produce an output with more useful tuning information. (TKPROF is discussed later in this chapter; that section shows the particular statistics collected by SQL_TRACE.)

To globally enable SQL_TRACE, you must set a number of INIT.ORA parameters:

Parameter	Setting	Description
SQL_TRACE	TRUE	Enables the trace for all application users. A setting of FALSE disables the trace. FALSE is the default.
USER_DUMP_DEST	directory	The directory where SQL_TRACE writes the trace file. The default is system dependent but generally is the directory that holds your system dumps (e.g., $ORACLE_HOME/rdbms/log).
TIMED_STATISTICS	TRUE	Causes the RDBMS to collect additional timing statistics. These timing statistics are useful to SQL_TRACE and also to the SQL*DBA MONITOR command. The default is FALSE.
MAX_DUMP_SIZE	number	Limits the physical size of the trace file to the specified number of bytes. If you enable the SQL_TRACE parameter for the entire database, this option helps control the amount of disk space used. To find out what size to specify, find out the number of operating system blocks available in your system. If SQL_TRACE runs out of space, it will truncate your output; you'll have to allocate more space and start again.

The way you invoke SQL_TRACE for individual Oracle tools and user sessions depends on the Oracle program you are running:

For:	Do This:
SQL*Forms	RUNFORM formname usercode/password -S (Version 3 and later)
SQL*Plus	ALTER SESSION SET SQL_TRACE TRUE
SQL*Reportwriter	Create a field called SQL_TRACE and a group report with an attribute of CHAR(40). Specify the following statement against the column: &SQL ALTER SESSION SET SQL_TRACE TRUE
Pro* tools	EXEC SQL ALTER SESSION SET SQL_TRACE TRUE

Turning on SQL_TRACE for a Running Session

A great new feature that snuck in with the Oracle7.2 release is the ability to turn SQL_TRACE on for a user session other than your own. One catch is that you must be logged on as Oracle to run it. This feature can be used in the same way as ORADBX; you identify a problem user and create a trace file of all of his or her database activities. You can run TKPROF against the trace file to obtain the SQL statements execution plans, as well as many run-time statistics such as disk I/Os, number of reads from Oracle's buffer cache, and CPU utilization.

To turn on SQL_TRACE for another user, you must provide the SID and the serial number of the problem user from the V$SESSION table. If you know the operating system user, you can perform the following query.

The process ID is obtained because most machines have the process ID as part of the trace file name. For example, most UNIX machines have the format ora_23526.trc, where 23526 is the operating system process ID.

# usersid.sql
SELECT  sid, serial#, osuser, process
   FROM v$session
  WHERE osuser='gurrym';
SID          SERIAL#             OSUSER     PROCESS
-----------------------------------------------------
20             26               gurrym       23526

You can also pick out the user that has the most disk I/Os and trace what statements they are running using the command

# maxsidio.sql
 SELECT ses.sid, ses.serial#, ses.osuser, ses.process
   FROM v$session ses, v$sess_io sio
  WHERE ses.sid  = sio.sid
    AND nvl(ses.username,'SYS') not in (`SYS', `SYSTEM')
    AND sio.physical_reads = (SELECT MAX(physical_reads)
                                FROM v$session ses2, v$sess_io sio2
                               WHERE ses2.sid = sio2.sid
                                 AND ses2.username 
                                  NOT IN (`SYSTEM', `SYS'));
SID                     SERIAL#             OSUSER     PROCESS 
----------------------------------------------------------------
41                        46               corriganp      12818

You can now turn SQL_TRACE on for the troublesome user with the following command; 41 is the SID and 46 is the serial number.

EXECUTE dbms_system.set_sql_trace_in_session(41,46,TRUE);

The SQL_TRACE output is automatically turned off when the user being traced logs off the database. To to turn it off explicitly, specify

EXECUTE dbms_system.set_sql_trace_in_session(41,46,FALSE);

You will find the trace file in the directory specified by the INIT.ORA parameter USER_DUMP_DEST. You can run TKPROF against the trace output file to obtain the EXPLAIN PLAN details and run-time statistics. (See the next section for more details on using TKPROF.)

TKPROF: Interpreting the Trace File

The TKPROF utility translates a trace file to a readable format. You can run TKPROF against a trace file that you have previously created using SQL_TRACE or ORADBX, or you can run it while the program that is creating the trace file is still running. You can optionally tell TKPROF to invoke the EXPLAIN PLAN statement (described in the next section) for the statements being analyzed.

You invoke TKPROF by issuing the command

TKPROF tracefile listfile [SORT = parameters] [PRINT = number]
      [INSERT =  FILENAME] [SYS = YES/NO] [TABLE = schema.table]
      [EXPLAIN = username/password] [RECORD = filename]

where

tracefile

Is the name of the trace file containing the statistics gathered by the trace facility. The trace file is stored in the directory specified by the INIT.ORA parameter USER_DUMP_DEST

listfile

Is the name of the file where TKPROF writes its output.

SORT=parameters

Is the order in which to display output. You can specify, as parameters, any of the statistics collected by SQL_TRACE. (These are listed in Table 10-3.) TKPROF outputs statistics in the descending order of the values of these parameters. For example, if you specify

SORT = EXECPU

TKPROF first displays statistics for the statements that had the worst EXECPU values (that is, required the most CPU time).

If you specify more than one parameter, for example,

SORT = (FCHPHR, PRSCPU)

TKPROF adds the statistics you specify. The output that appears first is for statements in which the sum of these two statistics was the worst.

PRINT = number

Is the number of statements included in the output. You might want to limit the amount of output to the worst-performing statements.

INSERT = filename

Creates a SQL script that can be used to store the trace file statistics in the database. The script name is identified by filename.

SYS = yes/no

Allows you to include Oracle's own dictionary statements. Including these statements makes your output considerably longer. It is usually best to set SYS=NO.

TABLE = schema.table

If many users are using TKPROF at the same time, you may get situations in which the users are interfering with each other's output. This is because TKPROF uses a single table in the database to store its EXPLAIN PLAN details. The table is called PROF$PLAN_TABLE. If the table doesn't exist, TKPROF creates it for you, uses the table, and then drops it. If the table does exist, TKPROF removes the rows from the table, which can create problems if there are multiple users. This option allows you to specify another table name, for example, MARKG_PLAN, to avoid conflicts.

RECORD = filename

Allows you to store a SQL script from your trace file so that you can replay your commands and test the effect of changing indexes and changing the optimizer. The recursive calls are removed from the script automatically.

EXPLAIN = username/password

Runs the EXPLAIN PLAN statement on all of the statements in the trace file, logging in under the account specified.

For example, you might specify

TKPROF 12_12626.TRC TRACE.LIS SORT=(EXECPU) EXPLAIN = username/password SYS=NO

When you run TKPROF, it interprets the trace file and puts the readable output in the file you specify. TKPROF produces a formatted listing. The rows and columns in the TKPROF output have the meanings shown in Table 10-2.

Table 10-2:

Row/Column	Description
Parse	Statistics for the parse steps performed by SQL statements. Parsing checks for security, and the existence of tables, columns, and other objects being referenced by your SQL.
Execute	Statistics for the execute steps performed by SQL statements. UPDATE, DELETE, and INSERT statements show the number of rows processed here. SELECT statements list the number of selected rows.
Fetch	Statistics for the fetch steps performed by SQL statements. SELECT statements show the number of rows processed here. UPDATE, INSERT, and DELETE do not return rows for this value.
count	Number of times a SQL statement is parsed or executed, plus the number of times a fetch is performed in order to carry out the operation.
cpu	CPU time for all parses, executes, and fetches in seconds.
elapsed	Elapsed time for parses, executes, and fetches in seconds.
disk	Number of data blocks read from disk for each parse, fetch, or execute. If a single multiblock read returns eight blocks, this figure is incremented only once for the physical read. Most disks operate at 50+ I/Os per second, so if you divide the figure by 50, you can usually get a good idea of the time taken to perform the disk reads.
query	Number of times a buffer was returned in consistent mode, that is, the data is for query only and has not been modified since the SELECT statement started.
current	Number of times a buffer was retrieved for INSERT, UPDATE, or DELETE.
rows	Number of rows processed by a SQL statement (only queries, not subqueries). Unfortunately, this figure does not include the rows returned by subqueries. The number of rows returned appears in the fetch step for the SELECT statement and in the execute step for the INSERT, UPDATE, and DELETE statements.

Following is an example of TKPROF output illustrating the rows and columns shown in Table 10-2. Notice that the cpu and elapsed times are 0.00. This is because the INIT.ORA parameter TIMED_STATISTICS is set to FALSE. If you set it to TRUE, you get more information, but it does have a 5% to 10% performance drag on your machine.

  SELECT     D.dept_name, E.surname
    FROM     emp e , dept D
   WHERE     D.dept_no like `ACC%'
     AND     D.dept_no = E.dept_no
ORDER BY     D.dept_name, E.surname;
              count   cpu    elapsed   disk   query     current    rows
    Parse:      1     0.00    0.00      3      11          0         0
    Execute:    1     0.00    0.00      0       0          0         0
    Fetch:      1     0.00    0.00      9      29          4        90
 
   Misses in library cache during parse: 1
   Parsing user id: 8

Rows Execution Plan

--------- ------------------------------------------------------------

90 MERGE JOIN

4 SORT JOIN

4 TABLE ACCESS BY ROWID DEPT

4 INDEX RANGE SCAN DEPY_IDX2

86 SORT JOIN

86 TABLE ACCESS BY ROWID EMP

86 INDEX RANGE SCAN EMP_IDX2

You can select any of the statistics shown in Table 10-3, computed by SQL_TRACE and interpreted by TKPROF, in the SORT clause. Of these, EXECPU (if you have set TIMED_STATISTICS=TRUE), PRSDSK, FCHDSK, and EXEDSK are probably the most useful. EXECPU shows the total CPU time spent executing the statement; and PRSDSK, FCHDSK, and EXEDSK record the number of disk reads.

Table 10-3:

Parameter	Description
PRSCNT	Number of times parsed
PRSCPU	CPU time spent parsing
PRSELA	Elapsed time spent parsing
PRSDSK	Number of physical disk reads during parse
PRSQRY	Number of consistent mode reads during parse
PRSCU	Number of current mode block reads during parse
PSRMIS	Number of library cache misses during parse
EXECNT	Number of executes
EXECPU	CPU time spent executing
EXEELA	Elapsed time spent executing
EXEDSK	Number of physical disk reads during execute
EXEQRY	Number of consistent mode block reads during execute
EXECU	Number of current mode block reads during execute
EXEROW	Number of rows processed during execute
EXEMIS	Number of library cache misses during the execute
FCHCNT	Number of fetches
FCHCPU	CPU time spent fetching
FCHELA	Elapsed time spent fetching
FCHDSK	Number of physical reads during fetch
FCHQRY	Number of consistent mode blocks read during fetch
FCHCU	Number of current mode blocks read during fetch
FCHROW	Number of rows fetched

TKPROF formulates its output first for each individual SQL statement and then at the user session level. It often pays to look at the "Overall Totals for All Statements" before you examine individual statement performance. The overall totals will tell you what general problems exist. Another time saver is to scan the TKPROF output file for keywords. For example, in UNIX, you can scan the file using grep FULL trace.lis. The word FULL is for full table scans.

Here are some rules for interpreting these statistics; these rules apply to all types of systems and jobs:

• If the cpu, elap, and disk figures in the Parse row are high relative to those in the Execute and Fetch rows, you probably need to enlarge your SHARED_POOL_SIZE so that you'll be able to store more of the dictionary in memory. This will reduce the required amount of disk I/O.
• If the library cache misses are greater than 5% of the count, you should tune your shared pool (see Chapter 11). The library cache misses are listed between the statistics and the EXPLAIN PLAN details in your TKPROF output.
• If the Parse count figure is relatively high, you may need to do open cursor tuning on the application.
• If the sum of Execute disk + Fetch disk is more than 10% of the sum of Execute query + Execute current + Fetch query + Fetch current, the hit ratio of finding data in the cache is too low. Consider tuning the statement by adding an index or by using a more appropriate index, by rewriting the statement, by using a hint, or by using a different optimizer option (e.g., FIRST_ROWS instead of ALL_ROWS for an online system). You will often get a further improvement by increasing the buffer cache using the INIT.ORA parameter DB_BLOCK_BUFFERS.
• If the Fetch count is about twice Fetch rows, and if PL/SQL is being used, it's likely that implicit cursors are in use. (Implicit cursors are SQL statements that are not declared; they are less efficient than explicit cursors.) Ask your analysts/programmers to investigate.
• If the total of the elap column is greater than 2.5 seconds, and if the query is an interactive, online one, investigate the SQL statements. The response times indicated by these statistics exceed the standards for most sites.
• If Execute query is high, and Execute rows and Execute current are markedly lower, investigate your indexes. Your tables probably do not have enough indexes or have inadequate indexes defined for them.

The following additional rules apply only to online transaction processing systems that require excellent response time (e.g., 2.5 seconds elapsed for all online queries):

• Make sure all Execute cpu times are less than 1 second.
• Make sure Parse cpu times are less than 1 second. If not, tune your shared pool, as documented in Chapter 11.
• Allow full table scans only on small tables. Don't allow them on tables with more than 200 rows or on tables that are frequently used in multiple-table joins.
• Remove all unnecessary calls to the system table, DUAL.
• Declare all PL/SQL SELECTs.
• Make sure Oracle chooses the appropriate driving table (described in the section called "The driving table" in Chapter 6, Tuning SQL).

Take Care in Using TKPROF

TKPROF has a few traps that you'll want to avoid:

• If any changes have occurred to the schema, such as adding an index or reanalyzing tables and/or indexes, the EXPLAIN PLAN results may be different from what they were when the trace file was created.
• If you have obtained your trace file from the production database and you are running TKPROF against your test or development database, the execution plans may be different from what they were when the trace file was created. This is the result of different statistics on tables and indexes. Other factors that may cause the cost-based optimizer to act differently are the DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT setting; the latter is used to determine whether a full table scan is preferable to an index search.
• TKPROF does not know what data type bind variables are. It assumes VARCHAR2. This may cause your EXPLAIN PLAN to appear as though an index is not being used when in fact it is.
• The optimizer behavior can change from one release of Oracle to the next, so you must make sure that you are running your TKPROF against the same version of Oracle that was used to create the trace file.
• Do not use a different optimizer mode from the one that was used when the trace file was created. RULE acts differently in the cost-based optimizer. FIRST_ROWS also behaves differently from ALL_ROWS.

EXPLAIN PLAN: Explaining
the Optimizer's Plan

EXPLAIN PLAN is a statement you can include in your SQL to explain the execution plan, or retrieval path, that the optimizer will use to do its database retrieval. The execution plan is the sequence of physical operations that Oracle must perform to return the data requested. (See the discussion of the optimizer in the section called .) By looking at the execution plans for SQL statements, you can see which ones are inefficient, and you can compare alternatives to find out which will give you better performance.

If you are going to issue the EXPLAIN PLAN statement, you need to create a table called PLAN_TABLE that will hold the data to be displayed. You do this by running a script. The name of this script is system dependent but is likely to be UTLXPLAN.sql. (You can use your own table instead, if you define it to be identical to the PLAN_TABLE and reference it in the INTO clause described below.)

Use the following syntax to run an explain:

EXPLAIN PLAN [SET STATEMENT_ID [=] string literal>]
[INTO table_name]
FOR sql_statement

where

STATEMENT_ID

Is a unique optional identifier for the statement

INTO

Allows the user to save the results of the analysis in the specified table. The table must conform to the format for the table used to store the analysis (see the description of the table format below). If this clause is not specified, the system will then attempt to store the information in a table named <user_id>.PLAN_TABLE. If the explicit or implicit table does not exist, the EXPLAIN command will fail.

sql_statement

Is an INSERT, DELETE, UPDATE, or query statement

The table that is used to represent the plan information consists of the following fields:

STATEMENT_ID

An identifier associated with the statement. If this identifier is not set by the user, it will be null. Note that a user can identify a statement by the time-stamp field.

TIMESTAMP

The date and time when the statement was analyzed

REMARKS

Any comment the user wishes to associate with this step of the analysis

OPERATION

The name of the operation being performed. The following table lists the operations described by the facility. The operation column on the first row of the EXPLAIN PLAN contains one of the following: DELETE STATEMENT, INSERT STATEMENT, SELECT STATEMENT, or UPDATE STATEMENT.

The operation shown when counting the number of rows returned by a query (i.e., SELECT COUNT(*)) is SORT. Note, however, that the table will not really be sorted. This is because of the way that COUNT is implemented internally.

OPTIONS

Option that modifies the operation (e.g., OUTER option on join operations, rationale for sorting, type of index scan, type of filter, etc.). Table 10-4 provides a list of the options for the operations that have options.

OBJECT_NODE

Name of the node that owns the database object. The column may also contain the database link. For the parallel query option, it describes the order in which the output from the query servers is processed.

OBJECT_OWNER

Name of the schema (user) that owns the database object.

OBJECT_INSTANCE

Number corresponding to the ordinal position of the object as it appears in the original query. Numbering proceeds from left to right, from outer to inner, with respect to the original query text.

OBJECT_NAME

Name of the table or index.

OBJECT_TYPE

Modifier that provides descriptive information about the database object (e.g., NON-UNIQUE for indexes).

OPTIMIZER

Current mode of the optimizer: RULE, CHOOSE, FIRST_ROWS, ALL_ROWS.

SEARCH_COLUMNS

Not used for the later releases of Oracle7 and 8.

ID

Number assigned to this operation in the tree. Corresponds to a preorder traversal of the row source tree.

PARENT_ID

Number assigned to the previous operation that receives information from this operation. This field combined with the ID field allows users to do a treewalk of the specified plan with the CONNECT BY statement.

POSITION

Position this database object occupies for the previous operation. Prior to Oracle7.3, the first row returned contained the cost of the statement (if the statement used the cost-based optimizer). The lower the cost, the more chance there is that your statement will perform well. The rule-based optimizer has a null in this column on the first row. To get the cost, add DECODE(id, 0, `Cost = `|| position) to your SELECT statement on the plan_table.

COST

Stores the cost of the statement if you are using the cost-based optimizer. This column replaces the value that was stored in the position column prior to Oracle7.3.

CARDINALITY

Used by the cost-based optimizer. It stores the number of distinct values that are likely to be returned, which will assist with determining whether to use a full table scan or an index lookup.

BYTES

Used by the cost-based optimizer to determine how many bytes are likely to be returned by the statement. The optimizer uses this information when determining the optimal access path.

OTHER_TAG

Introduced in Oracle7.3 to assist with parallel query tuning. The values and their meanings are shown in the following table. See Chapter 13, Tuning Parallel Query, for a detailed explanation of each option.

SERIAL or NULL

The SQL is executed locally using the parallel query option.

SERIAL_FROM_REMOTE

The SQL is executed at a remote site.

PARALLEL_COMBINED_WITH_PARENT

The table is scanned in parallel; the next step will be handled by the same parallel process.

PARALLEL_COMBINED_WITH_CHILD

The child process will scan the table in process and also perform the next step (e.g., a sort).

PARALLEL_TO_SERIAL

The table is scanned by using multiple processes, but the data is passed to a single process.

PARALLEL_TO_PARALLEL

The table scan is performed by a number of processes running in parallel, and the data is passed to a number of processes, also running in parallel.

PARALLEL_FROM_SERIAL

Many processes perform the table scan and hand the output to a single process.

OTHER

Other information specific to the row source that a user may find useful--for example, the SELECT statement to a remote node, or (with the parallel query option) the statement that is performed for each of the query servers.

Table 10-4: EXPLAIN PLAN Operations

Operation	Option	Description
AND-EQUAL		A retrieval using the intersection of rowids from index searches. Duplicates are eliminated. This operation is used for single-column accesses.
CONNECT BY		A retrieval that is based on a tree walk. The SQL will contain a CONNECT clause.
CONCATENATION		This is essentially a UNION ALL operation of the sources. Used for OR operations.
COUNT		This is a node used to count the number of rows returned from a table. Used for queries that use the ROWNUM metacolumn.
	STOPKEY	Rows are counted and the WHERE clause contains a ROWNUM < number, where number is any digit.
FILTER		A restriction of the rows returned from a table.
FIRST-ROW		A retrieval of only the first row.
FOR UPDATE		A retrieval that is used for updating. The SQL will contain a FOR UPDATE clause.
INDEX		A retrieval from an index.
	UNIQUE SCAN	A single rowid returned from a unique or primary key index.
	RANGE SCAN	One or more rows returned from an index in ascending order.
	RANGE SCAN DESCENDING	One or more rows returned from an index in descending order.
INTERSECTION		A retrieval of rows common to two tables.
MERGE JOIN		A join using merge scans. Two or more selects are performed. The rows from each select are individually sorted and then merged.
	OUTER	A merge join that contains an outer join.
MINUS		A retrieval of rows in source 1 table, not in source 2 table. Duplicates are eliminated.
NESTED LOOPS		A join using nested loops. Each value in the first subnode is looked up in the second subnode. Often used when one table in a join is indexed and the other is not. Use this in preference to a merge join for online systems.
	OUTER	A nested loop that contains an outer join.
PROJECTION		A retrieval of a subset of columns from a table.
REMOTE		A retrieval from a database other than the current database.
SEQUENCE		An operation involving a sequence table.
SORT		A retrieval of rows ordered on some column or group of columns.
	AGGREGATE	A single row is retrieved from a GROUP BY function.
	UNIQUE	Rows are sorted, and duplicates are eliminated.
	GROUP BY	Rows are sorted, and one or more rows are returned to satisfy a GROUP BY clause.
	JOIN	Rows are sorted in readiness for merging rows in a merge join.
	ORDER BY	Rows are returned in a particular order using ORDER BY.
TABLE ACCESS		A retrieval from a base table.
	FULL	A full table scan.
	CLUSTER	A selection of rows from a table based on the key of an indexed cluster.
	HASH	A selection of rows from a table based on the key of an indexed cluster.
	BY ROWID	A retrieval of a row from a table using a rowid. This usually implies an index lookup in which the rowid is obtained from the index.
UNION		A retrieval of unique rows from two tables.
VIEW		A retrieval from a virtual table.

EXPLAIN PLAN Table Definition

The following example shows the CREATE TABLE statement definition for the EXPLAIN PLAN table.

CREATE TABLE plan_table 
 
statement_id     VARCHAR2(30)
timestamp        DATE,
remarks          VARCHAR2(80),
operation        VARCHAR2(30),
options          VARCHAR2(30),
object_node      VARCHAR2(128),
object_owner     VARCHAR2(30),
object_name      VARCHAR2(30),
object_instance  NUMERIC
object_type      VARCHAR2(30),
optimizer        VARCHAR2(255),
search_columns   NUMERIC
id               NUMERIC,
parent_id	       NUMERIC,
position         NUMERIC
cost             NUMERIC,
cardinality      NUMERIC,
bytes            NUMERIC,
other_tag        VARCHAR2(255),
other            LONG);

Interpreting EXPLAIN PLAN Output

After you have run the EXPLAIN PLAN, it's important to interpret the results correctly. For the most usable output format, run the following statement against your PLAN_TABLE:

# plan.sql
SELECT LPAD(` `, 2*level) || operation ||' ` || 
         options ||' ` || object_name "Execution Plan"
  FROM plan_table
CONNECT BY PRIOR ID = parent_id
  START WITH ID = 1;

Figure 10-1 shows the effect.

Figure 10-1. Interpreting EXPLAIN PLAN output

Some of the more common EXPLAIN PLAN outputs are described in the following sections.

Primary or UNIQUE key index lookup

Primary and UNIQUE key lookups, shown in the example below, are extremely efficient. Use them whenever possible.

SELECT surname 
  FROM emp
 WHERE emp_no = 12111;
SELECT STATEMENT
         TABLE ACCESS           BY ROWID         emp
               INDEX            UNIQUE SCAN      emp_pk

Non-UNIQUE index lookup

It is usually better to use an index than to perform a full table scan. Make sure that the most appropriate index on the table is being used.

SELECT surname 
  FROM emp
 WHERE dept_no = 30;

SELECT STATEMENT

TABLE ACCESS BY ROWID emp

INDEX RANGE SCAN emp_idx2

Index-only lookup

For an index-only lookup, all of the columns in the SELECT statement and the WHERE clause have to be contained in the index. This is a very efficient way of accessing data. The data is also returned in the order of the index (ascending by default). In the following statement, the data would be returned in dept_no order:

 SELECT dept_no 
   FROM emp
  WHERE dept_no = 30;

SELECT STATEMENT

INDEX RANGE SCAN emp_idx2

ORDER BY without using an index to order the data

As was mentioned, using an index to sort your output is the most efficient way to produce sorted data. This is not always possible, given the column being selected and the column in the ORDER BY. The following output performs a separate sort process. If the statement read ORDER BY dept_no, surname, and the dept_no index was expanded to include the surname column (dept_no, surname), the sort could be avoided.

 SELECT surname 
   FROM emp
  WHERE dept_no = 30
  ORDER BY surname;
 SELECT STATEMENT
   SORT (ORDER BY)
     TABLE ACCESS        ROWID            emp
      INDEX              RANGE SCAN       emp_idx2

Full table scan

Avoid full table scans unless you are using the parallel query option, you are retrieving 15% or more of the rows in the table, or the table has very few rows. For example,

SELECT surname 
  FROM emp
 WHERE dept_no = 30;

SELECT STATEMENT

TABLE ACCESS FULL emp

Sort merge

Sort merges are usually best suited to batch processing, not online screens. Sort merges obtain all of the rows from two or more tables and then sort and merge all of the data before returning them to the transaction that is running the query. Online screens run better using nested loops, where rows are joined and presented to the transaction one at a time. If you set the OPTIMIZER_MODE parameter to ALL_ROWS, sort merges will usually be performed. If you set it to FIRST_ROWS, nested loops will usually be performed. This enables online screens to receive one buffer full of information (perhaps about 20 rows) without having to sort and merge all of the rows in the table.

The USE_MERGE hint will force a merge join to be used.

SELECT dept.dept_name, emp.surname 
  FROM emp, dept
 WHERE dept.dept_no  = 30
   AND emp.dept_no   = dept.dept_no;

SELECT STATEMENT

MERGE JOIN

SORT JOIN

TABLE ACCESS BY ROWID dept

INDEX RANGE SCAN depy_idx2

SORT JOIN

TABLE ACCESS BY ROWID emp

INDEX RANGE SCAN emp_idx2

Nested loop

As was mentioned, a nested loop is usually preferable to a merge join when many rows are going to be returned to an online screen. You can use the USE_NL hint in your statement to force a nested loop to be used.

SELECT dept.dept_name, emp.surname 
  FROM emp, dept
 WHERE dept.dept_no  = 30
   AND emp.dept_no   = dept.dept_no;
SELECT STATEMENT
    NESTED LOOP
         TABLE ACCESS           BY ROWID          dept
           INDEX                RANGE SCAN        depy_idx2
         TABLE ACCESS           BY ROWID          emp
           INDEX                RANGE SCAN        emp_idx2

Running EXPLAIN PLAN Against
Problem Statements

The following script detects all SQL statements that will have an expected run time of greater than 2.5 seconds and runs EXPLAIN PLAN against them. Assume that approximately 50 I/Os can be performed per second. Before you can run your main query, you will have to create a file called explain.sql as follows:

# explain.sql
SELECT LPAD(` `, 2*level) || operation ||' ` || 
         options ||' ` || object_name "Execution Plan"
  FROM plan_table
CONNECT BY PRIOR id = parent_id
  START WITH ID = 1;

Now run the main script, which creates a temporary file called expstat.sql. That script is run to create a file called expstat.lis, which you can view using your favorite editor.

SET LINESIZE 2000
SET ARRAYSIZE 1
SPOOL expstat.sql
COLUMN nl NEWLINE
SELECT `delete from plan_table;' nl,
       `explain plan for ` nl,
       sql_text||';' nl,
       `start explain.sql' nl
 FROM  v$sqlarea
WHERE  disk_reads / executions > 250
ORDER  BY disk_reads / executions desc;
SPOOL  OFF
SPOOL  expstat.lis
START  expstat.sql
SPOOL  OFF

ORADBX: Listing Events

ORADBX is an undocumented Oracle product that is available on most Oracle platforms used by Oracle consultants and Oracle support. Be careful with this product, and don't experiment with it. If you use it incorrectly, it can totally screw up your database. If you use the event 10046, you can get some valuable tuning information. You can obtain a full list of all the events that can be set by viewing the file oraus.msg. Most of the 10000-10999 range of events may be set using ORADBX. Please don't set any other event except for 10046 without permission from Oracle support.

The 10046 event has the ability to track the SQL statements that a user is running. If you spot an Oracle user on your database who is using a large amount of CPU, you can trace him or her to see exactly what SQL is being run, as well as the translated bind variable values being used. This event also provides the full execution path of the statement and which indexes, if any, are being used. It will also provide you with CPU usage if you have set the TIMED_STATISTICS parameter to TRUE.

Before you can use ORADBX, you must make the executable. Under UNIX, use the following make command. You will find that oradbx.o and oracle.mk are required to make the oradbx executable in the $ORACLE_HOME/rdbms/ admin/lib directory. You may wish to copy the oradbx executable across to the $ORACLE_HOME/bin directory to place it into your own path.

make -f oracle.mk oradbx

You can now start using ORADBX. To run the statement, select an Oracle shadow process of one of your users, typically the user who is using the most CPU or the most disk I/Os. For example, obtain the users in question for the PERS database instance on some UNIX systems, you'll run an operating command such as the following:

ps -ef | grep oracle | grep PERS > all_PERS_users.lis

This will list all the shadow processes, as well as other Oracle processes for the PERS database instances, and place them into a file called all_PERS_users.lis. You can now sort the file to see the largest CPU users first:

sort +6 -r all_PERS_users.lis > high_PERS_cpu_first.lis

You identify the process ID of the shadow process and run ORADBX against it. The following example assumes that the process ID that you have identified is 8972:

oradbx
debug 8972
event 10046 trace name context forever, level 12
exit

A trace file is created in the directory as specified by the INIT.ORA parameter USER_DUMP_DEST. The name of the file varies across operating systems but it usually contains the process ID and has a trc suffix (for example, ora_PERS_8972.trc). You run the TKPROF utility (described earlier) against the file to obtain the SQL statements, their execution path, and a range of statistics.

tkprof ora_PERS_8972.trc 8972.lis explain=owner/pass sys=no

In this example, ora_PERS_8972.trc is the trace file in the USER_DUMP_DEST directory; 8972.lis is the output list file, which you can name according to any naming convention that suits you; explain=owner/pass is the owner name and password of the owner of the database objects being used (or a user that has grants and synonyms to the tables); and sys=no tells TKPROF to include only the user SQL statements and not those performed against the Oracle dictionary tables.

To turn ORADBX off for the process, specify

oradbx
>debug 8972
>event 10046 trace name context off
>exit

NOTE: If you start ORADBX on a process that has already parsed the untuned SQL, you will not get the details of the statement. The statement must be parsed and run after ORADBX has been turned on for the process ID.

If you are using UNIX, you'll find the next script useful in quickly turning the tracing on and off for a selected process ID:

# oradbx.sh
 
#!/bin/ksh
#
#  This script turns on/off tracing a selected use shadow process.
#  You must have oradbx installed before this script will run.
#
#  usage: trace.sh pid <off>
#
if [ "$2" != "off" ]; then
 
oradbx <<EOD
debug $1
event 10046 trace name context forever, level 12
exit
EOD
 
else
oradbx <<EOD
debug $1
event 10046 trace name context off
exit
EOD
 
fi

ANALYZE: Validating and Computing Statistics

The ANALYZE statement introduced in Chapter 6 is a SQL*Plus statement that allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You issue ANALYZE as follows:

    ANALYZE  object-clause  operation  STATISTICS
        [VALIDATE STRUCTURE [CASCADE]]
        [LIST CHAINED ROWS [INTO table]]

where

object-clause

TABLE, INDEX, or CLUSTER, followed by a name. You can run ANALYZE on any one of these.

operation

You can choose one of these operations:

COMPUTE

Calculates each value. This option provides the most accurate statistics but is the slowest to run.

ESTIMATE

Estimates statistics by examining data dictionary values and performing data sampling. This option provides less accurate statistics but is much faster. The default estimate number of rows is 1064, which is usually far too few. You should use COMPUTE if you have sufficient time to run the analysis of use the SAMPLE command (see below).

DELETE

Removes all table statistics (freeing space), for example, ANALYZE TABLE EMP DELETE STATISTICS

Earlier versions of Oracle7 produced unpredictable results when the ESTIMATE option was used. It is best to compute your statistics using the following:

ANALYZE TABLE emp COMPUTE STATISTICS

If you don't have time to compute the statistics and you are using a version of Oracle earlier than Oracle7.3, estimate at least 50% of your rows using

ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 50 PERCENT

If you are using Oracle7.3 or later, analyze at least 33% of your rows.

Oracle provides you with a procedure that allows you to analyze an entire schema. Specify the following:

EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA(`SCOTT','COMPUTE'); 

where SCOTT is the owner of the tables, clusters, and indexes, and COMPUTE is the type of analysis that you require.

ANALYZE produces the following statistics; depending on whether you have specified COMPUTE, or ESTIMATE, these statistics will be exact or estimated.

For tables

Number of rows
Number of blocks that have been used
Number of blocks unused
Average available free space
Number of chained rows
Average row length

For indexes

Index level
Number of leaf blocks
Number of distinct keys
Average number of leaf blocks/key
Average number of data blocks/key
Clustering factor
Minimum key value (exact only)
Maximum key value (exact only)

For columns

Number of distinct values per column
Second smallest value per column
Second largest value per column

For clusters

Average cluster key chain length

ANALYZE stores these statistics in the views, USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES, and USER_HISTOGRAMS. (The last view is available only in Oracle7.3 and later.)

ANALYZE has many uses. In addition to its use in optimization, ANALYZE can look for chained rows (a performance drain on the system). See Chapter 11 and consult your documentation for complete information.

UTLBSTAT.sql and UTLESTAT.sql:
Taking Snapshots

Oracle provides the SQL scripts UTLBSTAT.sql and UTLESTAT.sql. They allow you to take a snapshot of how the database is performing. They provide information that may help you to identify problems and give you guidance about what needs tuning. The scripts are sometimes criticized because they identify performance problems but do not give very much advice on how to repair the problem. UTLBSTAT tells Oracle to start writing system statistics into a table. UTLESTAT stops this writing and displays the report. Keep in mind that the statistics that these scripts provide are system-wide, but only for the interval between UTLBSTAT and UTLESTAT.

Be sure to run these scripts when your system is doing ordinary processing. If you run them when there are no users on the system, they will give unrealistic and misleading results. You may also get distorted results if you run them all day (24 hours) on a system in which users are normally logged on for only the work day (8 hours). Most sites run UTLBSTAT and UTLESTAT from 10:00 a.m. to 12:00 p.m. and again from 2:00 p.m. to 4:30 p.m. These periods normally reflect peak production load and consistent application transaction activity.

You should run UTLBSTAT and UTLESTAT logged on as INTERNAL in SQL*DBA. UTLBSTAT.sql creates a set of tables and views that contain a snapshot of database performance statistics. The table and view names are as follows:

        View/Table Name          Description
        ---------------          -----------
        stats$begin_dc      Dictionary cache statistics from v$rowcache
        stats$begin_event   System wait statistics from v$system_event
        stats$begin_file    Table of file i/o statistics from stats$file_view
        stats$begin_latch   Latch statistics from v$latch
        stats$begin_lib     Library cache statistics from v$librarycache
        stats$begin_roll    Rollback segment statistics from v$rollstat
        stats$begin_stats   System stats from v$sysstat
        stats$file_view     File I/O statistics from v$filestat,
                                 v$datafile, ts$, file$
        stats$dates         Contains beginning date and time

In addition, UTLBSTAT.sql creates a set of tables that contain the ending snapshot of the database performance statistics:

        Table Name              Description
        ----------              -----------
        stats$end_dc            Dictionary cache stats from v$rowcache
        stats$end_event         Wait statistics from v$system_event
        stats$end_file          File I/O stats from stats$file_view
        stats$end_latch         Latch statistics from v$latch
        stats$end_lib           Library cache statistics from
                                          v$librarycache
        stats$end_roll          Rollback segment stats from v$rollstat
        stats$end_stats         System stats v$sysstat

UTLESTAT.sql populates the end statistics tables and creates an additional set of tables that contain the difference between the beginning statistics and the ending statistics:

        Table Name              Description
        ----------              -----------
        stats$dc                Dictionary cache statistics
        stats$event             Systemwide wait statistics
        stats$files             File I/O statistics
        stats$latches           Latch statistics
        stats$lib               Library cache statistics
        stats$roll              Rollback segment statistics
        stats$stats             System statistics
        stats$dates             Contains ending date and time

UTLESTAT.sql creates a report in your current directory with the following database performance statistics:

        Library Cache Stats
        System Wide Statistic Totals
        System Wide Event Stats
        Average Length of Dirty Buffer Write Queue
        File I/O Stats
        Tablespace I/O Stats
        Willing-To-Wait Latch Stats
        No-Wait Latch Stats
        Rollback Segment Stats
        Init.ora Parameters
        Dictionary Cache Stats
        Date/Time of executing utlbstat/utlestat 

In testing, we have found that UTLBSTAT and UTLESTAT incur no system overhead.

To start the snapshot, type

    SQLDBA> @UTLBSTAT

Let UTLBSTAT run for as long as you want to gather information. Then end the snapshot by typing

    SQLDBA> @UTLESTAT

Note that you must set the INIT.ORA parameter TIMED_STATISTICS to TRUE (the default is FALSE) to produce the timing statistics (e.g., "current lock get time") shown in the output. We have found that TIMED_STATISTICS does incur a 5% to 10% performance overhead, so don't leave it on in your production database for a prolonged period.

SQLWKS> Rem The total is the total value of the statistic between the time
SQLWKS> Rem bstat was run and the time estat was run.  Note that the estat
SQLWKS> Rem script logs on as "internal" so the per_logon statistics will
SQLWKS> Rem always be based on at least one logon.
SQLWKS> select n1.name "Statistic", 
               n1.change "Total", 
               round(n1.change/trans.change,2) "Per Transaction",
               round(n1.change/logs.change,2)  "Per Logon",
               round(n1.change/(to_number(to_char(end_time,   'J'))
                *60*60*24 - to_number(to_char(start_time, 'J'))
                  *60*60*24 + to_number(to_char(end_time,   'SSSSS')) 
                    - to_number(to_char(start_time, 'SSSSS'))), 2) "Per
                     Second"
           from stats$stats n1, stats$stats trans, stats$stats logs, 
                stats$dates
          where trans.name='user commits'
           and  logs.name='logons cumulative'
           and  n1.change != 0
          order by n1.name;

The "Statistics Definitions" section contains comments suggesting simple things you can do to improve performance when you notice suspicious statistics. The following sections show specifically how to interpret UTLBSTAT/UTLESTAT output. Chapter 11 expands on this discussion and suggests other ways to tune the areas of memory and disk mentioned below. The statistics shown in the table are from an Oracle7.3 database.

Statistic	Total	Per Transaction	Per Logon	Per Second
CR blocks created	34	34	.03	0
DBWR buffers scanned	127210	127210	95.72	1.6
DBWR checkpoints	18	18	.01	0
DBWR free buffers found	127068	127068	95.61	1.59
DBWR lru scans	12721	12721	9.57	.16
DBWR make free requests	12707	12707	9.56	.16
DBWR summed scan depth	127210	127210	95.72	1.6
DBWR timeouts	26529	26529	19.96	.33
SQL*Net round trips to/from	23	23	.02	0
background checkpoints comp	1	1	0	0
background checkpoints star	1	1	0	0
background timeouts	54395	54395	40.93	.68
bytes received via SQL*Net	2556	2556	1.92	.03
bytes sent via SQL*Net	1121	1121	.84	.01
calls to get snapshot scn:	2269	2269	1.71	.03
calls to kcmgas	20	20	.02	0
calls to kcmgcs	272	272	.2	0
calls to kcmgrs	2336	2336	1.76	.03
cleanouts and rollbacks	17	17	.01	0
cleanouts only - consistent	4	4	0	0
cluster key scan block gets	408766	408766	307.57	5.13
cluster key scans	247051	247051	185.89	3.1
commit cleanout failures	1	1	0	0
commit cleanout number succ	49	49	.04	0
consistent changes	45	45	.03	0
consistent gets	1406646	1406646	1058.42	17.64
cursor authentications	3	3	0	0
data blocks consistent read	45	45	.03	0
db block changes	265	265	.2	0
db block gets	3263	3263	2.46	.04
deferred (CURRENT) block cl	8	8	.01	0
enqueue releases	4935	4935	3.71	.06
enqueue requests	4929	4929	3.71	.06
execute count	1970	1970	1.48	.02
free buffer requested	104896	104896	78.93	1.32
immediate (CR) block cleano	21	21	.02	0
logons cumulative	1329	1329	1	.02
messages received	12764	12764	9.6	.16
messages sent	12764	12764	9.6	.16
no work - consistent read	830715	830715	625.07	10.42
opened cursors cumulative	8867	8867	6.67	.11
parse count	7547	7547	5.68	.09
physical reads	104849	104849	78.89	1.32
physical writes	146	146	.11	0
recursive calls	66561	66561	50.08	.83
redo blocks written	81	81	.06	0
redo buffer allocation retr	1	1	0	0
redo entries	147	147	.11	0
redo log space requests	1	1	0	0
redo size	35833	35833	26.96	.45
redo small copies	146	146	.11	0
redo synch writes	1	1	0	0
redo wastage	4744	4744	3.57	.06
redo writes	26	26	.02	0
rollbacks only - consistent	23	23	.02	0
session logical reads	1409864	1409864	1060.85	17.68
session pga memory	183515664	183515664	138085.53	2301.89
session pga memory max	183515664	183515664	138085.53	2301.89
session uga memory	3671736	3671736	2762.78	46.06
session uga memory max	15301296	15301296	11513.39	191.93
sorts (memory)	1335	1335	1	.02
sorts (rows)	512	512	.39	.01
summed dirty queue length	312	312	.27	.01
table fetch by rowid	28	28	.02	0
table scan blocks gotten	291430	291430	219.29	3.66
table scan rows gotten	629956	629956	474.01	7.9
table scans (long tables)	472	472	.36	.01
table scans (short tables)	265	265	.2	0
total number commit cleanouts	50	50	.04	0
user calls	1352	1352	1.02	.02
user commits	1	1	0	0
write requests	24	24	.02	0

Note that Oracle provides a number of other ways you can derive this same information on system activity, as described later in this chapter and in Chapter 11.

Statistics Definitions

The following are some of the more important tuning statistics:

consistent changes

Number of times a block has been changed, and number of times that the rollback entry had to be read for a transaction has to obtain a consistent read. If this figure is high on your system, make sure that no long-running updates are running during prime online usage times. Also be wary of SNAPSHOT TOO OLD errors.

consistent gets

Number of times a block was acquired in a consistent mode, that is, with the correct time stamp to provide read consistency. The figure is incremented by 1 for each block read during a full table scan. It is incremented by the (height of the index + (2 * index key entries)) for indexed table lookups and is incremented once for each block read during an index-only lookup.

db block changes

Total number of dirty blocks that have resided in the buffer cache. "Dirty" in this case implies that an update or a delete has been applied to the block. The more "db block changes," the more redo activity will be generated. If a block is changed once for each row within the block, the "db block changes" is incremented only by 1.

db block gets

Number of blocks read for update. These also include updates to rollback segment headers, temporary segment headers, and table and index segment headers. The figure is also incremented during extent allocation and when an update to the high-water mark takes place.

DBWR checkpoints

Number of times that checkpoints were sent to the database writer process DBWR. The log writer process hands a list of modified blocks that are to be written to disk. The dirty buffers to be written are pinned, and the DBWR begins writing the data out to the database.

It is usually best to keep the DBWR checkpoints to a minimum, although if there are too many dirty blocks to write out to disk at one time because of a "lazy" DBWR, there may be a harmful effect on response times for the duration of the write. See the parameters LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT, which have a direct effect on the regularity of checkpoints.

The size of your redo logs can also have an effect on the number of checkpoints if the LOG_CHECKPOINT_INTERVAL is set to a size larger than your redo logs, or if the LOG_CHECKPOINT_TIMEOUT is longer than the time it takes to fill a redo log or has not been set.

DBWR timeouts

Number of times that the DBWR looked for dirty blocks to write to the database. Timeouts usually occur every 3 seconds if the DBWR is idle, but the frequency depends on the operating system.

DBWR make free requests

Number of messages received requesting DBWR to make the buffers free. This value is a key indicator as to how effectively your DB_BLOCK_BUFFERS parameter is tuned. If you increase DB_BLOCK_BUFFERS and this value decreases markedly, there is a very high likelihood that the DB_BLOCK_BUFFERS was set too low.

DBWR free buffers found

Number of buffers that the DBWR found on the least-recently-used chain that were already clean. You can divide this value by the "DBWR make free requests" value to obtain the number of buffers that were found that were free and clean (i.e., did not have to be written to disk).

DBWR lru scans

Number of times that the database writer scans the least-recently-used chain for more buffers to write. The scan can be invoked either by a "DBWR make free requests" or by a checkpoint.

DBWR summed scan depth

Can be divided by "DBWR lru scans" to determine the length of the scans through the buffer cache. This is not the number of buffers scanned. If the write batch is filled and a write takes place to disk, the scan depth halts.

DBWR buffers scanned

Total number of buffers scanned in looking for dirty buffers to write to disk and create free space. The count includes both dirty and clean buffers. It does not halt as the "DBWR summed scan depth" does.

free buffer waits

Number of times a free buffer was requested in the buffer cache and none was available because the buffers in the buffer cache had been modified and needed to be written to disk first. This can cause sizable delays and is often caused by having the DB_BLOCK_BUFFERS set too small.

physical reads

Number of requests to the operating system to read a database block into the buffer cache. Even if the parameter DB_FILE_MULTIBLOCK_READ_COUNT is set to more than 1, the physical reads' incremented only by 1. Reading from the temporary segments does not increment this figure.

physical writes

Number of writes by LGWR and DBWR to the disk I/O subsystem. The figure is incremented by 1 regardless of whether the write is a single-block or multiblock write.

recursive calls

Number of times a change is made to Oracle's own internal tables. It is often an indication of too many extents being thrown on your database objects (e.g., tables) or an undersized library cache

redo entries

Number of times changed data is copied into the redo log buffer

redo log space requests

Number of times a user process has to wait for space in the redo log buffer. This wait is often caused by the archiver being lazy and the log writer not being able to write from the log buffer to the redo log because the redo log has not been copied by the ARCH process. One possible cause of this problem is when hot backups are taking place on files that are being written to heavily. For the duration of the hot backups, an entire block is written out to the log buffer and the redo logs for each change to the database, as compared to just writing the characters that have been modified. This figure should ideally be zero and is a key performance indicator.

redo synch writes

When a commit occurs, the log buffer must have all of its contents written to the redo logs. When this occurs, the redo sync writes is incremented by 1.

sorts (disk)

Indicates that the SORT_AREA_SIZE allocated to a user is not large enough to complete a sort and that the user has been forced to complete the sort in the temporary tablespace. It measures the number of disk writes. Increasing the SORT_AREA_SIZE may solve this problem.

sorts (memory)

Indicates that a sort that has been performed entirely in memory. This is the ideal situation for sorting from a performance perspective.

table fetch by rowid

Number of rows accessed by an index or as the result of explicitly stating the ROWID in the WHERE condition.

table fetch continued row

Number of additional blocks that have had to be read to read a single row after the first block has been accessed. The problem is caused by either having a PCTFREE that is too low or having very long rows. You can issue the command ALTER TABLE tname STORAGE (PCTFREE 40), where 40 is the figure most likely to avoid row migration for all future rows inserted into the table. To find the offending tables, look in the CHAIN_CNT column of DBA_TABLES or USER_TABLES. The long column chaining problem can often be solved by setting a higher DB_BLOCK_SIZE. Unfortunately, this is going to require a database rebuild.

table scan blocks gotten

Number of blocks read using full table scans

table scan rows gotten

Number of rows read using full table scans

table scans (long tables)

Number of rows read from tables using a full table scan, where the number of blocks read is greater than the parameter _SMALL_TABLE_THRESHOLD, which has a default of 5

table scans (short tables)

Number of rows read from tables using a full table scan, where the number of blocks read is less than or equal to the parameter _SMALL_TABLE_THRESHOLD, which has a default of 5

user calls

Is incremented by 1 each time a user logs on, parses a statement, or executes a statement

user commits

Number of times users have committed their transactions

user rollbacks

Number of times users have rolled back changes that they have made

write requests

Number of times the DBWR writes dirty buffers to disk, including tables, clusters, indexes, rollback segments, dictionary data, and sort data. You can tell how effective the DBWR has been for each request by dividing "physical writes" by "write requests."

Tuning the Buffer Cache Hit Ratio

The goal of this performance test is to find as much application data in memory as possible. If you tune the buffer cache correctly, you can significantly improve database performance. The hit ratio computed below is the rate at which Oracle finds the data blocks it needs already in memory. The closer the hit ratio approaches 100%, the better your system will perform. Use the statistics from UTLBSTAT/UTLESTAT to do the following calculation:

Hit ratio = (logical reads - physical reads) / (logical reads)
Logical reads = consistent gets + db block gets = 1406646 + 3263 = 1409909
Hit ratio = (1409909 - 104849) / (1409909) = (1305060) / (1409909) = 92% 

The general rule of thumb is this: if the hit ratio is below 95% for online transaction processing applications and 85% for batch applications, if the SHARED_POOL_SIZE has been tuned, and if you have been careful to tune your SQL, then you should increase the buffer cache value to the point at which at least 5% free memory remains available during peak usage. The online transaction processing system being monitored in this example has a hit ratio of 92%, which is not hopeless, but we discovered that the site had several untuned SQL statements. The statements were tuned, and the hit ratio went to 95%. The buffer cache was then tuned, and the hit ratio exceeded 99%. We usually consider anything below 60% very poor and believe that any OLTP application below 95% can be improved, given sufficient free memory. The particular threshold for your system depends on your application transaction mix and on the amount of free memory you have available.

What can you do to solve this problem? Enlarge the amount of buffer cache in your system by increasing the INIT.ORA parameter, DB_BLOCK_BUFFERS, but make sure you keep 5% of your memory free. Also make sure that your SQL is tuned and the appropriate indexes are in place.

For another example of computing the hit ratio via the MONITOR statistics, refer to Chapter 11.

Tuning Buffer Busy Wait Ratio

The goal of this performance test is to reduce contention for database data and rollback blocks. Use the UTLBSTAT/UTLESTAT statistics to perform this calculation:

Buffer busy waits ratio=(buffer busy waits) / (logical reads)=(1) / (39414) 
        = 0.00002

If the ratio is greater than 5%, there is a problem. In this sample, the figure is close to 0%, which is an ideal situation. We have found that a high percentage usually indicates a wait for a rollback segment buffer. You should add rollback segments to the rollback tablespace. If there are any "undo header waits" in the V$WAITSTAT table, add more rollback segments until the "undo header waits" is 0. If there are high "segment header waits," you may find that adding free lists helps. Check V$SESSION_WAIT to get the addresses of the actual blocks having contention.

Depending on your version of Oracle, the "buffer busy waits" will come from either the V$SYSSTAT view or the V$SESSION_WAIT and V$WAITSTAT views. UTLBSTAT and UTLESTAT reports from all three views. Be sure to determine your buffer busy waits ratio, because if this is not repaired, it can cause major damage to your performance.

Tuning the DBWR

The database writer (DBWR) process handles all writes to the database. The aim of this performance test is to ensure that free buffers are available in the buffer cache as needed. Read-only buffers can always be swapped out, but dirty buffers need to be retained until they have been successfully written to the database. User processes that require DBWR must be functioning efficiently. The following query provides you with the average length of the dirty queue. Anything above 100 indicates that the DBWR is having real problems keeping up.

# dirtque.sql
SELECT  DECODE (name, 'summed dirty queue length', value)
                         /
        DECODE (name, 'write requests', value) "Write Request Length" 
  FROM  v$sysstat
 WHERE  name IN ( 'summed dirty queue length', 'write requests')
   AND  value > 0;

If there were a problem, you could increase the number of free buffers by increasing the INIT.ORA parameters DB_BLOCK_BUFFERS, DB_WRITERS, and _DB_BLOCK_WRITE_BATCH. For a more detailed discussion of this process, see the section called .

Tuning Table Access Method

The goal of this performance test is to increase the effectiveness with which data is accessed by indexes. If the result of the following calculation is greater than 10%:

Table scans (long tables) / (table scans (short tables) + table scans
         (long tables))

you must evaluate your use of indexes. This rule may vary depending on the nature of your site's transaction mix. For databases that are used primarily for reporting, full table scans are sometimes preferable to indexed table lookups, especially if you are using the parallel query option. You'll have to select the right approach for your own particular site.

In our example, the calculation is

472 / (472 + 265) = .64

This indicates that a large percentage of the tables accessed were not indexed lookups. You need to investigate this situation. Refer to the section called for details on how to locate the resource-consuming SQL statements.

What can you do about this problem? Ensure that the appropriate indexes are in place. Also ensure that the best use is being made of the optimizer. For online transaction processing systems, investigate all full table scans to determine whether you can use an index to avoid the need to scan