Stored outline bug or enhancement? – Oracle 11g | Dion Cho

Stored outline bug or enhancement? – Oracle 11g

One of my colleagues sent me following interesting test case on the stored outline abnormality in Oracle 11g.

1. Create objects

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. Here we have a nice execution plan. Both Oracle 10gR2(10.2.0.1) and Oracle 11g(11.1.0.6) show the same plan. Note that Oracle chose the index 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. Create a stored outline(test_outln4) to store the current plan(T1_N1) and let Oracle use it.

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;

FIY, Oracle stores following hints for the stored outline TEST_OUTLN4.

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. Here comes the trick. Now I create the 3rd index T1_N3 on t1(c1, c2).

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

5. Oracle 10gR2 obeys the stored 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. But, Oracle 11g? It just ignores me and chooses another index which was created after the outline was created. But it still says that it is using the stored outline I made.

---------------------------------------------------------------------------
| 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. The reason? I have no idea at this time but the 10053 trace shows some hints. Oracle 10gR2 clearly says that it is using the INDEX hint given by the stored 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
    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

But, Oracle 11g silently ignores the INDEX hint given by the stored 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

Hm… Is this just a bug or another enhancement?

Footnote1: The orignal title was [Stored outline does not work – the stupidity of Oracle 11g], but some reasonable suggestions made me rewrite the title. I did’nt mean to mention the stupdity, but just tried to make an emphasis, which turned out to be a bad trial. :( It seems that I forget my dignity as an Oracle ACE from time to time. Let me write more responsible posts next time!

Footnote2: For the same reason as footnote1, I unapproved some comments including mine to prevent unnecessary arguments from happening. Forgive me for that. It turned out that the ugly post gets the most active responses. :(

Written by Dion Cho

July 24, 2009 at 7:26 am

Posted in Optimizer

13 Responses

Subscribe to comments with RSS.

Seems to me it is using a better index and still obeying the hint. The hint is telling it to use the index starting with column C1. It has a choice of two such indexes and is using the better option (which is what a hint is supposed to do).
I vote it as an enhancement. Now what would it have done if the new index was on c2,c1 ?

Gary

July 24, 2009 at 12:51 pm

Reply
- @Gary.
  
  Yes, it’s very likely – another enhancement of Oracle 11g. I would try other options to make things clear.
  
  Dion Cho
  
  July 24, 2009 at 2:31 pm
  
  Reply
Hi,

This is very interesting to see someone having the same issue that I am having.

My issue is with 10.2.0.3

My situation is a little different. I am following the directions given by J. Lewis in his class on hints. I give the minimal hint needed to get the access path I want. I run the query and it runs fast and does what I want. I then get the full hint from v$sql_plan and use that. When I do the wrong access path is picked.

So how could an exacting hint cause the wrong access path when the minimal one works? Looks like a long weekend for me figuring this out.

Robert Ponder
cell: 770.490.2767
email: rponder@ponderproserve.com

Robert Ponder

July 24, 2009 at 9:49 pm

Reply
- @Robert.
  
  Could you post your case to your blog or any other place like OTN forum?
  Oracle has some restrictions on applying hints and you might be hitting one of them.
  
  Dion Cho
  
  July 24, 2009 at 10:15 pm
  
  Reply
  - Hi Dion,
    
    Thanks for your response. It was an Oracle bug. I don’t recall the exact hint now but I finally found in the outline that the hint was not there. I did an insert to add it to the outline and everything worked just fine.
    
    Robert Ponder
    
    September 27, 2010 at 12:09 am
    
    Reply

Looks like a bug (not an enhancement) that exists in 11.1.0.6 and is already fixed in 11.1.0.7.

SQL&gt; select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> create table t1(c1 int, c2 int);

Table created.

SQL> -- c1 = skewed, c2 = normal
SQL> insert into t1
  2  select 1, level
  3  from dual
  4  connect by level <= 10000
  5  union all
  6  select 2, level
  7  from dual
  8  connect by level <= 1000
  9  union all
 10  select 3, level
 11  from dual
 12  connect by level <= 100
 13  union all
 14  select 4, level
 15  from dual
 16  connect by level <= 10
 17  union all
 18  select 5, level
 19  from dual
 20  connect by level  <= 1;

SQL> commit;

Commit complete.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> create index t1_n2 on t1(c2);

Index created.

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

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select count(*)
  3  from t1
  4  where c1 = 4
  5  and c2 between 1 and 10;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 359681750

--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C2">=1 AND "C2" ...

  1 SQL> create or replace outline test_outln4
  2  on
  3  select count(*)
  4  from t1
  5  where c1 = 4
  6  and c2 between 1 and 10;

Outline created.

SQL> alter session set use_stored_outlines = true;

Session altered.

SQL> select hint from user_outline_hints
  2  where name = 'TEST_OUTLN4';

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

SQL> create index t1_n3 on t1(c1, c2);

Index created.

SQL> explain plan for
  2  select count(*)
  3  from t1
  4  where c1 = 4
  5  and c2 between 1 and 10;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 359681750

--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C2">=1 AND "C2" select count(*)
  
  1 SQL> select count(*)
  2  from t1
  3  where c1 = 4
  4  and c2 between 1 and 10;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor(format=>'+outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  0p19ucnnf9zpw, child number 0
-------------------------------------
select count(*) from t1 where c1 = 4 and c2 between 1 and 10

Plan hash value: 359681750

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   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 |
--------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("C2">=1 AND "C2" 

SQL> select count(*)alter session set use_stored_outlines = false;

Session altered.

SQL> select count(*)
  2  from t1
  3  where c1 = 4
  4  and c2 between 1 and 10;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor(format=>'+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  0p19ucnnf9zpw, child number 1
-------------------------------------
select count(*) from t1 where c1 = 4 and c2 between 1 and 10

Plan hash value: 3096360861

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

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=4 AND "C2">=1 AND "C2"&lt;=10)

Greg Rahn

July 26, 2009 at 5:35 am

@Greg

Thanks for the test case. You did what I should have done already.
(I have no access to 11.1.0.7 right now, but let me check it tomorrow)

And your last advice made me realize that I should be more careful when posting article. The online writing sometimes makes me forget the fact that I’m not just writing something, but trying to contribute to the Oracle commnuity.

PS) I edited your test case to use sourcecode tag because <pre> tag has formatting problems with < character.

Dion Cho

July 26, 2009 at 7:06 am

Reply

Hi Dion.

First off let me say that this is a very interesting post and as usual pretty thought provoking.

I believe the whole issue revolves around a design decision to start using the newer form of the index hints that don’t specify the name of the index. So the hint uses the form INDEX(TABLE_NAME COLUMN_NAME) as opposed to the older more specific format INDEX(TABLE_NAME INDEX_NAME). I’m not sure when the newer format became available (I think in 10 but maybe in 9). At any rate, the non-specific format leaves it to the optimizers discretion as to which index to use. This means that the plan can change, even though the outline is being used. While I’m sure the developers had their reasons, it seems a bad design choice considering that outlines were invented to help improve stability of plans by giving the optimizer few if any choices.

This is not an 11g specific issue by the way, as the same behavior exists in 10.2. And it is not specific to Outlines either as it is also an issue with SQL Profiles and Baselines. As an aside, the form of the index hint is not always the non-specific version either. Even in 11.1.0.7, the specific format shows up. I have not tried to figure out how it decides to pick one format or the other (something to look at next time I get bored I guess).

Kerry

Kerry Osborne

July 26, 2009 at 5:29 pm

Reply
- Kerry.
  
  Thanks for the insight on the INDEX hint problem. I also thought it would be a new INDEX hint convention problem. I edited the outline(ol$hints) by replacing Oracle generated hint to the old fashioned one – like INDEX(t1 t1_n1)). But the problem still existed. There could be some mistakes while editing outline.
  
  I would investigate this problem with far more options than I expected this week. :)
  
  Dion Cho
  
  July 26, 2009 at 9:15 pm
  
  Reply
[…] Cho was on another quirk, the stored outline abnormality in Oracle 11g. Bug or enhancement, he wonders, and the readers […]

Log Buffer #156: a Carnival of the Vanities for DBAs | Pythian Group Blog

July 31, 2009 at 10:32 pm

Reply
Dion,

I had a chance to do some investigation on this issue. I did a blog post about it here:

http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/

This post shows a case where creating an outline on a statement causes it’s execution plan to change. Behavior is the same from 10.2.0.3 to 11.1.0.7.

Kerry

Kerry Osborne

August 14, 2009 at 7:51 pm

Reply
- Kerry.
  I was busy on the other stuffs and thanks for the sharing. I would take a careful look at your post.
  
  Dion Cho
  
  August 15, 2009 at 1:58 am
  
  Reply
2 days ago,I test this case under the same condition.]Surprisingly,10G and 11G choosed the composite index.

In this test case,The composite index would be acceptible to optimizer.

Everytime I simulate with my brain assuming the composite index consited of c1 and c2.

C1 is very selective condition with the condition of equal.
C2 is not as good as C1.
Imagine .
Let’s suppose that composite index.
——————————
C1 C2 rowid
——————————
1 1
1 2
1 3
. .
. .
. .
. 10000
2 1
2 2
. .
. 1000
3 1
3 2
3 3
. .
. 100
4 1 <–Index scan begin here
4 2
4 .
. 10 <–end
5 1
————————————–
All the Optimize have to do is index scan to get Count(*).

SANGSEO SEO

September 21, 2009 at 9:43 am

Reply

	Greg Rahn on Interesting case of the restri…
	Dion Cho on How to copy partition stats?…
	Prasad on How to copy partition stats?…
	Log Buffer #209, A C… on Interpreting Parallel MERGE…
	Dion Cho on Interesting combination of RAC…

Dion Cho – Oracle Performance Storyteller