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. |
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.
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
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:
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.
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