Oracle Scratchpad

January 3, 2014

Index Hash

Filed under: Bugs,CBO,Hints,Ignoring Hints,Index Joins,Indexing,Oracle — Jonathan Lewis @ 6:56 pm GMT Jan 3,2014

I’m afraid this is one of my bad puns again – an example of the optimizer  making a real hash of the index hash join. I’m going to create a table with several indexes (some of them rather similar to each other) and execute a query that should do an index join between the obvious two indexes. To show how obvious the join should be I’m going to start with a couple of queries that show the cost of simple index fast full scans.

Here’s the data generating code:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 10000
)
select
	rownum			id,
	mod(rownum-1,20)	flag,
	lpad(rownum,10,'0')	v10,
	lpad(rownum,20,'0')	v20,
	lpad(rownum,30,'0')	v30,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 100000
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt	 => 'for all columns size 1'
	);
end;
/
alter table t1 add constraint t1_pk primary key(id)
	using index (create index t1_pk on t1(id))
;

create index t1_flag on t1(flag);

create index t1_ia on t1(id, v20);
create index t1_ib on t1(id, v10);
create index t1_ic on t1(id, v30);

select
	index_name, leaf_blocks
from
	user_indexes
where
	table_name = 'T1'
order by
	index_name
;

/*  output from the query */
/*
INDEX_NAME           LEAF_BLOCKS
-------------------- -----------
T1_FLAG                      195
T1_IA                        515
T1_IB                        375
T1_IC                        657
T1_PK                        222

*/

Given the definitions of the primary key index and the three indexes that start with the ID column their relative sizes shouldn’t surprise you. The cost of an index fast full scan on these indexes will depend on your parameter settings and values for system stats, here are the figures from one system  (from autotrace) running 12.1 – the behaviour is consistent across several versions:


select /*+ index_ffs(t1 t1_pk) */ count(*) from t1;
select /*+ index_ffs(t1 t1_flag) */ count(*) from t1 where flag is not null;
select /*+ index_ffs(t1 t1_ia) */ count(*) from t1;
select /*+ index_ffs(t1 t1_ib) */ count(*) from t1;
select /*+ index_ffs(t1 t1_ic) */ count(*) from t1;

-- with autotrace results:

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

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     3 |    56   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T1_FLAG |   100K|   292K|    56   (2)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG" IS NOT NULL)

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

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

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

If you compare the different costs of the fast full scans they’re consistent with the different sizes (leaf_blocks) of the indexes; so you might expect the following query to do either a tablescan or an index join between the t1_flag index and the t1_pk index (which is the smallest candidate index to find the id column):


select	sum(id)
from
	t1
where
	flag = 0
;

But here’s the plan I got:


--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |     8 |   528   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                  |     1 |     8 |            |          |
|*  2 |   VIEW                  | index$_join$_001 |  5000 | 40000 |   528   (1)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN    | T1_FLAG          |  5000 | 40000 |    10   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| T1_IA            |  5000 | 40000 |   646   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=0)
   3 - access(ROWID=ROWID)
   4 - access("FLAG"=0)

Four things to notice:

  1. The optimizer has picked the wrong index
  2. The fast full scan of t1_ia is 646 in this plan when (on its own) it was only 142
  3. The cost of the whole query is less than the cost of one of the lines
  4. The index chosen looks as if it might have been selected on the basis of alphabetical order

Oops.

Fortunately, of course, we can always add hints to get the right plan – so let’s try this – and this time the plan is what I got by using explain plan followed by a call to dbms_xplan() with the ‘outline’ option:

explain plan for
select
	/*+
		qb_name(main)
		index_join(@main t1 t1_flag t1_pk)
	*/
	sum(id)
from
	t1
where
	flag = 0
;

select * from table(dbms_xplan.display(null,null,'outline alias'));
--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |     8 |   528   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                  |     1 |     8 |            |          |
|*  2 |   VIEW                  | index$_join$_001 |  5000 | 40000 |   528   (1)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN    | T1_FLAG          |  5000 | 40000 |    10   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| T1_IA            |  5000 | 40000 |   646   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN
   2 - SEL$998059AF / T1@MAIN
   3 - SEL$998059AF
   4 - SEL$998059AF / indexjoin$_alias$_001@SEL$998059AF
   5 - SEL$998059AF / indexjoin$_alias$_002@SEL$998059AF

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_JOIN(@"MAIN" "T1"@"MAIN" ("T1"."FLAG") ("T1"."ID" "T1"."V20"))
      OUTLINE(@"MAIN")
      OUTLINE_LEAF(@"MAIN")
      OUTLINE_LEAF(@"SEL$998059AF")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Ouch – the optimizer has ignored the hint and is still using the wrong index.
Here’s something really odd, though – and I’ll get around to looking at the 10053 eventually – let’s add an (undocumented) outline_leaf() hint to the query, a hint that is already in the Outline Data:

explain plan for
select
	/*+
		qb_name(main)
		outline_leaf(@main)
		index_join(@main t1 t1_flag t1_pk)
	*/
	sum(id)
from
	t1
where
	flag = 0
;

