JL Computer Consultancy

Hacking Outlines

Nov 2005


There are a couple of (old) articles on this website about using stored outlines in 8i and 9i. And one of the important points made in the second article is that is is much riskier hacking outlines in 9i than it used to be in 8i because there is so much more information in them that (probably) has to be kept consistent.

This note was written as a response to a question that appeared on the comp.databases.oracle.server newsgroup from someone having problems with getting a stored outline to work in 9i.  It is a very simple demo and I don’t guarantee that it addresses all possible issues, but it may help some people get the answer they need. There are, however, a few variations and comments embedded in the text to give you some ideas about further investigations.

The script connects as sys, drops and recreates a couple of schema names, and references a specific tablespace. So read and edit carefully before you use it. The main point of the script is to show that an outline created by one account can be used by another account – without any changes to the underlying information that defines the outline, even though the outln.ol$ table holds the actual username (not userid) of the user who created the outline, and the outln.ol$hints table includes the schema name in a list of tables referenced (column user_table_name).


rem

rem   Script:           outline_hack.sql

rem   Author:           Jonathan Lewis

rem   Dated:            Nov 2005

rem   Purpose:          Simple demo of fixing outlines manually

rem

rem   Last tested

rem         10.1.0.4

rem         9.2.0.6

rem   Not relevant (see below)

rem         8.1.7.4

rem

rem   Notes:

rem   This script connects as SYS - check for passwords

rem   This script drops and creates two users - check for pre-existence

rem   This script references a specific tablespace - edit as necessary

rem

rem   We create two users with identical tables but different

rem   indexes, and generate a plan from the user with no indexes.

rem   We then use that plan in the other account, and show that

rem   the user with a very good index does not use it, instead

rem   it follows the plan.

rem

rem   Dropping user u2 after it had created

rem   an outline did not delete the outline

rem

rem   If you have cursor_sharing set to FORCE or SIMILAR,

rem   then stored outline will be for the bind-substituted

rem   text, and apply for both SQL statements. You need to

rem   set the cursor_sharing attribute each time you connect.

rem

rem   If you have pre-existing cursors in the library cache

rem   before you start using the outline, then the pre-existing

rem   cursors will be re-used. This is not relevant in the test

rem   case, as we drop and recreate the objects, thus invalidating

rem   any cursors that might have been around.

rem

rem   This script is not relevant for 8i because it references

rem   columns and views that don't exist until 9i.  For an

rem   8i version, you merely need to enable sql_trace when

rem   you run the query against user U1, then EXIT and check

rem   the execution plan you find in the trace file.

rem

 

connect sys/sys as sysdba

 

drop user u1 cascade;

drop user u2 cascade;

 

create user u1 identified by u1

default tablespace test_8k

quota 10M on test_8k

;

 

grant

      create session,

      alter session,

      create table,

      create any outline

to u1;

 

create user u2 identified by u2

default tablespace test_8k

quota 10M on test_8k

;

 

grant

      create session,

      alter session,

      create table,

      create any outline

to u2;

 

create table u1.t1 as

select

      rownum            id,

      lpad(rownum,10)   small_vc,

      rpad('x',100)     padding

from

      all_objects

where

      rownum <= 10000

;

 

create index u1.t1_pk on u1.t1(id);

alter table u1.t1 add constraint t1_pk primary key(id);

 

--    should use dbms_stats really.

analyze table u1.t1 compute statistics;

 

 

create table u2.t1 as

select

      rownum            id,

      lpad(rownum,10)   small_vc,

      rpad('x',100)     padding

from

      all_objects

where

      rownum <= 10000

;

 

--    should use dbms_stats really.

analyze table u2.t1 compute statistics;

 

--

--    Now connect to the user who has no index

--    and capture an outline for a query on the

--    table. It will be a full tablescan.

--

 

connect u2/u2

--    alter session set cursor_sharing = force;

 

alter session set create_stored_outlines = true;

select small_vc from t1 where id = 99;

alter session set create_stored_outlines = false;

 

--

--    Now connect to the user who has the index

--    and run the query after enabling outline.

--    It will do a tablescan because of the outline.

--    Run a similar query to show that that user

--    have used the index.

--

 

connect u1/u1

--    alter session set cursor_sharing = force;

 

alter session set use_stored_outlines = true;

select small_vc from t1 where id = 99;

select small_vc from t1 where id = 100;

alter session set use_stored_outlines = false;

 

--

--    connect back to SYS to show that there are

--    two child cursors for the first query (there

--    should be because of name resolution), and show

--    that the plans are both for tablescans, despite

--    the existence of the PK index. Then show that

--    the plan for the second query was an indexed access.

--

 

connect sys/sys as sysdba

 

spool outline_hack

set feedback off

set linesize 120

set trimspool on

 

column hash_value new_value m_hash

column sql_text format a45

column plan format a45

 

select

      parsing_user_id,

      hash_value,

      child_number,

      executions,

      plan_hash_value,

      sql_text

from

      v$sql

where

      sql_text = 'select small_vc from t1 where id = 99'

--    sql_text like 'select small_vc from t1 where id%'

;

 

break on child_number skip 1

 

prompt

prompt      The plans when the outline is enabled

prompt

 

select

      child_number,

      substr(

            rpad(' ',2*depth) ||

            operation || ' ' ||

            object_owner || ' ' ||

            object_name || ' ' ||

            options,

            1,50

      )     plan

from

      v$sql_plan

where

      hash_value = &m_hash

order by

      child_number,

      id

;

 

 

select

      parsing_user_id,

      hash_value,

      child_number,

      executions,

      plan_hash_value,

      sql_text

from

      v$sql

where

      sql_text = 'select small_vc from t1 where id = 100'

;

 

 

prompt

prompt      The plan when there is no outline

prompt

 

select

      child_number,

      substr(

            rpad(' ',2*depth) ||

            operation || ' ' ||

            object_owner || ' ' ||

            object_name || ' ' ||

            options,

            1,50

      )     plan

from

      v$sql_plan

where

      hash_value = &m_hash

order by

      child_number,

      id

;

 

spool off

 


Here’s a sample output from the script.

Note that the query ‘select small_vc from t1 where id = 99’ shows two child cursors, one for schema u1, and one for schema u2. However, the cursors show the same value for plan_hash_value, and the execution plans both shows a full tablescan. On the other hand, when u1 runs ‘select small_vc from t1 where id = 100’, the execution plan is an indexed access path.


 

PARSING_USER_ID HASH_VALUE CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT

--------------- ---------- ------------ ---------- --------------- ---------------------------------------------

            107 3954279650            0          1      3617692013 select small_vc from t1 where id = 99

            106 3954279650            1          1      3617692013 select small_vc from t1 where id = 99

 

The plans when the outline is enabled

 

 

CHILD_NUMBER PLAN

------------ ---------------------------------------------

           0 SELECT STATEMENT

               TABLE ACCESS U2 T1 FULL

 

           1 SELECT STATEMENT

               TABLE ACCESS U1 T1 FULL

 

 

PARSING_USER_ID HASH_VALUE CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT

--------------- ---------- ------------ ---------- --------------- ---------------------------------------------

            106 1479029236            0          1       785719456 select small_vc from t1 where id = 100

 

 

The plan when there is no outline

 

CHILD_NUMBER PLAN

------------ ---------------------------------------------

           0 SELECT STATEMENT

               TABLE ACCESS U1 T1 BY INDEX ROWID

                 INDEX U1 T1_PK RANGE SCAN

 


Back to Index of Topics