Database Tuning (MOSC)

MOSC Banner

INCREMENTAL Statistics taking long time

edited Jun 27, 2022 9:16AM in Database Tuning (MOSC) 4 commentsAnswered

Hi Guys

I have table, lets say ACCOUNTING_DIRECTION, which has roughly four hundred sub-partitions per day. The table holds data since 2015. There is roughly forty-six thousand sub-partitions. They are all BASIC compressed and the statistics are all up to date.

I have locked all table partitions older than a month using DBMS_STATS.lock_partition_stats('SCOTT', 'ACCOUNTING_DIRECTION', PARTITION_NAME), which leaves 6000 sub-partitions. 

The table preference for the table ACCOUNTING_DIRECTION, is INCREMENTAL=TRUE, METHOD_OPT=FOR ALL COLUMNS SIZE 1, CASCADE=TRUE, GRANULARITY=AUTO AND STALE_PERCENT = 10%.The size of the table is 106GB. This table is regularly inserted through out the day.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center