Lesson 9

Oracle includes many different features to help you optimize the performance of your database. This module has covered some of these features, primarily ones that relate to improving query performance. In this module, you were not only introduced to Oracle's query optimizer, but you also learned how to:

1. Describe the Oracle optimizer’s functions
2. Describe new optimizer features of Oracle
3. Use stored outlines to predetermine optimizer behavior
4. Describe the components and uses of the DBMS_STATS package
5. Use DBMS_STATS to collect and move statistics
6. Describe the tools available for monitoring database operations
7. Use Oracle Enterprise Manager to monitor database activity

These features are not the only enhancements in Oracle that allow you to improve the performance of your database.Subsequent modules cover other improvements in Oracle in more depth.

Oracle's query optimizer automatically selects an execution plan for each query submitted. By and large, although the optimizer does a good job of selecting the execution plan, there may be times when the performance of the database suggests that it is using a less-than-optimal execution plan. The only way youcan really tell what path is being selected by the optimizer is to see the layout of the execution plan. You can use two Oracle character-mode utilities to examine the execution plan chosen by the Oracle optimizer. These tools allow you to see the successive steps used by Oracle to collect, select, and return the data to the user.
The first utility is the SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table. You can subsequently run a query on that table to get the execution plan, as shown in SQL*Plus in Figure 2-9.
The execution plan is presented as a series of rows in the table, one for each step taken by Oracle in the process of executing the SQL statement. The optimizer also includes some of the information related to its decisions, such as the overall cost of each step and some of the statistics that it used to make its decisions. The optimizer writes all of this information to a table in the database. By default, the optimizer uses a table called PLAN_TABLE; make sure the table exists before you use EXPLAIN PLAN. (The utlxplan.sql script included with your Oracle database creates the default PLAN_TABLE table.)
You can specify that EXPLAIN PLAN uses a table other than PLAN_TABLE in the syntax of the statement. For more information about the use of EXPLAIN PLAN, please refer to your Oracle documentation. There are times when youwant to examine the execution plan for a single statement. In such cases, the EXPLAIN PLAN syntax is appropriate. There are other times when youwant to look at the plans for a group of SQL statements. For these situations, you can set up a trace for the statements you want to examine and then use the second utility, TKPROF, to give you the results of the trace in a more readable format in a separate file. At other times, youmight also use Oracle's SQL Trace facility to generate a file containing the SQL generated when using TKPROF in tuning applications.

You were introduced to the following terms in this module:

1. Execution Plan: A description of the steps the Oracle database will take to retrieve and select the data requested by a query.
2. Package: A group of PL/SQL procedures that are combined into a single logical grouping. The package header contains the interface description for the procedures, whereas the package body contains the actual code for the procedures in the package.
3. Parallelism: The ability of the Oracle database to divide a single request into multiple tasks that will execute in parallel, which usually results in reduced execution time.

Click the exercise link below to review information with respect to monitoring database performance.
Monitoring Oracle Performance - Exercise