Home » RDBMS Server » Performance Tuning » Function takingg too long (Oracle Enterprise 11g)
Function takingg too long [message #581410] Fri, 05 April 2013 14:19 Go to next message
Tr0cken
Messages: 22
Registered: August 2010
Junior Member
Hi! I need help tweaking (or totally rebuilding) a function to improve its speed.

The function is this:

CREATE OR REPLACE FUNCTION get_chofer2(p_batchnumber IN NUMBER, p_terminalserialnumber IN NUMBER, p_placeidnumber IN NUMBER, p_trxdate IN DATE) RETURN VARCHAR2 
IS
   l_chofer NUMBER := 0;
BEGIN
    select q2.workerid into l_chofer
    from 
    (
    select max(aux.trxdate) trxdate
    from vw_chofer_aux1 aux
    where aux.BATCHNUMBER = p_batchnumber
          AND aux.TERMINALSERIALNUMBER = p_terminalserialnumber
          AND aux.PLACEIDNUMBER = p_placeidnumber
          and aux.trxdate < p_trxdate
    ) Q1 
    inner join 
    (
    SELECT distinct aux.trxdate, aux.WORKERID
    from vw_chofer_aux1 aux
    where aux.BATCHNUMBER = p_batchnumber
          AND aux.TERMINALSERIALNUMBER = p_terminalserialnumber
          AND aux.PLACEIDNUMBER = p_placeidnumber
          and aux.trxdate < p_trxdate
    )q2
    on q1.trxdate = q2.trxdate;     
   
   RETURN l_chofer
   ;
END;
/


And the select:

SELECT t.trxdate, t.batchnumber, t.terminalserialnumber, t.placeidnumber, t.trxdate,
       get_chofer2 (t.batchnumber,
       t.terminalserialnumber,
       t.placeidnumber,
       t.trxdate) workerid
FROM TRANSACTION t;


This processes millions of transactions and takes hours!

I need to get the WORKERID data for each transaction based on the date, and I can't thing of another way of doing it.

Perhaps can someone give me a hint?

Tank you!
Re: Function takingg too long [message #581411 is a reply to message #581410] Fri, 05 April 2013 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What is the purpose of the function: give the specification
2/ Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
3/ Rewrite the query to NOT call the function

Regards
Michel
Re: Function takingg too long [message #581481 is a reply to message #581411] Mon, 08 April 2013 01:59 Go to previous messageGo to next message
michael_bialik
Messages: 597
Registered: July 2006
Senior Member
Post TKPROF
Re: Function takingg too long [message #581483 is a reply to message #581481] Mon, 08 April 2013 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TKPROF will help in no way in this case.

Regards
Michel
Re: Function takingg too long [message #581486 is a reply to message #581483] Mon, 08 April 2013 04:01 Go to previous messageGo to next message
michael_bialik
Messages: 597
Registered: July 2006
Senior Member
As vw_chofer_aux1 is probably a view - TKPROF may point to missing indexes/incorrect order of joins etc.
Re: Function takingg too long [message #581495 is a reply to message #581486] Mon, 08 April 2013 04:55 Go to previous message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The call to the function is the main problem.
The first step is to remove it, then to optimize the statement.

Regards
Michel
Previous Topic: Clarification on using awrrpti.sql script
Next Topic: Regarding trace file &TKprof
Goto Forum:
  


Current Time: Mon Apr 21 09:23:59 CDT 2014

Total time taken to generate the page: 0.07123 seconds