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 processedReceived on Fri Feb 16 2001 - 14:33:08 CST
![]() |
![]() |