Database Tuning (MOSC)

MOSC Banner

INCREMENTAL stats. collection on large table takes long time after new column

Hi Guys

Version details : Oracle 12.2 running on Linux.

We have tables that are in terabytes which are partitioned by date and then sub-partitioned. They are have basic/advanced compression. There are no indexes. They hold more than seven years of data.

The table in question has its preferences set to CASCADE=>TRUE, GRANULARITY=>AUTO, INCREMENTAL=>TRUE, INCREMENTAL_STALENESS=>USE_STALE_PERCENT, USE_LOCKED_STATS, METHOD_OPT=>FOR ALL COLUMNS SIZE 1

Lets say if load 5 years of data in an empty table and collect stats using

dbms_stats.gather_table_stats(ownname => user,tabname => TABLE_NAME,granularity => 'AUTO',cascade => TRUE, degree => 8, method_opt => 'for all columns size repeat');

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