今天现场丢来一个sql,说是报数据库临时表空间不足,但是现场用到的两个库,临时表空间一个30g,一个60g,对于现场100万的用户数来说,足够了。
然后拿来现场的sql来看,sql本身也不复杂:
SELECT REGEXP_SUBSTR(VALUE, '[^|]+', 1, ROWNUM) tt
FROM (SELECT AV.VALUE
FROM PROD_ATTR_VALUE@LINK_CC AV, ATTR@LINK_CC AR
WHERE AV.ATTR_ID = AR.ATTR_ID
and AV.prod_id = '38813814'
AND AR.ATTR_CODE = 'TTCL_PARABOLIC_DETAILS')
CONNECT BY ROWNUM <= REGEXP_COUNT(VALUE, '[^|]+');
就是在库A通过dblink查询库B的两个表,做关联对取出的值通过指定的字符“|”分割字符串后列转行。
但是该sql在去掉dblink后直接在B库执行,很快就出结果了:
但是在A库通过dblink执行,查询就狂慢,而且查询到最后,就报了上面那个错误,说临时表空间不足。
后来分析两种情况下的执行计划,发下通过dblink执行的时候,会做二次全表关联查询:
直接在B库直接执行的执行计划:
在A库通过dblink执行的时候的执行计划:
发下两者执行计划上面一部分完全一样,其实上面就是子查询中的内容,但是下面的merge join cartesian的一部分就让人很不接,在我们看来就是不需要的,但是oracle又去走了一遍,后来发现这部分是connect by子句中的REGEXP_COUNT导致的,该子句使的为了这个统计把attr和prod_attr_value两个表全表扫描了一遍,而且这个扫描没有任何关联条件,所以导致了笛卡尔积很大,撑爆了temp表。知道了问题的原因,那么就要让oracle避免这个问题,改写后的sql如下:
with tt as (SELECT AV.VALUE as val
FROM PROD_ATTR_VALUE@LINK_CC AV, ATTR@LINK_CC AR
WHERE AV.prod_id = '38813814'
and AV.ATTR_ID = AR.ATTR_ID
AND AR.ATTR_CODE = 'TTCL_PARABOLIC_DETAILS'),
aa as (select REGEXP_COUNT(tt.val, '[^|]+') as cnt from tt )
SELECT REGEXP_SUBSTR(tt.val, '[^|]+', 1, ROWNUM) from tt,aa
CONNECT BY ROWNUM <= aa.cnt ;
测试后该语句通过dblink在A库查询的时候和在B库直接查询时间一样,如下:
改写后的执行计划:
成本跟直接在B库查询差别不大。