Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Improving the speed of the below query

Improving the speed of the below query

From: James Williams <techsup_at_mindspring.com>
Date: Fri, 16 Feb 2001 20:33:08 GMT
Message-ID: <3a8d8e30.1728905@news.mindspring.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US