Skip to Main Content
  • Questions
  • How to estimate temp tablespace space needed for a 380 Gigs table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Saurabh.

Asked: September 20, 2003 - 5:50 pm UTC

Last updated: November 28, 2009 - 12:35 pm UTC

Version: 9.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I have a 4.2 Billion row table with 8 columns
I need to group by 5 columns.
This table is 380 Gigs.

how much space in temp tablespace will I need.
Will it be close to 380 X 2 = 760 Gigs.
I need to use parallel query ( degree 8 or 16 probably )

Thanks for your advice.
Saurabh

and Tom said...

in 9iR2 as you have, this is pretty straightforward.

big_table@ORA920> delete from plan_table;

4 rows deleted.

big_table@ORA920>
big_table@ORA920> explain plan for
2 select object_name, owner, object_id, created, last_ddl_time, count(*)
3 from big_table
4 group by object_name, owner, object_id, created, last_ddl_time;

Explained.

big_table@ORA920>
big_table@ORA920> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
-------------------

------------------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------
|0 | SELECT STATEMENT | | 1833K| 85M| | 21993 (6)|
|1 | SORT GROUP BY | | 1833K| 85M| 225M| 21993 (6)|
|2 | TABLE ACCESS FULL | BIG_TABLE | 1833K| 85M| | 2417 (12)|
------------------------------------------------------------------------------

8 rows selected.


the plan as shown by dbms_xplan includes this.


Bear in mind, it is not a function so much of the number of rows in the source table -- more the number of distinct values of the group by columns!


Rating

  (33 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why no values ?

A reader, September 20, 2003 - 9:47 pm UTC

It gives me no values for tempspc for scott schema i just analyzed.I have listed 2 queries.
The first query is user objects table it does not even give me those rows ,bytes ,cost .tempspc columns is not there

The second query gives vlues for rows bytes cost but no tempspc
SQL> conn scott/tiger@orcl92
Connected.
SQL>  explain plan for
  2       select object_name,  object_id, created, last_ddl_time, count(*)
  3     from user_objects
  4   group by object_name,  object_id, created, last_ddl_time ;

Explained.

SQL> set line 2000
SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                       |  Name         | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |       |       |       |
|   1 |  SORT GROUP BY                  |               |       |       |       |
|   2 |   VIEW                          | USER_OBJECTS  |       |       |       |
|   3 |    UNION-ALL                    |               |       |       |       |
|*  4 |     FILTER                      |               |       |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| OBJ$          |       |       |       |
|*  6 |       INDEX RANGE SCAN          | I_OBJ2        |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
|*  7 |      TABLE ACCESS BY INDEX ROWID| IND$          |       |       |       |
|*  8 |       INDEX UNIQUE SCAN         | I_IND1        |       |       |       |
|   9 |     TABLE ACCESS BY INDEX ROWID | LINK$         |       |       |       |
|* 10 |      INDEX RANGE SCAN           | I_LINK1       |       |       |       |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR "SYS_A
              LIAS_1"."TYPE#"=1 AND  (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
              ."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
               OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE
              #"=9))=1)
   6 - access("SYS_ALIAS_1"."OWNER#"=:B1 AND "SYS_ALIAS_1"."LINKNAME" IS NULL)
       filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND "SYS_ALIAS_1"."NAME"<>'_def
              ault_auditing_options_' AND "SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT')
   7 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4
               OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
   8 - access("I"."OBJ#"=:B1)
  10 - access("L"."OWNER#"=:B1)


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Note: rule based optimization

34 rows selected.


SQL>   explain plan for
  2       select deptno, count(*)
  3     from emp
  4   group by deptno ;

Explained.

SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     3 |     9 |     4 |
|   1 |  SORT GROUP BY       |             |     3 |     9 |     4 |
|   2 |   TABLE ACCESS FULL  | EMP         |    14 |    42 |     2 |
--------------------------------------------------------------------

Note: cpu costing is off

10 rows selected.

Thanx Sir 

Tom Kyte
September 21, 2003 - 9:37 am UTC

well, with 14 rows -- how much temp space were you expecting to have to use???

dbms_xplan is very cool. it shows you only relevant information. that just means you won't NEED any temp. Consider --


scott@ORA920> analyze table emp compute statistics;
Table analyzed.


scott@ORA920> delete from plan_table;
3 rows deleted.

scott@ORA920> explain plan for
2 select deptno, count(*) from emp group by deptno;
Explained.

scott@ORA920> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 6 | 4 (50)|
| 1 | SORT GROUP BY | | 3 | 6 | 4 (50)|
| 2 | TABLE ACCESS FULL | EMP | 14 | 28 | 3 (34)|
-------------------------------------------------------------------------

8 rows selected.

for 14 rows with 3 distinct values of deptno -- we won't need any temp. lets move the goalposts however -- lots of rows, lots of values:

scott@ORA920> exec dbms_stats.set_table_stats( user, 'EMP', numrows => 2000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

scott@ORA920> exec dbms_stats.set_column_stats( user, 'EMP', 'DEPTNO', distcnt => 2000000 );

PL/SQL procedure successfully completed.

scott@ORA920> delete from plan_table;

3 rows deleted.

scott@ORA920>
scott@ORA920> explain plan for
2 select deptno, count(*) from emp group by deptno;

Explained.

scott@ORA920>
scott@ORA920> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
-------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000K| 3906K| | 104K (4)|
| 1 | SORT GROUP BY | | 2000K| 3906K| 30M| 104K (4)|
| 2 | TABLE ACCESS FULL | EMP | 2000K| 3906K| | 99312 (4)|
-------------------------------------------------------------------------------

8 rows selected.

when we need to count 2,000,000 deptno's, the optimizer sings a different song and says "you know, we'll be swapping some of this out"



it works.

Saurabh, September 21, 2003 - 10:50 am UTC

Tom, we were able to group by our 4.2 billion row
table. wew !! 

Your advice on how to calculate temp space needed was extremely helpfull.
It certainly works.. 

allthough a minor point to notice is
that the plan did said it needed 798 G, 
but we used about 705 Gigs only.
( I was monitoring our temp space usuage very 
  carefully while the process was running. )
also it did end up using about 2 times the table size ?

The final table created is 520K.

Thanks for the extreme fast response and
for teaching me something new.



==

after analyzing the 4.2 billion row table with 0.001 percent.

SQL> select count(1) from plan_table;

  COUNT(1)
----------
         0

SQL>  explain plan for
  2    select level1, level2,
  3       level3,
  4         level4,
  5         level5,
  6             domain,
  7         count(distinct machineid) as all_dom, count(distinct us_mid) as us_dom,
  8             count(distinct intl_mid) as intl_dom
  9             from q_temp
 10  group by level1, level2, level3, level4, level5, domain
 11  /

Explained.

SQL> set linesize 1000
SQL>
SQL> /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes |TempSpc| Cost  |  TQ    |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  4264M|   297G|       |  2537K|        |      |            |
|   1 |  SORT GROUP BY       |             |  4264M|   297G|   798G|  2537K| 45,01  | P->S | QC (RAND)  |
|   2 |   TABLE ACCESS FULL  | Q_TEMP      |  4264M|   297G|       | 47144 | 45,00  | P->P | HASH       |
---------------------------------------------------------------------------------------------------------

Note: cpu costing is off

10 rows selected.

SQL>
 

Tom Kyte
September 21, 2003 - 11:17 am UTC

it is an estimate remember! it is as close as it can estimate.... you needed about 90% of what it estimated, thats pretty good.

just like the row count estimates -- I'll bet there was a little less then 4,264,000 rows....

typo fixed.

A reader, September 21, 2003 - 10:52 am UTC

When I said
"the final table created was 57K"
I meant it had 57K rows.

Thanx for the no values ans

A reader, September 21, 2003 - 11:05 am UTC

You are Great

How to get the current Temp Tblspace Size ?

A reader, September 21, 2003 - 11:09 am UTC


Tom Kyte
September 21, 2003 - 11:24 am UTC

query the data dictionary?

dba_temp_files is the most likely view, if you've set things up correctly.


Temp Space Requirement not comming up

Devanshi Parikh, October 27, 2003 - 12:12 pm UTC

I have a query which involves 3 tables with the first table having 214 million rows, the second and the third are extreamly small with 1000 rows. When the query is executed, temp space is being used, as seen from v$sort_usage. I tried using the dbms_xplan.display function, which does give me the explain plan, but not the TEMP space column. My sort_area_size is 120M. Also, the sort_area_retained_size is 16M. Are there any special parameters that need to be set in order to use this correctly ? 

Below is the query

explain plan for
SELECT /*+ FULL(pr)  PARALLEL(pr, 4)  */ 
       pr.Business_Unit_GL, xaxn.EY_Xaxn_Group,
       to_date('31-OCT-2003', 'DD-MON-YYYY HH24:MI:SS'), 
       cal.End_Dt, 
    to_date('01-JAN-1998', 'DD-MON-YYYY HH24:MI:SS'),
       'PRJ', 'D', xaxn.Descr,0,
       sum(decode(xaxn.EY_Amt_Qty_flg,
                  'A', pr.Resource_Amount,
                  'Q', pr.Resource_Quantity, 0)) Sum,
       count(pr.rowid) Count,
       'N', ''
FROM   ps_proj_resource pr, ps_ey_xaxn_grps xaxn,    ps_cal_detp_tbl cal
WHERE  pr.Analysis_Type = xaxn.Analysis_Type
and    pr.Resource_Type = xaxn.Resource_Type
and    pr.Resource_Category LIKE xaxn.Resource_Category
and    pr.Resource_Sub_Cat  LIKE xaxn.Resource_Sub_Cat
and    pr.Resource_Sub_Cat <> 'RES'
and    pr.System_Source LIKE xaxn.System_Source
and    pr.Accounting_Dt BETWEEN
    cal.Begin_Dt and cal.End_Dt
and    pr.System_Source NOT IN ('IW1','ADJ','BAL','SUM')
and    cal.SetID = 'GLOBE'
and    cal.Calendar_ID = 'PE'
group by pr.Business_Unit_GL, xaxn.EY_Xaxn_Group, cal.End_Dt,
         xaxn.Descr;

ps_proj_resource pr - Table with 214 million rows
ps_ey_xaxn_grps - Table with 60 rows
ps_cal_detp_tbl - Table with 1000 rows

select * from table(dbms_xplan.display);

SQL> /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
| Id  | Operation             |  Name             | Rows  | Bytes | Cost  |  TQ 
   |IN-OUT| PQ Distrib |                                                        
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
|   0 | SELECT STATEMENT      |                   |  3021 |   286K|   521K|     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
   |      |            |                                                        
                                                                                
|   1 |  SORT GROUP BY        |                   |  3021 |   286K|   521K| 14,0
3  | P->S | QC (RAND)  |                                                        
                                                                                
|   2 |   MERGE JOIN          |                   |  3021 |   286K|   521K| 14,0
2  | P->P | HASH       |                                                        
                                                                                
|   3 |    SORT JOIN          |                   |  4679 |   347K|   521K| 14,0
2  | PCWP |            |                                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  4 |     HASH JOIN         |                   |  4679 |   347K|   521K| 14,0
2  | PCWP |            |                                                        
                                                                                
|   5 |      TABLE ACCESS FULL| PS_EY_XAXN_GRPS   |    65 |  2340 |     2 | 14,0
0  | S->P | BROADCAST  |                                                        
                                                                                
|*  6 |      TABLE ACCESS FULL| PS_PROJ_RESOURCE  |   170M|  6502M|   521K| 14,0
2  | PCWP |            |                                                        
                                                                                
|*  7 |    FILTER             |                   |       |       |       | 14,0
2  | PCWP |            |                                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
|*  8 |     SORT JOIN         |                   |       |       |       | 14,0
2  | PCWP |            |                                                        
                                                                                
|*  9 |      INDEX RANGE SCAN | PSCCAL_DETP_TBL   |   258 |  5418 |     3 | 14,0
1  | S->P | BROADCAST  |                                                        
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND                   
              "PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")                      
       filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND "PR".
