0201.O 使用coe_xfr_sql_profile.sql绑定执行计划

rundba 2024-11-24

127

oracle数据库中，原有SQL执行计划不理想，性能较差，本文构造新的执行计划，通过使用coe_xfr_sql_profile.sql绑定新的执行计划，提升性能，文中进行演示。

0. 环境

RHEL 7.7;
oracle database 19.3.0.0.0。

登录oracle后，创建示例表（scott.emp）：
```
SQL> @?/rdbms/admin/utlsampl.sql
```

1. 执行示例查询并查看执行计划

1) 执行sql语句

set lines 300 pages 1000
col sql_text for a50
select * from scott.emp where deptno=30;

2) 通过sql_text查找语句的sql_id

SQL> select sql_id,hash_value,sql_text from v$sql where sql_text like '%scott.emp%';




SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- --------------------------------------------------
fxkq7t08j0t7b  286287083 select sql_id,hash_value,sql_text from v$sql where
        sql_text like '%scott.emp%'




1mvxd868z75nf 2448660110 select * from scott.emp where deptno=30

3) 通过sql_id查看执行计划

SQL> select * from table(dbms_xplan.display_cursor('1mvxd868z75nf',null));




PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1mvxd868z75nf, child number 0
-------------------------------------
select * from scott.emp where deptno=30




Plan hash value: 3956160932




--------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |   |   |     3 (100)|    |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   522 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------




Predicate Information (identified by operation id):
---------------------------------------------------




   1 - filter("DEPTNO"=30)




Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




22 rows selected.

当前执行计划采用全表扫描，模拟生产较差语句。

2. 执行coe_xfr_sql_profile.sql生成outline

执行coe_xfr_sql_profile.sql：

SQL> @coe_xfr_sql_profile.sql


Parameter 1:
SQL_ID (required)


Enter value for 1: 1mvxd868z75nf    #输入sql_id




PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3956160932        .004          #当前提示hash_value值


Parameter 2:
PLAN_HASH_VALUE (required)


Enter value for 2: 3956160932       #输入上面提示的hash_value


Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "1mvxd868z75nf"
PLAN_HASH_VALUE: "3956160932"


SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;


Execute coe_xfr_sql_profile_1mvxd868z75nf_3956160932.sql
on TARGET system in order to create a custom SQL Profile
with plan 3956160932 linked to adjusted sql_text.




COE_XFR_SQL_PROFILE completed.

3. 创建一个较好的执行计划

1) 本次使用索引创建一个较好的执行计划

select /*+index(emp PK_EMP)*/ * from  scott.emp  where deptno=30;

2) 查看新执行计划

SQL>select * from table(dbms_xplan.display_cursor(null,null));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  987ba695ybyzp, child number 0
-------------------------------------
select /*+index(emp PK_EMP)*/ * from  scott.emp  where deptno=30


Plan hash value: 2898514743


----------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |       |       |    42 (100)|       |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |     6 |   522 |    42   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN        | PK_EMP |    14 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("DEPTNO"=30)


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




23 rows selected.

当前执行计划已经走索引。

3) 使用coe_xfr_sql_profile生成本次的outline

SQL>@coe_xfr_sql_profile.sql 987ba695ybyzp


Parameter 1:
SQL_ID (required)






PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2898514743        .002


Parameter 2:
PLAN_HASH_VALUE (required)


Enter value for 2: 2898514743


Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "987ba695ybyzp"
PLAN_HASH_VALUE: "2898514743"


SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3     RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;


Execute coe_xfr_sql_profile_987ba695ybyzp_2898514743.sql
on TARGET system in order to create a custom SQL Profile
with plan 2898514743 linked to adjusted sql_text.




COE_XFR_SQL_PROFILE completed.

4. 替换outline内容，并绑定新的执行计划

1) 替换outline内容

将coe_xfr_sql_profile_987ba695ybyzp_2898514743.sql中的outline替换coe_xfr_sql_profile_1mvxd868z75nf_3956160932.sql中的outline，替换参考：

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))]',
q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMP"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

2) 执行绑定

SQL> @coe_xfr_sql_profile_1mvxd868z75nf_3956160932.sql

3) 再次查看执行计划

set lines 300 pages 1000
select * from scott.emp where deptno=30;
 
SQL>select * from table(dbms_xplan.display_cursor(null,null));




PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1mvxd868z75nf, child number 0
-------------------------------------
select * from scott.emp where deptno=30




Plan hash value: 2898514743




----------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |       |       |    42 (100)|       |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |     6 |   522 |    42   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN        | PK_EMP |    14 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------




Predicate Information (identified by operation id):
---------------------------------------------------




   1 - filter("DEPTNO"=30)




Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL profile coe_1mvxd868z75nf_3956160932 used for this statement




24 rows selected.

此时，执行查询语句已经走新的执行计划。

文章转载自rundba，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

0201.O 使用coe_xfr_sql_profile.sql绑定执行计划

评论