ORA-01652:temp表空间不足的相关问题及处理

ORA-01652:temp表空间不足的相关问题及处理

 

原因分析

 

1，登录数据库，查看主机日志，报错内容为ORA-01652，temp表空间不足

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01

 

2，让业务部门重新执行相关脚本，发现占用temp表空间的具体语句如下，目前temp表空间96GB，大约1个小时会被该sql使用满，sql异常退出

  www.2cto.com  

[sql] 

Sql具体如下  

INSERT INTO www.WWW_BILL_DTL_TEMP_0101(ACCT_ID,SERV_ID,FEE,BRAND,  

PHONE_ID,USER_TYPE)  SELECT ACCT_ID, SERV_ID, sum(FEE) FEE,BRAND  

,PHONE_ID,USER_TYPE  FROM (  SELECT ACCT_ID, SERV_ID, SUM(FEE) F  

EE,BRAND,PHONE_ID,USER_TYPE  FROM  (select a.acct_id,e.serv_id,s  

um(b.unpay_fee) FEE,a.brand,a.phone_id,a.user_type from www.ACC_B  

ILL_010120121010 A , www.WWW_BILL_DTL_010120121010 B ,  www.OWE_MO  

NITOR_QUEUE_ACTION E  where a.bill_id=b.bill_id and A.ACCT_ID=E.  

ACCT_ID and a.brand in (:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SY  

S_B_03",:"SYS_B_04",:"SYS_B_05")  and b.fee_item_id>:"SYS_B_06"  

group by a.acct_id,e.serv_id,a.brand,a.phone_id,a.user_type )  g  

roup by ACCT_ID, SERV_ID,BRAND,PHONE_ID,USER_TYPE  UNION ALL  SE  

LECT ACCT_ID, SERV_ID, -:"SYS_B_07"*SUM(FEE) FEE,BRAND,PHONE_ID,  

USER_TYPE  FROM  (select a.acct_id,e.serv_id,sum(b.unpay_fee) FE  

E,a.brand,a.phone_id,a.user_type from www.WWW_BILL_010120121005 A  

 , www.WWW_BILL_DTL_010120121005 B ,  www.WWW_MONITOR_QUEUE_ACTION  

 E  where a.bill_id=b.bill_id and A.ACCT_ID=E.ACCT_ID and a.bran  

d in (:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12  

",:"SYS_B_13")  and b.fee_item_id>:"SYS_B_14" group by a.acct_id  

,e.serv_id,a.brand,a.phone_id,a.user_type )  group by ACCT_ID, S  

ERV_ID,BRAND,PHONE_ID,USER_TYPE ) GROUP BY ACCT_ID, SERV_ID,BRAN  

D,PHONE_ID,USER_TYPE  

执行计划如下

  www.2cto.com  

[sql] 

Plan hash value: 3236377944  

  

--------------------------------------------------------------------------------------------------------------------  

| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |  

--------------------------------------------------------------------------------------------------------------------  

|   0 | INSERT STATEMENT                     |                             |       |       | 19281 (100)|          |  

|   1 |  HASH GROUP BY                       |                             |     2 |   184 | 19281   (2)| 00:03:52 |  

|   2 |   VIEW                               |                             |     2 |   184 | 19280   (2)| 00:03:52 |  

|   3 |    UNION-ALL                         |                             |       |       |            |          |  

|   4 |     SORT GROUP BY                    |                             |     1 |    92 | 19271   (2)| 00:03:52 |  

|   5 |      VIEW                            |                             |     1 |    92 | 19271   (2)| 00:03:52 |  

|   6 |       SORT GROUP BY                  |                             |     1 |   144 | 19271   (2)| 00:03:52 |  

|*  7 |        HASH JOIN                     |                             |     1 |   144 | 19270   (2)| 00:03:52 |  

|   8 |         MERGE JOIN CARTESIAN         |                             |     1 |    65 |  8717   (2)| 00:01:45 |  

|   9 |          TABLE ACCESS FULL           | WWW_MONITOR_QUEUE_ACTION    |     1 |    26 |     2   (0)| 00:00:01 |  

|  10 |          BUFFER SORT                 |                             |   257K|  9810K|  8715   (2)| 00:01:45 |  

|* 11 |           TABLE ACCESS FULL          | WWW_BILL_DTL_010120121010   |   257K|  9810K|  8715   (2)| 00:01:45 |  

|* 12 |         TABLE ACCESS FULL            | WWW_BILL_010120121010       | 16755 |  1292K| 10552   (1)| 00:02:07 |  

|  13 |     SORT GROUP BY                    |                             |     1 |    53 |     9  (12)| 00:00:01 |  

|  14 |      VIEW                            |                             |     1 |    53 |     9  (12)| 00:00:01 |  

|  15 |       SORT GROUP BY                  |                             |     1 |    79 |     9  (12)| 00:00:01 |  

|  16 |        TABLE ACCESS BY INDEX ROWID   | WWW_BILL_DTL_010120121005   |     1 |    18 |     3   (0)| 00:00:01 |  

|  17 |         NESTED LOOPS                 |                             |     1 |    79 |     8   (0)| 00:00:01 |  

|  18 |          NESTED LOOPS                |                             |     1 |    61 |     5   (0)| 00:00:01 |  

|  19 |           TABLE ACCESS FULL          | WWW_MONITOR_QUEUE_ACTION    |     1 |    26 |     2   (0)| 00:00:01 |  

|* 20 |           TABLE ACCESS BY INDEX ROWID| WWW_BILL_010120121005       |     1 |    35 |     3   (0)| 00:00:01 |  

|* 21 |            INDEX RANGE SCAN          | ITDX_ACCT_ID_10120121005    |     1 |       |     2   (0)| 00:00:01 |  

|* 22 |          INDEX RANGE SCAN            | TPK_BILL_DTL_ID_10120121005 |     1 |       |     2   (0)| 00:00:01 |  

--------------------------------------------------------------------------------------------------------------------  

  www.2cto.com  

3，继续分析原因。仔细分析该sql语句，实际上可以拆分为两个并列子sql。后一部分的sql执行计划是好的，不优的是上半部分，因此解决方案就很简单了，把上半部分的统计信息和索引做成和下半部分同样的结构。经查确认，上部分的sql缺少相关索引，且由于表今天创建，且插入了大量数据，且没有统计信息，选择列没有设置索引，导致执行计划不优。

[sql] 

SQL>  select * from dba_objects where object_name =&＃39;WWW_BILL_DTL_010120121010&＃39;;  

  

OWNER                          OBJECT_NAME                                                                                                                      SUBOBJECT_NAME  

------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------  

 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S  

---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - -  

ZC                             WWW_BILL_DTL_010120121010  

   1209877        1209877 TABLE               06-OCT-12    06-OCT-12    2012-10-06:11:19:25 VALID   N N N  

  www.2cto.com  

解决方法

1，表WWW_BILL_DTL选择列ACCT_ID，在表WWW_BILL上bill_id，FEE_ITEM_ID添加组合索引，

2，然后重新收集统计信息，sql即走合适的执行路径了

 

[sql] 

exec dbms_stats.gather_table_stats(&＃39;ZC&＃39;,WWW_BILL_DTL_010120121010,method_opt => &＃39;FOR ALL COLUMNS SIZE 1&＃39;,cascade =>TRUE,estimate_percent => 40,granularity=>&＃39;all&＃39;,degree => 4,no_invalidate => false);  

  

exec dbms_stats.gather_table_stats(&＃39;ZC&＃39;,WWW_BILL_010120121010,method_opt => &＃39;FOR ALL COLUMNS SIZE 1&＃39;,cascade =>TRUE,estimate_percent => 40,granularity=>&＃39;all&＃39;,degree => 4,no_invalidate => false)  

  www.2cto.com  

简单总结：

1，本次问题出在新建的临时表，没有同时新建相关索引，且在数据创建和大量数据插入后，没有立即收集统计信息，导致oracle无法选择合适的执行计划，进而占用大量临时表空间，sql无法正常执行完成。
 

2，merge join cartesian（笛卡儿算法）：是每个集合的任务一个成员都要与其他集合的每个成员进行匹配。因此原有执行计划需要执行对两个大表的N×N次查询和排序，占用大量temp表空间