Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sreenivas.

Asked: September 28, 2007 - 11:08 pm UTC

Last updated: November 02, 2011 - 12:05 pm UTC

Version: 9.2.0.3

Viewed 1000+ times

You Asked

Dear Tom,
I need some help in implementing/understanding stored outlines..As per the articles provided from oracle,i understand that outlines will help in stablizing the exectuion plan across environments(test and production).
Here are the steps that i have done and i need some clarification.Pl help me.

Here are the details of our database

Version 9.2.0.3
Optimizer mode=all_rows

select /*+ INDEX(t1 pk_tbl_data_collections)*/
t1.DATA_COLLECTION_ID,t2.object_id,t1.collection_id
from tbl_data_collections t1,tbl_object_data t2,tbl_service_data t3
where t1.DATA_COLLECTION_ID = t2.data_collection_id
and t1.OLD_COLLECTION_ID = t3.data_collection_id
and t1.collection_id = :1
and t1.active = :2
and t1.service_id = :3

for example , we have sql from the code where the binded varibales are the parameters been passed to SQL.I have observed that the cost of explain plan for the above SQL is high and the reason is becuase of the hint applied on the query.
And the same SQL without the hint give me a much faster response time than earlier and the cost is very low.
I have the statistics upto date on the tables but the only differnece is the hint.

Now the problem is i have no control on the source code and so i thought the only way we can implement is through stored outlines.

I have followed these steps in creating the stored outlines.

alter session set create_stored_outlines=true;
create or replace outline
on
select t1.DATA_COLLECTION_ID,t2.object_id,t1.collection_id
from tbl_data_collections t1,tbl_object_data t2,tbl_service_data t3
where t1.DATA_COLLECTION_ID = t2.data_collection_id
and t1.OLD_COLLECTION_ID = t3.data_collection_id
and t1.collection_id = :1
and t1.active = :2
and t1.service_id = :3
;
alter session set use_stored_outlines=true;

When i queried view user_outlines i notice that the outline is created but not used.Why is that?
Is there any wrong in the way i was creating the outline.
I know that the SQL comming from code is not similar to the SQL that the outline is created on.Is that a problem? because i needed the query not to use the hint.I cannot modify the source code?

Please and Please help me out Tom.

Sorry for any typos


Thanks
Sreenivas







and Tom said...

because the query you saved the outline for differs from the query you are actually executing.

You would need to

a) capture the query exactly as it is executed by the application, hints and all
b) get it to have the plan you want in some session by some method (eg: setting session parameters, invalidating the index referenced)
c) capturing that plan and using it.


Or, you could just rename the index, making the hint reference something that doesn't exist, so it would be ignored. Beware however that it would be sort of "global", my experience is if the developers hinted once - they hinted a thousand times (because they didn't really understand what they were doing...) and renaming the index would cause thousands of hints to stop being meaningful.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Am I missing something ?

Nicosa, April 14, 2010 - 11:34 am UTC

Hi Tom,

I'm trying to implement outlines on a 10gR2 (10.2.0.4) on Linux, using what you provided in previous followup :

a) capture the query exactly as it is executed by the application, hints and all
b) get it to have the plan you want in some session by some method (eg: setting session parameters, invalidating the index referenced)
c) capturing that plan and using it.

This is how I do it :
create table T
(
c1 integer,
c2 integer,
c3 integer,
c4 integer,
c5 integer,
c6 integer,
c7 integer,
c8 date,
val1 varchar2(30)
);

alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

create index T_idx on T(c6, c7);


insert into T
select
mod(n,97),
mod(n,89),
mod(n,83),
mod(n,79),
mod(n,73),
mod(n,67),
mod(n,61),
sysdate+(n/(24*3600)),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a')
from (
select level n from dual connect by level <= 1000
);

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
method_opt => 'for all columns size 1',
estimate_percent => null,
cascade => true);
end;
/
The query I want to outline is :
select 
c1, c2, c3, c4, c5, c6, c7, c8
, val1
from T
where c1=19
and c2=54
and c3=28
and c4=41
and c5=16
and c6=51
and c7=18
and c8=(sysdate + 1.2345)
for update nowait;
I put it in a file called q.sql so I'm sure it's always the same (syntax wise)

I want it (for test purpose only) to range_scan T_idx instead of unique scan t_pk.
So I tried to make the index unusable before generating outline, then rebuild it and check weither outline is used or not :
SQL> alter index t_pk unusable;

