SQL语句导致的临时表空间满案例

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

alert.log 后台报错

简单增加临时表空间后一段时间发现两个数据文件都自动扩展到32g,依然不够用,alert继续报错ORA-1652

查看等待事件top5

enq: TS – contention

此事件在于系统中临时表空间争用。

RAC里,所有节点共用同一个临时表空间,而temp extent pool一旦分配给某一个节点,其它节点将不可见;

一旦某个节点上分配的temp extent耗尽,则会发出cross instance call(CIC)向其它节点请求temp extent;

此时SMON就去回收所有节点的free temp extent,此过程会持有TS enqCIC必须等待SMON完成对所有节点的free temp extent完成回收才会继续;

此事件根本的解决方法在于优化耗用多度临时表空间的sql语句。

direct path write temp

此事件通常发生在Oracle直接从PGA写数据到临时文件,这个写操作可以绕过SGA

查看表空间、数据文件I/O状况


查询哪些sql使用temp表空间

利用sql查询,按占用temp从高到低排序

或者使用direct path write temp事件定位sql

基于以上,这种情况基本可以定位以下:

  • SQL导致temp文件过度使用;
  • SQL执行计划不是最优,需要进行优化;
  • SQL写法欠佳,需要进行改写。

利用direct path write temp等待事件查询sql语句:


可以判定sql_id6mrh17udacwt9是导致此次事件的sql。

查询完整sql语句

调整前的执行计划

利用DBMS_SQLTUNE.CREATE_TUNING_TASKDBMS_SQLTUNE.EXECUTE_TUNING_TASKsql进行调优

PL/SQL 过程已成功完成。

调整后的执行计划

调整后等待事件消失,数据库临时表空间正常。

未经允许不得转载:Oracle一体机用户组 » SQL语句导致的临时表空间满案例

相关推荐