SQLT工具包含一个脚本,名字是 coe_load_sql_profile.sql,下面以用户SCOTT的EMP表为例,说明如何使用该脚本固定sql profile.

1.
SQL> -- 对emp的列ename创建一个索引
SQL> create index i_emp_ename on scott.emp(ename);
SQL> --收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP')
SQL> -- 对emp的列ename创建一个索引
SQL> create index i_emp_ename on scott.emp(ename);
SQL> --收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP')
2.运行原始的SQL语句

SQL> select ename from scott.emp where ename='MILLER';

ENAME
----------
MILLER 

执行计划如下:

-------------------------------
SQL_ID  329d885bxvrcr         
-------------------------------
Plan hash value: 4001599462
-------------------------------------------------
| Id  | Operation        | Name        | E-Rows |
-------------------------------------------------
|   0 | SELECT STATEMENT |             |        |
|*  1 |  INDEX RANGE SCAN| I_EMP_ENAME |      1 |
-------------------------------------------------
SQL> select ename from scott.emp where ename='MILLER';
ENAME
----------
MILLER
 

执行计划如下:

-------------------------------
SQL_ID  329d885bxvrcr         
-------------------------------
Plan hash value: 4001599462
-------------------------------------------------
| Id  | Operation        | Name        | E-Rows |
-------------------------------------------------
|   0 | SELECT STATEMENT |             |        |
|*  1 |  INDEX RANGE SCAN| I_EMP_ENAME |      1 |
-------------------------------------------------

--这是我们需要更改的plan

  1. 运行带有hint的SQL

    SQL> select /+ FULL (EMP) / ename from scott.emp where ename='MILLER';

执行计划如下:

-------------------------------
SQL_ID  4f74t4ab7rd5y
-------------------------------
Plan hash value: 3956160932
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |
-------------------------------------------
SQL> select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER';
 
执行计划如下:
-------------------------------
SQL_ID  4f74t4ab7rd5y
-------------------------------
Plan hash value: 3956160932
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |
-------------------------------------------

--这是我们需要的plan

4: 可以通过下面的SQL获取这2个SQL的sql_id和plan_hash_value

SQL> select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like '%scott.emp%';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------------------------------------------------------
4f74t4ab7rd5y      3956160932 select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER'
329d885bxvrcr      4001599462 select ename from scott.emp where ename='MILLER'
SQL> select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like '%scott.emp%';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------------------------------------------------------
4f74t4ab7rd5y      3956160932 select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER'
329d885bxvrcr      4001599462 select ename from scott.emp where ename='MILLER'

--329d885bxvrcr - 这是原始语句的SQL ID
--4f74t4ab7rd5y - 这是使用hint的SQL ID
--3956160932 - 这是需要替换的plan hash value.
5.进行plan的替换
--这两个计划都需要在缓存或AWR中
--需要以具有DBA权限的用户身份连接,例如SYSTEM

SQL> conn system
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
输入 1 的值:  329d885bxvrcr
 
Parameter 2:
MODIFIED_SQL_ID (required)
 
输入 2 的值:  4f74t4ab7rd5y
 
Parameter 3:
PLAN_HASH_VALUE (required)
 
输入 3 的值:  3956160932
 
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: "3956160932"
 
ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:3956160932 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
 
****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************
 
Export: Release 11.2.0.4.0 - Production on 星期二 12月 5 15:36:24 2017
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
口令:
 
coe_load_sql_profile completed.
SQL>
SQL> conn system
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
输入 1 的值:  329d885bxvrcr
 
Parameter 2:
MODIFIED_SQL_ID (required)
 
输入 2 的值:  4f74t4ab7rd5y
 
Parameter 3:
PLAN_HASH_VALUE (required)
 
输入 3 的值:  3956160932
 
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: "3956160932"
 
ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:3956160932 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
 
****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************
 
Export: Release 11.2.0.4.0 - Production on 星期二 12月 5 15:36:24 2017
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
口令:
 
coe_load_sql_profile completed.
SQL>
6.运行原来的SQL语句
SQL>conn scott/tiger
SQL> select ename from scott.emp where ename='MILLER';
 
PLAN_TABLE_OUTPUT
-------------------------------
SQL_ID  329d885bxvrcr
-------------------------------
Plan hash value: 3956160932
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='MILLER')
Note
-----
   - SQL profile 329D885BXVRCR_3956160932 used for this statement
SQL>conn scott/tiger
SQL> set autot traceonly explain stat
SQL> select ename from scott.emp where ename='MILLER';
查看是否生效:

 
PLAN_TABLE_OUTPUT
-------------------------------
SQL_ID  329d885bxvrcr
-------------------------------
Plan hash value: 3956160932
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='MILLER')
Note
-----
   - SQL profile 329D885BXVRCR_3956160932 used for this statement

我们可以看到,原始的SQL现在和使用hint的sql具有相同的plan_hash_value和plan。

此外,我们看到这个SQL启用了一个SQL配置文件。

查看系统中有哪些绑定的执行计划:
–查看绑定的sql profile

select * from dba_sql_profiles;

–删除上面绑定执行计划

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘coe_1g1k2rcasn516_3203434082’);
end;
/

注意:绑定计划不能用sys用户。

标签: none

评论已关闭