Stored Outlineが働かない－オラクル11g

仲間の中の一人が次のようなオラクル11ｇの非正常的なStored Outlineに対するテスト結果を送りました。

1. オブジェクトを作ります。

create table t1(c1 int, c2 int);

-- c1 = skewed, c2 = normal
insert into t1
select 1, level
from dual
connect by level <= 10000
union all
select 2, level
from dual
connect by level <= 1000
union all
select 3, level
from dual
connect by level <= 100
union all
select 4, level
from dual
connect by level <= 10
union all
select 5, level
from dual
connect by level <= 1;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for columns c1 size skewonly');

2. 次のような実行計画を見えています。オラクル10gR2とオラクル11ｇが同じ実行計画を持っています。オラクルがT1_N1索引を選択したのに注意してください。

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

3. 現在の実行計画をTEST_OUTLN4のStored Outlineに貯蔵しますし、オラクルがこのOutlineを使うようにします。

create or replace outline test_outln4
on
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

alter session set use_stored_outlines = true;

TEST_OUTLN4のOutlineは次のようなヒントで構成されています。

select hint from user_outline_hints
where name = 'TEST_OUTLN4';

-- Oracle 10.2.0.1
HINT
--------------------------------------------------
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

-- Oracle 11.1.0.6
HINT
------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS

4. ここから変なことができます。次のように3番目の索引T1_N3を追加します。

-- t1_n3 index (c1, c2) 
create index t1_n3 on t1(c1, c2);

5. オラクル10gR2は私が作ったOutlineをよく従います。

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note                                               
-----                                              
   - outline "TEST_OUTLN4" used for this statement 

6. しかし、オラクル11ｇはOutlineを従わなく、さっきの索引T1_N3を選択してしまいます。もっとおかしいものはオラクル自分は私はTEST_OUTLN4をよく使っていると話しているのです。

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N3 |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note                                               
-----                                              
   - outline "TEST_OUTLN4" used for this statement 

7. 理由は？今は私もよくわかりません。でも、10053トレースが少しの追加的な情報を提供します。

オラクル10gR2のトレースは明らかにOutlineによってINDEXヒントを使っていると記録しています。

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 11111  #Blks:  20  AvgRowLen:  6.00
Index Stats::
  Index: T1_N1  Col#: 1
    LVLS: 1  #LB: 22  #DK: 5  LB/K: 4.00  DB/K: 4.00  CLUF: 21.00
    User hint to use this index  -- Look at this part!
  Index: T1_N2  Col#: 2
    LVLS: 1  #LB: 24  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 2116.00
  Index: T1_N3  Col#: 1 2
    LVLS: 1  #LB: 28  #DK: 11111  LB/K: 1.00  DB/K: 1.00  CLUF: 19.00

でも、オラクル11ｇのトレースはOutlineによって与えたヒントに対する情報が全然ありません。

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 11111  #Blks:  20  AvgRowLen:  6.00
Index Stats::
  Index: T1_N1  Col#: 1
    LVLS: 1  #LB: 22  #DK: 5  LB/K: 4.00  DB/K: 4.00  CLUF: 21.00
  Index: T1_N2  Col#: 2
    LVLS: 1  #LB: 24  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 2116.00
  Index: T1_N3  Col#: 1 2
    LVLS: 1  #LB: 28  #DK: 11111  LB/K: 1.00  DB/K: 1.00  CLUF: 19.00
Access path analysis for T1

これはバグなのか、もしくは知られない新機能なのかわかりませんけど。。。私には馬鹿なバグに見えますが。。。