Slow MV Refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Slow MV Refresh

  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Slow MV Refresh

    I'm just getting back to the Oracle world, been a SQL DBA for the last 16 years, so be gentle.
    We have MVs that get refreshed nightly from Oracle On Demand data. Within the last week the refreshes have gone from 3 hours to 20 hours. We don't see a massive increase in data, there are no errors in the logs and pings are returning in a reasonable amount of time <200ms. The only thing we see is the following message in Enterprise Manager:
    Metrics "Database Time Spent Waiting (%)" is at 47.91113 for event class "Network"

    As the pings look to return fast enough, I'm not sure where to go with this message.
    Any suggestions would be appreciated.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    There could be a huge difference in refresh time depending on whether you are doing an incremental refresh and a full refresh. A full refresh will truncate the materialized view and completly rebuild it, compared with just updating rows that have changed. To do an incremental refresh you need to make sure that the underlying tables have log files, if something happened to one of the log files, then the refresh might go from incremental to full. Just a hunch.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    The refreshes are all full refreshes. I asked about incremental and why it wasn't being done, the answer I received was that they looked into it and it might take longer.
    The only reason I could think that it would take longer is on how DB Link works.
    In SQL it depends on the type of connection as to where the processing takes place. One way would send the query to the source where it would process and bring back the results. The other way would bring back ALL the data from the source to the target and then do the select, writing only the requested data. But obviously it wouldn't speed things up if all the data had to come back anyway.
    Can anyone confirm how the DB Link works?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    It seems as though things were good, then something changed. Figuring out what changed and why is likely to help you get back to where performance was.
    this space intentionally left blank

  5. #5
    Join Date
    Jan 2012
    Posts
    3
    Agreed.
    The problem is I'm being told nothing changed. The server is the same, the queries are the same, everything I question is the same, or so I'm told.
    I'm at a loss to explain the difference.
    Coming back to Network wait status. As I read it, that status is checked every minute and will alert when it hits a default threshold of 30. But what exactly does the alert mean, other than the obvious its over 30?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by pjcwik View Post
    I'm just getting back to the Oracle world, been a SQL DBA for the last 16 years, so be gentle.
    We have MVs that get refreshed nightly from Oracle On Demand data. Within the last week the refreshes have gone from 3 hours to 20 hours. We don't see a massive increase in data, there are no errors in the logs and pings are returning in a reasonable amount of time <200ms. The only thing we see is the following message in Enterprise Manager:
    Metrics "Database Time Spent Waiting (%)" is at 47.91113 for event class "Network"

    As the pings look to return fast enough, I'm not sure where to go with this message.
    Any suggestions would be appreciated.
    200ms is an awfully high latency on the network.

    Have your tracedroute to check where is the bottleneck? how many hops? is the network pipe based on public internet?

    Could you please do a tnsping?... you want to see tnsping returning a value under 10ms.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width