Index altered.

SQL> create outline myol
  2  for category myol
  3  on
  4  @q

Outline created.

SQL> alter index t_pk rebuild;

Index altered.

SQL> set autotrace traceonly explain
SQL> alter session set use_stored_outlines=myol;

Session altered.

SQL> @q

Execution Plan
----------------------------------------------------------
Plan hash value: 2503694904

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   214 |     3   (0)| 00:00:01 |
|   1 |  FOR UPDATE                  |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.234502314814814814814814814814814814
              81)

Note
-----
   - outline "MYOL" used for this statement
I'm quite lost.... autotrace says the outline myol is used, but I see the t_pk index being used.
One of those must be lying to me, or I missed something.

Here is the content of user_outline_hints for myol outline :
SQL> select * from user_outline_hints where name='MYOL';

NAME   NODE  STAGE  JOIN_POS HINT
----- ----- ------ --------- -------------------------------------------------------
MYOL      1      1         1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."C6" "T"."C7"))
MYOL      1      1         0 OUTLINE_LEAF(@"SEL$1")
MYOL      1      1         0 ALL_ROWS
MYOL      1      1         0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
MYOL      1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

5 rows selected.


Can you help ?
Tom Kyte
April 14, 2010 - 4:10 pm UTC

your example does not work, I cannot reproduce as it will not use t_pk for me.

Your insert fails

ops$tkyte%ORA10GR2> insert into T
  2  select
  3  mod(n,97),
  4  mod(n,89),
  5  mod(n,83),
  6  mod(n,79),
  7  mod(n,73),
  8  mod(n,67),
  9  mod(n,61),
 10  sysdate+(n/(24*3600)),
 11  rpad('a',30,'a'),
 12  rpad('a',30,'a'),
 13  rpad('a',30,'a'),
 14  rpad('a',30,'a'),
 15  rpad('a',30,'a'),
 16  rpad('a',30,'a')
 17  from (
 18  select level n from dual connect by level <= 1000
 19  );
insert into T
            *
ERROR at line 1:
ORA-00913: too many values



please run things like I do and provide SQLPlus CUT AND PASTE so we know what you actually run - not what you might have intended to run...

I feel stupid...

Nicosa, April 15, 2010 - 4:09 am UTC

Sorry Tom,

Trying to make it easier to read, I removed what I thought would be extra-columns... This was stupid.
All my apologizes.

I use 3 files for my example :
-> createTable.sql :
create table T
(
c1 integer,
c2 integer,
c3 integer,
c4 integer,
c5 integer,
c6 integer,
c7 integer,
c8 date,
val1 varchar2(30),
val2 varchar2(30),
val3 varchar2(30),
val4 varchar2(30),
val5 varchar2(30),
val6 varchar2(30)
);

alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

create index T_idx on T(c6, c7);

insert into T
select
mod(n,97),
mod(n,89),
mod(n,83),
mod(n,79),
mod(n,73),
mod(n,67),
mod(n,61),
sysdate+(n/(24*3600)),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a')
from (
select level n from dual connect by level <= 1000
);
commit;

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
method_opt => 'for all columns size 1',
estimate_percent => null,
cascade => true);
end;
/
-> q.sql (the query) :
select 
c1, c2, c3, c4, c5, c6, c7, c8
, val1, val2, val3, val4, val5, val6
from T
where c1=19
and c2=54
and c3=28
and c4=41
and c5=16
and c6=51
and c7=18
and c8=(sysdate + 1.2345)
for update nowait;
-> the "main" example file :
set lines 100
set tab off
set echo on
set feed on
set long 30000

@createTable.sql

alter index t_pk unusable;

create outline myol for category myol on
@q.sql

alter index t_pk rebuild;

alter session set use_stored_outlines=myol;

set autotrace traceonly explain

@q.sql

set autotrace off

col name for A4
col hint for A55
col node for 99999
col stage for 99999
select * from user_outline_hints where name='MYOL';


Now the run :
The output of sqlplus scott/tiger @fullExample.sql is as follow : 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 15 10:57:00 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.

