Home » SQL & PL/SQL » SQL & PL/SQL » materialized view issue (oracle 11.2.0.3)
materialized view issue [message #680513] Tue, 19 May 2020 09:20 Go to next message
irashu2001@gmail.com
Messages: 3
Registered: May 2020
Junior Member
1.I am trying to create materialized view on new DB and it’s taking 58 minutes but query used for creating materialized view is taking less than two minutes.

Note : we created materialized view with prebuilt option so it created in few seconds but refreshing in 7 minutes while our expectation is less than two minutes.

2.We are using DB link to execute query which is connecting PROD DB server.(only 83 rows to return each time )

3.It is scheduled to refresh within two minutes and it’s taking 10-15 minutes to refresh.

So anyone please help me to know what could be reason for this while complete query execution time is less than one minutes but when i create Mview it's taking more than 7 minutes to refresh.

Thanks in advance!

Re: materialized view issue [message #680514 is a reply to message #680513] Tue, 19 May 2020 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: materialized view issue [message #680518 is a reply to message #680513] Tue, 19 May 2020 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Query is just reads.
Create MVIEW is reads and writes.
How many rows? How many blocks? Size of rows? Size of blocks? Concurrent load?

I suggest you activate a trace during create mview to see what happens and where Oracle is spending time.

Re: materialized view issue [message #680520 is a reply to message #680518] Tue, 19 May 2020 11:35 Go to previous messageGo to next message
irashu2001@gmail.com
Messages: 3
Registered: May 2020
Junior Member
Thanks for your reply! I provide that info asap.Please also let me know as my Materialized view query is connecting prod db by db link so this trace file would be generated on our prod server or current server on which Materialized view is running!

[Updated on: Tue, 19 May 2020 11:39]

Report message to a moderator

Re: materialized view issue [message #680524 is a reply to message #680520] Tue, 19 May 2020 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You activate the trace in the session which executes the CREATE MVIEW:
exec DBMS_SESSION.SESSION_TRACE_ENABLE;
create mview ...
DBMS_SESSION.SESSION_TRACE_DISABLE;
Re: materialized view issue [message #680553 is a reply to message #680524] Wed, 20 May 2020 23:59 Go to previous messageGo to next message
irashu2001@gmail.com
Messages: 3
Registered: May 2020
Junior Member
due to production restriction i can't share the trace file i am trying to find some alternative way In the meanwhile could you please suggest some key points which i could check myself and still it doesn't work i will check how i can give u trace file.
Thanks once again for you quick reply

[Updated on: Thu, 21 May 2020 00:04]

Report message to a moderator

Re: materialized view issue [message #680554 is a reply to message #680553] Thu, 21 May 2020 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can:
1/ first, use TKPROF to get a trace profile, remove the statements and post it.
2/ then, if not sufficient to diagnose, remove the header and statements in the trace file and post this raw trace file.
Then there are nothing confidential on these posts.

Re: materialized view issue [message #680635 is a reply to message #680554] Wed, 27 May 2020 07:08 Go to previous message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
Find out if your mview can be made into a fast refresh by using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure. Look up on how to use this procedure. If you setup your mview refresh as a fast refresh it will only merge in the changes and nothing else. Your refresh will become MUCH faster
Previous Topic: Return names of bind variables
Next Topic: Audit Database Problem
Goto Forum:
  


Current Time: Thu Jul 09 23:42:53 CDT 2020