Great gap between Est. Temp Space Used and Act. Temp Space Used

Hi all,

I have a question about the Est. Temp Space Used and Act. Temp Space Used, 
from the query plan, I found the estimated temp space is 200% of the actual 
temp space

Est. Temp Space Used (Mb) 4722.6

Act. Temp Space Used (Mb) 1948.6

This make it difficult to set QUERY_TEMP_SPACE_LIMIT as the resources is 
very tight, do you have any idea to make the estimation of the temp space 
used more accurate?

Version: 12.7.0/080707/P/ESD 5/MS/Windows 2003/64bit

Keith Ma wrote:
> Hi all,
> 
> I have a question about the Est. Temp Space Used and Act. Temp Space Used, 
> from the query plan, I found the estimated temp space is 200% of the actual 
> temp space
> 
> Est. Temp Space Used (Mb) 4722.6
> 
> Act. Temp Space Used (Mb) 1948.6
> 
> This make it difficult to set QUERY_TEMP_SPACE_LIMIT as the resources is 
> very tight, do you have any idea to make the estimation of the temp space 
> used more accurate?
> 
> Version: 12.7.0/080707/P/ESD 5/MS/Windows 2003/64bit
> 
> 

Hi

I think we should found why the difference between the Actual and 
Estimated is so high. Do you have HG/LF/fast-FP indexes on all columns 
participating in the WHERE clause(s) of your query? Are these indexes 
really used by the query? If not - then the optimizer has to guess - 
which may lead to incorrect estimations. Also, joins and aggregations, 
especially when there are no HG indexes on join columns may affect 
optimizer calculations. Skewing of data distribution of join columns may 
also contribute to the problem.

I would suggest checking estimated vs. actual number of rows returned by 
leafs in the detailed execution plan. If you see differences also on 
this level then it may be easier to start the investigation here then to 
analyze just one figure at the very top of the plan. Since you are using 
IQ 12.7, you may play with optimizer hints (selectivity etc.) and see if 
estimation of returned rows for leafs and joins and total Temp space 
used estimation are affected by it.

Hope it helps
Leonid Gvirtz