"RESOURCE_SUB_CAT" LIKE                                                         
                                                                                
              "XAXN"."RESOURCE_SUB_CAT" AND "PR"."SYSTEM_SOURCE" LIKE "XAXN"."SY
STEM_SOURCE")                                                                   
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
   6 - filter("PR"."RESOURCE_SUB_CAT"<>'RES' AND "PR"."SYSTEM_SOURCE"<>'IW1' AND
                                                                                
                                                                                
              "PR"."SYSTEM_SOURCE"<>'ADJ' AND "PR"."SYSTEM_SOURCE"<>'BAL' AND "P
R"."SYSTEM_SOURCE"<>'SUM')                                                      
                                                                                
   7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")                             
   8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")                           
       filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")                           
   9 - access("CAL"."SETID"='GLOBE' AND "CAL"."CALENDAR_ID"='PE')               
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Note: cpu costing is off                                                        

31 rows selected.


Thanks, 

Tom Kyte
October 27, 2003 - 1:32 pm UTC

see what it says without the parallel operations.

Tried using SERIAL

Devanshi Parikh, October 27, 2003 - 3:12 pm UTC

I think that is what you meant. The plan for the same statement was displayed using the select below. Also, I tried taking out the PARALLEL hint and going thro' the same steps again, but again, the same output. No mention of TEMP space.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'SERIAL'));

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation             |  Name             | Rows  | Bytes | Cost  |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT      |                   |  3021 |   286K|   521K|     
|   1 |  SORT GROUP BY        |                   |  3021 |   286K|   521K|     
|   2 |   MERGE JOIN          |                   |  3021 |   286K|   521K|     
|   3 |    SORT JOIN          |                   |  4679 |   347K|   521K|     
|*  4 |     HASH JOIN         |                   |  4679 |   347K|   521K|     
|   5 |      TABLE ACCESS FULL| PS_EY_XAXN_GRPS   |    65 |  2340 |     2 |     
|*  6 |      TABLE ACCESS FULL| PS_PROJ_RESOURCE  |   170M|  6502M|   521K|     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  7 |    FILTER             |                   |       |       |       |     
|*  8 |     SORT JOIN         |                   |       |       |       |     
|*  9 |      INDEX RANGE SCAN | PSCCAL_DETP_TBL   |   258 |  5418 |     3 |     
---------------------------------------------------------------------------     
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND                   
              "PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")                      
       filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND      

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
              "PR"."RESOURCE_SUB_CAT" LIKE "XAXN"."RESOURCE_SUB_CAT" AND        
              "PR"."SYSTEM_SOURCE" LIKE "XAXN"."SYSTEM_SOURCE")                 
   6 - filter("PR"."RESOURCE_SUB_CAT"<>'RES' AND                                
              "PR"."SYSTEM_SOURCE"<>'IW1' AND "PR"."SYSTEM_SOURCE"<>'ADJ' AND   
              "PR"."SYSTEM_SOURCE"<>'BAL' AND "PR"."SYSTEM_SOURCE"<>'SUM')      
   7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")                             
   8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")                           
       filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")                           
   9 - access("CAL"."SETID"='GLOBE' AND "CAL"."CALENDAR_ID"='PE')               
                                                                                
Note: cpu costing is off                                                        

33 rows selected.

Thanks so much 

Tom Kyte
October 27, 2003 - 6:43 pm UTC

no, i meant -- get a serial plan generated. not a parallel one.

Output without PARALLEL Hint :

Devanshi Parikh, October 28, 2003 - 11:22 am UTC

Here is my SELECT :

explain plan for
SELECT /*+ FULL(pr) */
pr.Business_Unit_GL, xaxn.EY_Xaxn_Group,
to_date('31-OCT-2003', 'DD-MON-YYYY HH24:MI:SS'),
cal.End_Dt,
to_date('01-JAN-1998', 'DD-MON-YYYY HH24:MI:SS'),
'PRJ', 'D', xaxn.Descr,0,
sum(decode(xaxn.EY_Amt_Qty_flg,
'A', pr.Resource_Amount,
'Q', pr.Resource_Quantity, 0)) Sum,
count(pr.rowid) Count,
'N', ''
FROM ps_proj_resource pr, ps_ey_xaxn_grps xaxn, ps_cal_detp_tbl cal
WHERE pr.Analysis_Type = xaxn.Analysis_Type
and pr.Resource_Type = xaxn.Resource_Type
and pr.Resource_Category LIKE xaxn.Resource_Category
and pr.Resource_Sub_Cat LIKE xaxn.Resource_Sub_Cat
and pr.Resource_Sub_Cat <> 'RES'
and pr.System_Source LIKE xaxn.System_Source
and pr.Accounting_Dt BETWEEN
cal.Begin_Dt and cal.End_Dt
and pr.System_Source NOT IN ('IW1','ADJ','BAL','SUM')
and cal.SetID = 'GLOBE'
and cal.Calendar_ID = 'PE'
group by pr.Business_Unit_GL, xaxn.EY_Xaxn_Group, cal.End_Dt,
xaxn.Descr;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3021 | 286K| 2086K|
| 1 | SORT GROUP BY | | 3021 | 286K| 2086K|
| 2 | MERGE JOIN | | 3021 | 286K| 2086K|
| 3 | SORT JOIN | | 4679 | 347K| 2085K|
|* 4 | HASH JOIN | | 4679 | 347K| 2085K|
| 5 | TABLE ACCESS FULL| PS_EY_XAXN_GRPS | 65 | 2340 | 2 |
|* 6 | TABLE ACCESS FULL| PS_PROJ_RESOURCE | 170M| 6502M| 2085K|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | FILTER | | | | |
|* 8 | SORT JOIN | | | | |
|* 9 | INDEX RANGE SCAN | PSCCAL_DETP_TBL | 258 | 5418 | 3 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND
"PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")
filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"PR"."RESOURCE_SUB_CAT" LIKE "XAXN"."RESOURCE_SUB_CAT" AND
"PR"."SYSTEM_SOURCE" LIKE "XAXN"."SYSTEM_SOURCE")
6 - filter("PR"."RESOURCE_SUB_CAT"<>'RES' AND
"PR"."SYSTEM_SOURCE"<>'IW1' AND "PR"."SYSTEM_SOURCE"<>'ADJ' AND
"PR"."SYSTEM_SOURCE"<>'BAL' AND "PR"."SYSTEM_SOURCE"<>'SUM')
7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")
8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
9 - access("CAL"."SETID"='GLOBE' AND "CAL"."CALENDAR_ID"='PE')

