解决ora-01652无法通过128(在temp表空间中)扩展temp段的过程
昨天开发人员跟我说,执行一个sql语句后,大约花了10分钟,好不容易有一个结果,但是报了一个ora-01652错误,查阅了oracle的错误代码说明:意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因:一是临时表空间空间太小,二是不能自动扩展。
昨天开发人员跟我说,执行一个sql语句后,大约花了10分钟,好不容易有一个结果,但是报了一个ora-01652错误,查阅了oracle的错误代码说明:意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因:一是临时表空间空间太小,二是不能自动扩展。
分析过程:
好了,既然问题出在临时表空间,那就查看一下临时表空间信息:
SELECT a.TASKID, a.EXAMTYPECODE, a.CONTROLDEPARTMENT, a.DEFAULTRECRUITSPEC, a.ISUPCUNTRYLINE,
a.ISUPCOLLEGELINE, a.ISMAYJOINRETRIAL, a.JOINRETRIALGIST, a.JOINRETRIALBAK, a.TEMPERSIGN,
a.RECRUITSTUDENTTYPE, a.MATRICULATEGIST, a.ISNOTICEEXAMINEE, a.AFTERHANDLEDEP, a.TUITION,
a.KSBH, a.BH, a.XXB, a.BMDDM, a.BMDMC,
a.BMH, a.XM, a.XMPY, a.ZJLX, a.ZJLXMC,
a.ZJHM, a.CSRQ, a.MZM, a.MZ, a.XBM,
a.XB, a.HFM, a.HF, a.ZZMMM, a.ZZMM,
a.HKSZSSM, a.HKSZSSMC, a.HKSZDXXDZ, a.CSDSSM, a.CSDSSMC,
a.CSDXXDZ, a.XXGZDW, a.TXDZ, a.YZBM, a.LXDH,
a.DZXX, a.BYNY, a.XLM, a.XL, a.XWM,
a.XW, a.KSLYM, a.KSLY, a.KSFSM, a.KSFS,
a.BKLBM, a.BKLB, a.DASZDWSSM, a.DASZDWSSMC, a.DASZDW,
a.DASZDWDZ, a.DASZDWYZBM, a.XXGZJL, a.BYXX, a.BYXX1,
a.BYXX2, a.BYXX3, a.BYXX4, a.JL, a.JTCY,
a.BYDW, a.BYDWM, a.BYZYDM, a.BYZYMC, '0'||a.DWDM DWDM,
a.DWMC, a.TJDWDM, a.TJDWMC, a.ZYDM, a.ZYMC,
a.YXSM, a.YXSMC, a.YJFXM, a.YJFX, a.DSXM,
a.ZZLLM, a.ZZLLMC, a.WGYM, a.WGYMC, a.YWK1M,
a.YWK1MC, a.YWK2M, a.YWK2MC, a.YWK3M, a.YWK3MC,
a.XYJRM, a.XYJR, a.JFBZ, a.ZXBZ, a.BMSJ,
a.BYXXBZ, a.SFZH, a.BYND, a.KSDW, a.DWSZSSM,
a.DWSZSSMC, a.ZZLL, a.WGY, a.YWK1, a.YWK2,
a.YWK3, a.ZF, (NVL(a.fscj,0)) FSCJ, a.LQLBM, a.LQLB,
a.DXWPDW, a.PG, a.PGBZ, a.BZ, a.WDDWLM,
a.WDDWL, a.JFLY, a.LQQK, a.BLDW, a.NRXNY,
a.LQND, a.BZ1, a.BZ2, a.KCH, a.KCDD,
a.ZWH,a.ZPPATH, a.XH, a.BKZYM, a.BKZY,
a.XVH,a.DY6FLAG, a.BXYWK1MC, a.BXYWK2MC, a.BXYWK3MC,
a.QKBZ1, a.QKBZ2, a.QKBZ3, a.QKBZ4, a.QKBZ5,
a.SSMZLQ, a.KSQK, a.JGM, a.JGS, a.JGSX,
a.NLDM, a.NL, a.DXZYM, a.DXZY, a.HKSZDSX,
a.DQDM, a.YDWYB, a.BRTXZ, a.GZDWZ, a.YDWLM,
a.DWLB, a.BRYB, a.BRDH, a.JTDWM, a.GL,
a.TSLB, a.TSLBM, a.XXFSM, a.XXFS, a.LQZYM,
LQZY, ZPCJ, DSXHMH1, DSXHMH2, DSXHMH3,
a.DSXHMH4, a.SZSSM, a.ZXJH, a.ZXJHBZ, a.BLZGNX,
BLZG, BLZG1, BLZBZ, JS1MC, JS1CJ,
a.JS2MC, a.JS2CJ, a.SSMZ, a.SSMZBZ, a.DXWPSSM,
a.DXWPSS, a.LQXSM, a.LQXSMC, a.LQDSM, a.LQDSXM,
a.LQYJFXM, a.LQYJFXMC, a.HANDLERESULT, a.XLZSBH, a.XWZSBH,
a.LQYJFXID, a.XXXS,nvl2(a.reexam , ROUND(GYZF),(zf * ( 1- 0.3))) GYZF,a.GYFS, a.GYCS,
b.reexam ,a.SPECIALITYNO,a.SPECIALITYNAME,
(
SELECT STUDENTTYPE FROM CODE_STUDENTTYPE
WHERE STUDENTTYPECODE=A.recruitstudenttype ) STUDENTTYPE,
(SELECT collegename FROM ENROL_COLLEGEINFO)
FSDWMC,
(SELECT collegecode FROM ENROL_COLLEGEINFO) FSDWDM,
(CASE WHEN a.handleresult='已录取' THEN '1' ELSE '0' END ) nlq,'' HKSZDM
FROM
( SELECT X.*, Y.SPECIALITYNO,Y.SPECIALITYNAME FROM ENROL_EXAMINEE X,
BASE_SPECIALITY Y WHERE X.DEFAULTRECRUITSPEC=Y.SPECSTANDCODE AND x.CONTROLDEPARTMENT IS NOT NULL
) A
,(SELECT taskid,reexam FROM ENROL_EXAMINEE) b WHERE a.taskid=b.taskid
ORDER BY A.KSBH ASC
FROM
SQL> set serveroutput on
SQL> exec show_space('ENROL_EXAMINEE','AUTO');
Total Blocks............................2432
Total Bytes.............................19922944
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................11
Last Used Ext BlockId...................301576
Last Used Block.........................128
计算一下基表占用19922944字节,大约是19M。还不包括其它的表的字段统计。先将temp数据文件自动扩展。先不管这个sql语句是否优化,做一个执行计划和统计分析,得到如下结果:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation
|
Name
| Rows
| Bytes |TempSpc| Cost
|
|
0 | SELECT STATEMENT
|
|
9023K|
9130M|
|
3579K|
|
1 |
SORT ORDER BY
|
|
9023K|
9130M|
19G|
3579K|
|* 2 |
HASH JOIN
|
|
9023K|
9130M|
|
439 |
|
3 |
TABLE ACCESS FULL
| ENROL_EXAMINEE
| 15146 |
310K|
|
231 |
|* 4 |
TABLE ACCESS BY INDEX ROWID| ENROL_EXAMINEE
|
596 |
591K|
|
2 |
|
5 |
NESTED LOOPS
|
|
4171 |
4236K|
|
170 |
|
6 |
TABLE ACCESS FULL
| BASE_SPECIALITY
|
84 |
2016 |
|
2 |
|* 7 |
INDEX RANGE SCAN
| EXAMINEE_DEFAULTSPEC_INDEX
|
202 |
|
|
1 |
----------------------------------------------------------------------------------------------------
| Id
|
|
|*
|
|*
|
|
|*
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SYS_ALIAS_1"."TASKID"="ENROL_EXAMINEE"."TASKID")
4 - filter("SYS_ALIAS_1"."CONTROLDEPARTMENT" IS NOT NULL)
7 - access("SYS_ALIAS_1"."DEFAULTRECRUITSPEC"="Y"."SPECSTANDCODE")
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
filter("SYS_ALIAS_1"."DEFAULTRECRUITSPEC" IS NOT NULL)
Note: cpu costing is off
--------------------------------------------------------------------------------
Note: cpu costing is off
由此可知,临时表空间扩展到19G。这当然会把临时表空间全部吃掉。这时应该从sql语句本身来找问题。
那个sql语句基表大约有15000,而且涉及将近100个字段,最后还有一个非常耗费资源的排序操作。去掉那个order by 查看记录的返回数。
发现返回的记录数为26950729。这对我们数据库的硬件配置来说是一个很大数量级的数据,而且字段有非常多,所以占用既定的temp表空间的之后还不停的扩展,如果没有设置自动扩展,必然导致无法分配temp段。所以那个问题的产生就是必然的啦。现在追究为什么基表只有15000多条,而查询结构却是26950729条,所以怀疑是sql语句中存在cartesian,于是开始从业务需求来分析这个语句,果然是将最后一个(SELECT taskid,reexam FROM ENROL_EXAMINEE) b 中的基表应该是enrol_task而不是enrol_examinee,因为会产生两个表同个taskid来连接,而每个taskid下有很多学生,这就导致产生了一个庞大的cartesian乘积。最终导致对26950729条记录的排序而使临时表空间用尽。(此时没有自动扩展)将b结果集中ENROL_EXAMINEE用ENROL_TASK来替换,即使还有一个排序,结果也会在很短的时间内返回。
那个sql语句基表大约有15000,而且涉及将近100个字段,最后还有一个非常耗费资源的排序操作。去掉那个order by 查看记录的返回数。
发现返回的记录数为26950729。这对我们数据库的硬件配置来说是一个很大数量级的数据,而且字段有非常多,所以占用既定的temp表空间的之后还不停的扩展,如果没有设置自动扩展,必然导致无法分配temp段。所以那个问题的产生就是必然的啦。现在追究为什么基表只有15000多条,而查询结构却是26950729条,所以怀疑是sql语句中存在cartesian,于是开始从业务需求来分析这个语句,果然是将最后一个(SELECT taskid,reexam FROM ENROL_EXAMINEE) b 中的基表应该是enrol_task而不是enrol_examinee,因为会产生两个表同个taskid来连接,而每个taskid下有很多学生,这就导致产生了一个庞大的cartesian乘积。最终导致对26950729条记录的排序而使临时表空间用尽。(此时没有自动扩展)将b结果集中ENROL_EXAMINEE用ENROL_TASK来替换,即使还有一个排序,结果也会在很短的时间内返回。
最后需要说明的是:
1、sql语句完成之后,需要检查记录的准确性。
2、尽量不要在视图中进行order by ,这是一个非常耗费资源的操作。
写下拉,以存警示!