 |
|
Oracle Tips by Burleson |
Using DML
and Packages to Edit Outlines
This is an example of editing the outlines manually. The steps are
as follows:
-
Create a new public stored outline
-
Create a private outline
-
Edit the private outline, test the result
-
Publicize the edits by creating a public stored outline from
your private outline.
A Detailed Example
Let’s look at a more detailed example.
-
Create a new public stored outline
a. First, ensure that the required privileges have been granted.
SQL>
connect system as sysdba
Enter password:
Connected.
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;
Grant succeeded.
b. Connect and set your
environment. In this example, you will set hash_join_enabled=false
and you will still be able to enforce a hash join by editing
your stored outline.
SQL> connect scott
Enter password:
Connected.
SQL> set pages 1000
SQL> alter session set optimizer_goal=all_rows;
Session altered.
SQL> alter session set hash_join_enabled=false;
Session altered.
c. Create standard demo tables in
Scott's schema.
SQL> @$ORACLE_HOME/sqlplus/demo/demobld.sql
Building demonstration tables. Please wait.
Demonstration table build is complete.
d.
Analyze the tables for Oracle cost-based optimizer.
SQL>
analyze table emp compute statistics;
Table analyzed.
SQL> analyze table dept compute statistics;
Table analyzed.
e.
Check the current execution plan for the query involved.
SQL> set autotrace on explain
SQL> select e.ename from emp e, dept d where e.deptno=d.deptno;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=126
Card=41 Bytes=
1353)
1 0 NESTED LOOPS (Cost=126 Card=41 Bytes=1353)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=41
Bytes=820)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=41
Bytes=533)
SQL> set autotrace off
f. Create a public outline
SQL>
create or replace outline outln1
2 on select e.ename from emp e, dept d where e.deptno=d.deptno;
outline created.
2. Create a private outline
a. Create the Edit Tables
in your schema, to store information about your private
outlines. The tables created in this step must be there for the
next step, otherwise you will get error:
ORA-18009 "one or more outline system tables do not exist".
SQL> execute dbms_outln_edit.create_edit_tables;
PL/SQL procedure successfully completed.
b. Create your private outline by cloning it from the Public
outline.
SQL>
create private outline priv_outln1 from outln1;
outline created.
3. Edit the private outline, test the result
a. After you create the
private outline, new entries appear in the table ol$hints. This
table is created in your schema by running the procedure
create_edit_tables. Let’s examine the contents relevant to the
newly created private outline.
SQL> column hint# format 999999
SQL> column hint_text format a28
SQL> column user_table_name format a16
SQL> select hint#, hint_text, user_table_name from ol$hints
2 where ol_name = 'PRIV_OUTLN1';
HINT# HINT_TEXT USER_TABLE_NAME
------- ---------------------------- ----------------
1 NOREWRITE
2 NOREWRITE
3 NO_EXPAND
4 PQ_DISTRIBUTE(D NONE NONE) SCOTT.DEPT
5 USE_NL(D) SCOTT.DEPT
6 ORDERED
7 NO_FACT(D) SCOTT.DEPT
8 NO_FACT(E) SCOTT.EMP
9 FULL(E) SCOTT.EMP
10 FULL(D) SCOTT.DEPT
10 rows selected.
b. Edit the outline by
performing DML against the appropriate hint records in the
ol$hints table. In this example, you will change the hint
USE_NL to USE_HASH.
SQL>
update ol$hints set hint_text='USE_HASH(D)'
2 where hint# = 5;
1 row updated.
SQL> commit;
Commit
complete.
Query the table ol$hints
to see the changes.
SQL>
select hint#, hint_text, user_table_name from ol$hints
2 where ol_name = 'PRIV_OUTLN1';
HINT# HINT_TEXT USER_TABLE_NAME
------- ---------------------------- ----------------
1 NOREWRITE
2 NOREWRITE
3 NO_EXPAND
4 PQ_DISTRIBUTE(D NONE NONE) SCOTT.DEPT
5 USE_HASH(D) SCOTT.DEPT
6 ORDERED
7 NO_FACT(D) SCOTT.DEPT
8 NO_FACT(E) SCOTT.EMP
9 FULL(E) SCOTT.EMP
10 FULL(D) SCOTT.DEPT
10 rows selected.
c. After manually editing
the outline, re-synchronize the stored outline definition using
the following procedure:
SQL>
execute dbms_outln_edit.refresh_private_outline('PRIV_OUTLN1');
PL/SQL procedure successfully completed.
Note that the private outline name must match in case to
ol$hints.ol_name
Alternatively, you can
use:
SQL> alter system flush shared_pool or
or
SQL>
create private outline priv_outln1 from private priv_outln1;
d. Set the parameter
use_private_outlines, otherwise the query will still use nested
loops instead of not hash join.
SQL> alter
session set use_private_outlines=true;
Session altered.
e. Test the current
execution plan for the query, to confirm that Oracle optimizer
is now using hash join method for this query.
SQL> set
autotrace on explain
SQL> select e.ename from emp e, dept d where e.deptno=d.deptno;
ENAME
----------
MILLER
KING
CLARK
FORD
ADAMS
SCOTT
JONES
SMITH
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=41
Bytes=1
353)
1 0 HASH JOIN (Cost=10 Card=41 Bytes=1353)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=41
Bytes=820)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=41
Bytes=533)
4. Publicize the edits by
creating a public stored outline from your private outline.
If you want to preserve your edits for public use, then publicize
the edits with the following statement:
SQL> create or
replace outline outln2 from private priv_outln1;
Outline created.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
