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 -> Re: SQL performance tuning

Re: SQL performance tuning

From: John Morais <jmorais_at_rochester.rr.com>
Date: Wed, 13 Jun 2001 22:53:06 GMT
Message-ID: <m9SV6.189824$f85.29113763@typhoon.nyroc.rr.com>

Create function indexes on the date fields ie create function index to_char((trunc( SO.DT_SO_CMPLT,'date format')) etc etc..

and use a between ... dependin gon the number of distinct vaules in CD_SPEC_DT compared to the number of rows create and index on that column

and if none of that works try the rule hint

second
"James Williams" <techsup_at_mindspring.com> wrote in message news:3b27a302.157582740_at_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 - 17:53:06 CDT

Original text of this message

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