Oracle Scratchpad

October 2, 2009

Quiz Night

Filed under: Hints,Ignoring Hints — Jonathan Lewis @ 6:15 pm BST Oct 2,2009

Why is Oracle ignoring my hints ?
I have a table and want to count the rows, so here’s the query and execution plan I get on the first attempt:

select /*+ full(t) */ count(*) from t1 t;

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    79   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 47343 |    79   (2)| 00:00:01 |
-----------------------------------------------------------------------


If Oracle’s going to use that index, let’s drop it and try again, with more hints:

SQL> drop index t1_n2;

Index dropped.

SQL> select /*+ full(t) no_index(t) */ count(*) from t1 t;
-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   154   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_PK | 47343 |   154   (2)| 00:00:02 |
-----------------------------------------------------------------------

But Oracle doesn’t ignore hints – unless they’re illegal, out of context, or you’ve found a bug (although sometimes the hints aren’t supposed to do what you think – so even when you think that Oracle is ignoring a hint it isn’t).

So how can Oracle appear to be ignoring my hints.

Footnote: If you had trouble with this quiz and you’re going to Oracle Open World I’ll be doing a short presentation called “Hints on Hints” in Moscone South, room 306, on Monday 12th October from 14:30 to 15:30. (Updated Nov 2009: And it went very well.)

[Further reading on "ignoring hints"]