Note: cpu costing is off

33 rows selected.

Thanks,

Tom Kyte
October 28, 2003 - 2:14 pm UTC

whats your exact version?

The version is 9.2.0.3

Devanshi Parikh, October 29, 2003 - 10:40 am UTC

select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

Thanks,


Tom Kyte
October 29, 2003 - 11:44 am UTC

well, looking at the plan:

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3021 | 286K| 2086K|
| 1 | SORT GROUP BY | | 3021 | 286K| 2086K|
| 2 | MERGE JOIN | | 3021 | 286K| 2086K|
| 3 | SORT JOIN | | 4679 | 347K| 2085K|
|* 4 | HASH JOIN | | 4679 | 347K| 2085K|
| 5 | TABLE ACCESS FULL| PS_EY_XAXN_GRPS | 65 | 2340 | 2 |
|* 6 | TABLE ACCESS FULL| PS_PROJ_RESOURCE | 170M| 6502M| 2085K|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | FILTER | | | | |
|* 8 | SORT JOIN | | | | |
|* 9 | INDEX RANGE SCAN | PSCCAL_DETP_TBL | 258 | 5418 | 3 |
---------------------------------------------------------------------------

it won't NEED any sort. the hash join will hash ps_ey_xaxn_grps into memory.. It'll full scan ps_proj_resource and hash lookup into ps_ey_xaxn_grps -- and output only 4,679 rows to be sorted. This is thought to be 347k of data. that first into your 120m nicely.

then, you have a sort of 258 rows

then merged.


According to the stats, there isn't any temp needed.

So, now, you need to validate for us the estimate cardinalities. The information in the explain plan is valid if and only if the estimated cards there are accurrate.


Devanshi Parikh, October 29, 2003 - 12:45 pm UTC

Please let me know what additional information should I provide to better understand the dbms_xplan functionality. If I understood correctly, did you mean to say that the actual execution of the query could be "different" than what it shows ?

Thanks,

Tom Kyte
October 29, 2003 - 2:24 pm UTC

no, what i meant was:

o do you expect 65 rows from PS_EY_XAXN_GRPS
o do you expect 170m rows from PS_PROJ_RESOURCE after applying the filter in step #6
o do you expect the resulting HASH join between the two to result in 4,769 rows after applying filter #4


are those numbers "reasonable" or "way off"


A reader, October 29, 2003 - 3:29 pm UTC

I should be able to calculate those numbers for the filters tomorrow, since we are going to run a backup soon on the database.
Just a curious question, (I have not tried this yet, but I will when I get a window )
Say, if I were to change the sort_area_size for just my session to something like 64K, and then run the explain plan, can I say for sure that I "will" be sorting now ? In that case , the TEMP SPACE column should show up, right ?

Thanks,

Tom Kyte
October 29, 2003 - 5:18 pm UTC

i do, 

ops$tkyte@ORA920LAP> alter session set workarea_size_policy = manual;

Session altered.

ops$tkyte@ORA920LAP> alter session set sort_area_size = 100000000;

Session altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> delete from plan_table;

10 rows deleted.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> explain plan for
  2  SELECT /*+ ORDERED USE_HASH( pr xaxn ) FULL(pr) */
  3         pr.Business_Unit_GL, xaxn.EY_Xaxn_Group,
  4         to_date('31-OCT-2003', 'DD-MON-YYYY HH24:MI:SS'),
  5         cal.End_Dt,
  6      to_date('01-JAN-1998', 'DD-MON-YYYY HH24:MI:SS'),
  7         'PRJ', 'D', xaxn.Descr,0,
  8         sum(decode(xaxn.EY_Amt_Qty_flg,
  9                    'A', pr.Resource_Amount,
 10                    'Q', pr.Resource_Quantity, 0)) Sum,
 11         count(pr.rowid) Count,
 12         'N', ''
 13  FROM
 14  ps_ey_xaxn_grps xaxn,
 15  ps_proj_resource pr ,
 16  ps_cal_detp_tbl cal
 17  WHERE  pr.Analysis_Type = xaxn.Analysis_Type
 18  and    pr.Resource_Type = xaxn.Resource_Type
 19  and    pr.Resource_Category LIKE xaxn.Resource_Category
 20  and    pr.Resource_Sub_Cat  LIKE xaxn.Resource_Sub_Cat
 21  -- and       pr.Resource_Sub_Cat <> 'RES'
 22  and    pr.System_Source LIKE xaxn.System_Source
 23  and    pr.Accounting_Dt BETWEEN
 24      cal.Begin_Dt and cal.End_Dt
 25  -- and       pr.System_Source NOT IN ('IW1','ADJ','BAL','SUM')
 26  --and       cal.SetID = 'GLOBE'
 27  --and       cal.Calendar_ID = 'PE'
 28  group by pr.Business_Unit_GL, xaxn.EY_Xaxn_Group, cal.End_Dt,
 29           xaxn.Descr;

Explained.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation             |  Name             | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   211 | 33338 |   158 |
|   1 |  SORT GROUP BY        |                   |   211 | 33338 |   158 |
|   2 |   MERGE JOIN          |                   |   211 | 33338 |   122 |
|   3 |    SORT JOIN          |                   |   327 | 45780 |    77 |
|*  4 |     HASH JOIN         |                   |   327 | 45780 |    37 |
|   5 |      TABLE ACCESS FULL| PS_EY_XAXN_GRPS   |    65 |  3640 |    11 |
|   6 |      TABLE ACCESS FULL| PS_PROJ_RESOURCE  |   170M|    13G|    11 |
|*  7 |    FILTER             |                   |       |       |       |
|*  8 |     SORT JOIN         |                   |       |       |       |
|   9 |      TABLE ACCESS FULL| PS_CAL_DETP_TBL   |   258 |  4644 |    11 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND
              "PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")
       filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND
              "PR"."RESOURCE_SUB_CAT" LIKE "XAXN"."RESOURCE_SUB_CAT" AND
              "PR"."SYSTEM_SOURCE" LIKE "XAXN"."SYSTEM_SOURCE")
   7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")
   8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
       filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")

Note: cpu costing is off

29 rows selected.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter session set sort_area_size = 32767;

Session altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> delete from plan_table;

10 rows deleted.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> explain plan for
  2  SELECT /*+ ORDERED USE_HASH( pr xaxn ) FULL(pr) */
  3         pr.Business_Unit_GL, xaxn.EY_Xaxn_Group,
  4         to_date('31-OCT-2003', 'DD-MON-YYYY HH24:MI:SS'),
  5         cal.End_Dt,
  6      to_date('01-JAN-1998', 'DD-MON-YYYY HH24:MI:SS'),
  7         'PRJ', 'D', xaxn.Descr,0,
  8         sum(decode(xaxn.EY_Amt_Qty_flg,
  9                    'A', pr.Resource_Amount,
 10                    'Q', pr.Resource_Quantity, 0)) Sum,
 11         count(pr.rowid) Count,
 12         'N', ''
 13  FROM
 14  ps_ey_xaxn_grps xaxn,
 15  ps_proj_resource pr ,
 16  ps_cal_detp_tbl cal
 17  WHERE  pr.Analysis_Type = xaxn.Analysis_Type
 18  and    pr.Resource_Type = xaxn.Resource_Type
 19  and    pr.Resource_Category LIKE xaxn.Resource_Category
 20  and    pr.Resource_Sub_Cat  LIKE xaxn.Resource_Sub_Cat
 21  -- and       pr.Resource_Sub_Cat <> 'RES'
 22  and    pr.System_Source LIKE xaxn.System_Source
 23  and    pr.Accounting_Dt BETWEEN
 24      cal.Begin_Dt and cal.End_Dt
 25  -- and       pr.System_Source NOT IN ('IW1','ADJ','BAL','SUM')
 26  --and       cal.SetID = 'GLOBE'
 27  --and       cal.Calendar_ID = 'PE'
 28  group by pr.Business_Unit_GL, xaxn.EY_Xaxn_Group, cal.End_Dt,
 29           xaxn.Descr;

