Re: SQL performance tuning

On Thu, 14 Jun 2001 03:55:56 GMT, Alex Filonov <afilonov_at_pro-ns.net> wrote:

Dropped about 20 seconds off the query!

>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 Tue Jan 25 2005 - 06:00:09 CST