{"id":14094,"date":"2020-05-10T18:07:01","date_gmt":"2020-05-10T16:07:01","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-materialized-view-refresh-fast-or-complete\/"},"modified":"2020-05-10T18:07:01","modified_gmt":"2020-05-10T16:07:01","slug":"oracle-materialized-view-refresh-fast-or-complete","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-materialized-view-refresh-fast-or-complete\/","title":{"rendered":"Oracle Materialized View Refresh : Fast or Complete ?"},"content":{"rendered":"

In contrary of views, materialized views avoid executing the SQL query for every access by storing the result set of the query.<\/p>\n

When a master table is modified, the related materialized view becomes stale and a refresh is necessary to have the materialized view up to date.<\/p>\n

I will not show you the materialized view concepts, the Oracle Datawarehouse Guide<\/a> is perfect for that.<\/p>\n

I will show you, from a user real case,\u00a0 all steps you have to follow to investigate and tune your materialized view refresh.<\/p>\n

And, as very often in performance and tuning task, most of the performance issue comes from the way to write and design your SQL (here the SQL statement loading the materialized view).<\/p>\n

 <\/p>\n

First of all, I’m saying that spending almost 50 mins (20% of my DWH Load) to refresh materialized view is too much :<\/p>\n

\"\"<\/a><\/p>\n

The first step is to check which materialized view has the highest refresh time :<\/p>\n

SELECT * \nFROM (\n      SELECT OWNER,\n             MVIEW_NAME,\n             CONTAINER_NAME,\n             REFRESH_MODE,\n             REFRESH_METHOD,\n             LAST_REFRESH_TYPE,\n             STALENESS,\n             ROUND((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS\n       FROM ALL_MVIEWS\n       WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')\n      )\nORDER BY REFRESH_TIME_MINS DESC;\n\nOWNER   MVIEW_NAME                      CONTAINER_NAME                  REFRESH_MODE  REFRESH_METHOD LAST_REFRESH_TYPE STALENESS      REFRESH_TIME_MINS\n------- ------------------------------- ------------------------------- ------------- -------------- ----------------- --------------------------------\nSCORE   MV$SCORE_ST_SI_MESSAGE_HISTORY   MV$SCORE_ST_SI_MESSAGE_HISTORY DEMAND        FAST           FAST              FRESH          32.52\nSCORE   MV$SCORE_ST_SI_MESSAGE           MV$SCORE_ST_SI_MESSAGE         DEMAND        FAST           FAST              FRESH          16.38\nSCORE   MV$SC1_MYHIST2_STOP              MV$SC1_MYHIST2_STOP            DEMAND        FORCE          COMPLETE          NEEDS_COMPILE  .03\nSCORE   MV$SC1_MYHIST2_START             MV$SC1_MYHIST2_START           DEMAND        FORCE          COMPLETE          NEEDS_COMPILE  .03\nSCORE   MV$SC1_RWQ_FG_TOPO               MV$SC1_RWQ_FG_TOPO             DEMAND        FORCE          COMPLETE          NEEDS_COMPILE  .02\n\n<\/pre>\n
All the refresh time comes from the mview\u00a0 : MV$SCORE_ST_SI_MESSAGE_HISTORY and MV$SCORE_ST_SI_MESSAGE.<\/p>\n
Thanks to columns ALL_MVIEWS.LAST_REFRESH_DATE and ALL_MVIEWS.LAST_REFRESH_END_TIME, we got the sql statements and the executions plans related to the refresh operation :<\/p>\n
The first operation is a “Delete<\/em>“:<\/p>\n
\"\"<\/a><\/p>\n
The second operation is an “Insert<\/em>“:<\/p>\n
\"\"<\/a><\/p>\n
Let’s extract the PL\/SQL procedure doing the refresh used by the ETL tool :<\/p>\n
dbms_mview.refresh('SCORE.'||l_mview||'','?',atomic_refresh=>FALSE);\n--'?' = Force : If possible, a fast refresh is attempted, otherwise a complete refresh.\n<\/pre>\n
Being given that, here all questions which come to me :<\/p>\n
    \n
  1. My materialized view can be fast-refreshed, so why it takes more than 48 mins to refresh ?<\/li>\n
  2. With atomic_refresh set to false, oracle normally optimize refresh by using parallel DML and truncate DDL, so why a “Delete<\/em>” operation is done instead a “Truncate<\/em>” more faster ?<\/li>\n<\/ol>\n
    To answer to the first point, to be sure that my materialized view can be fast refresh, we can also use explain_mview<\/em> procedure and check the capability_name called “REFRESH_FAST”:<\/p>\n
    SQL> truncate table mv_capabilities_table;\n\nTable truncated.\n\nSQL> exec dbms_mview.explain_mview('MV$SCORE_ST_SI_MESSAGE_HISTORY');\n\nPL\/SQL procedure successfully completed.\n\nSQL> select capability_name,possible,related_text,msgtxt from mv_capabilities_table;\n\nCAPABILITY_NAME                POSSIBLE             RELATED_TEXT            MSGTXT\n------------------------------ -------------------- ----------------------- ----------------------------------------------------------------------------\nPCT                            N\nREFRESH_COMPLETE               Y\nREFRESH_FAST                   Y\nREWRITE                        Y\nPCT_TABLE                      N                    ST_SI_MESSAGE_HISTORY_H relation is not a partitioned table\nPCT_TABLE                      N                    ST_SI_MESSAGE_HISTORY_V relation is not a partitioned table\nPCT_TABLE                      N                    DWH_CODE                relation is not a partitioned table\nREFRESH_FAST_AFTER_INSERT      Y\nREFRESH_FAST_AFTER_ONETAB_DML  Y\nREFRESH_FAST_AFTER_ANY_DML     Y\nREFRESH_FAST_PCT               N\t\t\t\t\t\t\t\t\t\t\tPCT is not possible on any of the detail tables in the materialized view\nREWRITE_FULL_TEXT_MATCH        Y\nREWRITE_PARTIAL_TEXT_MATCH     Y\nREWRITE_GENERAL                Y\nREWRITE_PCT                    N\t\t\t\t\t\t\t\t\t\t\tgeneral rewrite is not possible or PCT is not possible on any of the detail tables\nPCT_TABLE_REWRITE              N                    ST_SI_MESSAGE_HISTORY_H relation is not a partitioned table\nPCT_TABLE_REWRITE              N                    ST_SI_MESSAGE_HISTORY_V relation is not a partitioned table\nPCT_TABLE_REWRITE              N                    DWH_CODE\t\t\t\trelation is not a partitioned table\n\n18 rows selected.<\/pre>\n
    Let’s try to force a complete refresh with atomic_refresh<\/em> set to FALSE in order to check if the “Delete<\/em>” operation is replaced by a “Truncate<\/em>” operation:<\/p>\n
    Now we have a “Truncate<\/em>“:<\/p>\n
    --c = complete refresh\n dbms_mview.refresh('SCORE.'||l_mview||'','C',atomic_refresh=>FALSE);\n<\/pre>\n
    \"\"<\/a><\/p>\n
    Plus an “Insert<\/em>” :<\/p>\n
    \"\"<\/a><\/p>\n
    Let’s check now the refresh time :<\/p>\n
    SELECT * \nFROM ( SELECT OWNER, \n\t\t\t  MVIEW_NAME, \n\t\t\t  CONTAINER_NAME, \n\t\t\t  REFRESH_MODE, \n\t\t\t  LAST_REFRESH_TYPE, \n\t\t\t  STALENESS, \n\t\t\t  round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS \n\t   FROM ALL_MVIEWS \n\t   WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')\n\t ) \nORDER BY REFRESH_TIME_MINS DESC;\n\nOWNER   MVIEW_NAME                       CONTAINER_NAME                   REFRESH_MODE LAST_REFRESH_TYPE STALENESS           REFRESH_TIME_MINS\n------- -------------------------------- -------------------------------- ------------ ----------------- -------------------------------------\nSCORE   MV$SCORE_ST_SI_MESSAGE           MV$SCORE_ST_SI_MESSAGE           FAST         COMPLETE \t FRESH                            6.75\nSCORE   MV$SCORE_ST_SI_MESSAGE_HISTORY   MV$SCORE_ST_SI_MESSAGE_HISTORY   FAST         COMPLETE \t FRESH                               1\n<\/pre>\n
     <\/p>\n
    Conclusion (for my environment) :<\/p>\n