關於sql_profile中的繫結變數

使用sql_profile來調優一些緊急的效能sql可以起到立竿見影的效果，如果sql語句本身結構就很清晰，簡單，略作修改就能得到調優後的sql語句。
但是如果語句中含有繫結變數，如果要得到調優後的sql_id就有些困難了。
比如我們存在下面的sql語句。
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'))

需要新增一個hint 得到一個新的sql_id.
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'))

如果使用explain plan可以得到包含繫結變數的執行計劃，但是卻無法得到對應的sql_id
比如sql_id 為74pzzzjddkyd4 
74pzzzjddkyd4 SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT FROM   BL1_CUSTOMER CUST        ,BL1_CYCLE_CUSTOMERS CY             
                        ...
              Q_TYPE IS NULL)   AND  EXISTS (SELECT 1                FROM   BL1_CYC_PAYER_POP PAYER
                     ...

使用explain plan for之後可以得到一個執行計劃情況，但是sql_id卻是不同的。
73d1q5xd835kt explain plan for
              SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
                ...
                 AND EXISTS
               (SELECT 1
                ...

對於這種情況，可以使用variable 來實現。
存在幾個變數，然後手工賦值，執行一下，也可以中途停止，就能夠從v$sql裡面抓到對應的sql_id
variable periodKey number;
variable cycleSeqNo number;
variable cycleSeqRun number;
exec :periodKey:=61;
exec :cycleSeqNo:=4106;
exec :cycleSeqRun:=0;
然後執行修改後的語句，這樣我們就得到了新增了Hint之後的sql語句。

如果我們需要修改的sql語句中的變數是:1 :2之類的，比如：
select  /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s p step) */
              ...
              s.WORKER  FROM TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE
              s.root2proc_inst = :1  AND s.step2step = step.objid AND ( NOT
              (step.step_type = 4)) AND ( s.assignee = 'BpmInServer'       OR
              s.assignee = 'BpmInServerSmThr'       OR s.assignee = 'BpmJms') AND
              s.committer is NOT NULL AND ( s.status in (50                    ))
這樣透過variable就會出錯了。只使用數字來作為變數還是不合規則的。
我們可以嘗試使用如下的一個簡單pl/sql來實現。
比如存在一個變數，我們就在 cursor中定義一個欄位，存在多個變數就定義多個欄位，最後在execute immediate的後面使用using子句來完成。
declare
cursor temp_cur is select '100' id from dual;
begin
for i in temp_cur loop
execute immediate 'select  /*+ leading(s) index(s TABLE_BPM_STEP_INST_1IX) use_nl(s step) */ 
s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS,
s.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT,
s.CURR_FAULT2FAULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM,
s.HAS_DEPENDENTS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS,
s.ITER_COUNT, s.NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS,
s.OBJID, s.OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST,
s.ROOT2PROC_INST, s.START_TIME, s.STATUS, s.STATUS_CHANGE_TIME,
s.STEP2STEP, s.TARGETED_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME,
s.WORKER  FROM TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE
s.root2proc_inst = :1  AND s.step2step = step.objid AND ( NOT
(step.step_type = 4)) AND ( s.assignee = ''BpmInServer''       OR
s.assignee = ''BpmInServerSmThr''       OR s.assignee = ''BpmJms'') AND
s.committer is NOT NULL AND ( s.status in (50                    ))' using i.id;
end loop;
end;
/
透過v$sql即可得到對應的sql_id
目前自己使用的是這兩種方式來解決繫結變數的問題，如果有更好的，希望拍磚。