The time from when the materialized view became stale query and the execution plan

stale_since_query.sql

SELECT
    (
        SELECT
            MIN(time_dp)
        FROM
            sys.smon_scn_time
        WHERE
            ( scn_wrp * 4294967296 + scn_bas ) > (
                SELECT
                    MIN(t.spare3)
                FROM
                    tab$        t,
                    dependency$ d
                WHERE
                        t.obj# = d.p_obj#
                    AND w.obj# = d.d_obj#
                    AND t.spare3 > w.lastrefreshscn
            )
    ) AS stale_since
FROM
    sys.user$                  u,
    sys.sum$                   w,
    sys."_CURRENT_EDITION_OBJ" o,
    sys.snap$                  s,
    sys.tab$                   t,
    sys.obj$                   co
WHERE
        w.containernam (+) = s.vname
    AND w.containerobj# = co.obj# (+)
    AND co.obj# = t.obj# (+)
    AND o.obj# (+) = w.obj#
    AND o.owner# = u.user# (+)
    AND ( ( u.name = s.sowner )
          OR ( u.name IS NULL ) )
    AND s.instsite = 0
    AND NOT ( bitand(s.flag, 268435456) > 0 /* MV with user-defined types */
              AND bitand(s.objflag, 32) > 0 )         /* secondary MV */
    AND NOT ( bitand(s.flag2, 33554432) > 0 )    /* secondary CUBE MV */
    AND NOT ( bitand(s.flag3, 512) > 0 )                   /* zonemap */;
    
Execution Plan
----------------------------------------------------------
Plan hash value: 800194146

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                      |     1 |   112 |   118   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                             |                      |     1 |    16 |            |          |
|*  2 |   FILTER                                    |                      |       |       |            |          |
|   3 |    TABLE ACCESS FULL                        | SMON_SCN_TIME        |  1932 | 30912 |   102   (0)| 00:00:01 |
|   4 |    SORT AGGREGATE                           |                      |     1 |    28 |            |          |
|   5 |     NESTED LOOPS                            |                      |     4 |   112 |     5   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED    | DEPENDENCY$          |     4 |    40 |     4   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                      | I_DEPENDENCY1        |     4 |       |     3   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS CLUSTER                   | TAB$                 |     1 |    18 |     1   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN                     | I_OBJ#               |     1 |       |     0   (0)| 00:00:01 |
|  10 |  NESTED LOOPS OUTER                         |                      |     1 |   112 |    11   (0)| 00:00:01 |
|  11 |   NESTED LOOPS OUTER                        |                      |     1 |   107 |    10   (0)| 00:00:01 |
|* 12 |    FILTER                                   |                      |       |       |            |          |
|  13 |     NESTED LOOPS OUTER                      |                      |     1 |   102 |     9   (0)| 00:00:01 |
|  14 |      NESTED LOOPS OUTER                     |                      |     1 |    85 |     8   (0)| 00:00:01 |
|* 15 |       HASH JOIN OUTER                       |                      |     1 |    70 |     4   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS FULL                    | SNAP$                |     1 |    38 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL                    | SUM$                 |     3 |    96 |     2   (0)| 00:00:01 |
|  18 |       VIEW PUSHED PREDICATE                 | _CURRENT_EDITION_OBJ |     1 |    15 |     4   (0)| 00:00:01 |
|* 19 |        FILTER                               |                      |       |       |            |          |
|  20 |         NESTED LOOPS                        |                      |     1 |    46 |     4   (0)| 00:00:01 |
|  21 |          TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$                 |     1 |    21 |     3   (0)| 00:00:01 |
|* 22 |           INDEX RANGE SCAN                  | I_OBJ1               |     1 |       |     2   (0)| 00:00:01 |
|* 23 |          INDEX RANGE SCAN                   | I_USER2              |     1 |    25 |     1   (0)| 00:00:01 |
|* 24 |         TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |
|* 25 |          INDEX RANGE SCAN                   | I_USER_EDITIONING    |     2 |       |     1   (0)| 00:00:01 |
|* 26 |         TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |
|* 27 |          INDEX RANGE SCAN                   | I_USER_EDITIONING    |     2 |       |     1   (0)| 00:00:01 |
|  28 |         NESTED LOOPS SEMI                   |                      |     1 |    29 |     2   (0)| 00:00:01 |
|* 29 |          INDEX SKIP SCAN                    | I_USER2              |     1 |    20 |     1   (0)| 00:00:01 |
|* 30 |          INDEX RANGE SCAN                   | I_OBJ4               |     1 |     9 |     1   (0)| 00:00:01 |
|  31 |      TABLE ACCESS CLUSTER                   | USER$                |     1 |    17 |     1   (0)| 00:00:01 |
|* 32 |       INDEX UNIQUE SCAN                     | I_USER#              |     1 |       |     0   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                         | I_OBJ1               |     1 |     5 |     1   (0)| 00:00:01 |
|  34 |   TABLE ACCESS CLUSTER                      | TAB$                 |     1 |     5 |     1   (0)| 00:00:01 |
|* 35 |    INDEX UNIQUE SCAN                        | I_OBJ#               |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------