Einführung in Materialized Views

DBA Community - August 2011

von Frank Schneede, ORACLE Deutschland B.V. & Co. KG

Die Steigerung der Abfrageperformance einzelner SQL Statements ist für den DBA eine täglich zu meisternde Herausforderung. Verschiedene Funktionen innerhalb der Oracle Datenbank können den DBA beim Tuning der Abfrageperformance unterstützen. Materialized Views (MAV), die bereits seit der Version Oracle 8i zur Verfügung stehen, sind ein gern genutztes Tuningmittel in Datawarehouse-Umgebungen.

In Materialized Views werden nahezu beliebig komplexe Aggregate auf großen Tabellen vorausberechnet und materialisert abgelegt. Ein Zugriff auf diese materialisierte View ist erheblich schneller als ein Zugriff auf die Basistabellen durch die Verwendung einer herkömmlichen View. Die Performancesteigerung rührt daher, dass für die Ermittlung der vorausberechneten Aggregate der Materialized View wesentlich weniger I/O Operationen notwendig sind.

Dieser Artikel stellt die Grundlagen des Arbeitens mit Materialized Views dar und gibt anhand von Beispielen Hinweise zu deren Erstellung und zum Feature Query Rewrite.

Anlegen einer Materialized View und Nutzung von Query Rewrite

Die in diesem Artikel gezeigten Beispiele nutzen das Beispielschema SH, das bei der Installation der Oracle Datenbank bereitgestellt wird. Das Beispielschema SH umfasst ein Star-Schema mit der Faktentabelle SALES und mehreren Dimensionstabellen, zum Beispiel CHANNELS, PROMOTIONS oder PRODUCTS. Um den Effekt der Nutzung von Materialized Views besser sehen zu können, ist es sinnvoll, die Tabelle SH.SALES signifikant zu vergrößern. Die verwendete Testumgebung sieht dann folgendermaßen aus:

SQL> DESC sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> SELECT count(*) FROM sales;

  COUNT(*)
----------
   7350744

SQL> SELECT SUM(bytes)/1024/1024 AS mb FROM user_segments WHERE segment_name='SALES';

        MB
----------
       296

SQL> SHOW sga

Total System Global Area  803500032 bytes
Fixed Size                  1347172 bytes
Variable Size             603980188 bytes
Database Buffers          192937984 bytes
Redo Buffers                5234688 bytes
SQL>
SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;

PROMO_CATEGORY_ID PROMO_CATEGORY                  SUM_SALES
----------------- ------------------------------ ----------
                2 NO PROMOTION                    756036174
                6 internet                       17595047.2
                3 TV                             9796026.08
                9 post                           2219410.08

Elapsed: 00:00:09.39

Die Testabfrage zeigt eine Antwortzeit von etwa 9 Sekunden, diese soll nun durch eine Materialized View verbessert werden. Um mit Materialized Views arbeiten zu können, sind entsprechende Berechtigungen notwendig, die direkt - und nicht über eine Rolle - zugewiesen werden müssen. Neben den Systemprivilegien CREATE MATERIALIZED VIEW und CREATE TABLE oder CREATE ANY TABLE ist das Objektprivileg SELECT auf den Basistabellen der Materialized View Voraussetzung, um im eigenen Schema Materialized Views anlegen und abfragen zu können. Die etwas komplexere Vergabe von System- und Objektprivilegien in der Situation, in der eine Materialized View auf Basistabellen eines anderen Schemas definiert wird, ist in der SQL Language Reference sehr gut beschrieben.

