Oracle Scratchpad

May 19, 2014

Ignoring Hints

Filed under: Bugs,Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 6:21 pm BST May 19,2014

Does Oracle ignore hints – not if you use them correctly, and sometimes it doesn’t ignore them even when you use them incorrectly!

Here’s an example that I’ve run on 11.2.0.4 and 12.1.0.1


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum			id,
	rownum			n1,
	rpad('x',100)		padding
from
	generator	v1
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);
end;
/

create index t1_i1 on t1(id);
alter index t1_i1 unusable;

select n1 from t1 where id = 15;
select /*+ index(t1 (id)) */ n1 from t1 where id = 15;

Any guesses about the output from the last 4 statements ?

Index created.

Index altered.

        N1
----------
        15

1 row selected.

select /*+ index(t1 (id)) */ n1 from t1 where id = 15
*
ERROR at line 1:
ORA-01502: index 'TEST_USER.T1_I1' or partition of such index is in unusable state

That’s a pretty convincing display of Oracle not ignoring hints.

Update:

Technically, of course, I haven’t demonstrated that Oracle is not ignoring the hint (i.e. that it’s obeying the hint – if you want to avoid the double negative) until I demonstrate that in the absence of the hint the error would not occur – but that task is left as an exercise to the reader.

 

15 Comments »

    • Matthias,

      Thanks for that. It hadn’t occurred to me that this might be expected behaviour: but it’s doing exactly what the manual says it should.

      Comment by Jonathan Lewis — May 19, 2014 @ 9:03 pm BST May 19,2014 | Reply

      • I am surprised to see the documentation to be so explicit – my impression is that the manual skips the details of hint effects in many cases.

        Comment by Martin Preiss — May 19, 2014 @ 9:12 pm BST May 19,2014 | Reply

      • Hmm – It may be expected but I’m still not happy about it.

        What makes me even less happy is the example on my blog with the local unusable index where if you explicitly specify the implicit partition, it doesn’t error. Otherwise it doesn’t.

        And why the special case for the unusable index?
        If we drop an index, a hint for that index won’t error, it will be ignored.

        Comment by Dom Brooks — May 19, 2014 @ 11:33 pm BST May 19,2014 | Reply

        • Dom,

          I agree – consistent treatment of hints suggests that the hint should be considered illegal – and we even have an analogy to call on with the hint: /*+ rewrite_or_error */ where we can hint for a rewrite, but raise an error if the rewrite cannot be done. It would be nice to have a mechanism which allows us to specify the (special case) where the query should error if the index were unusable.

          General musing on the problem:
          If we hint (alias (col1 col2)) and there is a viable index available in (col1 col2 col3) and (col1 col2) is unusable, should Oracle error or nmot (what if there is a “fail on missing index” option). At present it fails.
          As a counter-example to the “rewrite_or_error” we have the problem with parallel queries that will either silently run at the “wrong” degree of parallelism, or fail, (or get queued in the latest versions) depending on the system configuration. One of these is analogous to your viewpoint, the other to the current implementation.
          It doesn’t matter what Oracle does, someone’s going to complain that they’ve made the wrong choice.
          Hinting should be avoided

          Comment by Jonathan Lewis — May 21, 2014 @ 8:09 am BST May 21,2014

        • > Hinting should be avoided
          True but someone might not know they’re hinting, e.g. SPM baselines.

          Forced example:

          alter index t1_i1 rebuild;
          select /*+ index(t1 (id)) */ n1 from t1 where id = 15;
          select * from table(dbms_xplan.display_cursor);
          --683xw96qz9pyw
          --1429545322
          
          declare
             sqltext clob;
             spm_op pls_integer;
          begin
             sqltext := 'select n1 from t1 where id = 15';
             spm_op  :=
             dbms_spm.load_plans_from_cursor_cache
             (sql_id => '683xw96qz9pyw',
              plan_hash_value => 1429545322,
              sql_text => sqltext);
          end;
          / 
          alter index t1_i1 unusable;
          select n1 from t1 where id = 15;
          

          Comment by Dom Brooks — May 21, 2014 @ 9:33 am BST May 21,2014

        • Dom,

          Nice catch about SPM. When a query is protected against any plan change using an indexed SPM plan and that index is made unusable, the CBO, instead of using the plan it comes with, it errors about its inability to reproduce the SPM plan due to ORA-01502

          SQL> select n1 from t1 where id = 15;
          -------------------------------------------------------------
          | Id  | Operation                   | Name  | Rows  | Bytes |
          -------------------------------------------------------------
          |   0 | SELECT STATEMENT            |       |       |       |
          |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |
          |*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |
          -------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             2 - access("ID"=15)
          
          Note
          -----
             - SQL plan baseline SQL_PLAN_8xbb2605t56kcac124365 used for this statement
          
          
          SQL> alter index t1_i1 unusable;
          
          Index altered.
          
          SQL> select n1 from t1 where id = 15;
          select n1 from t1 where id = 15
          *
          ERROR at line 1:
          ORA-01502: index 'XXX_YYYY.T1_I1' or partition of such index is in unusable state
          
          SQL> alter session set optimizer_use_sql_plan_baselines = FALSE;
          
          Session altered.
          
          SQL> select n1 from t1 where id = 15;
          
                  N1
          ----------
                  15
          
          --------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes |
          --------------------------------------------------
          |   0 | SELECT STATEMENT  |      |       |       |
          |*  1 |  TABLE ACCESS FULL| T1   |     1 |     8 |
          --------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - filter("ID"=15)
          

          Comment by hourim — May 21, 2014 @ 12:29 pm BST May 21,2014

  1. The documentation says “If the optimizer chooses an unusable index, then an ORA-01502 error will result”. But the documentation is forgetting that there are situations where a unique index is unusable but it is nevertheless, used by the CBO, not in the plan, but in the decision the CBO will make to avoid scanning a table. As such, it produces a wrong result without of course generating this ORA-01502 error. An example of such situation is explained here

    http://hourim.wordpress.com/?s=Unusable+index

    I agree that those situations are rare but they exist.

    Best regards

    Mohamed

    Comment by hourim — May 20, 2014 @ 12:38 pm BST May 20,2014 | Reply

    • Mohammed,

      Thanks for the link.

      I thought I’d already written a note about similar errors, but if I have I can’t find it so it’s good to have easy acess to yours.

      There are two or three bugs (not necessarily logged as such) related to primary/unique constraints/indexes and table elimination – possibly all fixed by 12.1 with patches or back-ports to 11.2.0.4 – and all of them revolving around the optimizer trusting uniqueness when uniqueness was no longer the case.

      Going off-topic: while searching for a bug-note for a similar problem recently I came across a note that join elimination for multi-column constraints would become available in 12.2: it will be interested to see if that allows a multiple elimination along a grand-parent/parent/child chain of tables.

      Comment by Jonathan Lewis — May 21, 2014 @ 7:29 am BST May 21,2014 | Reply

  2. Hi Jonathan,

    i have tested the the same without the hint and it is working. and i have tested on 10.2.0.1.0 64bit

    jagdeep@css> drop table t1 purge;
    
    Table dropped.
    
    jagdeep@css> create table t1
      2  as
      3  with dat1 as (
      4          select
      5                  --+ materialize
      6                  rownum  n1
      7          from
      8                  dual
      9          connect by
     10                  level <= 1e4
     11  )
     12  select
     13          rownum  n1
     14          , rownum        n2
     15          , rpad('x', 100) padding
     16  from
     17          dat1
     18  ;
    
    Table created.
    
    jagdeep@css>
    jagdeep@css> begin
      2          dbms_stats.gather_table_stats(
      3                  user
      4                  , 't1'
      5                  , method_opt => 'for all columns size 1'
      6          );
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    jagdeep@css>
    jagdeep@css> create index t1_i1 on t1(n1);
    
    Index created.
    
    jagdeep@css> alter index t1_i1 unusable;
    
    Index altered.
    
    jagdeep@css>
    jagdeep@css> select
      2          n1
      3  from
      4          t1
      5  where
      6          n1 = 15
      7  ;
    
            N1
    ----------
            15
    
    1 row selected.
    
    jagdeep@css>
    jagdeep@css> select
      2          /*+
      3          index(t1 (n1))
      4          */
      5          n1
      6  from
      7          t1
      8  where
      9          n1 = 15
     10  ;
    select
    *
    ERROR at line 1:
    ORA-01502: index 'JAGDEEP.T1_I1' or partition of such index is in unusable state
    
    
    jagdeep@css>
    jagdeep@css> select
      2          n1
      3  from
      4          t1
      5  where
      6          n1 = 15
      7  ;
    
            N1
    ----------
            15
    
    1 row selected.
    

    Comment by jagdeepsangwan — May 21, 2014 @ 11:04 am BST May 21,2014 | Reply

    • isn’t that check already part of the initial test setup?

      Comment by Martin Preiss — May 21, 2014 @ 11:25 am BST May 21,2014 | Reply

      • Hi Martin,

        this was an update from Jonathan Lewis “until I demonstrate that in the absence of the hint the error would not occur – but that task is left as an exercise to the reader.”

        Sorry if i have some misunderstanding from that statement

        Comment by jagdeepsangwan — May 21, 2014 @ 11:33 am BST May 21,2014 | Reply

  3. Oracle does ignore hints only rarely (due bug). That’s what I said to developers in a company and immediately they hit this bug (so I was forced to filed an SR request on MOS). I took few days discussing with support and then they filed filed a bug, which was solved by development few days ago.

    Bug 18221994 : ORDERED HINT IGNORED AFTER TABLE EXPANSION AND JOIN FACTORIZATION

    Of course I’m not big fan of ORDERED hint, however they had some query which parse time (exec plan creation) took 5-8 hours to complete :) I have to say there were several bugs influencing enormous execution plan creation: Bug 13835529, Bug 18221994 and fact that TE and JF together can easily produce 50 000 join orders (even when _optimizer_max_permutations is set to default 2000).

    Regards
    Pavol Babel

    Comment by Pavol Babel — May 23, 2014 @ 2:06 pm BST May 23,2014 | Reply

    • Pavol,

      So that was one of yours.

      I was looking at it a little while ago, thinking that it wasn’t an entirely reasonable complaint. (This fits in with your comment about the ordered hint, of course; and I think that really ought to be replaced with a leading() hint in all cases anyway). Table expansion “doubles the query”, so it’s arguably a little naughty that Oracle doesn’t clone the hints to the two halves, but join factorization is such an extreme rewrite that I thought it would be a little surprising if Oracle could always make something sensible of an ordered hint.

      I think I’ve seen a similar type of problem occurring with Concatenation – the initial set of hints not being cloned properly to both halves of the concatenation.

      Comment by Jonathan Lewis — May 23, 2014 @ 8:51 pm BST May 23,2014 | Reply

      • Jonathan,

        you’re right, ignoring ORDERED hint in such situation (TE and JF) is slightly questionable. There is only one sure think, it would cut number of join orders to reasonable number :)

        On the other hand I’m able to think of some situation with ORDERED hint when also JF/TE could have positive effect

        Regards
        Pavol Babel

        Comment by Pavol Babel — May 26, 2014 @ 1:05 pm BST May 26,2014 | 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,985 other followers