Recently
i got a complain that MView refresh are getting slower. When traced got to know
that refresh is slow due to internal queries taking full scans on the tables.
All these internal queries are related to new feature of Oracle 12.2 which is
related to materialized view usage tracking. Feature is good but i see lot of
slowness from the application perspective.
Just
brief about feature: Starting 12.2 Oracle Database collects and stores statistics
about materialized view refresh operations. These statistics are accessible
using data dictionary views.
Statistics
for both current and historical materialized view refresh operations are stored
in the database. Historical materialized view refresh statistics enable you to
understand and analyze materialized view refresh performance over time in your
database. Refresh statistics can be collected at varying levels of granularity.
Back
to the problem in trace I saw Oracle executed DML’s and selects on below tables
and were performing full scan. See the execution plan below
mvref$_stats;
mvref$_run_stats;
mvref$_change_stats;
mvref$_stmt_stats;
Now
consider with each refresh lot of entries are inserted/updated/deleted in above
tables; so consider if you have millions of entries then such statements with
huge of executions can prove to be a disaster in terms of performance. Strange
is that there are no indexes on the table at least for the
deletes.:(
DELETE FROM
SYS.MVREF$_CHANGE_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ#
= :B1
Plan hash value:
2107128738
------------------------------------------------------------------------------------------
| Id |
Operation |
Name
| Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 |
DELETE STATEMENT
|
| |
| 2
(100)| |
| 1
|
DELETE |
MVREF$_CHANGE_STATS |
|
|
| |
|* 2
| TABLE ACCESS FULL| MVREF$_CHANGE_STATS |
1 | 26 | 2 (0)| 00:00:01
|
------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
filter(("REFRESH_ID"=:B2 AND "MV_OBJ#"=:B1))
SQL_ID
9g4dd2hsrpbph, child number 0
-------------------------------------
DELETE FROM
SYS.MVREF$_RUN_STATS WHERE REFRESH_ID = :B1
Plan hash value:
2011437079
---------------------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 |
DELETE STATEMENT
|
| |
| 2
(100)| |
| 1
|
DELETE |
MVREF$_RUN_STATS |
|
|
| |
|* 2
| TABLE ACCESS FULL| MVREF$_RUN_STATS | 1
| 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
filter("REFRESH_ID"=:B1)
There
is a collection level parameter which specifies the collection level for
materialized view refresh statistics. The values that can be set for the
COLLECTION_LEVEL parameter are:
NONE: No statistics are collected for materialized view
refresh operations.
TYPICAL
: Only basic refresh statistics
are collected for materialized view refresh operations. This is the default setting.
ADVANCED
: Detailed statistics, including
the parameters used in the refresh operation and the SQL statements that are
run, are collected for materialized view refresh operations.
In
my case i asked to turn of the feature.
In
general also if we see lot of MVIEWS related activities it is better to turn of
this feature as running such deletes will make application slow.
So
to avoid it better to turn off the collection/tracking level. By default
collection level is typical. So as to stop completely we need to execute below statement
to turn it off.
exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');
select * from DBA_MVREF_STATS_SYS_DEFAULTS;
PARAMETER_NAME VALUE
---------------- ----------------------------------------
COLLECTION_LEVEL NONE
RETENTION_PERIOD 365
There
are lot of stuff related to MViews usage tracking so please explore and use it
wisely.
Enjoy
Learning!!!
