一个SQL Tuning例子

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

以前同事的问题，非常隐蔽的问题，一直没有发现，跟大家分析！

1	以前同事的问题，非常隐蔽的问题，一直没有发现，跟大家分析！

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   (0)| 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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

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

----------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------

| 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 (0)| 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的：

1	反映说这个sql非常慢，但看执行计划，老实说基本上都是正常的，最开始就发现有个全表scan的：

|  70 |   TABLE ACCESS FULL        | S_CASE            |     1 |    64 |     2   (0)| 00:00:01 |

1	\| 70 \| TABLE ACCESS FULL \| S_CASE \| 1 \| 64 \| 2 (0)\| 00:00:01 \|

问了下同事说这个表记录为0，问题不是关键。比较怪异的地方是：
sql 条件里面：

问了下同事说这个表记录为0，问题不是关键。比较怪异的地方是：

sql 条件里面：

(T33.CELL_PH_NUM LIKE '13912345678')

1	(T33.CELL_PH_NUM LIKE '13912345678')

而对于的执行计划是下面这样的：

1	而对于的执行计划是下面这样的：

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

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

最开始没引起重视，确实没想到，还让同事做了10046和10053都没看出来。最后以前的技术总监看出来了，
当然最后我是恍然大悟。
原因是 oracle 这里把 like 当成 = 去执行了。最后把参数<span style="color: #0000ff;"> _like_with_bind_as_equality </span>调整为true后解决问题。
补充：这个参数在10g，11g中默认都为false。

最开始没引起重视，确实没想到，还让同事做了10046和10053都没看出来。最后以前的技术总监看出来了，

当然最后我是恍然大悟。

原因是 oracle 这里把 like 当成 = 去执行了。最后把参数<span style="color: #0000ff;"> _like_with_bind_as_equality </span>调整为true后解决问题。

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

Tags: _like_with_bind_as_equality
Posted in Oracle rdbms, tunning on 2月 25, 2012

8 Responses to “一个SQL Tuning例子”

路人 Says:

2月 26, 2012 at 下午 2:33

没弄明白，能不能解释一下，以前没见过这个案例，谢谢。

roger Says:

2月 27, 2012 at 上午 2:17

to 路人:

看下这个就明白了：

Parameter: _LIKE_WITH_BIND_AS_EQUALITY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Introduced:      8.1.6
Range of values: TRUE | FALSE
Related:
Description:
~~~~~~~~~~~~
Treat LIKE predicate with bind as an equality predicate for costing 
purposes.
This parameter forces the optimizer to treat expressions of the form
[indexed-column like :b1] similar to [index-column = :b1].
Normally CBO assigns internal default selectivity estimates for the
LIKE operator but this is nowhere near the true selectivity and can
cause an index access to be rejected.
NOTE:  Default selectivity has changed from earlier releases
Release           Selectivity
< 9.2.x               25%
>= 9.2.x               5%
As of 8.1.7.2 and Oracle9i this parameter also enabled equality 
costing for expressions of the form:
function(column) LIKE function(:bind)

Parameter: _LIKE_WITH_BIND_AS_EQUALITY

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Introduced: 8.1.6

Range of values: TRUE | FALSE

Description:

~~~~~~~~~~~~

Treat LIKE predicate with bind as an equality predicate for costing

purposes.

This parameter forces the optimizer to treat expressions of the form

[indexed-column like :b1] similar to [index-column = :b1].

Normally CBO assigns internal default selectivity estimates for the

LIKE operator but this is nowhere near the true selectivity and can

cause an index access to be rejected.

NOTE: Default selectivity has changed from earlier releases

Release Selectivity

< 9.2.x 25%

>= 9.2.x 5%

As of 8.1.7.2 and Oracle9i this parameter also enabled equality

costing for expressions of the form:

function(column) LIKE function(:bind)

francis Says:

2月 27, 2012 at 下午 3:48

真旭，这个问题还真是麻烦你了啊，我向你表示歉意啊
roger Says:

2月 28, 2012 at 上午 1:42

to francis: 没事，都是同事嘛！
jump2009 Says:

2月 28, 2012 at 上午 9:21

为什么写成CELL_PH_NUM like ‘13912345678’,一般直接写成CELL_PH_NUM = ‘13912345678’ 要like后面一般都加’%’
roger Says:

2月 28, 2012 at 下午 2:58

to jump2009:

这个是应用里面的sql，写法是固定的，程序产生的语句！
Stuart Arehart Says:

3月 16, 2012 at 下午 11:50

Always glad you just read a unique website. Thanks for the feedback. The design of your blog looks really beautiful. Cheers.
bunny harness Says:

3月 17, 2012 at 上午 3:17

Hi…

[…]Every once in a even though we pick blogs that we study. Listed beneath would be the most up-to-date websites that we decide on […]…

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

一个SQL Tuning例子

8 Responses to “一个SQL Tuning例子”

Leave a Reply