Home » RDBMS Server » Performance Tuning » Oracle HASH GROUP BY & SORT GROUP BY performance (Oracle 10g)
Oracle HASH GROUP BY & SORT GROUP BY performance [message #308842] Tue, 25 March 2008 12:30 Go to next message
rkg_007
Messages: 4
Registered: March 2008
Junior Member
Hi,

I have a select query which uses GROUP BY and HAVING clause performing selct over 3 million records.

In oracle 10g one version (i think rel 2), the explain plan shows HASH GROUP BY and this query runs very fast.

In oracle 10g another version (i think rel 1), the explain plan shows SORT GROUP BY and this query takes hours.

Pls suggest what should i do? Is there any hint that can make the query use HASH GROUP BY instead of SORT GROUP BY.
Or is there any other option that i should try?

Thanks in advance.
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308848 is a reply to message #308842] Tue, 25 March 2008 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does HASH GROUP BY exist in 10.1?

Regards
Michel
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308849 is a reply to message #308842] Tue, 25 March 2008 12:44 Go to previous messageGo to next message
rkg_007
Messages: 4
Registered: March 2008
Junior Member
No..10.1 by default uses SORT GROUP BY and 10.2 uses HASH GROUP BY...

Is any any way i can make the query use HASH GROUP BY by using some hint?

Thanks
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308851 is a reply to message #308849] Tue, 25 March 2008 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean "does HASH GROUP BY exist?" not "is it used?". I read you said it is not used.
If it does not exist then it can't be used.

Regards
Michel
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308858 is a reply to message #308842] Tue, 25 March 2008 13:21 Go to previous messageGo to next message
rkg_007
Messages: 4
Registered: March 2008
Junior Member
Sorry if I was not clear.

The query's explain plan shows

HASH GROUP BY - in 10.2 (HASH GROUP BY feature exists in 10.2)
SORT GROUP BY - in 10.1 (not sure if HASH GROUP BY exists in 10.1)


So i am looking for a way so that the query can show HASH GROUP BY in 10.1 if at all.(by using some hints or something)

Thanks in advance

[Updated on: Tue, 25 March 2008 13:22]

Report message to a moderator

Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308860 is a reply to message #308858] Tue, 25 March 2008 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You were perfactly clear.
In 10.2 your query uses HASH GROUP BY
In 10.1 your query uses SORT GROUP BY

My question now is does HASH GROUP BY an existing operation in 10.1? You have to first investigate this point.

Regards
Michel
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308865 is a reply to message #308858] Tue, 25 March 2008 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
HASH GROUP BY was indeed introduced in 10.2.

Regards
Michel
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308872 is a reply to message #308842] Tue, 25 March 2008 14:54 Go to previous messageGo to next message
rkg_007
Messages: 4
Registered: March 2008
Junior Member
Yes. I was researching about it. Seems you are right.
I may have to change the query to avoid using GROUP BY.

Thanks for your help
Re: Oracle HASH GROUP BY & SORT GROUP BY performance [message #308890 is a reply to message #308842] Tue, 25 March 2008 18:32 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Yea hash group by was introduced in 10.2 however you can check why sort is too long and whether it is so because of sorts on disk. One of the easiest ways to check it is using autotrace, or you can try also v$mystat and appropriate statistic from v$statname.
If there are sorts on disk then there are two possibilities:
1) increase pga_aggregate_target if you are using workarea_size_policy = auto keeping in mind that maximum only a portion of it (~ 5%) can be used for a single session.
2) increase sort_area_size if you are using workarea_size_policy = manual. Better do that only for this particular session. In fact you can also in this session switch to workarea_size_policy = manual.
More sort space should help assuming of course this is the reason why the query is slow.

Gints Plivna

[Updated on: Wed, 26 March 2008 01:46] by Moderator

Report message to a moderator

Previous Topic: altering rollback segment
Next Topic: Usuage of hints.
Goto Forum:
  


Current Time: Sun Dec 04 23:09:48 CST 2016

Total time taken to generate the page: 0.20805 seconds