Exploring the refreshing mechanism of materialized views

View | refresh
Exploring the refreshing mechanism of materialized views



Author:kamus

Mail:kamus@itpub.net

DATE:2004 year October



Today to build a historical query server for customers, with oracle8i snapshot implementation, that is, the 9i materialized view.

I did a bit of trace and looked at how Oracle was working backstage at the time of the refresh.





Pre-preparation, using the Dbms_support package, this package is not installed by default, you need to run the following command to install manually.

Sql>conn/as SYSDBA

Sql>@?\rdbms\admin\dbmssupp.sql

Sql>grant execute on Dbms_support to Kamus;

Sql>create public synonym dbms_support for dbms_support;



Look at the quick refresh, the test table is T1, the snapshot log is created, the view used for refreshing is mv_t1, the user is Kamus

Execute Trace:

Sql>conn Kamus

Sql>exec Dbms_support.start_trace (waits=>true,binds=>true);

Sql>exec Dbms_mview.refresh (List => ' mv_t1 ');

Sql>exec Dbms_support.stop_trace;



Then tkprof the report that generated the trace results, which is just an excerpt from the section below.

Performing a dbms_mview.refresh,oracle background will execute 13 user SQL and 92 internal SQL, which is a tedious task.



1. Start refreshing

BEGIN Dbms_mview.refresh (List => ' mv_t1 '); End;



2. Check the snap$ table to verify that the current user has a view that needs to be refreshed



3. Update the record in the dbms_lock_allocated data dictionary, setting the Expiration time

UPDATE dbms_lock_allocated SET expiration = Sysdate + (: b1/86400)

WHERE

ROWID =: B2



4. Check the Dblink and some advanced queue data dictionaries that might be used

5. Check table for related constraints



6. Check the values of several initialization parameters, including _enable_refresh_schedule,_delay_index_maintain,compatible



7. Update all records in Mlog that are not marked for scheduled refresh to immediate refresh

Update "Kamus". Mlog$_t1 "Set snaptime$$ =: 1

where

snaptime$$ > To_date (' 2100-01-01:00:00:00 ', ' yyyy-mm-dd:hh24:mi:ss ')



8. Recompile mv_t1 materialized view

ALTER SUMMARY "Kamus". Mv_t1 "COMPILE

This step is more suspicious, SQL is not alter summary find a command, if it is compiled, then may lock objects, it is possible to generate library cache lock



9. This is an interesting step to check for the SQL you want to execute

SELECT operation#, cols, Sql_txt, Tabnum, Fcmaskvec, Ejmaskvec, Setnum

From

sys.snap_refop$ WHERE (operation# >= 0 and operation# <= 6) OR operation#

In (a)) and Sowner =: 1 and VName =: 2 and Instsite =: 3 Order

by Tabnum, Setnum, operation#

For an MV refresh it will be used in the table where all the SQL exists.

If it is fast refresh, then for Query Mlog table, query base table data, INSERT, UPDATE, delete materialized view have a separate SQL.

The common meanings of operation# field values are as follows:

0: Query Mlog table

1: For the delete operation of the materialized view

2: Query the latest data from the base table

3: For the materialized View update operation

4: Insert operation for materialized view

If it is a complete refresh, then only one record is based on the base table's full table insert operation, operation# is 7.

The execution plan shown here is a full table scan for snap_refop$, which has a significant impact on performance if there is a large number of materialized views that need to be refresh.



10. Get the record primary key that needs to be updated

Select DISTINCT log$. " Idate "

From

(Select mlog$. " Idate "from" Kamus "." Mlog$_t1 "mlog$ WHERE" snaptime$$ ">: 1

and ("dmltype$$"!= ' I ')) log$ WHERE (log$.) Idate ") Not in (select

mas_tab$. " Idate "from" T1 "mas_tab$" WHERE log$. " Idate "= mas_tab$." Idate ")

Note that distinct is used here, which means we can guess that if you make multiple changes to the same record before a refresh, the refresh operation only needs to be done once, or you can get the latest value for that record in the base table.

The Idate field is the primary key in my test table.

"dmltype$$"!= ' I ' means an operation that is not an insert.

The execution plan here shows a full table scan of the Mlog table, and if there is a large number of updates, it is undoubtedly a step towards performance.



11. Gets the latest values for all fields in the base table that are currently required to be refreshed

Select current$. " Idate ", current$." C

From

(select "T1".) Idate "Idate", "T1". C "" C "from" T1 "" T1 ") current$, (SELECT

DISTINCT mlog$. " Idate "from" Kamus "." Mlog$_t1 "mlog$ WHERE" snaptime$$ ">

: 1 and ("dmltype$$"!= ' D ') log$ WHERE current$. " Idate "= log$." Idate "

This step indicates that only the record primary key involved in the modification operation is stored in the Mlog, and other field values are still queried in the base table.

The execution plan here shows another full table scan for the Mlog table.



12. Update the materialized view with the latest value obtained

UPDATE "Kamus". Mv_t1 "SET" Idate =: 1, "C" =: 2

WHERE

"Idate" =: 1

This step is still strange, because my test only inserts, and no update operation, whether Oracle in the refresh, regardless of the presence of update operation, will be a routine update view data? However, the update here uses the primary key in the materialized view, which should be fast.



13. Inserts the latest value obtained into the materialized view

INSERT into "Kamus". " Mv_t1 "(" Idate "," C ")

VALUES

(: 1,:2)

This step to really want to achieve the purpose, hehe.



14. Update a batch of data dictionaries indicating that the refresh is complete



15. Delete records that have been refreshed in the Mlog table

Delete from "Kamus". " Mlog$_t1 "

where

snaptime$$ <=: 1

This step is more resource-intensive, use Delete, generate redo and undo, can not reduce the Mlog table HWM flag, but also a full table scan, if often a large number of updates occur, it is best to regularly make Mlog table truncate action, Otherwise, this step may become slower.



At this point, the quick refresh of a materialized view is completely over.



Let's continue to look at the background mechanism for full refresh.

1-8 steps are basically the same as quick refreshes.

9. Check the SQL to execute

SELECT operation#, cols, Sql_txt

From

sys.snap_refop$ WHERE operation# = 7 and Sowner =: 1 and VName =: 2 and

Instsite =: 3

You can see the SQL that goes directly to operation# = 7, which is the SQL that needs to be used for a full refresh.



10. Check the constraints involved in full refreshes, indexes, triggers



11. Delete existing data in a materialized view

Delete from "Kamus". " mv_t "

This step surprised me, remember the document said it should be truncate operation, but here is the delete? In this case, the cost of a full refresh is really great.



12. Insert all data in a base table

INSERT/*+ Bypass_recursive_check * * "Kamus". mv_t "(" X ") Select" T "." X

From "T" "T"

Here use the hint/*+ bypass_recursive_check * *, is not in the actual application can improve the efficiency of insert?



13. Update a batch of data dictionaries indicating that the refresh is complete



14. If the Refresh Log Mlog table is created on the base table, then Oracle will delete the data in the Mlog table at once, regardless of whether the refresh is completely refreshed. If Mlog has not been created, this step will be omitted. So if you decide to use a full refresh, then do not create a refresh log on the base table to save unnecessary resource consumption.