wm_concat和listagg

最近某客户数据库做升级，10.2.0.4升级到11.2.0.4，某些业务查询出现性能问题，升级前后执行时间差距 近10倍。
源系统背景：
DB：10.2.0.4
OS: AIX

新环境背景:
DB：11.2.0.4
OS:linux 6.6

接到客户通知，我首先远程登录客户系统查看。 1、 检查应用所在主机负载—检查正常 2、 检查ORACLE所在主机负载情况，检查发现基本都是ORACLE进程导致的资源消耗 3、 接着我查看数据库会话情况如下图，发现运行的SQL消耗大量临时段。

4、 接着查看涉及的SQL 执行计划，均正常。 5、 前面发现占用大量的临时段，让我觉得不可思议，理论上：只有存在如下情况才会出现临时段的大量使用 1、 HASH JOIN 内存不够借助TEMP表空间 2、SORT 内存不够借助TEMP表空间 3、临时对象占用temp表空间 4、LOB 对象占用TEMP表空间 2、 从上面监控可以发现主要消耗临时段的对象类型是LOB_DATA 3、 我去查询了SQL 相关的表对象，未发现这些表里面定义了LOB相关的字段。 4、 接着排除法：排除语句里面涉及可能使用TEMP 的地方：发现如下位置      当我去掉这里的时候，该SQL在没有使用到TEMP。      初步定位是由于VM_CONCAT导致 6、 在MOS上面查询到如下关于wm_concat的说明   SQL USING WM_CONCAT RUNS SLOWER IN 10.2.0.5 and 11.2.0.2 (Doc ID 1393596.1) To Bottom In this Document   Symptoms   Changes   Cause   Solution   References 1.1  APPLIES TO: Oracle Server - Enterprise Edition - Version: 11.2.0.2 to 11.2.0.3 - Release: 11.2 to 11.2 Oracle Server - Enterprise Edition - Version: 10.2.0.5 to 10.2.0.5   [Release: 10.2 to 10.2] Information in this document applies to any platform. 1.2  SYMPTOMS SQLs using WM_CONCAT function run slower in 11.2.0.2 and 10.2.0.5 onward. Execution plans  can be exactly same as previous releases that used to run well. TKPROF shows much more QUERY and CURRENT gets. AWR or Statspack statistics show more counts for 'lob reads' and 'lob writes'. WMSYS.WM_CONCAT is Workspace Manager internal and undocumented function and not intended  to be used by customer applications.  This function concatenates the values of the input column. For  example: select deptno,wm_concat(ename) from emp group by deptno ; DEPTNO WM_CONCAT(ENAME) ---------- -------------------------------------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 1.3  CHANGES   Upgraded database to 10.2.0.5 or 11.2.0.2 ( and above ). The definition of WMSYS.WM_CONCAT function has changed in 11.2.0.2 and 10.2.0.5 onward to  use CLOB. 10.2.0.5, 11.2.0.2+ FUNCTION wm_concat( P1 VARCHAR2 IN ) RETURNS CLOB. 10.2.0.4-, 11.1.0.7-, 11.2.0.1  FUNCTION wm_concat( P1 VARCHAR2 IN ) RETURNS VARCHAR2 1.4  CAUSE WMSYS.WM_CONCAT  using CLOBs has serious performance impact when it is accessed excessively. 1.5  SOLUTION Since WMSYS.WM_CONCAT is undocumented and not intended to be used by customer's application, it is suggested that customers write their own customized functions serving the purpose. 从上面可以发现，wm_concat函数官方是不推荐使用的，该函数在数据库版本是10.2.0.4,11.1.0.7,11.2.0.1中是返回的VARCHAR2类型 该函数在10.2.0.5和11.2.0.2+以上返回的是CLOB 类型 从上面可以解释为什么监控发现有大量的临时表空间消耗。同时也说明了使用CLOB字段的wm_concat函数性能上很低下。 分析到这里基本定位问题是由于数据库升级后，函数返回值发生变化导致了查询效率低下，同时也导致了大量的临时段被使用。   7、 如何替换wm_concat行专列函数 1、 ORACLE建议使用自定义函数。 2、 我个人推荐使用LISTAGG（11R2引入的新函数），因为其和10.2.0.4版本的WM_CONCAT一样返回的是VARCHAR2类型 3、 下面介绍下LISTAGG的使用办法： 1、可以发现其是等价的。（例子1） 例子2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31134212/viewspace-2136091/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31134212/viewspace-2136091/