使用OUTLINE调整执行计划
1.测试SQL:
VAR PVNUMBER;
EXEC:PV:=1456;
selectcount(1) from t where object_id= :PV;
ExecutionPlan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF'IDX_OBJECT_ID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.创建要交换执行计划的OUTLN
创建两个outln:10g创建outln方法,可以参考:
HOWTO: Create a Stored Outline Based Upon an Existing Cursor [ID 445126.1]
一个用户需要使用OUTLN,需要有如下权限:
create session,create table,create procedure,create any outline,以及alter session
SQL>conn / as sysdba
Connected.
grantcreate any outline to scott;
grantalter session to scott;
grantexecute on dbms_outln to scott;
grantexecute on dbms_outln_edit to scott;
SQL>select name from dba_outlines;
no rowsselected
SCOTT用户创建outln:
create orreplace outline OL1
forcategory demo on
selectcount(1) from t where object_id= :PV;
create orreplace outline OL2
forcategory demo on
select/*+full(t)*/ count(1) from t where object_id= :PV;
Outlinecreated.
SQL>create private outline OLFROM from OL1;
createprivate outline OLFROM from OL1
*
ERROR atline 1:
ORA-18009:one or more outline system tables do not exist
# 调用如下过程,在scott用户下创建private outln使用的表
SQL>execute dbms_outln_edit.create_edit_tables;
PL/SQLprocedure successfully completed.
SQL>create private outline OLFROM from OL1;
Outlinecreated.
SQL>create private outline OLTO FROM OL2;
Outlinecreated.
3.调整outline使用的执行计划
SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM';
1 row updated.
SQL> delete from ol$ where ol_name='OLTO';
1 row deleted.
SQL> update ol$ set ol_name='OLTO' where ol_name='OLFROM';
1 row updated.
SQL> commit;
Commit complete.
SQL> execute dbms_outln_edit.refresh_private_outline('OLTO');
4.测试private OUTLN
SQL> altersession set use_private_outlines=true;
SQL>alter session set use_stored_outlines=demo;
Sessionaltered.
SQL>select count(1) from t where object_id= :PV;
ExecutionPlan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1120 Card=1 Bytes=13
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'(Cost=1120 Card=9495 Bytes=12
3435)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11636 consistent gets
9795 physical reads
0 redo size
519 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
5.发布private outline
create orreplace outline OL1 from private OLTO for category demo;
6.测试public outline
SQL>alter session set use_stored_outlines=demo;
SQL>select count(1) from t where object_id= :PV;
ExecutionPlan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1120 Card=1 Bytes=13
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'(Cost=1120 Card=9495 Bytes=12
3435)
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
11637 consistent gets
9595 physical reads
628 redo size
519 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>alter session set use_stored_outlines=;
Session altered.
SQL>select count(1) from t where object_id= :PV;
ExecutionPlan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF'IDX_OBJECT_ID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
7.系统级发布
altersystem set use_stored_outlines=demo;
参考metalink:
Howto Edit a Stored Outline to Use the Plan from Another Stored Outline [ID730062.1]