SQL> set feed on
SQL> set long 30000
SQL> 
SQL> @createTable.sql
SQL> create table T
  2  (
  3  c1 integer,
  4  c2 integer,
  5  c3 integer,
  6  c4 integer,
  7  c5 integer,
  8  c6 integer,
  9  c7 integer,
 10  c8 date,
 11  val1 varchar2(30),
 12  val2 varchar2(30),
 13  val3 varchar2(30),
 14  val4 varchar2(30),
 15  val5 varchar2(30),
 16  val6 varchar2(30)
 17  );

Table created.

SQL> 
SQL> alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

Table altered.

SQL> 
SQL> create index T_idx on T(c6, c7);

Index created.

SQL> 
SQL> insert into T
  2  select
  3  mod(n,97),
  4  mod(n,89),
  5  mod(n,83),
  6  mod(n,79),
  7  mod(n,73),
  8  mod(n,67),
  9  mod(n,61),
 10  sysdate+(n/(24*3600)),
 11  rpad('a',30,'a'),
 12  rpad('a',30,'a'),
 13  rpad('a',30,'a'),
 14  rpad('a',30,'a'),
 15  rpad('a',30,'a'),
 16  rpad('a',30,'a')
 17  from (
 18  select level n from dual connect by level <= 1000
 19  );

1000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname => user,
  4  tabname => 'T',
  5  method_opt => 'for all columns size 1',
  6  estimate_percent => null,
  7  cascade => true);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> alter index t_pk unusable;

Index altered.

SQL> 
SQL> create outline myol for category myol on
  2  @q.sql
  2  select
  3  c1, c2, c3, c4, c5, c6, c7, c8
  4  , val1, val2, val3, val4, val5, val6
  5  from T
  6  where c1=19
  7  and c2=54
  8  and c3=28
  9  and c4=41
 10  and c5=16
 11  and c6=51
 12  and c7=18
 13  and c8=(sysdate + 1.2345)
 14  for update nowait;

Outline created.

SQL> 
SQL> alter index t_pk rebuild;

Index altered.

SQL> 
SQL> alter session set use_stored_outlines=myol;

Session altered.

SQL> 
SQL> set autotrace traceonly explain
SQL> 
SQL> @q.sql
SQL> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

Execution Plan
----------------------------------------------------------
Plan hash value: 2503694904

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   214 |     2   (0)| 00:00:01 |
|   1 |  FOR UPDATE                  |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.234502314814814814814814814814814814
              81)

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

SQL> 
SQL> set autotrace off
SQL> 
SQL> col name for A4
SQL> col hint for A55
SQL> col node for 99999
SQL> col stage for 99999
SQL> select * from user_outline_hints where name='MYOL';

NAME   NODE  STAGE   JOIN_POS HINT
---- ------ ------ ---------- -------------------------------------------------------
MYOL      1      1          1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."C6" "T"."C7"))
MYOL      1      1          0 OUTLINE_LEAF(@"SEL$1")
MYOL      1      1          0 ALL_ROWS
MYOL      1      1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
MYOL      1      1          0 IGNORE_OPTIM_EMBEDDED_HINTS

5 rows selected.


Still the same problem :
- Autotrace says myol outline is used
- but I see it using T_PK where I wanted it to use T_IDX

I must be missing something...
Tom Kyte
April 15, 2010 - 8:49 am UTC

I'm having troubles reproducing.

anything 'different' about you? non-standard init.ora settings?

ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> set linesize 10000
ops$tkyte%ORA10GR2> drop table t purge;

Table dropped.

ops$tkyte%ORA10GR2> drop outline myol;

Outline dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a80 truncate
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table T
  2  (
  3  c1 integer,
  4  c2 integer,
  5  c3 integer,
  6  c4 integer,
  7  c5 integer,
  8  c6 integer,
  9  c7 integer,
 10  c8 date,
 11  val1 varchar2(30),
 12  val2 varchar2(30),
 13  val3 varchar2(30),
 14  val4 varchar2(30),
 15  val5 varchar2(30),
 16  val6 varchar2(30)
 17  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index T_idx on T(c6, c7);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into T
  2  select
  3  mod(n,97),
  4  mod(n,89),
  5  mod(n,83),
  6  mod(n,79),
  7  mod(n,73),
  8  mod(n,67),
  9  mod(n,61),
 10  sysdate+(n/(24*3600)),
 11  rpad('a',30,'a'),
 12  rpad('a',30,'a'),
 13  rpad('a',30,'a'),
 14  rpad('a',30,'a'),
 15  rpad('a',30,'a'),
 16  rpad('a',30,'a')
 17  from (
 18  select level n from dual connect by level <= 1000
 19  );

1000 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  dbms_stats.gather_table_stats(
  3  ownname => user,
  4  tabname => 'T',
  5  method_opt => 'for all columns size 1',
  6  estimate_percent => null,
  7  cascade => true);
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @q.sql
ops$tkyte%ORA10GR2> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 2503694904

--------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |      |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     2   (0)| 00:00
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00
--------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.2345023148148148148148148
              81)


