Re: Higher Consistent Gets...

From: <Jared.Still_at_radisys.com>
Date: Fri, 14 Sep 2001 16:10:23 -0700
Message-ID: <F001.0038F86D.20010914161019@fatcity.com>

!! Please do not post Off Topic to this List !!

Raj,

Which query is fastest? You don't have any timing information.

Make sure that timed_statistics is on in the database:

alter system set timed_statistics = true;

Run your query with trace on:

alter session set sql_trace = true;

Then run the resulting trace files through tkprof.

You'll have much more information to work with.

Jared

                                                                                       
                                     
                    Raj Gopalan                                                        
                                     
                    <raj.gopalan_at_netdecisi       To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>        
                    ons.co.uk>                   cc:                                   
                                     
                    Sent by:                     Subject:     Higher Consistent 
Gets...                                     
                    root_at_fatcity.com                                                   
                                     
                                                                                       
                                     
                                                                                       
                                     
                    09/14/01 09:55 AM                                                  
                                     
                    Please respond to                                                  
                                     
                    ORACLE-L

!! Please do not post Off Topic to this List !!

I am having problem with a query. This query fetches rows from a table which
has 15 million rows.

The problem is, when I execute this query with subquery, the consistent gets
are 4700. Where us without the subquery the consistent gets are just 400. If
I execute the subquery alone, the consistent gets are just 5.

Here is the main query with subquery results in cons.gets of 4700:

select pd.holiday_id holidayID, pd.package_number l_package_number,

min(pd.tfr_price) l_tpr_price
from fr_search_query pd
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/rrrr')

                             and TO_DATE('13/10/2001','dd/mm/rrrr')
   and pd.location_code in (select location_code
                              from gn_location
                            connect by prior location_code=parent_code
                              start with location_code='3142')

and ROWNUM < 301

 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
          pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     96   SORT (ORDER BY)
     96    SORT (GROUP BY)
     96     COUNT (STOPKEY)
     96      NESTED LOOPS
   5137       INLIST ITERATOR
   5138        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'FR_SEARCH_QUERY'
   8566         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
     96       VIEW
   5136        SORT (UNIQUE)
      1         CONNECT BY
      2          INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
                     'PK_GN_LOCATION' (UNIQUE)
      1          TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
      1          TABLE ACCESS (FULL) OF 'GN_LOCATION'

Running just the subquery results in cons.gets of just 5.

  select location_code
    from gn_location
connect by prior location_code=parent_code    start with location_code='3142'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   CONNECT BY
      2    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
               (UNIQUE)
      1    TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
      1    TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'

(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
  INET: raj.gopalan_at_netdecisions.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Sep 14 2001 - 18:10:23 CDT