also, it depends on the refresh interval. is it not?

Refresh interval is perhaps not as important as "how quickly the data changes" and how much changes in one hit. For some data sets 'fast' never is quicker than complete.

Question: For a partitioned MV are there any differences in mechanism for MV refresh? - is there a route like "truncate partition / insert (append) into" for just the changed partition. This would be useful in a DW context

Thanks Tom, a timely reminder to "Just read the Documentation"

Tom,

Do you help in writing Oracle doc's or is it some other group..

Thanks..

Tom, you comment that 'Fast' is not the best description for the non-complete refresh.

The business model in our DWH requires that we report 'AS IS' on data (that is we always join the fact to the current values of the reference data) The downside of this that when items change parentage (You'd think that <they> would know apples are members of fruit!) There is a need to refresh the pre-built aggregations
Fast MV refresh (on demand) is, in general, OK for our needs, but sometimes (to often) the business throws a major change that is really faster to process as a complete refresh - can you think of a way we can "guess" which approach will be quicker for a given data change and then execute the most appropriate refresh?.
A straight count of changed 'child' records will not help as the number parents affected could vary greatly - we have seen a change of 5% of the data affecting 25% of a parent summary and the same amount of change affecting less than 3% the same summary

OK... I was trying not to use the F (fast) word ;-} but chose the wrong alternative!

I fully understand your points (and they match our experience) But for a production system it would be great to have qualitative indicator to say "fast is best" or not.

Could we use the view logs to help on this?

Dear Tom
following are the stats from my system and i am not concluding anything as looking for your conclusion about mview refresh rate
 SQL> select count(*) from mlog$_ab;

   COUNT(*)

 ----------

    163127

    
 SELECT aobject_name,a.object_type,tablespace_name,SUM(bytes)
 OVER(partition by object_name)/(1024*1024) objsize FROM all_objects a,user_segments WHERE segment_name=object_name AND a.object_name like
 UPPER('MLOG$_ab') AND object_type<>'SYNONYM' ORDER BY
tablespace_name,objsize desc,object_name;
 OBJECT_NAME          OBJSIZE

----------           ----------

MLOG$_ab               20 

 
 Job Name                        Last Start        Last End     ST  Run  Pri/Xit

 ____________________________ ________________ ________________ __ _______ ___

 

 gca_p_b_refresh_person_vw    05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1

 refresh_vw    05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1

select count(*) from mlog$_ab;

COUNT(*)

----------

      1989
  Hence the mview  refresh rate -:     
    = 162k/29 min = 5.6k/min

it it feasible that 350k record in 15 minutes ????????????


    

    

     

sorry but I don't even know what I'm looking at here.

another way to look at that could be "we pushed 92 records per second over our wan and applied them to another table"

can we do a fast refresh of 350k changes in 15 minutes?  sure, here is my little desktop doing just that:


ops$tkyte@ORA9IR2> create table t
  2  as
  3  select rownum pk, big_table.* from big_table.big_table
  4  where rownum <= &R;
old   4: where rownum <= &R
new   4: where rownum <= 350000
 
Table created.
 
Elapsed: 00:00:03.67
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(pk);
 
Table altered.
 
Elapsed: 00:00:01.90
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view log on t;
 
Materialized view log created.
 
Elapsed: 00:00:00.07
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view t_mv
  2  refresh fast
  3  on demand
  4  as
  5  select * from t;
 
Materialized view created.
 
Elapsed: 00:00:05.17
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> begin
  2          for i in 1 .. &R
  3          loop
  4                  update t set owner='x' where pk = 1;
  5                  commit;
  6          end loop;
  7  end;
  8  /
old   2:        for i in 1 .. &R
new   2:        for i in 1 .. 350000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:13.29
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
 
  COUNT(*)
----------
    350000
 
Elapsed: 00:00:00.17
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('T_MV')
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:39.00
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:02.77
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          for i in 1 .. &R
  3          loop
  4                  update t set owner='x' where pk = i;
  5                  commit;
  6          end loop;
  7  end;
  8  /
old   2:        for i in 1 .. &R
new   2:        for i in 1 .. 350000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:03:11.22
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
 
  COUNT(*)
----------
    350000
 
Elapsed: 00:00:00.31
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('T_MV')
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:01:45.90
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:03.27
ops$tkyte@ORA9IR2>


<b>thing to note - big difference between 350k changes to a row vs 350k rows changed.... 

amount of data moving over the wan will impact you as well.


You can always run a 10046 level 12 trace to see

a) what the refresh does
b) what it is waiting on most of the time....

in order to start seeing where you want to apply more resources</b>

 

