Chapter 16 *

Introducing Database Tuning *

Tuning Overview *

Discussing the Nature of Tuning *

Exercises *

Outlining a Tuning Methodology *

Steps in an Appropriate Tuning Methodology *

Exercises *

Identifying Diagnostic Tools *

Exercises *

Diagnosing Problems *

Running UTLBSTAT and UTLESTAT *

Exercises *

Describing Contents of report.txt *

Exercises *

Understanding Latch Contention *

Latches Available in Oracle *

Obtaining Latches *

Exercises *

Checking for Events Causing Waits *

GETS *

Exercises *

Chapter Summary *

Two-Minute Drill *

Unit IV

Preparing for OCP DBA Exam 4: Performance Tuning Workshop

Chapter 16

Introducing Database Tuning

In this chapter, you will understand and demonstrate knowledge in the following areas:

This chapter is an introduction to tuning. OCP Exam 4 covers many aspects of tuning the Oracle database, and this chapter will set the stage both for your high-level understanding of the Oracle tuning process and for the material to come in the rest of Unit IV. This chapter will begin your exploration of tuning the Oracle database and the applications that use the Oracle database. Both the methodology for tuning and the tools for executing the tuning process will be discussed. The material in this chapter comprises about 15 percent of test questions asked on OCP Exam 4.

Tuning Overview

In this section, you will cover the following topics related to tuning overview:

The Oracle database server is designed to meet the needs of different applications, including those applications that have large user populations who execute many transactions to put data into the database and modify existing data in that database. Oracle also serves the needs of organizations that require large amounts of data to be available in a data warehouse, or an application that contains vast amounts of data available primarily for read access and reporting. In order to meet the needs of these different types of applications, Oracle offers a great deal of flexibility in the way it can be configured. The ongoing tuning process is used by DBAs to run the Oracle Server in such a way as to maximize query performance, storage management, and resource usage according to the needs of the application. This section will begin the discussion of the nature of tuning, allowing you to make the most of Oracle.

Discussing the Nature of Tuning

Tuning is done for many reasons on an Oracle database. Users often want their online applications to run faster. The developers may want batch processes to run faster as well. Management in the organization often recognizes the need for faster applications and batch processing on their Oracle databases. One solution to the problem of performance is to invest in the latest hardware containing faster processors, more memory, and more disk space. To be sure, this is often an effective solution, and methods for maximizing the hardware on the machine hosting the Oracle database will be presented in this unit in order to improve your understanding in this area for OCP. However, the latest and greatest machines are also the most expensive. Organizations generally need to plan their hardware purchases some time in advance also, which means that acute problems with performance are not usually resolved with the hardware purchase approach. Instead, the DBA must determine other ways to improve performance.

In order to meet the needs of an ongoing application that sometimes encounters performance issues, the DBA must know how to resolve those issues. Many problems with performance on an Oracle database can be resolved with three methods, the first being the purchase of new hardware described. The second and third are effective database configuration and effective application design. It should be understood by all people who use Oracle databases that by far the greatest problems with performance are caused by the application—not the Oracle database. Poorly written SQL statements, the use of multiple SQL statements where one would suffice, and other problems within an application are the source of most performance issues. The DBA should always place the responsibility of the first step in any performance situation onto the application developers to see if they can rewrite the code of the application to utilize the database more effectively.

Only after all possibility for resolving the performance issue by redeveloping the application is exhausted should the DBA attempt any changes to the configuration of the Oracle database. This consideration is designed to prevent the impromptu reconfiguration of the Oracle database to satisfy a performance need in one area, only to create a performance problem in another area. Any change to the configuration of the Oracle database should be considered carefully. The DBA should weigh the trade-offs she might need to make in order to improve performance in one area. For example, when changing the memory management configuration for the Oracle database without buying and installing more memory, the DBA must be careful not to size any part of the Oracle SGA out of real memory. Also, if the Oracle SGA takes up more existing memory, other applications that might be running on the same machine may suffer. The DBA may need to work in conjunction with the systems administrator of the machine to decide how to make the trade-off.

Exercises

1. Why must a database be tuned?
2. What is the cause of most performance problems on Oracle databases?

Outlining a Tuning Methodology

Before the DBA begins tuning Oracle, he or she should have an appropriate tuning methodology outlined. This methodology should correspond directly to the goals the DBA is attempting to reach regarding the use of each application and other needs identified by the organization. Some common goals include allowing an application to accept high-volume transaction processing at certain times of the day, returning frequently requested data quickly, or allowing users to create and execute ad hoc reports without adversely affecting online transaction processing. These goals, as well as the many other performance goals set before DBAs, fall into three general categories:

Steps in an Appropriate Tuning Methodology