select * from table(dbms_xplan.display(null,null,'outline alias'));
--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |     8 |   235   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                  |     1 |     8 |            |          |
|*  2 |   VIEW                  | index$_join$_001 |  5000 | 40000 |   235   (1)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN    | T1_FLAG          |  5000 | 40000 |    10   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| T1_PK            |  5000 | 40000 |   280   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN
   2 - SEL$998059AF / T1@MAIN
   3 - SEL$998059AF
   4 - SEL$998059AF / indexjoin$_alias$_001@SEL$998059AF
   5 - SEL$998059AF / indexjoin$_alias$_002@SEL$998059AF

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_JOIN(@"MAIN" "T1"@"MAIN" ("T1"."FLAG") ("T1"."ID"))
      OUTLINE(@"MAIN")
      OUTLINE_LEAF(@"MAIN")
      OUTLINE_LEAF(@"SEL$998059AF")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=0)
   3 - access(ROWID=ROWID)
   4 - access("FLAG"=0)

We get the plan we want, and it’s cheaper than the default one. It does still suffer from the problem that the cost of the fast full scan is larger than it should be (it seems to be the cost of an index fast full scan plus the cost of an index full scan) and the cost of the whole plan is still less than the cost of that one line.

There have been a number of cases where I’ve thought that the optimizer hasn’t chosen an index join when it was a sensible choice – this is probably one of the reasons why.

