10.4 The Oracle Optimizer
The Oracle Relational Database Management System (RDBMS) uses a subsystem known as the optimizer to generate the most efficient path to access the data stored in the tables. This path is known as the execution plan. Examining the execution plans generated by the optimizer allows you to judge the relative cost of one SQL statement versus another. You can then compare the costs of your SQL statements and use the results to adjust your statements accordingly. You can also affect the execution plan for a statement by passing hints to the optimizer. A hint is an optimizer directive that you may supply to help the optimizer generate a more efficient plan based on your needs. Before using hints in a given query, you should perform the three simple tuning activities described in the previous sections. Then, if you need further performance improvements, you should examine the execution plan for your query and consider the use of hints. In this section, I describe the different types of optimization methods used by the optimizer. Then I show you how to view and interpret a couple of example execution plans, and finally, I discuss the use of optimizer hints.
10.4.1 Understanding Optimization Methods
The optimizer may use one of two possible methods to generate the execution plan for a query. The first method is known as rule-based optimization, because syntactic rules are used to generate the execution plan. The second method is known as cost-based optimization, because actual statistics produced by analyzing the data contained in the tables are used to generate the execution plan. With cost-based optimization, the optimizer generates a set of execution plans for the SQL statement. The cost-based optimizer then evaluates the cost of each execution plan based on the amount of computing resources required to execute the plan, including I/O, CPU time, and memory. The cost-based optimizer then picks the execution plan with the lowest cost.
Generally, cost-based optimization generates a much more efficient execution plan than rule-based optimization because the data contained in the tables is taken into consideration. It doesn't always work, however, because statistics aren't always kept up to date by the DBA. The optimizer mode for a database is set by the DBA, and determines which method the optimizer uses to generate execution plans. The following optimizer mode values are available:
- CHOOSE
-
Cost-based optimization will be used if there are statistics for the tables involved in a query; otherwise, rule-based optimization will be used. This is the default.
- RULE
-
Rule-based optimization will be used by default.
- ALL_ROWS
-
Cost-based optimization will be used with the intention of providing the fastest overall retrieval of all rows returned by the query.
- FIRST_ROWS
-
Cost-based optimization will be used with the intention of providing the fastest retrieval of the first row returned by the query.
10.4.2 Examining Execution Plans
The optimizer generates an execution plan for each SQL statement. You can examine the execution plan generated by the optimizer for a given SQL statement by using the SQL*Plus EXPLAIN PLAN command. The EXPLAIN PLAN command populates a table named plan_table with the statement's execution plan. You may then examine that execution plan by issuing a SQL query against plan_table. To follow along with the examples in this section, you should connect to the fundamental_user schema using SQL*Plus. The first thing you (or the DBA) must do is create a table named plan_table to hold execution plans.
 | The table named plan_table is sometimes generically referred to as the plan table. |
