一次数据库不繁忙时一条sql语句2个执行计划导致业务超时的故障处理

• 故障原因：

一条select有两个执行计划，在sqlplus中执行选择好的执行计划，只要40毫秒，而在程序中执行选择了差的执行计划，要1分23秒左右，导致前台业务超时报错。

• 故障解决：

使用outline固定好的执行计划后解决了该故障。

• 故障发展顺序：

1，早上一上班，说CRM的一个业务报错，crm应用开发人员、接口的、tuxdo、dba集中到一起开始诊断错误。

2，业务返回超时错误

3，数据库这边抓取AWR报告发现如下信息：

4，此时应用开发人员也发过来了该条sql说业务会调用到这条sql。

5，于是在sqlplus中执行了该sql，发现执行时间非常快，逻辑读也很低执行计划也没有问题。

[sql] view plain copy print ?

1. 通过v$sql_bind_capture得到sql执行时的绑定变量值，在sqlplus中再次执行该sql  
2. select trade_id,  
3.        accept_month,  
4.        user_id,  
5.        t4.service_id,  
6.        modify_tag,  
7.        t4.start_date,  
8.        t4.end_date,  
9.        item_id,  
10.        user_id_a,  
11.        t4.package_id,  
12.        t4.product_id,  
13.        t1.product_name,  
14.        t2.package_name,  
15.        t3.service_name  
16.   FROM uop_crm2.TD_B_PRODUCT t1,  
17.        uop_crm2.TD_B_PACKAGE t2,  
18.        uop_crm2.TD_B_SERVICE t3,  
19.        (SELECT to_char(0) trade_id,  
20.                0 accept_month,  
21.                to_char(user_id) user_id,  
22.                service_id,  
23.                'A' modify_tag,  
24.                to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,  
25.                to_char(end_date, 'yyyy-mm-dd hh24:mi:ss') end_date,  
26.                to_char(ITEM_ID) ITEM_ID,  
27.                to_char(USER_ID_A) USER_ID_A,  
28.                PACKAGE_ID,  
29.                PRODUCT_ID  
30.           FROM uop_crm2.tf_f_user_svc a  
31.          WHERE user_id = TO_NUMBER('3114042824225916')  
32.            AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)  
33.            AND end_date > sysdate  
34.            AND NOT EXISTS  
35.          (SELECT 1  
36.                   FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c  
37.                  WHERE c.user_id = TO_NUMBER('3114042824225916')  
38.                    AND c.cancel_tag = '0'  
39.                    AND c.accept_month = TO_NUMBER('11')  
40.                    AND b.trade_id = c.trade_id  
41.                    AND b.accept_month = c.accept_month  
42.                    AND b.modify_tag in ('1', 'B')  
43.                    AND b.product_id = a.product_id  
44.                    AND b.package_id = a.package_id  
45.                    AND b.service_id = a.service_id  
46.                    AND b.start_date = a.start_date)  
47.         UNION ALL  
48.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
49.                d.ACCEPT_MONTH,  
50.                to_char(d.USER_ID) USER_ID,  
51.                SERVICE_ID,  
52.                MODIFY_TAG,  
53.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
54.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
55.                to_char(ITEM_ID) ITEM_ID,  
56.                to_char(d.USER_ID_A) USER_ID_A,  
57.                d.PACKAGE_ID,  
58.                d.PRODUCT_ID  
59.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
60.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
61.            AND e.cancel_tag = '0'  
62.            AND e.accept_month = TO_NUMBER('11')  
63.            AND d.trade_id = e.trade_id  
64.            AND d.user_id = TO_NUMBER('3114042824225916')  
65.            AND d.accept_month = TO_NUMBER('11')  
66.            AND d.modify_tag in ('0', 'A')  
67.            AND NOT EXISTS  
68.          (SELECT 1  
69.                   FROM uop_crm2.tf_b_trade_svc  
70.                  WHERE trade_id = TO_NUMBER('3114111918985865')  
71.                    AND accept_month = TO_NUMBER('11')  
72.                    AND modify_tag in ('1', 'B')  
73.                    AND product_id = d.product_id  
74.                    AND package_id = d.package_id  
75.                    AND service_id = d.service_id  
76.                    AND start_date = d.start_date)  
77.         UNION ALL  
78.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
79.                d.accept_month H,  
80.                to_char(d.USER_ID) USER_ID,  
81.                SERVICE_ID,  
82.                MODIFY_TAG,  
83.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
84.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
85.                to_char(ITEM_ID) ITEM_ID,  
86.                to_char(d.USER_ID_A) USER_ID_A,  
87.                d.PACKAGE_ID,  
88.                d.PRODUCT_ID  
89.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
90.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
91.            AND e.cancel_tag = '0'  
92.            AND e.accept_month = TO_NUMBER('11')  
93.            AND d.trade_id = e.trade_id  
94.            AND d.user_id = TO_NUMBER('3114042824225916')  
95.            AND d.accept_month = TO_NUMBER('11')  
96.            AND d.modify_tag in ('1', 'B')  
97.            AND SYSDATE < d.end_date  
98.            AND d.start_date < d.end_date) t4  
99.  WHERE t1.product_id(+) = t4.product_id  
100.    AND t2.package_id(+) = t4.package_id  
101.    AND t3.service_id(+) = t4.service_id;  
102.      
103. PACKAGE_NAME  
104. ----------------------------------------------------------------------------------------------------  
105. SERVICE_NAME  
106. ----------------------------------------------------------------------------------------------------  
107. 基本业务功能包  
108. 呼叫保持  
109.   
110.   
111. 15 rows selected.  
112.   
113. Elapsed: 00:00:00.01  
114.   
115. Execution Plan  
116. ----------------------------------------------------------  
117. Plan hash value: 1183257532  
118.   
119. -----------------------------------------------------------------------------------------------------------------------------------  
120. | Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
121. -----------------------------------------------------------------------------------------------------------------------------------  
122. |   0 | SELECT STATEMENT                          |                       |     3 |   903 |    38   (0)| 00:00:01 |       |       |  
123. |   1 |  NESTED LOOPS OUTER                       |                       |     3 |   903 |    38   (0)| 00:00:01 |       |       |  
124. |   2 |   NESTED LOOPS OUTER                      |                       |     3 |   702 |    35   (0)| 00:00:01 |       |       |  
125. |   3 |    NESTED LOOPS OUTER                     |                       |     3 |   498 |    32   (0)| 00:00:01 |       |       |  
126. |   4 |     VIEW                                  |                       |     3 |   429 |    29   (0)| 00:00:01 |       |       |  
127. |   5 |      UNION-ALL                            |                       |       |       |            |          |       |       |  
128. |   6 |       NESTED LOOPS ANTI                   |                       |     1 |    69 |    11   (0)| 00:00:01 |       |       |  
129. |   7 |        PARTITION RANGE SINGLE             |                       |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |  
130. |*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID | TF_F_USER_SVC         |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |  
131. |*  9 |          INDEX RANGE SCAN                 | PK_TF_F_USER_SVC      |     1 |       |     3   (0)| 00:00:01 |     6 |     6 |  
132. |  10 |        VIEW PUSHED PREDICATE              | VW_SQ_1               |     1 |     8 |     7   (0)| 00:00:01 |       |       |  
133. |  11 |         NESTED LOOPS                      |                       |     1 |    65 |     7   (0)| 00:00:01 |       |       |  
134. |* 12 |          TABLE ACCESS BY INDEX ROWID      | TF_B_TRADE            |     1 |    25 |     4   (0)| 00:00:01 |       |       |  
135. |* 13 |           INDEX RANGE SCAN                | IDX_TF_B_TRADE_USERID |     1 |       |     3   (0)| 00:00:01 |       |       |  
136. |  14 |          PARTITION RANGE SINGLE           |                       |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
137. |* 15 |           INDEX RANGE SCAN                | PK_TF_B_TRADE_SVC     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
138. |  16 |       NESTED LOOPS ANTI                   |                       |     1 |   137 |    11   (0)| 00:00:01 |       |       |  
139. |  17 |        NESTED LOOPS                       |                       |     1 |    97 |     8   (0)| 00:00:01 |       |       |  
140. |* 18 |         TABLE ACCESS BY INDEX ROWID       | TF_B_TRADE            |     1 |    25 |     4   (0)| 00:00:01 |       |       |  
141. |* 19 |          INDEX RANGE SCAN                 | IDX_TF_B_TRADE_USERID |     1 |       |     3   (0)| 00:00:01 |       |       |  
142. |  20 |         PARTITION RANGE SINGLE            |                       |     1 |    72 |     4   (0)| 00:00:01 |    11 |    11 |  
143. |  21 |          TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC        |     1 |    72 |     4   (0)| 00:00:01 |    11 |    11 |  
144. |* 22 |           INDEX RANGE SCAN                | PK_TF_B_TRADE_SVC     |     1 |       |     3   (0)| 00:00:01 |    11 |    11 |  
145. |  23 |        PARTITION RANGE SINGLE             |                       |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
146. |* 24 |         INDEX RANGE SCAN                  | PK_TF_B_TRADE_SVC     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
147. |  25 |       NESTED LOOPS                        |                       |     1 |    97 |     7   (0)| 00:00:01 |       |       |  
148. |  26 |        NESTED LOOPS                       |                       |     1 |    97 |     7   (0)| 00:00:01 |       |       |  
149. |  27 |         PARTITION RANGE SINGLE            |                       |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |  
150. |* 28 |          TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC        |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |  
151. |* 29 |           INDEX RANGE SCAN                | IDX1_TF_B_TRADE_SVC   |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |  
152. |* 30 |         INDEX UNIQUE SCAN                 | PK_TF_B_TRADE         |     1 |       |     1   (0)| 00:00:01 |       |       |  
153. |* 31 |        TABLE ACCESS BY INDEX ROWID        | TF_B_TRADE            |     1 |    25 |     2   (0)| 00:00:01 |       |       |  
154. |  32 |     TABLE ACCESS BY INDEX ROWID           | TD_B_SERVICE          |     1 |    23 |     1   (0)| 00:00:01 |       |       |  
155. |* 33 |      INDEX UNIQUE SCAN                    | PK_TD_B_SERVICE       |     1 |       |     0   (0)| 00:00:01 |       |       |  
156. |  34 |    TABLE ACCESS BY INDEX ROWID            | TD_B_PRODUCT          |     1 |    68 |     1   (0)| 00:00:01 |       |       |  
157. |* 35 |     INDEX UNIQUE SCAN                     | PK_TD_B_PRODUCT       |     1 |       |     0   (0)| 00:00:01 |       |       |  
158. |  36 |   TABLE ACCESS BY INDEX ROWID             | TD_B_PACKAGE          |     1 |    67 |     1   (0)| 00:00:01 |       |       |  
159. |* 37 |    INDEX UNIQUE SCAN                      | PK_TD_B_PACKAGE       |     1 |       |     0   (0)| 00:00:01 |       |       |  
160. -----------------------------------------------------------------------------------------------------------------------------------  
161.   
162. Predicate Information (identified by operation id):  
163. ---------------------------------------------------  
164.   
165.    8 - filter("END_DATE">SYSDATE@!)  
166.    9 - access("USER_ID"=3114042824225916 AND "PARTITION_ID"=5916)  
167.   12 - filter("C"."ACCEPT_MONTH"=11 AND "C"."CANCEL_TAG"='0')  
168.   13 - access("C"."USER_ID"=3114042824225916)  
169.   15 - access("B"."TRADE_ID"="C"."TRADE_ID" AND "B"."ACCEPT_MONTH"=11 AND "B"."SERVICE_ID"="A"."SERVICE_ID" AND  
170.               "B"."START_DATE"="A"."START_DATE" AND "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND "B"."PACKAGE_ID"="A"."PACKAGE_ID")  
171.        filter("B"."START_DATE"="A"."START_DATE" AND "B"."SERVICE_ID"="A"."SERVICE_ID" AND  
172.               "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND "B"."PACKAGE_ID"="A"."PACKAGE_ID" AND ("B"."MODIFY_TAG"='1' OR "B"."MODIFY_TAG"='B'))  
173.   18 - filter("E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"='0')  
174.   19 - access("E"."USER_ID"=3114042824225916)  
175.   22 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "D"."ACCEPT_MONTH"=11 AND "D"."USER_ID"=3114042824225916)  
176.        filter("D"."MODIFY_TAG"='0' OR "D"."MODIFY_TAG"='A')  
177.   24 - access("TRADE_ID"=3114111918985865 AND "ACCEPT_MONTH"=11 AND "SERVICE_ID"="D"."SERVICE_ID" AND  
178.               "START_DATE"="D"."START_DATE" AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID")  
179.        filter(("MODIFY_TAG"='1' OR "MODIFY_TAG"='B') AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID"  
180.               AND "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE")  
181.   28 - filter(("D"."MODIFY_TAG"='1' OR "D"."MODIFY_TAG"='B') AND "D"."START_DATE"<"D"."END_DATE" AND  
182.               "D"."END_DATE">SYSDATE@! AND "D"."ACCEPT_MONTH"=11)  
183.   29 - access("D"."USER_ID"=3114042824225916)  
184.   30 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"='0')  
185.   31 - filter("E"."USER_ID"=3114042824225916)  
186.   33 - access("T3"."SERVICE_ID"(+)="T4"."SERVICE_ID")  
187.   35 - access("T1"."PRODUCT_ID"(+)="T4"."PRODUCT_ID")  
188.   37 - access("T2"."PACKAGE_ID"(+)="T4"."PACKAGE_ID")  
189.   
190.   
191. Statistics  
192. ----------------------------------------------------------  
193.           0  recursive calls  
194.           0  db block gets  
195.          82  consistent gets  
196.           0  physical reads  
197.           0  redo size  
198.        2390  bytes sent via SQL*Net to client  
199.         520  bytes received via SQL*Net from client  
200.           2  SQL*Net roundtrips to/from client  
201.           0  sorts (memory)  
202.           0  sorts (disk)  
203.          15  rows processed  
204.   
205. SQL>   

6，执行很快，于是让应用开发人员在该sql执行前后加一个时间输出到日志里面，看看sql到底执行了多长时间，测试结果是58秒。

7，此时就确认应该sql在sqlplus和程序里的执行计划不一样，于是考虑针对sql_id:1huatx9vws2u3做一个sqlrpt。

8，为了再现这个差的执行计划是否真的执行很长时间，于是对该sqlid做了一个advanced的执行计划显示，并取出其中的差的Outline Data添加到sql里面然后再次执行。

[sql] view plain copy print ?

1. SQL> select * from table(dbms_xplan.display_cursor('1huatx9vws2u3',null,'advanced'));  

把plan hash value为353242268 outline data拿出来，outline data其实就是个hint，加到select后面，再现差的执行计划的执行效率：

[sql] view plain copy print ?

1. SELECT /*+                                                                                                                                                                                                                                       
2.       BEGIN_OUTLINE_DATA                                                                                                                                                                                                                    
3.       IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                           
4.       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')                                                                                                                                                                                                 
5.       DB_VERSION('11.2.0.4')                                                                                                                                                                                                                
6.       ALL_ROWS                                                                                                                                                                                                                              
7.       OUTLINE_LEAF(@"SEL$2AD7F9D9")  
8.       PUSH_PRED(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550" 7 6 5 4)                                                                                                                                                                           
9.       OUTLINE_LEAF(@"SEL$B29E968D")                                                                                                                                                                                                         
10.       UNNEST(@"SEL$3")                                                                                                                                                                                                                      
11.       OUTLINE_LEAF(@"SEL$385088EC")                                                                                                                                                                                                         
12.       UNNEST(@"SEL$5")                                                                                                                                                                                                                      
13.       OUTLINE_LEAF(@"SEL$6")                                                                                                                                                                                                                
14.       OUTLINE_LEAF(@"SET$1")                                                                                                                                                                                                                
15.       OUTLINE_LEAF(@"SEL$1")                                                                                                                                                                                                                
16.       OUTLINE(@"SEL$291F8F59")                                                                                                                                                                                                              
17.       OUTLINE(@"SEL$B29E968D")                                                                                                                                                                                                              
18.       UNNEST(@"SEL$3")   
19.       OUTLINE(@"SEL$E9784550")                                                                                                                                                                                                              
20.       OUTLINE(@"SEL$3")                                                                                                                                                                                                                     
21.       OUTLINE(@"SEL$4")                                                                                                                                                                                                                     
22.       OUTLINE(@"SEL$5")                                                                                                                                                                                                                     
23.       OUTLINE(@"SEL$2")                                                                                                                                                                                                                     
24.       NO_ACCESS(@"SEL$1" "T4"@"SEL$1")                                                                                                                                                                                                      
25.       INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("TD_B_SERVICE"."SERVICE_ID"))                                                                                                                                                                     
26.       INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("TD_B_PRODUCT"."PRODUCT_ID"))                                                                                                                                                                     
27.       INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("TD_B_PACKAGE"."PACKAGE_ID"))                                                                                                                                                                     
28.       LEADING(@"SEL$1" "T4"@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                 
29.       USE_NL(@"SEL$1" "T3"@"SEL$1")    
30.             USE_NL(@"SEL$1" "T1"@"SEL$1")                                                                                                                                                                                                         
31.       USE_NL(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                                                         
32.       INDEX_RS_ASC(@"SEL$6" "D"@"SEL$6" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                                                                                         
33.       INDEX(@"SEL$6" "E"@"SEL$6" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                           
34.       LEADING(@"SEL$6" "D"@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                             
35.       USE_NL(@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                                          
36.       NLJ_BATCHING(@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                                    
37.       INDEX_RS_ASC(@"SEL$385088EC" "D"@"SEL$4" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                                                                                  
38.       INDEX(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5" ("TF_B_TRADE_SVC"."TRADE_ID" "TF_B_TRADE_SVC"."ACCEPT_MONTH"                                                                                                                           
39.               "TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID" "TF_B_TRADE_SVC"."START_DATE" "TF_B_TRADE_SVC"."PRODUCT_ID"                                                                                                          
40.               "TF_B_TRADE_SVC"."PACKAGE_ID" "TF_B_TRADE_SVC"."USER_ID_A" "TF_B_TRADE_SVC"."MODIFY_TAG"))     
41.                     INDEX(@"SEL$385088EC" "E"@"SEL$4" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                    
42.       LEADING(@"SEL$385088EC" "D"@"SEL$4" "TF_B_TRADE_SVC"@"SEL$5" "E"@"SEL$4")                                                                                                                                                             
43.       USE_NL(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5")                                                                                                                                                                                      
44.       USE_NL(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                                                                                   
45.       NLJ_BATCHING(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                                                                             
46.       INDEX_RS_ASC(@"SEL$B29E968D" "A"@"SEL$2" ("TF_F_USER_SVC"."USER_ID" "TF_F_USER_SVC"."PARTITION_ID"                                                                                                                                    
47.               "TF_F_USER_SVC"."SERVICE_ID" "TF_F_USER_SVC"."START_DATE"))                                                                                                                                                                   
48.       NO_ACCESS(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                                   
49.       LEADING(@"SEL$B29E968D" "A"@"SEL$2" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                         
50.       USE_NL(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                                      
51.       INDEX_SS(@"SEL$2AD7F9D9" "B"@"SEL$3" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))    
52.             INDEX(@"SEL$2AD7F9D9" "C"@"SEL$3" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                    
53.       LEADING(@"SEL$2AD7F9D9" "B"@"SEL$3" "C"@"SEL$3")                                                                                                                                                                                      
54.       USE_NL(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                                                                                   
55.       NLJ_BATCHING(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                                                                             
56.       END_OUTLINE_DATA                                                                                                                                                                                                                      
57.   */   trade_id,  
58.        accept_month,  
59.        user_id,  
60.        t4.service_id,  
61.        modify_tag,  
62.        t4.start_date,  
63.        t4.end_date,  
64.        item_id,  
65.        user_id_a,  
66.        t4.package_id,  
67.        t4.product_id,  
68.        t1.product_name,  
69.        t2.package_name,  
70.        t3.service_name  
71.   FROM uop_crm2.TD_B_PRODUCT t1,  
72.        uop_crm2.TD_B_PACKAGE t2,  
73.        uop_crm2.TD_B_SERVICE t3,  
74.        (SELECT to_char(0) trade_id,  
75.                0 accept_month,  
76.                to_char(user_id) user_id,  
77.                service_id,  
78.                'A' modify_tag,  
79.                to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,  
80.                to_char(end_date, 'yyyy-mm-dd hh24:mi:ss') end_date,  
81.                to_char(ITEM_ID) ITEM_ID,  
82.                to_char(USER_ID_A) USER_ID_A,  
83.                PACKAGE_ID,  
84.                PRODUCT_ID  
85.           FROM uop_crm2.tf_f_user_svc a  
86.          WHERE user_id = TO_NUMBER('3114042824225916')  
87.            AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)  
88.            AND end_date > sysdate  
89.            AND NOT EXISTS  
90.          (SELECT 1  
91.                   FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c  
92.                  WHERE c.user_id = TO_NUMBER('3114042824225916')  
93.                    AND c.cancel_tag = '0'  
94.                    AND c.accept_month = TO_NUMBER('11')  
95.                    AND b.trade_id = c.trade_id  
96.                    AND b.accept_month = c.accept_month  
97.                    AND b.modify_tag in ('1', 'B')  
98.                    AND b.product_id = a.product_id  
99.                    AND b.package_id = a.package_id  
100.                    AND b.service_id = a.service_id  
101.                    AND b.start_date = a.start_date)  
102.         UNION ALL  
103.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
104.                d.ACCEPT_MONTH,  
105.                to_char(d.USER_ID) USER_ID,  
106.                SERVICE_ID,  
107.                MODIFY_TAG,  
108.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
109.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
110.                to_char(ITEM_ID) ITEM_ID,  
111.                to_char(d.USER_ID_A) USER_ID_A,  
112.                d.PACKAGE_ID,  
113.                d.PRODUCT_ID  
114.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
115.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
116.            AND e.cancel_tag = '0'  
117.            AND e.accept_month = TO_NUMBER('11')  
118.            AND d.trade_id = e.trade_id  
119.            AND d.user_id = TO_NUMBER('3114042824225916')  
120.            AND d.accept_month = TO_NUMBER('11')  
121.            AND d.modify_tag in ('0', 'A')  
122.            AND NOT EXISTS  
123.          (SELECT 1  
124.                   FROM uop_crm2.tf_b_trade_svc  
125.                  WHERE trade_id = TO_NUMBER('3114111918985865')  
126.                    AND accept_month = TO_NUMBER('11')  
127.                    AND modify_tag in ('1', 'B')  
128.                    AND product_id = d.product_id  
129.                    AND package_id = d.package_id  
130.                    AND service_id = d.service_id  
131.                    AND start_date = d.start_date)  
132.         UNION ALL  
133.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
134.                d.accept_month H,  
135.                to_char(d.USER_ID) USER_ID,  
136.                SERVICE_ID,  
137.                MODIFY_TAG,  
138.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
139.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
140.                to_char(ITEM_ID) ITEM_ID,  
141.                to_char(d.USER_ID_A) USER_ID_A,  
142.                d.PACKAGE_ID,  
143.                d.PRODUCT_ID  
144.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
145.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
146.            AND e.cancel_tag = '0'  
147.            AND e.accept_month = TO_NUMBER('11')  
148.            AND d.trade_id = e.trade_id  
149.            AND d.user_id = TO_NUMBER('3114042824225916')  
150.            AND d.accept_month = TO_NUMBER('11')  
151.            AND d.modify_tag in ('1', 'B')  
152.            AND SYSDATE < d.end_date  
153.            AND d.start_date < d.end_date) t4  
154.  WHERE t1.product_id(+) = t4.product_id  
155.    AND t2.package_id(+) = t4.package_id  
156.    AND t3.service_id(+) = t4.service_id;  
157.      

[sql] view plain copy print ?

1. ----------------------------------------------------------------------------------------------------  
2. 基本业务功能包  
3. 4G/3G流量提醒  
4.   
5.   
6. 15 rows selected.  
7.   
8. Elapsed: 00:01:22.05  
9.   
10. Execution Plan  
11. ----------------------------------------------------------  
12. Plan hash value: 2411435412  
13.   
14. -----------------------------------------------------------------------------------------------------------------------------------  
15. | Id  | Operation                                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
16. -----------------------------------------------------------------------------------------------------------------------------------  
17. |   0 | SELECT STATEMENT                            |                     |     3 |   903 | 78573   (1)| 00:15:43 |       |       |  
18. |   1 |  NESTED LOOPS OUTER                         |                     |     3 |   903 | 78573   (1)| 00:15:43 |       |       |  
19. |   2 |   NESTED LOOPS OUTER                        |                     |     3 |   702 | 78570   (1)| 00:15:43 |       |       |  
20. |   3 |    NESTED LOOPS OUTER                       |                     |     3 |   498 | 78567   (1)| 00:15:43 |       |       |  
21. |   4 |     VIEW                                    |                     |     3 |   429 | 78564   (1)| 00:15:43 |       |       |  
22. |   5 |      UNION-ALL                              |                     |       |       |            |          |       |       |  
23. |   6 |       NESTED LOOPS ANTI                     |                     |     1 |    69 | 78492   (1)| 00:15:42 |       |       |  
24. |   7 |        PARTITION RANGE SINGLE               |                     |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |  
25. |*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID   | TF_F_USER_SVC       |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |  
26. |*  9 |          INDEX RANGE SCAN                   | PK_TF_F_USER_SVC    |     1 |       |     3   (0)| 00:00:01 |     6 |     6 |  
27. |  10 |        VIEW PUSHED PREDICATE                | VW_SQ_1             |     1 |     8 | 78488   (1)| 00:15:42 |       |       |  
28. |  11 |         NESTED LOOPS                        |                     |     1 |    65 | 78488   (1)| 00:15:42 |       |       |  
29. |  12 |          NESTED LOOPS                       |                     |     1 |    65 | 78488   (1)| 00:15:42 |       |       |  
30. |  13 |           PARTITION RANGE SINGLE            |                     |     1 |    40 | 78486   (1)| 00:15:42 |    11 |    11 |  
31. |* 14 |            TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC      |     1 |    40 | 78486   (1)| 00:15:42 |    11 |    11 |  
32. |* 15 |             INDEX SKIP SCAN                 | IDX1_TF_B_TRADE_SVC | 62939 |       | 62209   (1)| 00:12:27 |    11 |    11 |  
33. |* 16 |           INDEX UNIQUE SCAN                 | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |  
34. |* 17 |          TABLE ACCESS BY INDEX ROWID        | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |  
35. |  18 |       NESTED LOOPS                          |                     |     1 |   137 |    64   (0)| 00:00:01 |       |       |  
36. |  19 |        NESTED LOOPS                         |                     |    14 |   137 |    64   (0)| 00:00:01 |       |       |  
37. |  20 |         NESTED LOOPS ANTI                   |                     |    14 |  1568 |    47   (0)| 00:00:01 |       |       |  
38. |  21 |          PARTITION RANGE SINGLE             |                     |    14 |  1008 |     5   (0)| 00:00:01 |    11 |    11 |  
39. |* 22 |           TABLE ACCESS BY LOCAL INDEX ROWID | TF_B_TRADE_SVC      |    14 |  1008 |     5   (0)| 00:00:01 |    11 |    11 |  
40. |* 23 |            INDEX RANGE SCAN                 | IDX1_TF_B_TRADE_SVC |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |  
41. |  24 |          PARTITION RANGE SINGLE             |                     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
42. |* 25 |           INDEX RANGE SCAN                  | PK_TF_B_TRADE_SVC   |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |  
43. |* 26 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |  
44. |* 27 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |  
45. |  28 |       NESTED LOOPS                          |                     |     1 |    97 |     7   (0)| 00:00:01 |       |       |  
46. |  29 |        NESTED LOOPS                         |                     |     1 |    97 |     7   (0)| 00:00:01 |       |       |  
47. |  30 |         PARTITION RANGE SINGLE              |                     |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |  
48. |* 31 |          TABLE ACCESS BY LOCAL INDEX ROWID  | TF_B_TRADE_SVC      |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |  
49. |* 32 |           INDEX RANGE SCAN                  | IDX1_TF_B_TRADE_SVC |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |  
50. |* 33 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |  
51. |* 34 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |  
52. |  35 |     TABLE ACCESS BY INDEX ROWID             | TD_B_SERVICE        |     1 |    23 |     1   (0)| 00:00:01 |       |       |  
53. |* 36 |      INDEX UNIQUE SCAN                      | PK_TD_B_SERVICE     |     1 |       |     0   (0)| 00:00:01 |       |       |  
54. |  37 |    TABLE ACCESS BY INDEX ROWID              | TD_B_PRODUCT        |     1 |    68 |     1   (0)| 00:00:01 |       |       |  
55. |* 38 |     INDEX UNIQUE SCAN                       | PK_TD_B_PRODUCT     |     1 |       |     0   (0)| 00:00:01 |       |       |  
56. |  39 |   TABLE ACCESS BY INDEX ROWID               | TD_B_PACKAGE        |     1 |    67 |     1   (0)| 00:00:01 |       |       |  
57. |* 40 |    INDEX UNIQUE SCAN                        | PK_TD_B_PACKAGE     |     1 |       |     0   (0)| 00:00:01 |       |       |  
58. -----------------------------------------------------------------------------------------------------------------------------------  
59.   
60. Predicate Information (identified by operation id):  
61. ---------------------------------------------------  
62.   
63.    8 - filter("END_DATE">SYSDATE@!)  
64.    9 - access("USER_ID"=3114042824225916 AND "PARTITION_ID"=5916)  
65.   14 - filter("B"."START_DATE"="A"."START_DATE" AND "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND  
66.               "B"."PACKAGE_ID"="A"."PACKAGE_ID" AND ("B"."MODIFY_TAG"='1' OR "B"."MODIFY_TAG"='B') AND "B"."ACCEPT_MONTH"=11)  
67.   15 - access("B"."SERVICE_ID"="A"."SERVICE_ID")  
68.        filter("B"."SERVICE_ID"="A"."SERVICE_ID")  
69.   16 - access("B"."TRADE_ID"="C"."TRADE_ID" AND "C"."ACCEPT_MONTH"=11 AND "C"."CANCEL_TAG"='0')  
70.   17 - filter("C"."USER_ID"=3114042824225916)  
71.   22 - filter(("D"."MODIFY_TAG"='0' OR "D"."MODIFY_TAG"='A') AND "D"."ACCEPT_MONTH"=11)  
72.   23 - access("D"."USER_ID"=3114042824225916)  
73.   25 - access("TRADE_ID"=3114111918985865 AND "ACCEPT_MONTH"=11 AND "SERVICE_ID"="D"."SERVICE_ID" AND  
74.               "START_DATE"="D"."START_DATE" AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID")  
75.        filter(("MODIFY_TAG"='1' OR "MODIFY_TAG"='B') AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID"  
76.               AND "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE")  
77.   26 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"='0')  
78.   27 - filter("E"."USER_ID"=3114042824225916)  
79.   31 - filter(("D"."MODIFY_TAG"='1' OR "D"."MODIFY_TAG"='B') AND "D"."START_DATE"<"D"."END_DATE" AND  
80.               "D"."END_DATE">SYSDATE@! AND "D"."ACCEPT_MONTH"=11)  
81.   32 - access("D"."USER_ID"=3114042824225916)  
82.   33 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"='0')  
83.   34 - filter("E"."USER_ID"=3114042824225916)  
84.   36 - access("T3"."SERVICE_ID"(+)="T4"."SERVICE_ID")  
85.   38 - access("T1"."PRODUCT_ID"(+)="T4"."PRODUCT_ID")  
86.   40 - access("T2"."PACKAGE_ID"(+)="T4"."PACKAGE_ID")  
87.   
88.   
89. Statistics  
90. ----------------------------------------------------------  
91.           0  recursive calls  
92.           0  db block gets  
93.     7065698  consistent gets  
94.      180962  physical reads  
95.        2212  redo size  
96.        2390  bytes sent via SQL*Net to client  
97.         520  bytes received via SQL*Net from client  
98.           2  SQL*Net roundtrips to/from client  
99.           0  sorts (memory)  
100.           0  sorts (disk)  
101.          15  rows processed  

9，由此可以看出sql在使用差的执行计划时在id=15那里使用了索引跳扫导致代价大增，逻辑读达到700百万，这样问题已经很明确了，就是这个sql导致业务超时。

10，通过outline固定执行计划后马上问题就解决了。

• 故障解决

1，可以通过hint强制索引

2，使用outline固定执行计划

3，base line固定执行计划

• 解决步骤

在这里我使用的是outline的方法：

1，给程序用户赋权

grant create any outline to uop_crm2;

2，创建outline

[sql] view plain copy print ?

1. conn uop_crm2/xxxxxx;  
2.   
3. create outline outline_crm2 for category crm2_cat1 on select trade_id,  
4.        accept_month,  
5.        user_id,  
6.        t4.service_id,  
7.        modify_tag,  
8.        t4.start_date,  
9.        t4.end_date,  
10.        item_id,  
11.        user_id_a,  
12.        t4.package_id,  
13.        t4.product_id,  
14.        t1.product_name,  
15.        t2.package_name,  
16.        t3.service_name  
17.   FROM uop_crm2.TD_B_PRODUCT t1,  
18.        uop_crm2.TD_B_PACKAGE t2,  
19.        uop_crm2.TD_B_SERVICE t3,  
20.        (SELECT to_char(0) trade_id,  
21.                0 accept_month,  
22.                to_char(user_id) user_id,  
23.                service_id,  
24.                'A' modify_tag,  
25.                to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,  
26.                to_char(end_date, 'yyyy-mm-dd hh24:mi:ss') end_date,  
27.                to_char(ITEM_ID) ITEM_ID,  
28.                to_char(USER_ID_A) USER_ID_A,  
29.                PACKAGE_ID,  
30.                PRODUCT_ID  
31.           FROM uop_crm2.tf_f_user_svc a  
32.          WHERE user_id = TO_NUMBER('3114042824225916')  
33.            AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)  
34.            AND end_date > sysdate  
35.            AND NOT EXISTS  
36.          (SELECT 1  
37.                   FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c  
38.                  WHERE c.user_id = TO_NUMBER('3114042824225916')  
39.                    AND c.cancel_tag = '0'  
40.                    AND c.accept_month = TO_NUMBER('11')  
41.                    AND b.trade_id = c.trade_id  
42.                    AND b.accept_month = c.accept_month  
43.                    AND b.modify_tag in ('1', 'B')  
44.                    AND b.product_id = a.product_id  
45.                    AND b.package_id = a.package_id  
46.                    AND b.service_id = a.service_id  
47.                    AND b.start_date = a.start_date)  
48.         UNION ALL  
49.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
50.                d.ACCEPT_MONTH,  
51.                to_char(d.USER_ID) USER_ID,  
52.                SERVICE_ID,  
53.                MODIFY_TAG,  
54.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
55.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
56.                to_char(ITEM_ID) ITEM_ID,  
57.                to_char(d.USER_ID_A) USER_ID_A,  
58.                d.PACKAGE_ID,  
59.                d.PRODUCT_ID  
60.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
61.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
62.            AND e.cancel_tag = '0'  
63.            AND e.accept_month = TO_NUMBER('11')  
64.            AND d.trade_id = e.trade_id  
65.            AND d.user_id = TO_NUMBER('3114042824225916')  
66.            AND d.accept_month = TO_NUMBER('11')  
67.            AND d.modify_tag in ('0', 'A')  
68.            AND NOT EXISTS  
69.          (SELECT 1  
70.                   FROM uop_crm2.tf_b_trade_svc  
71.                  WHERE trade_id = TO_NUMBER('3114111918985865')  
72.                    AND accept_month = TO_NUMBER('11')  
73.                    AND modify_tag in ('1', 'B')  
74.                    AND product_id = d.product_id  
75.                    AND package_id = d.package_id  
76.                    AND service_id = d.service_id  
77.                    AND start_date = d.start_date)  
78.         UNION ALL  
79.         SELECT to_char(d.TRADE_ID) TRADE_ID,  
80.                d.accept_month H,  
81.                to_char(d.USER_ID) USER_ID,  
82.                SERVICE_ID,  
83.                MODIFY_TAG,  
84.                to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,  
85.                to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') END_DATE,  
86.                to_char(ITEM_ID) ITEM_ID,  
87.                to_char(d.USER_ID_A) USER_ID_A,  
88.                d.PACKAGE_ID,  
89.                d.PRODUCT_ID  
90.           FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e  
91.          WHERE e.user_id = TO_NUMBER('3114042824225916')  
92.            AND e.cancel_tag = '0'  
93.            AND e.accept_month = TO_NUMBER('11')  
94.            AND d.trade_id = e.trade_id  
95.            AND d.user_id = TO_NUMBER('3114042824225916')  
96.            AND d.accept_month = TO_NUMBER('11')  
97.            AND d.modify_tag in ('1', 'B')  
98.            AND SYSDATE < d.end_date  
99.            AND d.start_date < d.end_date) t4  
100.  WHERE t1.product_id(+) = t4.product_id  
101.    AND t2.package_id(+) = t4.package_id  
102.    AND t3.service_id(+) = t4.service_id;  

3，

[sql] view plain copy print ?

1. alter system set use_stored_outlines=crm2_cat1;  

• 原因分析

此处为什么oracle优化器选择走了IDX1_TF_B_TRADE_SVC的service_id列索引跳扫而没有使用PK_TF_B_TRADE_SVC上的trade_id呢，我们可以看一下索引的信息：

[sql] view plain copy print ?

1. SQL> l  
2.   1* select index_name,blevel,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY from dba_indexes where TABLE_NAME='TF_B_TRADE_SVC'  
3. SQL> /  
4.   
5. INDEX_NAME                         BLEVEL AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY  
6. ------------------------------ ---------- ----------------------- -----------------------  
7. IDX1_TF_B_TRADE_SVC                     2                       1                       1  
8. PK_TF_B_TRADE_SVC                       3                       1                       1  

当两个索引AVG_LEAF_BLOCKS_PER_KEY+AVG_DATA_BLOCKS_PER_KEY的值相同时，就判断blevel，此处由于IDX1_TF_B_TRADE_SVC的blevel=2，oracle

认为走这个索引会有较少的IO，而sql语句中正好有service_id的谓词，因此选择走了跳扫。详细描述参考为什么Oracle不使用我的索引?! 一文。

[sql] view plain copy print ?

1. TABLE_NAME                     TABLE_OWNER                    INDEX_NAME                     COLUMN_NAME   COLUMN_POSITION  
2. ------------------------------ ------------------------------ ------------------------------ ------------- ---------------  
3. TF_B_TRADE_SVC                 UCR_CRM2                       IDX1_TF_B_TRADE_SVC            USER_ID                     1  
4. TF_B_TRADE_SVC                 UCR_CRM2                       IDX1_TF_B_TRADE_SVC            SERVICE_ID                  2  
5.   
6. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              TRADE_ID                    1  
7. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              ACCEPT_MONTH                2  
8. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              USER_ID                     3  
9. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              SERVICE_ID                  4  
10. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              START_DATE                  5  
11. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              PRODUCT_ID                  6  
12. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              PACKAGE_ID                  7  
13. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              USER_ID_A                   8  
14. TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              MODIFY_TAG                  9  

------------------结束---------------------

文章转载于：http://blog.csdn.net/cn_mos/article/details/41309075