OraFAQ Forum: SQL & PL/SQL » materialized view issue

Home » SQL & PL/SQL » SQL & PL/SQL » materialized view issue (oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

materialized view issue [message #680513]

Tue, 19 May 2020 09:20

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!

Report message to a moderator

Re: materialized view issue [message #680514 is a reply to message #680513]

Tue, 19 May 2020 09:57

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: materialized view issue [message #680518 is a reply to message #680513]

Tue, 19 May 2020 11:23

Michel Cadot
Messages: 68421
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.

Report message to a moderator

Re: materialized view issue [message #680520 is a reply to message #680518]

Tue, 19 May 2020 11:35

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

Michel Cadot
Messages: 68421
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;

Report message to a moderator

Re: materialized view issue [message #680553 is a reply to message #680524]

Wed, 20 May 2020 23:59

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

Michel Cadot
Messages: 68421
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.

Report message to a moderator

Re: materialized view issue [message #680635 is a reply to message #680554]

Wed, 27 May 2020 07:08

Bill B
Messages: 1971
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

Report message to a moderator

Previous Topic:	Return names of bind variables
Next Topic:	Audit Database Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 02 07:23:03 CDT 2023