Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL performance tuning
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 100SVRMGR> alter session set optimizer_index_cost_adj = 10; Statement processed.
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