Creating mview takes ages despite "build deferred" and "refresh on demand"

From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 23 May 2014 12:10:57 +0200

Hello,

I have a materialized view that I'm creating and I wanted to delay the actual 
refresh until the night, so I used the following statement:

create materialized view my_mview
  build deferred
  refresh on demand
as
select ...
;

However Oracle seems to run the select neverthelesse because the statement has 
been running for quite some time now and looking at the ASH information it is 
doing _something_. 

It has allocated some temp space as seen by "max(temp_space_allocated) from 
gv$active_session_history" for that particular session. 

When I look at the wait events for this statement, I can also see that there 
some waits for "direct path read temp" so I assume that Oracle *is* at least 
_running_ the select statement. 

So my question is: shouldn't the "build deferred" and "refresh on demand" 
prevent exactly that? What am I missing here? 

And where else could I look to find out _what_ Oracle is doing?

Even though the statement has been running for a while, it is not monitored by 
Oracle because it does not show up in gv$sql_monitor and 
dbms_sqltune.report_sql_monitor() does not return a report either.

Regards
Thomas
 


--
//www.freelists.org/webpage/oracle-l

Creating mview takes ages despite "build deferred" and "refresh on demand"

Other related posts: