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

Query Runs faster in 10G

From: <ronnie_yours_at_yahoo.com>
Date: 3 Mar 2006 12:29:21 -0800
Message-ID: <1141417760.979675.54930@z34g2000cwc.googlegroups.com>


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 Received on Fri Mar 03 2006 - 14:29:21 CST

Original text of this message

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