Oracle has its own four-step process that every DBA should use for performance tuning. In general, it is best to start with step 1 in every situation, in order to avoid creating problems as you attempt to solve them. Also, notice that progressing from step to step directly translates to an increase in the scope and impact of the proposed change. The steps of the process are to start by tuning the performance of applications using the Oracle database, then to tune the memory usage of the Oracle database. If there are still problems, the DBA can tune the disk I/O and utilization of the Oracle database, and finally, the DBA should tune locks and other contention issues. A full presentation of each area follows.

Step 1: Tune the Application

The importance of tuning the application SQL statements and PL/SQL code cannot be overstated. Most often, poorly written queries are the source of poor performance. DBAs should play an active part in encouraging developers to tune the application queries before engaging in other steps of the tuning process. Some of the tools available for developers are described shortly, including SQL Trace and the explain plan command. More detailed information about application tuning appears in Chapter 17.

Step 2: Tune Memory Structures

After application tuning, appropriate configuration and tuning of memory structures can have a sizeable impact on application and database performance. Oracle should have enough space allocation for the SQL and PL/SQL areas, data dictionary cache, and buffer cache to yield performance improvement. That improvement will be in the following areas:

More detailed information about memory tuning appears in Chapter 18.

Step 3: Tune Disk I/O Usage

Oracle Server is designed in such a way as to prevent I/O from adversely impacting application performance. Features such as the server, DBWR, LGWR, CKPT, and SMON background processes each contributing to the effective use and management of disk usage, and are designed to reduce an application’s dependency on fast writes to disk for improvements in performance. However, there are situations where disk usage can have an adverse impact on an application. Tuning disk usage generally means distributing I/O over several disks to avoid contention, storing data in blocks to facilitate data retrieval, and creating properly sized extents for data storage. More information about disk I/O tuning appears in Chapter 19.

Step 4: Detect and Eliminate Resource Contention

As in the case of tuning disk I/O, Oracle Server is designed in such a way as to minimize resource contention. For example, Oracle can detect and eliminate deadlocks. However, there are occasions where many users contend for resources such as rollback segments, dispatchers, or other processes in the multithreaded architecture of Oracle Server, or redo log buffer latches. Though infrequent, these situations are extremely detrimental to the performance of the application. More information about resource contention tuning appears in Chapter 20.

As stated, the steps listed above should be used in the order listed in the event of a tuning emergency. However, the proactive DBA should attempt to tune the database even when everything appears to be running well. The reason proactive tuning is necessary is to reduce the amount of time the DBA spends in production support situations. Proactive tuning also increases the DBA’s knowledge of his or her applications, thereby reducing the effort required when the inevitable production emergency arises.

Exercises

1. What are the three general categories of performance goals?
2. Where are the four steps for performance tuning? In what order should the DBA engage in these steps? How does scope relate to this order?

Identifying Diagnostic Tools

The most important step in solving performance issues is discovering them. While the easiest way to discover performance issues is to wait for developers or users to call and complain, this method is not very customer-oriented, and has proved very detrimental to the reputation of many information technology departments. This approach of taking performance problems on the chin is a reality that is not required, given the availability of tools from Oracle to help monitor and eliminate performance issues. Here are some utilities designed to assist in tuning the Oracle instance.

UTLBSTAT

This creates tables to store dynamic performance statistics for the Oracle database. Execution of this script also begins the statistics collection process. In order to make statistics collection more effective, the DBA should not run UTLBSTAT until after the database has been running for several hours or days. This utility uses underlying V$ performance views (see V$ views below) to find information about the performance of the Oracle database, and the accumulation of useful data in these views may take some time.

UTLESTAT

This ends collection of statistics for the instance. An output file called report.txt containing a report of the statistics generated by the UTLBSTAT utility. To maximize the effectiveness of these two utilities, it is important that UTLBSTAT be allowed to run for a long time before ending statistics collection, under a variety of circumstances. These circumstances include batch processing, online transaction processing, backups, and periods of inactivity. This wide variety of database activity will give the DBA a more complete idea about the level of usage the database experiences under normal circumstances.

SERVER MANAGER

Oracle’s Server Manager tool contains several menu options for monitoring the database and diagnosing problems. This menu is usable when Server Manager is run in GUI mode. Since most Oracle DBAs who use UNIX as the operating system to support Oracle run Server Manager in line mode, this option may not be as familiar to them as other options that take advantage of line mode operation.

EXPLAIN PLAN

This command enables the DBA or developer to determine the execution path of a block of SQL code. The execution plan is generated by the SQL statement processing mechanism. In Oracle7, this command can be executed by entering explain plan set statement_id = ‘name’ into plan_table for SQL_statement at the SQL*Plus prompt. The execution plan shows the step-by-step operations that Oracle will undertake in order to obtain data from the tables comprising the Oracle database. This option is provided mainly for developers and users who write SQL statements and run them against the database to avoid running inefficient SQL. The output of this information is placed into a special table created by running the utlxplan.sql script found in the rdbms/admin subdirectory under the Oracle software home directory. This table is called PLAN_TABLE. An example of an operation that obtains data from the database inefficiently is a full table scan. The execution information can be retrieved from PLAN_TABLE using a special query provided by Oracle—this query will display the information in PLAN_TABLE in a certain way that requires interpretation of the innermost indented operation outward, and then from top to bottom. More information about using explain plan will be presented in Chapter 17.

