Home » RDBMS Server » Server Administration » Mview is not refreshing (Oracle 11.1, Windows 2003 Server)
| Mview is not refreshing [message #522874] |
Wed, 14 September 2011 05:30  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.
drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE SMSCHAT
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE SMSCHAT
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+10/86400
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT TBL_VOICECHAT.ANI ANI,TBL_VOICECHAT.CHAT_ID CHAT_ID,TBL_VOICECHAT.GENDER GENDER,
TBL_VOICECHAT.AGE AGE,TBL_VOICECHAT.RECORD_FILE RECORD_FILE,TBL_VOICECHAT.SUB_DATE_TIME SUB_DATE_TIME,
TBL_VOICECHAT.STATUS STATUS,TBL_VOICECHAT.LOGIN_STATUS LOGIN_STATUS,TBL_VOICECHAT.BILLING_DATE BILLING_DATE,
TBL_VOICECHAT.CIRCLE_NAME CIRCLE_NAME,TBL_VOICECHAT.INTEREST INTEREST,TBL_VOICECHAT.HOBBIES HOBBIES,
TBL_VOICECHAT.LANGUAGE LANGUAGE,TBL_VOICECHAT.OLD_ID OLD_ID,TBL_VOICECHAT.SUB_FILE SUB_FILE,
TBL_VOICECHAT.M_ACT M_ACT,TBL_VOICECHAT.LAST_CALL_DATE LAST_CALL_DATE,TBL_VOICECHAT.FLAG FLAG,
TBL_VOICECHAT.SUNSIGN SUNSIGN,TBL_VOICECHAT.PSUNSIGN PSUNSIGN,TBL_VOICECHAT.BAL_STATE BAL_STATE,
TBL_VOICECHAT.TOTALHIT TOTALHIT,TBL_VOICECHAT.NEXT_BILLED_DATE NEXT_BILLED_DATE,
TBL_VOICECHAT.LAST_BILLED_DATE LAST_BILLED_DATE,
TBL_VOICECHAT.IS_PREPAID IS_PREPAID,TBL_VOICECHAT.DNDFLAG DNDFLAG,TBL_VOICECHAT.AMOUNT AMOUNT,
TBL_VOICECHAT.VALUE1 VALUE1
FROM AFCCV.TBL_VOICECHAT@SERVICEDB1 TBL_VOICECHAT;
I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.
Thanks
Pradeep
[Updated on: Sat, 24 September 2011 02:23] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Mview is not refreshing [message #522887 is a reply to message #522881] |
Wed, 14 September 2011 06:21  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Not true in 11g:
From the docs
You cannot specify both ON COMMIT and ON DEMAND. START WITH and NEXT take precedence over ON DEMAND. Therefore, in most circumstances it is not meaningful to specify ON DEMAND when you have specified START WITH or NEXT.
In earlier versions I believe the above syntax would be invalid.
|
|
|
|
|
|
| Re: Mview is not refreshing [message #522893 is a reply to message #522881] |
Wed, 14 September 2011 06:51 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal,
Thanks for your response. As now I created this mview and it is in use currently. So I can't drop and recreate it.
So I am using the below command for alteration.
alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
REFRESH FASt START WITH sysdate+0 NEXT sysdate+10/86400
Output of the command is
SQL> alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1" REFRESH FASt START
WITH sysdate+0 NEXT sysdate+10/86400;
Materialized view altered.
SQL>
After modification if I am checking the DDL for the meterlized view is the same as previous
SQL> l
1 SELECT dbms_metadata.get_ddl(replace(OBJECT_TYPE, ' ', '_'), OBJECT_NAME,OW
NER)
2 FROM DBA_OBJECTS
3 WHERE OBJECT_TYPE in ('MATERIALIZED VIEW')
4* AND OWNER = 'SMSCHAT'
SQL> /
DBMS_METADATA.GET_DDL(REPLACE(OBJECT_TYPE,'','_'),OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOL
OGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+10/86400
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT TBL_VOICECHAT.ANI ANI,TBL_VOICECHAT.CHAT_ID CHAT_ID,TBL_VOICECHAT.GE
NDER GENDER,
TBL_VOICECHAT.AGE AGE,TBL_VOICECHAT.RECORD_FILE RECORD_FILE,TBL_VOICECHAT.SUB_
DATE_TIME SUB_DATE_TIME,
TBL_VOICECHAT.STATUS STATUS,TBL_VOICECHAT.LOGIN_STATUS LOGIN_STATUS,TBL_VOICEC
HAT.BILLING_DATE BILLING_DATE,
TBL_VOICECHAT.CIRCLE_NAME CIRCLE_NAME,TBL_VOICECHAT.INTEREST INTEREST,TBL_VOIC
ECHAT.HOBBIES HOBBIES,
TBL_VOICECHAT.LANGUAGE LANGUAGE,TBL_VOICECHAT.OLD_ID OLD_ID,TBL_VOICECHAT.SUB_
FILE SUB_FILE,
TBL_VOICECHAT.M_ACT M_ACT,TBL_VOICECHAT.LAST_CALL_DATE LAST_CALL_DATE,TBL_VOIC
ECHAT.FLAG FLAG,
TBL_VOICECHAT.SUNSIGN SUNSIGN,TBL_VOICECHAT.PSUNSIGN PSUNSIGN,TBL_VOICECHAT.BA
L_STATE BAL_STATE,
TBL_VOICECHAT.TOTALHIT TOTALHIT,TBL_VOICECHAT.NEXT_BILLED_DATE NEXT_BILLED_DAT
E,TBL_VOICECHAT.LAST_BILLED_DATE LAST_BILLED_DATE,
TBL_VOICECHAT.IS_PREPAID IS_PREPAID,TBL_VOICECHAT.DNDFLAG DNDFLAG,TBL_VOICECHA
T.AMOUNT AMOUNT,TBL_VOICECHAT.VALUE1 VALUE1
FROM AFCCV.TBL_VOICECHAT@SERVICEDB1 TBL_VOICECHAT
SQL>
also I checked that this mview refreshing properply til the time for created. for your information its created in 12:30PM approx.
Regards
Pradeep S
[Updated on: Sat, 24 September 2011 02:26] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Mview is not refreshing [message #522896 is a reply to message #522895] |
Wed, 14 September 2011 06:58 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal,
Yes Job_Queue_process parameter is greter then 0.
SQL> show parameter job_queue
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
job_queue_processes integer
1000
SQL>
Regards
Pradeep
|
|
|
|
| Re: Mview is not refreshing [message #522934 is a reply to message #522896] |
Wed, 14 September 2011 10:17 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal/cookiemaster,
Please tell if there is any wrong. what else I should check. and where I can get the error for refreshment. Rest i will revert back if there is any issue in mview refreshment.
|
|
|
|
|
|
| Re: Mview is not refreshing [message #524337 is a reply to message #522895] |
Fri, 23 September 2011 02:29 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
As mentioned below Today Again the Mview not refershed after 8:28AM.
Below is the output of command.
SQL> select mview_name,last_refresh_date from dba_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ --------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS 20-jan-2011 11:44:40
TBL_VOICECHAT_NEW1 23-sep-2011 08:28:19
SQL> select job, failures, broken from dba_jobs where lower(what) like '%refr
esh%';
JOB FAILURES B
---------- ---------- -
90 0 N
SQL>
alert log content at that time when it was last refreshed Where the mview exist i.e. vxmldb
Fri Sep 23 07:13:04 2011
Thread 1 advanced to log sequence 14149
Current log# 7 seq# 14149 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 07:39:22 2011
Thread 1 advanced to log sequence 14150
Current log# 4 seq# 14150 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 08:04:34 2011
Thread 1 advanced to log sequence 14151
Current log# 5 seq# 14151 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 08:27:27 2011
Thread 1 advanced to log sequence 14152
Current log# 6 seq# 14152 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 08:54:11 2011
Thread 1 advanced to log sequence 14153
Current log# 7 seq# 14153 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 09:14:38 2011
Thread 1 advanced to log sequence 14154
Current log# 4 seq# 14154 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 09:44:36 2011
Thread 1 advanced to log sequence 14155
Current log# 5 seq# 14155 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 10:06:50 2011
Thread 1 advanced to log sequence 14156
Current log# 6 seq# 14156 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 10:24:41 2011
Thread 1 advanced to log sequence 14157
Current log# 7 seq# 14157 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 10:46:44 2011
Thread 1 advanced to log sequence 14158
Current log# 4 seq# 14158 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 11:06:13 2011
Thread 1 advanced to log sequence 14159
Current log# 5 seq# 14159 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 11:24:23 2011
Thread 1 advanced to log sequence 14160
Current log# 6 seq# 14160 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 11:46:05 2011
Thread 1 advanced to log sequence 14161
Current log# 7 seq# 14161 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Fri Sep 23 12:03:31 2011
Thread 1 advanced to log sequence 14162
Current log# 4 seq# 14162 mem# 0: E:\ORADATA\SMSCHAT\REDO04.LOG
Fri Sep 23 12:18:00 2011
Thread 1 advanced to log sequence 14163
Current log# 5 seq# 14163 mem# 0: E:\ORADATA\SMSCHAT\REDO05.LOG
Fri Sep 23 12:34:32 2011
Thread 1 advanced to log sequence 14164
Current log# 6 seq# 14164 mem# 0: E:\ORADATA\SMSCHAT\REDO06.LOG
Fri Sep 23 12:50:24 2011
Thread 1 advanced to log sequence 14165
Current log# 7 seq# 14165 mem# 0: E:\ORADATA\SMSCHAT\REDO07.LOG
Kindly tell me why this view not getting refreshed as per the schedule time. or how to check what error coming at that time and where I should look for the error.
Regards
Pradeep
|
|
|
|
| Re: Mview is not refreshing [message #524338 is a reply to message #524337] |
Fri, 23 September 2011 02:35 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
One More thing I checked in the Alert log file creating on source table database name from where the mview is getting refreshed.
below is the Alert log file content for that time when the mview is running on destination server.
Fri Sep 23 07:35:47 2011
Thread 1 advanced to log sequence 76794
Current log# 6 seq# 76794 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 07:58:54 2011
Thread 1 advanced to log sequence 76795
Current log# 4 seq# 76795 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 08:23:11 2011
Thread 1 advanced to log sequence 76796
Current log# 5 seq# 76796 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 08:31:03 2011
Thread 1 advanced to log sequence 76797
Current log# 6 seq# 76797 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 08:53:59 2011
Thread 1 advanced to log sequence 76798
Current log# 4 seq# 76798 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 09:14:37 2011
Thread 1 advanced to log sequence 76799
Current log# 5 seq# 76799 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 09:41:47 2011
Thread 1 advanced to log sequence 76800
Current log# 6 seq# 76800 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 10:11:07 2011
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Time: 23-SEP-2011 10:11:07
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 60
nt OS err code: 0
Client address: <unknown>
Fri Sep 23 10:15:41 2011
Thread 1 advanced to log sequence 76801
Current log# 4 seq# 76801 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 10:45:34 2011
Thread 1 advanced to log sequence 76802
Current log# 5 seq# 76802 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 11:10:03 2011
Thread 1 advanced to log sequence 76803
Current log# 6 seq# 76803 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 11:22:38 2011
Thread 1 advanced to log sequence 76804
Current log# 4 seq# 76804 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 11:35:04 2011
Thread 1 advanced to log sequence 76805
Current log# 5 seq# 76805 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 11:50:19 2011
Thread 1 advanced to log sequence 76806
Current log# 6 seq# 76806 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 12:08:11 2011
Thread 1 advanced to log sequence 76807
Current log# 4 seq# 76807 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Fri Sep 23 12:24:49 2011
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Time: 23-SEP-2011 12:24:49
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 60
nt OS err code: 0
Client address: <unknown>
Fri Sep 23 12:26:01 2011
Thread 1 advanced to log sequence 76808
Current log# 5 seq# 76808 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO05.LOG
Fri Sep 23 12:41:20 2011
Thread 1 advanced to log sequence 76809
Current log# 6 seq# 76809 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO06.LOG
Fri Sep 23 12:59:02 2011
Thread 1 advanced to log sequence 76810
Current log# 4 seq# 76810 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AFCCV\REDO04.LOG
Check this and kindly suggest what should i do for the problem resolving.
Regards
Pradeep
|
|
|
|
| Re: Mview is not refreshing [message #524340 is a reply to message #524338] |
Fri, 23 September 2011 03:12 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal/CookieMaster,
where I ran this query now then it is showing the below output.
1* select mview_name,last_refresh_date from dba_mviews
SQL> /
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ --------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS 20-jan-2011 11:44:40
TBL_VOICECHAT_NEW1 23-sep-2011 11:10:41
So I am confuse why this is showing now "23-sep-2011 11:10:41" because in previously it was showing "23-sep-2011 08:28:19"
Regards
Pradeep
|
|
|
|
|
|
|
|
|
|
| Re: Mview is not refreshing [message #524355 is a reply to message #524348] |
Fri, 23 September 2011 04:23 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal,
Thanks for clearing but want to know why this is taking so much time. As I also checked something
SQL> l
1 select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN from dba_jobs
2* where job=90
SQL> /
LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -
23-sep-2011 11:10:36 11:10:36 23-sep-2011 13:36:09 13:36:09 23-sep-2011 13:36:08 13:36:08 470489 N
SQL> spool off
LAST_DATE -
Date that this job last successfully executed
LAST_SEC
Same as LAST_DATE. This is when the last successful execution started.
THIS_DATE
Date that this job started executing (usually null if not executing)
THIS_SEC
Same as THIS_DATE. This is when the last successful execution started.
NEXT_DATE
Date that this job will next be executed
NEXT_SEC
Same as NEXT_DATE. The job becomes due for execution at this time.
TOTAL_TIME
Total wallclock time spent by the system on this job,in seconds
BROKEN
If Y,no attempt is being made to run this job. See dbms_jobq.broken(job).
According to NEXT_DATE this job should executed on specified time and mview should be get refreshed with in 10 min... but now there is 3:00PM and value is not getting changed.
[Updated on: Sat, 24 September 2011 02:28] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Mview is not refreshing [message #524365 is a reply to message #524361] |
Fri, 23 September 2011 05:27 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi John,
Jobs is still running.
and altered the mview using the below command for increasing refreshment type.
alter MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1" REFRESH FASt START WITH sysdate+0 NEXT sysdate+(10/86400*60)
But this command seems like hang state.
suggest what to do ... kill the job first ...or any thing else.
Pradeep
|
|
|
|
|
|
|
|
| Re: Mview is not refreshing [message #524460 is a reply to message #522874] |
Fri, 23 September 2011 23:53 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal,
Last Evening I again recreated the mview and checked for refreshment of the mview. today morning When I checked the mview refreshment time then it is showing "24-sep-2011 00:36:35". My Current Mview Script is
CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+(10/86400*60)
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT "TBL_VOICECHAT"."ANI" "ANI","TBL_VOICECHAT"."CHAT_ID" "CHAT_ID",
"TBL_VOICECHAT"."GENDER" "GENDER",
"TBL_VOICECHAT"."AGE" "AGE","TBL_VOICECHAT"."RECORD_FILE" "RECORD_FILE",
"TBL_VOICECHAT"."SUB_DATE_TIME" "SUB_DATE_TIME",
"TBL_VOICECHAT"."STATUS" "STATUS","TBL_VOICECHAT"."LOGIN_STATUS" "LOGIN_STATUS",
"TBL_VOICECHAT"."BILLING_DATE" "BILLING_DATE",
"TBL_VOICECHAT"."CIRCLE_NAME" "CIRCLE_NAME","TBL_VOICECHAT"."INTEREST" "INTEREST",
"TBL_VOICECHAT"."HOBBIES" "HOBBIES",
"TBL_VOICECHAT"."LANGUAGE" "LANGUAGE","TBL_VOICECHAT"."OLD_ID" "OLD_ID",
"TBL_VOICECHAT"."SUB_FILE" "SUB_FILE",
"TBL_VOICECHAT"."M_ACT" "M_ACT","TBL_VOICECHAT"."LAST_CALL_DATE" "LAST_CALL_DATE",
"TBL_VOICECHAT"."FLAG" "FLAG",
"TBL_VOICECHAT"."SUNSIGN" "SUNSIGN","TBL_VOICECHAT"."PSUNSIGN" "PSUNSIGN",
"TBL_VOICECHAT"."BAL_STATE" "BAL_STATE",
"TBL_VOICECHAT"."TOTALHIT" "TOTALHIT","TBL_VOICECHAT"."NEXT_BILLED_DATE"
"NEXT_BILLED_DATE","TBL_VOICECHAT"."LAST_BILLED_DATE" "LAST_BILLED_DATE",
"TBL_VOICECHAT"."IS_PREPAID" "IS_PREPAID","TBL_VOICECHAT"."DNDFLAG" "DNDFLAG",
"TBL_VOICECHAT"."AMOUNT" "AMOUNT","TBL_VOICECHAT"."VALUE1" "VALUE1"
FROM "AFCCV"."TBL_VOICECHAT"@SERVICEDB1 "TBL_VOICECHAT";
Mview jobs is running
SQL> alter session set nls_date_format='DD-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select mview_name,last_refresh_date from dba_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ --------------------
TBL_VOICECHAT_NEW1 24-sep-2011 00:36:35
1* select job, failures, broken from dba_jobs where lower(what) like '%refresh
%'
SQL> /
JOB FAILURES B
---------- ---------- -
110 0 N
SQL> l
1* select * from dba_jobs_running
SQL> /
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- -------------------- -------- -------------------- -------- ----------
2512 110 0 24-sep-2011 00:36:29 00:36:29 24-sep-2011 05:18:49 05:18:49 0
SQL> SELECT t.used_ublk, t.used_urec
2 FROM v$session s, v$transaction t
3 WHERE s.taddr=t.addr
4 and s.SID =2512;
USED_UBLK USED_UREC
---------- ----------
11162 446368
SQL>
I am not able to understand why this is happening..... first few hours it is running fine after that this looks like a hang stage.
REgards
Pradeep
[Updated on: Sat, 24 September 2011 02:29] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Mview is not refreshing [message #524481 is a reply to message #524476] |
Sat, 24 September 2011 01:16 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi Michal,
According to your reply. When the When I created this mview this takes hardly 10 min to complete. after that I checked the refeshment time till one 1-2 hour it was showing the correct time and refreshing properly.
So How the refreshment time now increased upto 5 hours....
Little bit confusing... can you please tel me something more in this.
Regards
Pradeep S
|
|
|
|
|
|
|
|
|
|
| Re: Mview is not refreshing [message #524495 is a reply to message #524487] |
Sat, 24 September 2011 02:05 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'm sure Michel is right.
Do you know how to check the number of updates made to a table? If you do that regularly, you might find a sudden burst of activity which causes this workload. One way is to query dba_tab_modifications, to get up-to-date figures you have to execute dbms_Stats.flush_database_monitoring_info first. The view is cleared when the table is analyzed.
Applying changes to an MV can be quite slow, do you have any indexes on the container table?
|
|
|
|
| Re: Mview is not refreshing [message #524497 is a reply to message #524495] |
Sat, 24 September 2011 02:40 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
SQL> connect afccv/**********
Connected.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 select table_name,inserts,updates,deletes
2 from user_tab_modifications
3* where table_name='TBL_VOICECHAT'
SQL> /
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TBL_VOICECHAT 33737 248849 27329
SQL>
Source table and destination mview having indexes
SQL> select index_name,index_type from user_indexes where table_name='TBL_VOICECHAT' ;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_STATUS_VOICECHAT NORMAL
IDX_GENDER_VOICECHAT NORMAL
IDX_ANI_VOICECHA2 NORMAL
IDX_ANI_VOICECHAT NORMAL
IDX_M_ACT NORMAL
SQL>
Destination mview server ...
SQL> select index_name from dba_indexes where owner='SMSCHAT' and table_name='TBL_VOICECHAT_NEW1';
INDEX_NAME
------------------------------
IDX_GNELOGAG_VOICENEW1
IDX_LOGIN_STATUS_NEW
SYS_C0034163
IDX_GENDER1
IDX_CIRCLE_NEW1
IDX_CHAT_ID1
6 rows selected.
|
|
|
|
| Re: Mview is not refreshing [message #524499 is a reply to message #524497] |
Sat, 24 September 2011 02:59 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So there has been a lot of DML on your source table. Can you check out the execution plans for the updates and delets on the MV container table? Perhaps your indexes aren't being used. Have you analyzed it recently?
(and by the way, if something I say is helpful, please let me know rather than just posting something without a "thank you")
|
|
|
|
| Re: Mview is not refreshing [message #524500 is a reply to message #524499] |
Sat, 24 September 2011 03:10 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi John,
Table has been analyzd today morning....
SQL> select table_name,last_analyzed from user_tables where table_name='TBL_VOICECHAT';
TABLE_NAME LAST_ANALYZED
------------------------------ --------------------
TBL_VOICECHAT 24-sep-2011 07:55:53
SQL>
And mostly I am reverting back with Thanks. if sometimes I forgot then I apologies next time I'll take care of this.
Thanks
|
|
|
|
| Re: Mview is not refreshing [message #524501 is a reply to message #524500] |
Sat, 24 September 2011 03:15 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Yes, but when was the MV container table analyzed, nit jsut te source table? What I'm trying to get at is whether the CBO could be choosing not to use indexes when locating rows for update or delete because of bad statistics.
|
|
|
|
|
|
| Re: Mview is not refreshing [message #524503 is a reply to message #524502] |
Sat, 24 September 2011 03:31 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi John,
As I checked MV Container Table has not been refreshed from the time created.
select object_name,object_type,created from dba_objects where object_name='TBL_VOICECHAT_NEW1';
OBJECT_NAME OBJECT_TYPE CREATED
------------------ ------------------ -------------------
TBL_VOICECHAT_NEW1 MATERIALIZED VIEW 23-SEP-011 16:44:40
TBL_VOICECHAT_NEW1 TABLE 23-SEP-011 16:37:53
select owner,table_name,last_analyzed from dba_tables
where table_name like 'TBL_VOICECHAT_NEW1';
OWNER TABLE_NAME LAST_ANALYZED
------- ------------------ -------------
SMSCHAT TBL_VOICECHAT_NEW1
Kindly check
thanks
|
|
|
|
| Re: Mview is not refreshing [message #524504 is a reply to message #524503] |
Sat, 24 September 2011 03:35 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Better run dbms_stats against it now! No promises, but the CBO will be relying on stats gathered by optimizer_dynamic_sampling, which will have been done the first time the MV was refreshed. This might slow things down. And then I think you really need to see the exec plans being used to refresh it.
I'm off now, good luck.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 07:06:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
