Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql

最新推荐文章于 2024-03-20 09:00:00 发布
最新推荐文章于 2024-03-20 09:00:00 发布
阅读量532 收藏
点赞数
分类专栏： Oracle

(shipped with SQLT) (文档 ID 1400903.1)

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

GOAL

This document explains the steps necessary to use coe_load_sql_baseline.sql / coe_load_sql_profile.sql  to apply a custom SQL Baseline (in 11g and higher)  or a custom SQL Profile (in 10g and higher).

One application of this is to prompt the optimizer to use a plan that could only be achieved through adding hints. For example: When you cannot modify the original SQL for whatever reason and you would like to "capture" the plan from a modified version of your SQL (the one with CBO Hints) and associate this "good" plan to the original un-modified SQL.

NOTE: Care should be taken to ensure that the 2 queries are syntactically identical in order to obtain the desired result. If for example the aliases used or predicates in the query are different,   the optimizer may not be able to follow the directives. In this case, the profile may be ignored by the optimizer or a different non-optimal plan obtained.

coe_load_sql_baseline.sql / coe_load_sql_profile.sql are scripts provided with the SQLT tool in the "utl" folder.

NOTE: Licensing considerations:

Both the coe_load_sql_baseline.sql and  coe_load_sql_profile.sql scripts may require licenses depending on how they are used.
The coe_load_sql_profile.sql script calls DBMS_SQLTUNE (which is a Licensed Command-Line API) requiring the Tuning Pack license (for which Oracle Diagnostics Pack is a prerequisite).
If the coe_load_sql_baseline.sql script finds the required plans in memory then it does not require any license.  However, if it retrieves plans from the Automatic Workload Repository (AWR), a Diagnostic Pack license would be required.

SQLT is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files that are commonly used to diagnose SQL statements performing poorly.You can download SQLT from:

Document: 215187.1 SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly

 

SOLUTION

There are many possible applications for the coe_load_sql_baseline.sql / coe_load_sql_profile.sql scripts. What follows is an example usage to prompt the optimizer to use a plan that could only be achieved through adding hints.

Instructions

  1. Make sure that both the original and modified statement are in memory. This can be achieved by executing the queries. Find the sql_id and  plan_hash_value for both the original sql (without hints) and the modified sql (with hints)
     
  2. Decide whether you want to guarantee plan stability for the statement (baseline) or not (profile). To help you decide refer to the following:

    Document 1524658.1 FAQ: SQL Plan Management (SPM) Frequently Asked Questions

  3. Change directory to sqlt/utl
    Following 2 scripts in the sqlt/utl directory can be used to achieve the goal explained in this document:
    • coe_load_sql_baseline.sql (11g and higher)
      Loads a plan from the modified sql with hints into a custom sql plan baseline of original SQL (without hints)

      For more information on baselines and plan stability see:

      Document 1359841.1 Plan Stability Features (Including SPM) Start Point 

    • coe_load_sql_profile.sql (10g and higher)
      Loads the plan from the modified sql with hints sql into a custom sql profile of the original SQL (without hints)

      For more information on sql profiles see:

      Document 271196.1 Automatic SQL Tuning - SQL Profiles.

  4. Run the script coe_load_sql_baseline.sql or coe_load_sql_profile.sql  and provide the sql_id of the original statement and the sql_id and plan_hash_value of the modified sql statement.

Example

Setup:

SQL> -- For our example we will create an index on emp as follows:
SQL> create index i_emp_ename on scott.emp(ename);

Index created.

SQL> -- Gather statistics
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP')

PL/SQL procedure successfully completed. 

 

Step 1: Run Original SQL

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

Plan hash value: 3045807146

---------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |     6 |     1   (0)|
|*  1 |  INDEX RANGE SCAN| I_EMP_ENAME |     1 |     6 |     1   (0)|
---------------------------------------------------------------------

This is the query for which we want to change the plan

Step 2: Run Hinted SQL

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

Plan hash value: 2872589290

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |     3   (0)|
---------------------------------------------------------------

This is the hinted query with the desired plan.

Step 3: Find sql_id and plan_hash_value for the 2 SQLs

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
--------------------------------------------------------------------------------
0vdqhcj6gaqnt 3924418374
select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like '%scott.
emp%'

4f74t4ab7rd5y 2872589290
select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER'

329d885bxvrcr 3045807146
select ename from scott.emp where ename='MILLER'


Original sql id: 329d885bxvrcr - this is the SQL ID of the original statement. We want to change the plan for this statement to that of the hinted SQL.
Modified sql id: 4f74t4ab7rd5y - this is the SQL ID of the hinted statement. This is the source of the plan for the statement we want to change.
Modified Plan Hash Value: 2872589290 - this is the PHV of the target plan (the plan from the hinted query)



 

