Re: ** temporary,queue table statistics

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.


Regards,
Daniel Fink

--
Daniel Fink

OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/


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

Other related posts: