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: Query Runs faster in 10G

Re: Query Runs faster in 10G

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 03 Mar 2006 21:56:44 +0100
Message-ID: <dua9q7$q31$1@news5.zwoll1.ov.home.nl>


ronnie_yours_at_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...
Received on Fri Mar 03 2006 - 14:56:44 CST

Original text of this message

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