ENTERPRISE MANAGER PERFORMANCE PACK

This set of utilities contains products that will help the DBA identify performance issues. This package is available mainly to organizations using Oracle on servers running Windows operating systems. This option may not be as well known to DBAs in organizations using Oracle in UNIX environments, because many DBAs use Oracle database management tools in line mode.

SQL TRACE

This tool extends the functionality provided by explain plan by giving statistical information about the SQL statements executed in a session that has tracing enabled. This additional statistical information is provided in a dump file. This utility is run for an entire session using the alter session set sql_trace = true statement. Tracing a session is especially useful for analyzing the full operation of an application or batch process containing multiple transactions, where it is unclear which part of the application or batch process is encountering performance issues.

TKPROF

The dump file provided by SQL Trace is often hard to read. TKPROF takes the output in a trace file and turns it into a more understandable report. The relationship between SQL TRACE and TKPROF is similar to the relationship between IMPORT and EXPORT, as TKPROF only operates or accepts as input the output file produced by SQL TRACE. The contents of the report produced by TKPROF will be discussed.

V$ VIEWS

These are views against several memory structures created by the Oracle SGA at instance startup. These views contain database performance information that is useful to both DBAs and Oracle to determine the current status of the database. The operation of performance tuning tools, such as those in the Oracle Enterprise Manager or Server Manager utilities running in GUI mode as well as utilities available from third-party vendors, use the underlying V$ performance views as their basis for information.

Exercises

1. Identify the usage of the UTLBSTAT and the UTLESTAT tools. Which tool produces report.txt?
2. Identify the tool that identifies the execution plan of a given SQL query. In what step of Oracle’s recommended tuning methodology might this information be useful?
3. What are the V$ views, and how are they used in the database?

Diagnosing Problems

In this section, you will cover the following topics related to diagnosing problems:

In order to solve performance issues, the DBA must be able to pinpoint their source. Often, the performance issue may occur in conjunction with another event. For example, a DBA may get a performance-related support call from users of an online transaction processing system who experience noticeably slower performance at 3 p.m. The DBA may think that there is a performance issue with the application until she discovers that there are a series of ad hoc reports that are scheduled to deploy at around that time. Unfortunately, performance issues sometimes happen infrequently or at irregular intervals. The task of identifying the problem can involve a detailed review of what was happening in the database at the time the performance issue arose. A real-time database monitoring tool that uses V$ dynamic performance views as the basis of reflecting database usage can tell the DBA when the performance issue happened. However, the tool will only be able to tell when the problem occurred in real time. The DBA or someone else must actually be observing the performance monitor when it reflects the change in performance. This can be time consuming and prone to human error such as momentary distraction. The answer to this problem is to maintain a history of performance statistics. This section will describe how the DBA can do this.

Running UTLBSTAT and UTLESTAT

These two utilities, mentioned earlier, provide the functionality required to maintain a history of performance information. To review, UTLBSTAT creates several statistics tables for storing the dynamic performance information. It also begins the collection of dynamic performance statistics. Typically, the script is found in the rdbms/admin directory under the Oracle software home directory, and is executed from within Server Manager. Though it is not necessary to execute the connect internal command before executing this query, the DBA should connect to the database as a user with connect internal privileges prior to running the query. The database overhead used for collecting these statistics, though not sizeable (depending on the platform running Oracle), can have an impact on the system as high as 10 percent. UTLBSTAT creates tables to store data from several V$ performance views including:

TIP: In order to execute UTLBSTAT, the TIMED_STATISTICS parameter must be set to TRUE for the Oracle instance in the init.ora file. Certain information will not be captured by the utility if this parameter is not correctly set. Also, do not run this utility against a database that has not been running for several hours or more, as it relies on dynamic performance views that will not contain useful information if the database has not been running for some time.

UTLESTAT ends the collection of performance statistics from the views named above. Typically, the script is found in the same location as UTLBSTAT, in the rdbms/admin directory under the Oracle software home directory, and it is executed from Server Manager. Though it is not necessary to execute the connect internal command before executing this query, the DBA should connect to the database as a user with connect internal privileges prior to running the query. This utility will gather all statistics collected and use them to generate an output file called report.txt. After generating report.txt, the utility will remove the statistics tables it used to store the performance history of the database. The contents of report.txt will be discussed shortly.

