Home » SQL & PL/SQL » SQL & PL/SQL » Performance Imorvement for Oracle Query (10.2.0.3.0)
Performance Imorvement for Oracle Query [message #406492] Thu, 04 June 2009 04:38 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

We are trying to improve the performance of the following query. The execution takes a lot of time and dumps in the end due to the large volume of data handled. Moreover table T1 does not have an index on 'col3' and we are not allowed to create indexes either. Request your inputs/suggestions for improving the performance of the query. If there are no alternatives for improving the following query kindly let me know how using PL/SQL will help.

select /* +parallel(t1,8)*/ t1.col1
,t4.col1
,sum(decode(t2.flag,'Y',1,0)) cnt1
,sum(decode(t2.flag,'N',1,NULL,1,0)) cnt2
from table1 t1
,table2 t2
,table4 t4
where t1.col3=t2.col3
and t1.col4 ='A'
and t2.col2 = t4.col2
and t2.status='A'
and t2.CREATION_DATE between to_date('&d1','DD/MON/YYYY') and to_date('&d2','DD/MON/YYYY' )
group by t1.col1, t4.col1;

Thank you,
SSN
Re: Performance Imorvement for Oracle Query [message #406495 is a reply to message #406492] Thu, 04 June 2009 04:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends (as usual) on a lot of stuff that you (as usual) don't tell us.

So the one advice left would be to go buy faster hardware.
Re: Performance Imorvement for Oracle Query [message #406502 is a reply to message #406492] Thu, 04 June 2009 05:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does 'Dumps in the end due to large volume of data handled' mean.

What oracle error do you get?
Re: Performance Imorvement for Oracle Query [message #406514 is a reply to message #406492] Thu, 04 June 2009 06:34 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Truncate both tables. That will improve the performance of the table, maybe.
Re: Performance Imorvement for Oracle Query [message #406518 is a reply to message #406514] Thu, 04 June 2009 06:49 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Pab,

Is this the way to answer the threads. Don't hink people who are posting the threads are useless.. They are posting the threads to learns the better ways to improve their knowledge.
Truncate both the tables means it doesn't contaisn any data so how how we will improve the data? please don't think you are the only expert in Oracle.... please try to develop the knowledge of the peers who joined in this forum...

Thank you
Re: Performance Imorvement for Oracle Query [message #406523 is a reply to message #406518] Thu, 04 June 2009 07:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@user71408 - for someone who claims to be trying to improve their knowledge, you've been reasonably resistant to any change in your posting style or content that would actually help us to help you.

If you read the Sticky on the Performance tuning forum, you'll see the sort of information that we need in order to help you.
Re: Performance Imorvement for Oracle Query [message #406526 is a reply to message #406523] Thu, 04 June 2009 07:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Don't hink people who are posting the threads are useless.


There are very strong indications in that direction about certain peoples, though.

When after hundreds of post they still post questions that are completely unanswerable because they totally lack even the basic information to even point into a possible vague general approach for a solution.
Re: Performance Imorvement for Oracle Query [message #406637 is a reply to message #406518] Fri, 05 June 2009 02:18 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Is this the way to answer the threads.
Since you seem to never 'listen' to the advice given or the requests made of you, I would say that pretty much any reply would have as much value.
Quote:
Don't hink people who are posting the threads are useless
I'm not convinced that it is your place to tell me what I can and cannot think. And I don't think that everyone is useless, just a very select few (and even they are not totally useless, they certainly offer amusement, I'm just not sure of their worth.
Quote:
They are posting the threads to learns the better ways to improve their knowledge
and yet you insist on posting in the way that you do. i.e. massively wide ranging questions with a complete dearth of pertinent information, commonly in the completely wrong forum. And then ignoring anyone who actually asks you to provide more, useful and relevant information, instead, repeating your question, and demanding full solutions.
Quote:
Truncate both the tables means it doesn't contaisn any data so how how we will improve the data?
At what point did you ask about improving the data? Or were we meant to surmise that from your detailed post?
Quote:
please don't think you are the only expert in Oracle
I absolutely do NOT think that. In fact, compared to many, many real experts here, I am but a novice.
Quote:
please try to develop the knowledge of the peers who joined in this forum
unfortunately, certain peers appear to be somewhat resistant to absorbing information.
You amuse me 71408 (can I call you 7?) so I continue to get involved in your threads. It astounds me that you still have not learned the correct way to get an answer on this (and other) site(s). Maybe one day...
Re: Performance Imorvement for Oracle Query [message #406644 is a reply to message #406637] Fri, 05 June 2009 02:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
pablolee
(can I call you 7?)
./fa/1600/0/ 7 of 9? ./fa/1964/0/
Re: Performance Imorvement for Oracle Query [message #406650 is a reply to message #406644] Fri, 05 June 2009 03:10 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Stunning looking woman. Gotta love that outfit.
Previous Topic: & in insert statement
Next Topic: WILL BFILE FETECH FILES FROM SHARED FOLDER
Goto Forum:
  


Current Time: Sat Dec 10 10:39:44 CST 2016

Total time taken to generate the page: 0.22214 seconds