Explained.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id  | Operation             |  Name             | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   211 | 33338 |       |   445K|
|   1 |  SORT GROUP BY        |                   |   211 | 33338 |       |   445K|
|   2 |   MERGE JOIN          |                   |   211 | 33338 |       |   445K|
|   3 |    SORT JOIN          |                   |   327 | 45780 |   120K|   445K|
|*  4 |     HASH JOIN         |                   |   327 | 45780 |       |   445K|
|   5 |      TABLE ACCESS FULL| PS_EY_XAXN_GRPS   |    65 |  3640 |       |    11 |
|   6 |      TABLE ACCESS FULL| PS_PROJ_RESOURCE  |   170M|    13G|       |    11 |
|*  7 |    FILTER             |                   |       |       |       |       |
|*  8 |     SORT JOIN         |                   |       |       |       |       |
|   9 |      TABLE ACCESS FULL| PS_CAL_DETP_TBL   |   258 |  4644 |       |    11 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND
              "PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")
       filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND
              "PR"."RESOURCE_SUB_CAT" LIKE "XAXN"."RESOURCE_SUB_CAT" AND "PR"."SYSTEM_SOURCE"
              LIKE "XAXN"."SYSTEM_SOURCE")
   7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")
   8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
       filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")

Note: cpu costing is off

29 rows selected.

ops$tkyte@ORA920LAP>


<b>but note, you probably want to be looking at pga_aggregate_target and not sort_area_size in 9iR2</b>
 

A related question

Devanshi Parikh, October 29, 2003 - 3:30 pm UTC

I should be able to calculate those numbers for the filters tomorrow, since we are going to run a backup soon on the database.
Just a curious question, (I have not tried this yet, but I will when I get a window )
Say, if I were to change the sort_area_size for just my session to something like 64K, and then run the explain plan, can I say for sure that I "will" be sorting now ? In that case , the TEMP SPACE column should show up, right ?

Thanks,

Excellent !!!!

Devanshi Parikh, October 30, 2003 - 11:07 am UTC

Tom,
You were absolutely right ! The cardinalities were quite close to the actual numbers that I got by executing those filters separately. The query did not use any (physical) temp space. Also, to prove that it will use temp, if the sort_area_size is decreased. Here is what I did.

alter session set sort_area_size=32768;
REM Ran the explain plan of the query above.
REM Here is what I got.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
|

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

| 0 | SELECT STATEMENT | | 3115 | 346K| | 235

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7K|

| 1 | SORT GROUP BY | | 3115 | 346K| 808K| 235
7K|

| 2 | MERGE JOIN | | 3115 | 346K| | 235
6K|

| 3 | SORT JOIN | | 4825 | 419K| 1112K| 235
6K|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | HASH JOIN | | 4825 | 419K| | 235
6K|

| 5 | TABLE ACCESS FULL| PS_EY_XAXN_GRPS | 67 | 2881 | |
2 |

|* 6 | TABLE ACCESS FULL| PS_PROJ_RESOURCE | 170M| 7480M| | 208
6K|

|* 7 | FILTER | | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 8 | SORT JOIN | | | | |
|

|* 9 | INDEX RANGE SCAN | PSCCAL_DETP_TBL | 258 | 6450 | |
3 |

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



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("PR"."ANALYSIS_TYPE"="XAXN"."ANALYSIS_TYPE" AND
"PR"."RESOURCE_TYPE"="XAXN"."RESOURCE_TYPE")
filter("PR"."RESOURCE_CATEGORY" LIKE "XAXN"."RESOURCE_CATEGORY" AND
"PR"."RESOURCE_SUB_CAT" LIKE "XAXN"."RESOURCE_SUB_CAT" AND "PR"."S
YSTEM_SOURCE"

LIKE "XAXN"."SYSTEM_SOURCE")
6 - filter("PR"."RESOURCE_SUB_CAT"<>'RES' AND "PR"."SYSTEM_SOURCE"<>'IW1' AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


"PR"."SYSTEM_SOURCE"<>'ADJ' AND "PR"."SYSTEM_SOURCE"<>'BAL' AND
"PR"."SYSTEM_SOURCE"<>'SUM')
7 - filter("PR"."ACCOUNTING_DT"<="CAL"."END_DT")
8 - access("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
filter("PR"."ACCOUNTING_DT">="CAL"."BEGIN_DT")
9 - access("CAL"."SETID"='GLOBE' AND "CAL"."CALENDAR_ID"='PE')

Note: cpu costing is off

33 rows selected.

Now, I see the TEMP SPACE column show up. This is an extreamly useful feature, since it allows us DBA's to (almost) accurately predict the TEMP space usage for huge queries like this.

Thanks,

Hwo to get the CPU cost in the output

Ganesh, June 09, 2004 - 10:57 am UTC

Your comments/suggestions are helpful.

When I do select * from table(dbms_xplan.display) I do not get the CPU costing? Am I missing some setting (init parameters?)

Thanks
Ganesh

Tom Kyte
June 09, 2004 - 11:29 am UTC

cpu costing is off in 9i, on in 10g

there are some cases in 9i where it comes on but they are actually "product issues"

Why is the TEMP estimate larger than the byte count?

David Scott, December 07, 2004 - 11:23 am UTC

In the example above, the Bytes column shows 85M, the TempSpc column shows 225M. This is more than 2.5 times the size of the original data. I'm curious as to why it would be this much larger; I would normally expect a summary query to use less space than the original data. Please clarify.

Tom Kyte
December 07, 2004 - 8:22 pm UTC

a temporary swap space to sort data will pretty much necessarily be larger than the original input set -- you have multiple passes on the data, overhead on each temp block and extent.

we read 'sort area size' (variable sizes with workarea policy auto) and sort it. when it fills up -- we swap it out and do it over (and over and over) then you need to merge them. so beween multiple passes, overhead to manage temp and so on..

Why it is not showing temp size in explain plan

Parag Jayant Patankar, March 14, 2005 - 7:28 am UTC

Hi Tom,

In our production we are facing a problem of temporary tablespace can not extend for complex query in Oracle 8i.

For this reason I have decided to adopt approch you have suggested in this thread.

1. I have created required table structures in Oracle 9i environment
2. set tables statistics by setting up number of rows with actucal environment
3. executed explain plan for required query
4. select * from table(dbms_xplan.display);

17:39:51 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |   653 |    99 |
|*  1 |  HASH JOIN                      |             |     1 |   653 |    99 |
|*  2 |   HASH JOIN                     |             |     1 |   553 |    87 |
|*  3 |    HASH JOIN                    |             |     1 |   453 |    75 |
|   4 |     NESTED LOOPS OUTER          |             |     1 |   353 |    63 |
|*  5 |      HASH JOIN OUTER            |             |     1 |   292 |    62 |
|*  6 |       HASH JOIN OUTER           |             |     1 |   213 |    50 |
|*  7 |        HASH JOIN                |             |     1 |   193 |    38 |
|   8 |         MERGE JOIN CARTESIAN    |             |  1600 |   171K|    22 |
|   9 |          TABLE ACCESS FULL      | TDF31       |     1 |    10 |    11 |
|  10 |          BUFFER SORT            |             |    16 |  1600 |    11 |
|* 11 |           TABLE ACCESS FULL     | TDN32       |    16 |  1600 |    11 |
|* 12 |         TABLE ACCESS FULL       | TDC31       |  2000 |   162K|    11 |
|  13 |        VIEW                     | VA5131      |  1250 | 25000 |    11 |
|* 14 |         TABLE ACCESS FULL       | TDZ01       |  1250 |   122K|    11 |
|* 15 |          TABLE ACCESS FULL      | TDZ01       |    63 |  6300 |    11 |
|  16 |       TABLE ACCESS FULL         | TDC33       |  5000 |   385K|    11 |
|  17 |      TABLE ACCESS BY INDEX ROWID| TDT31       |     1 |    61 |     1 |
|* 18 |       INDEX UNIQUE SCAN         | PKDT31      |     1 |       |       |
|* 19 |     TABLE ACCESS FULL           | TDZ01       |   238 | 23800 |    11 |
|* 20 |      TABLE ACCESS FULL          | TDZ01       |    63 |  6300 |    11 |
|* 21 |    TABLE ACCESS FULL            | TDZ01       |  1250 |   122K|    11 |
|* 22 |     TABLE ACCESS FULL           | TDZ01       |    63 |  6300 |    11 |
|* 23 |   TABLE ACCESS FULL             | TDZ01       |  1250 |   122K|    11 |
|* 24 |    TABLE ACCESS FULL            | TDZ01       |    63 |  6300 |    11 |
-------------------------------------------------------------------------------

17:44:30 SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_mode                       string      CHOOSE


  1  select table_name, NUM_ROWS
  2  from user_tables
  3* where trunc(last_analyzed) = to_date('14-MAR-05', 'DD-MON-RR')
17:56:50 SQL> /

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TDC31                               40000
TDC33                                5000
TDF31                                   1
TDN32                              160000
TDT31                               49000
TDZ01                              125000

In this explain plan why it is not showing any temporary space estimation ?

In my 9iR2 test environment my temporary file size is 50MB while in Oracle 8i ( actual production environment ) it is 350 MB and in Oracle 8i this query is getting failed beacause of temporary tablespace can not extend.

thanks for guiding me.

regards
pjp
 

Tom Kyte
March 14, 2005 - 8:12 am UTC

are the plans even remotely the same here between 8i and 9i (perhaps 9i needs no temp)

given the small size of all of those tables - I'm surprised it needs much temp at all.

How can I find out temp size estimation now ?

Parag Jayant Patankar, March 14, 2005 - 8:44 am UTC

Hi Tom,

Regarding my question in this thread how can I find out

1. What is an estimation of temp size for this SQL ?

2. my understanding is that if my sort_area_size is same ( in both cases it is 1 MB ), number of rows are same and temp file size is less then actual temp size where we faced temp tablespace extend error explain_plan output must show me temp size estimation. Can you explain me in the detail in such a case why it has not shown temp size ( sorry I am not very clear from your answer ) esitmation ?

According to you what method I should adopt to tackle this problem ?

regards & thanks
pjp

Tom Kyte
March 14, 2005 - 9:46 am UTC

apparently -- it is ZERO with that plan. see the bytes in that column? this is tiny.



Temp Size

Parag Jyanat Patankar, March 17, 2005 - 8:27 am UTC

Hi Tom,

Reqarding my question in thread I was running this query in Oracle 8.1.7 it was running out of space of temp tablespace ( around 1.9 GB ). As mentioned I was trying to estimate temp size on Oracle 9i as suggested. But it was showing me nothing. ( thanks for your help )

After lot of analysis I found that statistics of the schema is deleted and not created again. After gathering statistics there was no issue.

Can you explain me if table statistics were not present why it was creating a problem of temp tablespace and upto such a large extent ( my temp file size reached almost 2 gb for such a small tables ) ?

regards & thanks
pjp

Tom Kyte
March 17, 2005 - 9:29 am UTC

because of a plan that required lots of temp space?

no stats = rbo
stats = cbo

rbo plans generally are very different from cbo plans.

Why an insert requires an extra sort using more temp for something that appears to be sorted.

RichA, June 29, 2005 - 4:05 pm UTC

explain plan for
insert into foobar
select distinct foo
from
(select foo
from foo1
union
select foo
from foo2
);

Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------
0 | INSERT STATEMENT | | 2296K| 552M| | 1229K (2)|
1 | LOAD AS SELECT | | | | | |
2 | SORT UNIQUE | | 2296K| 552M| 1157M| 1229K (2)| - why?
3 | VIEW | | 2296K| 552M| | |
4 | SORT UNIQUE | | 2296K| 38M| 105M| 78452 (30)|
5 | UNION-ALL | | | | | |
6 | INDEX FULL SCAN| PK_FOO1 | 1698K| 29M| | 8900 (9)|
7 | INDEX FULL SCAN| PK_FOO2 | 598K| 9347K| | 2696 (11)|

[tkprof]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 29.70 40.28 20864 10581 2093 2367585
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 29.70 40.29 20864 10581 2093 2367585

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 161

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=11445 r=20864 w=28108 time=40500627 us)
2367585 SORT UNIQUE (cr=10378 r=20864 w=20855 time=29722095 us)
2367585 VIEW (cr=10378 r=13980 w=13971 time=22556301 us)
2367585 SORT UNIQUE (cr=10378 r=13980 w=13971 time=19408152 us)
2439404 UNION-ALL (cr=10378 r=9 w=0 time=9104863 us)
1752186 INDEX FULL SCAN PK_FOO1 (cr=7928 r=6 w=0 time=1731449 us)
687218 INDEX FULL SCAN PK_FOO2 (cr=2450 r=3 w=0 time=636803 us)