Care should be taken not to shut down the database while UTLBSTAT is running. If this should happen, there could be problems with interpreting the data, and since the database must be running for several hours in order for the V$ views that UTLBSTAT depends on to contain useful data, all work done by UTLBSTAT will be useless. The best thing to do in this situation is to run UTLESTAT as soon as possible to clear out all data from the prior run, and wait until the database has been up long enough to attempt a second execution.

Exercises

1. What is the purpose of the UTLBSTAT utility? What database initialization parameter should be set to TRUE before UTLBSTAT is executed?
2. What is the purpose of the UTLESTAT utility? What is the name of the output file produced by this utility?

Describing Contents of report.txt

There are several important areas of information provided by the report.txt file. The report.txt file provides a great deal of useful information in the following areas. First, it provides statistics for file I/O by tablespace and datafile. This information is useful in distributing files across many disks to reduce I/O contention. SGA, shared area, dictionary area, table/procedure, trigger, pipe, and other cache statistics. report.txt is also used to determine if there is contention for any of several different resources. This report also gives latch wait statistics for the database instance and shows if there is contention for resources using latches. Statistics are also given for how often user processes wait for rollback segments, which can be used to determine if more rollback segments should be added. Average length of a dirty buffer write queue is also shown, which the DBA can use to determine if DBWR is having difficulty writing blocks to the database. Finally, the report.txt file contains a listing of all initialization parameters for the database and the start and stop time for statistics collection. An example of report.txt, slightly modified for readability, is listed here:

SVRMGR> Rem Select Library cache statistics. The pin hit rate should be high.
SVRMGR> select namespace library, gets,
2> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
3> gethitratio, pins,
4> round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
5> pinhitratio, reloads, invalidations from stats$lib;

LIBRARY      GETS       GETHITRATI PINS       PINHITRATI RELOADS    INVALIDATI
------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY         2           1           2           1           0           0
CLUSTER      0           1           0           1           0           0
INDEX        0           1           0           1           0           0
OBJECT       0           1           0           1           0           0
PIPE         0           1           0           1           0           0
SQL AREA     5098        .605       18740       .772       185        0
TABLE/PROCED 7762        .989       15138       .982       186        0
TRIGGER      0           1           0           1           0           0

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", n1.change "Total",
2> round(n1.change/trans.change,2) "Per Transaction",
3> round(n1.change/logs.change,2) "Per Logon"
4> from stats$stats n1, stats$stats trans, stats$stats logs
5> where trans.name='user commits'
6> and logs.name='logons' and n1.change != 0
7> order by n1.name;

0 rows selected.

SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger than
SVRMGR> Rem the value of the db_block_write_batch init.ora parameter, consider
SVRMGR> Rem increasing the value of db_block_write_batch and check for disks that
SVRMGR> Rem 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 write queue length'
4> and writes.name = 'write requests';

0 rows selected.

SVRMGR> Rem I/O should be spread evenly across drives. A big difference between
SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on.
SVRMGR> select * from stats$files order by table_space, file_name;

TABLE_SPACE   FILE_NAME        PHYS_READS PHYS_BLKS_RD PHYS_RD_TIME PHYS_WRITES  PHYS_BLKS_WR PHYS_WRT_TIM
------------ ------------       ---------  ------------ ------------ ------------ ------------ ------------
DATA   /u01/oradata/norm/data01.dbf   303      405           0             108           108          0
INDEX  /u03/oradata/norm/index01.dbf  200      189           0             56            56           0
RBS    /u04/oradata/norm/rbs01.dbf    7        7             0             202           202          0
SYSTEM /u02/oradata/norm/system01.dbf 1072     3731          0             367           367          0
TEMP   /u05/oradata/norm/temp01.dbf   3        34            0             280           280          0
USERS  /u05/oradata/norm/users01.dbf  0         0            0             0             0            0

SVRMGR> Rem sum over tablespaces
SVRMGR> select table_space, sum(phys_reads) phys_reads,
2> sum(phys_blks_rd) phys_blks_rd,
3> sum(phys_rd_time) phys_rd_time, sum(phys_writes) phys_writes,
4> sum(phys_blks_wr) phys_blks_wr, sum(phys_wrt_tim) phys_wrt
5> from stats$files group by table_space order by table_space;

TBLE_SPACE     PHYS_READS  PHYS_BLKS_RD   PHYS_RD_TIME  PHYS_WRITES  PHYS_BLKS_WR   PHYS_WRT
---------- -------- ---------- --------- --------- ---------- --------
DATA       303        405           0     108          108           0
INDEX      200        189           0      56           56           0
RBS          7          7           0     202          202           0
SYSTEM    1072       3731           0     367          367           0
TEMP         3         34           0     280          280           0
TOOLS        1          1           0      0           0             0
USERS        0          0           0      0           0             0

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, sleeps, round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
4> from stats$latches where gets != 0 order by name;