24 rows selected.

ops$tkyte%ORA10GR2> alter index t_pk unusable;

Index altered.

ops$tkyte%ORA10GR2> @q.sql
ops$tkyte%ORA10GR2> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 882521605

--------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |       |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |   214 |     2   (0)| 00:0
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:0
--------------------------------------------------------------------------------

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

   2 - filter(("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C8"=SYSDATE@!+1.23450231481481481481481481481481481481))
   3 - access("C6"=51 AND "C7"=18)


24 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create outline myol for category myol on
  2  @q.sql
  2  select
  3  c1, c2, c3, c4, c5, c6, c7, c8
  4  , val1, val2, val3, val4, val5, val6
  5  from T
  6  where c1=19
  7  and c2=54
  8  and c3=28
  9  and c4=41
 10  and c5=16
 11  and c6=51
 12  and c7=18
 13  and c8=(sysdate + 1.2345)
 14  for update nowait;

Outline created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter index t_pk rebuild;

Index altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set use_stored_outlines=myol;

Session altered.

ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2> @q.sql
ops$tkyte%ORA10GR2> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 882521605

--------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |       |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |   214 |     2   (0)| 00:0
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:0
--------------------------------------------------------------------------------

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

   2 - filter(("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C8"=SYSDATE@!+1.23450231481481481481481481481481481481))
   3 - access("C6"=51 AND "C7"=18)

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


28 rows selected.

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> @q.sql
ops$tkyte%ORA10GR2> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

Execution Plan
----------------------------------------------------------
Plan hash value: 882521605

--------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |   214 |     2   (0)| 00:0
|   1 |  FOR UPDATE                  |       |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |   214 |     2   (0)| 00:0
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:0
--------------------------------------------------------------------------------

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

   2 - filter("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C8"=SYSDATE@!+1.23450231481481481481481481481481481481)
   3 - access("C6"=51 AND "C7"=18)

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

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

This my sandbox db...

Nicosa, April 15, 2010 - 9:55 am UTC

Tom,

