Home » RDBMS Server » Performance Tuning » Help in tuning the query (Oracle DB, 11.1.0.7.0, AIX 6)
Help in tuning the query [message #573220] Mon, 24 December 2012 06:05 Go to next message
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.

Re: Help in tuning the query [message #573223 is a reply to message #573220] Mon, 24 December 2012 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
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
Re: Help in tuning the query [message #573227 is a reply to message #573223] Mon, 24 December 2012 07:23 Go to previous messageGo to next message
Flyby
Messages: 147
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)
Re: Help in tuning the query [message #573228 is a reply to message #573227] Mon, 24 December 2012 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
>I have uploaded explain plan also.
Do I need new glasses because I see no EXPLAIN PLAN?
Re: Help in tuning the query [message #573229 is a reply to message #573228] Mon, 24 December 2012 08:26 Go to previous messageGo to next message
kiranpuli
Messages: 2
Registered: December 2012
Location: Bangalore, India
Junior Member
Now, I have uploaded explain plan
Re: Help in tuning the query [message #573230 is a reply to message #573229] Mon, 24 December 2012 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
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?
Re: Help in tuning the query [message #573231 is a reply to message #573229] Mon, 24 December 2012 08:38 Go to previous message
Michel Cadot
Messages: 59821
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
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:
  


Current Time: Fri Nov 28 17:05:15 CST 2014

Total time taken to generate the page: 0.08351 seconds