8 Comments »

  1. Interesting case. On a side note: 12C seems to have quite a number of inconsistencies in the plan display. Either the cost or the buffers don’t propagate all the way ‘up’ to the top in many of the exec plans I’ve seen. Something is broken :)

    Comment by Andy — January 4, 2014 @ 3:47 pm GMT Jan 4,2014 | Reply

  2. Andy,

    “Buffers not propagating” – is this from a query hinted with “rowsource_execution_statistics”, or some other method ? Might this be associated in some way with “STATISTICS COLLECTOR” operations and dynamically changing paths ?

    The cost (and rows) column has had some glitches ever since NLJ prefetch and batching appeared for nested loops, and odd little consistencies with subqueries have been creeping in since Oracle refined the output to cater for subquery execution estimates.

    Do you have any observations of categories of errors, or any interesting (or surprising, perhaps) examples that can be modeled easily ?

    Comment by Jonathan Lewis — January 5, 2014 @ 10:27 am GMT Jan 5,2014 | Reply

  3. Yes, it looks like doing by name. I first tried the steps as given by you in your post. The plans all came out to be exactly the same. I did a 10053 trace as well. In there it showed that it just tried one index join option –

    ---------------------
    QUERY BLOCK SIGNATURE
    ---------------------
    signature (optimizer): qb_name=MAIN nbfros=1 flg=0
      fro(0): flg=0 objn=23668 hint_alias="T1"@"MAIN"
    
    -----------------------------
    SYSTEM STATISTICS INFORMATION
    -----------------------------
      Using NOWORKLOAD Stats
      CPUSPEEDNW: 1822 millions instructions/sec (default is 100)
      IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
      IOSEEKTIM: 10 milliseconds (default is 10)
      MBRC: -1 blocks (default is 8)
    
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: T1  Alias: T1
        #Rows: 100000  #Blks:  2542  AvgRowLen:  172.00  ChainCnt:  0.00
    Index Stats::
      Index: T1_FLAG  Col#: 2
        LVLS: 1  #LB: 195  #DK: 20  LB/K: 9.00  DB/K: 2494.00  CLUF: 49880.00
        User hint to use this index
      Index: T1_IA  Col#: 1 4
        LVLS: 1  #LB: 515  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 2494.00
      Index: T1_IB  Col#: 1 3
        LVLS: 1  #LB: 375  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 2494.00
      Index: T1_IC  Col#: 1 5
        LVLS: 1  #LB: 657  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 2494.00
      Index: T1_PK  Col#: 1
        LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 2494.00
        User hint to use this index
    Access path analysis for T1
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
      Column (#2): FLAG(
        AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.050000 Min: 0 Max: 19
      Table: T1  Alias: T1
        Card: Original: 100000.000000  Rounded: 5000  Computed: 5000.00  Non Adjusted: 5000.00
      Access Path: TableScan
        Cost:  691.83  Resp: 691.83  Degree: 0
          Cost_io: 690.00  Cost_cpu: 40102700
          Resp_io: 690.00  Resp_cpu: 40102700
    ******** Begin index join (hint) costing ********
      ****** trying bitmap/domain indexes ******
      Access Path: index (AllEqRange)
        Index: T1_FLAG
        resc_io: 10.00  resc_cpu: 1072064
        ix_sel: 0.050000  ix_sel_with_filters: 0.050000 
        Cost: 10.05  Resp: 10.05  Degree: 0
      Bitmap nodes:
        Used T1_FLAG
          Cost = 10.079915, sel = 0.050000
      ****** finished trying bitmap/domain indexes ******
      Access Path: index (FullScan)
        Index: T1_IA
        resc_io: 516.00  resc_cpu: 23674663
        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
        Cost: 517.08  Resp: 517.08  Degree: 0
    
    ******** Cost index join ********
    
    Index join: Joining index T1_FLAG
    Index join: Joining index T1_IA
    Ix HA Join
      Outer table:  T1  Alias: T1
        resc: 10.05  card 5000.00  bytes: 13  deg: 1  resp: 10.05
      Inner table:  <unnamed>  Alias: 
        resc: 517.08  card: 100000.00  bytes: 15  deg: 1  resp: 517.08
        using dmeth: 2  #groups: 1
        Cost per ptn: 0.99  #ptns: 1
        hash_area: 124 (max=20890) buildfrag: 16  probefrag: 330  ppasses: 1
      Hash join: Resc: 528.12  Resp: 528.12  [multiMatchCost=0.00]
    
    ******** Index join cost ********
    
    Cost: 528.12  
    
    ******** Index join OK ********
    
    ******** End index join (hint) costing ********
      Best:: AccessPath: IndexJoin
             Cost: 528.12  Degree: 1  Resp: 528.12  Card: 5000.00  Bytes: 0
    
    ***************************************
    
    
    OPTIMIZER STATISTICS AND COMPUTATIONS
    ***************************************
    GENERAL PLANS
    ***************************************
    Considering cardinality-based initial join order.
    Permutations for Starting Table :0
    Join order[1]:  T1[T1]#0
    ***********************
    Best so far:  Table#: 0  cost: 528.1237  card: 5000.0000  bytes: 40000
    ***********************
    (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
    
    *********************************
    Number of join permutations tried: 1
    *********************************
    

    Then I changed the name of the PK index from T1_PK to PK_T1 and even though it shows HINT ignored, the plan looks to be what we want.

    alter table t1 add constraint pk_t1 primary key(id)
        using index (create index pk_t1 on t1(id))
    ;
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                  |       |       |   235 (100)|          |
    |   1 |  SORT AGGREGATE         |                  |     1 |     8 |            |          |
    |*  2 |   VIEW                  | index$_join$_001 |  5000 | 40000 |   235   (1)| 00:00:03 |
    |*  3 |    HASH JOIN            |                  |       |       |            |          |
    |*  4 |     INDEX RANGE SCAN    | T1_FLAG          |  5000 | 40000 |    10   (0)| 00:00:01 |
    |   5 |     INDEX FAST FULL SCAN| PK_T1            |  5000 | 40000 |   280   (1)| 00:00:04 |
    --------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - MAIN        
       2 - SEL$998059AF / T1@MAIN
       3 - SEL$998059AF
       4 - SEL$998059AF / indexjoin$_alias$_001@SEL$998059AF
       5 - SEL$998059AF / indexjoin$_alias$_002@SEL$998059AF
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
          DB_VERSION('11.2.0.2')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$998059AF")
          OUTLINE_LEAF(@"MAIN")
          OUTLINE(@"MAIN")
          INDEX_JOIN(@"MAIN" "T1"@"MAIN" ("T1"."FLAG") ("T1"."ID"))
          END_OUTLINE_DATA
      */
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("FLAG"=0)
       3 - access(ROWID=ROWID)
       4 - access("FLAG"=0)
    

    Comment by vikramrathour — January 6, 2014 @ 5:44 am GMT Jan 6,2014 | Reply

    • Sorry, forgot to mention that I am using Oracle XE 11gr2

      Comment by vikramrathour — January 6, 2014 @ 5:48 am GMT Jan 6,2014 | Reply

    • Vikramrathour,

      Thanks for producing some evidence to support the hypothesis; it’s not the first case where I’ve seen the optimizer “forget” which indexes it’s supposed to use, often in ways that suggest a pointer is not incremented, or a counter starts at 1 when it should have started at zero. This case looks more like a pointer not being set at all when it should have been.

      It’s interesting to note that in the original trace Oracle does actually flag the two indexes that have been hinted, before using the wrong one.

      The puzzle still remains – why does adding the outline_leaf() hint seem to make a difference ?

      Comment by Jonathan Lewis — January 6, 2014 @ 10:32 am GMT Jan 6,2014 | Reply

      • I have tried looking at the 10053 trace with the OUTLINE_LEAF() hint, and the only thing different (apart from the right index being chosen) is the below line –

        Or-Expansion validity checks failed on query block MAIN (#0) because NO_EXPAND hint
        
        For the query without outline_leaf() hint we get -
        Trying or-Expansion on query block MAIN (#0)
        

        which is odd. No idea why these lines are different

        Comment by vikramrathour — January 7, 2014 @ 5:56 am GMT Jan 7,2014 | Reply

  4. Hi,
    I can’t remember exactly what I used, but it was either the ‘STATISTICS_LEVEL=ALL’ at the session level, or the ‘GATHER_PLAN_STATISTICS’ in the statement hint. The ‘buffer’ I was talking about is the column output from the DBMS_XPLAN.DISPLAY_CURSOR output with either of those two things turned on. Let me dig into my notebook and see if I can find any of those test cases. Recalling from memory, every one of them has something to do with index scan of sort.

    Comment by Andy — January 6, 2014 @ 11:49 am GMT Jan 6,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

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

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers