0554-0556

Previous Table of Contents Next

Page 554

The number of processes used to satisfy a query depends upon the degree of parallel specified. The parallel query option is invoked for each table upon creation, using the PARALLEL clause, as in the following example:

 create table table_name (column1, column2,...) storage (storage options) parallel degree (default); create table table_name (column1, column2,...) storage (storage options) parallel degree (n); 

In the first example, the degree of parallel has been set to default. In this situation, Oracle will determine the appropriate degree. In the second example, n represents a value that you may set for the degree of parallel. The degree identifies how many processes should be used to resolve a query. Of course, the degree depends upon the number of CPUs available.

CAUTION

Do not invoke the parallel query option on a server with only one CPU, because degradation in performance will be experienced instead of improvement.

Performance Tools

When you tune a database, the first and most crucial step is to gather statistics on the current database performance. These tools give a benchmark of how the database is currently performing and enable the DBA to gauge progress by measuring improvement.

Viewing SGA and Parameter Settings

Use the Oracle Server*Manager to view current parameter settings for an Oracle RDBMS instance. The show sga command shows the current size and makeup of the SGA. You can also display the INIT.ORA parameters with the show parameter command. To display only a particular parameter, add it to the command. For example:

 % svrmgrl SVRMGR> Connect internal Connected. SVRMGR> show parameter block 

All the database parameters are shown, even ones that have not been explicitly set in the INIT.ORA parameter file. Parameters that the DBA has not set are shown with their default values. By spooling this list to a data file, the DBA can get an accurate snapshot of a database's settings.

Page 555

utlbstat and utlestat

To determine what needs to be fixed in an Oracle RDBMS instance, you must first decide what is broken. In some cases, performance problems occur sporadically; however, they usually have a specific pattern. Do they occur around lunchtime? At night? Early in the morning? One of the keys to performing successful performance tuning is being able to identify when the problem is occurring.

Oracle provides tools that enable you to examine in detail what the Oracle RDBMS was doing during a specific period of time. They are the begin statistics utility (utlbstat) and the end statistics utility (utlestat). These scripts enable you to take a snapshot of how the instance was performing during an interval of time. They use the Oracle dynamic performance (V$) tables to gather information.

CAUTION

It is important to use the utlbstat and utlestat utilities only against a database instance that has been running for a while. Because an Oracle RDBMS instance reinitializes its dynamic performance table during database startup, information gathered from a database that has not been running and had time to gather information is inconclusive.

To use utlbstat and utlestat, the database must have been started with the value of TIMED_STATISTICS in the INIT.ORA parameter file set to TRUE. Oracle does not collect some of the information required for the report if this parameter is not set to TRUE. Setting TIMED_STATISTICS to TRUE, however, causes the database instance to incur overhead. The amount is small ”only about 4_8 percent in quantitative terms ”and it is necessary to take an accurate snapshot of the database performance. Many DBAs set this parameter to TRUE only when they gather statistics.

After you have set the required parameters ”the database has run for a sufficient period of time ”and you have identified the window, you take the snapshot by using utlbstat. To execute either script, you must have the ability to connect internal to the database. Running utlbstat tells the RDBMS instance to begin gathering statistics until told otherwise . It is executed as follows :

 % svrmgrl SVRMGR> @$ORACLE_HOME/rdbms/admin/utlbstat 

From the moment this script is executed, the Oracle RDBMS instance gathers performance statistics. It continues to do so until you run the utlestat script, which stops gathering performance statistics. It is important that the database remain active and not be shut down while utlbstat is running.

 % svrmgrl SVRMGR> @$ORACLE_HOME/rdbms/admin/utlestat 

Page 556

When you run utlestat, the database creates a report called REPORT.TXT in the current directory, which contains the statistical information gathered. Each report contains the following information:

• Library cache statistics
• System summary statistics
• System-wide wait event statistics
• The average length of the dirty buffer write queue
• File I/O statistics
• SGA and cache statistics
• Latch statistics
• Rollback segment statistics
• Current initialization parameter settings
• Dictionary cache statistics
• Start and stop time statistics

A sample report called REPORT.TXT is included on the CD-ROM and shows what a report produced by utlestat might look like.

Generating the report is simple; interpreting it is another matter entirely. The rest of this chapter looks at what this information means. The report itself gives some brief hints. When in doubt, always remember to keep hit rates high and wait times low.

EXPLAIN PLAN

Performance tuning does not always have to happen on a global, database-level view. In theory, most tuning should take place at much lower, scalable levels where the performance impact is more easily measured. A fundamental truth of database tuning and optimization is that performance tuning is not sorcery or magic. Optimizing a database will not make a poorly tuned application run faster; the reverse is also true, though less common. It is important to examine how the database handles processing at the application, or SQL, level.

To do this, Oracle provides a tool in the form of the EXPLAIN PLAN, which enables the DBA to pass a SQL statement through the Oracle optimizer and learn how the statement will be executed by the database ”the execution plan. That way, it is possible to learn whether the database is performing as expected ”for example, whether it uses an index on a table instead of scanning the entire database table.

Several factors can affect the results returned by an EXPLAIN PLAN. They include

• Changes in statistics when running the database under the Cost-Based Optimizer
• The use of HINTS under the Rule-Based Optimizer that cause the query to select a particular execution path

Previous Table of Contents Next