rac下的TS enq解决方案–适用于rac数据仓库环境

公司里面有一个12nodes的rac环境来跑数据仓库的应用，有一个1TB的临时表空间，有一些应用会跑的很慢，比如多个大表关联，而且有复杂的分组排序的操作会耗费大量的临时表空间，这些应用有事会跑的比较快，有时会跑的莫名其妙的慢，这是为什么呢？

花了点时间看了一下原因，
模块1正常的时候15分钟完成，但是慢的时候会跑1个半小时，原来有些的P0XX进程都在等待”enq: TS – contention”,根据gv$session_wait我们可以通过p2找到tablespace ID，进而可以发现是临时表空间出现了TS enq，然后可以发现是smon进程持有了这enq，这些P0XX的并行进程都在等待smon去释放这个enq，但是smon这时候在做什么呢？
那首先让我们来看一下rac里面的temp空间分配的机制是怎么样的。在rac里面，大家都知道temp表空间对所有节点都是可见的，但是temp空间一旦分配给了某一个节点使用，其他节点对这部分空间将都会看不见。

很典型的情况就像下面这样，每个节点有自己的temp extent

INST_ID TSNAME TOTAL USED FREE  ---------- ------- ----- ---- -----  1 TEMP 6678 0 6678  2 TEMP 83966 0 83966  3 TEMP 8908 0 8908  4 TEMP 1589 0 1589  5 TEMP 10006 0 10006  6 TEMP 12147 0 12147  7 TEMP 99 0 99  8 TEMP 414 0 414  9 TEMP 10913 0 10913  10 TEMP 2347 6 2341  11 TEMP 343 3 340  12 TEMP 16189 0 16189

一旦某个节点上发生了一个大的分组排序的操作，它目前分配到的temp exten将会被耗尽，这时候它会发出一个Cross Instance Call（CIC）来向每个节点请求新的temp extent,这时候smon就启动去回收每个节点的free temp extent,在这个过程中smon会持有TS enq，发起空间请求的节点必须等待smon完成对所有节点的free temp extent回收请求后才会继续下一步动作，smon每次向每个节点回收100 extents的临时空间，在这个12nodes的环境里面最大就是1200 extents，每个extent设置为8M,大致每次回收8GB的空间可以给发起节点使用，但是在一个很大操作面前，8GB的临时空间一下子就能用完。而且在一个多并发的系统里面，同时会有很多个大操作在进行，这个时候对于临时表空间的争用将会非常严重，会严重影响整个系统的执行效率。

下面来看一个实验
首先看一下当前系统个节点临时空间的分配情况

INST_ID TSNAME TOTAL USED FREE  ---------- ------- ----- ----- ---  1 TEMP 6378 0 6378  2 TEMP 82296 0 82296  3 TEMP 8908 0 8908  4 TEMP 1589 0 1589  5 TEMP 10006 0 10006  6 TEMP 12147 0 12147  7 TEMP 99 0 99  8 TEMP 414 0 414  9 TEMP 10913 0 10913  10 TEMP 2347 6 2341  11 TEMP 520 3 517  12 TEMP 16189 0 16189

我们可以看到7节点上当前只分配到了99个extents，

select sum(TOTAL_EXTENTS)*8/1024||'G' from gv$sort_segment;   SUM(TOTAL_EXTENTS)*8/1024||'G'  -----------------------------------------  1185.984375G

当前系统总共分配了1185.984375G的temp空间，总的temp表空间大小是1200G,整个系统里面还有1200-1186=14GB的空闲temp空间可以使用

下面登录到7节点执行一个耗费临时空间的操作

dwdb7> exec big_sort(null);

过了一会可以看到

select sum(TOTAL_EXTENTS)*8/1024||'G' from gv$sort_segment;  SUM(TOTAL_EXTENTS)*8/1024||'G'  -----------------------------------------  1199.9921875G

系统的空闲临时空间已经被用完，这个时候7节点会通过CIC请求smon释放其他节点的空闲temp空间，可以看到节点7现在的等待

Taobao@dwdb7> /   EVENT  ----------------------------------------------------------------  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  PX Deq Credit: send blkd  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention  enq: TS - contention

再看各节点temp空间的分布

Taobao@dwdb9> select INST_ID,TABLESPACE_NAME TSNAME,  TOTAL_EXTENTS TOTAL,USED_EXTENTS USED,FREE_EXTENTS  FREE from gv$sort_segment order by inst_id;   INST_ID TSNAME TOTAL USED FREE  ---------- --------------- ----------  1 TEMP 6278 0 6278  2 TEMP 82196 0 82196  3 TEMP 8808 0 8808  4 TEMP 1489 0 1489  5 TEMP 9906 0 9906  6 TEMP 12047 0 12047  7 TEMP 2992 2992 0  8 TEMP 314 0 314  9 TEMP 10813 0 10813  10 TEMP 2247 3 2244  11 TEMP 420 3 417  12 TEMP 16089 0 16089

smon完成一轮空间回收，节点7继续进行排序操作，但是可以看到所有的其他节点free_extents都少了100

INST_ID TSNAME TOTAL USED FREE  ---------- -------------------------  1 TEMP 6178 0 6178  2 TEMP 82096 0 82096  3 TEMP 8708 0 8708  4 TEMP 1389 0 1389  5 TEMP 9806 0 9806  6 TEMP 11947 0 11947  7 TEMP 3728 3728 0  8 TEMP 214 0 214  9 TEMP 10713 0 10713  10 TEMP 2147 3 2144  11 TEMP 320 3 317  12 TEMP 15989 0 15989

然后节点7用完smon回收的空间后又会碰到空间不够的问题，于是发起CIC请求smon会继续回收，

INST_ID TSNAME TOTAL USED FREE  ---------- ------ -------- -------------  1 TEMP 6078 0 6078  2 TEMP 81996 0 81996  3 TEMP 8608 0 8608  4 TEMP 1289 0 1289  5 TEMP 9706 0 9706  6 TEMP 11847 0 11847  7 TEMP 5192 5192 0  8 TEMP 114 0 114  9 TEMP 10613 0 10613  10 TEMP 2047 3 2044  11 TEMP 220 117 103  12 TEMP 15889 0 15889

周而复始，直到整个操作完成，这中间的回收过程会比较久，而且排序操作会一直等待smon从而影响整个应用模块的时间，而且由于各个节点分配到的temp extent严重贫富不均，如果一个sql刚好分配到了某个temp extent比较少的节点，它将会深受其害。

问题的原因相信大家都看明白了，下面就来看一下解决的方案，Oracle提供了一个命令来释放temp空间

ALTER SESSION SET events ‘immediate trace name drop_segments level tablespace_number+1′;

现在节点1有5478个free temp extent,

 INST_ID TSNAME TOTAL USED FREE  ---------- ---------- -------- ---------- -------  1 TEMP 5478 0 5478

登录到节点1执行两次ALTER SESSION SET events ‘immediate trace name drop_segments level 4′后将会释放出200个extent

INST_ID TSNAME TOTAL USED FREE  ---------- ---------- -------- ---------- -------  1 TEMP 5278 0 5278

这样的话我们就可以自己包装到crontab，定时在每个节点执行这个命令，这样节点间贫富不均的情况将会被修正，各个节点的空闲temp空间将会被及时的返回到temp pool中供需要的节点使用，也就避免了sql会去等待smon回收，基本上相当于每个节点有自己的专属的temp空间的效果，修正了RAC在temp空间管理这块的缺陷。