Home » SQL & PL/SQL » SQL & PL/SQL » Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2]
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 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: 21824
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: 13967
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 #642693 is a reply to message #642692] |
Thu, 17 September 2015 07:04 |
cookiemonster
Messages: 13967
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.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 06 12:08:32 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
