Mview Refresh Slow Because of SELECT TO_NUMBER(VALUE) FROM V$SES_OPTIMIZER_ENV WHERE NAME = 'parallel_ddl_forced_degree' (Doc ID 1484133.1)

Last updated on AUGUST 23, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

A single mview job refreshes 764 mviews to 50 different databases. In 10g this took about 60 minutes; in 11gR2 this is taking about 90 minutes.   A tkprof showed the total elapsed time is 84 minutes, out of which the below statement is taking over half of that:

 

Changes

 Upgrade to 11gR2.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms