The Oracle Optimizer and ADWC – Hints

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!

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569