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

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Fri, 23 May 2014 12:10:57 +0200
Message-ID: <537F1EB1.2030906_at_mgm-tp.com>



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  

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 23 2014 - 12:10:57 CEST

Original text of this message