Workshop Section: Understanding the Optimizer
1. RULE 2. CHOOSE
3. FIRST_ROWS
We did not discuss the RULE and CHOOSE method since these are no longer supported, but they are legacy settings which you may find within a particular database installation. They have no effect within the parameter
OPTIMIZER_MODE in current releases. But as you may have surmised, the RULE mode was used to operate the Optimizer according to primitive rules-based algorithms.
Optimizer-2
This exercise requires that you have administrator access to the database. Examine the Optimizer mode for your instance by
querying the V$PARAMETER view from an administrator session.
The mode for your database should be ALL_ROWS, which is the default. If this is not the case and you have the authority to change it, change the OPTIMIZER_MODE parameter within the
initialization file and memory.
Oracle Database 11g R2: SQL Tuning – 1
In the event that your database does not have this setting and you must change it, the following command will accomplish this task:
SQL> ALTER SYSTEM execution plan selected by the Optimizer. First, from the user
session, explain once again the same SQL statement which we have been analyzing since the previous workshop. Ideally, use the
DBMS_XPLAN() system-supplied package which will allow you to request the most detailed plan information. Carefully examine the output.
Next, alter the goal for your session to FIRST_ROWS_1 and then explain the same SQL statement again. Notice how a different execution plan is selected to achieve the desired goal.
Answers
SQL> EXPLAIN PLAN
SET statement_id = 'TEST' FOR SELECT LName, project.PName
FROM employee
105 | P a g e INNER JOIN works_on ON works_on.essn = employee.ssn
INNER JOIN project ON project.pnumber = works_on.pno WHERE employee.Salary > 30000
Query Block Name / Object Alias (identified by operation id):
---
Oracle Database 11g R2: SQL Tuning – 1
106 | P a g e Predicate Information (identified by operation id):
--- 1 - access("PROJECT"."PNUMBER"="WORKS_ON"."PNO") 2 - access("WORKS_ON"."ESSN"="EMPLOYEE"."SSN") 3 - access("ESSN"="EMPLOYEE"."SSN")
4 - filter("EMPLOYEE"."SALARY">30000)
Column Projection Information (identified by operation id):
---
1 - "EMPLOYEE"."LNAME"[VARCHAR2,10], "PROJECT"."PNAME"[VARCHAR2,15]
2 - "EMPLOYEE"."LNAME"[VARCHAR2,10], "WORKS_ON"."PNO"[NUMBER,22]
3 - "EMPLOYEE"."SSN"[CHARACTER,9], "EMPLOYEE"."LNAME"[VARCHAR2,10]
4 - "EMPLOYEE"."LNAME"[VARCHAR2,10], "EMPLOYEE"."SSN"[CHARACTER,9]
5 - "ESSN"[CHARACTER,9]
6 - "WORKS_ON"."ESSN"[CHARACTER,9], "WORKS_ON"."PNO"[NUMBER,22]
7 - "PROJECT"."PNAME"[VARCHAR2,15], "PROJECT"."PNUMBER"[NUMBER,22]
Note ---
- dynamic sampling used for this statement
Notice in the output above the following items:
The hash semi join operation has been highlighted as the first join operation included in the plan.
Since Optimizer statistics have not yet been collected dynamic sampling was employed by the Estimator.
Now change the goal for this session as follows:
SQL> ALTER SESSION
SET optimizer_mode = FIRST_ROWS_1;
Session altered.
Explain the plan for the SQL statement a second time, using the techniques provided in the solution above. In this case, focus on the operations selected for this new plan and notice below how this has changed as per the goal.
PLAN_TABLE_OUTPUT
---
Plan hash value: 3405705238
---107 | P a g e
You can also observe that the amount of data or Bytes included in this plan is greater than the earlier plan and is therefore less efficient from an overall throughput perspective, which is just what we would expect with
FIRST_ROWS_1.
Optimizer-4
Having seen the Optimizer in action, it would be good to recall to mind some of its internals which we discussed. Can you list below the primary components found within the Optimizer:
1. __________
Oracle Database 11g R2: SQL Tuning – 1
108 | P a g e
Optimizer-5
Focusing your attention on the Estimator component within the Optimizer, can you list below the primary factors which influence the algorithm when computing the cost of an execution plan:
1. __________
2. __________
3. __________
Answers
1. Selectivity 2. Cardinality 3. Cost
Optimizer-6
This exercise is more of a learning exercise than something which you will use in your production work. But it should help you gain further insight into the Optimizer and SQL statement execution plans.
Recall the brief mention that Optimizer hints may be embedded within the SQL statement. These will be discussed at length later within this course.
For now, we want to mention that RULE is an example of a hint which will direct the Optimizer to function according to the legacy rules-based optimization algorithms. Modify the SQL statement under discussion in this workshop to include this hint. Then, examine the execution plan.
This will demonstrate not only an entirely different plan that you can read and understand, but will also make clear how the Optimizer can be
induced to generate very different plans. While this particular primitive rules-based plan would not actually be useful in a production
environment, it does serve as a helpful example.
Answers
Notice first the inclusion of the hint requiring rules-based optimization.
109 | P a g e SQL> EXPLAIN PLAN
SET statement_id = 'TEST' FOR SELECT /*+ RULE */ LName, project.PName FROM employee
INNER JOIN works_on ON works_on.essn = employee.ssn INNER JOIN project ON project.pnumber = works_on.pno WHERE employee.Salary > 30000
AND EXISTS (SELECT *
FROM dependent
WHERE essn = employee.ssn);
Explained.
When the resulting plan is viewed, you can see how dramatically it is changed.
Far less efficient SORT JOIN and MERGE JOIN operations are employed. These selections have been made without any concept of the cost, since blind adherence to the hard coded rules is followed. It is also clear that a more intricate and
lengthy plan is used.
Regardless of how efficient the plan may or may not be, it provides another learning opportunity in reading a complex execution plan. Note the following in this plan:
The PROJECT and then the WORKS_ON tables are scanned first, sorted and then combined in a SORT MERGE JOIN operation.
The EMPLOYEE and DEPENDENT tables are then processed similarly, subjected to full table scans, sorts and then a SORT MERGE JOIN.
The result tables are then passed up to the higher portions of the plan and progressively combined with additional sorts and then SORT MERGE JOIN operations. Finally, the selected rows are filtered from the largest of the result tables.
As the last step the columns are projected.
SQL> SELECT *
Oracle Database 11g R2: SQL Tuning – 1
110 | P a g e
| Id | Operation | Name | Rows | Bytes | Cost | ---
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | MERGE JOIN | | | | |
| 3 | SORT JOIN | | | | |
| 4 | MERGE JOIN | | | | |
| 5 | SORT JOIN | | | | |
| 6 | TABLE ACCESS FULL| PROJECT | | | |
|* 7 | SORT JOIN | | | | |
| 8 | TABLE ACCESS FULL| WORKS_ON | | | |
|* 9 | SORT JOIN | | | | |
|* 10 | TABLE ACCESS FULL | EMPLOYEE | | | |
|* 11 | TABLE ACCESS FULL | DEPENDENT | | | | --- ...
Note ---
- rule based optimizer used (consider using cbo)
For obvious reasons the notes highlight this inefficiency of this optimization method and encourage switching to the cost-based Optimizer.
Optimizer-7
Return the optimization mode for your session to its original value.
Answers
SQL> ALTER SESSION
SET optimizer_mode = ALL_ROWS;
Session altered.