Re: SQL performance tuning

From: John Morais <jmorais_at_rochester.rr.com>
Date: Thu, 14 Jun 2001 13:14:38 GMT
Message-ID: <2N2W6.27475$3y3.4367353@typhoon.nyroc.rr.com>

If using literals usually means you have to create histograms change form a width based to height based and I am not a fan of optimization. There is always the parameter optimizer_index_cost_adj :

I have attached the following :

In Oracle 8 indexes are not being recognized and full table scans are being performed according
to the explain plan. Because full table scans are being used by the optimizer,
the results of the query are taking a long time to be returned.

You are using Cost Based Optimization and have analyzed all the indexes, tables, and partitions involved.

SQL> analyze table (tablename) compute statistics;

You have even tried deleting statistics and using rule based, but it made no difference. After doing some research you found the OPTIMIZER_INDEX_COST_ADJ
parameter in INIT.ORA and think it might make difference in how the optimizer
uses indexes. After adjusting this parameter the optimizer changed its behavior for access path selection and used the indexes involved in the query.

Solution Description:

---

After adjusting the parameter OPTIMIZER_INDEX_COST_ADJ from 100 to 10 the query used indexes.

SVRMGR> show parameter optimizer_index_cost_adj;

NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
optimizer_index_cost_adj            integer 100

SVRMGR> alter session set optimizer_index_cost_adj = 10; Statement processed.
SVRMGR> show parameter optimizer_index_cost_adj;

NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
optimizer_index_cost_adj            integer 10

"David Sisk" <davesisk_at_ipass.net> wrote in message news:TBVV6.40311$ru2.10460437_at_typhoon.southeast.rr.com...
> This usually isn't a very popular suggestion, but you might want to try
> using literals rather than bind variables. The optimizer can, in theory,
> construct a more accurate plan with literals, although you take a hit on
 the
> parsing side (which is probably not an issue in comparison).
>
> Regards,
> Dave
>
> 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 Thu Jun 14 2001 - 08:14:38 CDT