Oracle Scratchpad

February 21, 2007

Ignoring Hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 9:04 pm GMT Feb 21,2007

Here’s a whimsical, but very telling, example of Oracle “ignoring” hints.

I have the following query, which includes a hint to use a specific index when visiting a certain table. It’s the primary key index, so has no issues relating to null values making the hint invalid – yet Oracle does not use this index. Has it ignored the hint ?

 
select 
	/*+ index(prd prd_pk) */ 
	dep.dept_name, 
	grp.group_name, 
	sum(sal.value)	value 
from 
	sales			sal, 
	products		prd, 
	groups			grp, 
	departments		dep 
where 
	prd.product_id = sal.product_id 
and	grp.product_group = prd.product_group 
and	dep.product_dept = grp.product_dept 
group by 
	dep.dept_name, 
	grp.group_name 
; 

The answer is No! Oracle does not ignore hints. But here’s the execution plan from 10gR2 autotrace, with CPU costing disabled.

 
Execution Plan (10.2.0.1) 
---------------------------------------------------------- 
Plan hash value: 3176690131           

------------------------------------------------------------------------------- 
| Id  | Operation                       | Name        | Rows  | Bytes | Cost  | 
------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                |             |    12 |   420 |    25 | 
|   1 |  HASH GROUP BY                  |             |    12 |   420 |    25 | 
|   2 |   NESTED LOOPS                  |             |   585 | 20475 |     4 | 
|   3 |    NESTED LOOPS                 |             |   585 | 11700 |     3 | 
|   4 |     MAT_VIEW REWRITE ACCESS FULL| SALES_SUM   |   585 |  4095 |     2 | 
|   5 |     TABLE ACCESS BY INDEX ROWID | GROUPS      |     1 |    13 |     1 | 
|*  6 |      INDEX UNIQUE SCAN          | GRP_PK      |     1 |       |       | 
|   7 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS |     1 |    15 |     1 | 
|*  8 |     INDEX UNIQUE SCAN           | DPT_PK      |     1 |       |       | 
-------------------------------------------------------------------------------           

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   6 - access("GRP"."PRODUCT_GROUP"="SALES_SUM"."PRODUCT_GROUP") 
   8 - access("DEP"."PRODUCT_DEPT"="GRP"."PRODUCT_DEPT") 

It’s a cheat – the optimizer rewrote the query to use a materialized view that allowed it to exclude the products table, so the hint became irrelevant.

The hint was not ignored, but you can only see this if you examine the 10053 trace file where you discover that, despite choosing the rewrite, the optimizer did cost the query without the rewrite and did consider the prd_pk index during that pass of the code – even when I made the the index appear so undesirable so that it would not be considered without the hint.

Here’s the execution plan when I add the no_rewrite (norewrite for earlier versions) hint to the SQL (note line 7 particularly):

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 1041076401          

------------------------------------------------------------------------------- 
| Id  | Operation                       | Name        | Rows  | Bytes | Cost  | 
------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                |             |    12 |   480 |   221 | 
|   1 |  HASH GROUP BY                  |             |    12 |   480 |   221 | 
|   2 |   NESTED LOOPS                  |             | 46477 |  1815K|    29 | 
|   3 |    NESTED LOOPS                 |             | 46477 |  1134K|    28 | 
|   4 |     NESTED LOOPS                |             | 46477 |   544K|    27 | 
|   5 |      TABLE ACCESS FULL          | SALES       | 46477 |   272K|    26 | 
|   6 |      TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |     6 |     1 | 
|*  7 |       INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |       | 
|   8 |     TABLE ACCESS BY INDEX ROWID | GROUPS      |     1 |    13 |     1 | 
|*  9 |      INDEX UNIQUE SCAN          | GRP_PK      |     1 |       |       | 
|  10 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS |     1 |    15 |     1 | 
|* 11 |     INDEX UNIQUE SCAN           | DPT_PK      |     1 |       |       | 
-------------------------------------------------------------------------------  

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   7 - access("PRD"."PRODUCT_ID"="SAL"."PRODUCT_ID") 
   9 - access("GRP"."PRODUCT_GROUP"="PRD"."PRODUCT_GROUP") 
  11 - access("DEP"."PRODUCT_DEPT"="GRP"."PRODUCT_DEPT") 

[Further reading on "ignoring hints"]

8 Comments »

  1. but the materialized view, is created “on the fly” or is an object in the schema?

    I’m wondering if adding foreign keys (if they have sense) can make the optimizer eliminate some joins.

    (as stated in the paper here

    http://portal.acm.org/citation.cfm?id=1164215&dl=ACM&coll=&CFID=15151515&CFTOKEN=6184618

    it was cited some times ago in this blog )

    Comment by Antonio — February 22, 2007 @ 9:02 am GMT Feb 22,2007 | Reply

  2. Antonio, the materialized view is an object in the database.

    An interesting thought – not relevant in this specific case – but you are correct. With 10gR2, one effect of join elimination is that you could hint an index which referenced a table which had been eliminated, so the index would not be used because it had become redundant.

    Comment by Jonathan Lewis — February 22, 2007 @ 8:13 pm GMT Feb 22,2007 | Reply

  3. Some time ago when analyzing a particular performance-related issue with Oracle Support we came across a (at least at that time non-public) 10.2 bug where the optimizer actually was ignoring certain hints (amongst others the “undocumented” cardinality resp. its successor opt_estimate hint) in case you were using ANSI join syntax. The workaround was to revert to old join syntax, you could see in the 10053 trace that it then again considered these hints. So whenever you’re stuck and wonder why a particular hint is ignored and you’re using ANSI join syntax consider using the old style, may be it helps.

    Comment by Randolf Geist — March 26, 2007 @ 12:35 pm GMT Mar 26,2007 | Reply

  4. [...] Ignoring hints: new DDL makes old hints “ignorable”. [...]

    Pingback by Rules for Hinting « Oracle Scratchpad — May 2, 2008 @ 1:45 pm GMT May 2,2008 | Reply

  5. [...] postings on hints: Hints – again Ignoring Hints What is a hint ? Taking a hint Comments [...]

    Pingback by Quiz Night « Oracle Scratchpad — December 15, 2009 @ 9:34 pm GMT Dec 15,2009 | Reply

  6. […] my materialized view, this query gets rewritten if the view is “fresh” – which makes the hints inapplicable – and neither the sys_op_count_chg() nor the sys_op_lbid() functions are valid unless […]

    Pingback by MV Refresh | Oracle Scratchpad — August 15, 2013 @ 6:12 pm GMT Aug 15,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,431 other followers