Das Anlegen einer Materialized View ist intuitiv, schnell umgesetzt und liefert sofort einen positiven Effekt. Die Materialized View wird standardmäßig sofort mit BUILD IMMEDIATE oder mit BUILD DEFERRED, das heißt mit dem ersten Refresh-Vorgang, physikalisch erzeugt. Die Standardoptionen für die Durchführung des Refreshvorganges werden ebenfalls beim Anlegen der Materialized View festgelegt. Hierbei wird zwischen dem Zeitpunkt des Refreshes und dem Refresh-Modus (FAST, FORCE, COMPLETE, siehe weiter unten) unterschieden. Der Refreshzeitpunkt kann auf Anforderung (REFRESH ON DEMAND, dies ist der default) ohne spezifische Zeitangabe, intervallgesteuert (REFRESH ON DEMAND START WITH ... NEXT ...) oder bei einer Änderung an den Basistabellen (REFRESH ON COMMIT) erfolgen. Die letztgenannente Variante hat Auswirkung auf die Performance der auslösenden Transaktion, daher sollte sie nur angewendet werden, wenn es unbedingt notwendig ist.

SQL> CREATE MATERIALIZED VIEW mv_sales
  2  ENABLE QUERY REWRITE
  3  AS
  4  SELECT   p.promo_category_id
  5  ,        p.promo_category
  6  ,        SUM(s.amount_sold) AS sum_sales
  7  FROM     sales s
  8  ,        promotions p
  9  WHERE    s.promo_id = p.promo_id
 10  GROUP BY p.promo_category_id
 11  ,        p.promo_category;

Materialized view created.

Elapsed: 00:00:06.61
SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;

PROMO_CATEGORY_ID PROMO_CATEGORY                  SUM_SALES
----------------- ------------------------------ ----------
                2 NO PROMOTION                    756036174
                6 internet                       17595047.2
                3 TV                             9796026.08
                9 post                           2219410.08

Elapsed: 00:00:00.07

Um den Einfluß von Caching auf die Performance auszuschließen, wird der Shared Pool mit dem Kommando ALTER SYSTEM FLUSH SHARED_POOL geleert, auch der Result Cache der Datenbank wird nicht verwendet. Die Erklärung, woher die signifikante Performancesteigerung um 9 Sekunden kommt, ist im Ausführungsplan der Abfrage zu sehen. Das hierfür verantwortliche Feature der Datenbank heißt Summary management—Materialized View Query Rewrite, ist Bestandteil der Enterprise Edition und muss beim Anlegen der Materialized View spezifiziert werden (siehe oben, Klausel ENABLE QUERY REWRITE).

SQL> set autotrace traceonly explain
SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1597931345

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     4 |   172 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_SALES |     4 |   172 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)

Mit zwei Initialisierungsparametern wird das Query Rewrite gesteuert. Der Parameter query_rewrite_enabled ist seit der Version 10g standardmäßig auf TRUE gesetzt und bewirkt, dass der SQL Optimizer anhand der Kosten prüft, ob eine Abfrage mittels einer Materialized View sinnvoll umgeschrieben werden kann. Die Einstellung FORCE erzwingt ein Umschreiben der Abfrage ohne Berücksichtigung der Kosten. Wenn query_rewrite_enabled = FALSE gesetzt ist oder die Materialized View mit DISABLE QUERY REWRITE angelegt ist, erfolgt kein Umschreiben des SQL Statements. Ein Zugriff auf die Aggregation in der Materialized View ist dann nur explizit möglich! Bei eingeschaltetem Query Rewrite erfolgt ein transparentes Umschreiben der Abfrage, der Anwender braucht keine Anpassungen am Code der Applikation, die die Abfrage erzeugt, vorzunehmen.