Step 4

At this point there are 2 options to stabilize the plan.

A: If you want to force the access path, use an outline (coe_load_sql_baseline.sql)
B: If you want to push the plan in a particular direction but want some flexibility then use a profile (coe_load_sql_profile.sql)
 

A: Using coe_load_sql_baseline.sql

  • The original SQL must be in cache or AWR
  • The modified SQL must be in cache
  • Connect as user with DBA privilege, for example SYSTEM

NOTES: 

Do not connect as SYS as the staging table cannot be created in SYS schema and you will receive an error: ORA-19381: cannot create staging table in SYS schema

Licensing: If the coe_load_sql_baseline.sql script finds the required plans in memory then it does not require any license.  However, if it retrieves plans from the Automatic Workload Repository (AWR), a Diagnostic Pack license would be required.

SQL> @coe_load_sql_baseline.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 329d885bxvrcr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 4f74t4ab7rd5y


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          2872589290                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3:  2872589290

Values passed to coe_load_sql_baseline:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: " 2872589290"
.
.
.

****************************************************************************
* Enter SCOTT password to export staging table STGTAB_BASELINE_329d885bxvrcr
****************************************************************************

Export: Release 11.2.0.3.0 - Production on Sun Mar 11 15:08:56 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

.
.
.
deleting: coe_load_sql_baseline.log


coe_load_sql_baseline completed.

Run original query

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

Plan hash value: 2872589290

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

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

   1 - filter("ENAME"='MILLER')

Note
-----
   - SQL plan baseline "329D885BXVRCR_4F74T4AB7RD5Y" used for this statement

We see that the original sql now has the same plan_hash_value as the hinted sql and the same plan.
Furthermore we see the a SQL plan baseline is enabled for this statement.

B: Using coe_load_sql_profile.sql

  • Both plans need to be in cache or in AWR
  • Connect as user with DBA privilege, for example SYSTEM

NOTES: 

Do not connect as SYS as the staging table cannot be created in SYS schema and you will receive an error: ORA-19381: cannot create staging table in SYS schema

Licensing: The coe_load_sql_profile.sql script calls DBMS_SQLTUNE (which is a Licensed Command-Line API) requiring the Tuning Pack license (for which Oracle Diagnostics Pack is a prerequisite). 

connect system/pass

SQL> @coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 329d885bxvrcr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 4f74t4ab7rd5y


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          2872589290                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 2872589290

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: "2872589290"

.
.
.

ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:2872589290 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;

****************************************************************************
* Enter SCOTT password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************

Export: Release 11.2.0.3.0 - Production on Sun Mar 11 14:45:47 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:
.
.
.

coe_load_sql_profile completed.

Run original query

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

Plan hash value: 2872589290

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| 
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |     3   (0)|
---------------------------------------------------------------

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

   1 - filter("ENAME"='MILLER')

Note
-----
   - SQL profile "329D885BXVRCR_2872589290" used for this statement


We see that the original SQL now has the same plan_hash_value as the hinted sql and the same plan. Furthermore we see the a SQL profile is enabled for this statement

