PERFORMANCE PROBLEMS WHILE CREATEING OR DROPPING MATERIALIZED VIEWS (Doc ID 1073606.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked Currency on 05/16/12

Symptoms

Create or drop materialized view takes too long compared to the time it takes to create a table with the same statement.

The difference looks to be from the

select distinct sq.sumobj#, sq.nodeid, sq.pflags, sq.xpflags, sq.hashval,
sq.hashval2, sq.selcnt, sm.containerobj#, sq.leafcnt, sq.flags
from
sumdetail$ sd, sumqb$ sq, sum$ sm where sq.sumobj# = sd.sumobj# and
sq.sumobj# = sm.obj# and sd.detailobj#=:1 and   bitand(sm.pflags,
1073741824) = 0 start with (((sq.nodeid = sd.qbcid and sq.nodeid != 0) or
sq.nodeid = 1)             and sd.detailobj# = :1) connect by prior
sq.orignode = sq.nodeid and prior sq.orignode != 0 order by sq.sumobj#,
sq.nodeid


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.01       0.00          0          0          0           0
Fetch     3309   3455.43    3430.64          7      47772          0        3306
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3315   3455.44    3430.64          7      47772          0        3306

which spend a lot of time with the fetch.

There are
SQL> select count(*) from sys.sumdetail$;

COUNT(*)
----------
   57618

Changes

In this case the slow drop materialized view is down to the fact that there are over 5000 mviews defined on the database.

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