一般在10g的时候，开发习惯使用WM_CONCAT进行字符串的拼接，但是该函数效率存在比较大的问题，在12c中甚至移除了该函数。本次在11g环境在对该函数进行测试，并与listagg进行对比。

初始化环境

create table t1(
id1 number,
id2 varchar2(30)
);
insert into t1 values (1,'aa');
insert into t1 values (1,'bb');
insert into t1 values (1,'cc');
insert into t1 values (2,'aa');

11g中测试

数据库版本为11.2.0.4.0

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

对比测试

SQL> set autot traceonly;
SQL> set timing on;
SQL> select id1 ,WMSYS.WM_CONCAT(id2) as con from t1 group by id1;

Elapsed: 00:00:00.53

Execution Plan
----------------------------------------------------------
Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   120 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     4 |   120 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        413  recursive calls
         24  db block gets
        564  consistent gets
        136  physical reads
          0  redo size
       2124  bytes sent via SQL*Net to client
       1792  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
         27  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL> select id1 , listagg(id2, ',') within group(order by null) as con from t1 group by id1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   120 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     4 |   120 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          7  physical reads
          0  redo size
        652  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

两个执行计划最明显的区别是listagg不存在db block gets，同时逻辑读和物理读也比较少。

使用runstats_pkg批量测试

创建2个测试存储过程

create or replace procedure p1 
as
  v_sql varchar2(3000);
begin
  v_sql := 'select id1 ,WMSYS.WM_CONCAT(id2) as con from t1 group by id1';
  for i in 1 .. 10000 loop
    execute immediate v_sql;
    end loop;
end;
/
create or replace procedure p2
as 
  v_sql varchar2(3000);
begin
  v_sql := q'[select id1 , listagg(id2, ',') within group(order by null) as con from t1 group by id1 ]';
  for i in 1 .. 10000 loop
    execute immediate v_sql;
    end loop;
end;
/

开始测试

set serveroutput on;
alter system flush shared_pool;

exec runStats_pkg.rs_start;
exec p1;
exec runStats_pkg.rs_middle;
exec p2;
exec runStats_pkg.rs_stop(1000);


Run1 ran in 12 cpu hsecs
Run2 ran in 9 cpu hsecs
run 1 ran in 133.33% of the time

Name                                  Run1        Run2        Diff
STAT...Elapsed Time                  2,859       1,223      -1,636
LATCH.cache buffers chains           3,321         405      -2,916
LATCH.shared pool                   13,426      10,305      -3,121
LATCH.row cache objects             13,126         236     -12,890
STAT...session uga memory                0      65,480      65,480
STAT...session pga memory max            0     131,072     131,072
STAT...session uga memory max      146,520           0    -146,520
STAT...session pga memory          -65,536     196,608     262,144
STAT...logical read bytes from   6,496,256   1,171,456  -5,324,800

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
34,319      12,412     -21,907    276.50%

可以看出WM_CONCAT函数的LATCH争用比较厉害，效率较差。
因为函数的内部具体算法看不到，猜想可能是WM_CONCAT遍历算法不够优化吧。

总结

推荐在11g以上版本使用listagg取代WM_CONCAT进行字符串的拼接。