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: <ronnie_yours_at_yahoo.com>
Date: 3 Mar 2006 13:31:24 -0800
Message-ID: <1141421484.065714.236920@v46g2000cwv.googlegroups.com>


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 Received on Fri Mar 03 2006 - 15:31:24 CST

Original text of this message

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