Oracle 11gR2 DBMS_STATS introduced useful function report_col_usage to report column usage i.e if column is being used for equality,like predicates. I came to know it while reading Optimizer whitepaper .As per document, DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object. This information is used by Oracle…
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…
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’,…