sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【案例】Oracle数据库SQL优化之 SQL Tuning like当=执行

时间:2016-11-29 22:47   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:分析一个大SQL语句的案例,发现该SQL语句执行时间较长,分析原因为SQL语句中like当=等于关系执行,是由于参数like_with_bind_as_equality导致。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 一个SQL Tuning例子

以前同事的问题,非常隐蔽的问题,一直没有发现,跟大家分析!
SQL> SET autotrace ON

SQL> SELECT
  2        T5.CONFLICT_ID,
  3        T5.LAST_UPD,
  4        T5.CREATED,
  5        T5.LAST_UPD_BY,
  6        T5.CREATED_BY,
  7        T5.MODIFICATION_NUM,
  8        T5.ROW_ID,
  9        T33.PROVIDER_FLG,
10        T15.NAME,
11        T33.CURR_PRI_LST_ID,
12        T17.KEY_VALUE,
13        T33.CITIZENSHIP_CD,
14        T33.DEDUP_KEY_UPD_DT,
15        T22.ROW_ID,
16        T31.STATE,
17        T31.ADDR,
18        T33.CON_CD,
19        T31.COUNTRY,
20        T31.CITY,
21        T31.ZIPCODE,
22        T33.CUST_SINCE_DT,
23        T33.PR_REGION_ID,
24        T33.NATIONALITY,
25        T28.CON_ID,
26        T33.PR_SECURITY_ID,
27        T4.NAME,
28        T33.MED_SPEC_ID,
29        T19.PR_EMP_ID,
30        T33.PREF_COMM_METH_CD,
31        T33.PR_OU_ADDR_ID,
32        T30.PR_EMP_ID,
33        T21.LOGIN,
34        T19.PR_EMP_ID,
35        T33.PR_PROD_LN_ID,
36        T33.PR_TERR_ID,
37        T9.PR_SMS_NUM_ID,
38        T33.PR_STATE_LIC_ID,
39        T33.AGENT_FLG,
40        T33.MAIDEN_NAME,
41        T33.MEMBER_FLG,
42        T33.PR_NOTE_ID,
43        T33.PR_INDUST_ID,
44        T26.LOGIN,
45        T9.PR_FAX_NUM_ID,
46        T33.SUPPRESS_MAIL_FLG,
47        T33.EMAIL_ADDR,
48        T33.BIRTH_DT,
49        T33.JOB_TITLE,
50        T33.MID_NAME,
51        T33.PR_DEPT_OU_ID,
52        T33.LAST_NAME,
53        T33.SEX_MF,
54        T33.PR_PER_ADDR_ID,
55        T33.PR_POSTN_ID,
56        T33.COMMENTS,
57        T28.PR_ADDR_ID,
58        T33.HOME_PH_NUM,
59        T33.OWNER_PER_ID,
60        T33.CELL_PH_NUM,
61        T33.WORK_PH_NUM,
62        T33.FAX_PH_NUM,
63        T33.FST_NAME,
64        T33.ASST_PH_NUM,
65        T18.ATTRIB_07,
66        T2.INTEGRATION_ID,
67        T33.PR_PER_PAY_PRFL_ID,
68        T33.INTEGRATION_ID,
69        T33.PRIV_FLG,
70        T33.PR_MKT_SEG_ID,
71        T33.PR_REP_SYS_FLG,
72        T33.PR_REP_MANL_FLG,
73        T33.PR_REP_DNRM_FLG,
74        T33.PR_OPTY_ID,
75        T33.SOC_SECURITY_NUM,
76        T33.PR_GRP_OU_ID,
77        T33.EMP_FLG,
78        T7.OWN_INST_ID,
79        T7.INTEGRATION_ID,
80        T33.PERSON_UID,
81        T5.NAME,
82        T2.NAME,
83        T2.PRTNR_FLG,
84        T33.PR_RESP_ID,
85        T33.BU_ID,
86        T28.STATUS,
87        T33.PR_ALT_PH_NUM_ID,
88        T33.PR_EMAIL_ADDR_ID,
89        T20.SHARE_HOME_PH_FLG,
90        T33.PR_SYNC_USER_ID,
91        T33.CON_CREATED_DT,
92        T33.EYE_COLOR,
93        T33.STOCK_PORTFOLIO,
94        T33.X_ACCNT_ID,
95        T18.ATTRIB_43,
96        T29.LOGIN,
97        T23.LAST_NAME,
98        T32.NAME,
99        T32.X_DEALER_CODE,
100        T33.SEX_MF,
101        T33.X_UPD_FLG,
102        T24.LOGIN,
103        T27.ROW_STATUS,
104        T16.PRIM_MARKET_CD,
105        T3.ROW_ID,
106        T10.OU_NUM,
107        T10.LOC,
108        T10.NAME,
109        T3.ROW_ID,
110        T10.PR_SRV_AGREE_ID,
111        T10.PR_BL_PER_ID,
112        T10.PR_SHIP_PER_ID,
113        T10.PR_BL_ADDR_ID,
114        T10.PR_SHIP_ADDR_ID,
115        T3.ROW_ID,
116        T11.CITY,
117        T11.ADDR,
118        T11.STATE,
119        T11.ZIPCODE,
120        T11.COUNTY,
121        T11.X_COUNTY_CD,
122        T8.LOGIN,
123        T33.ROW_ID,
124        T33.PAR_ROW_ID,
125        T33.MODIFICATION_NUM,
126        T33.CREATED_BY,
127        T33.LAST_UPD_BY,
128        T33.CREATED,
129        T33.LAST_UPD,
130        T33.CONFLICT_ID,
131        T33.PAR_ROW_ID,
132        T18.ROW_ID,
133        T18.PAR_ROW_ID,
134        T18.MODIFICATION_NUM,
135        T18.CREATED_BY,
136        T18.LAST_UPD_BY,
137        T18.CREATED,
138        T18.LAST_UPD,
139        T18.CONFLICT_ID,
140        T18.PAR_ROW_ID,
141        T20.ROW_ID,
142        T20.PAR_ROW_ID,
143        T20.MODIFICATION_NUM,
144        T20.CREATED_BY,
145        T20.LAST_UPD_BY,
146        T20.CREATED,
147        T20.LAST_UPD,
148        T20.CONFLICT_ID,
149        T20.PAR_ROW_ID,
150        T9.ROW_ID,
151        T9.PAR_ROW_ID,
152        T9.MODIFICATION_NUM,
153        T9.CREATED_BY,
154        T9.LAST_UPD_BY,
155        T9.CREATED,
156        T9.LAST_UPD,
157        T9.CONFLICT_ID,
158        T9.PAR_ROW_ID,
159        T7.ROW_ID,
160        T7.PAR_ROW_ID,
161        T7.MODIFICATION_NUM,
162        T7.CREATED_BY,
163        T7.LAST_UPD_BY,
164        T7.CREATED,
165        T7.LAST_UPD,
166        T7.CONFLICT_ID,
167        T7.PAR_ROW_ID,
168        T27.ROW_ID,
169        T25.ROW_ID,
170        T3.ROW_ID,
171        T6.ROW_ID,
172        T11.ROW_ID,
173        T14.ROW_ID
174     FROM
175         SIEBEL.S_CONTACT_BU T1,
176         SIEBEL.S_ORG_EXT T2,
177         SIEBEL.S_PARTY T3,
178         SIEBEL.S_MED_SPEC T4,
179         SIEBEL.S_PARTY T5,
180         SIEBEL.S_CON_ADDR T6,
181         SIEBEL.S_CONTACT_SS T7,
182         SIEBEL.S_USER T8,
183         SIEBEL.S_CONTACT_LOYX T9,
184         SIEBEL.S_ORG_EXT T10,
185         SIEBEL.S_ADDR_PER T11,
186         SIEBEL.S_POSTN T12,
187         SIEBEL.S_PARTY T13,
188         SIEBEL.S_PARTY T14,
189         SIEBEL.S_PRI_LST T15,
190         SIEBEL.S_ORG_EXT_FNX T16,
191         SIEBEL.S_DQ_CON_KEY T17,
192         SIEBEL.S_CONTACT_X T18,
193         SIEBEL.S_POSTN T19,
194         SIEBEL.S_EMP_PER T20,
195         SIEBEL.S_USER T21,
196         SIEBEL.S_CASE T22,
197         SIEBEL.S_USER T23,
198         SIEBEL.S_USER T24,
199         SIEBEL.S_PARTY T25,
200         SIEBEL.S_USER T26,
201         SIEBEL.S_POSTN_CON T27,
202         SIEBEL.S_POSTN_CON T28,
203         SIEBEL.S_USER T29,
204         SIEBEL.S_POSTN T30,
205         SIEBEL.S_ADDR_PER T31,
206         SIEBEL.S_ORG_EXT T32,
207         SIEBEL.S_CONTACT T33
208     WHERE
209        T19.PR_EMP_ID = T26.PAR_ROW_ID (+) AND
210        T2.PR_POSTN_ID = T30.PAR_ROW_ID (+) AND
211        T33.PR_POSTN_ID = T19.PAR_ROW_ID (+) AND
212        T33.PR_DEPT_OU_ID = T2.PAR_ROW_ID (+) AND
213        T5.ROW_ID = T28.CON_ID (+) AND T28.POSTN_ID (+) = '1234' AND
214        T30.PR_EMP_ID = T21.PAR_ROW_ID (+) AND
215        T33.PR_PER_ADDR_ID = T31.ROW_ID (+) AND
216        T5.ROW_ID = T22.PR_SUBJECT_ID (+) AND
217        T33.BU_ID = T32.PAR_ROW_ID (+) AND
218        T33.MED_SPEC_ID = T4.ROW_ID (+) AND
219        T33.CURR_PRI_LST_ID = T15.ROW_ID (+) AND
220        T5.ROW_ID = T17.CONTACT_ID (+) AND
221        T19.PR_EMP_ID = T23.PAR_ROW_ID (+) AND
222        T19.PR_EMP_ID = T29.PAR_ROW_ID (+) AND
223        T5.ROW_ID = T33.PAR_ROW_ID AND
224        T5.ROW_ID = T18.PAR_ROW_ID (+) AND
225        T5.ROW_ID = T20.PAR_ROW_ID (+) AND
226        T5.ROW_ID = T9.PAR_ROW_ID (+) AND
227        T5.ROW_ID = T7.PAR_ROW_ID (+) AND
228        T33.PR_POSTN_ID = T27.POSTN_ID AND T33.ROW_ID = T27.CON_ID AND
229        T27.POSTN_ID = T25.ROW_ID AND
230        T27.POSTN_ID = T12.PAR_ROW_ID (+) AND
231        T12.PR_EMP_ID = T24.PAR_ROW_ID (+) AND
232        T33.PR_DEPT_OU_ID = T3.ROW_ID (+) AND
233        T33.PR_DEPT_OU_ID = T10.PAR_ROW_ID (+) AND
234        T33.PR_DEPT_OU_ID = T16.PAR_ROW_ID (+) AND
235        T33.PR_PER_ADDR_ID = T6.ADDR_PER_ID (+) AND T33.ROW_ID = T6.CONTACT_ID (+) AND
236        T33.PR_PER_ADDR_ID = T11.ROW_ID (+) AND
237        T33.PR_SYNC_USER_ID = T14.ROW_ID (+) AND
238        T33.PR_SYNC_USER_ID = T8.PAR_ROW_ID (+) AND
239        T1.BU_ID = '1234' AND T33.ROW_ID = T1.CONTACT_ID AND
240        T1.BU_ID = T13.ROW_ID AND
241        ((T33.PRIV_FLG = 'N' AND T5.PARTY_TYPE_CD != 'Suspect' AND T1.CON_EMP_FLG = 'N') AND
242        (T1.CON_LAST_NAME >= 'M0808594')) AND
243        (T33.CELL_PH_NUM LIKE '13912345678')
244     ORDER BY
245        T1.BU_ID, T1.CON_LAST_NAME, T1.CON_FST_NAME
246  ;

