Re: SQL performance tuning

Good ideas. In addition:

If you still have plan with hash joins, increase hash_area_size to at least 8M:

alter session set hash_area_size = 8000000; (16M is even better). Hash join performance depends too much on hash area size.

Oleg Fedorov wrote:

> Hi,
>
> First of all,
>
> SO.DT_SO_CMPLT is NULL or
> SO.DT_SO_CMPLT>to_date('20010501','yyymmdd')
>
> looks better(Oracle do not need to convert SO.DT_SO_CMPLT to char for
> each record and can use index for this field) then
>
> to_char(SO.DT_SO_CMPLT,'yyyymmdd') > '20010501' or
> to_char(SO.DT_SO_CMPLT,'yyyymmdd') is null
>
> If that's not enough let us know.
>
> Good luck,
> Oleg
>
> techsup_at_mindspring.com (James Williams) 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 - 22:55:56 CDT