Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle, poor performance

Posted on 2011-09-30
10
Medium Priority
?
221 Views
Last Modified: 2012-05-12
below query in our process which is taking between 7-8 seconds when executed. when you execute the same query by commenting out the condition AND INBOUND_OUTBOUND_IND = 'I', it returns within milli seconds. The column INBOUND_OUTBOUND_IND is partitioned. we included the column in the condition to gain performance (searching 1 million records vs total 3 million records). other queries using INBOUND_OUTBOUND are just working fine, but this one is not. Could you please review and let us know why the condition on this column which is supposed to gain a performance is hurting us. If it can be fixed by defining any indexes on the database side, please let me know. any help in this regard is greatly appreciated.

SELECT inbound_seq_pk
      FROM aqtit01.tqt_it_inbound_seq
     WHERE inbound_seq_pk = (SELECT inbound_seq_pk
                           FROM (SELECT inbound_seq_pk, rownum AS rn
                                   FROM Aqtit01.TQT_IT_INBOUND_SEQ inboundSeq
                                  WHERE Inbound_Seq_Pk IN (SELECT Inbound_Seq_Pk
                                                             FROM Aqtit01.TQT_IT_Inbound_Parent
                                                            WHERE PARENT_MATRL IN (SELECT PARENT_MATRL
                                                                                     FROM Aqtit01.TQT_IT_Inbound_Parent
                                                                                    WHERE Inbound_Seq_Pk = 3349801) )
                                    AND Seq_Nbr     < 20110927191030002
                                    AND STS_CDE not in ('RESPONSE_SUCCESS')
                                    AND INBOUND_OUTBOUND_IND = 'I'
                                  ORDER BY seq_nbr
                                ) a
                          WHERE a.rn = 1)
                          AND STS_CDE not in ('RESPONSE_SUCCESS')
0
Comment
Question by:Rao_S
10 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36892239
>> The column INBOUND_OUTBOUND_IND is partitioned.

Please provide us execution plan with/without INBOUND_OUTBOND_IND and list of all indexes available along with column.
0
 

Author Comment

by:Rao_S
ID: 36892315
attached expalin plan output.. txqm1-poor-query-explain-1.xls
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 36893678
Can you post the plan with "AND INBOUND_OUTBOUND_IND = 'I'" commented out?

You might have to use a HINT to tweak the execution plan.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 1000 total points
ID: 36895976
Compute the statistics over the tables and indexes in the schema you use.

Have you indexed the column INBOUND_OUTBOUND_IND ?

Of the index (if the selectivity is not high) will help.

An index will be used if the selected by the condition rows are 4-10% of the table. Using hint you can try to force the usage of the index.
0
 

Author Comment

by:Rao_S
ID: 36904099
hi slightwv, the dba implemented a sql profile for the statement, the cost of the old explain plan was 7,970,617856, the new cost is 37,485,208. there was a gain the application performance.

the inbound_sequence_pk is no longer valid today and the sts_cde for that seq_pk has been to changed to 'O'. so i cannot send you the cost plan with "AND INBOUND_OUTBOUND_IND = 'I'" commented out....

with regards to indexes.. (if u check the explain plan)...these are the indexes being used....
PKQT_IT_INBOUND_SEQ is indexed on inbound_seq_pk and is the primary key..
XQT_IT_INBOUND_SEQ_N9 is indexed on seq_nbr..
XQT_IT_INBOUND_PARENT_U1 is indexed on inbound_seq_nbr,parent_matrl..
XQT_IT_INBOUND_PARENT_N2 is indexed on parent_matrl,inbound_seq_pk...

i am not sure about whether the indexes are local or global...but if u look at the expalin plan....TABLE ACCESS BY GLOBAL INDEX ROWID ....probably means the partitioned index is global...?

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36904342
>>new cost is 37,485,208

Then where was the plan you posted generated?  I don't see costs that high.
0
 

Author Comment

by:Rao_S
ID: 36904369
that is correct... i thought so too...that is what the dba told us....so do you know how he would have calculated the cost..?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36904394
>>so do you know how he would have calculated the cost..?

No.  You will need to ask them.

Also, if performance is no longer an issue because you moved from 'I' to 'O' then is there still a question here?
0
 

Author Comment

by:Rao_S
ID: 36904410
nope, no question... related to the query above...i will go ahead and close the question..
0
 

Author Closing Comment

by:Rao_S
ID: 36904417
Thank you...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question