Home » RDBMS Server » Performance Tuning » Query taking very long time (Oracle 10g)
| Query taking very long time [message #357897] |
Fri, 07 November 2008 01:32  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I have a query which was taking only 3 seconds when the number of records were 3000. Now the number of records are 60000 and they may increase more. And now the query is taking 38 minutes to run 
Please advice on the query pasted below.
SQL> explain plan for
2 SELECT /*+ FIRST_ROWS */
3 basedata.*
4 FROM (SELECT (SELECT SUBSTR (vrygbs, 3, 1)
5 FROM vwsrygbs v1
6 WHERE v1.vrygbs =
7 (SELECT MIN (v2.vrygbs)
8 FROM vwsrygbs v2
9 WHERE v1.vwkor_id_pk = v2.vwkor_id_pk)
10 AND ROWNUM = 1
11 AND owo.wkor_id_pk = v1.vwkor_id_pk) ryg,
12 owo.wkor_id_pk workorderid, owo.wkor_order_id_fk orderid,
13 vupdateddate currentstatustime,
14 vwostate.vstatunkey stateuniqueid,
15 owo.wkor_required_quantity orderquantity,
16 NVL (owo.wkor_hold_ctrl, 0) holdenabled,
17 NVL (owo.wkor_hold_type, 0) holdtype,
18 oli.oli_sku_number
19 || '-'
20 || oli.oli_skurevision_number skunumber,
21 oo.ord_deliverydatetime deliverydatetime,
22 oo.ord_deliverydatetime duedate, owsi.wsi_name workstepname,
23 asti.stai_state_name currentstatus,
24 asti.stai_state_name status,
25 ast.sta_state_id_pk currentstatusid,
26 owwm.wowsm_bom_id_fk bomid,
27 owo.wkor_parent_wo_id parentworkorderid,
28 owo.wkor_master_wo_id masterworkorderid,
29 owo.wkor_status_code workorderstatusid,
30 ows.wrs_id_pk workstepid, ar.role_id_pk roleid
31 FROM adm_roles ar,
32 adm_states ast,
33 adm_states_in asti,
34 ord_work_steps_in owsi,
35 ord_work_steps ows,
36 ord_orders oo,
37 ord_lineitems oli,
38 ord_work_order owo,
39 vworkorder_states vwostate,
40 ord_wkor_ws_mapping owwm
41 WHERE wkor_order_id_fk = oo.ord_id_pk
42 AND vwostate.vwoid = owo.wkor_id_pk
43 AND vwostate.vupdateddate =
44 (SELECT MAX (vwostate1.vupdateddate)
45 FROM vworkorder_states vwostate1
46 WHERE vwostate1.vwoid = owo.wkor_id_pk
47 AND vwostate1.voprid = ows.wrs_operation_type_id_fk
48 AND vwostate1.vwsid = ows.wrs_id_pk)
49 AND oo.ord_completed_date IS NULL
50 AND NVL (oo.ord_type, 0) != 'D'
51 AND NVL (oli.oli_type, 0) != 'P'
52 AND NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
53 AND owsi.wsi_language_id_fk = 1
54 AND ar.role_id_pk = 19
55 AND owo.wkor_status_code != 129
56 AND vwostate.voprid = ows.wrs_operation_type_id_fk
57 AND owwm.wowsm_wo_id = owo.wkor_id_pk
58 AND owwm.wowsm_ws_id = ows.wrs_id_pk
59 AND oli.oli_order_id_fk = owo.wkor_order_id_fk
60 AND wkor_ord_lineitem_id_fk = oli_id_pk
61 AND owsi.wsi_id_fk = ows.wrs_id_pk
62 AND ast.sta_state_id_pk = asti.stai_state_id_fk
63 AND vwostate.vstatunkey = ast.sta_state_key_un
64 AND owsi.wsi_language_id_fk = asti.stai_language_id_fk
65 AND owwm.wowsm_ws_id = vwostate.vwsid
66 AND ows.wrs_operation_type_id_fk = ar.role_opr_type_id_fk
67 AND ast.sta_state_key_un NOT IN (
68 SELECT v_sta_state_key_un
69 FROM vwstatesstartcomplete vsc
70 WHERE vsc.v_start_complete = 0
71 AND vsc.v_sta_operation_type_id_fk =
72 ows.wrs_operation_type_id_fk)) basedata,
73 adm_states as1,
74 adm_states as2
75 WHERE as1.sta_state_id_pk = basedata.currentstatusid
76 AND as2.sta_state_id_pk = basedata.workorderstatusid
77 AND ( ( (ryg = 'B' OR ryg = 'S')
78 AND (as1.sta_operation_type_id_fk = as2.sta_operation_type_id_fk
79 )
80 )
81 OR (ryg = 'R' OR ryg = 'Y' OR ryg = 'G')
82 )
83 AND basedata.ryg != 'S'
84 --and baseData.ryg != 'B'
85 ORDER BY duedate, basedata.orderid ASC, basedata.workorderid ASC;
Explained.
SQL> set pagesize 200
SQL> set linesize 200
SQL>
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1941250565
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 342 | | 986 (12)| 00:00:12 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 1 | 28 | | 25 (12)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 1 | 18 | | 12 (9)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 50 | | 12 (9)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 3 | 150 | | 12 (9)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 42 | | 8 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 36 | | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 23 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_ORDER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | ORD_WORK_ORDER_PK | 1 | | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | ORD_ORDERS | 15660 | 137K| | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ORDER_ID | 1 | | | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | ORD_WKOR_WS_MAPPING | 1 | 13 | | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_ORD_WKOR_WS_MAPPING_WO_ID | 1 | | | 1 (0)| 00:00:01 |
| 15 | BUFFER SORT | | 1 | 6 | | 6 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | ADM_SITE_CONFIGURATION | 1 | 6 | | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | | 3 (0)| 00:00:01 |
| 18 | VIEW | VWSRYGBS | 3 | 30 | | 13 (16)| 00:00:01 |
| 19 | SORT ORDER BY | | 3 | 162 | | 13 (16)| 00:00:01 |
|* 20 | HASH JOIN OUTER | | 3 | 162 | | 12 (9)| 00:00:01 |
| 21 | MERGE JOIN CARTESIAN | | 1 | 46 | | 8 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 40 | | 5 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 27 | | 3 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_ORDER | 1 | 18 | | 2 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | ORD_WORK_ORDER_PK | 1 | | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | ORD_ORDERS | 15660 | 137K| | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | ORDER_ID | 1 | | | 0 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | ORD_WKOR_WS_MAPPING | 1 | 13 | | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_ORD_WKOR_WS_MAPPING_WO_ID | 1 | | | 1 (0)| 00:00:01 |
| 30 | BUFFER SORT | | 1 | 6 | | 6 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | ADM_SITE_CONFIGURATION | 1 | 6 | | 3 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | | 3 (0)| 00:00:01 |
| 33 | SORT ORDER BY | | 1 | 342 | | 986 (12)| 00:00:12 |
|* 34 | FILTER | | | | | | |
|* 35 | HASH JOIN ANTI | | 1 | 342 | | 961 (12)| 00:00:12 |
| 36 | NESTED LOOPS | | 1 | 335 | | 958 (11)| 00:00:12 |
| 37 | NESTED LOOPS | | 37 | 10730 | | 735 (5)| 00:00:09 |
| 38 | NESTED LOOPS | | 37 | 9990 | | 734 (5)| 00:00:09 |
| 39 | NESTED LOOPS | | 37 | 9731 | | 733 (5)| 00:00:09 |
|* 40 | HASH JOIN | | 37 | 9435 | | 731 (5)| 00:00:09 |
|* 41 | TABLE ACCESS BY INDEX ROWID | ORD_ORDERS | 1 | 17 | | 1 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 1 | 232 | | 728 (5)| 00:00:09 |
|* 43 | HASH JOIN | | 1 | 215 | | 727 (5)| 00:00:09 |
|* 44 | HASH JOIN | | 65 | 12350 | | 626 (4)| 00:00:08 |
| 45 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_STEPS | 122 | 2318 | | 1 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 122 | 20130 | | 583 (4)| 00:00:08 |
| 47 | NESTED LOOPS | | 1 | 146 | | 582 (4)| 00:00:07 |
|* 48 | HASH JOIN | | 1 | 122 | | 581 (4)| 00:00:07 |
|* 49 | HASH JOIN | | 26 | 2990 | | 578 (4)| 00:00:07 |
| 50 | VIEW | VW_SQ_2 | 26 | 1326 | | 471 (4)| 00:00:06 |
| 51 | HASH GROUP BY | | 26 | 1534 | | 471 (4)| 00:00:06 |
|* 52 | HASH JOIN RIGHT OUTER | | 35373 | 2038K| | 467 (3)| 00:00:06 |
| 53 | INDEX FULL SCAN | SYS_C00112613 | 88 | 352 | | 1 (0)| 00:00:01 |
|* 54 | HASH JOIN | | 35373 | 1899K| | 465 (3)| 00:00:06 |
| 55 | TABLE ACCESS FULL | ORD_WORK_STEPS | 384 | 2688 | | 3 (0)| 00:00:01 |
| 56 | VIEW | | 35373 | 1658K| | 461 (3)| 00:00:06 |
| 57 | SORT UNIQUE | | 35373 | 1444K| 4232K| 461 (99)| 00:00:06 |
| 58 | UNION-ALL | | | | | | |
| 59 | TABLE ACCESS FULL | ORD_WO_STATES_HISTORY | 3795 | 118K| | 6 (0)| 00:00:01 |
| 60 | TABLE ACCESS FULL | ORD_WORK_ORDER | 31578 | 1326K| | 100 (4)| 00:00:02 |
|* 61 | HASH JOIN | | 31518 | 1969K| | 106 (6)| 00:00:02 |
| 62 | NESTED LOOPS | | 87 | 1131 | | 4 (0)| 00:00:01 |
| 63 | TABLE ACCESS BY INDEX ROWID| ADM_ROLES | 1 | 6 | | 1 (0)| 00:00:01 |
|* 64 | INDEX UNIQUE SCAN | SYS_C00112571 | 1 | | | 0 (0)| 00:00:01 |
|* 65 | TABLE ACCESS FULL | ADM_STATES | 87 | 609 | | 3 (0)| 00:00:01 |
|* 66 | TABLE ACCESS FULL | ORD_WORK_ORDER | 31518 | 1569K| | 101 (5)| 00:00:02 |
| 67 | TABLE ACCESS FULL | ORD_WORK_STEPS | 384 | 2688 | | 3 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_STEPS_IN | 1 | 24 | | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | ORD_WORK_STEPS_IN_PK | 1 | | | 0 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | IDX_ORD_WRK_STPS_OP_TYPE_ID_FK | 15 | | | 0 (0)| 00:00:01 |
|* 71 | TABLE ACCESS FULL | ORD_LINEITEMS | 16890 | 412K| | 42 (3)| 00:00:01 |
| 72 | TABLE ACCESS FULL | ORD_WKOR_WS_MAPPING | 81533 | 1990K| | 99 (4)| 00:00:02 |
|* 73 | INDEX RANGE SCAN | ORDER_ID | 1 | | | 0 (0)| 00:00:01 |
|* 74 | TABLE ACCESS FULL | ADM_STATES_IN | 88 | 2024 | | 3 (0)| 00:00:01 |
| 75 | TABLE ACCESS BY INDEX ROWID | ADM_STATES | 1 | 8 | | 1 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | SYS_C00112613 | 1 | | | 0 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID | ADM_STATES | 1 | 7 | | 1 (0)| 00:00:01 |
|* 78 | INDEX UNIQUE SCAN | SYS_C00112613 | 1 | | | 0 (0)| 00:00:01 |
| 79 | TABLE ACCESS BY INDEX ROWID | ADM_STATES | 1 | 20 | | 1 (0)| 00:00:01 |
|* 80 | INDEX UNIQUE SCAN | CONS_STA_STATE_KEY_UN | 1 | | | 0 (0)| 00:00:01 |
| 81 | VIEW | | 1 | 45 | | 6 (34)| 00:00:01 |
| 82 | SORT UNIQUE | | 2 | 100 | | 6 (67)| 00:00:01 |
| 83 | UNION ALL PUSHED PREDICATE | | | | | | |
|* 84 | FILTER | | | | | | |
|* 85 | TABLE ACCESS BY INDEX ROWID | ORD_WO_STATES_HISTORY | 1 | 32 | | 2 (0)| 00:00:01 |
| 86 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 87 | BITMAP AND | | | | | | |
| 88 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 89 | INDEX RANGE SCAN | IDX_ORD_WO_ST_HIST_WO_ID_FK | 5 | | | 1 (0)| 00:00:01 |
| 90 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 91 | INDEX RANGE SCAN | IDX_ORD_WO_ST_HIST_WRS_ID_FK | 5 | | | 1 (0)| 00:00:01 |
|* 92 | FILTER | | | | | | |
|* 93 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_ORDER | 1 | 68 | | 2 (0)| 00:00:01 |
|* 94 | INDEX UNIQUE SCAN | ORD_WORK_ORDER_PK | 1 | | | 1 (0)| 00:00:01 |
| 95 | VIEW | VW_SQ_3 | 1 | 7 | | 2 (0)| 00:00:01 |
| 96 | NESTED LOOPS | | 1 | 24 | | 2 (0)| 00:00:01 |
| 97 | INDEX FULL SCAN | SYS_C00112616 | 88 | 352 | | 1 (0)| 00:00:01 |
|* 98 | TABLE ACCESS BY INDEX ROWID | ADM_STATES | 1 | 20 | | 1 (0)| 00:00:01 |
|* 99 | INDEX UNIQUE SCAN | SYS_C00112613 | 1 | | | 0 (0)| 00:00:01 |
|*100 | COUNT STOPKEY | | | | | | |
|*101 | HASH JOIN | | 1 | 28 | | 25 (12)| 00:00:01 |
| 102 | VIEW | VW_SQ_1 | 1 | 18 | | 12 (9)| 00:00:01 |
| 103 | SORT GROUP BY | | 1 | 50 | | 12 (9)| 00:00:01 |
|*104 | HASH JOIN OUTER | | 3 | 150 | | 12 (9)| 00:00:01 |
| 105 | MERGE JOIN CARTESIAN | | 1 | 42 | | 8 (0)| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 36 | | 5 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 23 | | 3 (0)| 00:00:01 |
| 108 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_ORDER | 1 | 14 | | 2 (0)| 00:00:01 |
|*109 | INDEX UNIQUE SCAN | ORD_WORK_ORDER_PK | 1 | | | 1 (0)| 00:00:01 |
|*110 | TABLE ACCESS BY INDEX ROWID | ORD_ORDERS | 15660 | 137K| | 1 (0)| 00:00:01 |
|*111 | INDEX RANGE SCAN | ORDER_ID | 1 | | | 0 (0)| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | ORD_WKOR_WS_MAPPING | 1 | 13 | | 2 (0)| 00:00:01 |
|*113 | INDEX RANGE SCAN | IDX_ORD_WKOR_WS_MAPPING_WO_ID | 1 | | | 1 (0)| 00:00:01 |
| 114 | BUFFER SORT | | 1 | 6 | | 6 (0)| 00:00:01 |
| 115 | TABLE ACCESS FULL | ADM_SITE_CONFIGURATION | 1 | 6 | | 3 (0)| 00:00:01 |
| 116 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | | 3 (0)| 00:00:01 |
| 117 | VIEW | VWSRYGBS | 3 | 30 | | 13 (16)| 00:00:01 |
| 118 | SORT ORDER BY | | 3 | 162 | | 13 (16)| 00:00:01 |
|*119 | HASH JOIN OUTER | | 3 | 162 | | 12 (9)| 00:00:01 |
| 120 | MERGE JOIN CARTESIAN | | 1 | 46 | | 8 (0)| 00:00:01 |
| 121 | NESTED LOOPS | | 1 | 40 | | 5 (0)| 00:00:01 |
| 122 | NESTED LOOPS | | 1 | 27 | | 3 (0)| 00:00:01 |
| 123 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_ORDER | 1 | 18 | | 2 (0)| 00:00:01 |
|*124 | INDEX UNIQUE SCAN | ORD_WORK_ORDER_PK | 1 | | | 1 (0)| 00:00:01 |
|*125 | TABLE ACCESS BY INDEX ROWID | ORD_ORDERS | 15660 | 137K| | 1 (0)| 00:00:01 |
|*126 | INDEX RANGE SCAN | ORDER_ID | 1 | | | 0 (0)| 00:00:01 |
|*127 | TABLE ACCESS BY INDEX ROWID | ORD_WKOR_WS_MAPPING | 1 | 13 | | 2 (0)| 00:00:01 |
|*128 | INDEX RANGE SCAN | IDX_ORD_WKOR_WS_MAPPING_WO_ID | 1 | | | 1 (0)| 00:00:01 |
| 129 | BUFFER SORT | | 1 | 6 | | 6 (0)| 00:00:01 |
| 130 | TABLE ACCESS FULL | ADM_SITE_CONFIGURATION | 1 | 6 | | 3 (0)| 00:00:01 |
| 131 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - access("V1"."VRYGBS"="VW_COL_1" AND "V1"."VWKOR_ID_PK"="WKOR_ID_PK")
5 - access("WO"."WKOR_STATUS_CODE"=TO_NUMBER(TO_CHAR("ASTS"."STA_STATE_ID_PK"(+))||''))
10 - access("WO"."WKOR_ID_PK"=:B1)
11 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
12 - access("OO"."ORD_ID_PK"="WO"."WKOR_ORDER_ID_FK")
13 - filter("WOMP"."WOWSM_MAX_START_TIME" IS NOT NULL)
14 - access("WOMP"."WOWSM_WO_ID"="WO"."WKOR_ID_PK")
filter("WOMP"."WOWSM_WO_ID"=:B1)
20 - access("WO"."WKOR_STATUS_CODE"=TO_NUMBER(TO_CHAR("ASTS"."STA_STATE_ID_PK"(+))||''))
25 - access("WO"."WKOR_ID_PK"=:B1)
26 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
27 - access("OO"."ORD_ID_PK"="WO"."WKOR_ORDER_ID_FK")
28 - filter("WOMP"."WOWSM_MAX_START_TIME" IS NOT NULL)
29 - access("WOMP"."WOWSM_WO_ID"="WO"."WKOR_ID_PK")
filter("WOMP"."WOWSM_WO_ID"=:B1)
34 - filter((( (SELECT /*+ */ SUBSTR("VRYGBS",3,1) FROM (SELECT /*+ */ CASE WHEN (NVL("ASTS"."STA_STATE_KEY_UN",0)=75 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=76 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=77 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=78 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=79 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=80 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=95 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=105 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=115 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=125 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=155) THEN '1#S' WHEN (NVL("ASTS"."STA_STATE_KEY_UN",0)=61 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=62 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=63 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=64 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=65 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=66 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=94 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=104 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=114 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=124 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=129 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=154) THEN '2#B' WHEN
"WOMP"."WOWSM_MAX_START_TIME"<SYSDATE@!+"ADMSC"."SCF_CONSTANTR"/24 THEN '3#R' WHEN
"WOMP"."WOWSM_MAX_START_TIME"<SYSDATE@!+"ADMSC"."SCF_CONSTANTY"/24 THEN '4#Y' ELSE '5#G' END "VRYGBS","WO"."WKOR_ID_PK"
"VWKOR_ID_PK","OO"."ORD_ID_PK" "VORD_ID_PK","WO"."WKOR_REQUIRED_QUANTITY" "WKOR_REQUIRED_QUANTITY" FROM OCEOCT."ORD_ORDERS"
"OO",OCEOCT."ADM_STATES" "ASTS",OCEOCT."ORD_WORK_ORDER" "WO",OCEOCT."ORD_WKOR_WS_MAPPING" "WOMP",OCEOCT."ADM_SITE_CONFIGURATION"
"ADMSC" WHERE "WOMP"."WOWSM_WO_ID"="WO"."WKOR_ID_PK" AND "WOMP"."WOWSM_MAX_START_TIME" IS NOT NULL AND "WOMP"."WOWSM_WO_ID"=:B1
AND "WO"."WKOR_ID_PK"=:B2 AND "WO"."WKOR_STATUS_CODE"=TO_NUMBER(TO_CHAR("ASTS"."STA_STATE_ID_PK"(+))||'') AND
"OO"."ORD_ID_PK"="WO"."WKOR_ORDER_ID_FK" AND NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL ORDER BY
"WO"."WKOR_ID_PK",CASE WHEN (NVL("ASTS"."STA_STATE_KEY_UN",0)=75 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=76 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=77 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=78 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=79 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=80 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=95 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=105 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=115 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=125 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=155) THEN '1#S'
WHEN (NVL("ASTS"."STA_STATE_KEY_UN",0)=61 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=62 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=63 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=64 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=65 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=66 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=94 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=104 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=114 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=124 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=129 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=154) THEN '2#B'
WHEN "WOMP"."WOWSM_MAX_START_TIME"<SYSDATE@!+"ADMSC"."SCF_CONSTANTR"/24 THEN '3#R' WHEN
"WOMP"."WOWSM_MAX_START_TIME"<SYSDATE@!+"ADMSC"."SCF_CONSTANTY"/24 THEN '4#Y' ELSE '5#G' END ) "V1", (SELECT /*+ */ MIN(CASE
WHEN (NVL("ASTS"."STA_STATE_KEY_UN",0)=75 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=76 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=77 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=78 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=79 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=80 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=95 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=105 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=115 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=125 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=155) THEN '1#S' WHEN
(NVL("ASTS"."STA_STATE_KEY_UN",0)=61 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=62 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=63 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=64 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=65 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=66 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=94 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=104 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=114 OR
NVL("ASTS"."STA_STATE_KEY_UN",0)=124 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=129 OR NVL("ASTS"."STA_STATE_KEY_UN",0)=154) THEN '2#B'
WHEN "WOMP"."WOWSM_MAX_START_TIME"<SYSDATE@!+"ADMSC"."SCF_CONSTAN)
35 - access("AST"."STA_STATE_KEY_UN"="VW_COL_1" AND "STA_OPERATION_TYPE_ID_FK"="OWS"."WRS_OPERATION_TYPE_ID_FK")
40 - access("OWSI"."WSI_LANGUAGE_ID_FK"="ASTI"."STAI_LANGUAGE_ID_FK")
41 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
43 - access("OWWM"."WOWSM_WO_ID"="OWO"."WKOR_ID_PK" AND "OWWM"."WOWSM_WS_ID"="OWS"."WRS_ID_PK")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
44 - access("OLI"."OLI_ORDER_ID_FK"="OWO"."WKOR_ORDER_ID_FK" AND "WKOR_ORD_LINEITEM_ID_FK"="OLI_ID_PK")
48 - access("WRS_OPERATION_TYPE_ID_FK"="OWS"."WRS_OPERATION_TYPE_ID_FK" AND "WSID"="OWS"."WRS_ID_PK" AND
"OWS"."WRS_OPERATION_TYPE_ID_FK"="AR"."ROLE_OPR_TYPE_ID_FK")
49 - access("WOID"="OWO"."WKOR_ID_PK")
52 - access("ASTAT"."STA_STATE_ID_PK"(+)="ALLDATA"."STATEID")
54 - access("ALLDATA"."WSID"="OWS"."WRS_ID_PK")
61 - access("AS2"."STA_STATE_ID_PK"="OWO"."WKOR_STATUS_CODE")
64 - access("AR"."ROLE_ID_PK"=19)
65 - filter("AS2"."STA_STATE_ID_PK"<>129)
66 - filter(NVL("OWO"."WKOR_TYPE",'0')<>'P' AND NVL("OWO"."WKOR_TYPE",'0')<>'X' AND "OWO"."WKOR_STATUS_CODE"<>129)
69 - access("OWSI"."WSI_LANGUAGE_ID_FK"=1 AND "OWSI"."WSI_ID_FK"="OWS"."WRS_ID_PK")
70 - access("OWS"."WRS_OPERATION_TYPE_ID_FK"="OWS"."WRS_OPERATION_TYPE_ID_FK")
71 - filter(NVL("OLI"."OLI_TYPE",'0')<>'P')
73 - access("WKOR_ORDER_ID_FK"="OO"."ORD_ID_PK")
74 - filter("ASTI"."STAI_LANGUAGE_ID_FK"=1)
76 - access("AST"."STA_STATE_ID_PK"="ASTI"."STAI_STATE_ID_FK")
78 - access("AS1"."STA_STATE_ID_PK"="AST"."STA_STATE_ID_PK")
80 - access("ASTAT"."STA_STATE_KEY_UN"="AST"."STA_STATE_KEY_UN")
84 - filter("OWWM"."WOWSM_WS_ID"="OWS"."WRS_ID_PK")
85 - filter("OWSH"."WSH_UPDATED_DATE"="VW_COL_1" AND "OWSH"."WSH_STATE_ID_FK"="ASTAT"."STA_STATE_ID_PK")
89 - access("OWSH"."WSH_WORKORDER_ID_FK"="OWO"."WKOR_ID_PK")
91 - access("OWSH"."WSH_WRS_ID_FK"="OWWM"."WOWSM_WS_ID")
92 - filter("OWWM"."WOWSM_WS_ID"="OWS"."WRS_ID_PK")
93 - filter("OWO"."WKOR_LAST_UPDATED_DATE"="VW_COL_1" AND "OWO"."WKOR_STATUS_CODE"="ASTAT"."STA_STATE_ID_PK" AND
"OWO"."WKOR_WRS_ID_FK"="OWWM"."WOWSM_WS_ID")
94 - access("OWO"."WKOR_ID_PK"="OWO"."WKOR_ID_PK")
98 - filter(TO_NUMBER(DECODE(TO_CHAR("STA_STATE_START_WS")||TO_CHAR("STA_STATE_STOP_WS")||TO_CHAR("STA_STATE_INIT_WS"),'100','1
','010','0','-1'))=0)
99 - access("STA_STATE_ID_PK"="STAI_STATE_ID_FK")
100 - filter(ROWNUM=1)
101 - access("V1"."VRYGBS"="VW_COL_1" AND "V1"."VWKOR_ID_PK"="WKOR_ID_PK")
104 - access("WO"."WKOR_STATUS_CODE"=TO_NUMBER(TO_CHAR("ASTS"."STA_STATE_ID_PK"(+))||''))
109 - access("WO"."WKOR_ID_PK"=:B1)
110 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
111 - access("OO"."ORD_ID_PK"="WO"."WKOR_ORDER_ID_FK")
112 - filter("WOMP"."WOWSM_MAX_START_TIME" IS NOT NULL)
113 - access("WOMP"."WOWSM_WO_ID"="WO"."WKOR_ID_PK")
filter("WOMP"."WOWSM_WO_ID"=:B1)
119 - access("WO"."WKOR_STATUS_CODE"=TO_NUMBER(TO_CHAR("ASTS"."STA_STATE_ID_PK"(+))||''))
124 - access("WO"."WKOR_ID_PK"=:B1)
125 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
126 - access("OO"."ORD_ID_PK"="WO"."WKOR_ORDER_ID_FK")
127 - filter("WOMP"."WOWSM_MAX_START_TIME" IS NOT NULL)
128 - access("WOMP"."WOWSM_WO_ID"="WO"."WKOR_ID_PK")
filter("WOMP"."WOWSM_WO_ID"=:B1)
242 rows selected.
Please advice me as what can I change in the above query to make it faster.
Sorry for the too big line of message but wanted to paste it here instaed of attaching a file.
Regards,
Mahi
[Updated on: Fri, 07 November 2008 03:23] Report message to a moderator |
|
|
|
| Re: Query taking very long time [message #357925 is a reply to message #357897] |
Fri, 07 November 2008 04:49  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
I am a beginner here.
Still.... I would suggest to have a look into ORD_WKOR_WS_MAPPING table indexes.
If you are in oracle 9i, you can try with rule hint and get the plan and from there analyze. Though after 10g rule hint is not going to be supported I think it would help to analyze a query.
Even I am waiting to see for the others response in this forum.I do belive that your issue will be solved soon.
You could also provide some additional information like what are the indexes that has been created for a table.
Good luck!
Oli
[Updated on: Fri, 07 November 2008 04:50] Report message to a moderator |
|
|
|
| Re: Query taking very long time [message #358018 is a reply to message #357925] |
Fri, 07 November 2008 14:40 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You've got an 80 line SQL riddled with views, sub-queries, and SELECT expressions. Did you honestly expect it to scale well?
In my experience, a SQL this complex is doomed to failure. If SQL statements reflect a business requirement, I find it improbable that a real business requirement could be this ridiculously complex. That means that either the data modeller was an oxygen-thief, or the author of the SQL did not understand the model.
My suggestion would be to start from scratch. Find out what the SQL is actually supposed to do, build it up bit by bit, and tune it as you go. Avoid views on views. Avoid views containing GROUP BY, DISTINCT, UNION, or anything even remotely complex (stick to plain inner joins). Avoid outer joining to views.
Ross Leishman
|
|
|
|
| Re: Query taking very long time [message #358436 is a reply to message #358018] |
Tue, 11 November 2008 05:49 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi Ross,
I tried to make the query simpler by putting in values where it can be done. Now it is taking only 6 seconds but again the records may increase upto millions and this might take more time than taking now.
Please advice on the query with explain plan below to increase the performance so that it can process faster when records ae in millions with many concurrent users online.
SQL> explain plan for
2 select *
3 from (select vwostate.VUPDATEDDATE
4 currentstatustime,ast.STA_STATE_ID_PK,VWOID workorderid, asti.STAI_STATE_NAME currentstatus
5 ,oo.ORD_ID_PK orderid
6 , vwostate.VSTATUNKEY stateuniqueid
7 ,row_number() over (partition by vwostate.VOPRID,VWOID
8 order by vwostate.VUPDATEDDATE desc) num
9 ,vsc.V_START_COMPLETE startComp
10 , owo.WKOR_REQUIRED_QUANTITY orderquantity
11 ,nvl(owo.WKOR_HOLD_CTRL, 0) holdEnabled
12 ,nvl(owo.WKOR_HOLD_TYPE, 0) holdType
13 , oli.OLI_SKU_NUMBER || '-' || oli.OLI_SKUREVISION_NUMBER skunumber
14 , oo.ORD_DELIVERYDATETIME deliverydatetime
15 , oo.ORD_DELIVERYDATETIME duedate
16 , owsi.WSI_NAME workstepname
17 , asti.STAI_STATE_NAME status
18 , ast.STA_STATE_ID_PK currentstatusid
19 , owwm.WOWSM_BOM_ID_FK bomid
20 , owo.WKOR_PARENT_WO_ID parentworkorderid
21 , owo.WKOR_MASTER_WO_ID masterworkorderid
22 , owo.WKOR_STATUS_CODE workorderstatusid
23 , ows.WRS_ID_PK workstepid , ar.ROLE_ID_PK roleid
24 ,CASE
25 WHEN (NVL (ast.sta_state_key_un, 0) IN
26 (75,76,77,78,79,80,95,105,115,125,155,205)
27 )
28 THEN 'S'
29 WHEN (NVL (ast.sta_state_key_un, 0) IN
30 (61,62,63,64,65,66,94,104,114,124,129,154,207)
31 )
32 THEN 'B'
33 WHEN (SYSDATE + 3 / 24 > owwm.wowsm_max_start_time
34 )
35 THEN 'R'
36 WHEN (SYSDATE + 6 / 24 > owwm.wowsm_max_start_time
37 )
38 THEN 'Y'
39 ELSE 'G'
40 END ryg from VWORKORDER_STATES vwostate
41 ,ADM_STATES ast
42 , ADM_ROLES ar
43 ,ADM_STATES_IN asti
44 ,ord_work_order owo
45 ,ORD_LINEITEMS oli
46 ,ord_orders oo
47 ,VWSTATESSTARTCOMPLETE vsc
48 ,ord_wkor_ws_mapping owwm
49 , ord_work_steps ows
50 ,ORD_WORK_STEPS_IN owsi where vwostate.VOPRID = ar.ROLE_OPR_TYPE_ID_FK
51 and ast.STA_STATE_ID_PK = vwostate.VSTATEID
52 and ast.STA_STATE_ID_PK = asti.STAI_STATE_ID_FK
53 and asti.STAI_LANGUAGE_ID_FK = 1
54 and owo.WKOR_ID_PK = vwostate.VWOID
55 and NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
56 and owo.WKOR_STATUS_CODE != 129
57 and oli.OLI_ID_PK = owo.WKOR_ORD_LINEITEM_ID_FK
58 and owo.WKOR_ORDER_ID_FK = oo.ORD_ID_PK
59 and nvl(oo.ord_type,0)!='D'
60 and ast.STA_STATE_ID_PK = vsc.V_STA_STATE_ID_PK
61 and NVL (oli.oli_type, 0) != 'P'
62 and owwm.WOWSM_WO_ID = owo.WKOR_ID_PK
63 and owwm.WOWSM_WS_ID = vwostate.VWSID
64 and oo.ORD_COMPLETED_DATE is null
65 and ows.WRS_ID_PK = vwostate.VWSID
66 and ows.WRS_ID_PK = owsi.WSI_ID_FK
67 and owsi.WSI_LANGUAGE_ID_FK = asti.STAI_LANGUAGE_ID_FK and ar.ROLE_ID_PK = 19
68 ) basedata
69 where num=1
70 and basedata.startComp != 0
71 and basedata.ryg != 'S'
72 order by duedate,baseData.orderid asc
73 ,baseData.workorderid asc;
Explained.
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2278049303
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 384 | 130K| 676 (5)| 00:00:09 |
| 1 | SORT ORDER BY | | 384 | 130K| 676 (5)| 00:00:09 |
|* 2 | VIEW | | 384 | 130K| 675 (5)| 00:00:09 |
|* 3 | WINDOW SORT PUSHED RANK | | 384 | 81408 | 675 (5)| 00:00:09 |
| 4 | NESTED LOOPS | | 384 | 81408 | 674 (5)| 00:00:09 |
| 5 | NESTED LOOPS | | 384 | 79872 | 673 (5)| 00:00:09 |
|* 6 | HASH JOIN | | 386 | 71410 | 671 (5)| 00:00:09 |
|* 7 | HASH JOIN | | 390 | 65520 | 557 (5)| 00:00:07 |
|* 8 | HASH JOIN RIGHT OUTER | | 2589 | 371K| 383 (5)| 00:00:05 |
| 9 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 2589 | 351K| 379 (5)| 00:00:05 |
| 11 | TABLE ACCESS FULL | ADM_STATES | 88 | 1144 | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 2589 | 318K| 376 (5)| 00:00:05 |
| 13 | TABLE ACCESS FULL | ADM_STATES | 88 | 704 | 3 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 2589 | 298K| 372 (5)| 00:00:05 |
|* 15 | HASH JOIN | | 2570 | 245K| 300 (4)| 00:00:04 |
|* 16 | HASH JOIN | | 2555 | 157K| 96 (2)| 00:00:02 |
|* 17 | TABLE ACCESS FULL | ORD_WORK_STEPS_IN | 384 | 9216 | 3 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 2558 | 99762 | 93 (2)| 00:00:02 |
| 19 | NESTED LOOPS | | 2558 | 89530 | 92 (0)| 00:00:02 |
| 20 | NESTED LOOPS | | 15 | 195 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | ADM_ROLES | 1 | 6 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SYS_C00112571 | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | ORD_WORK_STEPS | 15 | 105 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | IDX_ORD_WRK_STPS_OP_TYPE_ID_FK | 15 | | 0 (0)| 00:00:01 |
| 25 | VIEW | | 173 | 3806 | 18 (0)| 00:00:01 |
| 26 | SORT UNIQUE | | | | | |
| 27 | UNION-ALL PARTITION | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID| ORD_WO_STATES_HISTORY | 127 | 4064 | 11 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_ORD_WO_ST_HIST_WRS_ID_FK | 126 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID| ORD_WORK_ORDER | 3920 | 164K| 291 (1)| 00:00:04 |
|* 31 | INDEX RANGE SCAN | IDX_ORD_WORK_ORDER_WRS_ID_FK | 3920 | | 12 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | SYS_C00112951 | 1 | 4 | 0 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | ORD_WORK_ORDER | 62664 | 2141K| 202 (5)| 00:00:03 |
|* 34 | TABLE ACCESS FULL | ORD_LINEITEMS | 33488 | 654K| 71 (3)| 00:00:01 |
| 35 | TABLE ACCESS FULL | ORD_WKOR_WS_MAPPING | 162K| 3323K| 172 (5)| 00:00:03 |
|* 36 | TABLE ACCESS FULL | ORD_ORDERS | 32622 | 541K| 113 (3)| 00:00:02 |
| 37 | TABLE ACCESS BY INDEX ROWID | ADM_STATES_IN | 1 | 23 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | SYS_C00112616 | 1 | | 0 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | SYS_C00112616 | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM"=1 AND TO_NUMBER("BASEDATA"."STARTCOMP")<>0 AND "BASEDATA"."RYG"<>'S')
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "OWS"."WRS_OPERATION_TYPE_ID_FK","ALLDATA"."WOID" ORDER BY
INTERNAL_FUNCTION("ALLDATA"."UPDATEDDATE") DESC )<=1)
6 - access("OWO"."WKOR_ORDER_ID_FK"="OO"."ORD_ID_PK")
7 - access("OWWM"."WOWSM_WO_ID"="OWO"."WKOR_ID_PK" AND "OWWM"."WOWSM_WS_ID"="ALLDATA"."WSID")
8 - access("ASTAT"."STA_STATE_ID_PK"(+)="ALLDATA"."STATEID")
10 - access("AST"."STA_STATE_ID_PK"="STA_STATE_ID_PK")
12 - access("AST"."STA_STATE_ID_PK"="ALLDATA"."STATEID")
14 - access("OLI"."OLI_ID_PK"="OWO"."WKOR_ORD_LINEITEM_ID_FK")
15 - access("OWO"."WKOR_ID_PK"="ALLDATA"."WOID")
16 - access("OWS"."WRS_ID_PK"="OWSI"."WSI_ID_FK")
17 - filter("OWSI"."WSI_LANGUAGE_ID_FK"=1)
22 - access("AR"."ROLE_ID_PK"=19)
24 - access("OWS"."WRS_OPERATION_TYPE_ID_FK"="AR"."ROLE_OPR_TYPE_ID_FK")
29 - access("OWSH"."WSH_WRS_ID_FK"="OWS"."WRS_ID_PK")
31 - access("OWO"."WKOR_WRS_ID_FK"="OWS"."WRS_ID_PK")
32 - access("OWS"."WRS_ID_PK"="ALLDATA"."WSID")
33 - filter(NVL("OWO"."WKOR_TYPE",'0')<>'P' AND NVL("OWO"."WKOR_TYPE",'0')<>'X' AND "OWO"."WKOR_STATUS_CODE"<>129)
34 - filter(NVL("OLI"."OLI_TYPE",'0')<>'P')
36 - filter(NVL("OO"."ORD_TYPE",'0')<>'D' AND "OO"."ORD_COMPLETED_DATE" IS NULL)
38 - access("AST"."STA_STATE_ID_PK"="ASTI"."STAI_STATE_ID_FK" AND "ASTI"."STAI_LANGUAGE_ID_FK"=1)
39 - access("STA_STATE_ID_PK"="STAI_STATE_ID_FK")
72 rows selected.
SQL> spool off
Thanks for looking into this,
Mahi
|
|
|
|
| Re: Query taking very long time [message #358561 is a reply to message #358018] |
Tue, 11 November 2008 20:44 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rleishman wrote on Sat, 08 November 2008 07:40 | My suggestion would be to start from scratch. Find out what the SQL is actually supposed to do, build it up bit by bit, and tune it as you go.
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 04:44:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
