In the note I wrote a couple of days ago about the way the optimizer switches from hash group by to sort group by if you add an order by X,Y clause to a query that has a group by X,Y I mentioned that this had been something I’d noticed about 15 years ago (shortly after Oracle introduced hash aggregation, in fact) but it was only the second of two details I’d noted when experimenting with this new operation. The first thing I’d noticed came from an even simpler example, and here’s a (cryptic) clue to what it was:
column operation_type format a24 select operation_type, count(*) from V$sql_workarea group by operation_type order by operation_type ; OPERATION_TYPE COUNT(*) ------------------------ ---------- APPROXIMATE NDV 1 BUFFER 130 CONNECT-BY (SORT) 10 GROUP BY (HASH) 12 GROUP BY (SORT) 240 HASH-JOIN 466 IDX MAINTENANCE (SORT) 39 LOAD WRITE BUFFERS 10 RANGE TQ COMPUTATION 13 RESULT CACHE 4 SORT (v1) 10 SORT (v2) 147 WINDOW (SORT) 35
The clue isn’t in the query, it’s in what’s missing from the result set, so here’s some code to create and query some data to make it easier to spot the anomaly:
rem rem Script: hash_agg.sql rem Author: Jonathan Lewis rem Dated: Sept 2007 rem create table t1 as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid wordpress format issue ) select lpad(mod(rownum-1,1000),6) small_vc_K, lpad(rownum-1,6) small_vc_M from generator v1, generator v2 where rownum <= 1e6 -- > comment to avoid wordpress format issue ; set serveroutput off prompt =========================== prompt 1000 distinct values (hash) prompt =========================== select /*+ gather_plan_statistics 1000 */ count(*) from ( select /*+ no_merge */ distinct small_vc_K from t1 ) ; select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last')); prompt =========================== prompt 1000 distinct values (sort) prompt =========================== select /*+ gather_plan_statistics 1000 */ count(*) from ( select /*+ no_merge no_use_hash_aggregation */ distinct small_vc_K from t1 ) ; select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
I’ve added the /*+ gather_plan_statistics */ hint to the query so that I can check on the rowsource execution stats and (particularly) the memory and/or temporary space used; and I’ve blocked hash aggregation in one of the two queries, so I expect to see a “hash unique” operation in the first query and a “sort unique” operation in the second. Here’s what I get from 19.11.0.0:
=========================== 1000 distinct values (hash) =========================== COUNT(*) ---------- 1000 SQL_ID 1baadqgv02r6b, child number 0 ------------------------------------- select /*+ gather_plan_statistics 1000 */ count(*) from ( select /*+ no_merge */ distinct small_vc_K from t1 ) Plan hash value: 171694178 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 765 (100)| 1 |00:00:00.07 | 2637 | 2632 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.07 | 2637 | 2632 | | | | | 2 | VIEW | | 1 | 1000 | 765 (56)| 1000 |00:00:00.07 | 2637 | 2632 | | | | | 3 | HASH UNIQUE | | 1 | 1000 | 765 (56)| 1000 |00:00:00.07 | 2637 | 2632 | 1889K| 1889K| 1417K (0)| | 4 | TABLE ACCESS FULL| T1 | 1 | 1000K| 405 (17)| 1000K|00:00:00.04 | 2637 | 2632 | | | | ---------------------------------------------------------------------------------------------------------------------------------------- 17 rows selected. =========================== 1000 distinct values (sort) =========================== COUNT(*) ---------- 1000 SQL_ID a66rqhgw7a6dk, child number 0 ------------------------------------- select /*+ gather_plan_statistics 1000 */ count(*) from ( select /*+ no_merge no_use_hash_aggregation */ distinct small_vc_K from t1 ) Plan hash value: 1750119335 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 765 (100)| 1 |00:00:00.22 | 2637 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.22 | 2637 | | | | | 2 | VIEW | | 1 | 1000 | 765 (56)| 1000 |00:00:00.22 | 2637 | | | | | 3 | SORT UNIQUE | | 1 | 1000 | 765 (56)| 1000 |00:00:00.22 | 2637 | 48128 | 48128 |43008 (0)| | 4 | TABLE ACCESS FULL| T1 | 1 | 1000K| 405 (17)| 1000K|00:00:00.02 | 2637 | | | | -------------------------------------------------------------------------------------------------------------------------------
Look back at the summary of v$sql_workarea. Can you now spot something that might be missing? Since we’ve now got two execution plans with their sql_ids, let’s run a much more precise query against the view.
select sql_id, child_number, operation_id, operation_type , total_executions, last_memory_used/1024 last_mem_kb from v$sql_workarea where sql_id in ( '1baadqgv02r6b', 'a66rqhgw7a6dk' ) order by sql_id, child_number, operation_id / SQL_ID CHILD_NUMBER OPERATION_ID OPERATION_TYPE TOTAL_EXECUTIONS LAST_MEM_KB ------------- ------------ ------------ -------------------- ---------------- ----------- 1baadqgv02r6b 0 3 GROUP BY (HASH) 1 1,417 a66rqhgw7a6dk 0 3 GROUP BY (SORT) 1 42
The first thing to note, of course, is that the sort aggregate at operation 1 doesn’t report a workarea at all; but this shouldn’t be a surprise, the operation is simply counting rows as they arrive, there’s no real sorting going on.
Operation 3 in both cases is the more interesting one. In the plan it’s reported as a “hash/sort unique” but the workarea operation has changed this to a “group by (hash/sort)”. It’s a little detail that probably won’t matter to most people most of the time – but it’s the sort of thing that can waste a couple of hours of time when you’re trying to analyze a performance oddity.
And another thing …
You’ll have noticed, by the way, that the hash unique operation actually demanded far more memory than the sort unique operation; 1.4MB compared to 42KB. In a large production system this may be fairly irrelevant, especially since the discrepancy tends to disappear for higher volumes, and hardly matters if the operation spills to disc. However, if you have a large number of processes doing a lot of small queries using hash aggregation you may find that cumulatively they use up an unexpected fraction of your pga_aggregate_target.
There is some scope (with the usual “confirm with Oracle support” caveat) for modifying this behaviour with a fix_control:
16792882 QKSFM_COMPILATION_16792882 Cardinality threshold for hash aggregation(raised to power of 10) alter session set "_fix_control"='16792882:3';
The default value is 0, the maximum value seems to be 3 and the description about raising to power of 10 seems to be the wrong way round, but I haven’t done any careful testing. When I set the value to 3 the hash unique with an estimated output of 1,000 switched to a sort unique.(hence my suggestion about 10N rather than N10).
Footnote
In the previous note I pointed out that Oracle would use a single sort group by operation rather than a consecutive hash group by / sort order by pair of operations. This simpler example helps to explain why. If you check the CBO trace files for the two you can check the costs of the inline aggregate view.
From a 19.11.0.0 trace file here are two costing fragments for the hash group by strategy; the first bit is the cost of the tablescan that acquires the rows (operation 4) the second is the final cost of the inline view / query block:
Best:: AccessPath: TableScan Cost: 404.639881 Degree: 1 Resp: 404.639881 Card: 1000000.000000 Bytes: 0.000000 Final cost for query block SEL$2 (#2) - All Rows Plan: Best join order: 1 Cost: 764.843155 Degree: 1 Card: 1000000.000000 Bytes: 7000000.000000 Resc: 764.843155 Resc_io: 337.000000 Resc_cpu: 1069607888 Resp: 764.843155 Resp_io: 337.000000 Resc_cpu: 1069607888
I won’t show you the equivalent extracts for the example where I blocked hash aggregation because the numbers are identical. So there’s no way that Oracle will want to do hash group by followed by sort order by, when it can do just a single sort group by that costs exactly the same as the hash group by operation alone. This is a shame, and a little ironic because when Oracle Corp introduce hash aggregation they made a bit of a fuss about how much more efficient it was than sorting- but it looks like no-one told the optimizer about this.