I am running version 9.2.0.4 (e) The compitability param is set to 9.2.0.4) and cannot seem to get the fast refresh working on an aggregrate query. I have done the following things (as described in the data wherehouse guid

1. created a materialized view log with all columns in the table and using the "with rowid, sequence .... including new values"

2. create the materialized view as follows
CREATE MATERIALIZED VIEW mm
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
rpt2
, SUM(val1)
, count(val1)
from a_table;

After i completed the above steps i query dba_mviews and everything looks okay. I then queried the materialized view log and found nothing there, as exptected. When i update the base table the mview log gets some rows. However when i query dba_mview i find that my materialized view is UNUSABLE. When i execute dbms_mvew.refresh('MM') i get this:
11:26:54 ORA-12057: materialized view
"MM" is INVALID and must complete refresh.

Is there some sort of specific settings that need to be in place in order for fast refresh to work????

The mview is based on a simple rollup query, nothing complex and its only coming from a single table.

/*
Here is the sql i used in my test. This is a very simplified
version but i am still getting the same errors.
*/

-- Create the base table
CREATE TABLE base (key1 VARCHAR2(1), key2 NUMBER, val NUMBER);

-- Populate the base table
INSERT INTO base VALUES('X',1,10);
INSERT INTO base VALUES('X',2,10);
INSERT INTO base VALUES('X',3,10);
INSERT INTO base VALUES('X',4,10);
INSERT INTO base VALUES('X',5,10);
INSERT INTO base VALUES('X',6,10);
INSERT INTO base VALUES('X',7,10);
INSERT INTO base VALUES('X',8,10);
INSERT INTO base VALUES('X',9,10);
INSERT INTO base VALUES('X',10,10);
INSERT INTO base VALUES('Y',1,10);
INSERT INTO base VALUES('Y',2,10);
INSERT INTO base VALUES('Y',3,10);
INSERT INTO base VALUES('Y',4,10);
INSERT INTO base VALUES('Y',5,10);
INSERT INTO base VALUES('Y',6,10);
INSERT INTO base VALUES('Y',7,10);
INSERT INTO base VALUES('Y',8,10);
INSERT INTO base VALUES('Y',9,10);
INSERT INTO base VALUES('Y',10,10);

-- Create the materialized view log
CREATE MATERIALIZED VIEW LOG ON base
WITH SEQUENCE, ROWID (key1,key2,val)
INCLUDING NEW VALUES;

-- Create the materiliized view
CREATE MATERIALIZED VIEW base_rlup
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
key1
, SUM(val)
, COUNT(val)
FROM base
GROUP BY key1;

/*
NOW SELECT FROM THE BASE TABLE AND THE DBA_MVIEWS TO VERIFY.
*/

-- Okay everything is okay. Now we are going to update the base table
UPDATE base SET val = 10000
WHERE key1 = 'X' AND key2 = 2;

COMMIT;

/*
At this point if i query the materialized view it does not reflect the changes
that were made to the base table. Also if i query dba_mviews the staleness attribute
for the mview is 'UNUSABLE'. Am i doing something completly wrong here??
*/

ops$tkyte@ORA9IR2> CREATE MATERIALIZED VIEW base_rlup
  2  BUILD IMMEDIATE
  3  REFRESH FAST ON COMMIT
  4  AS
  5  SELECT key1 , SUM(val) , COUNT(val)
  6    FROM base
  7   GROUP BY key1;
 
Materialized view created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
  2          l_data EXPLAINMVARRAYTYPE;
  3  begin
  4          dbms_mview.explain_mview( 'BASE_RLUP', l_data );
  5          open :x for
  6          select capability_name, possible,
  7                 msgno, msgtxt
  8        from table( cast( l_data as EXPLAINMVARRAYTYPE ) )
  9           order by seq;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> column msgtxt format a30 word_wrapped
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> print x;
 
CAPABILITY_NAME                P      MSGNO MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            F          0
REFRESH_COMPLETE               T          0
REFRESH_FAST                   T          0
REWRITE                        F          0
PCT_TABLE                      F       2068 relation is not a partitioned
                                            table
 
REFRESH_FAST_AFTER_INSERT      T          0<b>
REFRESH_FAST_AFTER_ONETAB_DML  F       2142 COUNT(*) is not present in the
                                            select list</b>
 
REFRESH_FAST_AFTER_ANY_DML     F       2161 see the reason why
                                            REFRESH_FAST_AFTER_ONETAB_DML
                                            is disabled
 
REFRESH_FAST_PCT               F       2157 PCT is not possible on any of
                                            the detail tables in the
                                            materialized view
 
REWRITE_FULL_TEXT_MATCH        F       2159 query rewrite is disabled on
                                            the materialized view
 
REWRITE_PARTIAL_TEXT_MATCH     F       2159 query rewrite is disabled on
                                            the materialized view
 
REWRITE_GENERAL                F       2159 query rewrite is disabled on
                                            the materialized view
 
REWRITE_PCT                    F       2158 general rewrite is not
                                            possible and PCT is not
                                            possible on any of the detail
                                            tables
 
 
13 rows selected.
 
<b>you need count(*) in there... add count(*) and it works</b>

 

Steve,
You need to add a count(*) to you materialized view create statement.

CREATE MATERIALIZED VIEW base_rlup
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
key1
, SUM(val) sum_val
, COUNT(val) cnt_val,
COUNT(*) cnt
FROM base
GROUP BY key1;

Well it looks like i found the problem. Appearantly in order for all basic DML to work under fast refresh you need to have a count(exp) for every corresponding aggregate expression as well as a count(*). Initially i was just confused about needing them both, but changig the above mv definition to:

CREATE MATERIALIZED VIEW base_rlup
REFRESH FAST ON COMMIT
AS
SELECT
key1
, SUM(val)
, COUNT(val)
, COUNT(*)
FROM base
GROUP BY key1;

Has fixed the problem. I guess im just gonna need to read the docs more closely in the future. Sorry if this cost you any time, and thanks again for all you help on this site.

a) there was a bug in aggregate on-commit refresh where the cardinality hint was incorrect on one of the recursive queries which could often lead to full scans on the source table during 'fast' refresh. Fixed in 9205+

b) If you've mucked around with DUAL (eg made it an IOT), then fast refreshes can go very very wrong.

c) delete's from tables which underpin agg mat views tyically end up with a top-to-bottom scan on the mat view (because the rows where count(*) now is zero need to be deleted). You may need to index the count column on the mat view.

Bottom line - there's no such thing as a free lunch

hth
connor

Hi Tom

you said earlier in the thread that a complete refresh does
a truncate followed by insert /*+ APPEND */
if the materialized view is part of a refresh group, doesn't it do a normal delete of the whole MV followed by normal insert ?

thanks

Neil

If you have Oracle10g, would you still be using dbms_mview.explain_mview or would you only use dbms_advisor.tune_mview? Can you explain in what situation would you use one over the other? Thanks.

