Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Runs faster in 10G
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
------- ------ -------- ---------- ---------- ---------- ----------
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: CHOOSE6206 HASH JOIN (OUTER)
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
------- ------ -------- ---------- ---------- ---------- ----------
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_ROWS6106 HASH JOIN
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
Received on Fri Mar 03 2006 - 15:31:24 CST