1. 首页
  2. IT资讯

由ORA-01652想到的 2

2、关于SORTHASH

无独有偶,我今天又在另外一个数据库中遇到了大量SORT导致的临时表空间不足,通过实例来说明一下,我们的数据库临时表空间是30G,而早上来的时候检查日志发现在20121018日下午4点出现了报错,如果是导致临时表空间耗尽的语句,那他的响应时间一定比较长,那么在DBA_HIST_ACTIVE_SESS_HISTORY一定会留下关于direct path write temp的等待时间,我通过语句

SQL> SELECT SAMPLE_TIME, SQL_ID, EVENT

  2    FROM DBA_HIST_ACTIVE_SESS_HISTORY

  3   WHERE SAMPLE_TIME > to_DATE(‘2012-10-18 16:00:00’ , ‘YYYY-MM-DD HH24:MI:SS’)

  4   AND SAMPLE_TIME

  5     AND EVENT  like ‘%direct%’

  6   ORDER BY SAMPLE_TIME;

找到了大量的这样的语句,同时由于本库日常压力并不大,在shared pool中还缓存了其SQL文本,顺利的找到了SQL,语句很长就不给出了。

在空闲时间我又执行了一次,同时跟踪了临时表空间的使用

SQL> select BLOCKS*8/1024/1024 from v$sort_usage;

 

BLOCKS*8/1024/1024

——————

     31.9951171875

这里我的临时表空间满了,语句也就报错了。当然这里要通过SESSION ADDR来确认对应的那个记录,我这里就一条也就无所谓了。为了找到根源我得出了SQL执行计划。

执行计划如下:

—————————————————————————————————-

| Id  | Operation                            | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

—————————————————————————————————-

|   1 |  SORT ORDER BY                       |                                |      1 |    366K|      0 |00:00:00.01 |       0 |

|*  2 |   HASH JOIN                          |                                |      1 |    366K|    112M|00:03:45.58 |   70565 |

|   3 |    TABLE ACCESS BY INDEX ROWID       | WEB_BAS_DIC_DT                 |      1 |     16 |     16 |00:0

|*  4 |     INDEX RANGE SCAN                 | IDX_WEB_BAS_DIC_DT_C_DICT_TYPE |      1 |     16 |     16 |00:0

|*  5 |    HASH JOIN                         |                                |      1 |    343K|    112M|00:01:53.36 |   70561 |

 

PLAN_TABLE_OUTPUT

—————————————————————————————————-

|   6 |     TABLE ACCESS FULL                | WEB_CLM_VCH_RPT                |      1 |    702K|    702K|00:00:00.01

|   7 |     MERGE JOIN CARTESIAN             |                                |      1 |    341K|    112M|00:00:00.63 |   44122 |

|   8 |      TABLE ACCESS BY INDEX ROWID     | FWK_BAS_DPT_CDE                |      1 |      1 |    160 |00

|   9 |       NESTED LOOPS                   |                                |      1 |      1 |    320 |00:00:03.74 |    6323 |

|  10 |        NESTED LOOPS                  |                                |      1 |      1 |    160 |00:00:00.04 |    6161 |

|  11 |         NESTED LOOPS                 |                                |      1 |      1 |    160 |00:00:00.04 |    5660 |

|  12 |          NESTED LOOPS                |                                |      1 |      1 |    160 |00:00:00.04 |    5338 |

|* 13 |           TABLE ACCESS FULL          | WEB_CLM_CNCL                   |      1 |      1 |    160 |00:00:00.04 |  

|  14 |           TABLE ACCESS BY INDEX ROWID| WEB_CLM_TEMP_DPT               |    160 |      1 |    160 |00:00:00

|* 15 |            INDEX UNIQUE SCAN         | PK_DPT_DPT                     |    160 |      1 |    160 |00:00:00.01 |   

|  16 |          TABLE ACCESS BY INDEX ROWID | FWK_BAS_EMP_CDE                |    160 |      1 |    160 |00:00:00.

|* 17 |           INDEX UNIQUE SCAN          | FWK_BAS_EMP_CDE_PK             |    160 |      1 |    160 |00:00:00.01

|  18 |         TABLE ACCESS BY INDEX ROWID  | WEB_CLM_MAIN                   |    160 |      1 |    160 |00:00:00.01

|* 19 |          INDEX UNIQUE SCAN           | PK_WEB_CLM_MAIN                |    160 |      1 |    160 |00:00:00.01 |

|* 20 |        INDEX RANGE SCAN              | IDX_FWK_BAS_DPT_CDE_ORG        |    160 |      1 |    160 |00

|  21 |      BUFFER SORT                     |                                |    160 |    693K|    112M|00:00:00.60 |   37660 |

|  22 |       TABLE ACCESS FULL              | WEB_CLM_RPT                    |      1 |    693K|    702K|00:00:00.01 |

简单的说一下这里最后需要排序的数据量为112M1亿多行,为什么这么多行明显这里的笛卡尔积是可能不合理的地方,问了开发后确实他们写掉了2个大表之间的连接条件,修改后就好了。这里顺便说一下buffer sort,这个不是真正的排序,只是把数据缓存在BUFFER中,避免笛卡尔积多次扫描带来的影响,这里笛卡尔积驱动了这张表扫描160次。如果缓存了效率就会提高,不缓存就必须进行多次全表扫描。

https://blogs.oracle.com/toddbao/entry/buffer_sort%E6%98%AFbuffer%E5%8D%B4%E4%B8%8D%E6%98%AFsort

这篇文章进行了说明,是我考OCM的时候的监考老师包光磊的博客。

3        、关于临时LOB

 可以参考文章

How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [ID 802897.1]

还有一篇文章

http://blog.oracle.com.cn/html/87/t-81587.html

 10.2.0.4之前必须通过断开连接进行释放,但是在10.2.0.4之后可以通过alter session set events ‘60025 trace name context forever’;

来释放本会话已经free但是没有释放的临时表空间,关于怎么看是否free

select * from v$temporary_lobs;

如果没有cache也没有nocache就是free了但是却没有释放的内存。Cache或者nocache  dbms_lob.createtemporary中参数cache是来指定的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-746791/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183296.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code