Skip to Main Content
  • Questions
  • Statistics on Materlized View taking plus 10 hours

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Kessy.

Asked: February 28, 2018 - 4:07 pm UTC

Last updated: March 01, 2018 - 3:18 am UTC

Version: Oracle11gr2

Viewed 1000+ times

You Asked

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?


and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.