Re: ** temporary,queue table statistics
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: ajoshi977@xxxxxxxxx
- Date: Sun, 28 Dec 2008 10:32:27 -0700
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:
