OraFAQ Forum: Performance Tuning » Union takes more time please help

Home » RDBMS Server » Performance Tuning » Union takes more time please help

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

Union takes more time please help [message #135405]

Wed, 31 August 2005 08:01

misha603
Messages: 20
Registered: July 2005
Location: India

Junior Member

Hi,
I have the following query to be executed
select /*+ FULL(TMPSIT) */ TMPSIT.cosit, SYSDATE
from (select /*+ PARALLEL(TV) PARALLEL(V) PARALLEL(S) */ S.cosit
from thisveh TV, rveh V, rsit S
where V.nuveh = TV.nuveh
and (S.COSIT = V.COSITORI
or S.COSIT = V.COSITDST)
and S.cocatsit = '30'
union
select S.cosit
from thisveh TV, rvehord O, rsit S
where O.nuveh = TV.nuveh
and S.COSIT = O.COSITDST
and S.cocatsit = '30'
union
select /*+ PARALLEL(TV) PARALLEL(L) PARALLEL(S) */ S.cosit
from thislot TV, rlot L, rsit S
where L.nulot = TV.nulot
and (S.COSIT = L.COSITDEP
or S.COSIT = L.COSITARI)
and S.cocatsit = '30'
union
select /*+ PARALLEL(TV) PARALLEL(MS) PARALLEL(S) */ S.cosit
from thismvm TV, rmvmsit MS, rsit S
where MS.numvm = TV.numvm
and S.COSIT = MS.COSIT and S.cocatsit = '30') TMPSIT

If I execute the above query it takes nearly an hour to execute but if I execute each query one by one each query gets executed in less than 5 minutes.
Query1 took 1 minute
Query2 took 4 minutes
Query3 took 2 minutes
Query4 took 3 minutes

Could you please tell me why is it taking so long when I use union?

Thanks
Regards
Santharaj

Report message to a moderator

Re: Union takes more time please help [message #135428 is a reply to message #135405]

Wed, 31 August 2005 09:27

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Does your data have duplicates that you need oracle to eliminate for you? If not, then use union all, so that oracle doesn't have to scan through your entire final result looking for duplicates.

And why all the hints, especially the full table scan hint? Is there something specific to your situation causing your statistics to not be of help?

And I guess those tables are intended to have a degree of 0 in most situations, or do you always want to parallel them?

Report message to a moderator

Re: Union takes more time please help [message #135596 is a reply to message #135428]

Thu, 01 September 2005 07:07

misha603
Messages: 20
Registered: July 2005
Location: India

Junior Member

Hi,
Thanks a lot for your response.
I tried giving union all but it gives me duplicate records. So I'm using union.
I'm really new to performance tuning. Could you please tune that query with appropiate hints.
I would really appreciate your kind and timely help.

Thanks in advance

Regards
Raja

Report message to a moderator

Re: Union takes more time please help [message #135618 is a reply to message #135405]

Thu, 01 September 2005 09:48

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Gather stats on your table, and remove the hints.

If it needs more, try to remove the duplication of criteria you have in there with your = '30' and the or statement by using an inline view of a subquery factoring clause.

Also, will want to spend time reading the oracle 10g performance tuning guide.

Report message to a moderator

Previous Topic:	To increase performance Delete statement
Next Topic:	Multiple Tablespaces

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 09:15:06 CDT 2025