10 Comments »

  1. Well perhaps t is an IOT? And as a Fast Full Scan is a kind of FTS on an index, the hint is not really ignored.

    Comment by Wolfgang — October 2, 2009 @ 6:38 pm BST Oct 2,2009 | Reply

  2. Can it be this parameter ?

    SQL> alter session set "_optimizer_ignore_hints"=TRUE;
    
    Session altered.
    
    SQL> select /*+ full(t1)*/ count(object_id) from t1;
    
    COUNT(OBJECT_ID)
    ----------------
               65665
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4103104450
    
    -------------------------------------------------------------------------------
    | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |       |     1 |     5 |    41   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |       |     1 |     5 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T1_IX | 65668 |   320K|    41   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    

    Comment by coskan — October 2, 2009 @ 7:19 pm BST Oct 2,2009 | Reply

  3. Is it due to a “trick” that the CBO recognises that a count(*) is an attempt to simply know how many rows are in the table, and so the statement is re-written into a scan of the smallest index that contains a value for all rows (usually a unique index on mandatory columns, such as the PK index, or a bitmap index if one exists). Count(*), count(1) etc are so commonly used that it is worth the CBO having a specific check for it and conversion to the fastest wat to satisfy the query.

    But I like Coskan’s answer too :-)

    Martin

    Comment by mwidlake — October 2, 2009 @ 7:27 pm BST Oct 2,2009 | Reply

  4. What if T1 is not a table, but is actually a view with embedded hints?


    CREATE TABLE T2 AS
    SELECT
     ROWNUM C1,
     ROWNUM*2 C2,
     LPAD(' ',500,' ') C3
    FROM
     DUAL
    CONNECT BY
     LEVEL <= 48000;

    CREATE UNIQUE INDEX T1_PK ON T2(C1);
    CREATE UNIQUE INDEX T1_N2 ON T2(C1,C2);

    ALTER TABLE T2 MODIFY (
     C1 NOT NULL,
     C2 NOT NULL);

    CREATE OR REPLACE VIEW T1 AS
    SELECT /*+ INDEX(T2) */
     C1,
     C2
    FROM
     (SELECT
       *
     FROM
       T2) T2;

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

    select /*+ full(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 1213398864

    ------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |       |   101 (100)|          |
    |   1 |  SORT AGGREGATE  |       |     1 |            |          |
    |   2 |   INDEX FULL SCAN| T1_PK | 48000 |   101   (1)| 00:00:02 |
    ------------------------------------------------------------------

    DROP INDEX T1_PK;

    select /*+ full(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 824454759

    ------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |       |   134 (100)|          |
    |   1 |  SORT AGGREGATE  |       |     1 |            |          |
    |   2 |   INDEX FULL SCAN| T1_N2 | 48000 |   134   (1)| 00:00:02 |
    ------------------------------------------------------------------

    DROP INDEX T1_N2;

    select /*+ full(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 3321871023

    -------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| T2   | 48000 |  1015   (1)| 00:00:13 |
    -------------------------------------------------------------------

    Comment by Charles Hooper — October 2, 2009 @ 8:09 pm BST Oct 2,2009 | Reply

  5. Unless you are tricking us with the names, I’ll assume t1 is a table that had a separate t1_pk primary key index, which would discount an IOT. It would be interesting if the tablespace for t1 was offline and Oracle decided to override the hint and use an index on an available tablespace rather than fail the query.

    Comment by Gary — October 3, 2009 @ 12:53 am BST Oct 3,2009 | Reply

  6. The index creation, view creation, and statistics gathering statements were lost in my previous post [Now fixed: JPL]. The view contained an INDEX() hint. Changing that hint to an INDEX_FFS() will result in an INDEX FAST FULL SCAN operation appearing in the plan.

    Another possibility uses two schemas, with a view potentially created to implement security that also contains hints, and a public synonym.

    In schema 1:

    CREATE TABLE T1 AS
    SELECT
     ROWNUM C1,
     ROWNUM*2 C2,
     LPAD(' ',500,' ') C3
    FROM
     DUAL
    CONNECT BY
     LEVEL <= 48000;

    CREATE UNIQUE INDEX T1_PK ON T1(C1);
    CREATE UNIQUE INDEX T1_N2 ON T1(C1,C2);

    ALTER TABLE T1 MODIFY (
     C1 NOT NULL,
     C2 NOT NULL);

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

    CREATE OR REPLACE VIEW T1_VIEW AS
    SELECT /*+ INDEX_FFS(T1_V) */
     C1,
     C2
    FROM
     (SELECT
       *
     FROM
       T1) T1_V;

    CREATE OR REPLACE PUBLIC SYNONYM T1 FOR T1_VIEW;

    GRANT SELECT ON T1_VIEW TO PUBLIC;
    GRANT SELECT ON T1 TO PUBLIC;

    In schema 2:

    select /*+ full(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 1018460547

    -----------------------------------------------------------------------
    | Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |       |       |    29 (100)|          |
    |   1 |  SORT AGGREGATE       |       |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T1_PK | 48000 |    29   (0)| 00:00:01 |
    -----------------------------------------------------------------------

    In schema 1:

    DROP INDEX T1_PK;

    In schema 2:

    select /*+ full(t) no_index(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 177081169

    -----------------------------------------------------------------------
    | Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |       |       |    38 (100)|          |
    |   1 |  SORT AGGREGATE       |       |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T1_N2 | 48000 |    38   (0)| 00:00:01 |
    -----------------------------------------------------------------------

    In schema 1:

    DROP INDEX T1_N2;

    In schema 2:

    select /*+ full(t) */ count(*) from t1 t;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

    Plan hash value: 3724264953

    -------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| T1   | 48000 |  1015   (1)| 00:00:13 |
    -------------------------------------------------------------------

    Comment by Charles Hooper — October 3, 2009 @ 1:27 pm BST Oct 3,2009 | Reply

  7. And the prize goes to …. (roll of drums, long pause that gets people irritated and does nothing to raise the atmosphere) Wolfgang.

    This was just a simple example to demonstrate how easy it can be to think that “something’s gone wrong” because what you’re looking at isn’t 100% vanilla flavoured.

    I am, however, impressed with the ingenuity used to come up with alternative reasons. I particularly liked the idea of the offline tablespace. (I tested it, it didn’t work because Oracle tried to obey the hint – but the optimizer can find a path that gets the right answer even when the “real” data happens to be offline.)

    Comment by Jonathan Lewis — October 3, 2009 @ 3:23 pm BST Oct 3,2009 | Reply

    • Thanks a lot Jonathan. By the way – what did you say was the price? A signed book of the upcoming Cost Based II? Very generous ;)

      Regards
      Wolfgang

      Comment by Wolfgang — October 3, 2009 @ 5:55 pm BST Oct 3,2009 | Reply

  8. […] always interested in examples where “the hint is ignored”, so I exchanged a couple of email messages with Jeff and he sent me an example (which I’ve […]

    Pingback by Recursive subquery factoring | Oracle Scratchpad — February 16, 2014 @ 6:11 pm BST Feb 16,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,990 other followers