09 February, 2020

Basic Replication -- 13 : Some Interesting SYS tables

I found an interesting SQL in the AWR report from my previous blog post.

What do you think this SQL statement does ?

DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1


Here are some interesting objects :
{note : They don't exist in the 11.2.0.4 database that I have ; they do exist in 12.2.0.1, but this test is in 19.3}

SQL> l
  1  select object_name, object_type
  2  from dba_objects
  3  where owner = 'SYS'
  4  and object_name like 'MVREF$%'
  5* order by 2,1
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MVREF$_STATS_SEQ               SEQUENCE
MVREF$_CHANGE_STATS            TABLE
MVREF$_RUN_STATS               TABLE
MVREF$_STATS                   TABLE
MVREF$_STATS_PARAMS            TABLE
MVREF$_STATS_SYS_DEFAULTS      TABLE
MVREF$_STMT_STATS              TABLE

7 rows selected.

SQL>


Right now, the SYS.MVREF$_STMT_STATS table appears to be empty.
SQL> desc SYS.MVREF$_STMT_STATS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MV_OBJ#                                   NOT NULL NUMBER
 REFRESH_ID                                NOT NULL NUMBER
 STEP                                      NOT NULL NUMBER
 SQLID                                     NOT NULL VARCHAR2(14)
 STMT                                      NOT NULL CLOB
 EXECUTION_TIME                            NOT NULL NUMBER
 EXECUTION_PLAN                                     SYS.XMLTYPE STORAGE BINARY

SQL>


It would be interesting to know how Oracle is using this and the other MVREF$% tables.
SYS.MVREF$_CHANGE_STATS obviously captures DML operations

This SYS.MVREF$_RUN_STATS captures the last refresh operation (*does it only capture the last operation ?*) And what does SYS.MVREF$_STATS capture :

SQL> l
  1  select *
  2  from SYS.MVREF$_RUN_STATS
  3* where MVIEWS='"HEMANT"."MV_1"'
SQL> /

RUN_OWNER_USER# REFRESH_ID NUM_MVS_TOTAL NUM_MVS_CURRENT MVIEWS             BASE_TABLES  METHOD ROLLBACK P R PURGE_OPTION
--------------- ---------- ------------- --------------- ------------------ ------------ ------ -------- - - ------------
PARALLELISM  HEAP_SIZE A N O NUMBER_OF_FAILURES START_TIME                 END_TIME                   ELAPSED_TIME LOG_SETUP_TIME
----------- ---------- - - - ------------------ -------------------------- -------------------------- ------------ --------------
LOG_PURGE_TIME C    TXNFLAG ON_COMMIT_FLAG
-------------- - ---------- --------------
            106        245             1               1 "HEMANT"."MV_1"                                 Y N            1
          0          0 Y N N                  0 09-FEB-20 09.55.33.000000  09-FEB-20 09.55.49.000000            16              1
                                                PM                         PM
             9 Y          0              0


SQL>
SQL> l
  1  select mviews, count(*) from sys.mvref$_run_Stats group by mviews
  2* order by 1
SQL> /

MVIEWS                                       COUNT(*)
------------------------------------------ ----------
"HEMANT"."MV_1"                                     1
"HEMANT"."MV_2"                                     8
"HEMANT"."MV_DEPT", "HEMANT"."MV_EMP"               1
"HEMANT"."MV_FAST_NOT_POSSIBLE"                     1
"HEMANT"."MV_OF_SOURCE"                             1
"HEMANT"."NEW_MV"                                   2
"HEMANT"."NEW_MV_2_1"                               1
"HEMANT"."NEW_MV_2_2"                               2
"HR"."HR_MV_ON_COMMIT"                              1
"HR"."MY_LARGE_REPLICA"                             1

10 rows selected.

SQL>
SQL> desc sys.mvref$_run_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 RUN_OWNER_USER#                                                          NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 NUM_MVS_TOTAL                                                            NOT NULL NUMBER
 NUM_MVS_CURRENT                                                          NOT NULL NUMBER
 MVIEWS                                                                            VARCHAR2(4000)
 BASE_TABLES                                                                       VARCHAR2(4000)
 METHOD                                                                            VARCHAR2(4000)
 ROLLBACK_SEG                                                                      VARCHAR2(4000)
 PUSH_DEFERRED_RPC                                                                 CHAR(1)
 REFRESH_AFTER_ERRORS                                                              CHAR(1)
 PURGE_OPTION                                                                      NUMBER
 PARALLELISM                                                                       NUMBER
 HEAP_SIZE                                                                         NUMBER
 ATOMIC_REFRESH                                                                    CHAR(1)
 NESTED                                                                            CHAR(1)
 OUT_OF_PLACE                                                                      CHAR(1)
 NUMBER_OF_FAILURES                                                                NUMBER
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 COMPLETE_STATS_AVAILABLE                                                          CHAR(1)
 TXNFLAG                                                                           NUMBER
 ON_COMMIT_FLAG                                                                    NUMBER

SQL> desc sys.mvref$_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OBJ#                                                                  NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 ATOMIC_REFRESH                                                           NOT NULL CHAR(1)
 REFRESH_METHOD                                                                    VARCHAR2(30)
 REFRESH_OPTIMIZATIONS                                                             VARCHAR2(4000)
 ADDITIONAL_EXECUTIONS                                                             VARCHAR2(4000)
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 INITIAL_NUM_ROWS                                                                  NUMBER
 FINAL_NUM_ROWS                                                                    NUMBER
 NUM_STEPS                                                                         NUMBER
 REFMET                                                                            NUMBER
 REFFLG                                                                            NUMBER

SQL>
SQL> select mv_obj#, count(*)
  2  from sys.mvref$_stats
  3  group by mv_obj#
  4  /

   MV_OBJ#   COUNT(*)
---------- ----------
     73223          1
     73170          1
     73065          1
     73244          1
     73079          8
     73094          1
     73197          2
     73113          2
     73188          1
     73167          1
     73110          1

11 rows selected.

SQL>
SQL> desc sys.mvref$_stats_params
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OWNER                                                                 NOT NULL VARCHAR2(128)
 MV_NAME                                                                  NOT NULL VARCHAR2(128)
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select count(*)
  2  from sys.mvref$_stats_params;

  COUNT(*)
----------
         0

SQL> desc sys.mvref$_stats_sys_defaults
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select * from sys.mvref$_stats_sys_defaults
  2  /

COLLECTION_LEVEL RETENTION_PERIOD
---------------- ----------------
               1               31

SQL>



Oracle has been introducing some more "internal" tables to trace MView Refresh operations.


No comments: