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

sqlt.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.