OraFAQ Forum: Performance Tuning » Tuning sub queries with function call

Home » RDBMS Server » Performance Tuning » Tuning sub queries with function call (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning sub queries with function call [message #508801]

Tue, 24 May 2011 09:27

steve_mavros
Messages: 2
Registered: May 2011

Junior Member

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same and appreciate anyone in helping out to tune this query.

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
FROM TABLE1 TAB_ALIAS1
WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
AND TRUNC(TAB_ALIAS1.ELAP_TIME) =
(
SELECT MAX(ELAP_TIME)
FROM TABLE1 TAB_ALIAS2
WHERE TAB_ALIAS2.COL1 = TAB_ALIAS1.COL1
AND TAB_ALIAS2.COL4 = <CONSTANT VALUE>
AND TAB_ALIAS2.COL5 = <CONSTANT VALUE>
)
AND FUNC_CALL(<INPUT PARAMETER2>) = 'Y'
AND EXISTS (
SELECT NULL
FROM TABLE2 TAB_ALIAS3
WHERE TAB_ALIAS3.COL1 = TAB_ALIAS1.COL1
)

Could any one suggest any recommendations to fine tune it by redesigning the same query?

Report message to a moderator

Re: Tuning sub queries with function call [message #508803 is a reply to message #508801]

Tue, 24 May 2011 09:32

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Not without more information. The full query plus explain plan would be a start.
Also run a trace and see if it's spending most of it's time in the function.

Also this doesn't look right:

AND TRUNC(TAB_ALIAS1.ELAP_TIME) = 
(
SELECT MAX(ELAP_TIME)

Surely you should be truncing both or neither. What happens if the max elap_time isn't midnight?

Report message to a moderator

Re: Tuning sub queries with function call [message #508805 is a reply to message #508803]

Tue, 24 May 2011 09:43

steve_mavros
Messages: 2
Registered: May 2011

Junior Member

Sorry, That was a typo mistake from my side. It has trunc on both sides

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
    FROM TABLE1 TAB_ALIAS1
    WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
    AND TRUNC(TAB_ALIAS1.ELAP_TIME) = 
        (
          SELECT TRUNC(MAX(ELAP_TIME))
          FROM TABLE1 TAB_ALIAS2
          WHERE TAB_ALIAS2.COL1 = TAB_ALIAS1.COL1
          AND TAB_ALIAS2.COL4 = <CONSTANT VALUE>
          AND TAB_ALIAS2.COL5 = <CONSTANT VALUE>
        )
     AND FUNC_CALL(<INPUT PARAMETER2>) = 'Y'
     AND EXISTS (
                  SELECT NULL
                  FROM TABLE2 TAB_ALIAS3
                  WHERE TAB_ALIAS3.COL1 = TAB_ALIAS1.COL1
                 )

Report message to a moderator

Re: Tuning sub queries with function call [message #508806 is a reply to message #508805]

Tue, 24 May 2011 09:46

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Still going to need the other info.

Report message to a moderator

Re: Tuning sub queries with function call [message #508823 is a reply to message #508805]

Tue, 24 May 2011 10:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Anyway, when you call a custom function you break the mathematical model of rdbms and optimizer can do nothing but execute many times your function (many is something between 0 to N per row).

Regards
Michel

[Updated on: Tue, 24 May 2011 10:19]

Report message to a moderator

Re: Tuning sub queries with function call [message #508828 is a reply to message #508823]

Tue, 24 May 2011 10:23

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Hmmmm

FUNC_CALL(<INPUT PARAMETER2>) = 'Y'

That kind of implies that input parameter2 isn't a column from one of the tables in the select.
In which case why is it in the select at all? Why not call it first and only run the select if it returns Y?

Report message to a moderator

Re: Tuning sub queries with function call [message #508831 is a reply to message #508828]

Tue, 24 May 2011 10:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is a very good question!

Regards
Michel

Report message to a moderator

Previous Topic:	Auto Stats gathering not including my schema?
Next Topic:	How to Optimize the Query Without Using IN opertator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:26:25 CDT 2024