I'm trying to update the stats on a materlized view I created:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CSPRD',
tabname => 'mv_llattrdata_shrinked',
cascade => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 3
);
END;
It's been 15 hours and the process is still running.
The base table itself is near 80GB with around +600 million rows. The materialized view is a subset of that data so maybe around 1/3.
The host has 40gb of ram and 4 cores. I specifically set the estimate percent to auto so it would be faster and I have parallelism / degree set to 3 for the same reason.
Am I missing something, what should I be looking at?
My guess would be expensive sorting for the histograms.
But let's not guess - let's get evidence.
Try this:
exec dbms_monitor.session_trace_enable(waits=>true);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CSPRD',
tabname => 'mv_llattrdata_shrinked',
cascade => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto'
);
END;
/
exec dbms_monitor.session_trace_enable(waits=>false);
Notice I've gone serial here to get all the data in a single trace file.
Let it run for a while and then take a look at trace data.
DBMS_STATS just runs a number of SQL's behind the scenes, so even while it is running, you can monitor V$SESSION (and v$active_session_history if licenses) to pick up the SQL_ID's that are causing problems.
But my suspicion would be excessive histograms, so cutting back on them might be the resolution ultimately.