Home » RDBMS Server » Performance Tuning » Optimize the Query (Oracle 9i)
| Optimize the Query [message #448092] |
Fri, 19 March 2010 07:47  |
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 #448103 is a reply to message #448092] |
Fri, 19 March 2010 08:30  |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 08:30:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
