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.)
Oracle Scratchpad 
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Wolfgang,
The prize is the warm glow of satisfaction in a job well done that I have just conferred on you.
;)
Comment by Jonathan Lewis — October 4, 2009 @ 10:05 am BST Oct 4,2009 |
[…] 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 GMT Feb 16,2014 |