SQL performance tuning

From: James Williams <techsup_at_mindspring.com>
Date: Wed, 13 Jun 2001 17:39:16 GMT
Message-ID: <3b27a302.157582740@news.mindspring.com>

Tuning the below query for a user. Have tried all manners of indexes, hints, and still can't get it to budge off of two minutes. Oracle 8.1.6.3 on Sun Solaris. Statistics are up to date. This query will be inside a PL/SQL program so bind variables are in the real deal.

QL>

  1 SELECT to_char(SO.KY_SO_NO) order_number,

  2           to_char(SO.DT_SO_PEND,'mm/dd/yy') appointment_date_c,
  3           to_char(to_date(SO.TM_APPT,'hh24mi'),'hh:mi am')
appointment_time_
c,
  4           to_char(SO.DT_SO_CMPLT,'mm/dd/yy') completion_date_c,
  5           ' ' completion_time_c,
  6           so.cd_mup_ord_type,
  7           decode(SO.CD_SO_STAT, 54, 'CMPL', 57, 'VOID')
order_status,
  8           SO.NM_CUST_1,
  9           (P.AD_SERV_STR_NO ||' '|| SN.AD_SERV_CDL_DIR  ||' '||

SN.AD_SERV_S
TR_NM ||' '|| SN.AD_SERV_STR_SFIX||' '|| SN.AD_SERV_SFIX ||', '|| P.AD_SERV_STRU
C ) premise_address,

 10           SN.AD_SERV_CITY,
 11           to_char(SN.AD_SERV_ZIP),
 12           'C' data_source,
 13  ' ',
 14  ' ',

 15 ' ',
 16 ' '

 17      FROM SERV_ORD SO, PREMISE P, STREET_NAME SN
 18     WHERE (( SO.CD_SPEC_DT = 'BYPS' OR SO.CD_SPEC_DT = 'PROR' ) or

CD_SO_STA
T = 57) and
 19 ( to_char(SO.DT_SO_CMPLT,'yyyymmdd') > '20010501' or to_char(SO.DT_SO_CMP
LT,'yyyymmdd') is null) and
 20 ( SO.KY_SO_NO = P.KY_SO_NO(+) AND SO.DT_SO_PEND = P.DT_SO_PEND(+) AND SO
.KY_PREM_NO = P.KY_PREM_NO(+) ) AND
 21* (P.KY_SO_NO = SN.KY_SO_NO(+) AND P.DT_SO_PEND = SN.DT_SO_PEND(+)
AND P.KY_STR_NM = SN.KY_STR_NM(+))
 22

Elapsed: 00:02:14.12

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28213 Card=193417 By
          tes=25144210)

   1    0   HASH JOIN (OUTER) (Cost=28213 Card=193417 Bytes=25144210)
   2    1     HASH JOIN (OUTER) (Cost=24369 Card=193417 Bytes=16633862
          )

   3    2       TABLE ACCESS (FULL) OF 'SERV_ORD' (Cost=19049 Card=193
          417 Bytes=10637935)

   4    2       TABLE ACCESS (FULL) OF 'PREMISE' (Cost=4819 Card=27266
          90 Bytes=84527390)

   5    1     TABLE ACCESS (FULL) OF 'STREET_NAME' (Cost=2779 Card=327
          2028 Bytes=143969232)

Statistics

---

        170  recursive calls
         12  db block gets
     312648  consistent gets
     242119  physical reads
          0  redo size
       1055  bytes sent via SQL*Net to client
        313  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          0  rows processed

Received on Wed Jun 13 2001 - 12:39:16 CDT