Home » RDBMS Server » Performance Tuning » ISSUE IN THE PERFORMANCE (ORACLE 10G)
ISSUE IN THE PERFORMANCE [message #596967] Fri, 27 September 2013 22:06 Go to next message
db_learner
Messages: 5
Registered: September 2011
Junior Member
Hi, I am facing performance issue due to my below query
I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before
but no success
Its a bottleneck and I could not have indexes on these tables in production

I want to change the appraoch in subquery using OR below but could not think of any solution yet

SELECT
    g.COLUMN1,
    g.COLUMN2,
    e.COLUMN3,
    g.COLUMN4, 
    MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1,
    min(to_char(date3,'dd-mm-yyyy'))
from table1 e, table2 g 
    where 
    ( 
        e.COLUMN3 in 
        ( 
            select COLUMN3 from table1 
            where 
            date2 =( to_date(?SVAR,'DD-MM-YYYY') - (?SVAR + 1 )) 
            and dat1 >= 0 and column6 = ?SVAR 
        ) 
        OR         
        e.COLUMN3 in 
        (
            select COLUMN3 from table1 having min(date3) = (to_date(?SVAR,'DD-MM-YYYY') - (?SVAR)) group by COLUMN3
        ) 
    )  
    and date3 >=  (to_date(?SVAR,'DD-MM-YYYY') - ?SVAR) 
    and g.column6 = e.column6   
    and e.column6 = ?SVAR 
    and g.COLUMN3 = e.COLUMN3 
    and (g.column7 = 'SBA' or g.column7 = 'CAA') 
    and g.column8 = ?SVAR
    and g.column9 = ?SVAR
    and g.column10 = ?SVAR 
    having max(dat1) <0 
    group by g.COLUMN1,g.COLUMN2,e.COLUMN3,g.COLUMN4



Kindly suggest and help
Re: ISSUE IN THE PERFORMANCE [message #596968 is a reply to message #596967] Fri, 27 September 2013 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 22522
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: ISSUE IN THE PERFORMANCE [message #596971 is a reply to message #596967] Sat, 28 September 2013 03:18 Go to previous messageGo to next message
John Watson
Messages: 4402
Registered: January 2010
Location: Global Village
Senior Member
There are some odd structures in your code. For instance, this
min(to_char(date3,'dd-mm-yyyy'))
Today is 28 September 2013, which is less than 1 October 2013 - but not when you compare the strings as you are. Also, you have two subqueries where one would do.

Fiddling around with the order of joins and predicates will have no effect if you are using the cost based optimizer.
Re: ISSUE IN THE PERFORMANCE [message #596998 is a reply to message #596967] Sun, 29 September 2013 06:52 Go to previous messageGo to next message
db_learner
Messages: 5
Registered: September 2011
Junior Member
Thanks for your comments.
Actually I am trying to acces my production server for explain plan ,I will share ASAP
I require 2 subqueries to cover 2 conditions .Any other alternative fo these 2 subqueries as already there is a join
I shall be thankful if an alternative approach is suggested
icon5.gif  Re: ISSUE IN THE PERFORMANCE [message #596999 is a reply to message #596998] Sun, 29 September 2013 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
Re: ISSUE IN THE PERFORMANCE [message #597001 is a reply to message #596999] Sun, 29 September 2013 09:14 Go to previous message
BlackSwan
Messages: 22522
Registered: January 2009
Senior Member
>Actually I am trying to acces my production server for explain plan ,I will share ASAP
There should be no reason to delay posting the ( FORMATTED ) DDL for tables & indexes involved with the problem SELECT

[Updated on: Sun, 29 September 2013 09:16]

Report message to a moderator

Previous Topic: pagging
Next Topic: Question for List partition
Goto Forum:
  


Current Time: Tue Jul 29 12:52:52 CDT 2014

Total time taken to generate the page: 0.08937 seconds