使用sqlt手工创建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.

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'

(PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)

PAYER.FORMAT_EXT_DATE IS NULL

           AND DOC.PERIOD_KEY =

           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo

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

                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 =

                 AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR

                 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 =

                         AND PAYER.DB_STATUS =

'BL'

                         AND (PAYER.UNDO_REQ_TYPE = 'N' OR

PAYER.UNDO_REQ_TYPE IS NULL)

                         AND

                         AND DOC.PERIOD_KEY =

                         AND DOC.CYCLE_SEQ_NO =

                         AND DOC.CYCLE_SEQ_RUN =

                         AND PAYER.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;

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;

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

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'

q'[OPT_PARAM('_optimizer_use_feedback'

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")]',

("BL1_CUSTOMER"."CUSTOMER_ID"))]',

("BL1_CYC_PAYER_POP"."CUSTOMER_NO"))]',

("BL1_DOCUMENT"."BA_NO"))]',

q'[END_OUTLINE_DATA]');

to below which is from coe_xfr_sql_profile_gc2kzv3ytkhhu_3780786163.sql

h

:=

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")]',

"BL1_CYCLE_CUSTOMERS"."CYCLE_SEQ_NO" "BL1_CYCLE_CUSTOMERS"."PERIOD_KEY"

"BL1_CYCLE_CUSTOMERS"."CUSTOMER_KEY"))]',

BROADCAST)]',

q'[GBY_PUSHDOWN(@"SEL$683B0107")]',

q'[USE_HASH_AGGREGATION(@"SEL$683B0107")]',

finally run script 

it will generate

sql profile for sql_id 74pzzzjddkyd4 with tuned hints.

step5: sanity check if execution plan has changed as

expected.