Re: help needed in tuning query

From: srivenu <srivenu_at_hotmail.com>
Date: 5 Aug 2004 02:59:17 -0700
Message-ID: <1a68177.0408050159.26abfc96@posting.google.com>

Sharkie,
SELECT contentintid

              FROM content
             WHERE contenttypeintid = 1
               AND statusintid in (1,2)
               AND to_char(content.adddate,'YYYYMMDD') >=
p_search.get_search_date('',1)
               AND to_char(content.adddate,'YYYYMMDD') <=

p_search.get_search_date('',2)
AND upper(content.searchstring) like '%ELECTRONIC%'  ORDER BY adddate desc
/

The calls to p_search.get_search_date will occur for every row that is evaluated.
Since that function will return the same data, why cant u call it just once and use that value as a bind variable. Since in the function you are calling dual, obviously, there will be gets on the DUAL table that will count towards the gets. Can you paste the code of the p_search.get_search_date function ?

Even without the function call, You have 1856 consistent gets for 3 rows.
Thats a bit high showing that there is a throw away. To find the actual throwaway,
start SQL*PLUS, start 10046 trace at level 12, run the SQL, close the SQL*PLUS and format the TKPROF.
You will get the actual plan used during execution in the TKPROF file with ROWS and ROW SOURCE heading. If you can paste that, we can see where the actual throw away is occuring. regards
Srivenu Received on Thu Aug 05 2004 - 04:59:17 CDT