|
10.4.2.1 Creating the plan table
To create the plan table, you (or the DBA) must run the SQL*Plus script utlxplan.sql, contained in the directory ORACLE_HOME\rdbms\admin. ORACLE_HOME refers to the directory in which the Oracle database software is installed. The following example shows the utlxplan.sql script being run. When you run the script yourself, you should replace the directory path used in the example with the relevant directory path for your environment:
SQL> @ e:\oracle\ora81\rdbms\admin\utlxplan.sql Table created.
Using SQL*Plus, you can examine the structure of plan_table using the DESC command. For example:
SQL> DESC plan_table Name Null? Type ----------------------------------------- -------- --------------------- 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 NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG DISTRIBUTION VARCHAR2(30)
The most important columns in this table are the following:
- statement_id
-
The name you assign to the execution plan.
- operation
-
The database operation performed.
- object_name
-
The name of the database object referenced in the operation.
- optimizer
-
The optimizer mode used; for example, CHOOSE or RULE.
- id
-
The number assigned to this operation in the execution plan.
- parent_id
-
The parent number for the current step in the execution plan. The parent_id value relates to an id value from a parent step.
- cost
-
The relative cost of the operation if cost-based optimization is used. If cost-based optimization is not used, then no cost is stored.
The next step is to generate an execution plan for a SQL statement using the EXPLAIN PLAN command.
10.4.2.2 Generating an execution plan
Once you've created a plan table, you can use the EXPLAIN PLAN command in SQL*Plus to generate an execution plan for a SQL statement. The syntax for the EXPLAIN PLAN command is:
EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR sql_statement;
The syntax elements are as follows:
- statement_id
-
A name that you assign to the execution plan.
- sql_statement
-
The SQL statement for which the execution plan is to be generated.
The EXPLAIN PLAN command in the following example populates plan_table with the execution plan for a simple query involving the customers table:
SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'CUSTOMERS' 3 FOR 4 SELECT id, first_name, last_name FROM customers;
Once you have run this command, you may examine the execution plan that is stored in plan_table.
10.4.2.3 Querying the plan table
For purposes of querying the plan table, I have provided a SQL*Plus script named explain_plan.sql in the ZIP file available on this book's web site. You should run this script to view an execution plan for a query. It will prompt you for an execution plan name, and then display that execution plan in a readable format.
The explain_plan.sql script is as follows:
-- The explain_plan.sql script displays the -- execution plan for the specified statement id. UNDEFINE 1; SELECT DECODE(id, 0, '', LPAD(' ', 2*(level - 1)) || level || '.' || position) || ' ' || operation || ' ' || options || ' ' || object_name || ' ' || object_type || ' ' || DECODE(optimizer, NULL, '', 'Optimizer = ' || optimizer) || ' ' || DECODE(cost, NULL, '', 'Cost = ' || position) AS execution_plan FROM plan_table CONNECT BY PRIOR id = parent_id AND statement_id = '&&1' START WITH id = 0 AND statement_id = '&1'; An execution plan stored in plan_table is organized into a hierarchy of operations. The operation with an ID of is the initial operation of the hierarchy, and all the other operations in the plan stem from this operation. The SELECT statement in the script shows the operations performed in the execution plan, starting with the operation having an ID of 0 and navigating downward through the hierarchy of relationships between each of the operations.
The following example shows how to run the explain_plan.sql script to retrieve the plan created earlier for the query on the customers table:
SQL> @ d:\jmp\my_book\sql\explain_plan.sql Enter value for 1: CUSTOMERS old 16: statement_id = '&&1' new 16: statement_id = 'CUSTOMERS' old 20: statement_id = '&1' new 20: statement_id = 'CUSTOMERS' EXECUTION_PLAN --------------------------------------- SELECT STATEMENT Optimizer = CHOOSE 2.1 TABLE ACCESS FULL CUSTOMERS
Here, you can see that there is only one nested operation in the execution plan (Operation 2.1), and that it involves a full table scan -- indicated by the words "TABLE ACCESS FULL" on the customers table. In this case, the full table scan came about because in my original SELECT statement, I requested that all rows be retrieved from the table. As you can see from the output, the optimizer mode for my database was set to the default setting of CHOOSE when this execution plan was generated. No statistics were available for the tables used in the query, so rule-based optimization was used: that's why you don't see any cost information in my plan output. Later in this chapter, I'll show you how to generate statistics for a table, and the cost-based execution plan that comes about as a result.
10.4.2.4 Execution plans for table joins
Execution plans for table joins are more complex. The following example generates the execution plan for a query involving a join between the products and product_types tables:
EXPLAIN PLAN SET STATEMENT_ID = 'PRODUCTS' FOR SELECT p.name, pt.name FROM products p, product_types pt WHERE p.type_id = pt.id;
If there are no statistics available for the products or product_types tables, rule-based optimization is used to generate the execution plan. In that case, the execution plan for this query is as follows:
SELECT STATEMENT Optimizer = CHOOSE 2.1 NESTED LOOPS 3.1 TABLE ACCESS FULL PRODUCTS 3.2 TABLE ACCESS BY INDEX ROWID PRODUCT_TYPES 4.1 INDEX UNIQUE SCAN PRODUCT_TYPES_PK UNIQUE
This execution plan is more complex, and you can see the hierarchical relationship between the various operations. The execution plan is run starting with the operation having the highest number. If two operations share the same parent, i.e., have the same outer number, then the operation listed first is the one that is run first. Each operation feeds its results back up the chain to its immediate parent.
For the execution plan shown, the operations are run in the following order: 4.1, 3.1, 3.2, and 2.1. Operation 3.1 is run before 3.2 because both share the same parent, but 3.1 is listed first. The following list is organized by order of execution and provides detailed information about each operation.
- Operation 4.1
-
Uses the unique index named product_types_pk on the id column of the product_types table. This index was created by Oracle when the table was created because the id column is the primary key for the table. Operation 4.1 feeds its results to Operation 3.2 in the form of a list of rowids.
- Operation 3.1
-
Does a full table scan on the products table. The results of this operation are fed into Operation 2.1.
- Operation 3.2
-
Accesses the rows in the product_types table using the list of rowids from Operation 4.1. The results of this operation are fed into Operation 2.1.
- Operation 2.1
-
Uses the rows from Operations 3.1 and 3.2 in a NESTED LOOPS operation to perform the join.
In the next section, I show you how to generate table statistics, and how they affect the execution plan for the join query used in this section's example.
10.4.2.5 Generating table statistics
Let's see how the execution plan for the query in the previous section is changed when I generate statistics for the products and product_types tables. Table statistics are generated using the ANALYZE command in SQL*Plus. The following two commands generate statistics for the products and product_types tables:
ANALYZE TABLE products COMPUTE STATISTICS; ANALYZE TABLE product_types COMPUTE STATISTICS;
The COMPUTE STATISTICS option means that every row in the table is considered. The other possible option, ESTIMATE STATISTICS, means that statistics are estimated based on a subset of the rows in the table.
With the statistics in place, let's generate another execution plan for the same query used in the previous section:
EXPLAIN PLAN SET STATEMENT_ID = 'PRODUCTS2' FOR SELECT p.name, pt.name FROM products p, product_types pt WHERE p.type_id = pt.id;
The execution plan that results from this EXPLAIN PLAN statement is as follows:
SELECT STATEMENT Optimizer = CHOOSE Cost = 3 2.1 HASH JOIN Cost = 1 3.1 TABLE ACCESS FULL PRODUCT_TYPES Optimizer = ANALYZED Cost = 1 3.2 TABLE ACCESS FULL PRODUCTS Optimizer = ANALYZED Cost = 2
This time, statistics are available and cost-based optimization is used to generate the execution plan. You can see that a relative cost has been calculated for each operation and for the query as a whole. You can use these relative cost values when comparing queries. Another difference is that this time a HASH JOIN operation is used to perform the join between the products and product_types tables. A HASH JOIN operation is generally more efficient than the NESTED LOOP that was generated by the rule-based optimizer for the previous section's execution plan.
10.4.3 Using Hints
What good is knowing an execution plan if you can't change it? That's a good question, and the good news is that with Oracle, you are able to change the execution plan for a query. One way to do so is to pass hints to the optimizer. A hint is an optimizer directive, passed in the form of a comment in a SQL statement, that influences the optimizer's choice of execution plan. The right hint, or hints, may improve the performance of a SQL query, and one way to gauge the effectiveness of a hint is to compare the relative cost of a SQL statement without the hint to the relative cost of that same statement with the hint.
There are many optimizer hints available to you. In this section, I show you an example using the FIRST_ROWS hint. The FIRST_ROWS hint tells the optimizer to generate an execution plan that minimizes the time taken to return the first row in a query. For example, the following statement uses FIRST_ROWS in a SELECT statement against the customer table. Notice that the hint is placed within the strings /*+ and */.
EXPLAIN PLAN SET STATEMENT_ID = 'PRODUCTS3' FOR SELECT /*+ FIRST_ROWS */ p.name, pt.name FROM products p, product_types pt WHERE p.type_id = pt.id;
The execution plan for this query is as follows:
SELECT STATEMENT Optimizer = HINT: FIRST_ROWS Cost = 13 2.1 NESTED LOOPS Cost = 1 3.1 TABLE ACCESS FULL PRODUCTS Optimizer = ANALYZED Cost = 1 3.2 TABLE ACCESS BY INDEX ROWID PRODUCT_TYPES Optimizer = ANALYZED Cost = 2 4.1 INDEX UNIQUE SCAN PRODUCT_TYPES_PK UNIQUE Optimizer = ANALYZED
Notice that the cost of this query is 13, which is much greater than the cost of 3 that you saw in the previous section for the version of this query without the hint. This cost increase comes about because a NESTED LOOPS operation is used to join the two tables. As I mentioned earlier, a NESTED LOOPS operation is less efficient than a HASH JOIN operation. However, a NESTED LOOPS operation does begin to yield results more quickly, and that's why it was chosen as a result of using the FIRST_ROWS hint.
By comparing execution plans and relative costs of different statements, you can determine the value of using features such as column indexes and hints. This section has barely scratched the surface of SQL tuning using hints. For a comprehensive introduction to the subject, consult one of the books I recommended earlier in this chapter.
Autotracing There is a faster way to view execution plans than by issuing an EXPLAIN PLAN statement followed by a query of the plan table. This faster way is to use the SQL*Plus autotracing feature, which causes the execution plan to be displayed after each SQL statement you enter. To use autotracing, your DBA must have run the script PLUSTRCE.SQL, contained in the admin subdirectory under the directory in which your SQL*Plus software is installed. The PLUSTRCE.SQL script should be run while logged in as the sys user. The script creates a database role named plustrace, which should then be granted to all users using the following command: GRANT plustrace TO public; After plustrace is granted to public, any user can enable autotracing by entering the following SQL*Plus command: SET AUTOTRACE ON Once autotracing is enabled, any SQL statement issued from SQL*Plus results in the statement's execution plan being displayed along with the results of the statement. In the following example, the id, first_name, and last_name columns from the customers table are displayed for customer #1: SQL> SELECT id, first_name, last_name 2 FROM customers WHERE id = 1; ID FIRST_NAME LAST NAME --- ---------- --------- 1 John Smith Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=1 Card= 1 Bytes=15) 2 1 INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE) To disable autotracing when you no longer need it, use the following command: SET AUTOTRACE OFF The one possible disadvantage of autotracing is that it requires you to actually execute a SQL statement to view that statement's execution plan. For a long-running SQL statement, you may prefer the EXPLAIN PLAN approach, so that you don't have to wait while the statement executes. |