LATCH_NAME         GETS         MISSES      HIT_RATIO    SLEEPS      SLEEPS/MISS
------------------ ----------- ----------- ----------- ----------- -----------
cache buffer handl  532         0           1               0           0
cache buffers chai  1193540     203         1               514         2.532
cache buffers lru   20200       145          .993           332         2.29
dml lock allocatio  1016        0           1               0           0
enqueues            3601        0           1               0           0
library cache       133513      853          .994           1887        2.212
messages            1998        0           1               0           0
multiblock read ob  5265        0           1               0           0
process allocation  14          0           1               0           0
redo allocation     3776        4            .999           15          3.75
row cache objects   150451      266          .998           633         2.38
sequence cache      170         0           1               0           0
session allocation  1430        1            .999           1           1
session idle bit    37204       7           1               11          1.571
shared pool         87978       447           .995          931         2.083
system commit numb  7702        4            .999           9           2.25
transaction alloca  578         0           1               0           0
undo global data    442         0           1               0           0
user lock           30          0           1               0           0

SVRMGR> Rem Statistics on no_wait latch gets. No_wait get does not wait for the
SVRMGR> Rem 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),3) nowait_hit_ratio
6> from stats$latches where immed_gets != 0 order by name;

LATCH_NAME         NOWAIT_GETS         NOWAIT_MISSES       NOWAIT_HIT_RATIO
------------------ ------------------ ------------------ ------------------
cache buffers chai 87850               109                 .999
cache buffers lru  580277              18656               .968
library cache      555                 45                  .919
row cache objects  649                 60                  .908

SVRMGR> Rem Waits_for_trans_tbl high implies you should add rollback segments.
SVRMGR> select * from stats$roll;

UNDO_SEG TRANS_T_G TRANS_T_W UNDO_BYT_WR SEGMENT_SIZE_BYT XACTS SHRINKS WRAPS
-------- --------- --------- ----------- ---------------- ----- ------- -----
0        6        0           0            180224            0     0        0
2        68        0          10915        10645504          0     0        0
3        28        0          4857         10645504          0     0        0
4        65        0          14027        10645504          0     0        0
5        18        0          1786         10645504          0     0        0
6        10        0          1530         10645504         -1     0        0
7        58        0          18306        10645504          1     0        0
8        50        0          8018         10645504         -1     0        0
9        39        0          13020        10645504          0     0        0
10       6         0          0            10645504          0     0        0
11       6         0          0            10645504          0     0        0
12       51        0          12555        10645504          0     0        0
13       61        0          10194        10645504          0     0        0
14       57        0          10081        10645504        -1      0       0
15       8         0          938          10645504         -1     0        0
16       29        0          3369         10645504         -1     0        0
17       20        0          3267         10645504          0     0        0
18       68        0          58861        10645504          0     0        0
19       12        0          6187         10645504          0     0        0
20       6         0          0            10645504          0     0        0
21       6         0          0            10645504          0     0        0

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
--------------------------------------- ---------------------------------------
audit_trail                              NONE
background_dump_dest                     $ORACLE_BASE/admin/norm/bdump
control_files                            /u02/oradata/norm/control.ctl
core_dump_dest                           $ORACLE_BASE/admin/norm/cdump
db_block_buffers                         6000
db_block_size                            4096
db_file_multiblock_read_count            8
db_file_simultaneous_writes              8
db_files                                 200
db_name                                  norm
distributed_transactions                 61
dml_locks                                750
enqueue_resources                        5000
gc_db_locks                              6000
ifile                                    /u07/app/oracle/admin/norm/pfile/config
log_archive_dest                         $ORACLE_BASE/admin/norm/arch/arch.log
log_archive_format                       'log%S%T.arch'
log_checkpoint_interval                  4096
log_checkpoints_to_alert                 TRUE
log_simultaneous_copies                  0
max_dump_file_size                       10240
max_enabled_roles                        22
mts_servers                              0
nls_sort BINARY
open_cursors                             255
optimizer_mode                           RULE
pre_page_sga                             TRUE
processes                                200
resource_limit                           TRUE
rollback_segments                        r01, r02, r03, r04, r05
row_locking                              ALWAYS
sequence_cache_entries                   30
sequence_cache_hash_buckets              23
sessions                                 225
shared_pool_size                         31457280
sort_area_retained_size                  131072
sort_area_size                           131072
temporary_table_locks                    225
transactions                             206
transactions_per_rollback_segment        42
user_dump_dest                           $ORACLE_BASE/admin/norm/udump

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  45       0         0        0         0        15        12
dc_free_extents 1300     53       64        0        133       311      302
dc_segments     2789     21        0        0         51       315       310
dc_rollback_seg 264      0         0        0         0        24        23
dc_used_extents 65       40        0        0         65       62        54
dc_users        134      0         0        0         0        36        24
dc_user_grants  59       0         0        0         0        58        19
dc_objects      7837     109       0        0         0        984       983
dc_tables       21636    15        0        0         0        415       412
dc_columns      62063    2272      3001     522      0         11106    11100
dc_table_grants 18080    113       0         0        0         956      938
dc_indexes      3620     39        2742     12        0        849       848
dc_constraint_d 554      82        61       9         0        536       535
dc_constraint_d 0        0         41       2         0        1         0
dc_synonyms     2524     85        0        0         0        510       509
dc_usernames    3010     0         0        0         0        44        40
dc_sequences    156      3         0        0         24       46        43
dc_sequence_gra 98       4         0        0         0        124       123
dc_tablespaces  38       0         0        0         38       16        8
dc_profiles     14       0         0        0         0        8         1

