Re: ** temporary,queue table statistics

From: Daniel Fink <>
Date: Sun, 28 Dec 2008 10:32:27 -0700
Message-ID: <>

Does your version of Oracle support dynamic sampling?

I recently encountered a situation where statistics were gathered on a temporary table that was populated by a 3rd party application. When the tables were added to the job to gather statistics, the stats were all 0 (the only time data was inserted into the temp table was by a specific application function). We started seeing very poor performance and found that the 0 statistics were causing the CBO to make bad decisions. Once we deleted and then locked stats on the temporary tables, the statements' performance was improved. The reason was not that the CBO used the defaults (as there were no statistics), but rather that the sessions used dynamic sampling to correctly gather statistics and enable the CBO to make correct calculations and decisions.

If the temporary table has very stable data (like populating the last 100 customer orders), then you may consider setting the table/index statistics to "correct" values.

There are two issues you need to consider...

  • If dynamic sampling is taking too much time and/or causing poor execution plans, you might want to consider setting table/index statistics.
  • Even when a temporary table is currently populated with stable data...that may (is likely) to change over time and you will have to revisit the statistics gathering issue.

Daniel Fink

Daniel Fink - Oracle Performance, Diagnosis, Data Recovery and Training

Oracle Blog

Lost Data?

A Joshi wrote:

> Hi,
> About statistics for temporary table :
> Is it best to have no statistics or Are they needed? Are they OK? Is
> there a problem if they have statistics? Any side effects.
> I mean as such they do not have any rows so one would expect no
> statistics. If the data contained in them is known : then is it
> advisable to create a session : then populate the temp table and then
> gather statistics on them. Any issues with that. Or is it create a
> regular table with same structure then populate with representative
> data. Just trying to get the input on this and any exceptions/issues.
> Thanks
> On one hand oracle says statistics on temp table is not supported but
> it also has note like :
> Metalink note 403587.1 does talk about gathering statistics.
> What about queue tables. Same questions for queue tables. Thanks
Received on Sun Dec 28 2008 - 11:32:27 CST

Original text of this message