ORA-01652:temp表空间不足的相关问题及处理(二)
| | 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 ='WWW_BILL_DTL_010120121010';
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('ZC',WWW_BILL_DTL_010120121010,method_opt => 'FOR ALL COLUMNS SIZE 1',cascade =>TRUE,estimate_percent => 40,granularity=>'all',degree => 4,no_invalidate => false);
exec dbms_stats.gather_table_stats('ZC',WWW_BILL_010120121010,method_opt => 'FOR ALL COLUMNS SIZE 1',cascade =>TRUE,estimate_percent => 40,granularity=>'all',degree => 4,no_invalidate => false)
www.2cto.com
简单总结：
1，本次问题出在新建的临时表，没有同时新建相关索引，且在数据创建和大量数据插入后，没有立即收集统计信息，导致oracle无法选择合适的执行计划，进而占用大量临时表空间，sql无法正常执行完成。
2，merge join cartesian（笛卡儿算法）：是每个集合的任务一个成员都要与其他集合的每个成员进行匹配。因此原有执行计划需要执行对两个大表的N×N次查询和排序，占用大量temp表空间