the test is done on my "sandbox" db (I use to test lots of things on it, so init parameters might be anything... but I don't remember doing something spécially strange).

Would the following query show it ?
SQL> select name, value from v$parameter where isdefault!='TRUE';

NAME                           VALUE
------------------------------ ------------------------------------------------------------
processes                      50
sessions                       60
nls_language                   FRENCH
nls_territory                  FRANCE
nls_date_format                yyyy/mm/dd hh24:mi:ss
sga_target                     209715200
control_files                  /oracleDatas/MY10G/controlfile/o1_mf_58tpcw32_.ctl, /oracleD
                               atas/MY10G/controlfile/o1_mf_58tpcwc5_.ctl

db_block_size                  8192
compatible                     10.2.0.3.0
log_archive_dest               /oracleDatas/MY10G/archivelog/
db_file_multiblock_read_count  16
db_create_file_dest            /oracleDatas
db_recovery_file_dest_size     1073741824
undo_management                AUTO
undo_tablespace                UNDOTBS1
remote_login_passwordfile      EXCLUSIVE
db_domain
dispatchers                    (PROTOCOL=TCP) (SERVICE=my10gXDB)
job_queue_processes            10
background_dump_dest           /oracleSofts/product/10g/admin/my10g/bdump
user_dump_dest                 /oracleSofts/product/10g/admin/my10g/udump
max_dump_file_size             1024000
core_dump_dest                 /oracleSofts/product/10g/admin/my10g/cdump
audit_file_dest                /oracleSofts/product/10g/admin/my10g/adump
db_name                        my10g
open_cursors                   300
pga_aggregate_target           52428800

27 rows selected.


I also tried to change star_transformation_enabled to true (but didn't bounce the DB since as it is supposed to be sys_modifiable=immediate) but with no effect.
Tom Kyte
April 15, 2010 - 10:02 am UTC

nothing strange there

suggestion: trying flushing shared pool and or reconnecting after the outline is created, before alter session is issued.


and use this script:

set echo on
set linesize 10000
drop table t purge;
drop outline myol;

column PLAN_TABLE_OUTPUT format a80 truncate

create table T
(
c1 integer,
c2 integer,
c3 integer,
c4 integer,
c5 integer,
c6 integer,
c7 integer,
c8 date,
val1 varchar2(30),
val2 varchar2(30),
val3 varchar2(30),
val4 varchar2(30),
val5 varchar2(30),
val6 varchar2(30)
);

alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

create index T_idx on T(c6, c7);

insert into T
select
mod(n,97),
mod(n,89),
mod(n,83),
mod(n,79),
mod(n,73),
mod(n,67),
mod(n,61),
sysdate+(n/(24*3600)),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a'),
rpad('a',30,'a')
from (
select level n from dual connect by level <= 1000
);
commit;

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
method_opt => 'for all columns size 1',
estimate_percent => null,
cascade => true);
end;
/

@q.sql
select * from table(dbms_xplan.display_cursor);
alter index t_pk unusable;
@q.sql
select * from table(dbms_xplan.display_cursor);

create outline myol for category myol on
@q.sql

alter index t_pk rebuild;




connect /
alter system flush shared_pool;




alter session set use_stored_outlines=myol;
set serveroutput off
@q.sql
select * from table(dbms_xplan.display_cursor);
set autotrace traceonly explain
@q.sql
set autotrace off



Doesn't compute... (^_^)

Nicosa, April 15, 2010 - 12:05 pm UTC

Tom,

Thanks for your time !
This part doesn't compute on my machine :
SQL> connect /
ERROR:
ORA-01017: invalid username/password; logon denied


I allowed myself to modify the end of the script as follows :
-added a "set tab off" for nice formatting when pasting on your site
-added "as sysdba" and a "conn scott/tiger" after the shared_pool flush.
(hope this was what you intended)

The end of the script now looks like this :
connect /as sysdba
alter system flush shared_pool;

conn scott/tiger


alter session set use_stored_outlines=myol;
set serveroutput off
@q.sql
select * from table(dbms_xplan.display_cursor);
set autotrace traceonly explain
@q.sql
set autotrace off

I also had to grant 'select any dictionary' to scott to allow dbms_xplan.display_cursor to select from v$session.

Here's the output :
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 15 18:55:28 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 10000
SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop outline myol;
drop outline myol
*
ERROR at line 1:
ORA-18002: the specified outline does not exist


SQL> 
SQL> column PLAN_TABLE_OUTPUT format a80 truncate
SQL> 
SQL> create table T
  2  (
  3  c1 integer,
  4  c2 integer,
  5  c3 integer,
  6  c4 integer,
  7  c5 integer,
  8  c6 integer,
  9  c7 integer,
 10  c8 date,
 11  val1 varchar2(30),
 12  val2 varchar2(30),
 13  val3 varchar2(30),
 14  val4 varchar2(30),
 15  val5 varchar2(30),
 16  val6 varchar2(30)
 17  );

Table created.

SQL> 
SQL> alter table T add constraint T_pk primary key (c1, c2, c3, c4, c5, c6, c7, c8);

Table altered.

SQL> 
SQL> create index T_idx on T(c6, c7);

Index created.

SQL> 
SQL> insert into T
  2  select
  3  mod(n,97),
  4  mod(n,89),
  5  mod(n,83),
  6  mod(n,79),
  7  mod(n,73),
  8  mod(n,67),
  9  mod(n,61),
 10  sysdate+(n/(24*3600)),
 11  rpad('a',30,'a'),
 12  rpad('a',30,'a'),
 13  rpad('a',30,'a'),
 14  rpad('a',30,'a'),
 15  rpad('a',30,'a'),
 16  rpad('a',30,'a')
 17  from (
 18  select level n from dual connect by level <= 1000
 19  );

1000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname => user,
  4  tabname => 'T',
  5  method_opt => 'for all columns size 1',
  6  estimate_percent => null,
  7  cascade => true);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> @q.sql
SQL> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 2503694904

--------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |      |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     2   (0)| 00:00
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00
--------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.2345023148148148148148148

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              81)


24 rows selected.

