使用sqlt手工创建sql_profile(r4笔记第37天)

jeanron100

发布于 2018-03-15 15:15:09

5540

发布于 2018-03-15 15:15:09

在生产环境中有一些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.

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.

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2015-02-05，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

oracle

本文分享自杨建荣的学习笔记微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

oracle

登录后参与评论

0 条评论

热度

使用sqlt手工创建sql_profile(r4笔记第37天)

使用sqlt手工创建sql_profile(r4笔记第37天)

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