no ROWS selected


Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4013657437

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                   |     2 |  5474 |    36   (6)| 00:00:01 |
|   1 |  SORT ORDER BY                                               |                   |     2 |  5474 |    36   (6)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                                         |                   |     2 |  5474 |    35   (3)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                                        |                   |     2 |  5434 |    34   (3)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER                                       |                   |     2 |  5394 |    33   (4)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER                                      |                   |     2 |  5354 |    32   (4)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER                                     |                   |     2 |  5314 |    31   (4)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER                                    |                   |     2 |  5274 |    30   (4)| 00:00:01 |
|   8 |         NESTED LOOPS                                         |                   |     2 |  5234 |    29   (4)| 00:00:01 |
|   9 |          NESTED LOOPS OUTER                                  |                   |     2 |  5212 |    28   (4)| 00:00:01 |
|  10 |           NESTED LOOPS OUTER                                 |                   |     2 |  5190 |    27   (4)| 00:00:01 |
|  11 |            NESTED LOOPS OUTER                                |                   |     2 |  5168 |    26   (4)| 00:00:01 |
|  12 |             NESTED LOOPS OUTER                               |                   |     2 |  5042 |    25   (4)| 00:00:01 |
|  13 |              NESTED LOOPS OUTER                              |                   |     2 |  4916 |    24   (5)| 00:00:01 |
|  14 |               NESTED LOOPS OUTER                             |                   |     2 |  4866 |    23   (5)| 00:00:01 |
|* 15 |                HASH JOIN OUTER                               |                   |     2 |  4716 |    22   (5)| 00:00:01 |
|  16 |                 NESTED LOOPS OUTER                           |                   |     2 |  4588 |    19   (0)| 00:00:01 |
|  17 |                  NESTED LOOPS OUTER                          |                   |     2 |  3738 |    18   (0)| 00:00:01 |
|  18 |                   NESTED LOOPS                               |                   |     2 |  2950 |    17   (0)| 00:00:01 |
|  19 |                    NESTED LOOPS OUTER                        |                   |     2 |  2812 |    16   (0)| 00:00:01 |
|  20 |                     NESTED LOOPS                             |                   |     2 |  2768 |    15   (0)| 00:00:01 |
|  21 |                      NESTED LOOPS OUTER                      |                   |     2 |  2702 |    14   (0)| 00:00:01 |
|  22 |                       NESTED LOOPS OUTER                     |                   |     2 |  2538 |    13   (0)| 00:00:01 |
|  23 |                        NESTED LOOPS OUTER                    |                   |     2 |  2378 |    12   (0)| 00:00:01 |
|  24 |                         NESTED LOOPS OUTER                   |                   |     2 |  2320 |    11   (0)| 00:00:01 |
|  25 |                          NESTED LOOPS OUTER                  |                   |     2 |  2270 |    10   (0)| 00:00:01 |
|  26 |                           NESTED LOOPS OUTER                 |                   |     2 |  2226 |     9   (0)| 00:00:01 |
|  27 |                            NESTED LOOPS OUTER                |                   |     2 |  2126 |     8   (0)| 00:00:01 |
|  28 |                             NESTED LOOPS OUTER               |                   |     2 |  1996 |     7   (0)| 00:00:01 |
|  29 |                              NESTED LOOPS OUTER              |                   |     2 |  1854 |     6   (0)| 00:00:01 |
|  30 |                               NESTED LOOPS                   |                   |     2 |  1810 |     5   (0)| 00:00:01 |
|  31 |                                NESTED LOOPS OUTER            |                   |     2 |  1732 |     4   (0)| 00:00:01 |
|  32 |                                 NESTED LOOPS OUTER           |                   |     2 |  1464 |     3   (0)| 00:00:01 |
|  33 |                                  NESTED LOOPS                |                   |     2 |  1196 |     2   (0)| 00:00:01 |
|* 34 |                                   INDEX UNIQUE SCAN          | S_PARTY_P1        |     1 |    11 |     1   (0)| 00:00:01 |
|* 35 |                                   TABLE ACCESS BY INDEX ROWID| S_CONTACT         |     2 |  1174 |     1   (0)| 00:00:01 |
|* 36 |                                    INDEX RANGE SCAN          | S_CONTACT_F68_X   |     2 |       |     1   (0)| 00:00:01 |
|  37 |                                  TABLE ACCESS BY INDEX ROWID | S_PRI_LST         |     1 |   134 |     1   (0)| 00:00:01 |
|* 38 |                                   INDEX UNIQUE SCAN          | S_PRI_LST_P1      |     1 |       |     1   (0)| 00:00:01 |
|  39 |                                 TABLE ACCESS BY INDEX ROWID  | S_MED_SPEC        |     1 |   134 |     1   (0)| 00:00:01 |
|* 40 |                                  INDEX UNIQUE SCAN           | S_MED_SPEC_P1     |     1 |       |     1   (0)| 00:00:01 |
|* 41 |                                TABLE ACCESS BY INDEX ROWID   | S_CONTACT_BU      |     1 |    39 |     1   (0)| 00:00:01 |
|* 42 |                                 INDEX RANGE SCAN             | S_CONTACT_BU_U1   |     1 |       |     1   (0)| 00:00:01 |
|  43 |                               TABLE ACCESS BY INDEX ROWID    | S_POSTN           |     1 |    22 |     1   (0)| 00:00:01 |
|* 44 |                                INDEX UNIQUE SCAN             | S_POSTN_U2        |     1 |       |     1   (0)| 00:00:01 |
|  45 |                              TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT_FNX     |     1 |    71 |     1   (0)| 00:00:01 |
|* 46 |                               INDEX RANGE SCAN               | S_ORG_EXT_FNX_U1  |     1 |       |     1   (0)| 00:00:01 |
|  47 |                             TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT         |     1 |    65 |     1   (0)| 00:00:01 |
|* 48 |                              INDEX UNIQUE SCAN               | S_ORG_EXT_U3      |     1 |       |     1   (0)| 00:00:01 |
|  49 |                            TABLE ACCESS BY INDEX ROWID       | S_ORG_EXT         |     1 |    50 |     1   (0)| 00:00:01 |
|* 50 |                             INDEX UNIQUE SCAN                | S_ORG_EXT_U3      |     1 |       |     1   (0)| 00:00:01 |
|  51 |                           TABLE ACCESS BY INDEX ROWID        | S_POSTN           |     1 |    22 |     1   (0)| 00:00:01 |
|* 52 |                            INDEX UNIQUE SCAN                 | S_POSTN_U2        |     1 |       |     1   (0)| 00:00:01 |
|  53 |                          TABLE ACCESS BY INDEX ROWID         | S_ORG_EXT         |     1 |    25 |     1   (0)| 00:00:01 |
|* 54 |                           INDEX UNIQUE SCAN                  | S_ORG_EXT_U3      |     1 |       |     1   (0)| 00:00:01 |
|* 55 |                         TABLE ACCESS BY INDEX ROWID          | S_CON_ADDR        |     1 |    29 |     1   (0)| 00:00:01 |
|* 56 |                          INDEX RANGE SCAN                    | S_CON_ADDR_F1     |     2 |       |     1   (0)| 00:00:01 |
|  57 |                        TABLE ACCESS BY INDEX ROWID           | S_ADDR_PER        |     1 |    80 |     1   (0)| 00:00:01 |
|* 58 |                         INDEX UNIQUE SCAN                    | S_ADDR_PER_P1     |     1 |       |     1   (0)| 00:00:01 |
|  59 |                       TABLE ACCESS BY INDEX ROWID            | S_ADDR_PER        |     1 |    82 |     1   (0)| 00:00:01 |
|* 60 |                        INDEX UNIQUE SCAN                     | S_ADDR_PER_P1     |     1 |       |     1   (0)| 00:00:01 |
|  61 |                      TABLE ACCESS BY INDEX ROWID             | S_POSTN_CON       |     1 |    33 |     1   (0)| 00:00:01 |
|* 62 |                       INDEX RANGE SCAN                       | S_POSTN_CON_M3    |     1 |       |     1   (0)| 00:00:01 |
|  63 |                     TABLE ACCESS BY INDEX ROWID              | S_POSTN           |     1 |    22 |     1   (0)| 00:00:01 |
|* 64 |                      INDEX UNIQUE SCAN                       | S_POSTN_U2        |     1 |       |     1   (0)| 00:00:01 |
|* 65 |                    TABLE ACCESS BY INDEX ROWID               | S_PARTY           |     1 |    69 |     1   (0)| 00:00:01 |
|* 66 |                     INDEX UNIQUE SCAN                        | S_PARTY_P1        |     1 |       |     1   (0)| 00:00:01 |
|* 67 |                   INDEX RANGE SCAN                           | S_DQ_CON_KEY_U1   |     1 |   394 |     1   (0)| 00:00:01 |
|  68 |                  TABLE ACCESS BY INDEX ROWID                 | S_CONTACT_SS      |     1 |   425 |     1   (0)| 00:00:01 |
|* 69 |                   INDEX RANGE SCAN                           | S_CONTACT_SS_U1   |     1 |       |     1   (0)| 00:00:01 |
|  70 |                 TABLE ACCESS FULL                            | S_CASE            |     1 |    64 |     2   (0)| 00:00:01 |
|  71 |                TABLE ACCESS BY INDEX ROWID                   | S_CONTACT_LOYX    |     1 |    75 |     1   (0)| 00:00:01 |
|* 72 |                 INDEX RANGE SCAN                             | S_CONTACT_LOYX_U1 |     1 |       |     1   (0)| 00:00:01 |
|  73 |               TABLE ACCESS BY INDEX ROWID                    | S_POSTN_CON       |     1 |    25 |     1   (0)| 00:00:01 |
|* 74 |                INDEX RANGE SCAN                              | S_POSTN_CON_M3    |     1 |       |     1   (0)| 00:00:01 |
|  75 |              TABLE ACCESS BY INDEX ROWID                     | S_EMP_PER         |     1 |    63 |     1   (0)| 00:00:01 |
|* 76 |               INDEX UNIQUE SCAN                              | S_EMP_PER_U1      |     1 |       |     1   (0)| 00:00:01 |
|  77 |             TABLE ACCESS BY INDEX ROWID                      | S_CONTACT_X       |     1 |    63 |     1   (0)| 00:00:01 |
|* 78 |              INDEX RANGE SCAN                                | S_CONTACT_X_U1    |     1 |       |     1   (0)| 00:00:01 |
|* 79 |            INDEX UNIQUE SCAN                                 | S_PARTY_P1        |     1 |    11 |     1   (0)| 00:00:01 |
|* 80 |           INDEX UNIQUE SCAN                                  | S_PARTY_P1        |     1 |    11 |     1   (0)| 00:00:01 |
|* 81 |          INDEX UNIQUE SCAN                                   | S_PARTY_P1        |     1 |    11 |     1   (0)| 00:00:01 |
|  82 |         TABLE ACCESS BY INDEX ROWID                          | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 83 |          INDEX UNIQUE SCAN                                   | S_USER_U2         |     1 |       |     1   (0)| 00:00:01 |
|  84 |        TABLE ACCESS BY INDEX ROWID                           | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 85 |         INDEX UNIQUE SCAN                                    | S_USER_U2         |     1 |       |     1   (Oracleoracleplus.net0)| 00:00:01 |
|  86 |       TABLE ACCESS BY INDEX ROWID                            | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 87 |        INDEX UNIQUE SCAN                                     | S_USER_U2         |     1 |       |     1   (0)| 00:00:01 |
|  88 |      TABLE ACCESS BY INDEX ROWID                             | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 89 |       INDEX UNIQUE SCAN                                      | S_USER_U2         |     1 |       |     1   (0)| 00:00:01 |
|  90 |     TABLE ACCESS BY INDEX ROWID                              | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 91 |      INDEX UNIQUE SCAN                                       | S_USER_U2         |     1 |       |     1   (0)| 00:00:01 |
|  92 |    TABLE ACCESS BY INDEX ROWID                               | S_USER            |     1 |    20 |     1   (0)| 00:00:01 |
|* 93 |     INDEX UNIQUE SCAN                                        | S_USER_U2         |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

  15 - access("T5"."ROW_ID"="T22"."PR_SUBJECT_ID"(+))
  34 - access("T13"."ROW_ID"='1234')
  35 - FILTER("T33"."PRIV_FLG"='N')
  36 - access("T33"."CELL_PH_NUM"='13912345678')
  38 - access("T33"."CURR_PRI_LST_ID"="T15"."ROW_ID"(+))
  40 - access("T33"."MED_SPEC_ID"="T4"."ROW_ID"(+))
  41 - FILTER("T1"."CON_LAST_NAME">='M0808594' AND "T1"."CON_EMP_FLG"='N')
  42 - access("T33"."ROW_ID"="T1"."CONTACT_ID" AND "T1"."BU_ID"='1234')
  44 - access("T33"."PR_POSTN_ID"="T19"."PAR_ROW_ID"(+))
  46 - access("T33"."PR_DEPT_OU_ID"="T16"."PAR_ROW_ID"(+))
  48 - access("T33"."PR_DEPT_OU_ID"="T10"."PAR_ROW_ID"(+))
  50 - access("T33"."PR_DEPT_OU_ID"="T2"."PAR_ROW_ID"(+))
  52 - access("T2"."PR_POSTN_ID"="T30"."PAR_ROW_ID"(+))
  54 - access("T33"."BU_ID"="T32"."PAR_ROW_ID"(+))
  55 - FILTER("T33"."PR_PER_ADDR_ID"="T6"."ADDR_PER_ID"(+))
  56 - access("T33"."ROW_ID"="T6"."CONTACT_ID"(+))
       FILTER("T6"."CONTACT_ID"(+) IS NOT NULL)
  58 - access("T33"."PR_PER_ADDR_ID"="T31"."ROW_ID"(+))
  60 - access("T33"."PR_PER_ADDR_ID"="T11"."ROW_ID"(+))
  62 - access("T33"."PR_POSTN_ID"="T27"."POSTN_ID" AND "T33"."ROW_ID"="T27"."CON_ID")
  64 - access("T27"."POSTN_ID"="T12"."PAR_ROW_ID"(+))
  65 - FILTER("T5"."PARTY_TYPE_CD"<>'Suspect')
  66 - access("T5"."ROW_ID"="T33"."PAR_ROW_ID")
  67 - access("T5"."ROW_ID"="T17"."CONTACT_ID"(+))
  69 - access("T5"."ROW_ID"="T7"."PAR_ROW_ID"(+))
  72 - access("T5"."ROW_ID"="T9"."PAR_ROW_ID"(+))
  74 - access("T28"."POSTN_ID"(+)='1234' AND "T5"."ROW_ID"="T28"."CON_ID"(+))
  76 - access("T5"."ROW_ID"="T20"."PAR_ROW_ID"(+))
  78 - access("T5"."ROW_ID"="T18"."PAR_ROW_ID"(+))
  79 - access("T33"."PR_SYNC_USER_ID"="T14"."ROW_ID"(+))
  80 - access("T33"."PR_DEPT_OU_ID"="T3"."ROW_ID"(+))
  81 - access("T27"."POSTN_ID"="T25"."ROW_ID")
  83 - access("T33"."PR_SYNC_USER_ID"="T8"."PAR_ROW_ID"(+))
  85 - access("T19"."PR_EMP_ID"="T29"."PAR_ROW_ID"(+))
  87 - access("T19"."PR_EMP_ID"="T26"."PAR_ROW_ID"(+))
  89 - access("T19"."PR_EMP_ID"="T23"."PAR_ROW_ID"(+))
  91 - access("T30"."PR_EMP_ID"="T21"."PAR_ROW_ID"(+))
  93 - access("T12"."PR_EMP_ID"="T24"."PAR_ROW_ID"(+))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo SIZE
      12616  bytes sent via SQL*Net TO client
        509  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          0  ROWS processed
反映说这个sql非常慢,但看执行计划,老实说基本上都是正常的,最开始就发现有个全表scan的:

|  70 |   TABLE ACCESS FULL        | S_CASE            |     1 |    64 |     2   (0)| 00:00:01 |
问了下同事说这个表记录为0,问题不是关键。比较怪异的地方是:

sql 条件里面:

(T33.CELL_PH_NUM LIKE '13912345678')
而对于的执行计划是下面这样的:

36 - access("T33"."CELL_PH_NUM"='13912345678')

最开始没引起重视,确实没想到,还让同事做了10046和10053都没看出来。最后以前的技术总监看出来了,当然最后我是恍然大悟。

原因是 oracle 这里把 like 当成 = 去执行了。最后把参数 _like_with_bind_as_equality 调整为true后解决问题

补充:这个参数在10g,11g中默认都为false。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle数据库SQL优化之 SQL Tuning like当=执行

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1342.html

Oracle研究中心

关键词:

Oracle like_with_bind_as_equality参数影响

Oracle 一个SQL Tuning例子