Home » RDBMS Server » Performance Tuning » Query taking different time in tqo schemas (Oracle 10g)
| Query taking different time in tqo schemas [message #343047] |
Tue, 26 August 2008 01:34  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I have two schemas on the same server where I am running the same query but it is taking different time to execute the same query in two schemas.
SELECT /*+ no_query_transformation */
basedata.*
FROM (SELECT (SELECT SUBSTR (vrygbs, 3, 1)
FROM vwsrygbs v1
WHERE v1.vrygbs =
(SELECT MIN (v2.vrygbs)
FROM vwsrygbs v2
WHERE v1.vwkor_id_pk = v2.vwkor_id_pk)
AND ROWNUM = 1
AND owo.wkor_id_pk = v1.vwkor_id_pk) ryg,
owo.wkor_id_pk workorderid, owo.wkor_order_id_fk orderid,
vupdateddate currentstatustime,
vwostate.vstatunkey stateuniqueid,
owo.wkor_required_quantity orderquantity,
oli.oli_sku_number
|| '-'
|| oli.oli_skurevision_number skunumber,
oo.ord_deliverydatetime deliverydatetime,
oo.ord_deliverydatetime duedate, owsi.wsi_name workstepname,
asti.stai_state_name currentstatus,
asti.stai_state_name status,
ast.sta_state_id_pk currentstatusid,
owwm.wowsm_bom_id_fk bomid,
owo.wkor_parent_wo_id parentworkorderid,
owo.wkor_master_wo_id masterworkorderid,
owo.wkor_status_code workorderstatusid,
ows.wrs_id_pk workstepid, ar.role_id_pk roleid
FROM ord_orders oo,
vworkorder_states vwostate,
ord_wkor_ws_mapping owwm,
ord_work_steps ows,
ord_lineitems oli,
ord_work_steps_in owsi,
adm_states ast,
adm_states_in asti,
ord_work_order owo,
adm_roles ar
WHERE wkor_order_id_fk = oo.ord_id_pk
AND vwostate.vwoid = owo.wkor_id_pk
AND vwostate.vupdateddate =
(SELECT MAX (vwostate1.vupdateddate)
FROM vworkorder_states vwostate1
WHERE vwostate1.vwoid = owo.wkor_id_pk
AND vwostate1.voprid = ows.wrs_operation_type_id_fk
AND vwostate1.vwsid = ows.wrs_id_pk)
AND vwostate.voprid = ows.wrs_operation_type_id_fk
AND owwm.wowsm_wo_id = owo.wkor_id_pk
AND owwm.wowsm_ws_id = ows.wrs_id_pk
AND oli.oli_order_id_fk = owo.wkor_order_id_fk
AND wkor_ord_lineitem_id_fk = oli_id_pk
AND owsi.wsi_id_fk = ows.wrs_id_pk
AND ast.sta_state_id_pk = asti.stai_state_id_fk
AND vwostate.vstatunkey = ast.sta_state_key_un
AND oo.ord_completed_date IS NULL
AND NVL (oo.ord_type, 0) != 'D'
AND NVL (oli.oli_type, 0) != 'P'
AND NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
AND owsi.wsi_language_id_fk = 1
AND owsi.wsi_language_id_fk = asti.stai_language_id_fk
AND owwm.wowsm_ws_id = vwostate.vwsid
AND ows.wrs_operation_type_id_fk = ar.role_opr_type_id_fk
AND ar.role_id_pk = 19
AND owo.wkor_status_code != 129
AND ast.sta_state_key_un NOT IN (
SELECT v_sta_state_key_un
FROM vwstatesstartcomplete vsc
WHERE vsc.v_start_complete = 0
AND vsc.v_sta_operation_type_id_fk =
ows.wrs_operation_type_id_fk)) basedata,
adm_states as1,
adm_states as2
WHERE as1.sta_state_id_pk = basedata.currentstatusid
AND as2.sta_state_id_pk = basedata.workorderstatusid
AND ( ( (ryg = 'B' OR ryg = 'S')
AND (as1.sta_operation_type_id_fk = as2.sta_operation_type_id_fk
)
)
OR (ryg = 'R' OR ryg = 'Y' OR ryg = 'G')
)
AND basedata.ryg != 'S'
AND basedata.ryg != 'B'
ORDER BY duedate, basedata.orderid ASC, basedata.workorderid ASC
The explain plan is also different in the two schemas. Though they are big but I have pasted them here.
The explan Plan in Schema 1 where it is running in some milliseconds :
Plan
SELECT STATEMENT ALL_ROWSCost: 262 Bytes: 304 Cardinality: 1
105 SORT ORDER BY Cost: 262 Bytes: 304 Cardinality: 1
104 NESTED LOOPS Cost: 261 Bytes: 304 Cardinality: 1
101 NESTED LOOPS Cost: 260 Bytes: 297 Cardinality: 1
98 VIEW OCEBACKUP. Cost: 259 Bytes: 290 Cardinality: 1
97 NESTED LOOPS Cost: 155 Bytes: 189 Cardinality: 1
94 NESTED LOOPS Cost: 154 Bytes: 165 Cardinality: 1
91 NESTED LOOPS Cost: 153 Bytes: 142 Cardinality: 1
83 NESTED LOOPS Cost: 152 Bytes: 134 Cardinality: 1
80 NESTED LOOPS Cost: 151 Bytes: 119 Cardinality: 1
77 NESTED LOOPS Cost: 150 Bytes: 95 Cardinality: 1
20 HASH JOIN Cost: 149 Bytes: 67 Cardinality: 1
18 HASH JOIN Cost: 142 Bytes: 165 Cardinality: 3
4 NESTED LOOPS Cost: 4 Bytes: 195 Cardinality: 15
2 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_ROLES Cost: 1 Bytes: 6 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086785 Cost: 0 Cardinality: 1
3 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 3 Bytes: 105 Cardinality: 15
17 VIEW VIEW OCEBACKUP.VWORKORDER_STATES Cost: 138 Bytes: 4,830 Cardinality: 115
16 HASH JOIN Cost: 138 Bytes: 6,785 Cardinality: 115
14 VIEW SYS. Cost: 134 Bytes: 5,980 Cardinality: 115
13 NESTED LOOPS OUTER Cost: 134 Bytes: 5,980 Cardinality: 115
9 VIEW OCEBACKUP. Cost: 19 Bytes: 5,520 Cardinality: 115
8 SORT UNIQUE Cost: 19 Bytes: 5,240 Cardinality: 115
7 UNION-ALL
5 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WO_STATES_HISTORY Cost: 9 Bytes: 1,184 Cardinality: 37
6 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 8 Bytes: 4,056 Cardinality: 78
12 VIEW SYS. Cost: 1 Bytes: 4 Cardinality: 1
11 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1 Bytes: 8 Cardinality: 1
10 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0 Cardinality: 1
15 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 3 Bytes: 2,296 Cardinality: 328
19 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6 Bytes: 2,340 Cardinality: 195
76 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 1 Bytes: 28 Cardinality: 1
75 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 0 Cardinality: 1
35 SORT AGGREGATE Bytes: 48 Cardinality: 1
34 VIEW VIEW OCEBACKUP.VWORKORDER_STATES Cost: 14 Bytes: 48 Cardinality: 1
33 NESTED LOOPS Cost: 14 Bytes: 55 Cardinality: 1
22 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 1 Bytes: 7 Cardinality: 1
21 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0087144 Cost: 0 Cardinality: 1
32 VIEW SYS. Cost: 13 Bytes: 48 Cardinality: 1
31 NESTED LOOPS OUTER Cost: 13 Bytes: 96 Cardinality: 2
28 VIEW OCEBACKUP. Cost: 13 Bytes: 96 Cardinality: 2
27 SORT UNIQUE Cost: 13 Bytes: 84 Cardinality: 2
26 UNION-ALL
23 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WO_STATES_HISTORY Cost: 9 Bytes: 32 Cardinality: 1
25 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2 Bytes: 52 Cardinality: 1
24 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
30 VIEW SYS. Cost: 0 Cardinality: 1
29 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0 Bytes: 4 Cardinality: 1
74 COUNT STOPKEY
73 FILTER
53 VIEW VIEW OCEBACKUP.VWSRYGBS Cost: 22 Bytes: 81 Cardinality: 9
52 SORT ORDER BY Cost: 22 Bytes: 918 Cardinality: 9
51 HASH JOIN Cost: 21 Bytes: 918 Cardinality: 9
49 HASH JOIN Cost: 15 Bytes: 855 Cardinality: 9
39 MERGE JOIN CARTESIAN Cost: 9 Bytes: 54 Cardinality: 3
36 TABLE ACCESS FULL TABLE OCEBACKUP.ADM_SITE_CONFIGURATION Cost: 3 Bytes: 6 Cardinality: 1
38 BUFFER SORT Cost: 6 Bytes: 36 Cardinality: 3
37 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6 Bytes: 36 Cardinality: 3
48 VIEW SYS. Cost: 5 Bytes: 847 Cardinality: 11
47 NESTED LOOPS OUTER Cost: 5 Bytes: 308 Cardinality: 11
41 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2 Bytes: 15 Cardinality: 1
40 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
46 VIEW SYS. Cost: 3 Bytes: 143 Cardinality: 11
45 VIEW VIEW OCEBACKUP.index$_join$_028 Cost: 3 Bytes: 88 Cardinality: 11
44 HASH JOIN
42 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 1 Bytes: 88 Cardinality: 11
43 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 1 Bytes: 88 Cardinality: 11
50 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_ORDERS Cost: 6 Bytes: 91 Cardinality: 13
72 SORT AGGREGATE Bytes: 18 Cardinality: 1
71 VIEW VIEW OCEBACKUP.VWSRYGBS Cost: 22 Bytes: 162 Cardinality: 9
70 SORT ORDER BY Cost: 22 Bytes: 999 Cardinality: 9
69 HASH JOIN Cost: 21 Bytes: 999 Cardinality: 9
67 HASH JOIN Cost: 15 Bytes: 936 Cardinality: 9
57 MERGE JOIN CARTESIAN Cost: 9 Bytes: 54 Cardinality: 3
54 TABLE ACCESS FULL TABLE OCEBACKUP.ADM_SITE_CONFIGURATION Cost: 3 Bytes: 6 Cardinality: 1
56 BUFFER SORT Cost: 6 Bytes: 36 Cardinality: 3
55 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6 Bytes: 36 Cardinality: 3
66 VIEW SYS. Cost: 5 Bytes: 946 Cardinality: 11
65 NESTED LOOPS OUTER Cost: 5 Bytes: 308 Cardinality: 11
59 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2 Bytes: 15 Cardinality: 1
58 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
64 VIEW SYS. Cost: 3 Bytes: 143 Cardinality: 11
63 VIEW VIEW OCEBACKUP.index$_join$_035 Cost: 3 Bytes: 88 Cardinality: 11
62 HASH JOIN
60 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 1 Bytes: 88 Cardinality: 11
61 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 1 Bytes: 88 Cardinality: 11
68 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_ORDERS Cost: 6 Bytes: 91 Cardinality: 13
79 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_LINEITEMS Cost: 1 Bytes: 24 Cardinality: 1
78 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0087087 Cost: 0 Cardinality: 1
82 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_ORDERS Cost: 1 Bytes: 15 Cardinality: 1
81 INDEX RANGE SCAN INDEX OCEBACKUP.ORDER_ID Cost: 0 Cardinality: 1
90 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1 Bytes: 8 Cardinality: 1
89 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 0 Cardinality: 1
88 VIEW VIEW OCEBACKUP.VWSTATESSTARTCOMPLETE Cost: 2 Bytes: 29 Cardinality: 1
87 NESTED LOOPS Cost: 2 Bytes: 24 Cardinality: 1
85 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1 Bytes: 20 Cardinality: 1
84 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 0 Cardinality: 1
86 INDEX RANGE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086829 Cost: 1 Bytes: 4 Cardinality: 1
93 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES_IN Cost: 1 Bytes: 23 Cardinality: 1
92 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086829 Cost: 0 Cardinality: 1
96 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_STEPS_IN Cost: 1 Bytes: 24 Cardinality: 1
95 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_STEPS_IN_PK Cost: 0 Cardinality: 1
100 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1 Bytes: 7 Cardinality: 1
99 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0 Cardinality: 1
103 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1 Bytes: 7 Cardinality: 1
102 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0 Cardinality: 1
In schema 2 where its taking more than 8 seconds:
Plan
SELECT STATEMENT ALL_ROWSCost: 3,604 Bytes: 313 Cardinality: 1
108 SORT ORDER BY Cost: 3,604 Bytes: 313 Cardinality: 1
107 NESTED LOOPS Cost: 3,603 Bytes: 313 Cardinality: 1
104 NESTED LOOPS Cost: 3,602 Bytes: 306 Cardinality: 1
101 VIEW OCE211. Cost: 3,601 Bytes: 299 Cardinality: 1
100 FILTER
36 NESTED LOOPS Cost: 3,538 Bytes: 189 Cardinality: 1
33 NESTED LOOPS Cost: 3,537 Bytes: 165 Cardinality: 1
30 NESTED LOOPS Cost: 3,536 Bytes: 141 Cardinality: 1
27 HASH JOIN Cost: 3,535 Bytes: 125 Cardinality: 1
25 HASH JOIN Cost: 3,526 Bytes: 2,646 Cardinality: 27
23 HASH JOIN Cost: 3,519 Bytes: 6,192 Cardinality: 72
9 HASH JOIN Cost: 9 Bytes: 53,944 Cardinality: 1,226
7 NESTED LOOPS Cost: 5 Bytes: 3,034 Cardinality: 82
4 NESTED LOOPS Cost: 4 Bytes: 2,378 Cardinality: 82
2 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_ROLES Cost: 1 Bytes: 6 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105095 Cost: 0 Cardinality: 1
3 TABLE ACCESS FULL TABLE OCE211.ADM_STATES_IN Cost: 3 Bytes: 1,886 Cardinality: 82
6 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1 Bytes: 8 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0 Cardinality: 1
8 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_STEPS Cost: 3 Bytes: 2,527 Cardinality: 361
22 VIEW VIEW OCE211.VWORKORDER_STATES Cost: 3,510 Bytes: 146,454 Cardinality: 3,487
21 HASH JOIN Cost: 3,510 Bytes: 205,733 Cardinality: 3,487
10 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_STEPS Cost: 3 Bytes: 2,527 Cardinality: 361
20 VIEW SYS. Cost: 3,507 Bytes: 181,324 Cardinality: 3,487
19 NESTED LOOPS OUTER Cost: 3,507 Bytes: 181,324 Cardinality: 3,487
15 VIEW OCE211. Cost: 16 Bytes: 167,376 Cardinality: 3,487
14 SORT UNIQUE Cost: 16 Bytes: 131,141 Cardinality: 3,487
13 UNION-ALL
11 TABLE ACCESS FULL TABLE OCE211.ORD_WO_STATES_HISTORY Cost: 5 Bytes: 62,040 Cardinality: 1,880
12 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_ORDER Cost: 8 Bytes: 69,101 Cardinality: 1,607
18 VIEW SYS. Cost: 1 Bytes: 4 Cardinality: 1
17 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1 Bytes: 8 Cardinality: 1
16 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0 Cardinality: 1
24 TABLE ACCESS FULL TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 6 Bytes: 46,860 Cardinality: 3,905
26 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_ORDER Cost: 8 Bytes: 42,498 Cardinality: 1,574
29 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1 Bytes: 16 Cardinality: 1
28 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0 Cardinality: 1
32 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_STEPS_IN Cost: 1 Bytes: 24 Cardinality: 1
31 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_STEPS_IN_PK Cost: 0 Cardinality: 1
35 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_LINEITEMS Cost: 1 Bytes: 24 Cardinality: 1
34 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105402 Cost: 0 Cardinality: 1
57 SORT AGGREGATE Bytes: 48 Cardinality: 1
56 VIEW VIEW OCE211.VWORKORDER_STATES Cost: 7 Bytes: 48 Cardinality: 1
55 NESTED LOOPS Cost: 7 Bytes: 55 Cardinality: 1
38 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_STEPS Cost: 1 Bytes: 7 Cardinality: 1
37 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105464 Cost: 0 Cardinality: 1
54 VIEW SYS. Cost: 6 Bytes: 48 Cardinality: 1
53 NESTED LOOPS OUTER Cost: 6 Bytes: 96 Cardinality: 2
50 VIEW OCE211. Cost: 6 Bytes: 96 Cardinality: 2
49 SORT UNIQUE Cost: 6 Bytes: 76 Cardinality: 2
48 UNION-ALL
45 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WO_STATES_HISTORY Cost: 2 Bytes: 33 Cardinality: 1
44 BITMAP CONVERSION TO ROWIDS
43 BITMAP AND
40 BITMAP CONVERSION FROM ROWIDS
39 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WO_ST_HIST_WO_ID_FK Cost: 1 Cardinality: 6
42 BITMAP CONVERSION FROM ROWIDS
41 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WO_ST_HIST_WRS_ID_FK Cost: 1 Cardinality: 6
47 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2 Bytes: 43 Cardinality: 1
46 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
52 VIEW SYS. Cost: 0 Cardinality: 1
51 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0 Bytes: 4 Cardinality: 1
62 VIEW VIEW OCE211.VWSTATESSTARTCOMPLETE Cost: 2 Bytes: 29 Cardinality: 1
61 NESTED LOOPS Cost: 2 Bytes: 24 Cardinality: 1
59 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1 Bytes: 20 Cardinality: 1
58 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.CONS_STA_STATE_KEY_UN Cost: 0 Cardinality: 1
60 INDEX RANGE SCAN INDEX (UNIQUE) OCE211.SYS_C00105140 Cost: 1 Bytes: 4 Cardinality: 1
99 COUNT STOPKEY
98 FILTER
79 VIEW VIEW OCE211.VWSRYGBS Cost: 13 Bytes: 18 Cardinality: 2
78 SORT ORDER BY Cost: 13 Bytes: 206 Cardinality: 2
77 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1 Bytes: 8 Cardinality: 1
76 NESTED LOOPS Cost: 12 Bytes: 206 Cardinality: 2
74 HASH JOIN Cost: 11 Bytes: 190 Cardinality: 2
67 MERGE JOIN CARTESIAN Cost: 5 Bytes: 36 Cardinality: 2
63 TABLE ACCESS FULL TABLE OCE211.ADM_SITE_CONFIGURATION Cost: 3 Bytes: 6 Cardinality: 1
66 BUFFER SORT Cost: 2 Bytes: 24 Cardinality: 2
65 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 2 Bytes: 24 Cardinality: 2
64 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WKOR_WS_MAPPING_WO_ID Cost: 1 Cardinality: 3
73 VIEW SYS. Cost: 5 Bytes: 231 Cardinality: 3
72 NESTED LOOPS OUTER Cost: 5 Bytes: 84 Cardinality: 3
69 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2 Bytes: 15 Cardinality: 1
68 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
71 VIEW SYS. Cost: 3 Bytes: 39 Cardinality: 3
70 TABLE ACCESS FULL TABLE OCE211.ADM_STATES Cost: 3 Bytes: 24 Cardinality: 3
75 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0 Cardinality: 1
97 SORT AGGREGATE Bytes: 18 Cardinality: 1
96 VIEW VIEW OCE211.VWSRYGBS Cost: 13 Bytes: 36 Cardinality: 2
95 SORT ORDER BY Cost: 13 Bytes: 224 Cardinality: 2
94 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1 Bytes: 8 Cardinality: 1
93 NESTED LOOPS Cost: 12 Bytes: 224 Cardinality: 2
91 HASH JOIN Cost: 11 Bytes: 208 Cardinality: 2
84 MERGE JOIN CARTESIAN Cost: 5 Bytes: 36 Cardinality: 2
80 TABLE ACCESS FULL TABLE OCE211.ADM_SITE_CONFIGURATION Cost: 3 Bytes: 6 Cardinality: 1
83 BUFFER SORT Cost: 2 Bytes: 24 Cardinality: 2
82 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 2 Bytes: 24 Cardinality: 2
81 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WKOR_WS_MAPPING_WO_ID Cost: 1 Cardinality: 3
90 VIEW SYS. Cost: 5 Bytes: 258 Cardinality: 3
89 NESTED LOOPS OUTER Cost: 5 Bytes: 84 Cardinality: 3
86 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2 Bytes: 15 Cardinality: 1
85 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1 Cardinality: 1
88 VIEW SYS. Cost: 3 Bytes: 39 Cardinality: 3
87 TABLE ACCESS FULL TABLE OCE211.ADM_STATES Cost: 3 Bytes: 24 Cardinality: 3
92 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0 Cardinality: 1
103 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1 Bytes: 7 Cardinality: 1
102 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0 Cardinality: 1
106 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1 Bytes: 7 Cardinality: 1
105 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0 Cardinality: 1
I see that the Cardinality is different (more in schema 2 where its taking longer)
Please advice what could be the obvious reason behind this.
Thanks for looking into this.
Mahi
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 14:55:28 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