Hi Tom
I have a question on this . I have a materialzed view log of 10g size on 32 G master table . The materialized view on this table was last refereshed on 07 sept 2006 . The table is frequently accessed . Due to some reasons the refreshes were stopped , now i wanted to enable the fast refresh on this particular Mview but this is not getting completed and throwing ora-1555 all the time . my retention was 6 hrs but i tried increasing that also. Now All i need is parameters to fasten the fast refresh .

Thanks
Naveen

Thanks for the Answer Tom..

Yes incremental refresh would be the appropriate one but fast refresh is what the whole world is known to . As i said table size is of 32 G and it is very very frequently accessed table , going for complete refresh may create ORA-01555 again .
the following parameters are always showing 0 on my system
UNXPSTEALCNT
UNXPBLKRELCNY
UNXPBLKREUCNT
EXPSTEALCNT
EXPBLKRELCNT
EXPBLKREUCNT

I am planning for complete refresh on this MV in the lean hours. shall i use parallel option in the dbms_mview package. can i use and any more parameters to increase the speed.
Please suggest.

Just stumbled upon this thread, thanks to the original topic.
Read about tune_mview en decided to try it for my mview.

Well, it appears Oracle not only has a funny idea about name-giving (fast in stead of incremental), but also about doing your work properly.

The result of executing tune_mview with my view is an error.
Well, that happens. So, what did I do wrong? Let's take a look at the error:

.ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further

So Oracle considers it to be an error if I do my work right?

;-)

Maybe I should apply for a job at Oracle. according to this view on what's right and not, I'm qualified ;-)

Tom,

Looking at it objectively, as you do, obviously you are very very right.
Still, you must admit that at the very least it's fun to see the desired situation being reported as "error encountered in Advisor".

Hi Tom,
I am planning to create a materialized view on a complex view.will it be fine performance wise where i require a incremental refresh? Please suggest whether I should continue with the idea or shall I built a view on base tables of view only.

Hi Tom,

I am using the following code to create a materialized view from a view.

CREATE MATERIALIZED VIEW MV_View
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT * FROM V_View@Dblink1;

I am getting the following error:
ORA-12014: table 'V_View' does not contain a primary key constraint.

I tried creating LOG also using the following statement:

CREATE MATERIALIZED VIEW LOG ON V_View@Dblink1 WITH ROWID;

But following error was received:

ORA-00949: illegal reference to remote database

Unable 2 resolve the problem.Please provide your suggestions.

Regards
Sunny

Hi, I have a table with all of the application audit details recorded and it has around 11 years worth of data. I have a requirement to create a materialized view with just current year data with fast refresh. Since I have the sysdate, I'm not able to achieve the fast refresh. Is there any work around for this?

I have a simple Materialized View which processes only about 400k rows and materializes into about 100k rows.

select *
from (select a.*, row_number() over (partition by...) rnum
      from  tab_a a, tab_b b
      where b.key = a.b_key
     ) tvw
where rn = 1
and   exists (select * from tab_c c where c.val = tvw.val)

The MV is not part of a refresh group and it's set to REFRESH COMPLETE ON DEMAND, there are NO MV logs on the base tables.

When I call mview.refresh, it takes 10 minutes to come back. When I try to do a create table as select...using the same query as the MV, it comes back in under 10 seconds.

Any ideas where I should be looking for the big difference? Both would seem to be processing and creating the same number of rows, why would a CTAS be so much quicker?

Initially, I thought it may be because MV is doing a delete, but it should be doing a truncate as you suggested above?

I've identified the problem - it was a BITMAP index on the MV.

Why though?

I understand locking issues with BITMAPs in an OLTP environment, but what else could be competing with a MV refresh?

Thank you.

We're on 10g R2.

But by changing the index definition from BITMAP to standard B-Tree, it goes back down from 10minutes to 16seconds, why?

Also how do you specify atomic refresh => false if the MV was created with a refresh interval of every 5 minutes and not manually refreshed using dbms_mview.refresh?

Tom,
Is it necessary that users should not post any transactions to MV related table when MV fast refresh is running for that table?

What I meant was, if we have a table A and we have created a mv i.e. A_MV based on table A then can a user post any transactions to table A when refresh for A_MV is going on.
If yes, then what will happen to the posted transaction in table A, which got posted after mv refresh started, will that be considered in next refresh?
Also, we are talking about point in time refresh here.

Team,

was reading about Mview from docs.
https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-E087FDD0-B08C-4878-BBA9-DE56A705835E
and it states

You can achieve better fast refresh performance for local materialized views 
if you use a materialized view log that contains a WITH COMMIT SCN clause.

so what kind of performance one can expect with "COMMIT SCN" enabled and without it?

SQL>
SQL> create table t as select * from dba_objects
  2  where object_id is not null;

Table created.

SQL> alter table t add primary key ( object_id );

Table altered.

SQL>
SQL> --create materialized view log on t with commit scn;
SQL> create materialized view log on t;

Materialized view log created.

SQL>
SQL> create materialized view mv
  2  refresh fast as
  3  select owner, object_id, object_name, created
  4  from t
  5  where last_ddl_time is not null;

Materialized view created.

SQL>
SQL> begin
  2   for i in 1 .. 10 loop
  3      delete from t where rownum <= 50; commit;
  4      update t set owner = lower(owner) where rownum <= 50; commit;
  5      declare
  6        c int := 0;
  7        r t%rowtype;
  8      begin
  9       for i in ( select * from t where object_id > 0 ) loop
 10          c := c + 1 ; exit when c > 50;
 11          r := i;
 12          r.object_id := -r.object_id;
 13          insert into t values r;
 14       end loop;
 15      end;
 16      commit;
 17   end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','MV');

PL/SQL procedure successfully completed.

