Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: Create Materialized view taking long time

    Hi,

    We have a cronjob that creates a MV with complex unions every Friday. It is running for 30 to 40 hours every time it gets recreated. Any ideas to improve this ?

    Here are the details.
    Environment: Oracle 9.2.0.6
    OS: SunOS 5.0 SPARC 64-bit FireSystem

    Create view options we used are
    CREATE MATERIALIZED VIEW TPCOM.PO_DETAIL_P_DIRECT_MV
    TABLESPACE TPAPP1
    NOCACHE
    NOLOGGING
    --PARALLEL 2
    BUILD IMMEDIATE
    --REFRESH COMPLETE ON DEMAND
    --START WITH SYSDATE
    --WITH PRIMARY KEY
    NEVER REFRESH
    AS
    --
    --

    Any help is appreciated.

    Thanks
    -Bheem
    Last edited by bheemsen; 09-26-07 at 21:05.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    POST EXPLAIN PLAN

    ALTER SESSION SET SQL_TRACE=TRUE
    -- & run results through TKPROF
    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
    May 2002
    Posts
    62
    Last weekend I used the SQL_TRACE=TRUE for the refresh of MV.

    The tkprof output is attached. I see the following error in trace file, but Oracle says I can safely ignore this error.

    We are having issues with only very slow performance.

    Error encountered: ORA-10980

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The problem SQL which took the MAJORITY of the time produced these stats:
    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.78       0.76          0          0          0           0
    Execute      1  10694.00   12052.04   21858472   16654284       2265     1846617
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2  10694.78   12052.81   21858472   16654284       2265     1846617
    So the SQL that is just above this section is what needs to be tuned.
    IMO, all other SQL in the run can be ignored.

    12052 seconds is just under 3.5 hours which I have no explanation as to why you report run times of 30 - 40 HOURS!
    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.

  5. #5
    Join Date
    May 2002
    Posts
    62
    anacedent,

    Thanks for your reply. Fortunately last weekend, the MV recreate took only 3 hours. Before that, the same create statement took neary 35 hours. So it is happening sometimes only. I left the SQL_TRACE=TRUE to stay. I will post again the trace file, whenever the query takes unusual time.

    At this time, do you suggest any improvements that can be made.

    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Nobody that I know or have ever heard of can tune SQL simply by looking at it.

    What I recall see in the output from TKPROF was a big, ugly SQL that was a series of UNIONs

    Please review my initial response!
    POST EXPLAIN PLAN
    (now for each piece of the UNIONed SQL discussed above.

    If tuning was simple, easy or straightforward, it would be done by a program.
    http://www.orafaq.com/forum/t/84315/74940/
    The above URL provides suggestions as to what might help you reduce run time.


    >So it is happening sometimes only.
    Then the root cause may not be in the PL/SQL code itself which is being traced.
    I doubt the code changes week to week, but it appears that data changes.
    A possibility may either sheer volume of data or a skew in the data values rendering the statistics to be sub-optimal.
    Last edited by anacedent; 10-01-07 at 19:53.
    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.

Posting Permissions

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