SQL> alter index t_pk unusable;

Index altered.

SQL> @q.sql
SQL> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 882521605

--------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |       |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |   214 |     2   (0)| 00:0
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:0
--------------------------------------------------------------------------------

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

   2 - filter(("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C8"=SYSDATE@!+1.23450231481481481481481481481481481481))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - access("C6"=51 AND "C7"=18)


24 rows selected.

SQL> 
SQL> create outline myol for category myol on
  2  @q.sql
  2  select
  3  c1, c2, c3, c4, c5, c6, c7, c8
  4  , val1, val2, val3, val4, val5, val6
  5  from T
  6  where c1=19
  7  and c2=54
  8  and c3=28
  9  and c4=41
 10  and c5=16
 11  and c6=51
 12  and c7=18
 13  and c8=(sysdate + 1.2345)
 14  for update nowait;

Outline created.

SQL> 
SQL> alter index t_pk rebuild;

Index altered.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> connect /as sysdba
Connected.
SQL> alter system flush shared_pool;

System altered.

SQL> 
SQL> conn scott/tiger
Connected.
SQL> 
SQL> 
SQL> alter session set use_stored_outlines=myol;

Session altered.

SQL> set serveroutput off
SQL> @q.sql
SQL> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0hp10pkdk56ky, child number 0
-------------------------------------
select c1, c2, c3, c4, c5, c6, c7, c8 , val1, val2, val3, val4, val5, val6
from T where c1=19 and c2=54 and c3=28 and c4=41 and c5=16 and c6=51 and
c7=18 and c8=(sysdate + 1.2345) for update nowait

Plan hash value: 2503694904

--------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|
|   1 |  FOR UPDATE                  |      |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     2   (0)| 00:00
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00
--------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.2345023148148148148148148

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              81)

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


28 rows selected.

SQL> set autotrace traceonly explain
SQL> @q.sql
SQL> select
  2  c1, c2, c3, c4, c5, c6, c7, c8
  3  , val1, val2, val3, val4, val5, val6
  4  from T
  5  where c1=19
  6  and c2=54
  7  and c3=28
  8  and c4=41
  9  and c5=16
 10  and c6=51
 11  and c7=18
 12  and c8=(sysdate + 1.2345)
 13  for update nowait;

Execution Plan
----------------------------------------------------------
Plan hash value: 2503694904

--------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   214 |     2   (0)| 00:00
|   1 |  FOR UPDATE                  |      |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   214 |     2   (0)| 00:00
|*  3 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00
--------------------------------------------------------------------------------

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

   3 - access("C1"=19 AND "C2"=54 AND "C3"=28 AND "C4"=41 AND "C5"=16 AND
              "C6"=51 AND "C7"=18 AND "C8"=SYSDATE@!+1.2345023148148148148148148
              81)

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

SQL> set autotrace off
SQL>
Still the strange behavior...

By the way, i had the opportunity to test it on another database (Linux OS too, same DB version) and it worked...
(of course, the test is not exactly the same as it is a true table with true data, and the query was using bind variables)
That would make me think the problem is with my "sandbox" db... I must have done something strange to it.
If I have time tomorrow, I'll create a brand new DB on the same system to see if this reproduces.
Tom Kyte
April 15, 2010 - 12:36 pm UTC

connect /

is in fact

connect user/password


you wouldn't have my user/password - and actually mine is just blank and blank.

How does OUTLINE work for same query with different HASH_VALUE

Sanju, June 15, 2010 - 2:40 am UTC

Oracle Version: 9.2.0.8

Hi Tom,
My application is executing a query which is of HASH_VALUE=568965. I take this query and run the same from SQLPLUS. The HASH_VALUE changes to 687956. I can understand why this happens.

Now when I create the Stored Outline, it says the HASH_VALUE is 687956(The one I executed from SQLPLUS). If I set the system to use the STORED_OUTLINE and execute the query from the application, I can see that the executed query is of HASH_VALUE 568965(since executed from application) but the stored outline is still used. How is this possible? As HASH_VALUE from OUTLN.OL$ says that it is on 687956. So should not it be executing only when HASH_VALUE is 687956(sqlplus) and not 568965(application).

Please can you explain this??

Thank You,
Sanju

Please can you explain?
Tom Kyte
June 22, 2010 - 10:16 am UTC

... I can understand why this happens. ..

