Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Higher Consistent Gets...
!! 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 !!
Hi
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'
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-LReceived on Fri Sep 14 2001 - 18:10:23 CDT
(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).