使用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
目前自己使用的是这两种方式来解决绑定变量的问题，如果有更好的，希望拍砖。