SQL>
SQL> -- exec dbms_monitor.session_trace_enable;
SQL> set timing on
SQL> exec dbms_mview.refresh('mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.93
SQL> -- exec dbms_monitor.session_trace_disable;
SQL>
SQL>
SQL>



SQL>
SQL> create table t as select * from dba_objects
  2  where object_id is not null;

Table created.

Elapsed: 00:00:00.32
SQL> alter table t add primary key ( object_id );

Table altered.

Elapsed: 00:00:00.07
SQL>
SQL> create materialized view log on t with commit scn;

Materialized view log created.

Elapsed: 00:00:00.00
SQL> -- create materialized view log on t;
SQL>
SQL> create materialized view mv
  2  refresh fast as
  3  select owner, object_id, object_name, created
  4  from t
  5  where last_ddl_time is not null;

Materialized view created.

Elapsed: 00:00:00.15
SQL>
SQL> begin
  2   for i in 1 .. 10 loop
  3      delete from t where rownum <= 50; commit;
  4      update t set owner = lower(owner) where rownum <= 50; commit;
  5      declare
  6        c int := 0;
  7        r t%rowtype;
  8      begin
  9       for i in ( select * from t where object_id > 0 ) loop
 10          c := c + 1 ; exit when c > 50;
 11          r := i;
 12          r.object_id := -r.object_id;
 13          insert into t values r;
 14       end loop;
 15      end;
 16      commit;
 17   end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
SQL> exec dbms_stats.gather_table_stats('','MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL>
SQL> -- exec dbms_monitor.session_trace_enable;
SQL> set timing on
SQL> exec dbms_mview.refresh('mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.66
SQL> -- exec dbms_monitor.session_trace_disable;
SQL>
SQL>
SQL>

Thanks for the test case, but can you also please explain why "WITH COMMIT SCN" goes faster,

a) what was the technical reason behind that ?
b) also what was the business use case, that one should consider using this?
c) why don't oracle goes as the default option then?

demo@XEPDB1> create materialized view log on t;

Materialized view log created.

demo@XEPDB1> select commit_scn_based
  2  from user_mview_logs
  3  where master ='T';

COM
---
NO

Greetings!

Database version: 12.1.0.2

While doing test with on-commit fast refresh materialized view we are seeing update record completes in no time but commit is taking more than 6 minutes to complete.

Can you please provide insight on the cause of the slowness and how to resolve this issue. I have provided below the TKPROF report from 10046 trace. Most of the time is spent on the 


1)  I tried with locking all materialized view logs involved in the materialized view tables but no difference in performance.


===
SQL> 
SQL> update PSG.REACHABLE_URLS set REC_DATE_END = systimestamp where src = 'PSG' and url = 'https://www.sprint.com/about/careers/benefits';

1 row updated.

Elapsed: 00:00:00.05
SQL> commit ;

Commit complete.

Elapsed: 00:06:21.49




SQL ID: 3q2ytq8c7669f Plan Hash: 219098488

DELETE /*+ BYPASS_UJVC */ FROM "PSG"."RB_DV_PRG_MAT" "SNA$" 
WHERE
 "SNA$"."URID" IS NULL AND "SNA$"."ERID" IN (SELECT /*+ NO_MERGE ("JV$") */ 
  "MAS$5".ROWID  FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM 
  "PSG"."REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM 
  "PSG"."MLOG$_REACHABLE_URLS" "MAS$"   WHERE "MAS$".XID$$ = :1  AND NOT 
         ("MAS$".DMLTYPE$$ = 'U' AND         
  (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                               
                  "MAS$".change_vector$$) = 0))))  "JV$", 
  "PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)  "MAS$5" WHERE 
  LOWER("JV$"."URL")=LOWER("MAS$5"."src_LINK") AND 
  "JV$"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD'))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          5          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          5          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row src Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  RB_DV_PRG_MAT (cr=3 pr=0 pw=0 time=325 us)
         0          0          0   HASH JOIN RIGHT SEMI (cr=3 pr=0 pw=0 time=324 us cost=3066 size=1408 card=44)
         0          0          0    VIEW  VW_NSO_1 (cr=3 pr=0 pw=0 time=219 us cost=3021 size=7104 card=592)
         0          0          0     HASH JOIN  (cr=3 pr=0 pw=0 time=219 us cost=3021 size=1247344 card=592)
         0          0          0      VIEW  (cr=3 pr=0 pw=0 time=97 us cost=2 size=2015 card=1)
         0          0          0       NESTED LOOPS  (cr=3 pr=0 pw=0 time=97 us cost=2 size=390 card=1)
         2          2          2        TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_REACHABLE_URLS (cr=2 pr=0 pw=0 time=86 us cost=1 size=273 card=1)
         2          2          2         INDEX RANGE SCAN I_MLOG$_REACHABLE_URLS (cr=1 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 11612850)
         0          0          0        TABLE ACCESS BY USER ROWID REACHABLE_URLS (cr=1 pr=0 pw=0 time=7 us cost=1 size=117 card=1)
         0          0          0      TABLE ACCESS STORAGE FULL RB_DV_PRG (cr=0 pr=0 pw=0 time=0 us cost=3019 size=5448056 card=59218)
         0          0          0    MAT_VIEW ACCESS STORAGE FULL RB_DV_PRG_MAT (cr=0 pr=0 pw=0 time=0 us cost=45 size=87600 card=4380)

********************************************************************************

SQL ID: g0tyfhtk93mra Plan Hash: 2113898825

