2

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

6
  • What is the session waiting on? Have you traced the session in QA to see what is taking time? Commented May 26, 2011 at 15:32
  • The current statement is the CREATE MATERIALIZED VIEW DDL statement Commented May 26, 2011 at 15:58
  • But what are the current wait events? What is the query plan? Commented May 26, 2011 at 16:08
  • Are you changing the MV when you rebuild it? Otherwise you can force a refresh with DBMS_MVIEW.REFRESH. Commented May 26, 2011 at 18:04
  • Yea, I'm modifying the select statement by adding NVLs around some columns. Commented May 26, 2011 at 18:12

1 Answer 1

4

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.

2
  • 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. Commented May 27, 2011 at 20:24
  • 1
    Did you keep a copy of the query plan from dev, when it was fast?
    – Gaius
    Commented Jul 5, 2012 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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