SVRMGR> Rem The times that bstat and estat were run.
SVRMGR> select * from stats$dates;

STATS_GATHER_TIMES
--------------------
28-JUN-99 15:20:42
28-JUN-99 16:30:40

An important facet of performance tuning lies in knowing how to set initialization parameters. Knowing the values of these parameters often becomes an issue when trying to identify and solve problems. Although the DBA can find the values of initialization parameters by reading the init.ora file used to start the database instance, this method of determining initialization parameters for the instance isn’t the most accurate. Instead, the DBA should use the show parameter command available in Server Manager. Alternately, the following select statement using the V$PARAMETER view will show the initialization parameters for the instance:

The next four chapters on performance tuning that comprise this unit utilize information from dynamic performance views on the Oracle database. These views are the cornerstone of statistics collection for the Oracle database and are used by many performance monitoring tools such as Server Manager. Access to these views, whose names generally start with either V$ or X$, is as follows: V$ views can be accessed either by the DBA logging in as the owner (SYS) or as a user with the select any table privilege granted to it. The X$ views can be accessed only by user SYS.

TIP: In Oracle7, V$ performance views are accessed by any user with select any table object privilege granted to them. X$ views are accessed only by user SYS. However, the select any table object privilege doesn’t have to give access to V$ views in Oracle8--see Chapter 25 for details.

Exercises

1. The output for the report.txt file shows a great deal of hit and wait information. What do you think this information means? What sorts of values (high or low) for hits and waits do you think indicate good or poor database performance?
2. You are the DBA on a database experiencing performance problems accessing information from many dictionary views. The database has been running for a while. You use the UTLBSTAT and UTLESTAT utilities to pinpoint the cause of the performance degradation. In what area might you look in the output file to see if there is a problem?
3. Name the sections of the report.txt file and identify uses for each.

Understanding Latch Contention

To monitor and control access to most Oracle system resources, there are mechanisms called latches that limit the amount of time and space any single process can command the resource at any given time. Monitoring the latch that controls access to the resource is the method used to determine if there is a problem with contention for the resource. A latch is simply an object in the Oracle database that a process must obtain access to in order to conduct a certain type of activity.

TIP: Contention is when one process in the Oracle database wants a resource to which another process has access. There is always some amount of contention for resources in the database. As long as those processes that hold access to resources via the latch do not hold it for a long time, things on the Oracle database should run smoothly. It is when processes contend for resources for a long time that the DBA must intervene to solve the problem.

Latches Available in Oracle

As with any other monitoring exercise, there are V$ dynamic performance views provided by Oracle to assist in the task of observing the performance of the resource. In this case, there are two views that accomplish the task. They are V$LATCHHOLDER and V$LATCH. V$LATCH gives statistical information about each latch in the system, like the number of times a process waited for and obtained the latch. There are approximately 40 different latches in Oracle (possibly more, depending on the options installed and used). V$LATCHHOLDER tells the DBA which latches are being held at the moment, and identifies the processes that are holding those latches. Unfortunately, this information is stored in V$LATCHHOLDER and V$LATCH according to latch number, while the actual names of the latches corresponding to latch number are stored only in V$LATCHNAME.

The following code block shows the contents of V$LATCHNAME, a dynamic performance view that contains the names of all latches in the database, along with their corresponding latch numbers. The name of the latch is stored in this view only, and can be joined with data stored in the other V$ views on latches by latch number. This listing should give an idea about the types of latches that are available in Oracle:

The latches in the list above manage many different resources on the Oracle database. Those resources, along with some of the latches from above that handle management of that resource, are listed below:

Obtaining Latches

To obtain any of the resources listed in the previous code block, the process must first acquire the latch. Processes that request latches to perform activities using Oracle resources do not always obtain the latch the first time they request them. There are two behaviors that processes will undertake when they need to use a latch and find that the latch is not available for their use. One is that the process will wait for the latch to become available for the process’s use. The other is that the process will not wait for the latch to become available, but instead will move on within its own process.

V$LATCHHOLDER handles identifying the processes running on the database that are holding latches. These particular processes may be causing waits on the system. A query against V$LATCHHOLDER will allow the user to identify the process IDs for all processes holding the latch. Since the period of time that any process will hold a latch is very brief, the task of identifying waits on the system, as discussed earlier, can be accomplished by continuously monitoring V$LATCHHOLDER to see which users are holding latches excessively. If there are processes that are holding latches for a long while, that process will appear again and again. Performance for all processes that are waiting for the latch to be free will wait as well.

