Find out Temp Space requirement for big operation

January 3, 2007

Purpose :
Shows how to find temp tablespace for big operations.

Time Needed : 5Min.
Preparation
There is no preparation required. If you have bigger table then just query and order by many columns.
OR you can artificially inflate the statistics and get the information like
exec dbms_stats.set_table_stats( user, ‘EMP’, numrows => 2000000, numblks => 1000000 );
exec dbms_stats.set_column_stats( user, ‘EMP’, ‘DEPTNO’, distcnt => 2000000 );

1. Test Case 1 (Already with existing big table – Siebel.s_dnb_org(few million)

explain plan for
select ROW_ID,CREATED,CREATED_BY,LAST_UPD,LAST_UPD_BY,MODIFICATION_NUM,CONFLICT_ID,DUNS_NUM,PRMTD_TO_ACCNT_FLG,PRMTD_TO_LIST_FLG,count(*)
from siebel.s_dnb_org
group by ROW_ID,CREATED,CREATED_BY,LAST_UPD,LAST_UPD_BY,MODIFICATION_NUM,CONFLICT_ID,DUNS_NUM,PRMTD_TO_ACCNT_FLG,PRMTD_TO_LIST_FLG
/

select * from table( dbms_xplan.display );
—————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
—————————————————————————-
| 0 | SELECT STATEMENT | | 18M| 771M| | 247K|
| 1 | SORT GROUP BY | | 18M| 771M| 2393M | 247K|
| 2 | TABLE ACCESS FULL | S_DNB_ORG | 18M| 771M| | 33856 |
—————————————————————————-

2. Test Case 2 (To estimate the space required to create index on big table)

Delete from plan_table;
explain plan for
select BUSINESS_NAME,row_id
from siebel.s_dnb_org
order by business_name

select * from table( dbms_xplan.display );
—————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
—————————————————————————-
| 0 | SELECT STATEMENT | | 21M| 724M| | 249K|
| 1 | SORT ORDER BY | | 21M| 724M| 1834M | 249K|
| 2 | TABLE ACCESS FULL | S_DNB_ORG | 21M| 724M| | 39794 |
—————————————————————————-

3. Test Case 3 (Small table inflate statistics)

Small table os space that does not go out of temp does not mention on this ??? Some time reducing the sort_area_size can show this(again disbale to pga_aggregate_target).

delete from plan_table;
explain plan for select deptno, count(*) from emp group by deptno;
select * from table( dbms_xplan.display );

Backout Procedure
None.

Advertisements