INSERT  /*+ NOAPPEND */ INTO "PSG"."RB_DV_PRG_MAT" SELECT /*+ 
  NO_MERGE("JV$") */ "MAS$5"."ORGANIZATION_ID","MAS$5"."RECORDED_DATE_START",
  "MAS$5"."src",EXTRACT(YEAR FROM "MAS$5"."EFFECTIVE_DATE_START"),
  "MAS$5"."TYPE_CODE","MAS$5"."SUB_TYPE_CODE",NVL("MAS$3"."LOOKUP_DETAIL",
  "MAS$5"."INDICATOR_CODE"),"MAS$5"."COMMENTS","MAS$5"."PCT",
  "MAS$5"."MEDIAN_VALUE","MAS$5"."MEAN_VALUE",NVL("MAS$1"."LOOKUP_DETAIL",
  "MAS$5"."src_SUB_TYPE_CODE"),"JV$"."URL","MAS$5"."src_SECTION",
  "MAS$5".ROWID,"MAS$4".ROWID,"MAS$3".ROWID,"MAS$2".ROWID,"MAS$1".ROWID,
  "JV$"."RID$" FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM 
  "PSG"."REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM 
  "PSG"."MLOG$_REACHABLE_URLS" "MAS$"   WHERE "MAS$".XID$$ = :1  AND NOT 
         ("MAS$".DMLTYPE$$ = 'U' AND         
  (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                               
                  "MAS$".change_vector$$) = 0)))) "JV$", 
  "PSG"."PSG_REF" AS OF SNAPSHOT(:B_SCN)  "MAS$1", 
  "PSG"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN)  "MAS$2", 
  "PSG"."PSG_REF" AS OF SNAPSHOT(:B_SCN)  "MAS$3", 
  "PSG"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN)  "MAS$4", 
  "PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)  "MAS$5" WHERE 
  ("MAS$5"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD') AND 
  "MAS$4"."PSG_TABLE_NAME"='RB_DV_PRG' AND "MAS$4"."PSG_COLUMN_NAME"=
  'INDICATOR_CODE' AND "MAS$3"."LOOKUP_CODE"(+)="MAS$5"."INDICATOR_CODE" AND 
  "MAS$3"."LOOKUP_REFERENCE"(+)="MAS$4"."LOOKUP_REFERENCE" AND 
  "MAS$2"."PSG_TABLE_NAME"='RB_DV_PRG' AND "MAS$2"."PSG_COLUMN_NAME"=
  'src_SUB_TYPE_CODE' AND "MAS$1"."LOOKUP_CODE"(+)=
  "MAS$5"."src_SUB_TYPE_CODE" AND "MAS$1"."LOOKUP_REFERENCE"(+)=
  "MAS$2"."LOOKUP_REFERENCE" AND LOWER("JV$"."URL"(+))=
  LOWER("MAS$5"."src_LINK") AND "JV$"."REC_DATE_END"(+)
  >TO_DATE('50000101','YYYYMMDD')) AND NOT EXISTS ( SELECT 1 FROM 
  "PSG"."RB_DV_PRG_MAT" "SNA2$" WHERE ("SNA2$"."ERID" = "MAS$5".ROWID)
   AND ("SNA2$"."MRID1" = "MAS$4".ROWID OR "MAS$4".ROWID IS NULL ) AND 
  ("SNA2$"."RRID1" = "MAS$3".ROWID OR "MAS$3".ROWID IS NULL ) AND 
  ("SNA2$"."MRID10" = "MAS$2".ROWID OR "MAS$2".ROWID IS NULL ) AND 
  ("SNA2$"."RRID10" = "MAS$1".ROWID OR "MAS$1".ROWID IS NULL ) AND "JV$".RID$ 
  IS NULL) AND NOT EXISTS ( SELECT 1  FROM "PSG"."REACHABLE_URLS" 
  "MAS_INNER$", "PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)  
  "MAS_OUTER$" WHERE "MAS$5".ROWID = "MAS_OUTER$".ROWID AND "JV$".RID$ IS 
  NULL AND LOWER("MAS_INNER$"."URL")=LOWER("MAS_OUTER$"."src_LINK") AND 
  "MAS_INNER$"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD'))



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    488.64     496.80       3095  101298679         38          15
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    488.64     496.80       3095  101298679         38          15

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row src Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  RB_DV_PRG_MAT (cr=101298671 pr=3095 pw=0 time=329252938 us)
        15         15         15   FILTER  (cr=101298670 pr=3095 pw=0 time=460261444 us)
     59174      59174      59174    HASH JOIN RIGHT OUTER (cr=4759 pr=3095 pw=0 time=3942774 us cost=2009 size=25463568 card=37011)
      2125       2125       2125     TABLE ACCESS STORAGE FULL PSG_REF_V24 (cr=23 pr=21 pw=0 time=21699 us cost=21 size=121125 card=2125)
     59174      59174      59174     HASH JOIN RIGHT OUTER (cr=4736 pr=3074 pw=0 time=3786391 us cost=1988 size=23353941 card=37011)
      2125       2125       2125      TABLE ACCESS STORAGE FULL PSG_REF_V24 (cr=23 pr=0 pw=0 time=228 us cost=21 size=121125 card=2125)
     59174      59174      59174      HASH JOIN RIGHT OUTER (cr=4713 pr=3074 pw=0 time=3626103 us cost=1966 size=21244314 card=37011)
         0          0          0       VIEW  (cr=1637 pr=0 pw=0 time=38061 us cost=24 size=117 card=1)
         0          0          0        HASH JOIN SEMI (cr=1637 pr=0 pw=0 time=38059 us cost=72 size=390 card=1)
     62874      62874      62874         TABLE ACCESS STORAGE FULL REACHABLE_URLS (cr=1635 pr=0 pw=0 time=9731 us cost=22 size=4858425 card=41525)
         2          2          2         TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_REACHABLE_URLS (cr=2 pr=0 pw=0 time=146 us cost=1 size=273 card=1)
         2          2          2          INDEX RANGE SCAN I_MLOG$_REACHABLE_URLS (cr=1 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 11612850)
     59174      59174      59174       NESTED LOOPS  (cr=3076 pr=3074 pw=0 time=3388605 us cost=1943 size=16914027 card=37011)
         1          1          1        NESTED LOOPS  (cr=6 pr=4 pw=0 time=13084 us cost=3 size=106 card=1)
         1          1          1         TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=3 pw=0 time=12527 us cost=2 size=53 card=1)
         1          1          1          INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=2 pw=0 time=760 us cost=1 size=0 card=1)(object id 11611101)
         1          1          1         TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=1 pw=0 time=553 us cost=1 size=53 card=1)
         1          1          1          INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=0 pw=0 time=15 us cost=0 size=0 card=1)(object id 11611101)
     59174      59174      59174        TABLE ACCESS BY INDEX ROWID BATCHED RB_DV_PRG (cr=3070 pr=3070 pw=0 time=3358890 us cost=1940 size=12990861 card=37011)
     59174      59174      59174         INDEX RANGE SCAN RB_DV_PRG_IDX5 (cr=159 pr=159 pw=0 time=292941 us cost=99 size=0 card=37011)(object id 11611103)
     59159      59159      59159    FILTER  (cr=101267371 pr=0 pw=0 time=491433495 us)
     59159      59159      59159     MAT_VIEW ACCESS STORAGE FULL FIRST ROWS RB_DV_PRG_MAT (cr=101267371 pr=0 pw=0 time=491344430 us cost=45 size=50 card=1)
         0          0          0    FILTER  (cr=26540 pr=0 pw=0 time=1108042 us)
         0          0          0     NESTED LOOPS  (cr=26540 pr=0 pw=0 time=1108009 us cost=3 size=394 card=2)
        15         15         15      TABLE ACCESS BY USER ROWID RB_DV_PRG (cr=15 pr=0 pw=0 time=173 us cost=1 size=92 card=1)
         0          0          0      TABLE ACCESS BY INDEX ROWID BATCHED REACHABLE_URLS (cr=26525 pr=0 pw=0 time=1107769 us cost=13 size=210 card=2)
    943110     943110     943110       INDEX RANGE SCAN PSG_REACHABLE_IDX5 (cr=2525 pr=0 pw=0 time=128172 us cost=1 size=0 card=201)(object id 11611241)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  gc cr grant 2-way                            1557        0.00          0.23
  cell single block physical read              3075        0.01          1.75
  gc cr multi block request                       3        0.00          0.00
  cell multiblock physical read                   3        0.01          0.01
  gc current multi block request                  1        0.00          0.00
  gcs enter server mode                           6        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00
  row cache lock                                  3        0.00          0.00
