3 Replies Latest reply: Jun 27, 2013 12:02 PM by 969952 RSS

    Fast refresh VS Complete refresh

    Nuno
      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
          JustinCave
          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

            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

              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.