?
故障原因：?
一条select有两个执行计划，在sqlplus中执行选择好的执行计划，只要40毫秒，而在程序中执行选择了差的执行计划，要1分23秒左右，导致前台业务超时报错。
?
故障解决：?
使用outline固定好的执行计划后解决了该故障。
?
故障发展顺序：?
1，早上一上班，说CRM的一个业务报错，crm应用开发人员、接口的、tuxdo、dba集中到一起开始诊断错误。
2，业务返回超时错误
3，数据库这边抓取AWR报告发现如下信息：
4，此时应用开发人员也发过来了该条sql说业务会调用到这条sql。
5，于是在sqlplus中执行了该sql，发现执行时间非常快，逻辑读也很低执行计划也没有问题。
?
通过v$sql_bind_capture得到sql执行时的绑定变量值，在sqlplus中再次执行该sql
select trade_id,
accept_month,
user_id,
t4.service_id,
modify_tag,
t4.start_date,
t4.end_date,
item_id,
user_id_a,
t4.package_id,
t4.product_id,
t1.product_name,
t2.package_name,
t3.service_name
FROM uop_crm2.TD_B_PRODUCT t1,
uop_crm2.TD_B_PACKAGE t2,
uop_crm2.TD_B_SERVICE t3,
(SELECT to_char(0) trade_id,
0 accept_month,
to_char(user_id) user_id,
service_id,
'A' modify_tag,
to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,
to_char(end_date, 'yyyy-mm-dd hh24:mi:ss') end_date,
to_char(ITEM_ID) ITEM_ID,
to_char(USER_ID_A) USER_ID_A,
PACKAGE_ID,
PRODUCT_ID
FROM uop_crm2.tf_f_user_svc a
WHERE user_id = TO_NUMBER('3114042824225916')
AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)
AND end_date > sysdate
AND NOT EXISTS
(SELECT 1
FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c
WHERE c.user_id = TO_NUMBER('3114042824225916')
AND c.cancel_tag = '0'
AND c.accept_month = TO_NUMBER('11')
AND b.trade_id = c.trade_id
AND b.accept_month = c.accept_month
AND b.modify_tag in ('1', 'B')
AND b.product_id = a.product_id
AND b.package_id = a.package_id
AND b.service_id = a.service_id
AND b.start_date = a.start_date)
UNION ALL
SELECT to_char(d.TRADE_ID) TRADE_ID,
d.ACCEPT_MONTH,
to_char(d.USER_ID) USER_ID,
SERVICE_ID,
MODIFY_TAG,
to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,
to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,
to_char(ITEM_ID) ITEM_ID,
to_char(d.USER_ID_A) USER_ID_A,
d.PACKAGE_ID,
d.PRODUCT_ID
FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
WHERE e.user_id = TO_NUMBER('3114042824225916')
AND e.cancel_tag = '0'
AND e.accept_month = TO_NUMBER('11')
AND d.trade_id = e.trade_id
AND d.user_id = TO_NUMBER('3114042824225916')
AND d.accept_month = TO_NUMBER('11')
AND d.modify_tag in ('0', 'A')
AND NOT EXISTS
(SELECT 1
FROM uop_crm2.tf_b_trade_svc
WHERE trade_id = TO_NUMBER('3114111918985865')
AND accept_month = TO_NUMBER('11')
AND modify_tag in ('1', 'B')
AND product_id = d.product_id
AND package_id = d.package_id
AND service_id = d.service_id
AND start_date = d.start_date)
UNION ALL
SELECT to_char(d.TRADE_ID) TRADE_ID,
d.accept_month H,
to_char(d.USER_ID) USER_ID,
SERVICE_ID,
MODIFY_TAG,
to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,
to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,
to_char(ITEM_ID) ITEM_ID,
to_char(d.USER_ID_A) USER_ID_A,
d.PACKAGE_ID,
d.PRODUCT_ID
FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
WHERE e.user_id = TO_NUMBER('3114042824225916')
AND e.cancel_tag = '0'
AND e.accept_month = TO_NUMBER('11')
AND d.trade_id = e.trade_id
AND d.user_id = TO_NUMBER('3114042824225916')
AND d.accept_month = TO_NUMBER('11')
AND d.modify_tag in ('1', 'B')
AND SYSDATE < d.end_date
AND d.start_date < d.end_date) t4
WHERE t1.product_id(+) = t4.product_id
AND t2.package_id(+) = t4.package_id
AND t3.service_id(+) = t4.service_id;
PACKAGE_NAME
----------------------------------------------------------------------------------------------------
SERVICE_NAME
----------------------------------------------------------------------------------------------------
基本业务功能包
呼叫保持
15 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1183257532
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 903 | 38 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS OUTER | | 3 | 903 | 38 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 3 | 702 | 35 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS OUTER | | 3 | 498 | 32 (0)| 00:00:01 | | |
| 4 | VIEW | | 3 | 429 | 29 (0)| 00:00:01 | | |
| 5 | UNION-ALL | | | | | | | |
| 6 | NESTED LOOPS ANTI | | 1 | 69 | 11 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 1 | 61 | 4 (0)| 00:00:01 | 6 | 6 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | TF_F_USER_SVC | 1 | 61 | 4 (0)| 00:00:01 | 6 | 6 |
|* 9 | INDEX RANGE SCAN | PK_TF_F_USER_SVC | 1 | | 3 (0)| 00:00:01 | 6 | 6 |
| 10 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 8 | 7 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 65 | 7 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | TF_B_TRADE | 1 | 25 | 4 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IDX_TF_B_TRADE_USERID | 1 | | 3 (0)| 00:00:01 | | |
| 14 | PARTITION RANGE SINGLE | | 1 | 40 | 3 (0)| 00:00:01 | 11 | 11 |
|* 15 | INDEX RANGE SCAN | PK_TF_B_TRADE_SVC | 1 | 40 | 3 (0)| 00:00:01 | 11 | 11 |
| 16 | NESTED LOOPS ANTI | | 1 | 137 | 11 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 97 | 8 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | TF_B_TRADE | 1 | 25 | 4 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | IDX_TF_B_TRADE_USERID | 1 | | 3 (0)| 00:00:01 | | |
| 20 | PARTITION RANGE SINGLE | | 1 | 72 | 4 (0)| 00:00:01 | 11 | 11 |
| 21 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC | 1 | 72 | 4 (0)| 00:00:01 | 11 | 11 |
|* 22 | INDEX RANGE SCAN | PK_TF_B_TRADE_SVC | 1 | | 3 (0)| 00:00:01 | 11 | 11 |
| 23 | PARTITION RANGE SINGLE | | 1 | 40 | 3 (0)| 00:00:01 | 11 | 11 |
|* 24 | INDEX RANGE SCAN | PK_TF_B_TRADE_SVC | 1 | 40 | 3 (0)| 00:00:01 | 11 | 11 |
| 25 | NESTED LOOPS | | 1 | 97 | 7 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS | | 1 | 97 | 7 (0)| 00:00:01 | | |
| 27 | PARTITION RANGE SINGLE | | 1 | 72 | 5 (0)| 00:00:01 | 11 | 11 |
|* 28 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC | 1 | 72 | 5 (0)| 00:00:01 | 11 | 11 |
|* 29 | INDEX RANGE SCAN | IDX1_TF_B_TRADE_SVC | 4 | | 3 (0)| 00:00:01 | 11 | 11 |
|* 30 | INDEX UNIQUE SCAN | PK_TF_B_TRADE | 1 | | 1 (0)| 00:00:01 | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | TF_B_TRADE | 1 | 25 | 2 (0)| 00:00:01 | | |
| 32 | TABLE ACCESS BY INDEX ROWID | TD_B_SERVICE | 1 | 23 | 1 (0)| 00:00:01 | | |
|* 33 | INDEX UNIQUE SCAN | PK_TD_B_SERVICE | 1 | | 0 (0)| 00:00:01 | | |
| 34 | TABLE ACCESS BY INDEX ROWID | TD_B_PRODUCT | 1 | 68 | 1 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | PK_TD_B_PRODUCT | 1 | | 0 (0)| 00:00:01 | | |
| 36 | TABLE ACCESS BY INDEX ROWID | TD_B_PACKAGE | 1 | 67 | 1 (0)| 00:00:01 | | |
|* 37 | INDEX UNIQUE SCAN | PK_TD_B_PACKAGE | 1 | | 0 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("END_DATE">SYSDATE@!)
9 - access("USER_ID"=3114042824