Unfortunately, each of these views uses a rather cryptic method of identifying which latch is currently being held. A listing of the columns in each table and their meaning follows this discussion. One solution to the problem of cryptic latch numbers used to identify the latch being used is to use V$LATCHNAME. This view maps the latch number to a more readable name that the DBA can associate with a latch. A sample query is given below that will list out the latches that are currently being held by a process, as well as the name of the latch being held:

Note that this query performs a join through the V$LATCH performance view. This is because the link from the latch name in V$LATCHNAME and the latch address that is given in V$LATCHHOLDER can only be made through the latch number, which is present in V$LATCH. More information about the contents of each performance view designed to store latch statistics can be found in Figure 16-1.

Figure 1: V$ performance views storing latch statistics

Exercises

1. Identify several different latches in the database. What resource do the redo allocation and copy latch handle resource management for?
2. What are two different ways a process may request access to a latch?
3. What are three dynamic performance views showing information about latches?

Checking for Events Causing Waits

The V$LATCH can be used as a link between latch views to obtain latch numbers corresponding with latch addresses. Sometimes this link is useful for tying in another important piece of information related to latches. This important piece of information is the wait ratio for the latch—the number of times processes waited for latch access in proportion to the overall number of times processes requested the latch. The wait ratio for latches helps to determine if there is a more serious problem associated with latch waits on the system. The following code block shows how to obtain the latch wait ratio using appropriate views:

This new feature of the latch holder query adds in the ratio of times a process did not obtain the latch vs. the total number of times the latch was requested. Consistent monitoring of these V$ performance views yields the following: If the same process shows up time and time again as holding the latch named, and the wait ratio is high for that latch, then there could be a problem with an event causing a wait on the system.

To find out more about the events or processes that are suffering as a result of an event causing waits, the V$PROCESS view can be put into play. V$PROCESS has a special column associated with it that identifies the address of a latch for which that process is currently experiencing a wait. This column is usually NULL, but if there is a value present for it then there is a wait happening. Associating the latch name and wait ratio can be accomplished with an extension of the query already identified. The following code block demonstrates this:

The execution of this query produces the process identifier for a process experiencing the wait, the name of the latch that is currently held by another process, and the wait ratio for that latch overall. The functionality that these V$ views give can be better managed with use of the MONITOR LATCH menu item present in Oracle’s utilities for database administration, Server Manager. This feature is not available in Oracle8.

There are two types of requests for latches. The distinction between each type of request is based on whether the requestor will continue to run if the latch is not available to that process. Some processes are willing to wait for the latch, while others are not. If a process will wait for the latch, the following series of events will take place:

1. The process will request the latch.
2. If the latch is available, the process will obtain it.
3. If the latch is unavailable, the process will wait a short period of time and ask for the latch again. This period of wait time is called a sleep. The process will continue its cycle of asking and sleeping until the latch becomes available.

Unlike those processes that are willing to wait until a latch becomes available, there are other processes that will not wait until the latch is free to continue. These processes require the latch immediately or they move on. The V$LATCH dynamic performance view captures statistics on both types of latch requests. The following chart depicts the columns of V$LATCH, the explanation of the column, and the corresponding type of request it reflects:

Consider a couple of examples of obtaining latches to understand the process Oracle uses to maintain latch statistics better. Assume a user process puts forth an immediate request to obtain a latch. The latch is available, so the request is granted and the user process obtains the latch. The IMMEDIATE_GETS column on V$LATCH corresponding to the row entry for that latch will be incremented by one . Using that same example, let’s say now that the latch was busy. Instead, Oracle will now update the IMMEDIATE_MISSES column on the corresponding row entry in V$LATCH for that latch. This example illustrates that the statistics compilation process for immediate requests for latches is straightforward.

Now, let us consider the more involved process of compiling statistics for willing to wait requests. A user process makes a willing to wait request for a latch. The latch is available, so the process obtains the latch. The GETS column from V$LATCH is incremented by one . Using the same example, the user process requests a latch, but this time the latch is unavailable. The user process has to wait. So, the user process goes to sleep. The MISSES column is incremented and the SLEEP column is incremented, and the process doesn’t get its latch. After a short period, the process wakes up and asks for the process again. The latch is now available, so the GETS column in V$LATCH for that latch is incremented as well. One can see that the numbers will add up on those columns corresponding to willing to wait requests if latches become tough to obtain.

The next important aspect of latches to cover is calculation of the wait ratio for a latch. The DBA can obtain the wait ratio for a given latch by executing the following query against Oracle:

