最近某客户数据库做升级，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博客

wm_concat和listagg

1.1 APPLIES TO:

1.2 SYMPTOMS

1.3 CHANGES

1.4 CAUSE

1.5 SOLUTION

最新文章