Striving for Optimal Performance

Yesterday, while reading a swiss italian dialect tranlation of Le petit prince (entitled Ul principe pinin), I noticed a very interesting discussion between the little prince and the king (the inhabitant of the first planet visited by the little prince…). Here is the most important part of that discussion (you can read the whole text, in English, here):

“If I ordered a general to fly from one flower to another like a butterfly, or to write a tragic drama, or to change himself into a sea bird, and if the general did not carry out the order that he had received, which one of us would be in the wrong?” the king demanded. “The general, or myself?”

“You,” said the little prince firmly.

“Exactly. One must require from each one the duty which each one can perform,” the king went on.

When I read that part of the text, I immediately thought to the most frequent mistake that people do when they use Oracle hints. I.e. asking through a hint something impossible to the query optimizer and, then, wondering why it does not work as expected. Who is wrong? The person who wrote the hint or the query optimizer? Most of the time, alas, the person who wrote the hint. To further emphasize this point, below you find the introductory text that I published in TOP (pages 252-254) to introduce hints.

Hope this helps the people who are still looking for an answer to the question “What are hints?”.

*****

According to the Merriam-Webster online dictionary, a hint is an indirect or summary suggestion. In Oracle’s parlance, the definition of a hint is a bit different. Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, it is something that impels toward an action, not merely suggesting one. It seems to me that Oracle’s choice of this word was not the best when naming this feature. In any case, the name is not that important. What hints can do for you is. Just don’t let the name mislead you.

Caution: Just because a hint is a directive, it doesn’t mean that the query optimizer will always use it. Or, seeing it the other way around, just because a hint is not used by the query optimizer, it doesn’t imply that a hint is merely a suggestion. As I will describe in a moment, there are cases where a hint is simply not relevant or legal, and therefore, it has no influence over the execution plan generated by the query optimizer.

While optimizing a SQL statement, the query optimizer may have to take a lot of execution plans into account. In theory, it should consider all possible execution plans. In practice, except for simple SQL statements, it is not feasible to consider too many combinations in order to keep the optimization time reasonable. Consequently, the query optimizer excludes some of the execution plans a priori. Of course, the decision to completely ignore some of them may be critical, and the query optimizer’s credibility is at stake in doing so.

Whenever you specify a hint, your goal is to reduce the number of execution plans considered by the query optimizer. Basically, with a hint you tell the query optimizer which operations should or should not be considered for a specific SQL statement. For instance, let’s say the query optimizer has to produce the execution plan for the following query:

SELECT *
FROM emp
WHERE empno = 7788

If the table emp is a heap table and its column empno is indexed, the query optimizer considers at least two execution plans. The first is to completely read the table EMP through a full table scan:

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

The second is to do an index lookup based on the predicate in the WHERE clause (empno = 7788) and then, through the rowid found in the index, to access the table:

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|   2 |   INDEX UNIQUE SCAN         | EMP_PK |
----------------------------------------------

In such a case, to control the execution plan provided by the query optimizer, you could add a hint specifying to use either the full table scan or the index scan. The important thing to understand is that you cannot tell the query optimizer “I want a full table scan on table emp, so search for an execution plan containing it.” However, you can tell it “If you have to decide between a full table scan and an index scan on table emp, take a full table scan.” This is a slight but fundamental difference. Hints can allow you to influence the query optimizer when it has to choose between several possibilities.

To further emphasize this essential point, let’s take an example based on the decision tree shown in Figure 7-1. Note that even if the query optimizer works with decision trees, this is a general example not directly related to Oracle. In Figure 7-1, the aim is to descend the decision tree by starting at the root node (1) and ending at a leaf node (111–123). In other words, the goal is to choose a path going from point A to point B. Let’s say that, for some reason, it is necessary to go through node 122. To do so, two hints, in the Oracle parlance, are added to prune the paths from node 12 to the nodes 121 and 123. In this way, the only path going on from node 12 leads to the node 122. But this is not enough to ensure that the path goes through node 122. In fact, if at node 1 it goes through node 11 instead of node 12, the two hints would never have an effect. Therefore, to lead the path through node 122, you should add another hint pruning the path from node 1 to node 11.

Something similar may happen with the query optimizer as well. In fact, hints are evaluated only when they apply to a decision that the query optimizer has to take. No more, no less. For this reason, as soon as you specify a hint, you may be forced to add several of them to ensure it works. And, in practice, as the complexity of the execution plans increases, it is more and more difficult to find all the necessary hints that lead to the desired execution plan.

In the section Evolving SQL Plan Baselines of TOP (pages 299-300) I explain what an evolution is and how to manually do it. What I do not cover is the automatic evolution. In fact, I wrote only the following text about it: “In addition to the manual evolution just explained, automatic evolution of SQL plan baselines is supported with the Tuning Pack. The idea is simply that an automated task periodically checks whether nonaccepted SQL plan baselines should be evolved.”

Since the automatic evolution is not as obvious as one might think, in this post I would like to share some information about it. First of all, let me explain when and how an automatic evolution works…