Der zweite - und wesentlich wichtigere - Parameter für das Query Rewrite ist query_rewrite_integrity. Über diesen Parameter wird festgelegt, wie mit Veränderungen auf den Basistabellen der Materialized View umgegangen wird. Die Standardeinstellung ENFORCED sagt aus, dass ein Query Rewrite nur erfolgt, wenn Datenkonsistenz und -integrität garantiert sind. Falls eine Veränderung einer Basistabelle erfolgt ist, so werden die Daten der Materialized View als nicht mehr aktuell (STALE) angesehen und es erfolgt kein Query Rewrite. In bestimmten Kundensituationen kann es vorkommen, dass Dimensionstabellen einer Materialized View Veränderungen unterliegen, aber dennoch die Konsistenz der Daten in der Faktentabelle nicht verletzt wird. Eine solche Situation liegt zum Beispiel vor, wenn Dimensionstabellen (Stammdaten) tagsüber gepflegt, die Faktentabelle (Bewegungsdaten) aber nur in einem nächtlichen ETL-Prozeß geladen wird. In diesem Fall ist es möglich, das Query Rewrite durch ALTER SESSION SET query_rewrite_integrity = stale_tolerated zu ermöglichen. Hierbei ist jedoch äußerste Vorsicht geboten, denn die Ergebnisse einer Abfrage auf den Basistabellen können zu den Ergebnissen der Materialized View abweichen!

SQL> show parameter rewrite

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
SQL> update sales set amount_sold=amount_sold+1 where rownum<2;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;

PROMO_CATEGORY_ID PROMO_CATEGORY                  SUM_SALES
----------------- ------------------------------ ----------
                2 NO PROMOTION                    756036175
                6 internet                       17595047.2
                3 TV                             9796026.08
                9 post                           2219410.08

Elapsed: 00:00:04.56

Execution Plan
----------------------------------------------------------
Plan hash value: 2034784796

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     4 |   124 | 12347   (5)| 00:02:29 |       |       |
|   1 |  HASH GROUP BY                |            |     4 |   124 | 12347   (5)| 00:02:29 |       |       |
|   2 |   NESTED LOOPS                |            |       |       |            |          |       |       |
|   3 |    NESTED LOOPS               |            |     4 |   124 | 12346   (5)| 00:02:29 |       |       |
|   4 |     VIEW                      | VW_GBC_5   |     4 |    68 | 12342   (5)| 00:02:29 |       |       |
|   5 |      HASH GROUP BY            |            |     4 |    36 | 12342   (5)| 00:02:29 |       |       |
|   6 |       PARTITION RANGE ALL     |            |  7350K|    63M| 11934   (2)| 00:02:24 |     1 |    28 |
|   7 |        TABLE ACCESS FULL      | SALES      |  7350K|    63M| 11934   (2)| 00:02:24 |     1 |    28 |
|*  8 |     INDEX UNIQUE SCAN         | PROMO_PK   |     1 |       |     0   (0)| 00:00:01 |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID| PROMOTIONS |     1 |    14 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------


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

   8 - access("ITEM_1"="P"."PROMO_ID")

Das weitergeführte Beispiel zeigt eine schlechte Antwortzeit und der Ausführungsplan beweist, dass kein Query Rewrite stattgefunden hat. Um die Abfrage umschreiben zu können, muss die Materialized View also auf den aktuellen Stand gebracht werden.

Materialized View Refresh

Das Aktualsieren des Datenbestandes einer Materialized View kann auf unterschiedliche Weisen erfolgen. Die am wohl weitesten verbreitete Aktualisierungsmethode ist Refresh On Demand, das heisst, eine Aktualisierung der Daten erfolgt skriptgesteuert oder über einen Schedulerjob durch die API DBMS_MVIEW.REFRESH(LIST=>'MV_SALES',METHOD=>'C'), wobei die Refreshmethode von Fall zu Fall unterschiedlich gewählt wird. Die Dokumentation liefert eine ausführliche Beschreibung der unterschiedlichen Refresh-Modi, daher sollen hier nur ein paar grundsätzliche Hinweise gegeben werden.

Ein Complete Refresh (METHOD=>'C') ist die sicherste, aber auch zeitaufwändigste Methode, eine Materialized View zu aktualisieren und die Datenkonsistenz zu garantieren. Die Materialized View wird mit DELETE geleert und anschließend durch das in der Materialized View Definition verwendete Statement gefüllt. Der folgende Ausschnitt eines 10046 Trace zeigt das Vorgehen.

