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
