Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

SELECT
count(distinct userid) n, col
FROM users
GROUP BY col;

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

Now we rewrite it as

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

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

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

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|   |   |      |
--------------------------------------------------------------------------------------------------------------------------------------------
share|improve this question
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 '12 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 '12 at 20:09
add comment

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.

share|improve this answer
    
10 seconds is what the full table scan takes. You think this is too much? I doubt and index would actually improve things considering the whole table needs to be read anyways? –  MK01 Feb 14 '12 at 16:41
2  
If you select only col and COUNT(DISTINCT userid) it will only read the index. –  ypercube Feb 14 '12 at 16:50
1  
@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? –  Leigh Riffel Feb 14 '12 at 16:54
    
Index slows it down by 1 second. –  MK01 Feb 14 '12 at 16:55
    
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. –  ypercube Feb 14 '12 at 16:59
show 7 more comments

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.

share|improve this answer
    
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 '12 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? –  jonearles Feb 16 '12 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 '12 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? –  jonearles Feb 17 '12 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 '12 at 13:29
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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