| Oracle UNION ALL performance issue [message #474898 is a reply to message #474895] |
Thu, 09 September 2010 09:16  |
|
|
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together with group by and without group by it take much time. Could you please help me on this.?
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM
(
(
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
(
SELECT c_test.date_completed 'date_completed',c_sample.login_date 'login_date'
FROM c_sample c_test
where (some conditions) ) ---SQL part 2
) AS tbl
GROUP BY to_char(to_date(login_date), 'YYYY')
Thanks and Regards,
Vetrivel Karuppan, Dharmapuri - Oracle
|
|
|
|
| Oracle UNION ALL performance issue [message #474899 is a reply to message #474895] |
Thu, 09 September 2010 09:17  |
|
|
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together using UNION ALL with group by and without group by it take much time. Could you please help me on this.?
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM
(
(
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
(
SELECT c_test.date_completed 'date_completed',c_sample.login_date 'login_date'
FROM c_sample c_test
where (some conditions) ) ---SQL part 2
) AS tbl
GROUP BY to_char(to_date(login_date), 'YYYY')
Thanks and Regards,
Vetrivel Karuppan, Dharmapuri - Oracle
[Updated on: Thu, 09 September 2010 09:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #474952 is a reply to message #474905] |
Fri, 10 September 2010 02:16 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
The conditions can be important too, if they are poorly written, can block indexes etc.
To elaborate on what Blackswan said, there can be many reasons a query isn't performing, you assume its the union operator but without the detail you(or we) can't tell. Michel is right about the union I think.
|
|
|
|
|
|
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475162 is a reply to message #474905] |
Mon, 13 September 2010 06:54 |
|
|
if I execute the sql part1 and sql part 2 separately without union all it takes only
few minutes. only when we try to use union all it takes much time( 30 min).
so there is not a indexes or poor conditions problem or DDL.
each sql part1(total record is count =900000) and sql part2 (total record is count =600000). so when I use UNION ALL that time only I am facing this issue.
Thanks and Regards,
Vetrivel Karuppan
|
|
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475168 is a reply to message #475164] |
Mon, 13 September 2010 07:10 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
How long does the query take without the group by?
i.e. time this one:
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
(
SELECT c_test.date_completed 'date_completed',c_sample.login_date 'login_date'
FROM c_sample c_test
where (some conditions) ) ---SQL part 2
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475182 is a reply to message #475164] |
Mon, 13 September 2010 09:00 |
|
|
Hello,
Please don't assume still I am using group by.
just assume that
SQL Query :
SQL1 UNION ALL SQL2
separately exection times are
SQL1 -- 5 seconds (total count - 5,00000)
SQL2 -- 10 seconds (total count - 4,00000)
if I use UNION ALL. it takes 30 minutes to complete the execution.why it takes that much time if I use UNION ALL.?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475336 is a reply to message #475333] |
Tue, 14 September 2010 08:24 |
|
|
oh ok..it takes the query to get all the rows around 32 min.
Do you mean it should take totally 15+17 min=32 min is the right one?
so we could not able to tune once again if it has total record count is around 10,00000 or more.
Thank you
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475747 is a reply to message #475672] |
Fri, 17 September 2010 05:08 |
|
|
Hi,
See the below msg from Roachcoach and cookiemonster. Really I frustrated because of this message. simply there are irridating me like this words. Please take a action on this.
Roachcoach
Messages: 111
Registered: May 2010
Location: UK
That was a joke.
As was this:
victoryhendry wrote on Wed, 15 September 2010 12:33
I have used hints /*+go_faster*/ . It took few seconds to retrieve the query.Wasn't it?
cookiemonster
Messages: 3896
Registered: September 2008
Location: Rainy Manchester
I'm usually really good at spotting sarcasm, even over the toneless internet, but honestly....I just dont know here.
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475749 is a reply to message #475747] |
Fri, 17 September 2010 05:24 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're complaining about my post?
really?
I told you John's suggestion was a joke.
He made the joke in response to this:
Quote:
I have used hints /*+go_faster*/ .
Since /*+go_faster*/ is really, obviously not a valid hint, I wondered if you were joking as well. Hence the rest of my post - I was really seriously asking if you were joking.
The only other possibilities that I can see are:
1) you used a real hint instead - in which case your description of what you did is so meaningless you can't really blame anyone but yourself for the confusion that followed.
2) You really did add /*+go_faster*/ as a hint to your query - I which case I'm just lost for words.
As for Roachcoach's reply - slightly unnecessary but not offensive. He just stated what we were all wondering.
So really no action is going to be taken.
EDIT: added some more words for clarity.
[Updated on: Fri, 17 September 2010 07:27] Report message to a moderator |
|
|
|
| Re: Oracle UNION ALL performance issue [message #475774 is a reply to message #475749] |
Fri, 17 September 2010 08:22 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
If I may add in my defense there was no smiley, or anything else for that matter, to indicate sarcasm. I assumed it was sarcasm for the reasons cookiemonster gave.
That being said you need to remember people search these forums for answers before posting quite a lot, referencing non-existent hints without clearly being a joke might confuse other users searching.
What is 'obviously' a joke to seasoned oracle folks isnt necessarily going to be obvious to everyone...is all I'm driving at really.
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475820 is a reply to message #475774] |
Fri, 17 September 2010 18:05 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I'm not sure what you're getting at @victoryhenry. I've seen some marginal-offensive behaviour on this forum before, but @RoachCoach and @cookiemonster didn't even come close. It was just a statement of fact. Your go_faster post was either dead-pan sarcasm or just a bit of embarrassing naivety. It was pretty hard to tell.
If you took these messages as offensive, then you have seriouly mis-read their intent. Quite simply, they were not offensive by any reasonable person's definition.
/*+ go_faster */ is not a real hint. I don't know who made it up, but Jonathon Lewis (Oracle expert) seems to have used it in his presentations as a joke. There are some references to those presentations on the Internet that don't explicitly state it was a joke. Perhaps this is where you saw it, and you thought it was real.
Back to the topic. You should expect a UNION ALL statement to take EXACTLY the same amount of time as the two separate statements. You cannot tune the UNION ALL statement except by tuning the individual SQLS. If you want to tune the individual SQLs, you are going to have to show them to us, and provide Explain Plans.
Ross Leishman
|
|
|
|
| Re: Oracle UNION ALL performance issue [message #475856 is a reply to message #475820] |
Sat, 18 September 2010 04:40 |
|
|
Hi Ross Leishman,
Really thank you very much for your message. I saw this hint "/*+ go_faster */" somewhere in net.
I tuned the sql based on my explain plan. actually it was not using index earlier so I corrected that one.
Please ignore my below complaints and actually I should not want to hurt anybody.
Thanks,
Everyone Have a nice day always !!!!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
It might be faster still if you convert all key words to upper case 