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')
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 | -------------------------------------------------
--这是我们需要更改的plan
3. 运行带有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 | -------------------------------------------
--这是我们需要的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'
--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>
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现在和使用hint的sql具有相同的plan_hash_value和plan。
此外，我们看到这个SQL启用了一个SQL配置文件。
相关参考:
Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)