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: Best way to tune the below query.

Re: Best way to tune the below query.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Jan 2001 11:32:18 -0000
Message-ID: <978694171.10614.0.nnrp-07.9e984b29@news.demon.co.uk>

You don't seem to have any stats on your tables, so are running RULE based optimisation.

option one -
alter session set optimizer_mode=first_rows;

option two -
analyze the tables to see what happens when the statistics force Oracle to choose ALL_ROWS.

Really it looks as if

    full_address like '4112 DUNWOODY%'
should be pretty selective, so might be the driver for the query under CBO.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



James Williams wrote in message <3a55abea.83287056_at_nntp.mindspring.com>...

> Below is the query and the explain plan. I am trying to get this
>query from 30 seconds down to about 5 seconds.
>
>1 Select
>2 it_order.cis_ordr_nbr order_number,
>3 to_char(to_date(it_order.appointment_dte,'yyyymmdd'),'mm/dd/yy')
>appointment_date_c,
>4 to_char(to_date(it_order.appointment_tme, 'hh24miss'),'hh:mi
>am') appointment_time_c,
>5 to_char(to_date(iv_order_trak.cmpl_dte,'yyyymmdd'),'mm/dd/yy')
>completion_date_c,
>6 to_char(to_date(iv_order_trak.cmpl_tme, 'hh24miss'),'hh:mi am')
>completion_time_c,
>7 it_order.ordr_typ_cd,
>8 it_order.ordr_stat_cd order_status,
>9 ih_cust_tenant.acct_name,
>10 js_location.complete_address premise_address,
>11 js_location.city,
>12 js_location.zip_9,
>13 'M' data_source,
>14 it_order.appointment_dte,
>15 it_order.appointment_tme,
>16 /*+ HASH(iv_order_trak) */
>17 iv_order_trak.cmpl_dte,
>18 iv_order_trak.cmpl_tme
>19 From js_location,ih_cust_tenant,iv_order_trak,it_order
>20 WHERE IT_ORDER.CIS_ACCT_NBR = IH_CUST_TENANT.CIS_ACCT_NBR(+)
>and
>21 IT_ORDER.LOCATION_ID = JS_LOCATION.LOCATION_ID and
>22 ( IT_ORDER.ordr_stat_cd <> 'VOID' or IT_ORDER.CIS_ORDR_NBR like
>'M%' ) and
>23 ( IT_ORDER.ordr_uid = iv_order_trak.ordr_uid and
>24 ( iv_order_trak.cmpl_dte >= to_char(sysdate - 30 , 'yyyymmdd' )
>or iv_order_trak.cmpl_dte is null ) and
>25 iv_order_trak.trak_seq_nbr = it_order.trak_seq_nbr )
>26* and JS_LOCATION.COMPLETE_ADDRESS like '4112 DUNWOODY%'
>QL> /
>
>o rows selected
>
>
>xecution Plan
>---------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 NESTED LOOPS
> 2 1 NESTED LOOPS (OUTER)
> 3 2 NESTED LOOPS
> 4 3 TABLE ACCESS (FULL) OF 'IT_ORDER'
> 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'IV_ORDER_TRAK'
> 6 5 INDEX (UNIQUE SCAN) OF 'IV_ORDER_TRAK_PK' (UNIQUE)
> 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'IH_CUST_TENANT'
> 8 7 INDEX (UNIQUE SCAN) OF 'IH_CUST_TENANT_PK' (UNIQUE)
> 9 1 TABLE ACCESS (BY INDEX ROWID) OF 'JS_LOCATION'
>10 9 INDEX (UNIQUE SCAN) OF 'JS_LOCATION_PK' (UNIQUE)
>
>
>
>
>tatistics
>---------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 280456 consistent gets
> 0 physical reads
> 0 redo size
> 798 bytes sent via SQL*Net to client
> 1632 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
>
>
>
Received on Fri Jan 05 2001 - 05:32:18 CST

Original text of this message

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