** Actual is 112M inner sort (Id 4), 56M outer sort (Id 2)

The plan of the select portion (without the insert) is:
Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------
0 | SELECT STATEMENT | | 2296K| 552M| | 78452 (7)|
1 | VIEW | | 2296K| 552M| | |
2 | SORT UNIQUE | | 2296K| 38M| 105M| 78452 (30)|
3 | UNION-ALL | | | | | |
4 | INDEX FULL SCAN | PK_FOO1 | 1698K| 29M| | 8900 (9)|
5 | INDEX FULL SCAN | PK_FOO2 | 598K| 9347K| | 2696 (11)|

with the expected single sort.
Why the extra sort for the insert?


Tom Kyte
June 29, 2005 - 8:17 pm UTC

looks wrong, please contact support (but you realize, you don't need the distinct or you don't need the union, rather a union all right?)



RichA, June 29, 2005 - 11:26 pm UTC

Thanks for the quick response; this didn't appear correct to me, either.
From the tkprof above, it appears to using time and temp disk resource for both sorts.

Good catch, union infers a sort unique, union all will include dups. I knew/know this, but did not see it here!

If the SQL is rewritten as intended:
explain plan for
insert into foobar
select foo
from
(select foo
from foo1
union
select foo
from foo2
);
the plan is:
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2296K| 552M| | 78452 (7)|
| 1 | VIEW | | 2296K| 552M| | |
| 2 | SORT UNIQUE | | 2296K| 38M| 105M| 78452 (30)|
| 3 | UNION-ALL | | | | | |
| 4 | INDEX FULL SCAN | PK_FOO1 | 1698K| 29M| | 8900 (9)|
| 5 | INDEX FULL SCAN | PK_FOO2 | 598K| 9347K| | 2696 (11)|
with only one sort.

This SQL is from a materialized view refresh, thusly:
dbms_mview.refresh('richa.rwa_foo','C');

I found it by looking for SQL that uses large amounts of temp space in v$sql_plan - this one reported using 1.2G.
Then I found this thread by Googling TempSpc.

So, it looks like the optimizer didn't catch this. Probably shouldn't expect it to, but I'll open a TAR.
Thanks again. 9205 RAC BTW


From Oracle Support

RichA, July 04, 2005 - 10:13 am UTC

There is a Bug.3537765 "SET queries with DISTINCT in query branch may have poor performance" filled on this issue.

This bug is fixed in 9.2.0.7.

9.2.0.7 PATCH is planned to release on 31st July 2005
"

The bug is currently public.
There is an one off for Solaris 32-bit only.
I'm not thouroughly convinced this is a fix, though.

tablespace sizing

Pradikan, July 26, 2005 - 10:43 am UTC

Hi Tom,

Want to know more about Capacity planning and how we are going to estimate the increase of tablespace with an increase in the incoming data.

If for example the estimate incoming size of data is around 3000k per day in a table which is having data , index and lob tablespaces, the hows the split across these tablespaces is calculated..

Thanks in advance





Tom Kyte
July 26, 2005 - 10:52 am UTC

benchmark, measure, multiply


tablespace sizing

pradikan, July 26, 2005 - 10:57 am UTC

Hi,

can you be more elaborative. I want to know the process involved to achieve this ..

Thanks



Tom Kyte
July 26, 2005 - 11:01 am UTC

load, measure, load more, measure, repeat



tablespace sizing

pradikan, July 27, 2005 - 4:12 am UTC

I am sorry for repeating the same quesiton....

can u give me an URL / example where i can find the calculations for the same...

Thanks



Tom Kyte
July 27, 2005 - 9:52 am UTC

not really, for I've never seen one that actually works.

load, measure, load more, measure more.




Estimating Temp Space With the Rule Based Optimizer

Danny Gawarecki, October 19, 2006 - 4:20 pm UTC

Tom - I am also having trouble with a query that is running me out of temp space and want to use the xplan to help me estimate how much temp space is needed. The problem is that the cardinality estimates displayed in the xplan are totally wrong. Here is the xplan I am currently generating:

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 770 | 8708 | | | |
| 1 | FILTER | | | | | | | |
| 2 | FILTER | | | | | 28,02 | PCWC | |
| 3 | HASH JOIN OUTER | | | | | 28,02 | P->S | QC (RAND) |
| 4 | TABLE ACCESS FULL | CBH_PTN_DOWNLOAD | 1 | 724 | 8703 | 28,01 | P->P | HASH |
| 5 | TABLE ACCESS FULL | PTN_SHIPPER_INFO | 2000 | 92000 | 4 | 28,00 | S->P | HASH |
| 6 | TABLE ACCESS BY INDEX ROWID| PKG_SORTATION_HIST_THU05 | 1 | 18 | 2 | | | |
| 7 | INDEX RANGE SCAN | PKSH_IND3_THU05 | 8 | | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------


The problem is that the CBH_PTN_DOWNLOAD table has 11,613,641 rows, PTN_SHIPPER_INFO has 10 rows, and PKG_SORTATION_HIST_THU05 accurately has 1 row.

We do use the rule based optimizer and do not do an analyze of any kind. Any ideas as to why the xplan has incorrect numbers (which is keeping me from getting the temp space estimate that I really want)?

I have also attached the query that generates this xplan.

