X

Insights into Statistics, Query Optimization and the Oracle Optimizer

The Oracle Optimizer and ADWC - Hints

Nigel Bayliss
Product Manager

This is Part 3 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find part 1 here and part 2 here.

It's time to take a look at optimizer hints. Here's our test query:

select sum(t1.num), sum(t2.num)
from   table1 t1
join   table2 t2 on (t1.id = t2.id);

Executing on an ADW database (using the LOW consumer group) yields this plan:

----------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |       |       |     4 (100)|
|   1 |  RESULT CACHE        | 86m6ud7jmfq443pumuj63z1bmd |       |       |            |
|   2 |   SORT AGGREGATE     |                            |     1 |    52 |            |
|*  3 |    HASH JOIN         |                            |     1 |    52 |     4   (0)|
|   4 |     TABLE ACCESS FULL| TABLE2                     |     1 |    26 |     2   (0)|
|   5 |     TABLE ACCESS FULL| TABLE1                     |  1000 | 26000 |     2   (0)|
----------------------------------------------------------------------------------------

There are of course no indexes on the table so this is the best plan (we get a single row from TABLE2 so it leads the HASH join).

I will now try to make the plan worse using a hint:   :-)

select /*+ LEADING(t1 t2) USE_NL(t2) */
       sum(t1.num), sum(t2.num)
from   table1 t1
join   table2 t2 on (t1.id = t2.id);

This doesn't work - the plan does not change. Take my word for it for now; there is a link to test scripts at the bottom of this post.

Autonomous Data Warehouse Cloud ignores optimizer hints and PARALLEL hints in SQL statements by default. If your application relies on them you can set OPTIMIZER_IGNORE_HINTS to FALSE at the session or system level using ALTER SESSION or ALTER SYSTEM. You can also enable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to FALSE at the session or system level.

For this example, I used ALTER SESSION to give me the sub-optimal plan I wanted (TABLE1 is now the leading table and it's a NESTED LOOPS join):

alter session set optimizer_ignore_hints = false;

select /*+ LEADING(t1 t2) USE_NL(t2) */
       sum(t1.num), sum(t2.num)
from   table1 t1
join   table2 t2 on (t1.id = t2.id);

----------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |       |       |    73 (100)|
|   1 |  RESULT CACHE        | db11srrdf8ar4d06x4b1j674pp |       |       |            |
|   2 |   SORT AGGREGATE     |                            |     1 |    52 |            |
|   3 |    NESTED LOOPS      |                            |     1 |    52 |    73   (3)|
|   4 |     TABLE ACCESS FULL| TABLE1                     |  1000 | 26000 |     2   (0)|
|*  5 |     TABLE ACCESS FULL| TABLE2                     |     1 |    26 |     0   (0)|
----------------------------------------------------------------------------------------

Why is ADWC set up like this? It's pretty simple: the Oracle Optimizer's job is to find good SQL execution plans without manual intervention. It is not the application developer's or DBA's job, so hints should be avoided as much as possible. Over time, they can prevent applications from taking advantage of new optimization techniques, so try and leave the heavy-lifting to the database. Think autonomous.

If you looked at  part 1 of this series, then you will know that we are careful with this restriction and allow INSERT /*+ APPEND */ by default.

To try this example for yourself, it's uploaded to GitHub.

Comments and suggestions welcome!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services