Oracle-L: RE: slow SQL query, diagnosis using 10046 trace event

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 06 May 2002 01:58:26 -0800
Message-ID: <F001.0045894B.20020506015826@fatcity.com>

('binary' encoding is not supported, stored as-is)
Suhen,

Look at what you are doing :

>Query Plan
>
>select orgplvee.org_lvl_parent
>,prdplvee.prd_lvl_parent ,
> (NVL(invbalee.on_hand_qty,0)+NVL(to_intrn_qty,0)),
>(NVL(invbalee.on_hand_retl,0)+NVL(to_intrn_retl,0)),
>(NVL(invbalee.on_hand_cost,0)+NVL(to_intrn_cost,0))
>from
> invbalee ,orgplvee ,prdplvee where
>(orgplvee.org_lvl_child=
> invbalee.org_lvl_child and
>prdplvee.prd_lvl_child=invbalee.prd_lvl_child)
> order by
>orgplvee.org_lvl_parent,prdplvee.prd_lvl_parent
>
>
>Execution Plan
>Id Par Pos Ins Plan
>--- ---- ---- ----
>----
> 0 #### SELECT STATEMENT (choose)
>Cost
>(48836,5333714,170678848)
> 1 0 1 SORT (order by) Cost
>(48836,5333714,170678848)
> 2 1 1 HASH JOIN Cost
>(1705,5333714,170678848)
> 3 2 1 INDEX (analyzed) UNIQUE
>JDAPROD ORGPLVEEP1 (fast
>full scan) Cost (1,1073,5365)
> 4 2 2 HASH JOIN Cost
>(1690,1357040,36640080)
> 5 4 1 INDEX (analyzed)
>UNIQUE JDAPROD PRDPLVEEP1
>(fast full scan) Cost (16,100070,8005
> 6 4 2 1 TABLE ACCESS
>(analyzed) JDAPROD INVBALEE
>(full) Cost (746,1257164,23886116)
>
>
>Each fetch call returned an average of 2 rows.
>

Your query generates a full scan of INVBALEE which looks pretty big. However, you 'feed' it no search criteria other than join conditions on ORGPLVEE and PRDPLVEE. Looks to me like a star query. Since you return few rows, the most sensible approach would probably be to use nested loops rather than the hash joins the optimizer jumps for. Summary :

You cannot do otherwise than scan a table (or possibly an index). Pick the smallest one.
Force the use of nested loops to get data from the other tables.

I am no great fan of hints, but it looks to me like time to play around with ORDERED and USE_NL.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon May 06 2002 - 04:58:26 CDT