Locking issue while MATERIALIZED VIEW REFRESH [message #679546] |
Fri, 06 March 2020 19:21 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi All,
I need your help in Materialized View refresh. I've tried many options like
ON COMMIT
ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/4096
ON DEMAND --DBMS_MVIEW.REFRESH('MV_TXN_HIST','F');
but the issue I'm facing is related to blocking sessions. Materialized view never refreshes and creates blocking sessions. Please help me to understand what can be the possible cause and solution to this problem.
Fast refresh doesn't work in any way and creates locking issues but COMPLETE refresh works perfectly fine.
CREATE TABLE TXN_TABLE_1
(
TXN_ID VARCHAR2(36)
,TXN_NOTE VARCHAR2(100)
,REF_ID VARCHAR2(36)
,REF_URL VARCHAR2(500)
,TXN_CREATE_TS TIMESTAMP(6)
,TXN_TYPE VARCHAR2(20)
,TXN_AMT NUMBER(18,3)
,TXN_CURR VARCHAR2(4)
) ;
CREATE TABLE TXN_TABLE_2
(
TXN_ID VARCHAR2(36)
,RES_CODE VARCHAR2(100)
,RES_ID VARCHAR2(36)
,RES_URL VARCHAR2(500)
,RES_CREATE_TS TIMESTAMP(6)
) ;
CREATE MATERIALIZED VIEW LOG ON TXN_TABLE_1 WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON TXN_TABLE_2 WITH ROWID ;
CREATE MATERIALIZED VIEW MV_TXN_HIST
REFRESH FAST ON COMMIT
WITH ROWID
AS
SELECT
T.TXN_ID
,T.TXN_AMT
,T.TXN_CURR
,R.RES_CODE
,NVL(R.RES_ID, T.RES_ID) RES_ID
FROM
TXN_TABLE_1 T
,TXN_TABLE_2 R
WHERE T.TXN_ID = R.TXN_ID(+) ;
Thanks & Regards
Manoj
[Updated on: Sat, 07 March 2020 02:15] Report message to a moderator
|
|
|
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679549 is a reply to message #679546] |
Sat, 07 March 2020 02:18 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi All,
I got the issue. I was using NVL in Materiliazed View's base query which was creating this issue.
But now I'm facing performance issue with materialized view itself. Time taken for insert and commit on base tables have been increased drastically. Can someone please help me to resolve this issue.
Thanks & Regards
Manoj
|
|
|
|
|
|
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679565 is a reply to message #679561] |
Mon, 09 March 2020 12:00 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
John Watson wrote on Mon, 09 March 2020 19:02Quote:I'm using global partitioned indexes on materialized view to be used in queries on materialized view. Parallel in materialized view base query. So the code you gave in your first post is nothing like reality?
Correct.
But I'm facing problem with it's fast refresh. Fast is happening sometimes and sometimes not, MV has even stopped refreshing. Whenever MV refreshed it took huge time to refresh.
What could be the possible cause. Please help.
Thanks & Regards
Manoj
|
|
|
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679628 is a reply to message #679565] |
Wed, 11 March 2020 07:28 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
To do a fast refresh you must have mview logs defined on the base tables specifying primary key. The base tables must have a primary key defined and not just a unique key. The primary key must be in the MVIEW and it must have a unique index on it
|
|
|