One of the Oracle Database 11g features is to provide concatenated data for each group called LISTAGG. In prior versions we used different ways to achieve same results. Some of those are STRAGG and 10g introduced undocumented function WM_CONCAT.
Below I illustrated the differences in performance using these different function. I used same huge data set and same environment.
First look at ListAgg Vs WM_Concat
DEV> select id,listagg(paramlist,’;’) within group (order by paramlist) as agg_data 2 from KP_AGG_TEST 3 group by id; 287485 rows selected. Elapsed: 00:00:43.59 Execution Plan ——————————————————— Plan hash value: 2522236266 —————————————————————————————– Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————– 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | —————————————————————————————– Statistics ——————————————————— 67 recursive calls 20 db block gets 4077 consistent gets 8320 physical reads 0 redo size 11625136 bytes sent via SQL*Net to client 134407 bytes received via SQL*Net from client 19167 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk)
287485 rows processed
DEV> select id,wm_concat(paramlist) from KP_AGG_TEST
2 group by id;
287485 rows selected.
Elapsed: 00:29:54.60
Execution Plan
———————————————————-
Plan hash value: 2522236266
——————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 |
| 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 |
| 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 |
——————————————————————————————
Statistics
———————————————————-
51 recursive calls
3449842 db block gets
323291 consistent gets
6328 physical reads
0 redo size
103733023 bytes sent via SQL*Net to client
41398092 bytes received via SQL*Net from client
862457 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
287485 rows processed
As you can see, WM_CONCAT took longer than List Agg.
Now let’s look at LISTAGG vs STRAGG
Query 1: With below ListAgg query, aggregated data is returned in the order. DEV> select id,listagg(paramlist,’;’) within group (order by paramlist) as agg_data 2 from KP_AGG_TEST 3 GROUP BY ID; 287485 rows selected. Elapsed: 00:00:44.95 Execution Plan ———————————————————- Plan hash value: 2522236266 —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | —————————————————————————————— Statistics ———————————————————- 67 recursive calls 30 db block gets 4077 consistent gets 13152 physical reads 0 redo size 11644303 bytes sent via SQL*Net to client 134406 bytes received via SQL*Net from client 19167 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 287485 rows processed ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Query 2: Note that below STRAGG Query doesn’t return aggregated data in the order. Per returned data, this is little diff from listagg Dev> select id, stragg(paramlist) from kp_agg_test 2 group by id; 287485 rows selected. Elapsed: 00:02:37.82 Execution Plan ———————————————————- Plan hash value: 2522236266 —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | —————————————————————————————— Statistics ———————————————————- 287538 recursive calls 22 db block gets 1786494 consistent gets 6341 physical reads 0 redo size 18532148 bytes sent via SQL*Net to client 134406 bytes received via SQL*Net from client 19167 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 287485 rows processed ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Query 3:Below query returns aggregated data in the order, same as listagg. 10-NOV-11 LV_DEV@GALVD501> select id, stragg_order_by_item(paramlist) from kp_agg_test 2 group by id; 287485 rows selected. Elapsed: 00:02:46.53 Execution Plan ———————————————————- Plan hash value:2522236266 —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | —————————————————————————————— Statistics ———————————————————- 287539 recursive calls 30 db block gets 1786488 consistent gets 9938 physical reads 0 redo size 11904394 bytes sent via SQL*Net to client 134406 bytes received via SQL*Net from client 19167 SQL*Net roundtrips to/from client 287485 sorts (memory) 1 sorts (disk) 287485 rows processed Results Comparision Query 1: Elapsed time is less compare to 2 and 3 belowQuery 2:
– Elapsed time is high – Recursive calls, consistent gets high – CPU/Cost is same as listagg. – High Bytes sent via SQL*Net – Result set was not in the order as Query 1 Query 3: – Elapsed time is high – Recursive calls, consistent gets high – CPU/Cost is same as listagg. – High Bytes sent via SQL*Net – Result set was same as Query 1 Conclusion:Avoid using WM_CONCAT, which is moreover not documented.
If you are using stragg, you can switch them to LISTAGG. It is difficult to change lot of queries that are already present and at atleast start using LISTAGG over STRAGG. LISTAGG provides options like ordering, customized delimiter.
Oracle Enablement 