Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsgate.cistron.nl!news2.euro.net!newshub3.home.nl!newshub1.home.nl!home.nl!not-for-mail
From: Frank van Bortel <frank.van.bortel@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Query Runs faster in 10G
Date: Fri, 03 Mar 2006 21:56:44 +0100
Organization: @Home Benelux
Lines: 73
Message-ID: <dua9q7$q31$1@news5.zwoll1.ov.home.nl>
References: <1141417760.979675.54930@z34g2000cwc.googlegroups.com>
Reply-To:  frank.van.bortel@gmail.com
NNTP-Posting-Host: cc28855-a.hnglo1.ov.home.nl
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: news5.zwoll1.ov.home.nl 1141418631 26721 82.75.95.92 (3 Mar 2006 20:43:51 GMT)
X-Complaints-To: usenet@corp.home.nl
NNTP-Posting-Date: Fri, 3 Mar 2006 20:43:51 +0000 (UTC)
User-Agent: Thunderbird 1.5 (Windows/20051201)
In-Reply-To: <1141417760.979675.54930@z34g2000cwc.googlegroups.com>
X-Enigmail-Version: 0.94.0.0
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262734

ronnie_yours@yahoo.com wrote:
> Hi,
> 
> I have a query which takes 0.2 seconds in 10G but takes 18 seconds in
> 8i.
> 
> the query is
> 
>  SELECT n.story_id, n.date_of_story AS sdate, n.headline,
> lp.product_location_description
>         FROM news_story n, int_news_locations l, flat_regionhierarchy
> r, lkp_product_location_name lp
>        WHERE r.parent_region_id = 50015
>          AND n.product_location_id between 500 and 599
>          AND l.region_id = r.child_region_id
>          AND lp.product_location_id(+) = n.product_location_id
>          AND n.story_id = l.story_id;
> 
> EXPLAIN PLAN IN 10G
> ==================
> 
> SELECT STATEMENT, GOAL =
> ALL_ROWS			Cost=6703	Cardinality=40726	Bytes=5335106
>  HASH JOIN			Cost=6703	Cardinality=40726	Bytes=5335106
>   INDEX RANGE SCAN	Object owner=CODB	Object
> name=PK_REGION_CHILDPARENTID	Cost=4	Cardinality=788	Bytes=7880
>   HASH JOIN RIGHT OUTER			Cost=6698	Cardinality=33077	Bytes=4002317
>    MAT_VIEW ACCESS BY INDEX ROWID	Object owner=CODB	Object
> name=LKP_PRODUCT_LOCATION_NAME	Cost=2	Cardinality=13	Bytes=481
>     INDEX RANGE SCAN	Object owner=CODB	Object
> name=SYS_C006198	Cost=1	Cardinality=13
>    HASH JOIN			Cost=6695	Cardinality=33077	Bytes=2778468
>     MAT_VIEW ACCESS FULL	Object owner=CODB	Object
> name=INT_NEWS_LOCATIONS	Cost=112	Cardinality=80624	Bytes=806240
>     MAT_VIEW ACCESS FULL	Object owner=CODB	Object
> name=NEWS_STORY	Cost=6364	Cardinality=32557	Bytes=2409218
> 
> EXPLAIN PLAN IN 8I
> ================
> 
> SELECT STATEMENT, GOAL =
> CHOOSE			Cost=4751	Cardinality=3872	Bytes=464640
>  HASH JOIN OUTER			Cost=4751	Cardinality=3872	Bytes=464640
>   HASH JOIN			Cost=4745	Cardinality=3872	Bytes=329120
>    NESTED LOOPS			Cost=41	Cardinality=16500	Bytes=264000
>     TABLE ACCESS FULL	Object owner=CODB	Object
> name=INT_NEWS_LOCATIONS	Cost=41	Cardinality=80930	Bytes=647440
>     INDEX UNIQUE SCAN	Object owner=CODB	Object
> name=PK_REGION_CHILDPARENTID		Cardinality=63	Bytes=504
>    TABLE ACCESS FULL	Object owner=CODB	Object
> name=NEWS_STORY	Cost=4679	Cardinality=17983	Bytes=1240827
>   TABLE ACCESS FULL	Object owner=CODB	Object
> name=LKP_PRODUCT_LOCATION_NAME	Cost=1	Cardinality=53	Bytes=1855
> 
> 
> Why is it behaving differently in 10G and how should i improve
> performance in 8i.
> 
> Thanks
> Ron
> 

Apart from the obvious "yet another reason to upgrade to 10g", your
execution plans differ. One, because ALL_ROWS is the goal in 10g, and
CHOOSE in 8i.
Don't recall if ALL_ROWS appears in an explain plan unless it it set,
either at instance, or at session level.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...