Re: ** temporary,queue table statistics
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Wed, 31 Dec 2008 17:14:25 -0800 (PST)
Martin/Daniel,
Thanks. The version is 10G. Any suggestion for Queue tables? Thanks
--- On Sun, 12/28/08, Martin Berger <martin.a.berger@xxxxxxxxx> wrote:
From: Martin Berger <martin.a.berger@xxxxxxxxx>
Subject: Re: ** temporary,queue table statistics
To: ajoshi977@xxxxxxxxx
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Sunday, December 28, 2008, 12:52 PM
Josi,
please
keep in mind, temporary tables can hold different sets of data for
different sessions (!), but statistics are stored once per
database. If the session which gathers statistics does
not have a representative set of ata in the temp table, you probably
will make things worse. So if unsure, dynamic sampling might be the
better way.
my .02€ Martin
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
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:
