开发者社区> jeanron100> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

使用sqlt手工创建sql_profile

简介: 在生产环境中有一些sql语句出现问题,大多是一些很紧急的问题,可能有些sql语句出现了执行计划的问题,通过hint能够做很大的改进,但是如果想让变更尽快生效,可以使用sql_profile来实现。
+关注继续查看
在生产环境中有一些sql语句出现问题,大多是一些很紧急的问题,可能有些sql语句出现了执行计划的问题,通过hint能够做很大的改进,但是如果想让变更尽快生效,可以使用sql_profile来实现。
说起sql_profile的创建有两种方式,一种是自动,通过sql tuning的功能能够得到一些建议,会自动生成sql_profile,只需要启用即可,如果需要手工创建则需要费一些功夫。
描述都是自己简单总结的。

step1:  get sqlt and transfer to target env

get sqlt.zip as attached, unzip and transferred to target environment, there is no need to config anything in advance for this scenario.

img_e25d4fb2f8de1caf41a735ec53088516.pngsqlt.zip

step2:  get sql_id which need to be tuned(which is already running, we can get it from v$sql), and get new sql_id which is tuned (need to consider bind variable scenario )

issue query is below(sql_id 74pzzzjddkyd4)

SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT 1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))

and we can get new sql_id using below.sql_id(gc2kzv3ytkhhu)


    get sql_id for tuned sql(with additional hints)

 

variable periodKey number;
variable cycleSeqNo number;
variable cycleSeqRun number;
exec :periodKey:=61;
exec :cycleSeqNo:=4106;
exec :cycleSeqRun:=0;

SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT 1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
          

and fetch it from v$sql
SQL_ID        SQL_FULLTEXT
------------- ----------------------------------------------------------------------------------------------------
gc2kzv3ytkhhu SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
                FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
               WHERE CYC_CUST.PERIOD_KEY = :periodKey
                 AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
                 AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
                 AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
                 AND EXISTS
               (SELECT /*+ unnest parallel(payer,4) full(payer)*/1
                        FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
                       WHERE PAYER.PERIOD_KEY = :periodKey
                         AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
                         AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
                         AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
                         AND PAYER.DB_STATUS = 'BL'
                         AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
                         AND PAYER.FORMAT_EXT_DATE IS NULL
                         AND DOC.PERIOD_KEY = :periodKey
                         AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
                         AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
                         AND PAYER.BA_NO = DOC.BA_NO
                         AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))          

step3: generate script to create sql profile and verify

go to sqlt path.
sqlt/utl> ls -lrt coe_xfr_sql_profile.sql
-rw-r--r-- 1 oraccbs1 dba 19045 Oct 31 01:00 coe_xfr_sql_profile.sql

use dba account and run script coe_xfr_sql_profile.sql, input sql_id and hash_value.

sqlt/utl> sqlplus n1/n1
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 4 16:12:27 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: gc2kzv3ytkhhu

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3780786163
Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3780786163

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "gc2kzv3ytkhhu"
PLAN_HASH_VALUE: "3780786163"

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_gc2kzv3ytkhhu_3780786163.sql
on TARGET system in order to create a custom SQL Profile
with plan 3780786163 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.
there will be one file for sql_id generated like coe_xfr_sql_profile_gc2kzv3ytkhhu_3780786163.sql

for sql_id 74pzzzjddkyd4, use the same steps as above to generate related file, file name will be  coe_xfr_sql_profile_74pzzzjddkyd4_1323438769.sql

 

as of now,  coe_xfr_sql_profile_74pzzzjddkyd4_1323438769.sql is script for sql_id 74pzzzjddkyd4 which has issue and running.
coe_xfr_sql_profile_gc2kzv3ytkhhu_3780786163.sql is script for sql_id gc2kzv3ytkhhu which is tuned

 

step4: replace hints and crete sql profile.

we will need to change below from coe_xfr_sql_profile_74pzzzjddkyd4_1323438769.sql
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]',
q'[DB_VERSION('11.2.0.2')]',
q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('_optimizer_cost_based_transformation' 'off')]',
q'[OPT_PARAM('_optimizer_skip_scan_enabled' 'false')]',
q'[OPT_PARAM('_optimizer_use_feedback' 'false')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 10)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$2")]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "CYC_CUST"@"SEL$1")]',
q'[INDEX(@"SEL$1" "CUST"@"SEL$1" ("BL1_CUSTOMER"."CUSTOMER_ID"))]',
q'[LEADING(@"SEL$1" "CYC_CUST"@"SEL$1" "CUST"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "CUST"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "CUST"@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$2" "PAYER"@"SEL$2" ("BL1_CYC_PAYER_POP"."CUSTOMER_NO"))]',
q'[INDEX(@"SEL$2" "DOC"@"SEL$2" ("BL1_DOCUMENT"."BA_NO"))]',
q'[LEADING(@"SEL$2" "PAYER"@"SEL$2" "DOC"@"SEL$2")]',
q'[USE_NL(@"SEL$2" "DOC"@"SEL$2")]',
q'[NLJ_BATCHING(@"SEL$2" "DOC"@"SEL$2")]',
q'[END_OUTLINE_DATA]');

 

