I'm getting poor performance from DISTINCT. The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right. I would expect some kind of HASH aggregation to produce much better result. Is there a hint to tell oracle to use HASH for DISTINCT rather than sort? I've used /*+ USE_HASH_AGGREGATION */ in similar situations, but it is not working for DISTINCT.

So this is my original query:

count(distinct userid) n, col
FROM users

users has 30M rows, each userid is there 12 times. This query takes 70 seconds.

Now we rewrite it as

count(userid) n, col
(SELECT distinct userid, col FROM users)

And it takes 40 seconds. Now add the hint to do hash instead of sort:

count(userid) n, col
(SELECT /*+ USE_HASH_AGGREGATION */ distinct userid, col FROM users)

and it takes 10 seconds.

If somebody can explain to me why this is happening or how I can beat the first simple query into working as good as the 3rd one, that would be fantastic.
The reason I care about query simplicity is because these queries are actually generated.

Plans: 1) Slow:

| Id  | Operation      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | Used-Tmp|
|   0 | SELECT STATEMENT   |               |      1 |        |      5 |00:01:12.01 |     283K|    292K|       |       |      |     |
|   1 |  SORT GROUP BY     |               |      1 |      5 |      5 |00:01:12.01 |     283K|    292K|   194M|   448K|  172M (0)|   73728 |
|   2 |   TABLE ACCESS FULL| USERS |      1 |     29M|     29M|00:00:08.17 |     283K|    283K|       |       |      |     |

2) Fast

| Id  | Operation        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT     |               |      1 |        |      5 |00:00:13.09 |     283K|    283K|   |   |      |
|   1 |  SORT GROUP BY       |               |      1 |      5 |      5 |00:00:13.09 |     283K|    283K|  3072 |  3072 | 2048  (0)|
|   2 |   VIEW               |               |      1 |   8647K|   2445K|00:00:13.16 |     283K|    283K|   |   |      |
|   3 |    HASH UNIQUE       |               |      1 |   8647K|   2445K|00:00:12.57 |     283K|    283K|   113M|    10M|  216M (0)|
|   4 |     TABLE ACCESS FULL| USERS         |      1 |     29M|     29M|00:00:07.68 |     283K|    283K|   |   |      |
  • 1
    Are you running them one after the other in the same system? What happens from a performance perspective if you run them in a different order?
    – Adam Musch
    Feb 14, 2012 at 19:58
  • I've been running them in different order. Also I'm pretty much the only user on the system and it has over 100Gb of RAM so all of this crap should be in cache.
    – MK01
    Feb 14, 2012 at 20:09

2 Answers 2


Can you add indexes? I would first try adding an index on (col, userid).

10 seconds looks too much for a 30M rows table.

  • 3
    If you select only col and COUNT(DISTINCT userid) it will only read the index. Feb 14, 2012 at 16:50
  • 2
    @MK01 Why would it need to read the table at all with such an index? Are there other columns you left out of your examples? Feb 14, 2012 at 16:54
  • 1
    I'm no expert in Oracle. But I've run more complex aggegate queries in MySQL (which is not an enterprise DBMS), in tables of 100M+ rows in less than 1 sec. 10 secs seems too high for sucha simple quey when an idex is there. Feb 14, 2012 at 16:59
  • 1
    I think you should add in the description of your question the table's definition and the query plan. Feb 14, 2012 at 21:22
  • 1
    Well, the lengthy columns is probably the explanation why the optimizer does not use the index. Please add, at least the related columns (VARCHAR2(100) or VARCHAR2(5000)?) and the indexes you have. I'm sure someone with more experience in Oracle will find this useful so they can answer. Feb 14, 2012 at 21:51

Is hash group by aggregation disabled for your system or session? Does this query return FALSE:

select value from v$parameter where lower(name) = '_gby_hash_aggregation_enabled';

The hint USE_HASH_AGGREGATION will still work if the parameter is FALSE, but queries will never use HASH GROUP BY.

If it's not disabled, you probably should disable it. I think hash group by aggregation is just completely broken, and should never be used. It returns wrong results, and can throw ORA-600 errors if you use lots of memory.

For example, see bug "4604970: WRONG RESULTS...". Supposedly fixed in 11.1.0, but it's still broken. Try this query:

select stddev(test), count(distinct test) from
    select 7/9 test from dual
    union all
    select 7/9 test from dual

If "_gby_hash_aggregation_enabled"=false; it will return 0 and 1. If it's set to true, it will fail with ORA-01428: argument '-.00000000000000000000000000000000000001' is out of range. This is because the values are just slightly off. I've tried this on several versions of 10g and 11gR2.

I think this is the worst possible type of bug. If you're very lucky you'll see an error like the one above. But most likely all of your results will just be slightly off.

  • I can't find any traces of _gby_hash_aggregation_enabled being set anywhere. However everything indicates that it is disabled. Your test query works fine on this server and when I try it on another one it chooses hash aggregation and fails like you describe. This is very depressing because I can't get performance I need out of the SORT aggregation.
    – MK01
    Feb 16, 2012 at 13:38
  • What's different between the two servers? Anything obvious in v$parameter? Maybe something like _hash_join_enabled=false, or a very low value of hash_area_size with workarea_size_policy set to MANUAL?
    – Jon Heller
    Feb 16, 2012 at 14:38
  • So there are 2 servers, let's call them prod and dev: prod={Enterprise; workarea_size_policy=AUTO; uses hash agg}, dev={Standard, workarea_size_policy=MANUAL; hash_area_size=2,048,000,000; doesn't use hash agg}.
    – MK01
    Feb 16, 2012 at 15:12
  • Maybe the difference between sorting and hashing algorithms is only indirectly related to the problem. There are some times when SORT GROUP BY performs better than HASH GROUP BY. Maybe this is one of those times, but something else is slowing the first query down. Both queries try to use roughly the same amount of memory, but the first query can't fit everything in memory and has to write to temp. Why can't SORT GROUP BY use more memory? What is the value of sort_area_size?
    – Jon Heller
    Feb 17, 2012 at 4:32
  • Interestingly, the dev server has sort_area_size set to 1024,000,000. As compared to the prod with 65,536 which sounds ridiculous? So perhaps dev server picks sort because it has so much memory assigned to sort area. Except is still makes no sense because everything points out to hash being faster.
    – MK01
    Feb 17, 2012 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.