Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!oleane.net!oleane!news.in2p3.fr!in2p3.fr!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!v46g2000cwv.googlegroups.com!not-for-mail From: ronnie_yours@yahoo.com Newsgroups: comp.databases.oracle.server Subject: Re: Query Runs faster in 10G Date: 3 Mar 2006 13:31:24 -0800 Organization: http://groups.google.com Lines: 132 Message-ID: <1141421484.065714.236920@v46g2000cwv.googlegroups.com> References: <1141417760.979675.54930@z34g2000cwc.googlegroups.com> <1141420279.500669.284230@p10g2000cwp.googlegroups.com> NNTP-Posting-Host: 64.201.8.130 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1141421489 16098 127.0.0.1 (3 Mar 2006 21:31:29 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 3 Mar 2006 21:31:29 +0000 (UTC) User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: v46g2000cwv.googlegroups.com; posting-host=64.201.8.130; posting-account=tX4fGwwAAAA7jNMfYzgk596bkdoCBp9f Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262740 I just ran a tkprof in 8i and 10g. Below are the results. Hopefully they might help shed some light on the issue 8I TKPROF OUTPUT ------------------------------ 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 l.region_id = r.child_region_id AND n.story_id = l.story_id AND n.product_location_id >= 500 AND n.product_location_id < 600 AND lp.product_location_id(+) = n.product_location_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.05 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 63 2.46 17.75 47019 210987 58 6206 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66 2.50 17.80 47019 210987 58 6206 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 94 (CODB) Rows Row Source Operation ------- --------------------------------------------------- 6206 HASH JOIN OUTER 6206 HASH JOIN 23054 NESTED LOOPS 80954 TABLE ACCESS FULL INT_NEWS_LOCATIONS 23054 INDEX UNIQUE SCAN (object id 205047) 31825 TABLE ACCESS FULL NEWS_STORY 53 TABLE ACCESS FULL LKP_PRODUCT_LOCATION_NAME Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 6206 HASH JOIN (OUTER) 6206 HASH JOIN 23054 NESTED LOOPS 80954 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'INT_NEWS_LOCATIONS' 23054 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_REGION_CHILDPARENTID' (UNIQUE) 31825 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'NEWS_STORY' 53 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'LKP_PRODUCT_LOCATION_NAME' 10G TKPROF OUTPUT ----------------------------------- 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 l.region_id = r.child_region_id AND n.story_id = l.story_id AND n.product_location_id >= 500 AND n.product_location_id < 600 AND lp.product_location_id(+) = n.product_location_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 124 1.47 1.80 56194 59060 0 12212 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 128 1.48 1.80 56194 59060 0 12212 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 66 (CODB) Rows Row Source Operation ------- --------------------------------------------------- 6106 HASH JOIN (cr=29530 pr=28104 pw=0 time=513195 us) 788 INDEX RANGE SCAN PK_REGION_CHILDPARENTID (cr=5 pr=0 pw=0 time=1646 us)(object id 49566) 19780 HASH JOIN RIGHT OUTER (cr=29525 pr=28104 pw=0 time=756825 us) 14 MAT_VIEW ACCESS BY INDEX ROWID LKP_PRODUCT_LOCATION_NAME (cr=2 pr=0 pw=0 time=212 us) 14 INDEX RANGE SCAN SYS_C006198 (cr=1 pr=0 pw=0 time=31 us)(object id 49661) 19780 HASH JOIN (cr=29523 pr=28104 pw=0 time=596830 us) 80028 MAT_VIEW ACCESS FULL INT_NEWS_LOCATIONS (cr=500 pr=496 pw=0 time=161678 us) 31575 MAT_VIEW ACCESS FULL NEWS_STORY (cr=29023 pr=27608 pw=0 time=505863 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 6106 HASH JOIN 788 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_REGION_CHILDPARENTID' (INDEX (UNIQUE)) 19780 HASH JOIN (RIGHT OUTER) 14 MAT_VIEW ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LKP_PRODUCT_LOCATION_NAME' (MAT_VIEW) 14 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SYS_C006198' (INDEX (UNIQUE)) 19780 HASH JOIN 80028 MAT_VIEW ACCESS MODE: ANALYZED (FULL) OF 'INT_NEWS_LOCATIONS' (MAT_VIEW) 31575 MAT_VIEW ACCESS MODE: ANALYZED (FULL) OF 'NEWS_STORY' (MAT_VIEW) Thanks Ron