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

原创

jeanron100 2021-07-22 14:38:53 ©著作权

文章标签 sqlt 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者jeanron100的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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]');