CREATE TABLE cbh_new
UNRECOVERABLE
PARALLEL 8
TABLESPACE S735PDLD
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
AS
SELECT /*+ parallel(ptn_download_data,8) */ ptn_download_data.*
FROM ptn_download_data, ptn_shipper_info
WHERE pdld_shipper_nbr = psi_shipper_nbr(+)
AND NOT
(
(--PIER
NVL(PDLD_LAST_UPDATE_DATE, PDLD_UPDATE_TIMESTAMP) < (trunc(sysdate) - 29) - nvl(psi_pier_days_to_retain,10) AND
pdld_prohib_ld_dest_cny_cd IS NOT NULL OR
pdld_prohib_ld_dest_org_nbr IS NOT NULL OR
pdld_missort_pkg_det_cd IS NOT NULL
)
OR (-- DOMESTIC
NVL(PDLD_LAST_UPDATE_DATE, PDLD_UPDATE_TIMESTAMP) < (trunc(sysdate) - 29) - nvl(psi_dom_days_to_retain,5) AND
pdld_pkcl_class_cd = 'D' AND
pdld_prohib_ld_dest_cny_cd IS NULL AND
pdld_prohib_ld_dest_org_nbr IS NULL AND
pdld_missort_pkg_det_cd IS NULL
)
OR (-- NON-DOMESTIC
nvl(PDLD_LAST_UPDATE_DATE, PDLD_UPDATE_TIMESTAMP) < (trunc(sysdate) - 29) - nvl(psi_nondom_days_to_retain,10) AND
pdld_pkcl_class_cd <> 'D' OR pdld_pkcl_class_cd IS NULL) AND
pdld_prohib_ld_dest_cny_cd IS NULL AND
pdld_prohib_ld_dest_org_nbr IS NULL AND
pdld_missort_pkg_det_cd IS NULL
)
OR (
PDLD_PROHIB_LD_DEST_CNY_CD IS NULL AND
PDLD_PROHIB_LD_DEST_ORG_NBR IS NULL AND
PDLD_MISSORT_PKG_DET_CD IS NULL AND
EXISTS (
SELECT PKSH_PARCEL_ID
FROM sortarchive.pkg_sortation_hist_thu05
WHERE pksh_pksc_message_type in (''07'',''54'',''77'',''27'')
AND PKSH_FINAL_DIVERT_IND = ''Y''
AND pksh_parcel_id = PDLD_PACK_PARCEL_ID
)
)
);


Tom Kyte
October 20, 2006 - 3:59 am UTC

sorry - but if you use parallel, guess what.


YOU ARE NOT using the rbo, it is not possible at all.

so, got stats?

explain plan not giving correct result

A reader, April 28, 2008 - 1:42 pm UTC

Hi Tom, 

   SELECT
    sfolt.order_id
    ,sfolt.order_line_id
    ,scheduled_date
    ,order_name
    ,order_date
    ,order_type_id
    ,cluster_type_id
    ,sfolt.placement_id placement_id
    ,ad_slot_id
    ,ad_component_id
    ,inventory_type_id
    ,customer_id
    ,agency_id
    ,pricing_type_id
    ,package_id
    ,(case when nvl(scheduled_impressions,0) <> 0 then (extended_sales_price/scheduled_impressions)*1000 else null end) cpm
    ,scheduled_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  scheduled_impressions
    ,extended_sales_price  * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_sales_price
    ,extended_list_price   * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_list_price
    ,extended_floor_price  * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_floor_price
    ,remnant_revenue_cpm   * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  remnant_revenue_cpm
    ,adjustment_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END) adjustment_impressions
    ,adjustment_sales_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END) adjustment_sales_price
    ,sum(sfolt.scheduled_impressions/(sfolt.flight_end_date - sfolt.flight_start_date +1)) over ( partition by sfolt.order_id, sfolt.order_line_id order by sfolt.order_id, sfolt.order_line_id) total_impressions
   FROM
    T_ORDER_LINE_TOTAL_PROPORTION tp
    ,rapt_bddm.f_order_line sfolt
    ,rapt_bddm.l_ad_slot_proportion lptp
   WHERE
    tp.order_id = sfolt.order_id
    AND tp.order_line_id = sfolt.order_line_id
    AND tp.flight_start_date = sfolt.flight_start_date
    AND tp.flight_end_date = sfolt.flight_end_date
    AND LEAST(GREATEST(TRUNC(sfolt.scheduled_date, 'MM'), :l_min_proportion_month), :l_max_proportion_month ) = proportion_month
    AND sfolt.placement_id = lptp.placement_id
    AND sfolt.day_of_week  = lptp.day_of_week
    AND sfolt.scheduled_date between :l_dt_start_date and last_day(:l_dt_start_date);


***
If I run expalin plan for above it is showing below result, it is saying temp usage 41m but when I execute it actualy it is taking more then 20 GB..


10:41:24 SQL> EXEC dbms_stats.gather_table_stats(ownname=>'rapt_UDM', tabname=>'T_ORDER_LINE_TOTAL_PROPORTION', degree=>4)

PL/SQL procedure successfully completed.


10:42:46 NBD>EXEC dbms_stats.gather_table_stats(ownname=>'rapt_bddm', tabname=>'F_ORDER_LINE', degree=>4)

PL/SQL procedure successfully completed.

10:43:18 NBD>EXEC dbms_stats.gather_table_stats(ownname=>'rapt_bddm', tabname=>'L_AD_SLOT_PROPORTION', degree=>4)

PL/SQL procedure successfully completed.


delete from plan_table;

explain plan for SELECT
    sfolt.order_id
    ,sfolt.order_line_id
    ,scheduled_date
    ,order_name
    ,order_date
    ,order_type_id
    ,cluster_type_id
    ,sfolt.placement_id placement_id
    ,ad_slot_id
    ,ad_component_id
    ,inventory_type_id
    ,customer_id
    ,agency_id
    ,pricing_type_id
    ,package_id
    ,(case when nvl(scheduled_impressions,0) <> 0 then (extended_sales_price/scheduled_impressions)*1000 else null end) cpm
    ,scheduled_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  scheduled_impressions
    ,extended_sales_price  * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_sales_price
    ,extended_list_price   * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_list_price
    ,extended_floor_price  * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  extended_floor_price
    ,remnant_revenue_cpm   * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END)  remnant_revenue_cpm
    ,adjustment_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END) adjustment_impressions
    ,adjustment_sales_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot ELSE proportion/total_proportion END) adjustment_sales_price
    ,sum(sfolt.scheduled_impressions/(sfolt.flight_end_date - sfolt.flight_start_date +1)) over ( partition by sfolt.order_id, sfolt.order_line_id order by sfolt.order_id, sfolt.order_line_id) total_impressions
   FROM
    T_ORDER_LINE_TOTAL_PROPORTION tp
    ,rapt_bddm.f_order_line sfolt
    ,rapt_bddm.l_ad_slot_proportion lptp
   WHERE
    tp.order_id = sfolt.order_id
    AND tp.order_line_id = sfolt.order_line_id
    AND tp.flight_start_date = sfolt.flight_start_date
    AND tp.flight_end_date = sfolt.flight_end_date
    AND LEAST(GREATEST(TRUNC(sfolt.scheduled_date, 'MM'), :l_min_proportion_month), :l_max_proportion_month ) = proportion_month
    AND sfolt.placement_id = lptp.placement_id
    AND sfolt.day_of_week  = lptp.day_of_week
    AND sfolt.scheduled_date between :l_dt_start_date and last_day(:l_dt_start_date);


Explained.

Elapsed: 00:00:00.37
10:26:53 SQL> @ plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3159842403

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                               | 79905 |    19M|       | 52149   (4)| 00:10:26 |
|   1 |  WINDOW SORT          |                               | 79905 |    19M|    41M| 52149   (4)| 00:10:26 |
|*  2 |   FILTER              |                               |       |       |       |            |          |
|*  3 |    HASH JOIN          |                               | 79905 |    19M|       | 47768   (4)| 00:09:34 |
|*  4 |     HASH JOIN         |                               |    55 | 11220 |       |  2043   (3)| 00:00:25 |
|*  5 |      TABLE ACCESS FULL| F_ORDER_LINE                  |  1128 |   163K|       |  1993   (3)| 00:00:24 |
|   6 |      TABLE ACCESS FULL| T_ORDER_LINE_TOTAL_PROPORTION | 22118 |  1209K|       |    50   (2)| 00:00:01 |
|   7 |     TABLE ACCESS FULL | L_AD_SLOT_PROPORTION          |    29M|  1373M|       | 45294   (3)| 00:09:04 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:L_DT_START_DATE<=LAST_DAY(:L_DT_START_DATE))
   3 - access("PROPORTION_MONTH"=LEAST(GREATEST(TRUNC(INTERNAL_FUNCTION("SFOLT"."SCHEDULED_DATE"),'fmmm
              '),:L_MIN_PROPORTION_MONTH),:L_MAX_PROPORTION_MONTH) AND "SFOLT"."PLACEMENT_ID"="LPTP"."PLACEMENT_ID"
              AND "SFOLT"."DAY_OF_WEEK"="LPTP"."DAY_OF_WEEK")
   4 - access("TP"."ORDER_ID"="SFOLT"."ORDER_ID" AND "TP"."ORDER_LINE_ID"="SFOLT"."ORDER_LINE_ID" AND
              "TP"."FLIGHT_START_DATE"="SFOLT"."FLIGHT_START_DATE" AND
              "TP"."FLIGHT_END_DATE"="SFOLT"."FLIGHT_END_DATE")
   5 - filter("SFOLT"."SCHEDULED_DATE">=:L_DT_START_DATE AND
              "SFOLT"."SCHEDULED_DATE"<=LAST_DAY(:L_DT_START_DATE))



when query is running, output of below query is 

SELECT s.username, s.sid,  SERIAL#, segtype, program, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks, (u.blocks*8192)/(1024*1024*1024) gb,
sum((u.blocks*8192)/(1024*1024*1024)) over (partition by u.tablespace) total, machine
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr


10:32:06 SQL> @sort

USERNAME     SID    SERIAL# SEGTYPE   PROGRAM                        TABLESPACE CONTENTS     EXTENTS     BLOCKS      GB
---------- ----- ---------- --------- ------------------------------ ---------- --------- ---------- ---------- -------
RAPT_UDM     532        108 SORT      sqlplusw.exe                   TEMP       TEMPORARY      12989    1662592   12.68


It is still running, and it will go upto 20G, my question is why in plan table it is showing 41M while actauly it is using 12.68 GB, In time it is showing less then 1 minuts but this is taking more then 30 minutes. for running 1 month it is taking 40 minutes. plus it use 20GB temp space, If I run it for all the 12 months then it will take 5-6 hrs plus more then 100Gb temp space. Only I am the user accessing database. Databse : Oracle 10g
Blow is the spool, 





Tom Kyte
April 28, 2008 - 2:42 pm UTC

a) are the estimated "rows" values near accurate?

b) is that the actual plan that is being used?

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html


and you understand that the generic plan is "one size fits all" sort of stuff - eg: if you run for 1 month, you process 1 month of data and would need temp to store 1 month of data - it would seem to be obvious that if the same plan was used to process 12 months of data - it would, well, require more temp space than 1 month.

My guess: rows in the plan above are not representative of reality. temp space estimation is made based on a GUESS at how much memory we'll be able to use at runtime (which can differ a lot from what we ACTUALLY get to use of course, since we are a multi-user system - others might already be using the memory) and a GUESS at how many rows flow out of each step of the plan.

A reader, April 28, 2008 - 4:03 pm UTC

a) are the estimated "rows" values near accurate?