During the maintenance window, the SQL Tuning Advisor works on the SQL statements that had a significant impact on the system. When possible, it provides some recommendations to improve their response time. If it notices that a nonaccepted SQL plan baseline leads to better performance than an accepted one, it recommends a SQL profile that says nothing else than to accept the SQL plan baseline. Obviously, if that SQL profile is accepted (which is, per default, not the case) the SQL plan baseline is accepted as well. Therefore, the SQL plan baselines are automatically accepted only if the SQL profiles generated by the SQL Tuning Advisor are also automatically accepted.

Now that we have seen the theory, let me show you an example:

• First of all, let the database engine capture a SQL plan baseline. This is performed by setting the initialization parameter optimizer_capture_sql_plan_baselines to TRUE and by executing the same SQL statement twice.

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82

SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

• As a result, we have a SQL plan baseline with the following content (basically, the SQL text, some information about the status and an execution plan).

SQL> SELECT *
  2  FROM table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_492bdb47e8861a89',
  4               format     => 'basic'
  5             ));

-----------------------------------------------------------------------
SQL handle: SYS_SQL_492bdb47e8861a89
SQL text: SELECT count(pad) FROM t WHERE n = 42
-----------------------------------------------------------------------

-----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a893fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
-----------------------------------------------------------------------

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

• One of the essential things to notice in the previous output is the full table scan used to get data from the table T. In that particular case, this is not optimal (the table T contains 1,000,000 rows and only 82 of them fulfill the restriction n=42). To improve the response time of this query, let’s create an index based on the column referenced in the WHERE clause.

SQL> CREATE INDEX i ON t (n);

• With that index in place, the execution plan associated with the SQL plan baseline is no longer the best one. However, the query optimizer is not allowed to choose another execution plan (provided that the initialization parameter optimizer_use_sql_plan_baselines is set to TRUE, which is the default). The only thing that the query optimizer can do is to add that information to the SQL plan baseline. To reproduce that behavior it is enough to execute the SQL statement once.

SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82

• Now, let’s display the content of the SQL plan baseline. Notice that there are two execution plans. The first one, which is based on a full table scan, is the execution plan currently in use. The second one, which is based on an index range scan, is not accepted and, therefore, it cannot be chosen by the query optimizer. To use it, an evolution is necessary.

SQL> SELECT *
  2  FROM table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_492bdb47e8861a89',
  4               format     => 'basic'
  5             ));

----------------------------------------------------------------------
SQL handle: SYS_SQL_492bdb47e8861a89
SQL text: SELECT count(pad) FROM t WHERE n = 42
----------------------------------------------------------------------

----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a893fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
----------------------------------------------------------------------

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a8959340d78
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
----------------------------------------------------------------------

Plan hash value: 3694077449

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | I    |
---------------------------------------------

• To make sure that the SQL Tuning Advisor works on the SQL statement used during this test, let’s execute it 50 times. In addition, an AWR snapshot is also taken.

SQL> DECLARE
  2    l_count PLS_INTEGER;
  3  BEGIN
  4    FOR i IN 1..50
  5    LOOP
  6      SELECT count(pad) INTO l_count
  7      FROM t
  8      WHERE n = 42;
  9    END LOOP;
 10    dbms_workload_repository.create_snapshot;
 11  END;
 12  /

• Instead of waiting for the next automatic execution of the SQL Tuning Advisor, let’s start it manually. Notice that with the parameter execution_params it is specified to automatically accept SQL profiles. To change the value of this parameter for the automatic execution of the SQL Tuning Advisor, the procedure set_tuning_task_parameter in the package dbms_sqltune is available.

SQL> DECLARE
  2    l_args dbms_advisor.argList;
  3  BEGIN
  4    l_args := dbms_advisor.argList('ACCEPT_SQL_PROFILES', 'TRUE');
  5    dbms_sqltune.execute_tuning_task(
  6      task_name        => 'SYS_AUTO_SQL_TUNING_TASK',
  7      execution_params => l_args
  8    );
  9  END;
 10  /

• After the execution of the SQL Tuning Advisor both execution plans of the SQL plan baseline are accepted.

SQL> SELECT plan_name, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE sql_handle = 'SYS_SQL_492bdb47e8861a89';

PLAN_NAME                      ACC
------------------------------ ---
SYS_SQL_PLAN_e8861a893fdbb376  YES
SYS_SQL_PLAN_e8861a8959340d78  YES

In summary, with the right configuration in place the database engine automatically evolves the SQL plan baselines that are likely to reduce the load on the system. This is for sure a good thing. The only real problem (or, better, restriction) that I see in this area, it is that not only you need the Enterprise Edition but also the Tuning Pack. In fact, the former is needed for using SQL plan baselines and the latter for the SQL Tuning Advisor.

Today I presented at the DOAG Conference in Nuremberg. As the title of this post suggests, I talked about the query optimizer features introduced in Oracle Database 11g. This is an evolution of the paper I already presented at the UKOUG Conference in Birmingham, at the Hotsos Symposium in Dallas, at the AOUG Expertentreff in Vienna, and at the TrivadisOPEN in Zürich. The aim of this short post is to point out that I just uploaded the presentation and the demo associated to it under Publications.