Oracle Materialized View In-Depth and Materialized View refresh issues in 19c
We had situation demanding tuning of MV refresh and options like TUNE_MVIEW and Atomic_Refresh was explored . Hence Documenting materialized view sample commands and views in this blog .
After migration if we are facing slowness in Materialized View refresh , we can explore to re-create Materialized View
Since main topic for this blog was MV issues in 19c , posting that topic first in line below
We also have Real-Time Materialized Views to explore .
To Note :
1) We can create Mv on ON PREBUILT TABLE
2) We can create Index on Mv
3) We can gather stats on mv just like other table .
4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND.
5) Mv REFRESH_METHOD can be Complete / FAST or FORCE . A FORCE attempts a FAST and, if it can't do so (e.g. the Materialized View Log is not accessible), attempts a COMPLETE Refresh from all the rows of the Source Table.
What is Oracle materialized view
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
What is Oracle Materialized View Log
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.
Refresh Group
A refresh group is a collection of one or more materialized views that Oracle refreshes in an atomic transaction, guaranteeing that relationships among the master tables are preserved
There are 4 different options that we will try in order to achieve a parallel MV refresh. Namely these are the following:
A. The PARALLELISM parameter of the DBMS_MVIEW.REFRESH procedure
B. PARALLEL attribute of the materialized view
C PARALLEL attribute of the base table(s)
D. PARALLEL hint in the query defining the materialized view
Materialized View refresh issues in 19c specially when we are migrating from version from 12.1 or below
In 19c there are lot of reported issues with regards to Materialized View refresh . Below are known fixes and Bugs As per 2879576.1 and 2562155.1
1) Using /*+ no_merge */ hint . The no_merge hint prevents it from rewriting the view in unexpected ways that result in your other hints being invalid.
2) using optimizer_features_enable='9.0.0'
3) Disabling Stats COLLECTION_LEVEL which was introduced in 12.2
The setting causing a lot of noise (and a performance degradation) in this case is set by default with SET_SYSTEM_DEFAULT where you define either the COLLECTION_LEVEL or the RETENTION_PERIOD with.
The COLLECTION_LEVEL defaults to TYPICAL. According to the documentation, TYPICAL means “Only basic refresh statistics are collected for the refresh operation. This is the default setting.“.
If you are coming from 11g or 12.1.0.2 – and I can only guess that this stats collection did not happen in those releases.
And there seem to be an issue in some cases which you can read in MOS Note: 2764909.1 – Materialized View Refresh Statistics Causing High CPU and Logical Reads.
wait is due to usage tracking enabled for MVIEW operations is implemented starting from 12.2
In order to avoid issues, you should disable the collection.
exec DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT('COLLECTION_LEVEL', 'NONE');
In addition, the customer – following again Oracle Support’s advice, did also wipe out the existing stats:
truncate table mvref$_stats;
truncate table mvref$_run_stats;
truncate table mvref$_change_stats;
truncate table mvref$_stmt_stats;
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.
Select * from DBA_MVREF_STATS_SYS_DEFAULTS;
4) Mview refresh creates Global Temporary table names starting with WRI$_ADV_ (Doc ID 2502544.1)
You can prevent segment advisor from kicking in during the mview refresh by setting the below parameter.
alter system set "_mv_refresh_shrink_log" = FALSE;
The disadvantage with that setting is the mview log is not shrunk automatically after mview refresh. It can however be done manually.
Example:
ALTER MATERIALIZED VIEW LOG ON TEST ENABLE ROW MOVEMENT;
ALTER MATERIALIZED VIEW LOG ON TEST SHRINK SPACE;
5) Try re-creating Materialized View
Atomic_Refresh with value FALSE and adding parallel to refresh .
Advantages of ATOMIC_REFRESH => FLASE
No or very less archive generation
Complete refresh process is very fast as compared to process with ATOMIC_REFRESH => TRUE
Disadvantages of ATOMIC_REFRESH => FLASE
Unavailability of data during the refresh process, which could be unacceptable to application and business user.
This is the optional parameter atomic_refresh of the procedure dbms_mview.refresh and is very useful. If the parameter is set to FALSE,
the materialized view is deleted with a much faster TRUNCATE command. I immediately got the gain, as the runtime was dropped by 60%
DELETE is replaced with TRUNCATE, and hence a great drop in ‘checkpoint incomplete‘ and ‘Log File Sync‘ waits.
BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE);
END;
/
BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE, PARALLELISM=>4);
END;
/
Materialized view capabilities to decide MV refresh method
MV can be either fast refreshed . Oracle is able to let us know wether a materialized view can be fast (also know as incremental) refreshed or not.
You need first to create the mv_capabilities_table table (in the schema you are going to use against it the dbms_mview package) using the $ORACLE_HOME/rdbms/admin/utlxmv.sql
Once this table created you can execute the dbms_mview.explain_mview package as shown below:
SQL> exec dbms_mview.explain_mview ('my_materialied_mv');
select
mvname
,capability_name
,possible
from
mv_capabilities_table
where
mvname = 'MY_MATERIALIED_MV'
and
capability_name like '%REFRESH%';
Tracing for MV:
1) racing for invalid MV
ALTER SYSTEM SET EVENT='32333 trace name context forever, level 9' SCOPE=SPFILE
Using EXPLAIN_REWRITE to check if query rewrite is working on MV
We can use the DBMS_MVIEW.EXPLAIN_REWRITE package to diagnose why Query redirection to a materialized view is not happening.
We can redirect the output to a table called REWRITE_TABLE which is created via the utlxrw.sql script located under $ORACLE_HOME/rdbms/admin.
SQL> truncate table rewrite_table;
SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name NOT LIKE ''A%'' -
GROUP BY prod_name',-
'SALES_MV','SH');> > > > > >
SQL> SELECT message FROM rewrite_table ORDER BY sequence;
MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01112: WHERE clause of mv, SALES_MV, is more restrictive than query
QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENFORCED integrity mode
Running Tuning Advisor On Materialized view Using DBMS_ADVISOR.TUNE_MVIEW
TUNE_MVIEW can help us in another way by actually rewriting the CREATE MATERIALIZED VIEW statement for us to enable things like Fast Refreshes and also Query Rewrites.
We can redirect the output to a script as well. In this case we have a directory DATA_PUMP_DIR which points to an OS location and we specify the script which should be created.
Note - usage of the DBMS_ADVISOR package will require the system privilege ADVISOR to be granted to the user executing the package.
We can either view the recommendations via the view *_TUNE_MVIEW or from the generated SQL script file.
SQL> VARIABLE task_cust_mv VARCHAR2(30);
SQL> VARIABLE create_mv_ddl VARCHAR2(4000);
SQL> EXECUTE :task_cust_mv := 'TEST_TUNE_MV';
SQL> EXECUTE :create_mv_ddl := '-
CREATE MATERIALIZED VIEW cust_mv-
REFRESH FAST-
ENABLE QUERY REWRITE AS -
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount-
FROM sales s, customers cs-
WHERE s.cust_id = cs.cust_id-
GROUP BY s.prod_id, s.cust_id';
SQL> EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv),
SQL> set long 500000
SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW
WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';
DBMS_MVIEW_STATS procedure to set the COLLECTION_LEVEL parameter.
Database-wide: DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT
For individual MViews: DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS
1) Setting Materialized View Refresh Statistics Collection Level for the Database
DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('COLLECTION_LEVEL','ADVANCED');
2) Disabling Statistics Collection for Materialized View Refresh
DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('COLLECTION_LEVEL','NONE');
3) Setting the Materialized View Statistics Collection Level for the Entire Database
DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS (NULL, 'TYPICAL');
4) Setting the Materialized View Statistics Collection Level for Multiple Materialized Views
DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('SH.SALES_2013_MV, SH.SALES_2014_MV','ADVANCED',60);
5) Setting the Retention Period for Materialized View Refresh Statistics
DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('RETENTION_PERIOD',60);
6) Preventing the Purging of Materialized View Refresh Statistics
DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('RETENTION_PERIOD',–1);
MV Tuning Tip as per Oracle Doc ID 412400.1)
1. Make sure that statistics are generated on the master table and the materialized refresh view log, and that the master table is appropriately indexed.
2) The init.ora parameter _mv_refresh_use_stats should be set to FALSE .
Note: This parameter may have a negative effect on performance for cases where the HASH_SJ is seen as an issue:
TURN OFF HASH_SJ hint
The default setting of "_mv_refresh_use_stats" is FALSE from 10.2 & higher versions.
Hence, we can set it to non-default value i.e. TRUE in any 10.2 & higher versions.
Reference note: 733054.1
3) Index the following two columns of the snapshot log (mlog$_<table_name>): snapstime$$ and the column(s) corresponding to the primary key of the master table.
These can be standard b-tree indexes. The latter index would not exist if this was a rowid-based mview. Note 258252.1 MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring .
Note: This is not required for SCN BASED Mview Logs which are available from 11.2. onwards.
4) Make sure that the sort parameters on the master table database are adequate. Check PGA_AGGREGATE_TARGET is set appropriately, so sorting does not go to disk.
If you do not use PGA_AGGREGATE_TARGET, SORT_AREA_SIZE is used. Increasing SORT_AREA_SIZE from it's default of 64k to 1M can increase fast refresh speed dramatically. Be careful not to set SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE too high since this memory is allocated on a per session basis, and you might start receiving out of memory errors.
4) Materialized view log not getting purged and size keeps growing .
There are reported issue where Materialized view log not getting purged due to failed refresh or complete refresh not done for long time .
We need to rerog mv logs or perform full refresh in such case.
6) Base table and Mv log can be altered to have parallel clause .
Materialized view Sample Commands
ALTER MATERIALIZED VIEW LOG ON TEST ENABLE ROW MOVEMENT;
ALTER MATERIALIZED VIEW LOG ON TEST SHRINK SPACE;
create index mv_1_ndx_on_owner on mv_1 (obj_owner)
/
exec dbms_stats.gather_table_stats('','MV_1');
create materialized view mv_fast_not_possible refresh force on demand
as select id, data_element_2, sysdate
from source_table
/
ALTER MATERIALIZED VIEW mview_name COMPILE;
ALTER MATERIALIZED VIEW mview_name refresh fast ;
CREATE MATERIALIZED VIEW sales_sumry1
BUILD deferred
REFRESH FORCE
AS
SELECT p.brand, c.city_name, t.month,SUM(s.amt) AS tot_sales,COUNT(s.amt) AS num_sales
FROM sales1 s, city1 c, timetab1 t, product1 p
WHERE s.city_code = c.city_code
AND s.state_code = c.state_code
AND s.sdate = t.sdate
AND s.prod_code = p.prod_code
GROUP BY p.brand,c.city_name,t.month;
create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;
create materialized view v
build immediate
refresh force on demand
enable query rewrite
as
select t1.rowid r1, t2.rowid r2, t1.c c1, t2.c c2
from t1,t2
where t1.c = t2.c;
DROP MATERIALIZED VIEW sales_sumry
Sample 1:
DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON products;
pause clean up mviews
DROP MATERIALIZED VIEW cust_avgsales_mv;
DROP MATERIALIZED VIEW cust_avgsales_mv$SUB1;
pause create the mview logd
CREATE MATERIALIZED VIEW LOG ON sales
WITH SEQUENCE, ROWID (cust_id,prod_id,amount_sold)
INCLUDING NEW VALUES;
pause
CREATE MATERIALIZED VIEW LOG ON products
WITH SEQUENCE, ROWID (prod_id,prod_name)
INCLUDING NEW VALUES;
pause try to create an MVIEW FAST REFRESH on a complex query (inline view)
CREATE MATERIALIZED VIEW cust_avgsales_mv
REFRESH FAST ENABLE QUERY REWRITE AS
SELECT cust_id, avg(dollar_sales)
FROM ( SELECT p.prod_name, s.cust_id,
sum(s.amount_sold) AS dollar_sales
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name, s.cust_id
) prod_sales
GROUP BY cust_id;
Pause Do it again by decomposing the above to two MVIEWs
CREATE MATERIALIZED VIEW cust_avgsales_mv$SUB1
REFRESH FAST ENABLE QUERY REWRITE AS
SELECT p.prod_name, s.cust_id, COUNT(*) AS cnt_all,
count(s.amount_sold) AS cnt_sales,
sum(s.amount_sold) AS dollar_sales
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name, s.cust_id;
pause now the second one
CREATE MATERIALIZED VIEW cust_avgsales_mv
REFRESH FORCE ENABLE QUERY REWRITE AS
SELECT cust_id, count(*) cnt,
count(dollar_sales) cnt_dollar,
avg(dollar_sales) avg_dollar
FROM cust_avgsales_mv$SUB1
GROUP BY cust_id;
pause clean up
DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON products;
DROP MATERIALIZED VIEW cust_avgsales_mv;
DROP MATERIALIZED VIEW cust_avgsales_mv$SUB1;
Sample 2 :
CREATE MATERIALIZED VIEW ASSAY
("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N)
AS
select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS")
from ASSAY_INHIB
where upper("VALIDATION")='YES'
group by "TYPE","METHOD",COMPOUND_CORPORATE_ID;
CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id);
ALTER MATERIALIZED VIEW ASSAY REFRESH FORCE ON DEMAND;
alter materialized view PAYER1.A_ERA_DAY parallel (degree default);
alter materialized view PAYER1.A_ERA_DAY noparallel;
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET query_rewrite_integrity=stale_tolerated;
ALTER MATERIALIZED VIEW sales_sumry1 ENABLE query REWRITE;
BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE, PARALLELISM=>4);
END;
BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE);
END;
xec dbms_mview.refresh('PAYER1.A_ERA_DAY','C');
exec dbms_mview.refresh('PAYER1.A_ERA_MONTH_PAYER','F');
execute dbms_mview.refresh('V','F');
set lines 150
set pages 1000
select 'exec dbms_snapshot.refresh('''||owner||'.'||mview_name||''''||','''||refresh_method||''''||',parallelism=>8,heap_size=>256);'
from dba_mviews where last_refresh_date < trunc(sysdate) and mview_name like upper('%&name%')
order by last_refresh_date
select 'execute DBMS_MVIEW'||'.'||'REFRESH('||''''||OWNER||'.'||MVIEW_NAME||''''||','||''''||'C'||''''||',ATOMIC_REFRESH=>false);' FROM dba_mviews;
Getting DDL for existing Materialized View
SELECT
replace (dbms_metadata.get_ddl(replace(
OBJECT_TYPE, ' ', '_'),
OBJECT_NAME,OWNER)
,'q#"#'
,'q#''#'
)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'MATERIALIZED VIEW'
AND object_name = 'MY_MATERIALIED_MV';
Database Parameter influencing Materialized View :
query_rewrite_enabled
query_rewrite_integrity
job_queue_processes
Session level parameter influencing Mv :
alter session set "_unnest_subquery"=false;
alter session set "_complex_view_merging"=false;
alter session set "_push_join_predicate"=false;
alter session set "_optimizer_mode_force" = false;
alter session set "_optimizer_cbqt_or_expansion"=off;
alter session set "_simple_view_merging"=false;
alter session set OPTIMIZER_ADAPTIVE_STATISTICS = FALSE;
alter session set optimizer_dynamic_sampling=0;
alter session set "_optimizer_cartesian_enabled" = FALSE;
alter session set "_optimizer_gather_stats_on_load"=FALSE;
alter session set "_optimizer_mode_force" = false;
alter session set "_mv_generalized_oj_refresh_opt"=FALSE;
Checking MV refresh history
-- check ongoing mv refresh
select s.sid, s.username
from dba_jobs_running jr, v$session s, dba_jobs j
where jr.sid=s.sid and
j.job=jr.job and
upper(j.what) like '%REFRESH%%';
SELECT * FROM V$MVREFRESH O WHERE O.CURRMVOWNER = j.owner AND O.CURRMVNAME = j.table_name;
SELECT /*+ RULE */
A.JOB JOB#,
SCHEMA_USER MVIEW_OWNER,
DECODE(SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,’”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2),NULL,SUBSTR(WHAT,1,40), SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,’”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2)) MVIEW_NAME,
LAST_DATE LAST_RUN_DATE,
NEXT_DATE NEXT_SCHED_RUN_DATE,
DECODE(BROKEN,’Y’,’YES’,’N’,’NO’,’ ‘) IS_BROKEN,
FAILURES,
RUNNING IS_RUNNING,
B.SID SID
FROM DBA_JOBS A
LEFT OUTER JOIN (SELECT /*+ RULE */
JOB,’YES’ RUNNING,SID
FROM DBA_JOBS_RUNNING ) B
ON A.JOB = B.JOB
ORDER BY SCHEMA_USER, MVIEW_NAME;
SELECT inserts, updates, deletes
INTO l_ending_inserts,
l_ending_updates,
l_ending_deletes
FROM user_tab_modifications
WHERE table_name = 'mvtable';
SELECT SUM( CASE WHEN dmltype$$ = 'I' THEN 1 ELSE 0 END ) num_pending_inserts,
SUM( CASE WHEN dmltype$$ = 'U' THEN 1 ELSE 0 END ) num_pending_updates,
SUM( CASE WHEN dmltype$$ = 'D' THEN 1 ELSE 0 END ) num_pending_deletes
FROM mlog$_mvtable
-- Identify the Last and Next Refresh Dates
SELECT owner, mview_name, last_refresh_date FROM dba_mviews WHERE owner = <BWPM DB Username> AND mview_name = ’NJAMS_MV_STATISTICS’
alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
select owner, mview_name, last_refresh_type, last_refresh_date from all_mviews;
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
column what format a36
select what, last_date, next_date
from dba_jobs
where upper(what) like 'ÛMS_REFRESH.REFRESH(%%.%%';
SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';
SELECT mv_name, step, stmt, execution_time
FROM dba_mvref_stmt_stats
WHERE mv_name = '1MV1';
SELECT step, sqlid, stmt, execution_time
FROM DBA_MVREF_STATS M, DBA_MVREF_STMT_STATS S
WHERE M.refresh_id = S.refresh_id and M.mv_name = 'MY_SALES'
ORDER BY step;
SEELCT LOG_DATE , JOB_NAME , RUN_DURATION , SESSION_ID , SLAVE_PID FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE owner='XX'
AND job_name like 'MV_R%'ORDER BT ACTUAL_START_date ;
SET LINE 200
col sql_id for a30
col program for a30
col module for a30
col sql_plan_hash_VALUE fro a30
COL SAMPLE_TIME FOR A30
SELECT SQL_ID , SAMPLE_TIME , MODULE , PROGRAM , sql_plan_hash_VALUE FROM V$ACTIVE_SESSION_HISTORY WHERE SESSION_ID IN
( SELECT SUBSTR(SESSION_ID,1 , INSTR( SESSION_ID,',')-1) FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'MV_R%' AND TRUNC(LOG_DATE) ='30-JUL-2024' )
ORDER BY SAMPLE_TIME ;
SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh between '29-OCT-10' AND '31-OCT-10';
SELECT * FROM ALL_MVIEW_REFRESH_TIMES;
SELECT owner, mview_name, last_refresh_date
FROM all_mviews
WHERE owner = <<user that owns the materialized view>>
AND mview_name = <<name of the materialized view>>
elect owner, name, last_refresh, error, status, refresh_mode
from all_snapshots
where owner = 'owner';
Other Views :
For refresh group, the below queries gives the information about group
select * from dba_refresh;
select * from dba_refresh_children;
select * from sys.v_$mvrefresh;
set lines 500 pages 500 col sep ';' ;
select MVIEW_NAME , REFRESH_MODE, REFRESH_METHOD, FAST_REFRESHABLE , STALENESS , LAST_REFRESH_TYPE , LAST_REFRESH_DATE, UPDATABLE ,
COMPILE_STATE , STALE_SINCE , AFTER_FAST_REFRESH from user_mviews where MVIEW_NAME ='MV1';
SELECT *
FROM (
SELECT OWNER,
MVIEW_NAME,
CONTAINER_NAME,
REFRESH_MODE,
REFRESH_METHOD,
LAST_REFRESH_TYPE,
STALENESS,
ROUND((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS
FROM ALL_MVIEWS
WHERE LAST_REFRESH_TYPE IN (‘FAST’,’COMPLETE’)
)
ORDER BY REFRESH_TIME_MINS DESC;
select v.owner MV_Owner, v.name MV_Name, v.snapshot_site, v.refresh_method,
l.log_table MV_Log_Name, l.master MV_Source,
to_char(l.current_snapshots,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
from dba_registered_snapshots v, dba_snapshot_logs l
where v.snapshot_id = l.snapshot_id
and l.log_owner = 'HEMANT'
/
select mview_name, query, master_link, refresh_mode, refresh_method,
last_refresh_type, to_char(last_refresh_date,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
from dba_mviews
where owner = 'HEMANT'
order by 1 desc
/
SELECT LOG_OWNER,MASTER,LOG_TABLE
FROM all_mview_logs
WHERE MASTER IN ('DWH_CODE','ST_SI_MESSAGE_H','ST_SI_MESSAGE_V');
Select owner, object_name, object_type, status from dba_objects WHERE owner=’<BWPM DB Username>’ and OBJECT_NAME=’NJAMS_MV_STATISTICS’ and OBJECT_TYPE=’MATERIALIZED VIEW’
SELECT *
FROM (
SELECT OWNER,
MVIEW_NAME,
CONTAINER_NAME,
REFRESH_MODE,
REFRESH_METHOD,
LAST_REFRESH_TYPE,
STALENESS,
ROUND((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS
FROM ALL_MVIEWS
WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
)
ORDER BY REFRESH_TIME_MINS DESC;
SELECT parameter_name, value from DBA_MVREF_STATS_SYS_DEFAULTS;
SELECT mv_name,collection_level,retention_period
FROM DBA_MVREF_STATS_PARAMS
WHERE mv_owner = 'SH';
SELECT refresh_id, refresh_method, elapsed_time, initial_num_rows, final_num_rows
FROM dba_mvref_stats
WHERE mv_name = 'NEW_SALES_RTMV' and mv_owner = 'SH';
SELECT mviews, elapsed_time, complete_stats_available
FROM dba_mvref_run_stats
WHERE refresh_id = 100;
SELECT *
FROM ( SELECT OWNER,
MVIEW_NAME,
CONTAINER_NAME,
REFRESH_MODE,
LAST_REFRESH_TYPE,
STALENESS,
round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS
FROM ALL_MVIEWS
WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
)
ORDER BY REFRESH_TIME_MINS DESC;
select * from mlog$_tablename; ( mv og )
select owner||'.'||mview_name view_name from user_mviews where owner = 'REPORTING' and compile_state <> 'VALID'
SELECT detailobj_owner owner,
detailobj_name name,
detailobj_type type,
detailobj_alias alias
FROM user_mview_detail_relations
WHERE mview_name='SALES_SUMRY'
/
select * from all_refresh_dependencies;
col mview_table_owner for a10
col container_name for a12
set long 40
select mview_name,mview_table_owner,container_name,
refresh_method,known_stale,rewrite_enabled,summary,query
from user_mview_analysis;
col owner for a10
col mview_name for a20
col container_column for a20
set long 20
select * from user_mview_aggregates;
col mview_name for a12
col DETAILOBJ_NAME for a10
col DETAILOBJ_COLUMN for a10
select * from user_mview_keys
col DETAILOBJ1_OWNER for a10
col DETAILOBJ1_RELATION for a10
col DETAILOBJ1_COLUMN for a10
col DETAILOBJ2_OWNER for a10
col DETAILOBJ2_RELATION for a10
col DETAILOBJ2_COLUMN for a10
select * from user_mview_joins;
set timi on
set time on
set line 300
alter session set db_file_multiblock_read_count=128;
alter session set skip_unusable_indexes=true;
alter session set sort_area_size=52428800;
alter session set hash_area_size=80028800;
select MVIEW_NAME,owner,last_refresh_type,refresh_method,to_char(LAST_REFRESH_DATE ,'DD-MON-YYYY HH:MI:SS')
from dba_mviews order by LAST_REFRESH_DATE;
set lines 200
set pages 1000
column "MVIEW BEING REFRESHED" format a42
column INSERTS format 9999999999
column UPDATES format 9999999999
column DELETES format 9999999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR
"MVIEW BEING REFRESHED",x.sid_knst,
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE',
3, 'WRAPUP', 'UNKNOWN' ) STATE,
TOTAL_INSERTS_KNSTMVR INSERTS,
TOTAL_UPDATES_KNSTMVR UPDATES,
TOTAL_DELETES_KNSTMVR DELETES
from X$KNSTMVR X
WHERE type_knst=6 and
exists (select 1 from v$session s
where s.sid=x.sid_knst and
s.serial#=x.serial_knst)
order by 1 ;
SELECT
A.JOB JOB#,
SCHEMA_USER MVIEW_OWNER,
substr(DECODE(SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2),NULL,SUBSTR(WHAT,1,80),
SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2)),0,80) JOB_DESCRIPTION,
to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DATE,
to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_SCHED_RUN_DATE,
DECODE(BROKEN,'Y','YES','N','NO',' ') IS_BROKEN,
FAILURES,
RUNNING IS_RUNNING,
B.SID SID
FROM DBA_JOBS A LEFT OUTER JOIN (SELECT JOB,'YES' RUNNING,SID FROM DBA_JOBS_RUNNING ) B
ON A.JOB = B.JOB
ORDER BY SCHEMA_USER, JOB_DESCRIPTION;
SELECT *
FROM ( SELECT OWNER,
MVIEW_NAME,
CONTAINER_NAME,
REFRESH_MODE,
LAST_REFRESH_TYPE,
STALENESS,
round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS
FROM ALL_MVIEWS
WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
)
ORDER BY REFRESH_TIME_MINS DESC;
References :
http://175.178.24.119/19c/content/dwhsg/monitoring-materialized-view-refresh.html#GUID-6AD8879A-0BE5-466E-8D19-1D076C5DDFFB
General Materialized View Refresh Performance Tuning Tips (Doc ID 412400.1)
Slow Materialized View Refresh After Upgrading Materialized View database To 19C (Doc ID 2766301.1)
Materialized View Refresh Slowness After Upgrading To 19C (Doc ID 2879576.1)
Materialized View Refresh Statistics Causing High CPU and Logical Reads (Doc ID 2764909.1)
How to Monitor the Progress of a Materialized View Refresh (MVIEW) (Doc ID 258021.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/monitoring-materialized-view-refresh.html#GUID-D87F8F81-8B93-4D7E-87F5-3BFEAA2A7C06
https://hemantoracledba.blogspot.com/2019/
Comments
Post a Comment