This discussion is archived
8 Replies Latest reply: Nov 16, 2012 5:32 AM by SomeoneElse RSS

Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW

Blues Breaker Newbie
Currently Being Moderated
Sorry I'm not able to paste anything.

The customer moved their database from 10g to a 11gR2 database.
They created the MV in the new system and now it takes 26 hours to refresh
as opposed to 15 min in the old 10g database.

Just looking for a game plan for troubleshooting.

I know I'm not providing much of anything so please forgive me.

Where would you look first ? Explain plan .. system settings .. ? Let me know.

If you have a good reference then that would work. The docs are not working.


Thanks in advance.

BB
  • 1. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    rp0428 Guru
    Currently Being Moderated
    >
    I know I'm not providing much of anything so please forgive me.
    >
    Sorry - but that's unforgiveable. ;)
    >
    The customer moved their database from 10g to a 11gR2 database.
    >
    Do you mean they upgraded their existing database from 10g to 11gr2? Or do you mean they loaded the data into a new 11gr2 database?

    Unless you provide more information there is no way to provide any specific suggestions. What kind of MV? Are the source and MV on the same server?

    If it is a new DB then there may be missing indexes or constraints. The MV may initially be empty and the first refresh will be a COMPLETE one that will take a lot longer than an incremental.

    Some of the objects or constraints could be invalid.
  • 2. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    Blues Breaker Newbie
    Currently Being Moderated
    Thanks for the help. You're a Saint.
    Do you mean they upgraded their existing database from 10g to 11gr2? Or do you mean they loaded >>the data into a new 11gr2 database?
    No upgrade.They data-pumped from 10g to 11g. Hence the side by side comparison. I should have been more clear.
    Unless you provide more information there is no way to provide any specific suggestions. What kind of >>MV? Are the source and MV on the same server?
    I understand. They're sending the sql to me. The source is possibly elsewhere. I will check.
    Sorry but everyone's gone until tomorrow.
    If it is a new DB then there may be missing indexes or constraints. The MV may initially be empty and >>the first refresh will be a COMPLETE one that will take a lot longer than an incremental.
    That is something that I will check. That might be the key to this whole problem..
    Some of the objects or constraints could be invalid.
    I found a bunch of invalid objects and I'm working to resolve (compile) them.

    That might be it. Some of the MVs are invalid.

    I'll let you know how it turns out. You've been very helpful. Thanks again.


    BB
  • 3. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Also confirm that database statistics on the 11g database are current

    Pl see

    General Materialized View Refresh Performance Tuning Tips [ID 412400.1]

    HTH
    Srini
  • 4. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    SomeoneElse Guru
    Currently Being Moderated
    At some point (don't remember which version) mviews were given the option of atomic_refresh.

    It's true by default which means a complete refresh will do a delete followed by the insert. If it's huge, the delete could take a long time.

    If you change it to false it will do a truncate followed by the insert. Of course the truncate is much faster. But this might not always be acceptable.
  • 5. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    Blues Breaker Newbie
    Currently Being Moderated
    Do you mean they upgraded their existing database from 10g to 11gr2?
    Or do you mean they loaded the data into a new 11gr2 database?
    Yeah I confirmed this morning that they data-pumped the entire database to the 11g host.
    Unless you provide more information there is no way to provide any specific suggestions.
    What kind of MV? Are the source and MV on the same server?
    They do a manual refresh :

    create materialized view (mview_name as select ...)
    dbms_snapshot.refresh(mview_name, 'C')

    The source is the same for both however it is from a place that uses a connection.
    If it is a new DB then there may be missing indexes or constraints.
    The MV may initially be empty and the first refresh will be a COMPLETE
    one that will take a lot longer than an incremental.
    Some of the objects or constraints could be invalid.
    The indexes appear to be the same and the constraints enabled/disabled in the same manner.
    They confirmed that the refresh after the initial is taking way too long compared to before (10g).
    Oddly some of the mviews see no performance degradation in the new environment.
    They have many.



    Thanks for taking the time to think about it. It was much appreciated.

    If I get to the bottom of it I'll let everybody know.

    BB
  • 6. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    Blues Breaker Newbie
    Currently Being Moderated
    The pga_aggregate_target was set to 6GB in the 10g database and only 768MB in the
    11g database .

    Problem solved.

    Thanks for any and all comments.

    They all helped to enlighten me.

    BB
  • 7. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    Blues Breaker Newbie
    Currently Being Moderated
    I thought that the pga was the biggest culprit but I was wrong.

    It may could have made a difference if we were able to increase it dramitically.

    I'm depending on a third party for information however your suggestion had the greatest impact.

    So I added:

    atomic_refresh=false


    The difference was huge !

    Thanks for that info.

    BB
  • 8. Re: Materialized View - Refresh in 10g FAST .. refresh in 11g SLOW
    SomeoneElse Guru
    Currently Being Moderated
    Keeping in mind that sometimes an atomic refresh is necessary.

    It depends on your business requirements.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points