Using Oracle Cost-based Optimizer Hints: Part 1

This article series will focus using Oracle cost-based optimizer (CBO) hints, and this first article on producing a series of hints that will replicate an exact explain plan.  This technique might be useful for some problem SQL but this technique is also a useful learning tool for hints.

Remember, the DBMS_XPAN.Display procedure can be run after executing a SQL to produce information from the just-executed SQL.  I want to introduce you to the FORMAT=>’OUTLINE’ option.  This option will produce a list of hints that will replicate the explain plan.  This option even puts the /*+ and */ around them all.  These hints are ready to copy/paste right into your SQL.

SELECT count(*)

from A, B, C

WHERE A.STATUS = B.STATUS

AND   A.B_ID = B.ID

AND   B.STATUS = 'OPEN'

AND   B.ID = C.B_ID

AND   C.STATUS = 'OPEN';

I ran the above SQL in SQL*Plus then entered this command:

Select * from table(DBMS_XPLAN.Display(FORMAT=>’OUTLINE’));

Producing this output:

 

We get the same useful explain plan but we also get this ‘Outline Data’.  The CBO ignores any hint that isn’t a hint like the first line ‘BEGIN_OUTLINE_DATA’.  This can probably be taken out.  I am not aware that this is a valid hit.

We can see hash joins in the explain plan and the next two lines shows the USE_HASH hints.  The SEL$1 is the base query block, where the intermediate row sets are kept and passed up the explain plan processing tree.  Since this query has no sub queries, only SEL$1 appears.  If this query had sub queries and the sub queries were not  transformed into inline views (query transformation), you would see SEL$#  where # is the line number in the explain plan of the sub query.  These query blocks are temporary holding areas for the results of the sub queries.

Notice the next hint is a hint I actually like and use ‘LEADING’ .  This hint specifies the order of table processing to the CBO.

The next three lines are forcing the use of the indexes and a full table scan on C, again, matches up nicely with the explain plan.

I’m not sure what the OUTLINE_LEAF hint is doing.  Following this are some optimizer settings picked up from the environment, and they appear in these hints again, so if these are used, the exact explain plan is generated.  ALL_ROWS is a CBO optimizer choice, the other two options is FIRST_ROWS(#) and RULE…where the number in first rows is how many rows to optimize for.  I believe valid settings are 1, 10, 100, 1000 and maybe more.

DB_VERSION and OPTIMIZER_FEATURE_ENABLE allow for the CBO to process the SQL as if it were running on an older version of the database.  I’ve seen DBA types set these at the database level (init.ora settings) when an application was tested on newer database and it performed poorly but performed fine with these set.

This is the easy way out and probably something I’d do myself.  What should happen is to use this hint to just roll back the SQL causing the real issues.

The next article will finish this conversation feature another technique for adding init.ora settings to these SQL statements.