did you mean "cannot"?

probably you added whitespace or changed the query just a tiny bit when you ran it in sqlplus - it has to match EXACTLY the query as it existed in the application - down to the use of spaces, tabs, newlines and all.


Lots of stored outlines

Lise, July 01, 2010 - 8:45 am UTC

Hi,
We are upgrading from 9.2 to 11.1 and find that due to having several GROUP BY queries without ORDER BY (even though we need them to be ordered), will of course not be ordered in our 'new' db due to the introduction of HASH GROUP BY algorithm.
There are lots of code changes to be made (approx 200 objects) and the testing of course. We will not meet our deadline.
The options are to use the hidden parameter _gby_hash_aggregation_enabled, or get all the stored outlines from the 9i database, test each one to ensure that they are being used as we expect them to, or change all the code and miss the deadline. Is there any other way, and if not what would you recommend please?

Tom Kyte
July 06, 2010 - 2:21 pm UTC

... will of
course not be ordered in our 'new' db due to the introduction of HASH GROUP BY
algorithm.
...

No, I disagree with you. They will not be ordered because they NEVER HAD TO BE ORDERED and might have sometimes appeared to be ordered - but they were not.

In no version of Oracle - ever - did a group by have to sort data. A simple additional index - data would be returned "non sorted". Introduce partitions - not sorted. Gather statistics - not sorted. Have a client with a character set that doesn't sort binary - NOT sorted in all cases.


... There are lots of code changes to be made (approx 200 objects) and the testing
of course. We will not meet our deadline.
...


I would explain to management that the developed code has a serious bug that must be fixed.


That is what I would recommend. Anything else will not actually get the problem fixed - ever.

CREATE_STORED_OUTLINES - Missing in 9iR2.

Rajeshwaran Jeyabal, August 09, 2010 - 12:22 pm UTC 

test@9iR2> select * from v$version;

BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

test@9iR2> show parameter create_stored;
test@9iR2>
test@9iR2>
test@9iR2> show parameter create;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
test@9iR2>
test@9iR2>
test@9iR2>
test@9iR2>
test@9iR2> show parameter CREATE_STORED_OUTLINES;
test@9iR2>
test@9iR2>
test@9iR2>
test@9iR2>
test@9iR2> SELECT *
  2  FROM V$PARAMETER
  3  WHERE upper(NAME) LIKE '%CREATE_STORED_OUTLINES%'
  4  /

no rows selected

Elapsed: 00:00:00.00
test@9iR2>


But in Oracle 10gR2

scott@10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

scott@10GR2> show parameter CREATE_STORED_OUTLINES;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
create_stored_outlines               string
scott@10GR2>
scott@10GR2>
scott@10GR2> SELECT name,value
  2  FROM V$PARAMETER
  3  WHERE upper(NAME) LIKE '%CREATE_STORED_OUTLINES%'
  4  /

NAME       VALUE
------------------------ ------------------------------------
create_stored_outlines

Elapsed: 00:00:00.03


Tom:

Referring from Oracle product documentation, I could not find create_stored_outlines in 9iR2, Is that missing in 9.2.0.8 patch sets?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/outlines.htm#26854
Tom Kyte
August 09, 2010 - 2:02 pm UTC

it just seems missing from v$parameter

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> alter system set CREATE_STORED_OUTLINES = true;

System altered.



http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2071573

... The CREATE_STORED_OUTLINES parameter determines whether Oracle should automatically create and store an outline for each query submitted on the system. CREATE_STORED_OUTLINES is not an initialization parameter. ...


it wasn't included in v$parameter (initialization parameters)


Stored Oulines for Index

Rajeshwaran, Jeyabal, May 07, 2011 - 11:48 am UTC

Tom:

The below Testcase is for learning purpose and not for any real projects.

rajesh@ORA10GR2> create table t
  2  nologging
  3  as
  4  select     mod(rownum,10) as x,
  5             sysdate        as y,
  6             rpad('x',40,'x')||rownum as z,
  7             a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:05.15
rajesh@ORA10GR2>
rajesh@ORA10GR2> create index t_ind_01 on t(z) nologging;

Index created.

Elapsed: 00:00:00.43
rajesh@ORA10GR2>
rajesh@ORA10GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'T',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     method_opt=>'for all indexed columns size 254');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.81
rajesh@ORA10GR2>


