|
|
|
|
|
|
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642470 is a reply to message #642469] |
Fri, 11 September 2015 08:15   |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Not even close to clear.
All you've told us is:
You have a query
It's fast when you run it in toad / sql developer
It's slow when you run it on the server.
You haven't told us how you run it on the server (sqlplus is apparently involved somehow)
You have told us nothing about the query itself.
You haven't said if the query is literally the only thing run on the server or if other code is involved.
You've given us nothing to work on what so ever.
So the only useful advice we can give is to trace the session.
If you want any more advice you're going to have to supply some actual details about the query and how exactly it's being run.
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642471 is a reply to message #642469] |
Fri, 11 September 2015 08:28 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
saurabh.sha wrote on Fri, 11 September 2015 13:58Hi James/Moderator,'
Apologies for mistakenly posting the query at 2 forums, i didn;t post the query as here we are not looking to tune the query as query is running fine and in acceptable time in clients ( Toad or sql developer), it just that when we schedule it in server which have DB hosted ( using the SQLplus connection) the query is taking very long to run.
Ideally we were expecting the query to run faster as it is directly hitting the oracle DB engine and not making connection through client.
please let me know if you feel i am not clear.
Thanks and Regards
Saurabh
UNIX is case sensitive. So what you need to do is re-write the query to put all the key words into upper case.
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642501 is a reply to message #642469] |
Sat, 12 September 2015 14:48 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
saurabh.sha
query is running fine and in acceptable time in clients ( Toad or sql developer)
TOAD, by default, returns only 500 rows (at least, in version I use - 12.5. If I remember well, some of previous versions used to return 1000 rows). I believe that SQL Developer does the same. What happens when you run that query in yet another, reliable tool called SQL*Plus? How long does the query take in SQL*Plus?
Therefore, I suspect that query really takes a lot of time; it is TOAD and SQL Developer that fooled you.
Now, query might be tuned so that it runs faster, but - in order to do that, you need to provide some more details. Have a look at our Performance Tuning sub-forum and read the sticky topic.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642660 is a reply to message #642659] |
Wed, 16 September 2015 09:17 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Wow.
That query has the most evil case/decode statement I've ever clapped eyes on. I'm amazed it runs fast in any circumstances and I guarantee it's not properly understood.
Here's one of them reformatted a bit to make it more readable:
CASE
WHEN DECODE ((SELECT SUM (ol.ordered_qty)--query 1
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
'0',
'Cancelled',
DECODE ((SELECT SUM (ol.ordered_qty)--query 1
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT SUM (sts.status_quantity) --query 1a
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT st.description --query 2
FROM yfs_status st,
yfs_pipeline p
WHERE st.status = (SELECT MAX (status) --query 3
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0)
AND p.process_type_key = st.process_type_key
AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key --query 3a
FROM yfs_order_release_status ors
WHERE ors.order_header_key = oh.order_header_key
AND ors.order_line_key = ol.order_line_key
AND ors.status_quantity > 0
)
),
DECODE ((SELECT MIN (status) --query 3b
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT MAX (status) --query3
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT st.description --query 2a
FROM yfs_status st,
yfs_pipeline p
WHERE st.status = (SELECT MIN (status) --query 3
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
)
AND p.process_type_key = st.process_type_key
AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key --query 3a
FROM yfs_order_release_status ors
WHERE ors.order_header_key = oh.order_header_key
AND ors.order_line_key = ol.order_line_key
AND ors.status_quantity > 0
)
),
DECODE ((SELECT MIN (status) --query 3b
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT MAX (status) --query 4
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
AND sts.status < '9000'
),
(SELECT st.description --query 2a
FROM yfs_status st,
yfs_pipeline p
WHERE st.status = (SELECT MIN (status) --query 3
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
)
AND p.process_type_key = st.process_type_key
AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key --query 3a
FROM yfs_order_release_status ors
WHERE ors.order_header_key = oh.order_header_key
AND ors.order_line_key = ol.order_line_key
AND ors.status_quantity > 0
)
),
(SELECT 'Partially ' || st.description --query 2b (same as 2 apart from the ||)
FROM yfs_status st, yfs_pipeline p
WHERE st.status = (SELECT MAX (status) --query 4
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
AND sts.status < '9000'
)
AND p.process_type_key = st.process_type_key
AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key --query 3a
FROM yfs_order_release_status ors
WHERE ors.order_header_key = oh.order_header_key
AND ors.order_line_key = ol.order_line_key
AND ors.status_quantity > 0
)
)
)
)
)
) = 'Cancelled'
THEN cancelled_date.status_date --cancelled_date.status_date
ELSE (SELECT MAX (rcpt_status.new_status_date)
FROM yfs_receipt_status_audit rcpt_status
WHERE rcpt_status.receipt_header_key = rh.receipt_header_key
AND rcpt_status.new_status = '1500') --rcpt_status.new_status_date-- rcpt_status.new_status_date
END AS reporting_date_gmt,
I've added comments to inditify all the individual queries contained within. Queries with the same number have the same from and where but different selects.
There should be no need to repeat all those sub-queries. However since I know nothing about your system or what the query is supposed to, and because it's depressingly complicated I'll limit myself to a few obvious suggestions:
1) Query 2b is a complete waste of time. Whatever it returns can't possibly = 'Cancelled' so you should just rip it out and replace with null if necessary.
I say that. However, my last point below makes it a moot point. Leave it in and fix the other code duplication.
2) Bit's like this:
DECODE ((SELECT SUM (ol.ordered_qty)--query 1
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
),
(SELECT SUM (sts.status_quantity) --query 1a
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
)
could be rewritten like this:
DECODE ((SELECT 1
FROM yfs_order_release_status sts
WHERE sts.order_header_key = oh.order_header_key
AND sts.order_line_key = ol.order_line_key
AND sts.status_quantity > 0
HAVING SUM (ol.ordered_qty) = SUM (sts.status_quantity)
),
1
One pass on the table rather than 2.
There are two other pairs of queries in that that you can apply the same trick to.
There there's the other massive bit of code duplication.
You've got a decode for sib_whs_order_status and CASE statements for reporting_date_gmt and billing_date_gmt.
Both CASE statments are identical. Both CASE statements replicate the full DECODE.
Considering how many aub-queries the decode contains that's frankly madness.
Some nesting will sort that out:
SELECT
sib_whs_order_status,
reporting_date_gmt,
reporting_date_gmt as billing_date_gmt
FROM (SELECT sib_whs_order_status,
CASE WHEN sib_whs_order_status = 'Cancelled'
THEN
cancelled_date.status_date --cancelled_date.status_date
ELSE
(SELECT MAX (rcpt_status.new_status_date)
FROM yfs_receipt_status_audit rcpt_status
WHERE rcpt_status.receipt_header_key =
rh.receipt_header_key
AND rcpt_status.new_status = '1500') as reporting_date_gmt
FROM (SELECT DECODE(......) as sib_whs_order_status
FROM .....
)
)
Though really if reporting_date_gmt is always the same as billing_date_gmt then probably one of those shouldn't be in the output.
Sort all that out and see where you stand.
|
|
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642662 is a reply to message #642661] |
Wed, 16 September 2015 10:23 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's worth pointing out that due to the way decodes short-circuit that query can undergo massive fluctions in the time it takes due to the state of the data.
Say you run the query in toad at a point in time where the very first sub-query in the decode returns 0 for most records. In that case the decode will return a status of Cancelled without running the rest of the aub-queries.
Now suppose the batch job runs later on and the state of the data changes in the meantime.
So that when the batch job runs the decodes end up falling all the way through to query 2b to get the status for most records. The same number of records are returned but now the decode is running 19 sub-queries per record returned instead of 1. And of course you're replicated the decode twice so that's actually 3*19 = 57 sub-queries per record instead of 3.
|
|
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642690 is a reply to message #642689] |
Thu, 17 September 2015 06:18 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I would seriously fix the decode thing first. If nothing else it'll simply the query and the explain plan and so make it a lot simpler to see what's going on.
You should make sure the statistics are up to date.
Once that is done see what affect ditching the WITH clause has and if it's still slow after that come back to us with the new query and explain plan and we'll be in a better position to see if we need to do anything with indexes.
|
|
|
|
|
|
| Re: Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2] [message #642693 is a reply to message #642692] |
Thu, 17 September 2015 07:04 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's the problem with the decodes - you've got conditional selects but the explain plan has to include them all because it can't know in advance how many are going to be run. And generally they will all be run for some records in the result set and not others.
Look at a result set - any record with a status that starts with 'Partially ' is one where all the sub-queries were run, all 57. Any record with a status that doesn't start with that didn't run all the sub-queries.
That fact makes it really difficult to tell where the time is being spent.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