Yes Tom, you are correct, I did not notice rows against f_order_line table. actualy this table has 400K rows but here it is showing very less. so now I created another table from f_order_line say f_order_linejan07 which has only 1 month data, now plan is

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 452120033

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1713K| 392M| | 134K (2)| 00:26:58 |
| 1 | WINDOW SORT | | 1713K| 392M| 836M| 134K (2)| 00:26:58 |
|* 2 | HASH JOIN | | 1713K| 392M| | 45749 (4)| 00:09:09 |
|* 3 | HASH JOIN | | 1186 | 227K| | 111 (4)| 00:00:02 |
| 4 | TABLE ACCESS FULL| T_ORDER_LINE_TOTAL_PROPORTION1 | 1186 | 65230 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| F_ORDER_LINE_JAN07 | 24006 | 3305K| | 106 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL | L_AD_SLOT_PROPORTION | 29M| 1233M| | 45208 (3)| 00:09:03 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("SFOLT"."PLACEMENT_ID"="LPTP"."PLACEMENT_ID" AND
"SFOLT"."DAY_OF_WEEK"="LPTP"."DAY_OF_WEEK")
3 - access("TP"."ORDER_ID"="SFOLT"."ORDER_ID" AND "TP"."ORDER_LINE_ID"="SFOLT"."ORDER_LINE_ID" AND
"TP"."FLIGHT_START_DATE"="SFOLT"."FLIGHT_START_DATE" AND
"TP"."FLIGHT_END_DATE"="SFOLT"."FLIGHT_END_DATE")

22 rows selected.

This time I checked all rows aginst each table is correct and now it is showing 836M but when I am running the same query (with new table) it is using USERNAME SID SERIAL# SEGTYPE PROGRAM TABLESPACE CONTENTS EXTENTS BLOCKS GB
---------- ----- ---------- --------- ------------------------------ ---------- --------- ---------- ---------- -------
RAPT_UDM 532 121 SORT sqlplusw.exe TEMP TEMPORARY 7869 1007232 7.68

and it is still running, plus no user is connected with the databse, i check v$session and it is showin only my user even en above query it is showing only my session. even already 11 minutes pass but still query is running while in plan it is showing newarly 1 min.

second can you give me link in which you have given explaination about all hints. in plan it is showing WINDOW SORT, what it means?

is there any other way to run this with better result, i means is there any new feature which i can use to get better performace, this way I have to process data every day, and every day i have to process for 2 years data, so this process is realy killing me.. hit ratio is 90.12

Tom Kyte
April 29, 2008 - 8:33 am UTC

what are your pga memory settings. A window sort is just a buffered sort that might spill to disk if you don't have enough memory (suggest you turn on sql tracing with wait events and review a tkprof for this, it'll be very illuminating)

A reader, April 29, 2008 - 10:06 pm UTC

SGA IS 2.734375 GB
pga is 384 MB
total ram = 8 GB

second I am thiknig after analyzine table sample size column will shows total no. of records but If my table is big then it is giving less no. eg.

18:59:38 NBD>SELECT COUNT(1) FROM F_ORDER_LINE;

COUNT(1)
----------
448347

Elapsed: 00:00:00.59
18:59:41 NBD>EXEC dbms_stats.gather_table_stats(ownname=>'RAPT_BDDM', tabname=>'F_ORDER_LINE', degree=>4)

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.42
18:59:49 NBD>SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED FROM ALL_TABLES WHERE TABLE_NAME = 'F_ORDER_LINE'
19:00:09 2 /

TABLE_NAME SAMPLE_SIZE LAST_ANAL
------------------------------ ----------- ---------
F_ORDER_LINE 5536 29-APR-08

I am expecting 448347 but getting very less, BUT if table size is small then it will give correct no.

19:01:12 NBD>SELECT COUNT(1) FROM F_ORDER_LINE_JAN07;

COUNT(1)
----------
24006

Elapsed: 00:00:03.83
19:08:24 NBD>EXEC dbms_stats.gather_table_stats(ownname=>'RAPT_BDDM', tabname=>'F_ORDER_LINE_JAN07', degree=>4)

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.45
19:08:41 NBD>SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED FROM ALL_TABLES WHERE TABLE_NAME = 'F_ORDER_LINE_JAN07';

TABLE_NAME SAMPLE_SIZE LAST_ANAL
------------------------------ ----------- ---------
F_ORDER_LINE_JAN07 24006 29-APR-08



Tom Kyte
April 30, 2008 - 9:44 am UTC

ummm, you selected sample size, the number of rows we peeked at.

why don't you peek at num_rows, the estimated number of rows in the table. and realize that num_rows is based on a sample of 5,536 out of 448,347 (about a 1.2% random sample) so it is unlikely to be exactly 448,347 - but it will be close.

to estimate temp space for gathering statistics for a 30G table

PhoenixBai, November 23, 2009 - 3:59 am UTC

Tom, How to estimate the size of temp space that is necessary to collect statistics for a 30G table?


Tom Kyte
November 23, 2009 - 4:19 pm UTC

it would totally depend on the options you use - gathering histograms for size 254 for all columns - a lot more than no histograms. Estimation levels - that impacts it as well.

I am not aware of any formula, since dbms_stats runs dozens of queries to gather it's information - it would depend on those queries, but we don't 'see' them.

Just gather_table_stats(user, 'IMS_ALCTN')

PhoenixBai, November 24, 2009 - 3:49 am UTC

Just gather_table_stats(user, 'IMS_ALCTN'),as simple as that! So, Any idea how to make a guess? I tried, 800M, doesn`t work, then tried 1800M, still not. And I wonder if this procedure will full scan table? BTW, my version is 9iR2. (sorry for the unreadable format of my text, I don`t know how to make a switch to a new line here. ENTER doesn`t work) Thanks
Tom Kyte
November 24, 2009 - 11:24 am UTC

a guess at what???

enter works for everyone else.

an estimation of Temp space used for gather_table_stats(user, 'IMS_ALCTN')

PhoenixBai, November 24, 2009 - 8:58 pm UTC

A guess of a general size of temp space for gather_table_stats(user, 'IMS_ALCTN')procedure. As you said, since we can`t see those queries used in this procedure, I guess there is no way we can estimate it. Thanks for your time anyway! BTW, is it possible that asktom don`t support IE8.0? Or some other reasons maybe. I will try use FIREFOX next time!
Tom Kyte
November 25, 2009 - 12:11 pm UTC

... is it possible that asktom don`t support IE8.0?...

maybe the other way around would be true, IE8 doesn't support asktom, but - not as far as I know. I've not heard anything.

And you don't really say what you experienced, so it would be hard to say....

It is IE8! I tried IE6, it works perfectly

PhoenixBai, November 25, 2009 - 11:01 pm UTC

