首页 技术 正文

如何通过outline为SQL语句指定执行计划

技术 2022年11月10日
0 收藏 745 点赞 3,812 浏览 7741 个字

  • 创建测试表

以用户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 statementStatistics
----------------------------------------------------------
          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 statementStatistics
----------------------------------------------------------
         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/bashif [ $# -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
fiHASH_VALUE=$1
SQL_FILE=$2
OL_OWNER=$3echo "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;
/
EOFsqlplus -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;
EOFsqlplus -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=$3sqlplus -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=$2sqlplus -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 a6select 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_OWNERecho "2. Create outline OL_TEMP for SQL in $SQL_FILE"
create_ol_from_sqlfile $SQL_FILE OL_TEMP $OL_OWNERecho "3. Exchange outline OL_$HASH_VALUE with OL_TEMP, and drop OL_TEMP"
exchange_outline OL_$HASH_VALUE OL_TEMP $OL_OWNERecho "4. Display final outline for SQL $HASH_VALUE : OL_$HASH_VALUE in category GOOD_PLAN "
display_outline OL_$HASH_VALUE $OL_OWNER
点赞 745

贡献者

上一篇： Ubuntu14.04LTS上安装Pip
下一篇： Java集合框架之Map接口浅析
相关推荐
python开发_常用的python模块及安装方法
python开发_常用的python模块及安装方法
adodb：我们领导推荐的数据库连接组件bsddb3：BerkeleyDB的连接组件Cheetah-1.0：我比较喜欢这个版本的cheeta…
技术
日期：2022-11-24 点赞：878 阅读：9,000
Educational Codeforces Round 11 C. Hard Process 二分
Educational Codeforces Round 11 C. Hard Process 二分
C. Hard Process题目连接：http://www.codeforces.com/contest/660/problem/CDes…
技术
日期：2022-11-24 点赞：807 阅读：5,512
下载Ubuntn 17.04 内核源代码
下载Ubuntn 17.04 内核源代码
zengkefu@server1:/usr/src$ uname -aLinux server1 4.10.0-19-generic #21…
技术
日期：2022-11-24 点赞：569 阅读：6,358
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号Name: www.greendown.cn Code: &nb…
技术
日期：2022-11-24 点赞：733 阅读：6,141
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机和自定义相机实例本博文主要是介绍了android上使用相机进行拍照并显示的两种方式，并且由于涉及到要把拍到的照片显…
技术
日期：2022-11-24 点赞：512 阅读：7,771
Struts的使用
Struts的使用
一、Struts2的获取　　Struts的官方网站为：http://struts.apache.org/　　下载完Struts2的jar包,…
技术
日期：2022-11-24 点赞：671 阅读：4,849
Lomu

贡献者

这家伙很懒，只想把你留下。

文章
77
收藏
0
人气
249,955
粉丝
进主页
文章展示
  • [ 技术 ] python开发_常用的python模块及安装方法
  • [ 技术 ] Educational Codeforces Round 11 C. Hard Process 二分
  • [ 技术 ] 下载Ubuntn 17.04 内核源代码
  • [ 技术 ] 可用Active Desktop Calendar V7.86 注册码序列号
  • [ 技术 ] Android调用系统相机、自定义相机、处理大图片
  • [ 技术 ] Struts的使用
    • 个人收藏笔记记录
    我的笔记
    首页 首页 菜单 首页 首页