Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    Unanswered: Creating a Mat view takes too much time

    Hi folks.

    I'm trying to create a materialized view in Oracle 9i, using this SQL

    CREATE MATERIALIZED VIEW BOLINF.XXAE_DEMANTRA_DATA_MV
    BUILD IMMEDIATE
    USING INDEX
    REFRESH FORCE ON DEMAND
    AS SELECT
    xxxxxxxxxx

    At first I had problems with TEMP Tablespace, which ran out of space.
    So I added another tempfile of 2GB.
    Now it's been running for about 1 hours and it hasn`t finished yet.
    (Query returns about 700.000 rows).
    alert.log shows archiving and checkpointing.

    There is enough free space on temp TS.

    SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

    TABLESPACE_NAME BYTES_USED BYTES_FREE
    ------------------------------ ---------- ----------
    TEMP 131072 2147352576
    TEMP 1153433600 0

    bytes free does not change while running the CREATE MAT View statement

    The question is: how can I know if it will take 8 hours more or the session is hanged?

    How can I do it faster?
    The query perhaps is not optimized, however as a DBA I have to find a solution and create that MV :-)

    Can I see any log?
    Can I query any dba view??

    Thanks in advance,
    Diego.-

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) Obtain the Session ID (SID) of the session creating the MV
    2) Repeatedly SELECT * FROM V$SESS_IO WHERE SID = <value_from_#1>
    The counts should be increasing when progress is being made.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You should also check the definition of the mat view. Especially if it is including a join operation. Ensure that the join is proper and you are accessing indexes where available...
    This is an assumption since the mat view is refresh on demand and not on commit. I am assuming that it is a "complex" query and not valid for on commit refresh. Check the execution plan of the underlying query

  4. #4
    Join Date
    Nov 2007
    Posts
    1
    thanks guys i wanned to do it as well

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •