Tuning SQL Statement Execution in 10g, Part 1

Last week I was in Houston teaching a class on 10g New Features and Performance Tuning for a group of developers at a fairly large organization downtown. I try to make my classes as interactive as possible and we had a number of interesting discussions about topics such as appropriate uses for bitmap join indexes, IOT’s, and cluster tables in this organization’s specific applications.

But there was one topic that kept coming up throughout the week. In the past, when experiencing problems with query performance they’d discovered that the RULE hint seemed to make queries run faster. So they now have a large number of queries that use the RULE hint… and they’re afraid to remove the hint for fear that their queries will start to bomb. But since this hint is officially desupported in 10g (although it still works) they are concerned about what to do when they upgrade.

Now you can in fact just leave the “RULE” hint in your queries. Oracle 10g will not ignore the hint and your queries will work but they will not use new features in Oracle such as bitmap indexes and Oracle Support will no longer help you troubleshoot them. So how do you control the way these SQL statements are executed? There are four methods for controlling SQL execution in Oracle 10g: rewriting the SQL, hints, stored outlines and SQL profiles.

Rewrite the SQL

The oldest method for tuning SQL is still valid today albeit in a different way. Unlike the old Rule Based Optimizer, the new Cost Based Optimizer ignores the order of tables in the FROM clause (unless you’ve specified the ORDERED hint) and it now has some fairly sophisticated query rewrite capabilities. But often the first step to tuning SQL is simply asking “what am I trying to do” and deciding if there’s a simpler way to do it. For example, suppose you’re trying to list all employees whose department is missing from the DEPT table. Perhaps the first method that comes to mind is using a non-correlated subquery:

SQL> set autotrace traceonly explain
SQL> select * from emp
  2  where deptno not in (select deptno from dept);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     9 |   333 |    12   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |     3 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

This will work. But this is an example where you could simply rewrite your SQL statement to get a better cost. For example, if you know that DNAME in the DEPT table is never NULL then you could find the same information using an outer join rather than a subquery:

SQL> select * from emp e, dept d
  2  where e.deptno=d.deptno(+) and dname is null;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   798 |     7  (15)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |      |    14 |   798 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

That reduced the cost of the query from 12 to 7 – great! But it’s not exactly the same as the first query since we need to know that DNAME is not null. Now I excluded the predicate information from the very first query – it show that Oracle is actually transforming the non-correlated subquery to a correlated subquery. So lets see what happens if we just rewrite the query as a correlated query ourselves.

SQL> select * from emp e
  2  where not exists (select 1 from dept d where d.deptno=e.deptno);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    40 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    40 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

The same improved cost as the outer join! Two important points about this: first, NOT IN behaves differently than NOT EXISTS in the presence of NULL values. (In fact this handling is why the first query was more expensive.) Second, always remember that cost is what oracle predicts will happen – not what actually will happen! So remember to test the actual performance of these queries (perhaps with tkprof). But this still a good example of how writing a query differently can change how Oracle retrieves the answers to your questions.

Hints

Hints are integral to the CBO and are the oldest method of tuning cost-based SQL statements; they were introduced with the CBO itself in Oracle 7. A hint makes a suggestion to the optimizer about how to execute a query. If the hint doesn’t make sense then the optimizer will ignore it and resume execution as usual. Hints range from broad statement-level optimization approach hints to specific query-block-level transformation hints. There are three hints that should be used judiciously: ALL_ROWS, FIRST_ROWS, and APPEND (for data loads). All other hints should be used sparingly and their use should be justified.

As a quick example, suppose that you’re reading all the employees and departments with this query:

SQL> select * from emp e, dept d
  2  where e.deptno=d.deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   798 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

If you knew that your application was actually going to fetch only the first record then you should tell the optimizer this. This is an example where you should use a hint:

SQL> select /*+ first_rows(1) */ * from emp e, dept d
  2  where e.deptno=d.deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     1 |    57 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

You can see that Oracle has now found a better way to execute your query, knowing what you actually want. The cost reflects this adjustment.

It is also possible to control the execution at a more granular level. Suppose that you specifically want to force Oracle to use a sort-merge join on these two tables. You can use a hint to do just that:

SQL> select /*+ use_merge(e d) */ * from emp e, dept d
  2  where e.deptno=d.deptno;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   798 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |   798 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    80 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |    14 |   518 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

These hints allow you to make decisions that are normally made by the optimizer and should be used with care. However, that being said, hints are a very powerful tool for controlling how Oracle executes SQL statements. For more detailed information about hints, a good place to start is the Oracle Performance Tuning Guide. Every developer who works on an Oracle database must have at least a basic understanding of hints.

These first two methods are very powerful ways to control SQL execution. The client in Houston could certainly use hints other than RULE and achieve the exact same execution plan and performance. However these two methods rest on one critical assumption: that you are able to control the SQL statements. What if you’re running a third party application which is generating SQL that you have no control over? This is where the next two methods come in. Stay tuned for part 2 and the next level of advanced SQL statement tuning: stored outlines and SQL profiles!