********************************************************************************

Hi Connor

>>
Try gathering stats on your mview log just before you do the refresh, and then, if that is a typical size, look at locking those stats.

Alternatively, delete the stats on the log and lock them so that dynamic sampling comes into play.

Let us know how that goes.
>>


This is on-commit fast refresh. I am just updating one row in a small table. and then commit. Commit takes for ever (more than 6 minutes to complete).


Tried both your recommendations. but still commit is taking long time. I am seeing the same internal recursive SQL in 10046 trace shared earlier in my previous update.

Here is the mview definition:

CREATE MATERIALIZED VIEW "PSG"."RB_DV_PRG_NEW_XXX" ("ORGANIZATION_ID", "RECORDED_DATE_START", "SOURCE", "FOR_YEAR", "TYPE_CODE", "SUB_TYPE_CODE", "INDICATOR_CODE", "COMMENTS", "PCT", "MEDIAN_VALUE", "MEAN_VALUE", "SOURCESUBTYPECODE", "SOURCELINK", "SOURCESECTION", "ERID", "MRID1", "RRID1", "MRID10", "RRID10", "URID")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "PSG_DATA"
BUILD IMMEDIATE
USING INDEX
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
AS SELECT BASE.ORGANIZATION_ID,
BASE.RECORDED_DATE_START,
BASE.SOURCE,
EXTRACT (YEAR FROM BASE.EFFECTIVE_DATE_START)
AS FOR_YEAR,
TYPE_CODE,
BASE.SUB_TYPE_CODE,
NVL (REF1.LOOKUP_DETAIL, BASE.INDICATOR_CODE)
AS INDICATOR_CODE,
BASE.COMMENTS,
BASE.PCT,
BASE.MEDIAN_VALUE,
BASE.MEAN_VALUE,
NVL (REF10.LOOKUP_DETAIL, BASE.SOURCE_SUB_TYPE_CODE)
AS SOURCESUBTYPECODE,
REACH.URL
AS SOURCELINK,
BASE.SOURCE_SECTION
AS SOURCESECTION,
BASE.ROWID
AS erid,
MAP1.ROWID
AS mrid1,
REF1.ROWID
AS rrid1,
MAP10.ROWID
AS mrid10,
REF10.ROWID
AS rrid10,
REACH.ROWID
AS urid
FROM PSG.RB_DV_PRG_NEW BASE,
PSG.PSG_MAP_REML_TO_PSG MAP1,
PSG.PSG_REFERENCES REF1,
PSG.PSG_MAP_REML_TO_PSG MAP10,
PSG.PSG_REFERENCES REF10,
PSG.PSG_REACHABLE_URLS reach
WHERE BASE.RECORDED_DATE_END > TO_DATE ('50000101', 'YYYYMMDD')
AND MAP1.PSG_TABLE_NAME = 'RML_BEN_DIV_PROG'
AND MAP1.PSG_COLUMN_NAME = 'INDICATOR_CODE'
AND REF1.LOOKUP_CODE(+) = BASE.INDICATOR_CODE
AND REF1.LOOKUP_REFERENCE(+) = MAP1.LOOKUP_REFERENCE
AND MAP10.PSG_TABLE_NAME = 'RML_BEN_DIV_PROG'
AND MAP10.PSG_COLUMN_NAME = 'SOURCE_SUB_TYPE_CODE'
AND REF10.LOOKUP_CODE(+) = BASE.SOURCE_SUB_TYPE_CODE
AND REF10.LOOKUP_REFERENCE(+) = MAP10.LOOKUP_REFERENCE
AND LOWER (REACH.URL(+)) = LOWER (BASE.SOURCE_LINK)
AND REACH.RECORDED_DATE_END(+) > TO_DATE ('50000101', 'YYYYMMDD');

