Can anyone explain how the oracle "hash group" works?

Question

I've recently come across a feature of doing a large query in oracle, where changing one thing resulted in a query that used to take 10 minutes taking 3 hours.

To briefly summarise, I store a lot of coordinates in the database, with each coordinate having a probability. I then want to 'bin' these coordinates into 50 metre bins (basically round the coordinate down to the nearest 50 metres) and sum the probability.

To do this, part of the query is 'select x,y,sum(probability) from .... group by x,y'

Initially I was storing a large number of points with a probability of 0.1 and queries were running reasonably ok, taking about 10 minutes for each one.

Then I had a request to change how the probabilities were calculated to adjust the distribution, so rather than all of them being 0.1, they were different values (e.g. 0.03, 0.06, 0.12, 0.3, 0.12, 0.06, 0.03). Running exactly the same query resulted in queries of about 3 hours.

Changing back to all 0.1 brought the queries back to 10 minutes.

Looking at the query plan and performance of the system, it looked like the problem was with the 'hash group' functionality designed to speed up grouping in oracle. I'm guessing that it was creating hash entries for each unique x,y,probability value and then summing probability for each unique x,y value.

Can anyone explain this behaviour any better?

Additional Info

Thanks to the answers. They allowed me to verify what was going on. I'm currently running a query and the tempseg_size from v$sql_workarea_active is currently at 7502561280 and growing rapidly.

Given that the development server I'm running on only has 8gb of ram, it looks like the query needs to use temporary tables.

I've managed to workaround this for now by changing the types of queries and precalculating some of the information.

Answer 1

Hash group (and hash joins, as well as other operations such as sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. The last two methods use TEMP storage and is thus much slower.

By increasing the number of possible items you might have exceeded the number of items that will fit in memory reserved for this type of operations.

Try looking at v$sql_workarea_active whilst the query is running, to see if this is the case. Or look at v$sql_workarea for historical information. It will also give you an indication of how much memory and/or temp space is needed for the operation.

If turns out to be the actual problem - try increasing the pga_aggregate_target initialization parameter, if possible. The amount of memory available for optimal hash/sort operations is usually around a 5% fraction of the pga_aggregate_target.

See the Performance Tuning Guide for more detail.

Answer 2

"'m guessing that it was creating hash entries for each unique x,y,probability value and then summing probability for each unique x,y value" -- almost certainly so, since that is what the query requires.

You can check for the likelihood of a query requiring temporary dfisk space to complete a sort or group-by (etc) by using the explain plan.

explain plan for
select x,y,sum(probability) from .... group by x,y
/

select * from table(dbms_xplan.display)
/

If the optimizer can correctly deduce from statistics the approximate unique number of combinations of x and y then there's a pretty good chance that in the TempSpc column of the output of the second query it will show you just how much disk space (if any) will be required to complete the query (no column = no disk space requirement).

Way too much information here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i999234

If the temp space usage is high then as CaptP says, it may be time for some memory tweakage. On databases that perform a lot of sorts and aggregations it is common to specify a higher PGA target than an SGA target.

Answer 3

Is your *PGA_AGGREGATE_TARGET* set to zero by any chance? It's unlikely that it's the HASH GROUPBY on its own that caused the issue, it's probably something before it or after it. Downgrade your *OPTIMIZER_FEATURES_ENABLE* to 10.1.0.4 and rerun the query - you'll see that now you'll get a SORT GROUPBY which should pretty much always be outperformed by a HASH GROUPBY, unless your PGA sizing is set to MANUAL and your hash work area is undersized.