OraFAQ Forum: Performance Tuning » Long running Query

Home » RDBMS Server » Performance Tuning » Long running Query (Windows, Oracle 11g)

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

Long running Query [message #649083]

Fri, 11 March 2016 22:58

narendra_bagur
Messages: 5
Registered: March 2016
Location: Bangalore

Junior Member

Hi,

Attaching a long running query and the SQL Plan for the query. I am a java developer and this Oracle query is being called from Java program.
We provided the query and SQL plan to the Database expert for tuning advise.

He is of the opinion that the query is badly written with OR conditions which impact the performance.
His suggestion to rewrite the query in any of the methods

METHOD 1
---------------
1) Create a temporary table
2) Create a Database procedure
3) This procedure should contain multiple queries ---Split the query into multiple queries based on logical and functional conditions so that we can avoid the OR conditions
4) insert the results of these queries into temporary table
5) Execute the procedure and read the result from temporary table from Java

OR

METHOD 2
----------
1) Rewrite the Query to remove the OR conditions completely
2) Use UNION ALL to meet the logical conditions instead of OR operator
3) call the modified query from JAVA.

We cannot use Method 1 from JAVA so we are thinking to use the method 2.

But before taking the advices, wanted to cross check that is the original query really badly written
I have read that there is not much performance improvement with UNION ALL compared to OR condition.

Since I am a Java developer don't have much knowledge on Oracle and how queries work.

Please advise on this Please give your suggestions on the SQL Plan and Query

SQL query and plan in the attachment.

Thanks,
Narendra

Attachment: Oracle_Query.txt
(Size: 25.37KB, Downloaded 2543 times)

Report message to a moderator

Re: Long running Query [message #649087 is a reply to message #649083]

Sat, 12 March 2016 01:54

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

I would be interested to know who your expert is, and what his qualifications are. His first suggestion is, I would say, going to degrade performance. Going via a segment (either a permanent table or a global temporary table) wold probab;y slow things down. Furthermore, when you consider read consistency, the results may not be be correct, either.
His second suggestion is rather strange, as Oracle has already done the re-write to a concatention.

Who is the expert?

Report message to a moderator

Re: Long running Query [message #649089 is a reply to message #649083]

Sat, 12 March 2016 01:56

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You have a Cartesian product (no join to the product table, your 'expert' really should have picked that up) and your correlated sub queries are probably the main time consumers, have a look and see if you can adapt the query to not need those correlated sub queries.
Your predicate bi.action_date = sysdate;
is unlikely to be useful as sysdate also contains a time portion, are you genuinely looking for rows with an action date specific to the EXACT point in time that you execute the query? (I could of course, be wrong there, I don't know your data/process)

tbh I can't really be bothered trying to pick through a parallelised explain plan, feel free to post the plan again without the parallelism.

[Updated on: Sat, 12 March 2016 01:58]

Report message to a moderator

Re: Long running Query [message #649091 is a reply to message #649089]

Sat, 12 March 2016 03:21

narendra_bagur
Messages: 5
Registered: March 2016
Location: Bangalore

Junior Member

Hi pablolee,

Thanks for your reply.
I didnt understand what you mean by
Posting plan without parallelism.

We had requeated for query plan to analyse.
How to take plan without parallelism.
Is not the parallelism part of the query plan itself.

Report message to a moderator

Re: Long running Query [message #649092 is a reply to message #649087]

Sat, 12 March 2016 03:24

narendra_bagur
Messages: 5
Registered: March 2016
Location: Bangalore

Junior Member

Thanks for your reply.
What about the query plan does it look good. Do we need to change
The query.

Report message to a moderator

Re: Long running Query [message #649094 is a reply to message #649092]

Sat, 12 March 2016 04:00

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

As I said before, the optimizer has already re-written the query to the form your "expert" suggested. Who is he? Have you discussed this with him? The change you might try is to hint the query so that it will NOT do that transformation. Look up the NO_EXPAND hint.
You have also been given a precise instruction on removing one bug in your code: add the missing join predicate.
As for removing parallel processing, I'll leave it up to you to research that. Hint: you can do it with a hit, or with ALTER SESSION.

Time to do some work, NArenda.

Report message to a moderator

Re: Long running Query [message #649096 is a reply to message #649094]

Sat, 12 March 2016 05:34

narendra_bagur
Messages: 5
Registered: March 2016
Location: Bangalore

Junior Member

Yes I am doing my research. As I mentioned earlier am Java developer and trying to figure it out how to write good SQL

I came across this one.

https://blogs.oracle.com/optimizer/entry/or_expansion_transformation

What do you suggest on this.

Report message to a moderator

Re: Long running Query [message #649097 is a reply to message #649096]

Sat, 12 March 2016 05:39

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Narenda, you have ignored what I have suggested so far. I see no purpose in trying to assist. Perhaps someone else will make a suggestion that you will not ignore.

Report message to a moderator

Re: Long running Query [message #649105 is a reply to message #649097]

Sun, 13 March 2016 03:03

narendra_bagur
Messages: 5
Registered: March 2016
Location: Bangalore

Junior Member

Hello John,

I have not ignored anything. As I told earlier am a Java resource trying to figure out performance issues in SQLs called from Java.
Since I am new to SQL I try to search for the solution of this problem.

I have taken note of your suggestion, but every solution is not a good solution until it is tested and verified.
Also I have taken pablolee's advise and I have fixed the cartesian product due to no join conditions of one table and currently looking for alternative methods to rewrite the subqueries.

At present my focus is to decipher the issues in the Plan, which as a Java developer I don't understand at all but when I try to get answers from these forums I get very vague responses, they even don't even tell me if there is a problem at all with the current Plan, whether it looks a good plan or further we need to do something.

Since I don't get the simple answers to these simple question, you will agree that I need to look and search more and try to find answers on my own.

You will agree that is not a bad thing to do.

Report message to a moderator

Re: Long running Query [message #649106 is a reply to message #649105]

Sun, 13 March 2016 03:11

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

What is the SQL, the plan, and the run time now that you have fixed the missing join? Is the performance now OK?

I don't understand this comment of yours,Quote:

I get very vague responses

I thought my suggestion to test using the NO_EXPAND hint to prevent the re-write to a UNION ALL query (that your "expert" clearly did not understand) was as precise as one could be.

Report message to a moderator

Re: Long running Query [message #649111 is a reply to message #649106]

Sun, 13 March 2016 22:47

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

The following link contains information about the NO_EXPAND HINT.

http://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#BABHBECB

Report message to a moderator

Re: Long running Query [message #649308 is a reply to message #649083]

Tue, 22 March 2016 03:40

vivek_h
Messages: 1
Registered: March 2016

Junior Member

Product table is no where mentioned in joining condition. Give one more condition in Product table with any other table.
p.<columnname>= related column in any of the below mentioned table.

ACCOUNT A,
BC_ACCOUNT BC,
T_LANG TL,
PRODUCT_CAT PCAT,
ACCOUNT_BI BI

[Updated on: Tue, 22 March 2016 03:45]

Report message to a moderator

Re: Long running Query [message #649309 is a reply to message #649308]

Tue, 22 March 2016 03:45

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This has already been mentioned 10 days ago, please read and understand the previous replies before posting yours.

Report message to a moderator

Previous Topic:	Why din table fetch continued row increase after fetching every columns
Next Topic:	improve sql performance (long sql here)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 09:30:32 CDT 2025