jx_jy
关注
专栏目录
coe_load_sql_profile.sql
08-20
oracle sqlt工具脚本，用来使用sql_profile绑定内存中已有的执行计划
coe_load_sql_baseline.sql脚本使用方法（SPM使用hint方式加载基线）
mhmds的专栏
02-28 1051
本文主要介绍通过hint生成执行计划，然后添加到SQL plan baseline。使用hint方式添加执行计划具体步骤可以参考MOS文章（文档 ID 787692.1），此方法需要你保证SPM中已经存在此SQL语句的基线，才能添加成功，添加之后可以再删除原来的计划，只使用我们用hint生成的计划。       这里我们使用oracle提供的脚本coe_load_sql_baseline.s
Oracle 如何不改变SQL为其绑定构造的执行计划
Hehuyi_In的博客
05-16 1897
前面几篇文章介绍的方法只能在sql本身有过好执行计划的时候将其固定，如果sql从未有过好的执行计划，需要添加hint但又无法修改sql代码时，之前的方法就不大合适，需要使用新的方法。 一、 问题背景 遇到过的一个案例就十分符合这种情况。11.2的数据库A中发现一个慢sql，执行超过4万秒。查询program得知是开发用的datagrip工具自动执行的语句，无法关闭也无法修改该sql，实际对业务功能没一点作用，对DB压力还特别大。 select O.owner as synonym_schema.
执行计划绑定方法
Du.的博客
01-27 4114
固化执行计划的方法包括profile、baseline、ouline的方式，常用的方式baseline、outline，可以使用sqlt工具中包含的sql脚本来绑定（coe_load_sql_profile.sql、coe_xfr_sql_profile.sql、coe_load_sql_baseline.sql） 1、情景1--不跨库绑定profile 同实例里不同/相同的sql_id绑定同一个执行计划（plan_hash_value），使用coe_load_sql_profile.sql脚本实现
使用coe_load_sql_profile.sql固定执行计划
文档搬运工
12-01 1188
参考文档： Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1) -- 在emp上创建索引 BB@test>create index i_emp_ename on emp(ename); Index created. BB@test> -- 收集统计信息 e.
MidtermExam_吴先_13000128171
08-08
Question 1.6 emphasizes the role of transport layer ports in identifying and directing data to the appropriate application. Each port number corresponds to a specific service or protocol, ensuring ...
2021_2022版高中英语Module2AJobWorthDoingIntroduction&ReadingandVocabulary素养评价检测含解析外研版必修5202103091212
08-07
3. **句子结构与用法**：完成句子部分展示了多种常见的英语句型，如with复合结构（I like to sleep with the windows open），结果状语从句（Kathy is so lovely that...），时间状语从句（Jack was working...when....
twxproxy_pro v211 source code
05-26
Unlike most other helpers, the idea of this program is to simply provide a sidelined assistance in the game rather than directing its play. This project also includes several subprojects that look to...
Deliver.Audacious.Web.Apps.with.Ember2
10-19
Directing Traffic with Routes Chapter 3. Laying Out a User Interface Chapter 4. Building In Reuse with Components Chapter 5. Modeling Your Data Chapter 6. Reading Nonstandard APIs Chapter 7. Reusing ...
hibernate-shards.jar
02-22
When a program is linked with a library, whether statically or using a shared library, the combination of the two is legally speaking a combined work, a derivative of the original library. The ...
coe_xfr_sql_profile.sql
08-03
coe_xfr_sql_profile.sql
oracle优化-绑定执行计划coe脚本
12-14
oracle优化-绑定执行计划coe脚本 。。。。
使用coe_load_sql_profile脚本通过sql_profile绑定执行计划
DBA小站
08-26 853
##使用情景： 在部分spm不能使用的情况下，如sql中含有远程表，spm绑定不生效。 使用sql_profile绑定内存中已有的正确执行计划。 ##使用方式： 1、上传脚本 coe_load_sql_profile.sql https://download.csdn.net/download/royzhang7/21387886 2、登录数据库，要用有DBA权限的非sys用户（system或其他DBA权限用户） 3、执行 @coe_load_sql_profile.sql 4、输入参数(这里如果
SQL优化篇--绑定执行计划（手工制造正确执行计划）
fengsongtao123的博客
09-06 2210
生产数据库中经常出现SQL语句走错执行计划的情况，如果该sqlid还有其他高效的执行计划，可以通过coe_xfr_sql_profile.sql脚本进行绑定，但是如果sqlid没有高效的执行计划，就需要通过自己手工生成一个执行计划（通过加hint，或者其他方法），然后将手工生成的 执行计划绑定到生产中运行的sqlid上，下面就演示下具体方法： –下面SQL正常走ob.object_id列上的索引I...
sql profile和sql plan baseline的比较
Expec-乐的博客
06-24 653
原文链接：http://mp.weixin.qq.com/s/nOcwvT4KbiwhcEaGDsKvTw 本文已获原作者授权转载。文章出自微信公众帐号：老虎刘谈SQL优化 作者介绍：老虎刘,原oracle 研发部门 Real-World Performance TEAM 成员，现在售后部门SSC专职做数据库性能优化，主要为银行、通信、证券、制造等大型企业提供服务。 比较 两个功能（以下简称pro...
coe_xfr_sql_profile.sql和coe_load_sql_profile.sql
coco3600的博客
09-12 379
coe_xfr_sql_profile.sql和coe_load_sql_profile.sql 【SPM】Oracle如何固定执行计划： http://blog.itpub.net/...
Oracle利用coe_load_sql_profile脚本绑定执行计划
cuibaye0692的博客
01-22 716
coe_load_sql_profile_v2.sql脚本利用的是profile原理，只是做了半自动的形式来使用，下面是测试过程。 coe_load_sql_profile_v2.txt ...
Oracle中使用coe_load_sql_profile脚本固定执行计划
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-20 983
通过固定执行计划的方法，即使在Oracle优化器可能因为统计信息变化而选择不同执行计划的情况下，SQL Profile也能强制数据库按照已知高效的执行计划来执行SQL语句。这有助于保持应用程序的性能稳定性。
rabbitMQ multiple
05-16
3. Request/Response: In this pattern, a client sends a request to a server, and the server responds with a message. This is useful for building RPC (Remote Procedure Call) systems. 4. Work Queues: In...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值