to below which is from coe_xfr_sql_profile_gc2kzv3ytkhhu_3780786163.sql
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]',
q'[DB_VERSION('11.2.0.2')]',
q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('_optimizer_cost_based_transformation' 'off')]',
q'[OPT_PARAM('_optimizer_skip_scan_enabled' 'false')]',
q'[OPT_PARAM('_optimizer_use_feedback' 'false')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 10)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$683B0107")]',
q'[OUTLINE_LEAF(@"SEL$C772B8D1")]',
q'[UNNEST(@"SEL$2")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$7511BFD2")]',
q'[OUTLINE(@"SEL$1")]',
q'[NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")]',
q'[INDEX_RS_ASC(@"SEL$C772B8D1" "CYC_CUST"@"SEL$1" ("BL1_CYCLE_CUSTOMERS"."CUSTOMER_NO" "BL1_CYCLE_CUSTOMERS"."CYCLE_SEQ_NO" "BL1_CYCLE_CUSTOMERS"."PERIOD_KEY" "BL1_CYCLE_CUSTOMERS"."CUSTOMER_KEY"))]',
q'[INDEX(@"SEL$C772B8D1" "CUST"@"SEL$1" ("BL1_CUSTOMER"."CUSTOMER_ID"))]',
q'[LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "CYC_CUST"@"SEL$1" "CUST"@"SEL$1")]',
q'[USE_NL(@"SEL$C772B8D1" "CYC_CUST"@"SEL$1")]',
q'[USE_NL(@"SEL$C772B8D1" "CUST"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$C772B8D1" "CUST"@"SEL$1")]',
q'[PQ_DISTRIBUTE(@"SEL$C772B8D1" "CYC_CUST"@"SEL$1" NONE BROADCAST)]',
q'[PQ_DISTRIBUTE(@"SEL$C772B8D1" "CUST"@"SEL$1" NONE BROADCAST)]',
q'[FULL(@"SEL$683B0107" "PAYER"@"SEL$2")]',
q'[INDEX(@"SEL$683B0107" "DOC"@"SEL$2" ("BL1_DOCUMENT"."BA_NO"))]',
q'[LEADING(@"SEL$683B0107" "PAYER"@"SEL$2" "DOC"@"SEL$2")]',
q'[USE_NL(@"SEL$683B0107" "DOC"@"SEL$2")]',
q'[NLJ_BATCHING(@"SEL$683B0107" "DOC"@"SEL$2")]',
q'[PQ_DISTRIBUTE(@"SEL$683B0107" "DOC"@"SEL$2" NONE BROADCAST)]',
q'[GBY_PUSHDOWN(@"SEL$683B0107")]',
q'[USE_HASH_AGGREGATION(@"SEL$683B0107")]',
q'[END_OUTLINE_DATA]');


finally run script  coe_xfr_sql_profile_74pzzzjddkyd4_1323438769.sql
it will generate sql profile for sql_id 74pzzzjddkyd4 with tuned hints.

 

step5: sanity check if execution plan has changed as expected.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【MySQL从入门到精通】【高级篇】(二十二)慢查询日志分析,SHOW PROFILE查看SQL执行成本
上一篇文章我们介绍数据库的优化步骤【MySQL从入门到精通】【高级篇】(二十一)数据库优化步骤_查看系统性能参数,其中,说到了通过开启慢查询日志来分析慢查询的SQL。这篇文章就是具体来介绍如何开启慢查询日志以及如何分析慢查询日志。
0 0
Mysql中 慢查询日志和show profile进行sql分析
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
0 0
几个必须掌握的SQL优化技巧(五):Show Profile分析SQL性能
在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。
0 0
MySQL数据库性能优化由浅入深(表设计、慢查询、SQL索引优化、Explain分析、Show Profile分析、配置优化)
通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解
0 0
[20180302]sql profile能减少分析时间吗?
[20180302]sql profile能减少分析时间吗? --//链接http://www.itpub.net/thread-2097379-1-1.html的讨论,测试看看sql profile能减少分析时间吗? --//要找到这样的语句分析时间"很长",...
799 0
[20180301]sql profile 非绑定变量.txt
[20180301]sql profile 非绑定变量.txt http://www.itpub.net/thread-2097379-1-1.html 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING            ...
762 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载