Articles Comments

AskDba.org Weblog » Entries tagged with "dbms_stats"

GATHER_STATS_JOB – Is it enabled?

August 18th, 2009 | 2 Comments

Few days back , Martin had posted a series of post on a issue where GATHER_STATS_JOB was failing silently for a large object. If you have missed it, you can check following links Automated statistics gathering silently fails http://mwidlake.wordpress.com/2009/07/20/automated-statistics-gathering-silently-fails/ Automated Statistics Gathering Silently Fails #2 http://mwidlake.wordpress.com/2009/07/23/automated-statistics-gathering-silently-fails-2/ Automatic Statistics Gathering Fails #3   http://mwidlake.wordpress.com/2009/07/29/automatic-statistics-gathering-fails-3/ Automated statistics gathering silently fails Automated Statistics Gathering Silently Fails #2 Automatic Statistics Gathering Fails #3 This post is based on some of the discussions on the thread. Please note that this is not intended to discuss bugs (if any) associated with the job Gather_stats_job was introduced with Oracle 10g to gather statistics for database objects which has stale statistics (10% of data has changed, you can query dba_tab_modifications) or the tables for which the statistics has not been gathered (new tables … Read entire article »

Filed under: database, oracle

dbms_stats.copy_table_stats does not alter low/high value

June 24th, 2009 | 5 Comments

I was working on a performance issue arising due to missing table stats on a new partition for large Partitioned table.This was a 10.2.0.4 database on IBM AIX 5L. So as to resolve the issue , I used dbms_stats.copy_table_stats to copy the stats to new parition from the previous partition. EXEC DBMS_STATS.COPY_TABLE_STATS (‘owner’, ‘tabname’, ‘SourcePart’, ‘TargetPart’) You can find more details about it in below link http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html There is one more link explaining this with example http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/ Article from Optimizer development group states that the low and high value for the target partition is set from the partition key values. But when I used dbms_stats.copy_table_stats, I found that it is not the case and it actually copies the low and high values from the previous partition. You can use following function to check the high and low … Read entire article »

Filed under: performance