ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用sql_profile 替换执行计划

使用sql_profile 替换执行计划

原创 Linux操作系统 作者:wwjfeng 时间:2012-04-19 19:22:59 0 删除 编辑

使用sql_profile 替换执行计划

 

测试脚本准备:

create table EMP as select * from SCOTT.EMP@LEDMES;

create table DEPT as select * from SCOTT.DEPT@LEDMES;

 

当前执行计划:

SQL> select ename, dname from emp e, dept d where d.deptno=e.deptno and e.deptno=30;

Execution Plan

-----------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     6 |   132 |     2   (0)|

|   1 |  NESTED LOOPS                |         |     6 |   132 |     2   (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)|

|   3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |     6 |    54 |     1   (0)|

|   5 |    INDEX RANGE SCAN          | DEPTNO  |     6 |       |     0   (0)|

-----------------------------------------------------------------------------

 

SQL> select /*+ no_index( d,PK_DEPT) */ename, dname from emp e, dept d where d.deptno=e.deptno and e.deptno=30;

Execution Plan

---------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     6 |   132 |     4   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     6 |    54 |     1   (0)|

|   2 |   NESTED LOOPS              |        |     6 |   132 |     4   (0)|

|   3 |    TABLE ACCESS FULL        | DEPT   |     1 |    13 |     3   (0)|

|   4 |    INDEX RANGE SCAN         | DEPTNO |     6 |       |     0   (0)|

 

获取以上2sqlsql_id

SQL1: dbsqdjfus2yyh 

SQL2: 6bazq0fsjqq0a

 

建立profileSQL2的执行计划取代SQL1 的执行计划

SQL> @create_sql_profiles.sql

Enter value for sql_id1(used to generate sql_text): dbsqdjfus2yyh

Enter value for child_no1 (used to generate sql_text) (0):

Enter value for sql_id2(used to generate sql_hints): 6bazq0fsjqq0a

Enter value for child_no2(used to generate sql_hints) (0):

Enter value for profile_name (PROF_sqlid_planhash): PROF_1

Enter value for category (DEFAULT):

Enter value for force_matching (FALSE): TRUE

old   9: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id1'||'_'||plan_hash_value,'&&profile_name')

new   9: decode('PROF_1','X0X0X0X0','PROF_dbsqdjfus2yyh'||'_'||plan_hash_value,'PROF_1')

old  15: sql_id = '&&sql_id1'

new  15: sql_id = 'dbsqdjfus2yyh'

old  16: and child_number = &&child_no1;

new  16: and child_number = 0;

old  31: sql_id = '&&sql_id2'

new  31: sql_id = '6bazq0fsjqq0a'

old  32: and child_number = &&child_no2

new  32: and child_number = 0

old  40: category => '&&category',

new  40: category => 'DEFAULT',

old  42: force_match => &&force_matching

new  42: force_match => TRUE

old  53:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id1'||' Child: '||'&&child_no1'||' not found in v$sql.');

new  53:   dbms_output.put_line('ERROR: sql_id: '||'dbsqdjfus2yyh'||' Child: '||'0'||' not found in v$sql.');

 

检查SQL_PROFILE 是否被创建

select name,category,signature,sql_text,created,status,force_matching from dba_sql_profiles;

NAME

CATEGORY

SIGNATURE

SQL_TEXT

CREATED

STATUS

FORCE_MATCHING

PROF_1

DEFAULT

##########

select ename, dname from emp e, dept d where d.deptno=e.deptno and e.deptno=30

########

ENABLED

YES

 

 

SQL1当前执行计划:

select * from table(dbms_xplan.display_cursor('dbsqdjfus2yyh'));

PLAN_TABLE_OUTPUT

SQL_ID  dbsqdjfus2yyh, child number 0

-------------------------------------

select ename, dname from emp e, dept d where d.deptno=e.deptno and e.deptno=30

 

Plan hash value: 1071645890

 

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |       |       |     2 (100)|          |

|   1 |  NESTED LOOPS                |         |     6 |   132 |     2   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |

|   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |     6 |    54 |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | DEPTNO  |     6 |       |     0   (0)|          |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("D"."DEPTNO"=30)

   5 - access("E"."DEPTNO"=30)

 

SQL1实际执行计划

SQL> select ename, dname from emp e, dept d where d.deptno=e.deptno and e.deptno=30;

 

Execution Plan

----------------------------------------------------------

 

---------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     6 |   132 |     4   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     6 |    54 |     1   (0)|

|   2 |   NESTED LOOPS              |        |     6 |   132 |     4   (0)|

|   3 |    TABLE ACCESS FULL        | DEPT   |     1 |    13 |     3   (0)|

|   4 |    INDEX RANGE SCAN         | DEPTNO |     6 |       |     0   (0)|

 

可以看到SQL1使用了SQL2的执行计划

 

删除profile exec DBMS_SQLTUNE.drop_SQL_PROFILE ( name => 'PROF_1');

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-721716/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    138
  • 访问量
    59899