Steps for creating Outlines
SQL> set line 200 pagesize 100 echo on
SQL> create user scott identified by tiger ;
User created.
SQL> alter user scott default tablespace users temporary tablespace temp;
User altered.
SQL> grant connect , resource , execute any procedure to scott;
Grant succeeded.
sql> Grant , connect, resource to scott;
SQL> grant create any outline to scott;
Grant succeeded.
SQL> grant execute on dbms_outln to scott;
Grant succeeded.
SQL> grant execute on dbms_outln_edit to scott;
SQL> grant create any outline, drop any outline to scott;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
SQL> Grant EXECUTE ANY PROCEDURE to scott;
To confirm login with scott and view the session privelidges -
SQL> connect scott/tiger
Connected.
SQL> select * from session_privs
2 /
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
11 rows selected.
---- Login to scott schema and create table, insert records and create index
CREATE TABLE scott.emp
(empno NUMBER ,
ename VARCHAR2(10) ,
job VARCHAR2(9),
mgr NUMBER ,
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(10,2)
,
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2)
)
/
Insert few records
INSERT INTO SCOTT."EMP" (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO) VALUES ('10', 'John', 'MGR', TO_DATE('01-Dec-2012', 'DD-MON-RR'), '1000', '100', '40')
/
INSERT INTO SCOTT."EMP" (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO) VALUES ('20', 'Eurel', 'QA', TO_DATE('02-Dec-2012', 'DD-MON-RR'), '2000', '200', '40')
/
INSERT INTO SCOTT."EMP" (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO) VALUES ('40', 'Dave', 'Tester', TO_DATE('04-DEC-2012', 'DD-MON-RR'), '2400', '24', '30')
INSERT INTO SCOTT."EMP" (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO) VALUES ('30', 'Lela', 'QA', TO_DATE('03-DEC-2012', 'DD-MON-RR'), '3000', '300', '30')
/
INSERT INTO SCOTT."EMP" (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO) VALUES ('50', 'TOM', 'MGR', TO_DATE('03-DEC-2012', 'DD-MON-RR'), '2400', '24', '40')
/
create index idx_emp_empno on emp(empno) tablespace users
/
/* Create sample outline by scott user and check later execution path */
SQL> create outline empoln on select * from emp where empno = 30;
-- This is just to verify in case if you want to see creation of outline has inserted plan records in the below tables in outln schema
Also creation of outline enters corresponding records in below tables in outln schema
sql> select count(*) from ......; -- one by one you can see below all there tables as this is optional step
ol$nodes
ol$hints
ol$
-- Just to verify interactive plan by autotrace utility of Oracle
SQL> set autotrace trace explain
SQL> select * from emp where empno = 30;
Execution Plan
----------------------------------------------------------
Plan hash value: 2941534610
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 0
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 0
|* 2 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 | | 1 (0)| 0
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=30)
-- Actual steps starts here -----------------
Run as a scott (sample schema for testing)
SQl> alter session set optimizer_mode=rule
/
/* SQL> ALTER SESSION SET query_rewrite_enabled=TRUE; */
sql> alter session set use_stored_outlines=true;
sql> ALTER SESSION SET use_stored_outlines=EMPOLN;
-- flush shared pool to clean the memory before executing this sql so that later we can pick correct sql_id from v$sql view
-- execute with sys user on another sql prompt
sql> alter system flush shared_pool;
-- Execute below on same sql command prompt as above
sql> select * from emp where empno = 30;
-- Verify if the plan is used by above query or not
SQL> SELECT name, category, used FROM user_outlines;
SQL> /
NAME CATEGORY USED
------------------------------ ------------------------------ ------
EMPOLN DEFAULT USED
--
/* ------ Login with sys and execute to see the plan
-- Find out the sql_id to
see the stored plan executed in the memory
-------- */
SQL> select sql_id from v$sql where sql_text like '%select * from emp where empno = 30%'
/
-- Note the sql_id to pass the same for below query
-- Execute with user sys
select t.*
from v$sql s
, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'TYPICAL ALLSTATS LAST')) t
where s.sql_id = '&sql_id'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6v6gcqm49qmvd, child number 0
-------------------------------------
select * from emp where empno = 30
Plan hash value: 2941534610
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CP
U)| E-Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | | | 2 (10
0)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 8 | 696 | 2 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 3 | | 1 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=30)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- outline "EMPOLN" used for this statement
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
--- Procedure ends here -----
--- Additional information to view as outln user
Login to outln and view the output of below query -
select ol_name, hint#, hint_text from ol$hints
where ol_name = 'EMPOLN'
order by ol_name, hint#
/
