| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.server -> Improving the speed of the below query
Just ran an analyze. Also, trying various hints. What else would some of the tuning quru's recommend?
SELECT
EMP.MUP_USER_ID MUP_USER_ID
, (nvl(FP.POINTS,0)) SUM_POINTS
, (TRUNC(FOT.FO_COMPLETION_DT)) CMPL_DT
, (EMP.AGLC_ORGANIZATION_NAME) AGLC_ORGANIZATION_NAME
, (OTV.COMPLETION_TIME + OTV.TRAVEL_TIME)/60 POTENTIAL_HRS
, 'Z' SUPV_USER_ID
FROM
FIELD_ORDER_OUTCOME FOT
, FIELD_ORDER FO
, SERVICE_ORDER SO
, EMP_OVERVIEW_DW EMP
, FSR_POINTS FP
,ORDER_TYPE OT
, ORDER_TIME_VW OTV
WHERE
FO.COMPL_STATUS_CD IN ( 'CMPL','CGI')
AND
FOT.FO_KEY=FO.FO_KEY
AND fo.so_key=so.so_key
AND so.ORDER_TYPE_KEY=OT.ORDER_TYPE_KEY(+)
-----AND FP.TASK(+) = OT.ORDER_TYPE
AND FP.TASK =
DECODE(FO.COMPL_STATUS_CD,'CMPL',OT.ORDER_TYPE,'CGI','CGI',OT.ORDE
R_TYPE)
AND fo.COMPL_USER_key=emp.employee_key
AND OTV.SOURCE_ORDER_NBR = FO.SOURCE_ORDER_NBR AND
OTV.MUP_USER_ID = EMP.MUP_USER_ID AND
(OTV.COMPLETION_FLAG = 1 OR OTV.CGI_FLAG = 1)
AND TRUNC(FOT.FO_COMPLETION_DT) BETWEEN
TO_DATE('01-DEC-00','DD-MON-YY') AND TO_DATE('31-DEC-00','DD-MON-YY')
AND EMP.AGLC_ORG_ID = '9'
AND emp.AGLC_ORG_ID <> 1 --- excluded UPI DUMMY
3887 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=91388 Card=1 Bytes=2
11)
1 0 NESTED LOOPS (Cost=91388 Card=1 Bytes=211)
2 1 NESTED LOOPS (OUTER) (Cost=91387 Card=1 Bytes=202)
3 2 NESTED LOOPS (Cost=91386 Card=1 Bytes=193)
4 3 HASH JOIN (Cost=91385 Card=1 Bytes=186)
5 4 NESTED LOOPS (OUTER) (Cost=297 Card=39 Bytes=4329)
6 5 HASH JOIN (Cost=297 Card=39 Bytes=4212)
7 6 HASH JOIN (Cost=267 Card=624 Bytes=28080)
8 7 TABLE ACCESS (FULL) OF 'FIELD_ORDER_OUTCOME'
(Cost=87 Card=624 Bytes=6864)
9 7 TABLE ACCESS (FULL) OF 'FIELD_ORDER' (Cost=1
10 Card=109172 Bytes=3711848)
10 6 VIEW OF 'TRUE_EMP_SUPV_VIEW' (Cost=25 Card=499
Bytes=31437)
11 10 HASH JOIN (OUTER) (Cost=25 Card=499 Bytes=16
467)
12 11 NESTED LOOPS (Cost=9 Card=499 Bytes=13473)
13 12 TABLE ACCESS (FULL) OF 'AGLC_ORGANIZATIO
N' (Cost=1 Card=2 Bytes=26)
14 12 TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=
4 Card=7988 Bytes=111832)
15 11 VIEW OF 'CURRENT_EMP_SUPV_VIEW' (Cost=14 C
ard=18 Bytes=108)
16 15 SORT (ORDER BY) (Cost=14 Card=18 Bytes=4
68)
17 16 FILTER
18 17 NESTED LOOPS (Cost=12 Card=18 Bytes=
468)
19 18 HASH JOIN (Cost=12 Card=18 Bytes=4
14)
20 19 TABLE ACCESS (FULL) OF 'EMP_SUPE
RVISOR' (Cost=1 Card=1019 Bytes=13247)
21 19 TABLE ACCESS (FULL) OF 'EMPLOYEE
' (Cost=4 Card=7988 Bytes=79880)
22 18 INDEX (UNIQUE SCAN) OF 'SYS_C00925
' (UNIQUE)
23 17 SORT (AGGREGATE)
24 23 TABLE ACCESS (FULL) OF 'EMP_SUPERV
ISOR' (Cost=1 Card=3 Bytes=42)
25 5 INDEX (UNIQUE SCAN) OF 'SYS_C001137' (UNIQUE)
26 4 VIEW OF 'ORDER_TIME_VW' (Cost=91040 Card=40112 Byt
es=3008400)
27 26 FILTER
28 27 SORT (GROUP BY) (Cost=91040 Card=40112 Bytes=2
045712)
29 28 HASH JOIN (Cost=12614 Card=2015653 Bytes=102
798303)
30 29 TABLE ACCESS (FULL) OF 'WORK_ACTIVITY' (Co
st=1 Card=165 Bytes=990)
31 29 HASH JOIN (Cost=11043 Card=2015653 Bytes=9
0704385)
32 31 TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=
4 Card=7988 Bytes=79880)
33 31 TABLE ACCESS (FULL) OF 'EMPLOYEE_ACTIVIT
Y_DURATION' (Cost=683 Card=2015653 Bytes=70547855)
34 3 TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE_ORDER' (Co
st=1 Card=250685 Bytes=1754795)
35 34 INDEX (UNIQUE SCAN) OF 'SYS_C001126' (UNIQUE)
36 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_TYPE' (Cost=1
Card=108 Bytes=972)
37 36 INDEX (UNIQUE SCAN) OF 'SYS_C001080' (UNIQUE)
38 1 TABLE ACCESS (BY INDEX ROWID) OF 'FSR_POINTS' (Cost=1 Ca
rd=123 Bytes=1107)
39 38 INDEX (UNIQUE SCAN) OF 'PK_FSR_POINTS' (UNIQUE)
Statistics
13291 recursive calls
8206 db block gets
51797 consistent gets
74316 physical reads
313380 redo size
314004 bytes sent via SQL*Net to client
29173 bytes received via SQL*Net from client
261 SQL*Net roundtrips to/from client
35 sorts (memory)
1 sorts (disk)
3887 rows processed
Received on Fri Feb 16 2001 - 14:33:08 CST
![]() |
![]() |