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