Home » RDBMS Server » Performance Tuning » Perfoemnace Tunning (Oracle 10g)
Perfoemnace Tunning [message #386503] Sun, 15 February 2009 23:01 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi To All,
Please suggest me changes in the query so that I can change total cost for the given query.Please check in the attached file for the query and the explain plan.


SELECT loanid "Loan Id",
loanno "Loan No",
to_date(next_due_dt, 'DD/MM/RRRR') "Next DueDate"
FROM lmt_loan_dtl lld,
(SELECT lrs_1.due_date AS next_due_dt, lrs_1.loanid AS rs_loanid
FROM (SELECT lrs.loanid,
lrs.due_date,
row_number() over(PARTITION BY loanid ORDER BY loanid, due_date ASC) AS row_number
FROM lmt_repay_schedule lrs
WHERE lrs.due_date >=
(Select c.effectivedate
from company c
WHERE c.companyid = lrs.companyid)
AND lrs.status <> 'X') lrs_1
WHERE row_number = 1)
WHERE lld.loanid = rs_loanid(+)
AND lld.loanid NOT IN (SELECT ltd.loanid
FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
WHERE ltd.tranhdrid = lth.tranhdrid
AND lth.cifid = lld.cifid
and lth.status = 'M')
and lld.cg_loan_status not in
(fnccgid('LOANSTATUS', 'X', 5000),
fnccgid('LOANSTATUS', 'W', 5000),
fnccgid('LOANSTATUS', 'F', 5000),
fnccgid('LOANSTATUS', 'RESTCLOS', 5000))
Re: Perfoemnace Tunning [message #386504 is a reply to message #386503] Sun, 15 February 2009 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

>Please check in the attached file
HUH? File? What file?

[Updated on: Sun, 15 February 2009 23:03]

Report message to a moderator

Re: Perfoemnace Tunning [message #386507 is a reply to message #386504] Sun, 15 February 2009 23:05 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Now What is worng in posting my question?Please suggest me right way to post the question.
Re: Perfoemnace Tunning [message #386508 is a reply to message #386503] Sun, 15 February 2009 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Post 2 lists.
List #1 the Posting guidelines you followed.
List #2 the Posting Guidelines you did not follow.

[Updated on: Sun, 15 February 2009 23:12]

Report message to a moderator

Re: Perfoemnace Tunning [message #386511 is a reply to message #386507] Sun, 15 February 2009 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Perfoemnace Tunning [message #386857 is a reply to message #386503] Tue, 17 February 2009 03:14 Go to previous messageGo to next message
sukhijank
Messages: 5
Registered: February 2009
Junior Member
@sr_orcl

Can not make much sense with the given information; ; but you can try:

   AND NOT EXISTS (
          SELECT 1
            FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
           WHERE ltd.tranhdrid = lth.tranhdrid
             AND lth.cifid = lld.cifid
             AND lth.status = 'M'
             AND ltd.loanid = lld.loanid)

instead of

   AND lld.loanid NOT IN (SELECT ltd.loanid
                            FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
                           WHERE ltd.tranhdrid = lth.tranhdrid
                             AND lth.cifid = lld.cifid
                             AND lth.status = 'M')


Regards,
Naresh
Re: Perfoemnace Tunning [message #386875 is a reply to message #386857] Tue, 17 February 2009 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both are not equivalent.

Regards
Michel
Re: Perfoemnace Tunning [message #386910 is a reply to message #386503] Tue, 17 February 2009 04:30 Go to previous message
sukhijank
Messages: 5
Registered: February 2009
Junior Member
Hi Michel,
Yes, you are right. I am aware of the nuances of NOT IN and NOT EXISTS. Both may not give same results only in the situation when inner query gives NULL values.

But, for the given scenario, where the author is already using NOT IN for excluding certain set of loanids, I presume that the inner query will not yield NULL values, otherwise the query submitted by the author would have been wrong at the first place itself.

Regards,
Naresh

Previous Topic: TkProf-Perormance Tunning (merged)
Next Topic: exchange partition
Goto Forum:
  


Current Time: Tue Dec 06 12:35:19 CST 2016

Total time taken to generate the page: 0.05699 seconds