...
/* MV_REFRESH (DEL) */ delete from "SH"."MV_SALES"
...
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SH"."MV_SALES"("PROMO_CATEGORY_ID",
"PROMO_CATEGORY","SUM_SALES") SELECT "P"."PROMO_CATEGORY_ID","P"."PROMO_CATEGORY",SUM("S"."AMOUNT_SOLD")
 FROM "SALES" "S","PROMOTIONS" "P" WHERE "S"."PROMO_ID"="P"."PROMO_ID" GROUP BY "P"."PROMO_CATEGORY_ID",
 "P"."PROMO_CATEGORY"
...

Ein vollständiger Refresh konsumiert sehr viele Ressourcen und dauert bei großen Materialized Views entsprechend lange. Aus diesem Grund ist es wichtig, eine Materialized View so zu definieren, dass sie mit einem Fast Refresh (METHOD=>'F'), der nur die Änderungen seit dem letzten Refresh verarbeitet, aktualisiert werden kann. Wenn die Basistabellen der Materialized View partitioniert sind, so ist es möglich, mit einem Fast_PCT Refresh (METHOD=>'P') nur die Sätze der Materialized View zu aktualisieren, die durch geänderte Partitionen der Basistabellen betroffen sind. Die Ermittlung der geänderten Partitionen erfolgt über das Feature Partition Change Tracking. Die Methode des Fast_PCT Refresh ist eine sehr effiziente Methode, Materialized Views auf großen Datenmengen inkrementell zu aktualisieren, denn es wird nur auf die Datenbereiche zugegriffen, die für eine Aktualisierung notwendig sind.

Unter bestimmten Bedingungen ist auch ein inkrementeller Fast Refresh nicht sinnvoll oder nicht möglich. Eine solche Situation liegt vor, wenn

die Menge der Daten, die in die Materialized View eingepflegt werden muss, zu groß ist, oder
die Materialized View aufgrund ihrer Struktur nicht Fast Refresh geeignet ist.

Für diesen Fall kann mit sogenannten Materialized Views On Prebuilt Table gearbeitet werden. Das Konzept der Prebuilt Tables sieht vor, dass der DBA manuell die Aggregationstabelle erstellt und befüllt und danach auf der gefüllten Tabelle ("Prebuilt Table") die gewünschte Materialized View erstellt. Der große Vorteil dieses Vorgehens liegt darin, dass sämtliche Features (zum Beispiel Parallel Query) der Datenbank verwendet werden können, um die Materialized View so schnell zu erstellen, wie es technisch machbar ist. Bei sehr großen Basistabellen sollte also für die schnelle Befüllung der Prebuilt Table ein möglichst hoher Parallelitätsgrad gewählt werden. Für die nachfolgenden Abfragen auf der Materialized View wäre die hohe Parallelität allerdings kontraproduktiv. Daher ist es wichtig, den Parallelitätsgrad nach der Erstellung der Prebuilt Table auf einen sinnvollen Wert zurückzustellen. In dem dargestellten Beispiel wird aufgrund der Datenmenge kein Parallel Query eingesetzt. Das weiterentwickelte Beispiel sieht demnach folgendermaßen aus:

SQL> DROP MATERIALIZED VIEW mv_sales;

Materialized view dropped.

SQL> CREATE TABLE mv_sales
  2  AS
  3  SELECT   p.promo_category_id
  4  ,        p.promo_category
  5  ,        SUM(s.amount_sold) AS sum_sales
  6  FROM     sales s
  7  ,        promotions p
  8  WHERE    s.promo_id = p.promo_id
  9  GROUP BY p.promo_category_id
 10  ,        p.promo_category;

Table created.

SQL> CREATE MATERIALIZED VIEW mv_sales
  2  ON PREBUILT TABLE
  3  ENABLE QUERY REWRITE
  4  AS
  5  SELECT   p.promo_category_id
  6  ,        p.promo_category
  7  ,        SUM(s.amount_sold) AS sum_sales
  8  FROM     sales s
  9  ,        promotions p
 10  WHERE    s.promo_id = p.promo_id
 11  GROUP BY p.promo_category_id
 12  ,        p.promo_category;

