| 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
|
|
|
|
| Re: Long running Query [message #649087 is a reply to message #649083] |
Sat, 12 March 2016 01:54  |
John Watson
Messages: 8964
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?
|
|
|
|
| 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 #649094 is a reply to message #649092] |
Sat, 12 March 2016 04:00 |
John Watson
Messages: 8964
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.
|
|
|
|
|
|
| Re: Long running Query [message #649097 is a reply to message #649096] |
Sat, 12 March 2016 05:39 |
John Watson
Messages: 8964
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.
|
|
|
|
|
|
| Re: Long running Query [message #649106 is a reply to message #649105] |
Sun, 13 March 2016 03:11 |
John Watson
Messages: 8964
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.
|
|
|
|
|
|
| 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: 68733
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
