Home » RDBMS Server » Performance Tuning » Optimize the Query (Oracle 9i)
Optimize the Query [message #448092] Fri, 19 March 2010 07:47 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
The below query is taking lot of time and gets almost hanged when number of concurrent users are many.
Please help me in optimizing this query.

SELECT DISTINCT evt.projectnm, evt.projectdesc, evt.actlvldesc3 AS ctrdesc,
                evt.contractno AS contract_number,
                evt.contractdesc AS contract_name, evt.worklvl1 AS company,
                evt.worklvl2 AS ACCOUNT, evt.worklvl3 AS costcenter,
                evt.worklvl4, evt.actlvl3 AS ctr,
                evt.worklvl5 AS disciplineid,
                evt.worklvldesc5 AS disciplinedesc,
                evt.actlvl2 AS subfunction,
                evt.actlvldesc2 AS subfunctiondesc, evt.locationact,
                evt.fullnm, evt.personnum, evt.legacyid, evt.payrulename,
                evt.workertypenm, evt.supervisornum, evt.supervisorfullname,
                evt.useraccountnm, evt.approver, evt.corecode, evt.coredesc,
                evt.craftcode, evt.craftdesc, evt.craftid, evt.craftname,
                evt.createddtm, evt.weekending, evt.eventdtm, evt.adjstartdtm,
                evt.adjenddtm, evt.shiftid AS shift, evt.hourtype AS hourtype,
                evt.hoursqty,
                CASE
                   WHEN evt.shiftid = 1
                      THEN evt.hoursqty
                   ELSE 0
                END AS dayhours,
                CASE
                   WHEN evt.shiftid > 1
                      THEN evt.hoursqty
                   ELSE 0
                END AS nighthours,
                CASE
                   WHEN evt.hourtype NOT LIKE '%O%'
                    OR evt.hourtype IS NULL
                      THEN evt.hoursqty
                   ELSE 0
                END AS reghours,
                CASE
                   WHEN evt.hourtype LIKE '%O%'
                      THEN evt.hoursqty
                   ELSE 0
                END AS othours,
                evt.barge, evt.weldcode, evt.equipcode, evt.payperiod,
                evt.leadermancode, evt.leaderman, evt.OPERATOR,
                evt.cuteoffdtm, evt.foremancode, evt.foremanname,
                evt.superintcode, evt.superintname, evt.superviscode,
                evt.supervisname, evt.deptcode, evt.deptname,
                evt.empapprovedsw, evt.approvedsw, evt.actapprovedsw,
                evt.foremancode,
                CASE
                   WHEN evt.actapprovedsw = 1
                      THEN 'A'
                   WHEN evt.empapprovedsw = 1
                      THEN 'M'
                   WHEN evt.approvedsw = 1
                      THEN 'E'
                   ELSE 'N'
                END AS approvalstatus
           FROM jrm_vp_events_batam evt LEFT JOIN mywtkemployee wtk
                ON evt.personid = wtk.personid AND wtk.sessionid = :b1
          WHERE 1 = 1
            AND evt.eventdtm >=
                            CASE
                               WHEN :b1 > 0
                                  THEN wtk.startdate
                               ELSE TO_DATE (:b14)
                            END
            AND evt.eventdtm <
                              CASE
                                 WHEN :b1 > 0
                                    THEN wtk.enddate
                                 ELSE TO_DATE (:b13)
                              END
            AND evt.eventtypeid = 15
            AND evt.hoursqty > 0
            AND evt.companyemp LIKE :b12
            AND UPPER (evt.projectnm) LIKE UPPER (:b11)
            AND UPPER (evt.contractno) LIKE UPPER (:b10)
            AND UPPER (evt.deptcode) LIKE UPPER (:b9)
            AND UPPER (evt.personnum) LIKE UPPER (:b8)
            AND UPPER (evt.actlvl2) LIKE UPPER (:b7)
            AND UPPER (evt.actlvl3) LIKE UPPER (:b6)
            AND UPPER (evt.worklvl5) LIKE UPPER (:b5)
            AND evt.employeetype != 'MISC'
            AND evt.employeetype LIKE
                       CASE
                          WHEN :b4 = 2
                             THEN 'EBCS'
                          WHEN :b4 = 1
                             THEN 'T&A'
                          ELSE '%'
                       END
            AND (   (evt.shiftid LIKE
                        CASE
                           WHEN :b3 = 1
                              THEN '1'
                           WHEN :b3 = 2
                              THEN '2'
                           WHEN :b3 = 3
                              THEN '3'
                           WHEN :b3 = 4
                              THEN '2'
                           ELSE '%'
                        END
                    )
                 OR (evt.shiftid LIKE CASE
                                        WHEN :b3 = 4
                                           THEN '3'
                                        ELSE '99'
                                     END)
                )
            AND UPPER (evt.projectstatus) NOT LIKE
                                     CASE
                                        WHEN :b2 = 1
                                           THEN '-SHOW-'
                                        ELSE 'COMPLETE'
                                     END
            AND UPPER (evt.projectstatus) NOT LIKE
                                    CASE
                                       WHEN :b2 = 1
                                          THEN '-SHOW-'
                                       ELSE 'CANCELLED'
                                    END
       ORDER BY 1, 3;


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 886195462

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                    |   534 |  5559K|       |   368K  (1)| 01:13:42 |
|   1 |  SORT ORDER BY                                               |                    |   534 |  5559K|  8552K|   368K  (1)| 01:13:42 |
|   2 |   HASH UNIQUE                                                |                    |   534 |  5559K|  8552K|   367K  (1)| 01:13:28 |
|*  3 |    HASH JOIN RIGHT OUTER                                     |                    |   534 |  5559K|       |   366K  (1)| 01:13:13 |
|   4 |     TABLE ACCESS FULL                                        | JRM_CORECRAFTCODES |  1305 | 28710 |       |     4   (0)| 00:00:01 |
|*  5 |     FILTER                                                   |                    |       |       |       |            |          |
|*  6 |      HASH JOIN RIGHT OUTER                                   |                    |   534 |  5548K|       |   366K  (1)| 01:13:13 |
|   7 |       TABLE ACCESS BY INDEX ROWID                            | MYWTKEMPLOYEE      |    31 |  1364 |       |     6   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                                      | XU1_MYWTKEMPLOYEE  |    12 |       |       |     2   (0)| 00:00:01 |
|*  9 |       HASH JOIN RIGHT OUTER                                  |                    |   534 |  5525K|       |   366K  (1)| 01:13:13 |
|  10 |        TABLE ACCESS FULL                                     | JRM_WFA_CREWINFO   |     1 |   245 |       |     2   (0)| 00:00:01 |
|  11 |        VIEW                                                  |                    |   534 |  5397K|       |   366K  (1)| 01:13:13 |
|  12 |         NESTED LOOPS OUTER                                   |                    |   534 |  2889K|       |   366K  (1)| 01:13:13 |
|  13 |          NESTED LOOPS OUTER                                  |                    |   532 |  2857K|       |   364K  (1)| 01:12:59 |
|  14 |           NESTED LOOPS OUTER                                 |                    |   529 |  2821K|       |   363K  (1)| 01:12:44 |
|  15 |            NESTED LOOPS OUTER                                |                    |   527 |  2804K|       |   362K  (1)| 01:12:29 |
|  16 |             NESTED LOOPS OUTER                               |                    |   525 |  2773K|       |   361K  (1)| 01:12:15 |
|* 17 |              FILTER                                          |                    |       |       |       |            |          |
|* 18 |               HASH JOIN OUTER                                |                    |   525 |  2753K|    27M|   360K  (1)| 01:12:01 |
|* 19 |                FILTER                                        |                    |       |       |       |            |          |
|* 20 |                 HASH JOIN RIGHT OUTER                        |                    |  5381 |    27M|       | 37141   (1)| 00:07:26 |
|* 21 |                  TABLE ACCESS FULL                           | PERSONCSTMDATA     | 11184 |   240K|       |    65   (2)| 00:00:01 |
|  22 |                  VIEW                                        | JRM_VP_EVENTS      |  3937 |    19M|       | 37076   (1)| 00:07:25 |
|* 23 |                   FILTER                                     |                    |       |       |       |            |          |
|* 24 |                    HASH JOIN RIGHT OUTER                     |                    |  3937 |  8223K|  4208K| 37076   (1)| 00:07:25 |
|  25 |                     TABLE ACCESS FULL                        | LABORACCT          |   130K|  2677K|       |  1225   (1)| 00:00:15 |
|* 26 |                     HASH JOIN RIGHT OUTER                    |                    |  3937 |  8143K|       | 35582   (1)| 00:07:07 |
|* 27 |                      TABLE ACCESS FULL                       | COMBHOMEACCT       | 11951 |   303K|       |    64   (4)| 00:00:01 |
|  28 |                      NESTED LOOPS                            |                    |  3517 |  7185K|       | 35517   (1)| 00:07:07 |
|  29 |                       NESTED LOOPS OUTER                     |                    |  2060 |  4099K|       | 29333   (2)| 00:05:52 |
|* 30 |                        FILTER                                |                    |       |       |       |            |          |
|  31 |                         NESTED LOOPS OUTER                   |                    |  1648 |  3217K|       | 25548   (2)| 00:05:07 |
|* 32 |                          FILTER                              |                    |       |       |       |            |          |
|  33 |                           NESTED LOOPS OUTER                 |                    |  1648 |  3102K|       | 23899   (2)| 00:04:47 |
|  34 |                            NESTED LOOPS OUTER                |                    |  1648 |  3020K|       | 23422   (2)| 00:04:42 |
|* 35 |                             HASH JOIN RIGHT OUTER            |                    |  1648 |  3014K|       | 23422   (2)| 00:04:42 |
|  36 |                              TABLE ACCESS FULL               | PAYRULE            |   127 |  1016 |       |     3   (0)| 00:00:01 |
|* 37 |                              HASH JOIN RIGHT OUTER           |                    |  1625 |  2959K|       | 23418   (2)| 00:04:42 |
|  38 |                               TABLE ACCESS FULL              | PAYRULEIDS         |   125 |  6125 |       |     3   (0)| 00:00:01 |
|* 39 |                               FILTER                         |                    |       |       |       |            |          |
|* 40 |                                HASH JOIN RIGHT OUTER         |                    |  1625 |  2881K|       | 23415   (2)| 00:04:41 |
|  41 |                                 TABLE ACCESS FULL            | WORKERTYPE         |     8 |    96 |       |     3   (0)| 00:00:01 |
|* 42 |                                 HASH JOIN RIGHT OUTER        |                    |  1625 |  2862K|       | 23411   (2)| 00:04:41 |
|  43 |                                  TABLE ACCESS FULL           | WTKEMPLOYEE        | 11278 |   132K|       |    35   (0)| 00:00:01 |
|* 44 |                                  HASH JOIN RIGHT OUTER       |                    |  1625 |  2843K|       | 23375   (2)| 00:04:41 |
|* 45 |                                   TABLE ACCESS FULL          | PERSONCSTMDATA     | 11148 |   239K|       |    65   (2)| 00:00:01 |
|* 46 |                                   HASH JOIN RIGHT OUTER      |                    |  1190 |  2056K|       | 23310   (2)| 00:04:40 |
|  47 |                                    VIEW                      |                    | 10101 |   217K|       |   569   (3)| 00:00:07 |
|  48 |                                     HASH GROUP BY            |                    | 10101 |   147K|       |   569   (3)| 00:00:07 |
|* 49 |                                      TABLE ACCESS FULL       | WFCAUDIT           | 78371 |  1148K|       |   563   (2)| 00:00:07 |
|* 50 |                                    HASH JOIN RIGHT OUTER     |                    |  1190 |  2031K|       | 22740   (2)| 00:04:33 |
|  51 |                                     TABLE ACCESS FULL        | USERACCOUNT        |  3164 | 72772 |       |    19   (0)| 00:00:01 |
|  52 |                                     VIEW                     |                    |  1190 |  2004K|       | 22721   (2)| 00:04:33 |
|* 53 |                                      FILTER                  |                    |       |       |       |            |          |
|  54 |                                       NESTED LOOPS OUTER     |                    |  1190 |  1735K|       | 22721   (2)| 00:04:33 |
|  55 |                                        VIEW                  |                    |  1190 |  1646K|       | 21530   (2)| 00:04:19 |
|* 56 |                                         FILTER               |                    |       |       |       |            |          |
|  57 |                                          NESTED LOOPS OUTER  |                    |  1190 |   538K|       | 21530   (2)| 00:04:19 |
|  58 |                                           NESTED LOOPS       |                    |  1190 |   449K|       | 20339   (2)| 00:04:05 |
|* 59 |                                            HASH JOIN         |                    |  1190 |   443K|       | 20338   (2)| 00:04:05 |
|* 60 | L                                           TABLE ACCESS FUL | WFAACTIVITY        | 10789 |  1727K|       |  2581   (2)| 00:00:31 |
|* 61 |                                             HASH JOIN        |                    | 23800 |  5066K|       | 17757   (2)| 00:03:34 |
|  62 | TER                                          NESTED LOOPS OU |                    |   564 | 43428 |       |   135   (3)| 00:00:02 |
|* 63 |                                               HASH JOIN      |                    |   564 | 41172 |       |   135   (3)| 00:00:02 |
|* 64 |                                                HASH JOIN     |                    |   564 | 36096 |       |   124   (2)| 00:00:02 |
|* 65 |  FULL                                           TABLE ACCESS | PERSON             |   564 | 30456 |       |    89   (2)| 00:00:02 |
|  66 |  FULL                                           TABLE ACCESS | JAIDS              | 11278 |   110K|       |    34   (0)| 00:00:01 |
|  67 | LL SCAN                                        INDEX FAST FU | XU2_WTKEMPLOYEE    | 11278 |    99K|       |    10   (0)| 00:00:01 |
|* 68 | CAN                                           INDEX UNIQUE S | PK_MYPAYPERIOD     |     1 |     4 |       |     0   (0)| 00:00:01 |
|* 69 | LL                                           TABLE ACCESS FU | WFAREPACTYSPAN     |   475K|    64M|       | 17618   (2)| 00:03:32 |
|* 70 |                                            INDEX UNIQUE SCAN | PK_WFAACTYHDR      |     1 |     5 |       |     0   (0)| 00:00:01 |
|* 71 | DEX ROWID                                 TABLE ACCESS BY IN | WFAACTIVITY        |     1 |    76 |       |     1   (0)| 00:00:01 |
|* 72 |                                            INDEX UNIQUE SCAN | PK_WFAACTIVITY     |     1 |       |       |     0   (0)| 00:00:01 |
|* 73 |  ROWID                                 TABLE ACCESS BY INDEX | WFAACTIVITY        |     1 |    76 |       |     1   (0)| 00:00:01 |
|* 74 |                                         INDEX UNIQUE SCAN    | PK_WFAACTIVITY     |     1 |       |       |     0   (0)| 00:00:01 |
|* 75 |                             INDEX UNIQUE SCAN                | PK_FIXEDRULE       |     1 |     4 |       |     0   (0)| 00:00:01 |
|  76 |                            TABLE ACCESS BY INDEX ROWID       | LABORACCT          |     1 |    51 |       |     1   (0)| 00:00:01 |
|* 77 |                             INDEX UNIQUE SCAN                | PK_LABORACCT       |     1 |       |       |     0   (0)| 00:00:01 |
|* 78 |                          TABLE ACCESS BY INDEX ROWID         | WFAACTIVITY        |     1 |    71 |       |     1   (0)| 00:00:01 |
|* 79 |                           INDEX UNIQUE SCAN                  | PK_WFAACTIVITY     |     1 |       |       |     0   (0)| 00:00:01 |
|* 80 |                        TABLE ACCESS BY INDEX ROWID           | WFAREPACTYRES      |     1 |    39 |       |     3   (0)| 00:00:01 |
|* 81 |                         INDEX RANGE SCAN                     | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
|* 82 |                       TABLE ACCESS BY INDEX ROWID            | WFAREPTOTAL        |     2 |   108 |       |     3   (0)| 00:00:01 |
|* 83 |                        INDEX RANGE SCAN                      | X1_WFAREPTOTAL     |     2 |       |       |     2   (0)| 00:00:01 |
|  84 |                VIEW                                          |                    |  3616K|   120M|       |   312K  (1)| 01:02:29 |
|  85 |                 HASH GROUP BY                                |                    |  3616K|   606M|  1284M|   312K  (1)| 01:02:29 |
|* 86 |                  HASH JOIN RIGHT OUTER                       |                    |  3616K|   606M|       |   123K  (1)| 00:24:45 |
|  87 |                   INDEX FAST FULL SCAN                       | PK_LABORACCT       |   130K|   637K|       |    76   (2)| 00:00:01 |
|* 88 |                   HASH JOIN RIGHT OUTER                      |                    |  3616K|   589M|       |   123K  (1)| 00:24:43 |
|* 89 |                    TABLE ACCESS FULL                         | COMBHOMEACCT       | 11951 |   303K|       |    64   (4)| 00:00:01 |
|* 90 |                    HASH JOIN RIGHT OUTER                     |                    |  3230K|   446M|       |   123K  (1)| 00:24:42 |
|  91 |                     VIEW                                     |                    | 10101 | 40404 |       |   569   (3)| 00:00:07 |
|  92 |                      HASH GROUP BY                           |                    | 10101 |   147K|       |   569   (3)| 00:00:07 |
|* 93 |                       TABLE ACCESS FULL                      | WFCAUDIT           | 78371 |  1148K|       |   563   (2)| 00:00:07 |
|* 94 |                     HASH JOIN RIGHT OUTER                    |                    |  3230K|   434M|       |   122K  (1)| 00:24:35 |
|  95 |                      INDEX FULL SCAN                         | PK_WORKERTYPE      |     8 |    24 |       |     1   (0)| 00:00:01 |
|* 96 |                      HASH JOIN RIGHT OUTER                   |                    |  3230K|   425M|       |   122K  (1)| 00:24:35 |
|  97 |                       INDEX FULL SCAN                        | PK_FIXEDRULE       |    15 |    60 |       |     1   (0)| 00:00:01 |
|* 98 |                       HASH JOIN RIGHT OUTER                  |                    |  3230K|   412M|       |   122K  (1)| 00:24:34 |
|  99 |                        TABLE ACCESS FULL                     | PAYRULE            |   127 |  1016 |       |     3   (0)| 00:00:01 |
|*100 |                        HASH JOIN RIGHT OUTER                 |                    |  3184K|   382M|  4216K|   122K  (1)| 00:24:34 |
| 101 |                         VIEW                                 | index$_join$_042   |   215K|  1685K|       |  1699   (1)| 00:00:21 |
|*102 |                          HASH JOIN                           |                    |       |       |       |            |          |
| 103 |                           INDEX FAST FULL SCAN               | PK_WFAACTIVITY     |   215K|  1685K|       |   570   (1)| 00:00:07 |
| 104 |                           INDEX FAST FULL SCAN               | X1_WFAACTIVITY     |   215K|  1685K|       |   804   (1)| 00:00:10 |
|*105 |                         HASH JOIN RIGHT OUTER                |                    |  3184K|   358M|       |   111K  (1)| 00:22:16 |
| 106 |                          INDEX FULL SCAN                     | PK_PAYRULEIDS      |   125 |   500 |       |     1   (0)| 00:00:01 |
|*107 |                          HASH JOIN RIGHT OUTER               |                    |  3184K|   346M|       |   111K  (1)| 00:22:16 |
| 108 |                           TABLE ACCESS FULL                  | WTKEMPLOYEE        | 11278 |   132K|       |    35   (0)| 00:00:01 |
|*109 |                           HASH JOIN RIGHT OUTER              |                    |  3184K|   309M|       |   111K  (1)| 00:22:15 |
|*110 |                            INDEX FAST FULL SCAN              | PK_PERSONCSTMDATA  | 11148 | 89184 |       |    19   (0)| 00:00:01 |
|*111 |                            HASH JOIN RIGHT OUTER             |                    |  3184K|   285M|    12M|   111K  (1)| 00:22:15 |
|*112 |                             TABLE ACCESS FULL                | WFAREPACTYRES      |   539K|  6847K|       |  6433   (2)| 00:01:18 |
|*113 |                             HASH JOIN RIGHT OUTER            |                    |  3184K|   245M|       | 90914   (1)| 00:18:11 |
| 114 |                              INDEX FAST FULL SCAN            | PK_LABORACCT       |   130K|   637K|       |    76   (2)| 00:00:01 |
|*115 |                              HASH JOIN RIGHT OUTER           |                    |  3184K|   230M|       | 90810   (1)| 00:18:10 |
| 116 |                               INDEX FAST FULL SCAN           | XU2_USERACCOUNT    |  3164 | 15820 |       |     4   (0)| 00:00:01 |
|*117 |                               HASH JOIN                      |                    |  3184K|   215M|    96M| 90780   (1)| 00:18:10 |
| 118 |                                VIEW                          |                    |  1652K|    77M|       | 34727   (2)| 00:06:57 |
|*119 |                                 HASH JOIN RIGHT OUTER        |                    |  1652K|   211M|  5272K| 34727   (2)| 00:06:57 |
| 120 |                                  VIEW                        | index$_join$_040   |   215K|  2739K|       |  2480   (1)| 00:00:30 |
|*121 |                                   HASH JOIN                  |                    |       |       |       |            |          |
| 122 |                                    INDEX FAST FULL SCAN      | X4_WFAACTIVITY     |   215K|  2739K|       |  1320   (1)| 00:00:16 |
| 123 |                                    INDEX FAST FULL SCAN      | X1_WFAACTIVITY     |   215K|  2739K|       |   804   (1)| 00:00:10 |
| 124 |                                  VIEW                        |                    |  1652K|   190M|       | 25822   (2)| 00:05:10 |
|*125 |                                   HASH JOIN                  |                    |  1652K|   165M|       | 25822   (2)| 00:05:10 |
| 126 |                                    INDEX FAST FULL SCAN      | PK_WFAACTYHDR      |  3521 | 17605 |       |     4   (0)| 00:00:01 |
|*127 |                                    HASH JOIN RIGHT OUTER     |                    |  1652K|   157M|       | 25805   (2)| 00:05:10 |
| 128 |                                     INDEX FULL SCAN          | PK_MYPAYPERIOD     |   125 |   500 |       |     1   (0)| 00:00:01 |
|*129 |                                     HASH JOIN                |                    |  1652K|   151M|       | 25790   (2)| 00:05:10 |
| 130 |                                      INDEX FAST FULL SCAN    | XU2_WTKEMPLOYEE    | 11278 |    99K|       |    10   (0)| 00:00:01 |
|*131 |                                      HASH JOIN               |                    |  1652K|   137M|       | 25766   (2)| 00:05:10 |
| 132 |                                       VIEW                   | index$_join$_026   | 11278 |   242K|       |    85   (2)| 00:00:02 |
|*133 |                                        HASH JOIN             |                    |       |       |       |            |          |
| 134 |                                         INDEX FAST FULL SCAN | PK_PERSON          | 11278 |   242K|       |    30   (0)| 00:00:01 |
| 135 |                                         INDEX FAST FULL SCAN | XU1_PERSON         | 11278 |   242K|       |    54   (0)| 00:00:01 |
|*136 |                                       HASH JOIN              |                    |  1652K|   102M|       | 25667   (2)| 00:05:09 |
| 137 |                                        TABLE ACCESS FULL     | JAIDS              | 11278 |   110K|       |    34   (0)| 00:00:01 |
|*138 |                                        HASH JOIN             |                    |  1652K|    86M|  9064K| 25619   (2)| 00:05:08 |
|*139 | R                                       HASH JOIN RIGHT OUTE |                    |   215K|  6532K|  5272K|  5384   (1)| 00:01:05 |
| 140 |                                          VIEW                | index$_join$_038   |   215K|  2739K|       |  2480   (1)| 00:00:30 |
|*141 |                                           HASH JOIN          |                    |       |       |       |            |          |
| 142 | CAN                                        INDEX FAST FULL S | X4_WFAACTIVITY     |   215K|  2739K|       |  1320   (1)| 00:00:16 |
| 143 | CAN                                        INDEX FAST FULL S | X1_WFAACTIVITY     |   215K|  2739K|       |   804   (1)| 00:00:10 |
| 144 |                                          TABLE ACCESS FULL   | WFAACTIVITY        |   215K|  3792K|       |  2560   (2)| 00:00:31 |
|*145 |                                         TABLE ACCESS FULL    | WFAREPACTYSPAN     |  1652K|    37M|       | 17642   (2)| 00:03:32 |
|*146 |                                TABLE ACCESS FULL             | WFAREPTOTAL        |  5394K|   113M|       | 16514   (1)| 00:03:19 |
|*147 |              TABLE ACCESS BY INDEX ROWID                     | WFAREPACTYRES      |     1 |    39 |       |     3   (0)| 00:00:01 |
|*148 |               INDEX RANGE SCAN                               | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
|*149 |             TABLE ACCESS BY INDEX ROWID                      | WFAREPACTYRES      |     1 |    39 |       |     3   (0)| 00:00:01 |
|*150 |              INDEX RANGE SCAN                                | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
|*151 |            TABLE ACCESS BY INDEX ROWID                       | WFAREPACTYRES      |     1 |    13 |       |     3   (0)| 00:00:01 |
|*152 |             INDEX RANGE SCAN                                 | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
|*153 |           TABLE ACCESS BY INDEX ROWID                        | WFAREPACTYRES      |     1 |    39 |       |     3   (0)| 00:00:01 |
|*154 |            INDEX RANGE SCAN                                  | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
|*155 |          TABLE ACCESS BY INDEX ROWID                         | WFAREPACTYRES      |     1 |    39 |       |     3   (0)| 00:00:01 |
|*156 |           INDEX RANGE SCAN                                   | X1_WFAREPACTYRES   |     1 |       |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EVT"."COMPANYEMP"=SYS_OP_C2C("CRFT"."ENTITY"(+)) AND "CORE"."CORECODE"=SYS_OP_C2C("CRFT"."CORECODE"(+)))
   5 - filter("from$_subquery$_020"."QCSJ_C000000000700002">=CASE  WHEN (TO_NUMBER(:B1)>0) THEN "WTK"."STARTDATE" ELSE
              TO_DATE(:B14) END  AND "from$_subquery$_020"."QCSJ_C000000000700002"<CASE  WHEN (TO_NUMBER(:B1)>0) THEN "WTK"."ENDDATE" ELSE
              TO_DATE(:B13) END )
   6 - access("from$_subquery$_020"."QCSJ_C000000002000000"="WTK"."PERSONID"(+))
   8 - access("WTK"."SESSIONID"(+)=TO_NUMBER(:B1))
   9 - access("from$_subquery$_020"."REPTXT"=SYS_OP_C2C("CREW"."CREWID"(+)))
  17 - filter(TO_CHAR("JRM_RPTFN_GET_WORK_SHIFT"("SHIFT"."SHIFTSTARTTIME")) LIKE CASE TO_NUMBER(:B3) WHEN 1 THEN '1' WHEN 2 THEN
              '2' WHEN 3 THEN '3' WHEN 4 THEN '2' ELSE '%' END  OR TO_CHAR("JRM_RPTFN_GET_WORK_SHIFT"("SHIFT"."SHIFTSTARTTIME")) LIKE CASE
              TO_NUMBER(:B3) WHEN 4 THEN '3' ELSE '99' END )
  18 - access("SHIFT"."EVENTDTM"(+)="EVT"."EVENTDTM" AND "SHIFT"."PERSONNUM"(+)="EVT"."PERSONNUM")
  19 - filter(UPPER(NVL(SUBSTR(SUBSTR("PERSONCSTMDATATXT",1,INSTR("PERSONCSTMDATATXT",U' - ')-1),1,2),U' ')) LIKE
              SYS_OP_C2C(UPPER(:B9)))
  20 - access("EVT"."PERSONID"="PERSONID"(+))
  21 - filter("CUSTOMDATADEFID"(+)=2)
  23 - filter(NVL("LABORHOME"."LABORLEV1NM",U' ') LIKE SYS_OP_C2C(:B12))
  24 - access("LABORHOME"."LABORACCTID"(+)="HA"."LABORACCTID")
  26 - access("WE"."EMPLOYEEID"="HA"."EMPLOYEEID"(+))
  27 - filter("HA"."EXPIRATIONDTM"(+)>=SYSDATE@! AND "HA"."EFFECTIVEDTM"(+)<=SYSDATE@!)
  30 - filter(UPPER(SUBSTR(DECODE("from$_subquery$_104"."QCSJ_C000000010200008",1,"from$_subquery$_104"."QCSJ_C000000010200002",2,"
              from$_subquery$_104"."QCSJ_C000000010200003",3,"from$_subquery$_104"."SEARCHNM",4,"L3"."SEARCHNM",U''),INSTR(DECODE("from$_subquery
              $_104"."QCSJ_C000000010200008",1,"from$_subquery$_104"."QCSJ_C000000010200002",2,"from$_subquery$_104"."QCSJ_C000000010200003",3,"f
              rom$_subquery$_104"."SEARCHNM",4,"L3"."SEARCHNM",U''),U'-',1,1)+1)) LIKE SYS_OP_C2C(UPPER(:B11)) AND
              UPPER(DECODE(DECODE("from$_subquery$_104"."QCSJ_C000000010200008",1,"from$_subquery$_104"."QCSJ_C000000010200066",2,"from$_subquery
              $_104"."QCSJ_C000000010200067",3,"from$_subquery$_104"."COMPLSTATTYPEID",4,"L3"."COMPLSTATTYPEID",NULL),0,'Not
              Started',1,'Incomplete',2,'Complete',3,'Cancelled','N/A')) NOT LIKE CASE TO_NUMBER(:B2) WHEN 1 THEN '-SHOW-' ELSE 'COMPLETE' END
              AND UPPER(DECODE(DECODE("from$_subquery$_104"."QCSJ_C000000010200008",1,"from$_subquery$_104"."QCSJ_C000000010200066",2,"from$_subq
              uery$_104"."QCSJ_C000000010200067",3,"from$_subquery$_104"."COMPLSTATTYPEID",4,"L3"."COMPLSTATTYPEID",NULL),0,'Not
              Started',1,'Incomplete',2,'Complete',3,'Cancelled','N/A')) NOT LIKE CASE TO_NUMBER(:B2) WHEN 1 THEN '-SHOW-' ELSE 'CANCELLED' END )
  32 - filter(UPPER(NVL("LABORWORK"."LABORLEV5NM",U' ')) LIKE SYS_OP_C2C(UPPER(:B5)))
  35 - access("WE"."PAYRULEID"="PRU"."PAYRULEID"(+))
  37 - access("WE"."PAYRULEID"="PR"."PAYRULEID"(+))
  39 - filter(CASE "WT"."WORKERTYPENM" WHEN U'H' THEN 'T&A' WHEN U'CH' THEN 'T&A' WHEN U'CE' THEN 'EBCS' WHEN U'CN' THEN 'EBCS'
              WHEN U'E' THEN 'EBCS' WHEN U'N' THEN 'EBCS' ELSE 'MISC' END <>'MISC' AND CASE "WT"."WORKERTYPENM" WHEN U'H' THEN 'T&A' WHEN U'CH'
              THEN 'T&A' WHEN U'CE' THEN 'EBCS' WHEN U'CN' THEN 'EBCS' WHEN U'E' THEN 'EBCS' WHEN U'N' THEN 'EBCS' ELSE 'MISC' END  LIKE CASE
              TO_NUMBER(:B4) WHEN 2 THEN 'EBCS' WHEN 1 THEN 'T&A' ELSE '%' END )
  40 - access("WE"."WORKERTYPEID"="WT"."WORKERTYPEID"(+))
  42 - access("from$_subquery$_104"."QCSJ_C000000009000001"="WE"."EMPLOYEEID"(+))
  44 - access("from$_subquery$_104"."QCSJ_C000000009000001"="LEGACYID"."PERSONID"(+))
  45 - filter("LEGACYID"."CUSTOMDATADEFID"(+)=1)
  46 - access("from$_subquery$_104"."QCSJ_C000000009000001"="SIGNOFF"."EMPLOYEEID"(+))
  49 - filter("WFCAUDITTYPEID"=5)
  50 - access("UA"."PERSONID"(+)="from$_subquery$_104"."QCSJ_C000000009000001")
  53 - filter(UPPER(DECODE("from$_subquery$_102"."QCSJ_C000000010200008",1,'
              ',2,SYS_OP_C2C("from$_subquery$_102"."QCSJ_C000000010200002"),3,SYS_OP_C2C("from$_subquery$_102"."QCSJ_C000000010200003"),4,SYS_OP_
              C2C("L2"."SEARCHNM"),' ')) LIKE UPPER(:B7))
  56 - filter(UPPER(DECODE("A"."ACTYLEVELCNT",1,' ',2,' ',3,SYS_OP_C2C("A"."SEARCHNM"),4,SYS_OP_C2C("L1"."SEARCHNM"),' ')) LIKE
              UPPER(:B6))
  59 - access("RAS"."WFAACTIVITYID"="A"."WFAACTIVITYID")
  60 - filter(UPPER(NVL(SUBSTR("A"."WFADEFAULT1TXT",1,INSTR("A"."WFADEFAULT1TXT",U'-',1,1)-1),U' ')) LIKE SYS_OP_C2C(UPPER(:B10)))
  61 - access("RAS"."EMPLOYEEID"="J"."EMPLOYEEID")
  63 - access("J"."EMPLOYEEID"="E"."EMPLOYEEID")
  64 - access("J"."PERSONID"="P"."PERSONID")
  65 - filter(UPPER("P"."PERSONNUM") LIKE SYS_OP_C2C(UPPER(:B8)))
  68 - access("E"."PAYRULEID"="MP"."PAYRULEID"(+))
  69 - filter("RAS"."EVENTTYPEID"=15 AND "RAS"."DELETEDSW"=0 AND "RAS"."ORPHANEDSW"=0)
  70 - access("A"."WFAACTYHDRID"="H"."WFAACTYHDRID")
  71 - filter("L1"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-1)
  72 - access("A"."PARENTID"="L1"."WFAACTIVITYID"(+))
  73 - filter("L2"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-2)
  74 - access("L1"."PARENTID"="L2"."WFAACTIVITYID"(+))
  75 - access("FR"."FIXEDRULEID"(+)="PRU"."FIXEDRULEID")
  77 - access("LABORWORK"."LABORACCTID"(+)="from$_subquery$_104"."LABORACCTID")
  78 - filter("L3"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-3)
  79 - access("L2"."PARENTID"="L3"."WFAACTIVITYID"(+))
  80 - filter("HOURTYPE"."DELETEDSW"(+)=0)
  81 - access("from$_subquery$_104"."WFAREPACTYSPANID"="HOURTYPE"."WFAREPACTYSPANID"(+) AND "HOURTYPE"."WFAMAPID"(+)=23)
  82 - filter("RT"."HOURSQTY">0)
  83 - access("from$_subquery$_104"."WFAREPACTYSPANID"="RT"."WFAREPACTYSPANID")
  86 - access("LABORHOME"."LABORACCTID"(+)="HA"."LABORACCTID")
  88 - access("WE"."EMPLOYEEID"="HA"."EMPLOYEEID"(+))
  89 - filter("HA"."EXPIRATIONDTM"(+)>=SYSDATE@! AND "HA"."EFFECTIVEDTM"(+)<=SYSDATE@!)
  90 - access("from$_subquery$_041"."QCSJ_C000000002700001"="SIGNOFF"."EMPLOYEEID"(+))
  93 - filter("WFCAUDITTYPEID"=5)
  94 - access("WE"."WORKERTYPEID"="WT"."WORKERTYPEID"(+))
  96 - access("FR"."FIXEDRULEID"(+)="PRU"."FIXEDRULEID")
  98 - access("WE"."PAYRULEID"="PRU"."PAYRULEID"(+))
 100 - access("L3"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-3 AND "L2"."PARENTID"="L3"."WFAACTIVITYID"(+))
 102 - access(ROWID=ROWID)
 105 - access("WE"."PAYRULEID"="PR"."PAYRULEID"(+))
 107 - access("from$_subquery$_041"."QCSJ_C000000002700001"="WE"."EMPLOYEEID"(+))
 109 - access("from$_subquery$_041"."QCSJ_C000000002700001"="LEGACYID"."PERSONID"(+))
 110 - filter("LEGACYID"."CUSTOMDATADEFID"(+)=1)
 111 - access("from$_subquery$_041"."WFAREPACTYSPANID"="HOURTYPE"."WFAREPACTYSPANID"(+))
 112 - filter("HOURTYPE"."WFAMAPID"(+)=23 AND "HOURTYPE"."DELETEDSW"(+)=0)
 113 - access("LABORWORK"."LABORACCTID"(+)="from$_subquery$_041"."LABORACCTID")
 115 - access("UA"."PERSONID"(+)="from$_subquery$_041"."QCSJ_C000000002700001")
 117 - access("from$_subquery$_041"."WFAREPACTYSPANID"="RT"."WFAREPACTYSPANID")
 119 - access("L2"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-2 AND "L1"."PARENTID"="L2"."WFAACTIVITYID"(+))
 121 - access(ROWID=ROWID)
 125 - access("A"."WFAACTYHDRID"="H"."WFAACTYHDRID")
 127 - access("E"."PAYRULEID"="MP"."PAYRULEID"(+))
 129 - access("J"."EMPLOYEEID"="E"."EMPLOYEEID")
 131 - access("J"."PERSONID"="P"."PERSONID")
 133 - access(ROWID=ROWID)
 136 - access("RAS"."EMPLOYEEID"="J"."EMPLOYEEID")
 138 - access("RAS"."WFAACTIVITYID"="A"."WFAACTIVITYID")
 139 - access("L1"."ACTYLEVELCNT"(+)="A"."ACTYLEVELCNT"-1 AND "A"."PARENTID"="L1"."WFAACTIVITYID"(+))
 141 - access(ROWID=ROWID)
 145 - filter("RAS"."DELETEDSW"=0 AND "RAS"."ORPHANEDSW"=0)
 146 - filter("RT"."ADJSTARTDTM" IS NOT NULL)
 147 - filter("CREWCODE"."DELETEDSW"(+)=0)
 148 - access("EVT"."REPACTYSPANID"="CREWCODE"."WFAREPACTYSPANID"(+) AND "CREWCODE"."WFAMAPID"(+)=201)
 149 - filter("BARGE"."DELETEDSW"(+)=0)
 150 - access("EVT"."REPACTYSPANID"="BARGE"."WFAREPACTYSPANID"(+) AND "BARGE"."WFAMAPID"(+)=6)
 151 - filter("BATCHID"."DELETEDSW"(+)=0)
 152 - access("EVT"."REPACTYSPANID"="BATCHID"."WFAREPACTYSPANID"(+) AND "BATCHID"."WFAMAPID"(+)=7)
 153 - filter("EQUIPCODE"."DELETEDSW"(+)=0)
 154 - access("EVT"."REPACTYSPANID"="EQUIPCODE"."WFAREPACTYSPANID"(+) AND "EQUIPCODE"."WFAMAPID"(+)=8)
 155 - filter("WELDCODE"."DELETEDSW"(+)=0)
 156 - access("EVT"."REPACTYSPANID"="WELDCODE"."WFAREPACTYSPANID"(+) AND "WELDCODE"."WFAMAPID"(+)=13)

Note
-----
   - dynamic sampling used for this statement

282 rows selected.




Regards,
Mahi
Re: Optimize the Query [message #448096 is a reply to message #448092] Fri, 19 March 2010 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should change step or line 42, I hesitate between both.

Regards
Michel

[Updated on: Fri, 19 March 2010 08:01]

Report message to a moderator

Re: Optimize the Query [message #448103 is a reply to message #448092] Fri, 19 March 2010 08:30 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would guess the problem is with the complexity of your underlying views. I might suggest the following:

manually partition the query by splitting the query up into a union all of two parts based on :B1.

where :b1 > 0
and evt.eventdtm >= wtk.startdate
and evt.eventdtm < wtk.enddate
...
union all
where not (:b1 > 0)
and evt.eventdtm >= :b14
and evt.eventdtm < :b13

Your goal is to change the query plan by opening up knolwedge about eventdtm.

            AND evt.eventdtm >=
                            CASE
                               WHEN :b1 > 0
                                  THEN wtk.startdate
                               ELSE TO_DATE (:b14)
                            END
            AND evt.eventdtm <
                              CASE
                                 WHEN :b1 > 0
                                    THEN wtk.enddate
                                 ELSE TO_DATE (:b13)
                              END

This is just a guess. In the end you may have to do a lot of tuning of your views to get things to go faster.

Also, if there are inherent problems in your views, this strategy could backfire and only make things worse so you need to test well.

Good luck, Kevin
Previous Topic: Slow connection in Linux 64 bit oracle 9i
Next Topic: 11g sql tuning(urgent)
Goto Forum:
  


Current Time: Sun Dec 04 08:25:26 CST 2016

Total time taken to generate the page: 0.14587 seconds