You can say that as long as it makes you feel good! Though, you are a little websitecentered (selfcentered).
Let`s say an application using Oracle 8i. And after upgrade to Oracle 10g, the application doesn`t work well as before. Who`s fault? Application`s. Why? It doesn`t do change to adjust to Oracle10g accordingly. Since some features or procedures supported in 8i have been deprecated or removed even.
While our application is still using the same old things.

It is the same with asktom and IE8. asktom needs to do some change to support IE8. Not a fan of Microsoft though.

Besides saying all this, I am a huge huge fan:-)

PS: experience is: I hit ENTER, there comes a space instead of a new line. Not so much to describe actually.
Tom Kyte
November 28, 2009 - 12:35 pm UTC

I'll have someone look at it, we are just using a text area.



Your analogy does not hold water. For it to - the roles would have to be reversed entirely.

There are an infinite set of browsers out there and a single asktom. Asktom is just using plain old html. If a browser changes the way it wants to interpret something - what am I to do? Have an infinite set of asktom versions?

Your example was "we upgraded a server", very different from "someone is using a brand new client that changed the way the old clients worked", the thing in the center (the server) has not changed. Rather the infinite set of clients has one client that says "I don't want to play that way, you play my way"

Temp tablespace running out of space

Tarun, April 06, 2010 - 11:17 am UTC

DB - 9.2.0.8
A query is hitting temp tablespace, current size is 30Gig. Stats are latest for all the tables but still the explain plan does not estimate on TEMP usage.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 1560 | 15298 | | |
| 1 | SORT GROUP BY | | 3 | 1560 | 15298 | | |
| 2 | VIEW | | 3 | 1560 | 15296 | | |
| 3 | UNION-ALL | | | | | | |
|* 4 | HASH JOIN SEMI | | 1 | 329 | 5405 | | |
| 5 | NESTED LOOPS | | 1 | 316 | 5402 | | |
| 6 | NESTED LOOPS | | 1 | 299 | 5401 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS | | 1 | 282 | 5400 | | |
| 8 | NESTED LOOPS | | 21 | 5145 | 5399 | | |
| 9 | NESTED LOOPS | | 334 | 69472 | 5396 | | |
|* 10 | HASH JOIN | | 266K| 39M| 75 | | |
| 11 | TABLE ACCESS FULL | TAX_ACCRUAL_PT_TRC_TMP | 6363 | 89082 | 3 | | |
| 12 | NESTED LOOPS | | 64638 | 8963K| 44 | | |
|* 13 | HASH JOIN | | 209 | 27797 | 42 | | |
| 14 | VIEW | VW_SQ_4 | 407 | 8954 | 1 | | |
| 15 | SORT GROUP BY | | 407 | 5291 | 1 | | |
|* 16 | INDEX FULL SCAN | SYS_C0015570 | 485 | 6305 | 32 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | DEFERRED_INCOME_TAX_RATES | 8 | 136 | 1 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 18 | NESTED LOOPS | | 4982 | 540K| 40 | | |
|* 19 | HASH JOIN | | 612 | 57528 | 9 | | |
| 20 | TABLE ACCESS FULL | TAX_ACCRUAL_POWERTAX_MAP | 1478 | 45818 | 2 | | |
|* 21 | HASH JOIN | | 2391 | 147K| 6 | | |
| 22 | NESTED LOOPS | | 23 | 943 | 3 | | |
| 23 | VIEW | TAX_ACCRUAL_COMPANY_ROLLUP_V | 2 | 60 | 2 | | |
| 24 | UNION-ALL | | | | | | |
|* 25 | INDEX SKIP SCAN | TA_CO_ROLLUP | 1 | 39 | | | |
| 26 | INDEX FULL SCAN | SYS_C007841 | 109 | 436 | 1 | | |
|* 27 | FILTER | | | | | | |
| 28 | INDEX FULL SCAN | SYS_C007841 | 109 | 436 | 1 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 29 | INDEX FULL SCAN | SYS_C0024990 | 10 | 110 | 1 | | |
| 30 | TABLE ACCESS FULL | NORMALIZATION_SCHEMA | 1155 | 25410 | 2 | | |
|* 31 | INDEX RANGE SCAN | SYS_C0015570 | 8 | | 1 | | |
|* 32 | INDEX RANGE SCAN | TA_PT_TC_ROLL_TMP_PK | 309 | 2781 | 1 | | |
| 33 | TABLE ACCESS BY GLOBAL INDEX ROWID | DEFERRED_INCOME_TAX | 1 | 52 | 1 | ROWID | ROW L |
|* 34 | INDEX RANGE SCAN | SYS_C00112917 | 1 | | 1 | | |
| 35 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAX_DEPRECIATION | 1 | 37 | 1 | ROWID | ROW L |
|* 36 | INDEX UNIQUE SCAN | SYS_C00112925 | 1 | | | | |
| 37 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAX_DEPRECIATION | 1 | 37 | 1 | ROWID | ROW L |
|* 38 | INDEX UNIQUE SCAN | SYS_C00112925 | 1 | | | | |
| 39 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAX_DEPR_ADJUST | 1 | 17 | 1 | ROWID | ROW L |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 40 | INDEX UNIQUE SCAN | SYS_C00113007 | 1 | | | | |
| 41 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAX_DEPR_ADJUST | 1 | 17 | 1 | ROWID | ROW L |
|* 42 | INDEX UNIQUE SCAN | SYS_C00113007 | 1 | | | | |
| 43 | VIEW | VW_NSO_3 | 1 | 13 | 2 | | |
| 44 | TABLE ACCESS FULL | TMP_COMPANIES | 1 | 13 | 2 | | |
|* 45 | HASH JOIN SEMI | | 2 | 438 | 9891 | | |
|* 46 | HASH JOIN | | 2 | 412 | 9888 | | |
| 47 | NESTED LOOPS | | 52 | 9568 | 9886 | | |
| 48 | NESTED LOOPS | | 258 | 45150 | 9885 | | |
| 49 | NESTED LOOPS | | 1003 | 141K| 9865 | | |
| 50 | NESTED LOOPS | | 2290 | 254K| 9842 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 51 | NESTED LOOPS | | 484 | 49852 | 9837 | | |
| 52 | NESTED LOOPS | | 59 | 5074 | 9834 | | |
| 53 | NESTED LOOPS | | 66 | 5214 | 9833 | | |
|* 54 | HASH JOIN | | 66 | 4752 | 9832 | | |
| 55 | TABLE ACCESS FULL | NORMALIZATION_SCHEMA | 1155 | 21945 | 2 | | |
| 56 | TABLE ACCESS BY GLOBAL INDEX ROWID| DEFERRED_INCOME_TAX | 1 | 19 | 1 | ROWID | ROW L |
| 57 | NESTED LOOPS | | 12679 | 656K| 9829 | | |
|* 58 | HASH JOIN | | 120K| 3990K| 7425 | | |
| 59 | TABLE ACCESS FULL | TAX_ACCRUAL_PT_TRC_TMP | 6363 | 89082 | 3 | | |
| 60 | PARTITION RANGE ALL | | | | | 1 | 194 |
| 61 | TABLE ACCESS FULL | TAX_BOOK_RECONCILE | 4028K| 76M| 7088 | 1 | 194 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 62 | INDEX RANGE SCAN | SYS_C00112917 | 1 | | 1 | | |
|* 63 | TABLE ACCESS BY INDEX ROWID | JURISDICTION | 1 | 7 | 1 | | |
|* 64 | INDEX UNIQUE SCAN | SYS_C0016046 | 1 | | | | |
|* 65 | INDEX UNIQUE SCAN | SYS_C0017831 | 1 | 7 | | | |
| 66 | TABLE ACCESS BY INDEX ROWID | DEFERRED_INCOME_TAX_RATES | 8 | 136 | 1 | | |
|* 67 | INDEX RANGE SCAN | SYS_C0015570 | 8 | | 1 | | |
|* 68 | INDEX RANGE SCAN | SYS_C0024990 | 5 | 55 | 1 | | |
|* 69 | VIEW | TAX_ACCRUAL_COMPANY_ROLLUP_V | 1 | 30 | 1 | | |
| 70 | UNION-ALL PARTITION | | | | | | |
|* 71 | INDEX RANGE SCAN | TA_CO_ROLLUP | 1 | 39 | | | |
|* 72 | INDEX UNIQUE SCAN | SYS_C007841 | 1 | 4 | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 73 | FILTER | | | | | | |
|* 74 | INDEX UNIQUE SCAN | SYS_C007841 | 1 | 4 | | | |
| 75 | TABLE ACCESS BY INDEX ROWID | TAX_ACCRUAL_POWERTAX_MAP | 1 | 31 | 1 | | |
|* 76 | INDEX RANGE SCAN | TA_POWERTAX_MAP_PK | 1 | | 1 | | |
|* 77 | INDEX UNIQUE SCAN | TA_PT_TC_ROLL_TMP_PK | 1 | 9 | | | |
| 78 | VIEW | VW_SQ_2 | 407 | 8954 | 1 | | |
| 79 | SORT GROUP BY | | 407 | 5291 | 1 | | |
|* 80 | INDEX FULL SCAN | SYS_C0015570 | 485 | 6305 | 32 | | |
| 81 | VIEW | VW_NSO_1 | 1 | 13 | 2 | | |
| 82 | TABLE ACCESS FULL | TMP_COMPANIES | 1 | 13 | 2 | | |
---------------------------------------------------------------------------------------------------------------------------------

And the same query fails with TEMP tablespace error.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here