Forum Stats

  • 3,827,843 Users
  • 2,260,829 Discussions
  • 7,897,397 Comments

Discussions

Materialized View Create Fast But Refresh Slow on Oracle 19c

User_BJSOY
User_BJSOY Member Posts: 6 Green Ribbon

Hi There,

Good day!

Recently I am creating several materialized views on Oracle 19c which all base tables come from one single remote database through dblink, and all of the MVs schedule refresh everyday.

For most of the MVs I add some hints like use_hash to reduce the refresh time, but the hint does not work on one of them, the strange thing is the mv only take no more than 10 seconds for creation, but it takes hours even days to do refresh, there are only no more than 1 thousand records. Someone saying use hint optimizer_features_enable('9.0.0') will solve the problem, I copied the insert into ... select ... statement from the MV refresh execution plan and do an experimental on a table, seems it does work, but when I am trying to add the hint on the MV, it does not work at all, I also tried to add clause alter session set optimizer_features_enable='9.0.0' in the refresh job, it does not work too.

Anyone have idea to solve this question? There are some work around list below, but still would like to know whether can make the MV refresh fast directly. 😅

  1. Instead do fresh, re-create the MV everyday.
  2. Create a staging table, then make MV refresh data from it.
  3. Do fast refresh, actually I have not make a try, as we are not going to create any MV log table on remote database.

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,979 Blue Diamond
    edited Sep 29, 2021 7:15PM Answer ✓

    At first glance, this looks like a case of the example I reported a few years ago (and confirmed recently as still present in 19c): https://jonathanlewis.wordpress.com/2016/11/14/distributed-trap/

    All the tables in the query are remote, but you've also selected the local systimestamp - so the query is distributed. Oracle manages to run this as "fully remote" when it's DDL, but has to drive it locally when it is DML - hence the separate appearance of all those remote table in the plan.

    How much control do you have over the setup - it looks as if you've pulled the code from the database rather than having direct access to the source, will you be allowed to change the way the MV is set up?

    Things you can do to test:

    Create a new MV which doesn't have the systimestamp in it (we'll put that back later when we've checked the hypothesis), and see if a refresh of that MV runs quickly.

    Create an MV including the systimstamp, but use a variation of the strategy I posted in comment #4 to the blog post - change the code to something like:


    select
            (select systimestamp from dual),
            v.*
    from    (
            select /*+ no_merge */
                    --- the rest of your original query
            ) v
            
    

    The intent is that adding the no_merge hint should make the view non-mergeable, so the optimizer will send the view text to the remote database for optimising, and only worry about executing the call to dual for local systimestamp when the data is returning from the remote database. (The plan should look like the one at the end of comment #4 if this works.)

    Regards

    Jonathan Lewis


    UPDATE: the "distinct" should be included in the text of the non-mergeable view.

    User_BJSOY

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,427 Gold Trophy

    Fast refresh does not work for any materialized view. If it's a MV based on one table, then fast refresh may work if you have first created a materialized view log. If it is based on several tables joined and it also has some group functions, then fast refresh is impossible.

    Instead of re-creating MV, better use a table that you truncate then insert select into it. Thus, you won't have all the procedures referencing the MV decompiled when you drop the MV and recreate it.

    This thing with optimizer features 9.0 sounds really bad because that was many years ago and the optimizer has evolved pretty much since then. Were it to use optimizer features 12, I'd say OK if you just migrated from Oracle 12 database...

  • Paulzip
    Paulzip Member Posts: 8,695 Blue Diamond
    edited Sep 27, 2021 4:58PM

    Why would you randomly change an optimiser hint on a hunch, especially one which downgrades to an obsolete version? Also, use_hash and hints in general are not some magic go fast bullet. I've rarely needed to use hints in MViews to get good performance.

    What is the slow MView query doing?

    What's the hint free execution plan on it if you pull it out?

    Are you doing an atomic refresh?

    If you have no MView log, you won't be getting a fast refresh anyway, it'll be a full refresh. If I were in your situation I'd enable trace then refresh and take a look at the trace files.

  • User_BJSOY
    User_BJSOY Member Posts: 6 Green Ribbon

    Thanks BEDE for the suggestion, I was doing some practice on mv fast refresh, start from creating mv log, even we won't use it for now.

  • User_BJSOY
    User_BJSOY Member Posts: 6 Green Ribbon

    Thanks Paulzip, I had one trace file for this mv before, it's kind long and I have no clue what I can get from it after reading it, maybe I should take time to get familiar with the trace & trace file thing.

    Regard what the slow MView query doing? The strange thing is run the select statement is fast, create the MView is fast, only when do MView refresh it take significantly long time.

    The atomic refresh impact less on the refresh duration.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,979 Blue Diamond
    edited Sep 29, 2021 6:57PM

    If you showed us the statement that created the MW that might give us some clues about the problem, Better still if you also showed us the executon plan for "create table as select ...." (if it takes the few seconds that the MV creation would take) and for "insert as select ..."

    There are cases where distributed DML (e.g. insert as select) won't use the same execution path as distributed DDL (e.g. create as select). I think that generally this is result of gradual enhancements (or bug fixes) in the code. For example it used to be the case that a distributed "create as select" would use a Bloom filter in a hash join but the equivalent "insert as select" would not even though the execution plan was the same in every other respect. (I think that one was fixed some time in 12c, though).

    Seeing the statement and the plans may remind someone of a relevant case while a general "it's very slow" won't.

    You mentiond that there are only 1,000 rows - is that just the MV itself: how does that compare with the tables in the query that creates the MVs.


    Regards

    Jonathan Lewis

  • User_BJSOY
    User_BJSOY Member Posts: 6 Green Ribbon
    edited Sep 29, 2021 6:22AM

    Hi Jonathan, attach the statement, create...as select... execution plan and the insert into...select execution plan, would you give suggestion based on them? Thank you.

    I've tried add driving_site hints on this query before, but seems the execution plan haven't been impacted. Not sure whether the hint had been added correctly.


  • BEDE
    BEDE Oracle Developer Member Posts: 2,427 Gold Trophy

    The db-link is a killer. And the worst thing is the not exists.

    The main problem is how that executes on the remote database.

    I'd rather create a view (not materialized) or a stored procedure returning sys_refcursor or a pipelined function on the remote DB. If that is not possible, then try to produce several materialized views of the remote tables, which may also have some additional indexes, and then join them in the local database.

    And now it is clear to me why fast refresh is not possible. How could the local database "know" what happens every second in the remote database?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,979 Blue Diamond
    edited Sep 29, 2021 7:15PM Answer ✓

    At first glance, this looks like a case of the example I reported a few years ago (and confirmed recently as still present in 19c): https://jonathanlewis.wordpress.com/2016/11/14/distributed-trap/

    All the tables in the query are remote, but you've also selected the local systimestamp - so the query is distributed. Oracle manages to run this as "fully remote" when it's DDL, but has to drive it locally when it is DML - hence the separate appearance of all those remote table in the plan.

    How much control do you have over the setup - it looks as if you've pulled the code from the database rather than having direct access to the source, will you be allowed to change the way the MV is set up?

    Things you can do to test:

    Create a new MV which doesn't have the systimestamp in it (we'll put that back later when we've checked the hypothesis), and see if a refresh of that MV runs quickly.

    Create an MV including the systimstamp, but use a variation of the strategy I posted in comment #4 to the blog post - change the code to something like:


    select
            (select systimestamp from dual),
            v.*
    from    (
            select /*+ no_merge */
                    --- the rest of your original query
            ) v
            
    

    The intent is that adding the no_merge hint should make the view non-mergeable, so the optimizer will send the view text to the remote database for optimising, and only worry about executing the call to dual for local systimestamp when the data is returning from the remote database. (The plan should look like the one at the end of comment #4 if this works.)

    Regards

    Jonathan Lewis


    UPDATE: the "distinct" should be included in the text of the non-mergeable view.

    User_BJSOY
  • User_BJSOY
    User_BJSOY Member Posts: 6 Green Ribbon

    Indeed, the db-like is a killer.

    If my team do not insist to tune the MV, I've adopt the work around like using staging view several days ago. Luckily Jonathan's no_merge hint post works, I think this solution would satisfy them.

    Thank you BEDE for your earnest suggestions.

  • User_BJSOY
    User_BJSOY Member Posts: 6 Green Ribbon

    The no_merge hint works!

    Actually I've tried this before, but I did not figured out the local systimestamp field makes this hint fail. I tried to write the query as below, for now it does work, I will monitor the MV refresh log to see will there are any exceptions.

    SELECT systimestamp, v.*
      FROM (
            my original query with /*+ no_merge */
      ) v;
    

    Thank you Jonathan for your earnest suggestions, they are helped me a lot.