Oracle Database Discussions

Dear Gurus

Hoping that someone can point me in the right direction to trouble-shoot. Version Enterprise 11.1.0.7 AIX.

Range partitioned table with hash sub-partitions.

Automatic stats gather is on.

dba_tables shows global stats YES analyzed 06/09/2011 (when first analyzed on migration of data) and dba_tab_partitions shows most partitions analyzed at that date and most others up until 10/10/2011 - done by the automatically by the weekend stats_gather scheduled job.

46 new partitions added in the last few months but no stats gathered on in dba_tab_partitions and dba_table last_analyzed says 06/09/2011 - the date it was first analyzed manually gathering stats rather than using auto stats gatherer.

Checked dbms_stats.get_prefs set to incremental and all the default values recommended by Oracle are set including publish = TRUE.

dba_tab_partitions has no values in num_rows, last_analyzed etc.

dba_tab_modifications has no values next to the new partitions but shows inserts as being 8 million approx per partition - no deletes or updates.

dba_tab_statistics has no values next to the new partitions. All other partitions are marked as NO in the stale column.

checked the dbms_stats job history window - and it showed that the window for stats gathering stopped within the Window automatically allowed.

Looked at Grid Control - the stats gather for the table started at 6am Saturday morning and closed 2am Monday morning.

Checked the recommended Window - and it stopped analyzing that table at 2am exactly having tried to analyze it since Saturday morning at 6am.

Had expected that as the table was in incremental mode - it wouldn't have timed out and the new partitions would have been analyzed within the window.

The job_queue_processes on the database = 1.

Increased the job_queue_processes on the database = 2.

Had been told that the original stats had taken 3 days in total to gather so via GRID - scheduled a dbms_scheduler (10.2.0.4) - to gather stats on that table over a bank holiday weekend - but asked management to start it 24 hours earlier to take account of extra time.

The Oracle defaults were accepted (and as recommended in various seminars and whilte papers) - except CASCADE - although I wanted the indexes to be analyzed - I decided that was icing on the cake I couldn't afford).

Went to work - 24 hours later - checked dba_scheduler_tasks_job running. Checked stats on dba_tab_stats and tba tablestats nothing had changed. I had expected to see partition stats for those not gathered first - but quick check of Grid - and it was doing a select via full table scan - and still on the first datafile!! Some have suggested to watchout for the DELETE taking along time - but I only saw evidence of the SELECT - so ran an AWR report - and sure enough full table scan on the whole table. Although the weekend gather stats job was also in operation - it wasn't doing my table - but was definitely running against others.

So I checked the last_analyzed on other tables - one of them is a partitioned table - and they were getting up-to-date stats. But the tables and partitions are ridiculously small in comparison to the table I was focussed on.

Next day I came in checked the dba_scheduler_job log and my job had completed within 24 hours and completed successfully.

Horrors of horrors - none of the stats had changed one bit in any view I looked at.

I got my excel spreadsheet out - and worked out whether because there was less than 10% changed - and I'd accepted the defaults - that was why there was nothing in the dba_tables to reflect it had last been analyzed when I asked it to.

My stats roughly worked out showed that they were around the 20% mark - so the gather_table stats should have picked that up and gathered stats for the new partitions? There was nothing in evidence on any views at all.

I scheduled the job via GRID 10.2.04 for an Oracle database using incremental stats introduced in 11.1.0.7 - is there a problem at that level?

There are bugs I understand with incremental tables and gathering statistics in 11.1.0.7 which are resolved in 11.2.0 - however we've applied all the CPU until April of last year - it's possible that as we are so behind - we've missed stuff?

Or that I really don't know how to gather stats on partitioned tables and it's all my fault - in which case - please let me know - and don't hold back!!!

I'd rather find a solution than save my reputation!!

Thanks for anyone who replies - I'm not online at work so can't always give you my exact commands done - but hopefully you'll give me a few pointers of where to look next?

Thanks!!!!!!!!!!!!!