Creating mview takes ages despite "build deferred" and "refresh on demand"
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-lReceived on Fri May 23 2014 - 12:10:57 CEST