使用ash分析ORA-01652问题（r4笔记第36天)
公众号：杨建荣的学习笔记 · 作者：r4笔记第36天 · 发布：2015-02-04 20:51:04 · 原文链接
今天在检查生产库的问题的时候，收到开发的邮件，他们在运行一个job的时候报出了ora的错误，想让我们来看一下是什么原因。
ora错误是01652的错误，单纯来看是由于临时表空间不足造成的。
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
因为问题发生在上午，从shared pool里查看对应的sql已经查不到了，这个时候使用ash是一个很方便的方式。
参考问题发生的时间点，抓取了一个4分钟的ash报告。
首先看到时间基本都消耗在了两个程序上，其中一个还是toad连接进来的session.
| Module | Action | %Activity | ||
| USG01 | EnvelopeMT@ccbdbpr2 (TNS V1-V3) | 72.81 | UNNAMED | 72.81 |
| TOAD 11.0.0.116 | 21.58 | UNNAMED |
21.58 |
想看看具体的session情况,可以结合等待事件来分析一下，这个时候就可以很清楚的看到那个时间段的操作了。
| % Activity | % Event | Program | XIDs | ||||
| 6937, 1129 | 27.91 | db file sequential read | 21.29 | USG1C | EnvelopeMT@…2 (TNS V1-V3) | 148/240 [ 62%] | 0 |
| CPU + Wait for CPU | 6.04 | 42/240 [ 18%] | 0 | ||||
| 3992,34841 | 21.58 | direct path read | 18.99 | XXXXXX | Toad.exe | 132/240 [ 55%] | 0 |
| CPU + Wait for CPU | 2.59 | 18/240 [ 8%] | 0 | ||||
| 6844,29137 | 10.50 | db file sequential read | 10.22 | USG1C | EnvelopeMT@…2 (TNS V1-V3) | 71/240 [ 30%] | 0 |
| 384, 4043 | 9.93 | db file sequential read | 6.62 | PRDUSG1C | pm1EnvelopeMT@…2 (TNS V1-V3) | 46/240 [ 19%] | 23 |
| CPU + Wait for CPU | 3.31 | 23/240 [ 10%] | 14 | ||||
| 7130, 5817 | 5.18 | db file sequential read | 5.04 | PRDUSG1C | pm1EnvelopeMT@…2 (TNS V1-V3) | 35/240 [ 15%] | 0 |
但是分析sql语句的时候却没有发现toad相关的session执行的sql语句。
| Planhash | % Activity | % Event | % RwSrc | Event | ||||
| 28kbzsqpfpp7j | 421773076 | 1 | 27.91 | db file sequential read | 21.29 | TABLE ACCESS – BY LOCAL INDEX ROWID | 18.56 | SELECT RE.L3_NET_START_TIME, R… |
| CPU + Wait for CPU | 6.04 | SORT – ORDER BY | 3.02 | |||||
| 0g5pzj39xvd1a | 2387198001 | 30 | 7.77 | db file sequential read | 6.76 | UPDATE | 6.47 | UPDATE RATED_EVENT SET L3_NET_… |
| CPU + Wait for CPU | 1.01 | UPDATE | 0.86 | |||||
| gqy0gxb05ycg4 | 958688106 | 28 | 5.76 | CPU + Wait for CPU | 5.76 | SELECT STATEMENT | 3.45 | /* */ SELECT CYCLE_CODE, CYCLE… |
| cwwa33b2a8byf | 421773076 | 1 | 5.32 | db file sequential read | 5.32 | TABLE ACCESS – BY LOCAL INDEX ROWID | 5.18 | SELECT RE.L3_NET_START_TIME, R… |
| 2c88v51gn6zxt | 421773076 | 1 | 5.04 | db file sequential read | 4.89 | TABLE ACCESS – BY LOCAL INDEX ROWID | 4.89 | SELECT RE.L3_NET_START_TIME, R… |
从以上问题可以简单的分析出，资源的消耗在一个job和toad相关的session上，至于toad的进程在那个时间点在做什么通过ash还没有抓取到更详细的信息。但是可以从等待事件来看，是在做一个大查询。
根据系统的负载最后给出了几点建议。
首先是根据报告对那个时间点操作的客户进行确认，是否做了一些额外的操作。
然后从目前的系统角度来看，这个库的temp空间本身也存在着一定的不足，目前只有8G，需要做一定的扩展，因为库中有几个大表，都在百G级别，一些排序操作可能会消耗相当大的temp空间。
最后和开发确认资源消耗较多的sql语句。（28kbzsqpfpp7j)
开发给出的反馈是这个job的程序很久没有更新了。我简单检查了一下最近的执行历史做了确认。
所以问题就锁定在两个方面，一个是toad相关的session导致的，一个是temp空间不足造成的（一种可能甚至是toad对应的session消耗了一部分的temp空间，到了sql_id(28kbzsqpfpp7j) temp空间或者说sql_id在做排序操作的时候消耗的temp空间过大）
排除了其他原因之后，再次尝试跑就没有问题了。对于临时表空间的扩展也在稍后做了添加。