Explore the refresh mechanism of the physicalized view
Author: kamus
Mail: kamus@itpub.net
Date: October 2004
Today, the history query server is set up, and the Oracle8i's Snapshot is implemented, that is, the entity view of 9i.
I made TRACE in hand, I saw how the Oracle background works.
Pre-preparatory, use the dbms_support package, this package is un installed, you need to run the following command to manually install.
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;
First look at the quick refresh, the test form is T1, create a snapshot log, the view used to refresh 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 generates reports of trace results, the following is only part of it.
Execute a dbms_mview.refresh, the Oracle background will perform 13 USER SQL and 92 INTERNAL SQL, which is really a complicated work.
1. Start refresh
Begin dbms_mview.refresh (List => 'MV_T1'); END;
2. Check the SNAP $ table, confirm that the current user needs to refresh the view
3. Update records in DBMS_LOCK_ALLOCATED data dictionary, set expiration time
Update dbms_lock_allocated set expression = sysdate (: b1 / 86400)
WHERE
RowId =: b2
4. Check the DBLINK and some advanced queues that may be used
5. Check the relevant constraints
6. Check the values of several initialization parameters, including _Nable_refresh_schedule, _delay_index_maintain, compatible
7. Refresh all of Mlog all the records refreshed on the timed refresh to immediately 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. Re-compiling MV_T1 physicalized view
ALTER SUMMARY "Kamus". "MV_T1" Compile
This step is more suspicious. In SQL, there is no ALTER SUMMARY to find a command. If it is compiled, then it may lock the object, it is possible to generate library cache lock.
9. Check the SQL text to be executed, this step is more interesting
Select Operation #, COLS, SQL_TXT, TABNUM, FCMASKVEC, EJMASKVEC, SETNUM
From
Sys.snap_refop $ Where
By Tabnum, SetNum, Operation #
For a MV refresh, you will use this table in SQL.
If it is FAST refresh, there is a SQL for the query mlog table, the query base table data, INSERT, UPDATE, and DELETE entity view.
The common meaning of the Operation # field value is as follows:
0: Query the mlog table
1: DELETE operation for physicalized view
2: Query the latest data of the base table
3: UPDATE operations for physicalized views
4: INSERT operations for physicalized views
If it is a Complete refresh, only one record is based on the full table INSERT operation of the base Table, Operation # is 7.
The execution plan here is displayed for full table scans for SNAP_REFOP $. If there is a large number of realized views that need Refresh in the system, it is undoubtedly affecting performance.
10. Number of records that need 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 this Distinct is used here, that is, we can speculate that if you have made multiple modifications for the same record before one refresh, then the refresh operation only needs to be made, that is, the latest value of the record in the base table can be obtained. .
The iDate field is the primary key in my test table.
"DMLTYPE $$"! = 'I' means not an operation of INSERT.
The execution plan here displays a full table scan for the Mlog table. If there is a lot of update operations, it is undoubtedly a step of affecting performance.
11. Number of records in the base table currently need to refresh all fields
Select Current $. "IDate", current $. "C"
From
(SELECT "T1". "IDate" "iDate", "T1". "C" "c" "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 is indicated that only record primary keys involved in mlog are stored, and other field values will still go to the base table. The execution plan here shows a full table scan for the MLOG table again.
12. Update the realized view with the latest value of the obtained
Update "kamus". "Mv_t1" set "iDate" =: 1, "c" =: 2
WHERE
"IDate" =: 1
This step is still quite weird, because I only made INSERT in my test, there is no Update operation, and when Oracle is refreshing, regardless of whether there is an update of Update, will be routine to make a view data update? However, this update will use the primary key in the physicalized view, the speed should be very fast.
13. Insert the latest values obtained into the physical view
INSERT INTO "Kamus". "MV_T1" ("iDate", "c")
Values
(: 1,: 2)
This step is to the purpose of truly achievement, huh, huh.
14. Update a batch of data dictionary, indicating that the refresh has been completed
15. Delete records that have been refreshed in the mlog table
Delete from "kamus". "Mlog $ _T1"
WHERE
Snaptime $$ <=: 1
This step is to consume resource, use Delete, generate REDO and UNDO, can not reduce the hwm flag of the mlog table, and a full table scan, if there is a lot of updates, it is best to make a mlog table Truncate action Otherwise, this step can be more slower.
At this point, the quick refresh of a realized view is completely over.
We continue to look at the backstage mechanism that completely refreshes.
1-8 Steps with the quick refresh basically the same.
9. Check the SQL text to be executed
Select Operation #, COLS, SQL_TXT
From
Sys.snap_refop $ Where Operation # = 7 and slow =: 1 and vName =: 2 and
INSTSITE =: 3
You can see the SQL of Operation # = 7 directly, this is the SQL that needs to be used completely.
10. Check the constraints, indexes, triggers involved in fully refresh
11. Delete the original data in the physicalized view
Delete from "kamus". "mv_t"
This step makes me very surprised, remember that the documentation should be truncate operation, but what is DELETE? In this case, the cost of complete refresh is really great.
12. Insert all data in the base table
INSERT / * BYPASS_RECURSIVE_CHECK * / INTO "kamus". "MV_T" ("x") SELECT "T". "X"
From "t" "t"
The prompt / * bypass_recursive_check * / is not used in practical applications to improve the efficiency of INSERT in practical applications?
13. Update a batch of data dictionary, indicating that the refresh has been completed
14. If you create a refresh log mlog table on the base table, Oracle does not have been fully refreshed this refresh, you will be able to delete the operation of data in the mlog table. This step will be omitted if you have created mlog. So if you decide to use full refresh, don't create a refresh log on the base Table, save unnecessary resource consumption.
