如何通过outline为SQL语句指定执行计划
-
创建测试表
以用户jyu连接，创建测试表
SQL> conn jyu/jyu;
Connected.
SQL> create table t (id number, name varchar2(100));
Table created.
SQL> insert into t select rownum,object_name from dba_objects;
47391 rows created.
SQL> commit;
Commit complete.
创建索引
SQL> create index t_idx1 on t(id);
Index created.
收集统计数据
SQL> exec dbms_stats.gather_table_stats('JYU','T');
PL/SQL procedure successfully completed.
-
执行计划
查看SQL语句执行计划
SQL> set autotrace traceonly
SQL> select * from t where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3292636276
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- outline "OLD_OUTLN" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
576 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL语句选择了使用索引的执行计划
使用Hint指定语句使用全表扫描的执行计划
SQL> explain plan for select /*+ full(t) */ * from t where id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 50 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 28 | 50 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
1 - filter("ID"=1)
13 rows selected.
使用outline固定执行计划
以sysdba连接数据库
SQL> conn /as sysdba
Connected.
分别为2个SQL语句创建outline
SQL> alter session set current_schema = jyu;
Session altered.
SQL> create or replace outline OLD_OUTLN for category TEMP_PLAN on select * from t where id=1;
Outline created.
SQL> create or replace outline NEW_OUTLN for category TEMP_PLAN on select /*+ full(t) */ * from t where id=1;
Outline created.
交换SQL语句的outline
SQL> create private outline OLFROM from OLD_OUTLN;
Outline created.
SQL> create private outline OLTO from NEW_OUTLN;
Outline created.
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');
PL/SQL procedure successfully completed.
SQL> create or replace outline OLD_OUTLN from private OLTO for category GOOD_PLAN;
Outline created.
SQL> drop outline NEW_OUTLN;
Outline dropped.
-
设置使用指定的outlines
有两种方式可在全局设置使用outline
方式一：使用alter system设置(数据库重启后失效)
SQL> conn / as sysdba
Connected.
SQL> alter system set use_stored_outlines=GOOD_PLAN;
System altered.
方式二：通过trigger设置(数据库重启仍然有效)
SQL> create or replace trigger enable_outlines_trig
--Ref : How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1)
after startup on database
begin
execute immediate('alter system set use_stored_outlines=GOOD_PLAN');
end;
/
-
检查SQL语句执行计划
SQL> conn jyu/jyu
Connected.
SQL> set autotrace traceonly
SQL> select * from t where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 50 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 28 | 50 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- outline "OLD_OUTLN" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
145 db block gets
269 consistent gets
0 physical reads
576 redo size
576 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
outline生效，SQL语句执行了全表扫描。#!/bin/bash if [ $# -lt 3 ]; then cat <<EOF Fix given SQL plan using given sql in file usage: fix_plan.sh <hash_value> <hinted_sql_file> <owner> examples: fix_plan.sh 716428968 good.sql ALEX EOF exit 1 fi HASH_VALUE=$1 SQL_FILE=$2 OL_OWNER=$3 echo "HASH_VALUE : $HASH_VALUE" echo "SQL_FILE : $SQL_FILE" echo "OL_OWNER : $OL_OWNER" echo "" #Create outline by hash_value function create_ol_from_hashvalue { HASH_VALUE=$1 OL_NAME=$2 OL_OWNER=$3 #generate create outline sql #I didn't use dbms_outln.create_outline, because it cannot create given name outline # and there's no hash value in V$SQL and DBA_OUTLINES to associate the two # according to "How To Match a SQL Statement to a Stored Outline (Doc ID 743312.1)" sqlplus -S "/ as sysdba" > /tmp/tmp_$OL_NAME.sql <<EOF set feedback off set serveroutput on size unlimited declare v_sqltext varchar2(32000); begin --get sql text select dbms_lob.substr(SQL_FULLTEXT, 30000, 1 ) into v_sqltext from v\$sql where hash_value = $HASH_VALUE and rownum=1; dbms_output.put_line('alter session set current_schema = $OL_OWNER;'); v_sqltext := 'create or replace outline $OL_NAME for category TEMP_PLAN on ' || chr(10) || v_sqltext || chr(10) ||';'; dbms_output.put_line(v_sqltext); dbms_output.put_line('exit;'); end; / EOF sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql } #Create outline from sql file function create_ol_from_sqlfile { SQL_FILE=$1 OL_NAME=$2 OL_OWNER=$3 #generate create outline sql cat > /tmp/tmp_$OL_NAME.sql <<EOF alter session set current_schema = $OL_OWNER; create or replace outline $OL_NAME for category TEMP_PLAN on `cat $SQL_FILE` exit; EOF sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql } #Exchange outlines, make GOOD_SQL plan to GOOD_PLAN category #Ref: How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1) function exchange_outline { OL1=$1 OL2=$2 OL_OWNER=$3 sqlplus -S "/ as sysdba" <<EOF set feedback off alter session set current_schema = $OL_OWNER; create private outline OLFROM from $OL1; create private outline OLTO from $OL2; update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM'; delete from ol$ where ol_name='OLTO'; update ol$ set ol_name='OLTO' where ol_name='OLFROM'; commit; execute dbms_outln_edit.refresh_private_outline('OLTO'); create or replace outline $OL1 from private OLTO for category GOOD_PLAN; drop outline $OL2; exit; EOF } #display outline function display_outline { OL_NAME=$1 OL_OWNER=$2 sqlplus -S "/ as sysdba" <<EOF set pagesize 1000 linesize 160 set long 32000 col hint format a55 col join_pos format a45 col owner format a12 col name format a18 col ts format a14 col h format 999 col category format a12 col sql_text format a80 col used format a6 select name, sql_text, category, used, to_char(TIMESTAMP, 'YY-mm-dd hh24:MI') from dba_outlines where name = '$OL_NAME' and OWNER = '$OL_OWNER'; select ol_name name, category, hint#, stage# stage, hint_text hint, join_pred join_pos from outln.ol\$hints where ol_name = '$OL_NAME' order by ol_name, hint#; exit; EOF } #main function echo "1. Create outline OL_$HASH_VALUE for SQL $HASH_VALUE" create_ol_from_hashvalue $HASH_VALUE OL_$HASH_VALUE $OL_OWNER echo "2. Create outline OL_TEMP for SQL in $SQL_FILE" create_ol_from_sqlfile $SQL_FILE OL_TEMP $OL_OWNER echo "3. Exchange outline OL_$HASH_VALUE with OL_TEMP, and drop OL_TEMP" exchange_outline OL_$HASH_VALUE OL_TEMP $OL_OWNER echo "4. Display final outline for SQL $HASH_VALUE : OL_$HASH_VALUE in category GOOD_PLAN " display_outline OL_$HASH_VALUE $OL_OWNER