I know histograms on column 'Z' doesn't provide proper cardinality estimates. Since data is more 32 char's.

rajesh@ORA10GR2> variable x varchar2(50);
rajesh@ORA10GR2> exec :x := rpad('x',40,'x')||'1';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2> select *
  2  from t
  3  where z = :x;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35824 |  5387K|   444   (1)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    | 35824 |  5387K|   444   (1)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("Z"=:X)


Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
       1603  consistent gets
          0  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA10GR2>
rajesh@ORA10GR2> select /*+ index(t,t_ind_01) */ *
  2  from t
  3  where z = :x;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 244289671

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 35824 |  5387K|  7747   (1)| 00:01:33 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        | 35824 |  5387K|  7747   (1)| 00:01:33 |
|*  2 |   INDEX RANGE SCAN          | T_IND_01 | 35824 |       |   287   (0)| 00:00:04 |
----------------------------------------------------------------------------------------

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

   2 - access("Z"=:X)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;


Is there is a way to implement Stored outlines saying then when the below query is executed always go for Index Range scan rather than Full Table scan?

select *
from t
where z = :x;


I don't know how to Implement this, but tried it doesn't work.

rajesh@ORA10GR2> create or replace outline test_outln
  2  for category test_ctgry
  3  on
  4  select *
  5  from t
  6  where z = :x;

Outline created.

Elapsed: 00:00:00.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable x varchar2(50);
rajesh@ORA10GR2> exec :x := rpad('x',40,'x')||'1';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set use_stored_outlines=TEST_CTGRY;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2> select *
  2  from t
  3  where z = :x;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35824 |  5387K|   444   (1)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    | 35824 |  5387K|   444   (1)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("Z"=:X)

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


Statistics
----------------------------------------------------------
        525  recursive calls
        147  db block gets
       1691  consistent gets
          0  physical reads
        624  redo size
       1607  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA10GR2> set autotrace off;


I know in Oracle 10g we have SQL Profiles, which is far better than Outlines and also

Outlines = way to force a particular plan
Sql profiles = provides, cardinality information and subject to influence a plan but not tied to a single plan.

you would suggest me to use SQL Profiles rather than Stored outlines.

But, Is there is a way to implement Stored outlines for the below query saying go for Index Range scan rather than Full Table scan? 

select *
from t
where z = :x;



Tom Kyte
May 09, 2011 - 7:09 am UTC

likely, if you set your optimizer goal to first_rows(1), it would choose the index access. So, change your session optimizer settings, then create the stored outline. Presuming it uses the index (likely), then you would have stored the right plan, the plan you wish to have stored.



A reader, May 09, 2011 - 11:01 am UTC

Hello Sir,

value in column is z is already unique since the op is concating rownum at the end of string, I have 2 question

(1) Did not in this case histogram is Unnecessary

(2) why index is not used


Thanks for your time


Tom Kyte
May 09, 2011 - 11:25 am UTC

the optimizer doesn't know it is unique.

How would it know? There is no unique constraint.


The index is not used because the estimated cardinality is very high. We gather statistics on the first 32 bytes of string data - if you have a string that has lots of repeating long strings on the front of it - the statistics are not going to be very good.

see
http://www.oracle.com/technetwork/issue-archive/2011/11-jan/o11asktom-195084.html

parallel hint in stored outline

abhishek, November 02, 2011 - 10:09 am UTC 

Hi Tom,

Is is possible to embed parallel hint in stored outline.

scott@ORCL> create table t (x int);

Table created.

scott@ORCL> insert into t values (1);

1 row created.

scott@ORCL> insert into t values (2);

1 row created.

scott@ORCL> insert into t values (3);

1 row created.

scott@ORCL> insert into t values (4);

1 row created.

scott@ORCL> insert into t values (5);

1 row created.

scott@ORCL> select * from t;

         X
----------
         1
         2
         3
         4
         5
 
Now is it possible to create outline so that whenever I execute the query "select * from t T1;" it automatically get executed as "select /*+ parallel(t,30) Full(T1) */ * from t T1;"

Thanks,
Abhishek

Tom Kyte
November 02, 2011 - 12:05 pm UTC

stored outlines are deprecated. query plan baselines are not.


search for "So being able to create a Baseline on an existing statement is useful, but being able to create a Baseline on one statement, and then attach it to a different statement is awesome."

on this page:

http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library