Any of the names for latches listed in the code block displaying the contents of V$LATCHNAME can be used as part of the in clause in this statement. However, as we will learn in this unit, the latches that manage access to the redo log resources are particularly important because there are few of them, and every process that makes changes to data needs access to them. If either the wait ratio on willing to wait or the immediate latch requests for the latch named by the DBA in the query are greater than 1, then there is a problem with latch contention in the database.

Exercises

1. What is a willing to wait request for a latch? How does it differ from an immediate request for a latch?
2. What is a wait ratio? What are the two types of wait ratios associated with latches? How can the DBA find out what the wait ratio is? If the wait ratio is 6, is there an issue with latch contention in the database?

Chapter Summary

In this chapter we have covered the fundamentals in this advanced area of performance tuning as evaluated by OCP Exam 4. These areas are the tuning overview and problem diagnostics. These two topics comprise 15 percent of OCP Exam 4 content. The beginning of this chapter focused on the nature of database tuning. The text outlined three goals for any DBA approaching the task of performance tuning. They are improving performance of certain queries running against the Oracle database, improving performance of certain applications running against Oracle, and improving Oracle’s overall handling of database user and application load. The text also covered why DBAs should always begin the tuning process by trying to achieve the first goal, that of tuning the SQL queries first. Only when that approach does not work should higher levels of performance tuning be sought.

The following steps were also presented as the outline of an appropriate tuning methodology for DBAs. Step 1 is to tune the applications. Step 2 is to tune memory structures. Step 3 is to tune disk I/O usage. Step 4 is to detect and eliminate I/O contention. These steps are the Oracle-recommended outline of tasks to execute in all tuning situations. The DBA is encouraged to use these steps when he or she isn’t sure of the cause for poor performance on the Oracle database. Following the logical hierarchy or scope of each change is the important feature to remember from this section. OCP Exam 4 will focus some attention on taking the most appropriate tuning measure without making sweeping changes to the database in order to avoid causing more problems than were solved.

The tools used to diagnose and solve performance issues were also discussed in this chapter. One important method the DBA will use to determine performance issues on an Oracle database involves the use of the statistics gathering utilities UTLBSTAT and UTLESTAT. The location of these utilities on the Oracle distribution is usually the rdbms/admin subdirectory under the Oracle software home directory. UTLBSTAT begins statistics collection for observing database performance over an extended period of time. When executed, this process will create the necessary data dictionary tables to store performance data history, and then it will begin collecting those historical performance statistics. UTLESTAT is the statistics collection end utility. On execution, it takes the statistics gathered in the tables UTLBSTAT created and creates a report based on that data. The report name is report.txt, and it can be found in the current directory. The report.txt file consists of several parts, which are described and illustrated in the chapter body.

These tools are dependent on performance data collected by the V$ views in the Oracle data dictionary. That data is valid only for the current Oracle instance; the data is not carried over when an instance is shut down. Since it takes some time for those statistics gathered to have any meaning, it is wise to hold off running UTLBSTAT and UTLESTAT until the database instance has been available for several hours.

Also covered by this chapter was the topic of latch contention in the Oracle instance redo log. Similar to locks, latches exist to limit access to certain types of resources. There are at least 40 different latches in the Oracle database. Two important latches in the database manage the redo log resource. They are the redo copy and redo allocation latch. Latches can be monitored by using the dynamic performance views V$LATCH, V$LATCHHOLDER, and V$LATCHNAME. The statistics to monitor about latches are the number of times a process has to wait for Oracle to fulfill a request it makes to obtain a latch vs. the times a process requests a latch and obtains it. A process will generally do one of two things if it is denied access to a latch. Its request is either immediate or willing to wait. These two types of process requests affect the collection of V$LATCH statistics in the following way. V$LATCH tracks the number of GETS, MISSES, and SLEEPS for processes willing to wait on a request for a latch. A process will sleep if its latch request is denied and the process wants to wait for it to become available. V$LATCH also calculates the number of IMMEDIATE_GETS and IMMEDIATE_MISSES for those processes requesting latches that want the latch immediately or the process moves on. Latch wait time for processes willing to wait is based on the number of MISSES divided by GETS, times 100, or (MISSES/GETS)*100. Latch wait time for processes requiring immediate access to latches or the process will move on is based on the number of IMMEDIATE_MISSES divided by IMMEDIATE_GETS, times 100, or (IMMEDIATE_MISSES/IMMEDIATE_GETS) *100.

Looking for events causing waits was also covered. Events that are causing waits appear in both the V$LOCK dynamic performance view and the V$LATCHHOLDER view. Both these views list information about the processes that are currently holding the keys to access certain resources on the Oracle database. If there are high wait ratios associated with a process holding a latch or lock, as reflected by statistics gathered from V$LATCH or by the presence of a process ID in the V$LOCK view, then there could be a contention issue on the database.

Two-Minute Drill

1. Tune applications
2. Tune memory structures
3. Tune I/O
4. Detect and resolve contention