4 Replies Latest reply: Feb 7, 2013 4:04 AM by MattSk RSS

    query performance on materialized view vs master tables

    MattSk
      Hi,

      I am afraid of strange behavior in db, on my master tables UDBMOVEMENT_ORIG(26mil.rows) and UDBIDENTDATA_ORIG(18mil.rows) is created materialized view TMP_MS_UDB_MV (UDBMOVEMENT is synonym to this object) which meets some default conditions and join condition on these master tables. MV got about 12milions rows. I created MV to query not so huge objects, MV got 3GB, master tables toghether 12GB. But I don't understand that even physical reads and consistent gets are less on MV than on master tables, the final execution time is shorter on master tables. See my log below.

      Why?

      Thanks for answers.


      SQL> set echo on
      SQL> @flush
      SQL> alter system flush buffer_cache;

      System altered.

      Elapsed: 00:00:00.20
      SQL> alter system flush shared_pool;

      System altered.

      Elapsed: 00:00:00.65
      SQL> SELECT
      2 UDBMovement.zIdDevice, UDBMovement.sDevice, UDBMovement.zIdLocal, UDBMovement.sComputer, UDBMovement.tActionTime, UDBIdentData.sCardSubType, UDBIdentData.sCardType, UDBMovement.cEpan, UDBMovement.cText, UDBMovement.lArtRef, UDBMovement.sArtClassRef, UDBMovement.lSequenz, UDBMovement.sTransMark, UDBMovement.lBlock, UDBMovement.sTransType, UDBMovement.lGlobalID, UDBMovement.sFacility, UDBIdentData.sCardClass, UDBMovement.lSingleAmount, UDBMovement.sVAT, UDBMovement.lVATTot, UDBIdentData.tTarifTimeStart, UDBIdentData.tTarifTimeEnd, UDBIdentData.cLicensePlate, UDBIdentData.lMoneyValue, UDBIdentData.lPointValue, UDBIdentData.lTimeValue, UDBIdentData.tProdTime, UDBIdentData.tExpireDate
      3 FROM UDBMOVEMENT_orig UDBMovement, Udbidentdata_orig UDBIdentData
      4 WHERE
      5 UDBMovement.lGlobalId = UDBIdentData.lGlobalRef(+) AND UDBMovement.sComputer = UDBIdentData.sComputer(+)
      6 AND UDBMovement.sTransType > 0 AND UDBMovement.sDevice < 1000 AND UDBMovement.sDevice>= 0 AND UDBIdentData.sCardType IN (2) AND (bitand(UDBMovement.sSaleFlag,1) = 0 AND bitand(UDBMovement.sSaleFlag,4) = 0) AND UDBMovement.sArtClassRef < 100
      7 AND UDBMovement.tActionTime >= TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.25 AND UDBMovement.tActionTime < TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.5
      8 ORDER BY tActionTime, lBlock, lSequenz;

      4947 rows selected.

      Elapsed: 00:00:15.84

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1768406139

      ------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
      ------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 7166 | 1238K| | 20670 (1)| 00:04:09 |
      | 1 | SORT ORDER BY | | 7166 | 1238K| 1480K| 20670 (1)| 00:04:09 |
      | 2 | NESTED LOOPS | | | | | | |
      | 3 | NESTED LOOPS | | 7166 | 1238K| | 20388 (1)| 00:04:05 |
      |* 4 | TABLE ACCESS BY INDEX ROWID| UDBMOVEMENT_ORIG | 7142 | 809K| | 7056 (1)| 00:01:25 |
      |* 5 | INDEX RANGE SCAN | IDX_UDBMOVARTICLE | 10709 | | | 61 (0)| 00:00:01 |
      |* 6 | INDEX UNIQUE SCAN | UDBIDENTDATA_PRIM | 1 | | | 1 (0)| 00:00:01 |
      |* 7 | TABLE ACCESS BY INDEX ROWID | UDBIDENTDATA_ORIG | 1 | 61 | | 2 (0)| 00:00:01 |
      ------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      4 - filter("UDBMOVEMENT"."STRANSTYPE">0 AND "UDBMOVEMENT"."SDEVICE"<1000 AND
      BITAND("SSALEFLAG",1)=0 AND "UDBMOVEMENT"."SDEVICE">=0 AND BITAND("UDBMOVEMENT"."SSALEFLAG",4)=0)
      5 - access("UDBMOVEMENT"."TACTIONTIME">=TO_DATE(' 2011-05-05 06:00:00', 'syyyy-mm-dd
      hh24:mi:ss') AND "UDBMOVEMENT"."TACTIONTIME"<TO_DATE(' 2011-05-05 12:00:00', 'syyyy-mm-dd
      hh24:mi:ss') AND "UDBMOVEMENT"."SARTCLASSREF"<100)
      filter("UDBMOVEMENT"."SARTCLASSREF"<100)
      6 - access("UDBMOVEMENT"."LGLOBALID"="UDBIDENTDATA"."LGLOBALREF" AND
      "UDBMOVEMENT"."SCOMPUTER"="UDBIDENTDATA"."SCOMPUTER")
      7 - filter("UDBIDENTDATA"."SCARDTYPE"=2)


      Statistics
      ----------------------------------------------------------
      543 recursive calls
      0 db block gets
      84383 consistent gets
      4485 physical reads
      0 redo size
      533990 bytes sent via SQL*Net to client
      3953 bytes received via SQL*Net from client
      331 SQL*Net roundtrips to/from client
      86 sorts (memory)
      0 sorts (disk)
      4947 rows processed

      SQL> @flush
      SQL> alter system flush buffer_cache;

      System altered.

      Elapsed: 00:00:00.12
      SQL> alter system flush shared_pool;

      System altered.

      Elapsed: 00:00:00.74
      SQL> SELECT UDBMovement.zIdDevice, UDBMovement.sDevice, UDBMovement.zIdLocal, UDBMovement.sComputer, UDBMovement.tActionTime, UDBMovement.sCardSubType, UDBMovement.sCardType, UDBMovement.cEpan, UDBMovement.cText, UDBMovement.lArtRef, UDBMovement.sArtClassRef, UDBMovement.lSequenz, UDBMovement.sTransMark, UDBMovement.lBlock, UDBMovement.sTransType, UDBMovement.lGlobalID, UDBMovement.sFacility, UDBMovement.sCardClass, UDBMovement.lSingleAmount, UDBMovement.sVAT, UDBMovement.lVATTot, UDBMovement.tTarifTimeStart, UDBMovement.tTarifTimeEnd, UDBMovement.cLicensePlate, UDBMovement.lMoneyValue, UDBMovement.lPointValue, UDBMovement.lTimeValue, UDBMovement.tProdTime
      2 FROM UDBMOVEMENT WHERE
      3 UDBMovement.sTransType > 0 AND UDBMovement.sDevice < 1000 AND UDBMovement.sDevice>= 0 AND UDBMovement.sCardType IN (2) AND (bitand(UDBMovement.sSaleFlag,1) = 0 AND bitand(UDBMovement.sSaleFlag,4) = 0) AND UDBMovement.sArtClassRef < 100
      4 AND UDBMovement.tActionTime >= TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.25
      5 AND UDBMovement.tActionTime < TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.5 ORDER BY tActionTime, lBlock, lSequenz;

      4947 rows selected.

      Elapsed: 00:00:26.46

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3648898312

      -----------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 2720 | 443K| 2812 (1)| 00:00:34 |
      | 1 | SORT ORDER BY | | 2720 | 443K| 2812 (1)| 00:00:34 |
      |* 2 | MAT_VIEW ACCESS BY INDEX ROWID| TMP_MS_UDB_MV | 2720 | 443K| 2811 (1)| 00:00:34 |
      |* 3 | INDEX RANGE SCAN | EEETMP_MS_ACTTIMEDEVICE | 2732 | | 89 (0)| 00:00:02 |
      -----------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("UDBMOVEMENT"."STRANSTYPE">0 AND BITAND("UDBMOVEMENT"."SSALEFLAG",4)=0 AND
      BITAND("SSALEFLAG",1)=0 AND "UDBMOVEMENT"."SARTCLASSREF"<100)
      3 - access("UDBMOVEMENT"."TACTIONTIME">=TO_DATE(' 2011-05-05 06:00:00', 'syyyy-mm-dd
      hh24:mi:ss') AND "UDBMOVEMENT"."SDEVICE">=0 AND "UDBMOVEMENT"."SCARDTYPE"=2 AND
      "UDBMOVEMENT"."TACTIONTIME"<TO_DATE(' 2011-05-05 12:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
      "UDBMOVEMENT"."SDEVICE"<1000)
      filter("UDBMOVEMENT"."SCARDTYPE"=2 AND "UDBMOVEMENT"."SDEVICE"<1000 AND
      "UDBMOVEMENT"."SDEVICE">=0)


      Statistics
      ----------------------------------------------------------
      449 recursive calls
      0 db block gets
      6090 consistent gets
      2837 physical reads
      0 redo size
      531987 bytes sent via SQL*Net to client
      3953 bytes received via SQL*Net from client
      331 SQL*Net roundtrips to/from client
      168 sorts (memory)
      0 sorts (disk)
      4947 rows processed

      SQL> spool off

      Edited by: MattSk on Feb 4, 2013 2:20 PM
        • 1. Re: query performance on materialized view vs master tables
          JustinCave
          Well, there are indexes on the base tables that are being used by the first query. The second query is doing a full table scan of the materialized view. That implies that the indexes are useful.

          You could potentially create some indexes on the materialized view for whatever criteria are most selective. My guess would be that tActionTime would be one of the columns that it would make sense to index-- it's not obvious to me how selective any of the other predicates are likely to be.

          Justin
          • 2. Re: query performance on materialized view vs master tables
            rp0428
            >
            The second query is doing a full table scan of the materialized view.
            >
            What are you basing that statement on?

            I don't see any full table scan in the plan for the second query. All I see is this
            >
            * 2 MAT_VIEW ACCESS BY INDEX ROWID TMP_MS_UDB_MV 2720 443K 2811 (1) 00:00:34
            • 3. Re: query performance on materialized view vs master tables
              JustinCave
              rp0428 wrote:
              >
              The second query is doing a full table scan of the materialized view.
              >
              What are you basing that statement on?
              Apparently, hallucinations. Sorry about that.

              Justin
              • 4. Re: query performance on materialized view vs master tables
                MattSk
                I have added some tkprof outputs on MV and master tables:
                ********************************************************************************

                SELECT tmp_ms_udb_mv.zIdDevice, tmp_ms_udb_mv.sDevice, tmp_ms_udb_mv.zIdLocal, tmp_ms_udb_mv.sComputer, tmp_ms_udb_mv.tActionTime, tmp_ms_udb_mv.sCardSubType, tmp_ms_udb_mv.sCardType, tmp_ms_udb_mv.cEpan, tmp_ms_udb_mv.cText, tmp_ms_udb_mv.lArtRef, tmp_ms_udb_mv.sArtClassRef, tmp_ms_udb_mv.lSequenz, tmp_ms_udb_mv.sTransMark, tmp_ms_udb_mv.lBlock, tmp_ms_udb_mv.sTransType, tmp_ms_udb_mv.lGlobalID, tmp_ms_udb_mv.sFacility, tmp_ms_udb_mv.sCardClass, tmp_ms_udb_mv.lSingleAmount, tmp_ms_udb_mv.sVAT, tmp_ms_udb_mv.lVATTot, tmp_ms_udb_mv.tTarifTimeStart, tmp_ms_udb_mv.tTarifTimeEnd, tmp_ms_udb_mv.cLicensePlate, tmp_ms_udb_mv.lMoneyValue, tmp_ms_udb_mv.lPointValue, tmp_ms_udb_mv.lTimeValue, tmp_ms_udb_mv.tProdTime
                FROM tmp_ms_udb_mv WHERE
                tmp_ms_udb_mv.sTransType > 0 AND tmp_ms_udb_mv.sDevice < 1000 AND tmp_ms_udb_mv.sDevice>= 0 AND tmp_ms_udb_mv.sCardType IN (1) AND (bitand(tmp_ms_udb_mv.sSaleFlag,1) = 0 AND bitand(tmp_ms_udb_mv.sSaleFlag,4) = 0) AND tmp_ms_udb_mv.sArtClassRef < 100
                AND tmp_ms_udb_mv.tActionTime >= TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.25
                AND tmp_ms_udb_mv.tActionTime < TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.5
                ORDER BY tActionTime, lBlock, lSequenz

                call count cpu elapsed disk query current rows
                ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                Parse 1 0.04 0.10 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 596 0.17 27.07 2874 8894 0 8925
                ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                total 598 0.21 27.18 2874 8894 0 8925

                Misses in library cache during parse: 1
                Optimizer mode: ALL_ROWS
                Parsing user id: 60

                Rows Row Source Operation
                ------- ---------------------------------------------------
                8925 SORT ORDER BY (cr=8894 pr=2874 pw=0 time=27071773 us)
                8925 MAT_VIEW ACCESS BY INDEX ROWID TMP_MS_UDB_MV (cr=8894 pr=2874 pw=0 time=31458291 us)
                8925 INDEX RANGE SCAN EEETMP_MS_ACTTIMEDEVICE (cr=68 pr=68 pw=0 time=161347 us)(object id 149251)

                ********************************************************************************

                SELECT
                UDBMovement.zIdDevice, UDBMovement.sDevice, UDBMovement.zIdLocal, UDBMovement.sComputer, UDBMovement.tActionTime, UDBIdentData.sCardSubType, UDBIdentData.sCardType, UDBMovement.cEpan, UDBMovement.cText, UDBMovement.lArtRef, UDBMovement.sArtClassRef, UDBMovement.lSequenz, UDBMovement.sTransMark, UDBMovement.lBlock, UDBMovement.sTransType, UDBMovement.lGlobalID, UDBMovement.sFacility, UDBIdentData.sCardClass, UDBMovement.lSingleAmount, UDBMovement.sVAT, UDBMovement.lVATTot, UDBIdentData.tTarifTimeStart, UDBIdentData.tTarifTimeEnd, UDBIdentData.cLicensePlate, UDBIdentData.lMoneyValue, UDBIdentData.lPointValue, UDBIdentData.lTimeValue, UDBIdentData.tProdTime, UDBIdentData.tExpireDate
                FROM UDBMOVEMENT_orig UDBMovement, Udbidentdata_orig UDBIdentData
                WHERE
                UDBMovement.lGlobalId = UDBIdentData.lGlobalRef(+) AND UDBMovement.sComputer = UDBIdentData.sComputer(+)
                AND UDBMovement.sTransType > 0 AND UDBMovement.sDevice < 1000 AND UDBMovement.sDevice>= 0 AND UDBIdentData.sCardType IN (1) AND (bitand(UDBMovement.sSaleFlag,1) = 0 AND bitand(UDBMovement.sSaleFlag,4) = 0) AND UDBMovement.sArtClassRef < 100
                AND UDBMovement.tActionTime >= TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.25
                AND UDBMovement.tActionTime < TO_DATE('05/05/2011 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + 0.5
                ORDER BY tActionTime, lBlock, lSequenz

                call count cpu elapsed disk query current rows
                ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                Parse 1 0.03 0.06 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 596 0.76 16.94 3278 85529 0 8925
                ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                total 598 0.79 17.01 3278 85529 0 8925

                Misses in library cache during parse: 1
                Optimizer mode: ALL_ROWS
                Parsing user id: 60

                Rows Row Source Operation
                ------- ---------------------------------------------------
                8925 SORT ORDER BY (cr=85529 pr=3278 pw=0 time=16942799 us)
                8925 NESTED LOOPS (cr=85529 pr=3278 pw=0 time=15017857 us)
                22567 TABLE ACCESS BY INDEX ROWID UDBMOVEMENT_ORIG (cr=17826 pr=1659 pw=0 time=7273473 us)
                22570 INDEX RANGE SCAN IDX_UDBMOVARTICLE (cr=111 pr=111 pw=0 time=112351 us)(object id 143693)
                8925 TABLE ACCESS BY INDEX ROWID UDBIDENTDATA_ORIG (cr=67703 pr=1619 pw=0 time=8154915 us)
                22567 INDEX UNIQUE SCAN UDBIDENTDATA_PRIM (cr=45136 pr=841 pw=0 time=3731470 us)(object id 108324)