Hello Sir,

Please suggest me interviewer ask me how to do validation
at everyday on 07 pm to 07 am any DML statement are run for XYZ table then message will display you can't perform any DML operation between 07 pm to 07 am.

please sir guide me.


I used below query but did not work this query
select case when a BETWEEN b and c then 1 else 0 end h,a,b,c
--case when b>=a and c<=a then 1 else 0 end k
from (
select to_date(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') a,
to_date(to_char(to_date(to_char(sysdate,'dd-mm-yyyy')|| ' 07:00:00 pm','dd-mm-yyyy hh:mi:ss pm'),'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') b,
to_date(to_char(to_date(to_char(sysdate+1,'dd-mm-yyyy')||' 07:00:00 Am','dd-mm-yyyy hh:mi:ss Am'),'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') c
from dual
);

after 11:59 pm that query does not work, if it's same day then it will work.

Hi Connor,

I have posted the MVIEW DDL on follow-up to your question - "can we see the definition of the mview please"

Can you provide your insight.

Thanks!

Hi Connor,

>>Can you try the refresh after doing the following:
>> alter session set "_mv_refresh_use_stats" = false;

I did try your recommendation but its still taking 4+ minutes during the commit. 10046 trace shows the same internal recursive SQL with the same plan.

complete refresh of the same mview takes about 3 seconds.

Thanks

MAT_VIEW ACCESS STORAGE FULL FIRST ROWS RB_DV_PRG_MAT (cr=101267371 pr=0 pw=0 time=491344430 us cost=45 size=50 card=1)

and not exists (
  select 1
  from "PSG"."RB_DV_PRG_MAT" "SNA2$"
  where ("SNA2$"."ERID" = "MAS$5".rowid)
    and ("SNA2$"."MRID1" = "MAS$4".rowid
         or "MAS$4".rowid is null)
    and ("SNA2$"."RRID1" = "MAS$3".rowid
         or "MAS$3".rowid is null)
    and ("SNA2$"."MRID10" = "MAS$2".rowid
         or "MAS$2".rowid is null)
    and ("SNA2$"."RRID10" = "MAS$1".rowid
         or "MAS$1".rowid is null)
    and "JV$".rid$ is null
)

Hi Chris,

Thanks!

>>
- Does creating index(es) on the MV columns in the inserts not exist clause help?

and not exists (
select 1
from "PSG"."RB_DV_PRG_MAT" "SNA2$"
where ("SNA2$"."ERID" = "MAS$5".rowid)
and ("SNA2$"."MRID1" = "MAS$4".rowid
or "MAS$4".rowid is null)
and ("SNA2$"."RRID1" = "MAS$3".rowid
or "MAS$3".rowid is null)
and ("SNA2$"."MRID10" = "MAS$2".rowid
or "MAS$2".rowid is null)
and ("SNA2$"."RRID10" = "MAS$1".rowid
or "MAS$1".rowid is null)
and "JV$".rid$ is null
)
>>

as per your suggestion, created indexes on those MV columns. Wow, it really improved the on-commit fast refresh performance. it took about less than a second now with on-commit fast refresh!

Thanks much!

Here is the 10046 trace:

********************************************************************************

SQL ID: 1zfr4z4q2sgz4 Plan Hash: 3275388845

INSERT /*+ NOAPPEND */ INTO "PSG3"."RB_DV_PRG_MAT" SELECT /*+
NO_MERGE("JV$") */ "MAS$5"."ORGANIZATION_ID","MAS$5"."RECORDED_DATE_START",
"MAS$5"."SOURCE",EXTRACT(YEAR FROM "MAS$5"."EFFECTIVE_DATE_START"),
"MAS$5"."TYPE_CODE","MAS$5"."SUB_TYPE_CODE",NVL("MAS$3"."LOOKUP_DETAIL",
"MAS$5"."INDICATOR_CODE"),"MAS$5"."COMMENTS","MAS$5"."PCT",
"MAS$5"."MEDIAN_VALUE","MAS$5"."MEAN_VALUE",NVL("MAS$1"."LOOKUP_DETAIL",
"MAS$5"."SOURCE_SUB_TYPE_CODE"),"JV$"."URL","MAS$5"."SOURCE_SECTION",
"MAS$5".ROWID,"MAS$4".ROWID,"MAS$3".ROWID,"MAS$2".ROWID,"MAS$1".ROWID,
"JV$"."RID$" FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
"PSG3"."PSG_REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"PSG3"."MLOG$_PSG_REACHABLE_URLS" "MAS$" WHERE "MAS$".XID$$ = :1 AND NOT
("MAS$".DMLTYPE$$ = 'U' AND
(sys.dbms_snapshot_utl.vector_compare(:B_CV0,
"MAS$".change_vector$$) = 0)))) "JV$",
"PSG3"."PSG_REFERENCES" AS OF SNAPSHOT(:B_SCN) "MAS$1",
"PSG3"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$2",
"PSG3"."PSG_REFERENCES" AS OF SNAPSHOT(:B_SCN) "MAS$3",
"PSG3"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$4",
"PSG3"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN) "MAS$5" WHERE
("MAS$5"."RECORDED_DATE_END">TO_DATE('50000101','YYYYMMDD') AND
"MAS$4"."PSG_TABLE_NAME"='RML_BEN_DIV_PROG' AND "MAS$4"."PSG_COLUMN_NAME"=
'INDICATOR_CODE' AND "MAS$3"."LOOKUP_CODE"(+)="MAS$5"."INDICATOR_CODE" AND
"MAS$3"."LOOKUP_REFERENCE"(+)="MAS$4"."LOOKUP_REFERENCE" AND
"MAS$2"."PSG_TABLE_NAME"='RML_BEN_DIV_PROG' AND "MAS$2"."PSG_COLUMN_NAME"=
'SOURCE_SUB_TYPE_CODE' AND "MAS$1"."LOOKUP_CODE"(+)=
"MAS$5"."SOURCE_SUB_TYPE_CODE" AND "MAS$1"."LOOKUP_REFERENCE"(+)=
"MAS$2"."LOOKUP_REFERENCE" AND LOWER("JV$"."URL"(+))=
LOWER("MAS$5"."SOURCE_LINK") AND "JV$"."RECORDED_DATE_END"(+)
>TO_DATE('50000101','YYYYMMDD')) AND NOT EXISTS ( SELECT 1 FROM
"PSG3"."RB_DV_PRG_MAT" "SNA2$" WHERE ("SNA2$"."ERID" =
"MAS$5".ROWID) AND ("SNA2$"."MRID1" = "MAS$4".ROWID OR "MAS$4".ROWID IS
NULL ) AND ("SNA2$"."RRID1" = "MAS$3".ROWID OR "MAS$3".ROWID IS NULL ) AND
("SNA2$"."MRID10" = "MAS$2".ROWID OR "MAS$2".ROWID IS NULL ) AND
("SNA2$"."RRID10" = "MAS$1".ROWID OR "MAS$1".ROWID IS NULL ) AND "JV$".RID$
IS NULL) AND NOT EXISTS ( SELECT 1 FROM "PSG3"."PSG_REACHABLE_URLS"
"MAS_INNER$", "PSG3"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)
"MAS_OUTER$" WHERE "MAS$5".ROWID = "MAS_OUTER$".ROWID AND "JV$".RID$ IS
NULL AND LOWER("MAS_INNER$"."URL")=LOWER("MAS_OUTER$"."SOURCE_LINK") AND
"MAS_INNER$"."RECORDED_DATE_END">TO_DATE('50000101','YYYYMMDD'))



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.50 0.61 3127 122583 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.50 0.62 3127 122583 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL RB_DV_PRG_MAT (cr=122583 pr=3127 pw=0 time=604002 us)
0 0 0 FILTER (cr=122583 pr=3127 pw=0 time=603992 us)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2980 pr=2945 pw=0 time=288191 us cost=3028 size=40530765 card=59169)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REFERENCES_V24 (cr=23 pr=21 pw=0 time=4172 us cost=21 size=121125 card=2125)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2957 pr=2924 pw=0 time=230268 us cost=3007 size=37158132 card=59169)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REFERENCES_V24 (cr=23 pr=0 pw=0 time=205 us cost=21 size=121125 card=2125)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2934 pr=2924 pw=0 time=170934 us cost=2986 size=33785499 card=59169)
0 0 0 VIEW (cr=3 pr=0 pw=0 time=153 us cost=4 size=117 card=1)
0 0 0 NESTED LOOPS (cr=3 pr=0 pw=0 time=152 us cost=4 size=151 card=1)
1 1 1 SORT UNIQUE (cr=2 pr=0 pw=0 time=144 us cost=2 size=34 card=1)
2 2 2 TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_PSG_REACHABLE_URLS (cr=2 pr=0 pw=0 time=126 us cost=2 size=34 card=1)
2 2 2 INDEX RANGE SCAN I_MLOG$_PSG_REACHABLE_URLS (cr=1 pr=0 pw=0 time=14 us cost=1 size=0 card=2)(object id 11625453)
0 0 0 TABLE ACCESS BY USER ROWID PSG_REACHABLE_URLS (cr=1 pr=0 pw=0 time=6 us cost=1 size=117 card=1)
59169 59169 59169 NESTED LOOPS (cr=2931 pr=2924 pw=0 time=110154 us cost=2981 size=26862726 card=59169)
1 1 1 NESTED LOOPS (cr=6 pr=4 pw=0 time=4628 us cost=3 size=106 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=3 pw=0 time=4143 us cost=2 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=2 pw=0 time=3573 us cost=1 size=0 card=1)(object id 11611101)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=1 pw=0 time=482 us cost=1 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=0 pw=0 time=9 us cost=0 size=0 card=1)(object id 11611101)
59169 59169 59169 TABLE ACCESS STORAGE FULL RB_DV_PRG (cr=2925 pr=2920 pw=0 time=99007 us cost=2978 size=20590812 card=59169)
59169 59169 59169 FILTER (cr=119603 pr=182 pw=0 time=263903 us)
59169 59169 59169 MAT_VIEW ACCESS BY INDEX ROWID BATCHED RB_DV_PRG_MAT (cr=119603 pr=182 pw=0 time=246550 us cost=2 size=50 card=1)
59169 59169 59169 INDEX RANGE SCAN RB_DV_PRG_MAT_IDX1 (cr=60434 pr=182 pw=0 time=162225 us cost=1 size=0 card=1)(object id 11927917)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=197 card=1)
0 0 0 TABLE ACCESS BY USER ROWID RB_DV_PRG (cr=0 pr=0 pw=0 time=0 us cost=1 size=92 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED PSG_REACHABLE_URLS (cr=0 pr=0 pw=0 time=0 us cost=7 size=105 card=1)
0 0 0 INDEX RANGE SCAN PSG_REACHABLE_IDX5 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=100)(object id 11611241)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 63 0.00 0.00
cell single block physical read 188 0.00 0.07
gc cr multi block request 23 0.00 0.00
cell multiblock physical read 42 0.00 0.04
row cache lock 3 0.00 0.00
********************************************************************************