| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: help needed in tuning query
srivenu_at_hotmail.com (srivenu) wrote in message
Thanks a lot for this answer. Replacing the date functions in the query with variables helped a lot.
> Can you paste the code of the p_search.get_search_date function ?
Yes, sure:
<get_search_date_code>
function get_search_date(p_indate in char, p_def in int) return char is
v_return char(8):='';
v_temp char(2):='';
begin
if(p_indate is null)then --no date passed, calculate it
if(p_def=1)then --startdate
select to_char(add_months(sysdate,-1),'YYYYMMDD') into v_return from dual;
else --enddate
select to_char(sysdate,'YYYYMMDD') into v_return from dual;
end if;
else
v_return:=p_indate;
end if;
return v_return;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RETURN('-1');
end get_search_date;
</get_search_date_code>
> 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.
OK, here it is, with dates hardcoded though:
<TKPROF_output>
TKPROF: Release 8.1.6.1.0 - Production on Wed Aug 11 13:46:08 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Trace file: ora_11199.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set events '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 111 (USER)
FROM content
WHERE contenttypeintid = 1
AND statusintid in (1,2)
AND to_char(content.adddate,'YYYYMMDD') >= '20040711'
--p_search.get_search_date('',1)
AND to_char(content.adddate,'YYYYMMDD') <= '20040811'
--p_search.get_search_date('',2)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.64 0.64 0 1721 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.64 0.64 0 1721 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 111
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY
2 INLIST ITERATOR
2 TABLE ACCESS BY INDEX ROWID T_CONTENT
551 INDEX RANGE SCAN (object id 29465)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
2 SORT (ORDER BY)
2 INLIST ITERATOR
2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_CONTENT'
551 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_T_CONTENT_ALL'
(NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.64 0.64 0 1721 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.64 0.64 0 1721 0 2
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
1 statement EXPLAINed in this session.
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
USER.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
52 lines in trace file.
 |
 |