Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a materialized view in my Oracle 10g database that I need to rebuild. So I'm doing something like this...

drop materialized view ViewName;

CREATE MATERIALIZED VIEW ViewName
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT ....
FROM ....

This creation of the materialized view takes 12 minutes to run in our DEV schema and at one time ran 5+ days in our QA schema without completing. There are no locks on the tables when running the script in the QA schema. Both of the schemas arre also on the same table space. The DB definition is exactly the same.

Any ideas on why it is taking so long to execute the DDL statement to create the MV in our QA schema? Or any ideas what's preventing it from being executed?

Thanks

share|improve this question
    
What is the session waiting on? Have you traced the session in QA to see what is taking time? –  Justin Cave May 26 '11 at 15:32
    
The current statement is the CREATE MATERIALIZED VIEW DDL statement –  contactmatt May 26 '11 at 15:58
    
But what are the current wait events? What is the query plan? –  Justin Cave May 26 '11 at 16:08
    
Are you changing the MV when you rebuild it? Otherwise you can force a refresh with DBMS_MVIEW.REFRESH. –  Jack Douglas May 26 '11 at 18:04
    
Yea, I'm modifying the select statement by adding NVLs around some columns. –  contactmatt May 26 '11 at 18:12
show 1 more comment

1 Answer

Start by focusing on the query used by the materialized view. Examine why the query is running slow and you will solve why the materialized view creation is running slow. If this is the case it may require another question on the query itself. In the unlikely event that the queries run in the same amount of time, let us know.

share|improve this answer
    
I ended up doing a rebuild of my DEV database with my QA data and structure and now both queries are the same, slow speed. –  contactmatt May 27 '11 at 20:24
1  
Did you keep a copy of the query plan from dev, when it was fast? –  Gaius Jul 5 '12 at 21:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.