Materialized view created.

Beim Arbeiten mit Materialized Views On Prebuilt Table ist zu beachten, dass ein Query Rewrite unmittelbar nach deren Anlegen bei der Einstellung query_rewrite_integrity = ENFORCED nicht erfolgt. Hierzu muss die Integrität auf einen weniger restriktiven Wert eingestellt werden. Die Einstellung query_rewrite_integrity = TRUSTED bewirkt, dass der Optimizer darauf "vertraut", dass die Datenintegrität sichergestellt ist, sie aber nicht erzwingt. Die Einstellung query_rewrite_integrity = STALE TOLERATED würde hier zwar ebenfalls zu einem Umschreiben der Abfrage führen, jedoch ist mit dieser Einstellung die Datenintegrität bei Veränderung der Basistabellen nicht sichergestellt, wie bereits weiter oben ausgeführt worden ist.

SQL> set autotrace on explain
SQL> set timing on
SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;

PROMO_CATEGORY_ID PROMO_CATEGORY                  SUM_SALES
----------------- ------------------------------ ----------
                2 NO PROMOTION                    756036175
                6 internet                       17595047.2
                3 TV                             9796026.08
                9 post                           2219410.08

Elapsed: 00:00:03.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2034784796

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     4 |   124 | 10707   (5)| 00:02:09 |       |       |
|   1 |  HASH GROUP BY                |            |     4 |   124 | 10707   (5)| 00:02:09 |       |       |
|   2 |   NESTED LOOPS                |            |       |       |            |          |       |       |
|   3 |    NESTED LOOPS               |            |     4 |   124 | 10706   (5)| 00:02:09 |       |       |
|   4 |     VIEW                      | VW_GBC_5   |     4 |    68 | 10702   (5)| 00:02:09 |       |       |
|   5 |      HASH GROUP BY            |            |     4 |    36 | 10702   (5)| 00:02:09 |       |       |
|   6 |       PARTITION RANGE ALL     |            |  7350K|    63M| 10293   (2)| 00:02:04 |     1 |    28 |
|   7 |        TABLE ACCESS FULL      | SALES      |  7350K|    63M| 10293   (2)| 00:02:04 |     1 |    28 |
|*  8 |     INDEX UNIQUE SCAN         | PROMO_PK   |     1 |       |     0   (0)| 00:00:01 |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID| PROMOTIONS |     1 |    14 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------


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

   8 - access("ITEM_1"="P"."PROMO_ID")

SQL> alter session set query_rewrite_integrity=TRUSTED;

Session altered.

Elapsed: 00:00:00.01
SQL> SELECT   p.promo_category_id
  2  ,        p.promo_category
  3  ,        SUM(s.amount_sold) AS sum_sales
  4  FROM     sales s
  5  ,        promotions p
  6  WHERE    s.promo_id = p.promo_id
  7  GROUP BY p.promo_category_id
  8  ,        p.promo_category;

PROMO_CATEGORY_ID PROMO_CATEGORY                  SUM_SALES
----------------- ------------------------------ ----------
                2 NO PROMOTION                    756036175
                6 internet                       17595047.2
                3 TV                             9796026.08
                9 post                           2219410.08

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1597931345

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     4 |   172 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_SALES |     4 |   172 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)

Fazit

Die Verwendung von Materialized Views kann eine deutliche Performancesteigerung bringen, ohne dass der DBA bzw. Anwendungsentwickler einen Mehraufwand durch zusätzliche Codierung hat. Je nach den spezifischen Anforderungen kann ein Refresh über die API erfolgen oder die Materialized View wird manuell erstellt bzw. gepflegt.

Das sehr facettenreiche Thema Materialized Views wird in einem der nächsten Tipps noch tiefer beleuchtet. Weiterführende Informationen finden Sie außerdem hier:

Zurück zum Anfang des Artikels

Zurück zur Community-Seite