| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Improving the speed of the below query
Do you have indices on the various xx_key fields (which look like primary and foreign keys to me).
Also the DECODE in the where clause looks suspect to me. What is the logic behind it?
Finally your date comparison
> AND TRUNC(FOT.FO_COMPLETION_DT) BETWEEN
> TO_DATE('01-DEC-00','DD-MON-YY') AND TO_DATE('31-DEC-00','DD-MON-YY')
*may* run better if rewritten as
AND FOT.FO_COMPLETION_DT BETWEEN
TO_DATE('01-DEC-00','DD-MON-YY') AND TO_DATE('01-JAN-01','DD-MON-YY') i.e
don't trunc the data column and choose your between period slightly
differently. Obviously you also need an index on this column.
-- Niall Litchfield Oracle DBA Audit Commission UK "James Williams" <techsup_at_mindspring.com> wrote in message news:3a8d8e30.1728905_at_news.mindspring.com...Received on Mon Feb 19 2001 - 04:31:33 CST
> 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
>
 |
 |