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 Go to next message
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 Sad

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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.

Previous Topic: Dunsel Joins
Next Topic: Find speed
Goto Forum:
  


Current Time: Fri Dec 09 09:51:49 CST 2016

Total time taken to generate the page: 0.08466 seconds