一次SQL语法写法不规范造成的PGA耗用较大且不释放的案例分析

本文是讲解对碰到问题时的一个分析过程与分析方法，虽然篇幅较短，关键是思路，里面的oradebug可以开个专题讲解。

背景：

收到监控系统反馈，一个Linux系统上的RAC数据库的内存耗用较多，swap空间也使用较多。

分析过程：

1. 按照操作系统命令，对内存使用情况的进程进行排序

ps -aux | sort -k4nr | head -20

2. 对排名靠前的会话进行分析，且分析awr报告，发现内存耗用都是在PGA上

3. 对排名靠前的会话进行采用heapdump分析（这个可以看看消耗的内容）

数据库heapdump命令.

oradebug setospid 108856

oradebug unlimit

oradebug dump heapdump 5

oradebug tracefile_name

4. 对trace文件分析解读，发现超过90%的内存耗费在top uga heap段的qmxdpls_subheap部分

EXTENT 8 addr=0x7fe877963030

Chunk 7fe877963040 sz= 2072 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877963858 sz= 4264 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877964900 sz= 4184 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877965958 sz= 4264 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877966a00 sz= 4184 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877967a58 sz= 4264 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877968b00 sz= 4184 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

Chunk 7fe877969b58 sz= 4264 freeable "qmxdpls_subhea " ds=0x7fe8bc0ce278

5. 对qmxdpls_subhea进行分析，此部分是由解析xml时使用的，初步怀疑问题点是在程序调用xml时造成内存泄漏

6. 为了进一步对初步判断的点进行更深入的程序分析，定位找到相应的代码

select PGA_ALLOC_MEM/1024/1024/1024 GB,a.spid,b.machine,b.program,b.sql_id,b.event,b.status from v$process a,v$session b where a.addr=b.paddr

and PGA_ALLOC_MEM/1024/1024/1024>1 order by 1;

发现多个进程耗用PGA 4G，且都是一个sql_id，直接杀掉这些进程，释放部分空间，可是过一会，又起来了。

7. 分析sql代码，找到发现调用xml解析的点

代码里面有一段：XMLDOM.OPENDOCUMENT，上MOS进行查询，Oracle对此语句的写法有建议：在打开解析一个xml报文后，用XMLDOM.FREEDOCUMENT 释放内存，以免造成数据库crash。

8. 通知开发，对此sql进行修改，按照官方建议整改，后续监控发现，没有该问题发生。

参考MOS文档：

---END---