OraFAQ Forum: Performance Tuning » Help in tuning the query

Home » RDBMS Server » Performance Tuning » Help in tuning the query (Oracle DB, 11.1.0.7.0, AIX 6)

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

Help in tuning the query [message #573220]

Mon, 24 December 2012 06:05

kiranpuli
Messages: 2
Registered: December 2012
Location: Bangalore, India

Junior Member

Please help me in tuning the below query.

UPDATE GAPOSIT GA
SET BLEAN2 = (SELECT CASE
WHEN (SELECT COALESCE(SUM(BA.VALUE), 0)
FROM BALANCE BA, PERIOD PE
WHERE BA.PERIODSEQ = PE.PERIODSEQ
AND PE.CALENDARSEQ = 2251799813685249
AND PE.REMOVEDATE = '01-Jan-2200'
AND PE.STARTDATE < '08-Oct-2012'
AND BA.BALANCESTATUSID = 'posted'
AND BA.PROCESSINGUNITSEQ = 38280596832649218
AND EXISTS (SELECT 1
FROM PAYE PA
WHERE BA.PAYEESEQ = PA.PAYEESEQ
AND PA.PAYEEID = GA.GENERICATTRIBUTE14
AND PA.EFFECTIVEENDDATE = '01-Jan-2200'
AND PA.REMOVEDATE = '01-Jan-2200'
) ) < 0
THEN 1
ELSE 0
END CASE
FROM DUAL )
WHERE GA.PAGENUMBER = 0
AND EXISTS (SELECT 1
FROM POSIT PO
WHERE PO.RULEELEMENTOWNERSEQ = GA.RULEELEMENTOWNERSEQ
AND PO.GENERICNUMBER3 = 1
AND PO.REMOVEDATE = '01-Jan-2200'
AND PO.EFFECTIVESTARTDATE = '01-Jan-2004'
AND PO.EFFECTIVEENDDATE = '01-Jan-2200'
AND PO.PROCESSINGUNITSEQ = 38280596832649218 );

Other details below:

NUM_ROWS LAST_ANALYZED TABLE_NAME
---------- ------------- ------------------------------
1615849 12/13/2012 POSIT
671 11/29/2012 PERIOD
1558841 12/20/2012 PAYE
2008 12/23/2012 GAPOSIT
1212344 10/8/2012 BALANCE

I have uploaded explain plan also. Please let me know incase of any further information required.

Report message to a moderator

Re: Help in tuning the query [message #573223 is a reply to message #573220]

Mon, 24 December 2012 06:30

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

Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

In the end, "REMOVEDATE = '01-Jan-2200'" is a bug, the proof:

SQL> select to_date('01-Jan-2200') from dual;
select to_date('01-Jan-2200') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

Report message to a moderator

Re: Help in tuning the query [message #573227 is a reply to message #573223]

Mon, 24 December 2012 07:23

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

have your query compare dates to dates and not to strings. AND PE.REMOVEDATE = '01-Jan-2200' ->and pe_remove_date=to_date(your string,your format)

Report message to a moderator

Re: Help in tuning the query [message #573228 is a reply to message #573227]

Mon, 24 December 2012 08:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have uploaded explain plan also.
Do I need new glasses because I see no EXPLAIN PLAN?

Report message to a moderator

Re: Help in tuning the query [message #573229 is a reply to message #573228]

Mon, 24 December 2012 08:26

kiranpuli
Messages: 2
Registered: December 2012
Location: Bangalore, India

Junior Member

Now, I have uploaded explain plan

Attachment: explain plan.JPG
(Size: 145.41KB, Downloaded 1056 times)

Report message to a moderator

Re: Help in tuning the query [message #573230 is a reply to message #573229]

Mon, 24 December 2012 08:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

why do you choose to violate Posting Guidelines?

Report message to a moderator

Re: Help in tuning the query [message #573231 is a reply to message #573229]

Mon, 24 December 2012 08:38

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

Senior Member
Account Moderator

I can't download files and other people also can't, so you can count all of them out to help you. Not a good strategy to have an answer, above all in this day.

Read the link(s) I posted and repost accordingly.

Regards
Michel

Report message to a moderator

Previous Topic:	what's operation can cause these wait event?
Next Topic:	Sql query taking High CPU and execution time in oracle 11gR2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 04:20:43 CDT 2024