玉面飞龙的BLOG

如果表的统计信息够准确，Oracle9i CBO下使用explain plan可以大概估计SQL（包括查询和DDL）使用临时表空间的数量。

Explain plan中会多出|TempSpc|显示估计的临时表空间需求量。下例显示HASH JOIN中使用不同的表作Driving Table，Temp Tablespace的需求。

SQL> explain plan for
select count(1) from JNL_DETAIL a,JNL_RUNS_STATUS b
where a.ID=b.ID and a.ID_NAME=b.ID_NAME and b.DATE_TAG > to_date(’20060701′,’YYYYMMDD’);

SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 22 | 5322K| | |
| 1 | SORT AGGREGATE | | 1 | 22 | | | |
|* 2 | HASH JOIN | | 5492M| 112G| 5322K| | |
|* 3 | TABLE ACCESS FULL | SMALLTABLE_SMMARY | 1115 | 16725 | 12 | | |
| 4 | PARTITION RANGE ALL | | | | | 1 | 3 |
| 5 | INDEX FAST FULL SCAN| BIGTABLE_DETAIL_PK | 16G| 107G| 5266K| 1 | 3 |
————————————————————————————————
Predicate Information (identified by operation id):
————————————————–
2 - access(”A”.”ID”=”B”.”ID” AND “A”.”ID_NAME”=”B”.”ID_NAME”)
3 - filter(”B”.”DATE_TAG”>TO_DATE(’2006-07-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’))

SQL> explain plan for
select /*+ leading(a) */ count(1) from BIGTABLE_DETAIL a,SMALLTABLE_SMMARY b
where a.ID=b.ID and a.ID_NAME=b.ID_NAME and b.DATE_TAG > to_date(’20060701′,’YYYYMMDD’);

SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 22 | | 7625K| | |
| 1 | SORT AGGREGATE | | 1 | 22 | | | | |
|* 2 | HASH JOIN | | 5492M| 112G| 293G| 7625K| | |
| 3 | PARTITION RANGE ALL | | | | | | 1 | 3 |
| 4 | INDEX FAST FULL SCAN| BIGTABLE_DETAIL_PK | 16G| 107G| | 5266K| 1 | 3 |
|* 5 | TABLE ACCESS FULL | SMALLTABLE_SMMARY | 1115 | 16725 | | 12 | | |
——————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 - access(”A”.”ID”=”B”.”ID” AND “A”.”ID_NAME”=”B”.”ID_NAME”)
5 - filter(”B”.”DATE_TAG”>TO_DATE(’2006-07-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’))
Note: cpu costing is off

更多参考在这里。