This discussion is archived
3 Replies Latest reply: Jun 27, 2013 10:02 AM by 969952 RSS

Fast refresh VS Complete refresh

Nuno Newbie
Currently Being Moderated
Hello Oracle users


I'm using materialized views to replicate data from one BD to another. Basically is a subset of data, based on a date (for instance, records from less than a year ago), and without all the columns (master table has 10 cols, replica has 5, for example).
I know that fast refresh is better for small amounts of data, but my question, as stupid as it may seem is: what is "small amounts"? 100 records? 1000? 10000?
This is constraining the type of refresh i'll use; the complete one takes a while, but aparently the fast will be worse. By the way, the table in question takes a few thousand rows each day...

thank you for any help
  • 1. Re: Fast refresh VS Complete refresh
    Justin Cave Oracle ACE
    Currently Being Moderated
    A fast refresh (assuming you are actually doing a fast refresh) just transfers the incremental changes to the destination database. If you are only generating a few thousand rows, assuming the rows are ridiculously large, a fast refresh should be quite fast.

    There are three basic ways, from a performance standpoint, for a materialized view to be refreshed
    - A non-transactional full refresh truncates the materialized view, then does a direct-path insert
    - A transactional full refresh deletes the materialized view and then does a regular insert
    - An incremental (fast) refresh applies the changes doing inserts and updates as appropriate

    Exactly where a full refresh becomes faster than an incremental refresh depends, among other things, on
    - whether you are doing a transactional full refresh
    - what proportion of rows experience changes and what portion of changes are updates since applying updates is slower than applying inserts
    - what the bottlenecks in your system are

    Since you are replicating between databases, I would expect that the cost of physically transferring the data over the network would overwhelm the cost of applying the changes such that an incremental refresh would make sense unless the vast majority of rows (pulling a number out of thin air, 70%) changed between refreshes. If you have a very fast network and a lot of load on the disk, however, that proportion might well be smaller, maybe more in the 40-50% range.

    Rather than relying on my guesses, though, you'd be much better served doing the benchmarks in your environment.

    Justin
  • 2. Re: Fast refresh VS Complete refresh
    969952 Newbie
    Currently Being Moderated

    Thanks you. But in SQL queries multiple join conditions and having Select statements having Complex queries. So we can't do FAST refresh.Please correct me. So can you please provide me what are actions I can perform to optimize the queries  apart from partitions? Please provide your suggestions.

     

    Thanks.

  • 3. Re: Fast refresh VS Complete refresh
    969952 Newbie
    Currently Being Moderated

    Hi Justin,

     

    Please have a look into the below explain plan for  one of my MV and let me how can I reduce the CPU cost?

    {code}

     

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                             | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                      |                               |   124 | 10664 |       |  1018   (5)| 00:00:13 |        |      |            |

    |   1 |  PX COORDINATOR FORCED SERIAL         |                               |       |       |       |            |          |        |      |            |

    |   2 |   PX SEND QC (RANDOM)                 | :TQ10022                      |   124 | 10664 |       |  1018   (5)| 00:00:13 |  Q1,22 | P->S | QC (RAND)  |

    |   3 |    HASH GROUP BY                      |                               |   124 | 10664 |       |  1018   (5)| 00:00:13 |  Q1,22 | PCWP |            |

    |   4 |     PX RECEIVE                        |                               |   124 | 10664 |       |  1018   (5)| 00:00:13 |  Q1,22 | PCWP |            |

    |   5 |      PX SEND HASH                     | :TQ10021                      |   124 | 10664 |       |  1018   (5)| 00:00:13 |  Q1,21 | P->P | HASH       |

    |   6 |       HASH GROUP BY                   |                               |   124 | 10664 |       |  1018   (5)| 00:00:13 |  Q1,21 | PCWP |            |

    |   7 |        VIEW                           |  A_VIEW                       |    73M|  6004M|       |  1005   (4)| 00:00:13 |  Q1,21 | PCWP |            |

    |   8 |         UNION-ALL                     |                               |       |       |       |            |          |  Q1,21 | PCWP |            |

    |*  9 |          HASH JOIN                    |                               |    69M|  2522M|       |   474   (2)| 00:00:06 |  Q1,21 | PCWP |            |

    |  10 |           BUFFER SORT                 |                               |       |       |       |            |          |  Q1,21 | PCWC |            |

    |  11 |            PX RECEIVE                 |                               |    66 |   528 |       |     1   (0)| 00:00:01 |  Q1,21 | PCWP |            |

    |  12 |             PX SEND BROADCAST         | :TQ10000                      |    66 |   528 |       |     1   (0)| 00:00:01 |        | S->P | BROADCAST  |

    |  13 |              INDEX FULL SCAN          | COUNT_UIDX                    |    66 |   528 |       |     1   (0)| 00:00:01 |        |      |            |

    |* 14 |           HASH JOIN                   |                               |    62M|  1780M|       |   471   (2)| 00:00:06 |  Q1,21 | PCWP |            |

     

     

    |  19 |            PX BLOCK ITERATOR          |                               |    62M|  1424M|       |   468   (1)| 00:00:06 |  Q1,21 | PCWC |            |

    |  20 |             TABLE ACCESS FULL         | TRANSACTIONS                  |    62M|  1424M|       |   468   (1)| 00:00:06 |  Q1,21 | PCWP |            |

    |* 21 |          HASH JOIN                    |                               |  2531K|   125M|       |   475   (2)| 00:00:06 |  Q1,21 | PCWP |            |

     

     

    |* 26 |           HASH JOIN                   |                               |  2263K|    94M|       |   474   (2)| 00:00:06 |  Q1,21 | PCWP |            |

     

     

    |* 31 |            HASH JOIN                  |                               |  7841K|   284M|       |   472   (2)| 00:00:06 |  Q1,21 | PCWP |            |

     

    |* 35 |                TABLE ACCESS FULL      | CONVERSIONS                   |    16 |   224 |       |     2   (0)| 00:00:01 |  Q1,04 | PCWP |            |

    |  36 |             PX BLOCK ITERATOR         |                               |    62M|  1424M|       |   468   (1)| 00:00:06 |  Q1,21 | PCWC |            |

    |* 37 |              TABLE ACCESS FULL        | TRANSACTIONS                  |    62M|  1424M|       |   468   (1)| 00:00:06 |  Q1,21 | PCWP |            |

     

    |  41 |           NESTED LOOPS                |                               |   606K|    36M|       |    51  (36)| 00:00:01 |  Q1,21 | PCWP |            |

    |  42 |            VIEW                       | TRANSACTIONS_VIEW             |   391K|    18M|       |    50  (36)| 00:00:01 |  Q1,21 | PCWP |            |

     

    {code}

     

    Thanks.

Incoming Links

Legend

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