Optimierung und Analyse von Materialized Views

DBA Community - September 2011

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

In einem kürzlich erschienenen Community Tipp wurden bereits die Grundlagen von Materialized Views behandelt. Es wurde anhand eines praktischen Beispiels gezeigt, dass einfache Materialized Views sich grundsätzlich sehr schnell und intuitiv anlegen lassen. Durch die Verwendung von Materialized Views und Query Rewrite wird die Performance für bestimmte Abfragen gesteigert.

Ob und unter welchen Bedingungen das Query Rewrite auf eine bestehende Materialized View für eine Abfrage funktioniert und wie effizient die Aktualisierung der Materialized View erfolgen kann, hängt von unterschiedlichen Faktoren ab, die sich dem DBA nicht immer sofort erschließen. Daher gibt es im Funktionsumfang der Oracle Datenbank verschiedene Werkzeuge und APIs, die dem DBA helfen, die Arbeit mit Materialized Views zu optimieren.

Dieser Community Tipp knüpft an den oben genannten Tipp an und zeigt in praktischen Beipielen, wie der DBA die Möglichkeiten einer Materialized View analysieren und optimieren kann.

Analysieren der Eigenschaften einer Materialized View

In dem oben genannten Tipp wurde die Materialized View MV_SALES im Beispielschema SH angelegt, mit der die Praxisbeispiele weitergeführt werden.

SQL> CREATE MATERIALIZED VIEW mv_sales
     ENABLE QUERY REWRITE
     AS
     SELECT   p.promo_category_id
     ,        p.promo_category
     ,        SUM(s.amount_sold) AS sum_sales
     FROM     sales s
     ,        promotions p
     WHERE    s.promo_id = p.promo_id
     GROUP BY p.promo_category_id
     ,        p.promo_category;

Materialized view created.

Elapsed: 00:00:06.61
SQL>

Für die Adminstration und Analyse einer Materialized View wird die API DBMS_MVIEW verwendet. An dieser Stelle sollen zwei der Prozeduren der API vorgestellt werden, weiterf�hrunde Informationen zu den anderen Prozeduren und Funktionen finden Sie in der Dokumentation. Mit der Prozedur DBMS_MVIEW.EXPLAIN_MVIEW werden die Eigenschaften einer existierenden Materialized View ermittelt.

Als Parameter können alternativ der Name der Materialized View oder das SELECT-Statement, das der Materialized View zu Grunde liegt, verwendet werden. Das Statement darf eine Länge von 32627 Zeichen nicht überschreiten. Die Ausgabe erfolgt entweder direkt in ein VARRAY, oder, was etwas handlicher ist, in die Tabelle MV_CAPABILITIES_TABLE, die vor dem Start der Prozedur manuell angelegt werden muss. Das Skript utlxmv.sql, das sich im Verzeichnis $ORACLE_HOME/rdbms/admin befindet, erzeugt die Tabelle MV_CAPABILITIES_TABLE im aufrufenden Schema. In ihr werden die Ergebnisse der Analyse einer Materialized View durch die API DBMS_MVIEW.EXPLAIN_MVIEW() kommentiert abgelegt. Auf diese Weise erhält man Aussagen darüber, welche Art der Aktualisierung bei welchen DML Operationen möglich ist, ob bei partitionierten Materialized Views Partition Change Tracking möglich ist oder welche Eigenschaften in Bezug auf Query Rewrite gelten.

SQL> @?/rdbms/admin/utlxmv.sql

Table created.

SQL> exec dbms_mview.explain_mview( mv => 'MV_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT capability_name
     ,      possible
     ,      msgtxt
     FROM   mv_capabilities_table
     WHERE  mvname = 'MV_SALES'
     AND    capability_name LIKE 'REWRITE%';

CAPABILITY_NAME                P MSGTXT
------------------------------ - ----------------------------------------------
REWRITE                        Y
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N general rewrite is not possible or PCT is not
                                 possible on any of the detail tables


SQL>

Hier zeigt sich, dass die Materialized View MV_SALES grundsätzlich für Query Rewrite geeignet ist, dass es jedoch Einschränkungen im Zusammenhang mit Partition Change Tracking gibt. Diese rühren in diesem Beispiel daher, dass weder die Dimensionstabellen noch die Materialized View selber partitioniert sind.

Analyse des Query Rewrite für ein Statement zu einer Materialized View

Mit der zweiten Prozedur DBMS_MVIEW.EXPLAIN_REWRITE(), die nun näher erläutert werden soll, lässt sich überprüfen, ob der Optimizer eine vorgegebene Abfrage mittels Query Rewrite auf die angelegte Materialized View umschreiben kann. Auch hier gilt die Einschränkung, dass das zu prüfende SQL-Statement nicht länger als 32627 Zeichen sein darf. In unserem Beispiel soll geprüft werden, ob das folgende Statement auf die Materialized View MV_SALES umgeschrieben werden kann:

SELECT p.promo_category_id
,      p.promo_category
,      AVG(s.amount_sold) AS sum_sales
FROM  sales      s
,     promotions p
WHERE s.promo_id = p.promo_id
GROUP BY p.promo_category_id
,        p.promo_category;

Die Ausgabe erfolgt analog zum obigen Vorgehen in ein VARRAY oder in eine Tabelle, die manuell angelegt werden muss. Die Tabelle REWRITE_TABLE, die die Analyseergebnisse von DBMS_MVIEW.EXPLAIN_REWRITE() enthält, wird durch Aufruf des Skriptes utlxrw.sql im aktuellen Schema angelegt. Die Tabelleninhalte sind nicht besonders schön darzustellen, daher kann man sich aus dem Verzeichnis $ORACLE_HOME/rdbms/demo ein weiteres nützliches Hilfsmittel installieren. Über das Skript xrwutl.sql wird die Stored Procedure xrw unter dem Schema SYS angelegt.

Das xrw Utility ist für PUBLIC freigegeben und wird mit SYS-Berechtigung ausgeführt. Es bringt die Ausgabe von DBMS_MVIEW.EXPLAIN_REWRITE() in eine besser lesbare Form. Hilfe zur Verwendung von xrw erhält man durch den Aufruf xrw_help.

SQL> @?/rdbms/admin/utlxrw.sql

Table created.

SQL> @?/rdbms/demo/xrwutl.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL>
SQL> set echo off;
Connected.

...

Grant succeeded.

SQL>
SQL> set serveroutput on
SQL> set lines 110
SQL> set pages 100
SQL> exec xrw( mv_list      => 'MV_SALES', -
>              command_list => 'QUERY_TXT,REWRITTEN_TXT', -
>              querytxt     => 'SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales -
>                               FROM  sales s, promotions p -
>                               WHERE s.promo_id = p.promo_id -
>                               GROUP BY p.promo_category_id, p.promo_category');
============================================================================
>>
>> MESSAGE  : QSM-01150: query did not rewrite
>> QUERY    : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
>> RW QUERY : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
============================================================================
>>
------------------------- ANALYSIS OF QUERY REWRITE -------------------------
>>
>> MESSAGE  : QSM-01052: referential integrity constraint on table, PROMOTIONS, not VALID in ENFORCED
integrity mode
>> QUERY    : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
>> RW QUERY : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
----------------------------------------------------------------------------
>>
>> MESSAGE  : QSM-01065: materialized view, MV_SALES, cannot compute measure, AVG, in the query
>> QUERY    : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
>> RW QUERY : SELECT p.promo_category_id,p.promo_category,AVG(s.amount_sold) AS sum_sales  FROM sales s,
promotions p  WHERE s.promo_id=p.promo_id  GROUP BY p.promo_category_id, p.promo_category
============================ END OF MESSAGES ===============================

PL/SQL procedure successfully completed.

SQL>

Es wird klar, dass das Query Rewrite nicht funktionieren kann, da lediglich die Summe der Verkäufe in der Materialized View enthalten ist, nicht jedoch der Mittelwert. Weitere Beipiele zum Umgang mit der API DBMS_MVIEW finden sich im Beispielskript smxrw.sql im Verzeichnis $ORACLE_HOME/rdbms/demo.

Optimieren einer Materialized View

Um die Materialized View MV_SALES zu verbessern, kommt nun der SQL Access Advisor zum Einsatz. Der SQL Access Advisor ist ein Tuning-Werkzeug, das die Datenbankperformance durch Empfehlungen zur Partitionierung, Indizierung oder Verwendung von Materialized Views bzw. Materialized View Logs verbessern hilft. Der SQL Access Advisor kann sowohl in Form von Skripten als auch �ber die grafische Oberfläche des Oracle Enterprise Managers benutzt werden. Weitere Details zur Verwendung des SQL Access Advisor sind im Performance Tuning Guide erläutert.

Um den SQL Access Advisor verwenden zu dürfen, müssen das Diagnostic- und das Tuning-Pack lizenziert sein. Der SQL Access Advisor erfordert das Systemprivileg ADVISOR, das dem User SH zugewiesen werden muss. In der Data Dictionary View USER_TUNE_MVIEW wird das Ergebnis des SQL Access Advisors festgehalten. Der Vergleich zur oben angelegten Materialized View zeigt, dass zusätzlich Materialized View Logs auf den Basistabellen erzeugt werden und die Materialized View selbst über zusätzliche Spalten verfügt, durch die ein Fast Refresh oder auch das Query Rewrite auf der oben getesteten Abfrage möglich werden.

SQL> DECLARE task_mv_sales VARCHAR2(20):= 'tune_mv_sales';
     BEGIN
       DBMS_ADVISOR.TUNE_MVIEW( task_name      => task_mv_sales,
                                mv_create_stmt => 'CREATE MATERIALIZED VIEW mv_sales
                                                   ENABLE QUERY REWRITE
                                                   AS
                                                   SELECT   p.promo_category_id
                                                   ,        p.promo_category
                                                   ,        SUM(s.amount_sold) AS sum_sales
                                                   FROM     sales s
                                                   ,        promotions p
                                                   WHERE    s.promo_id = p.promo_id
                                                   GROUP BY p.promo_category_id
                                                   ,        p.promo_category');
     END;
     /

PL/SQL procedure successfully completed.

SQL> set long 16384
     SELECT action_id
     ,      script_type
     ,      statement
     FROM   user_tune_mview
     WHERE  task_name='tune_mv_sales';

 ACTION_ID SCRIPT_TYPE    STATEMENT
---------- -------------- --------------------------------------------------------------------------------
         3 IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE ("PROMO_ID","A
                          MOUNT_SOLD")  INCLUDING NEW VALUES

         4 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE ("PROMO_ID
                          ","AMOUNT_SOLD")  INCLUDING NEW VALUES

         5 IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SH"."PROMOTIONS" WITH ROWID, SEQUENCE ("PROMO_I
                          D","PROMO_CATEGORY","PROMO_CATEGORY_ID")  INCLUDING NEW VALUES

         6 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PROMOTIONS" ADD ROWID, SEQUENCE ("PRO
                          MO_ID","PROMO_CATEGORY","PROMO_CATEGORY_ID")  INCLUDING NEW VALUES

         7 IMPLEMENTATION CREATE MATERIALIZED VIEW SH.MV_SALES   REFRESH FAST WITH ROWID ENABLE QUERY REWR
                          ITE AS SELECT SH.PROMOTIONS.PROMO_CATEGORY_ID C1, SH.PROMOTIONS.PROMO_CATEGORY C
                          2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COU
                          NT(*) M3 FROM SH.PROMOTIONS, SH.SALES WHERE SH.SALES.PROMO_ID = SH.PROMOTIONS.PR
                          OMO_ID GROUP BY SH.PROMOTIONS.PROMO_CATEGORY_ID, SH.PROMOTIONS.PROMO_CATEGORY

         8 UNDO           DROP MATERIALIZED VIEW SH.MV_SALES

6 rows selected.

SQL>

Die durch den SQL Access Advisor ermittelten Tasks lassen sich durch den Aufruf von DBMS_ADVISOR.GET_TASK_SCRIPT() und DBMS_ADVISOR.CREATE _FILE() in einem Directory in Skriptform ablegen. Die dazu notwendigen Rechte auf dem Directory müssen entsprechend vergeben werden. Das folgende Listing zeigt, wie die Skripte erzeugt werden.

SQL> create directory tune_dir as '/tmp/tune_dir';

Directory created.

SQL> DECLARE
       create_script CLOB;
       undo_script   CLOB;
     BEGIN
       -- Erzeugen eines Skripts zum Anlegen/Implementieren der MV (default: type => 'IMPLEMENTATION')
       create_script := DBMS_ADVISOR.GET_TASK_SCRIPT( task_name => 'tune_mv_sales');
       -- Erzeugen eines Skripts zur Wiederherstellung der alten MV (type => 'UNDO')  
       undo_script := DBMS_ADVISOR.GET_TASK_SCRIPT( task_name => 'tune_mv_sales'
                                                  , type      => 'UNDO');
       -- Herausschreiben des Create Skriptes
       DBMS_ADVISOR.CREATE_FILE( buffer   => create_script
                               , location => 'TUNE_DIR'
                               , filename => 'mv_sales_create.sql');
       -- Herausschreiben des Undo Skriptes
       DBMS_ADVISOR.CREATE_FILE( buffer   => undo_script
                               , location => 'TUNE_DIR'
                               , filename => 'mv_sales_undo.sql');
     END;
     /

PL/SQL procedure successfully completed.

SQL>

Das nächste Listing zeigt das Create-Skript, mit dem die Materialized View in ihrer verbesserten Struktur angelegt wird.

SQL> !cat /tmp/tune_dir/mv_sales_create.sql
Rem  SQL Access Advisor: Version 11.2.0.2.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            tune_mv_sales
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROMO_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROMO_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."PROMOTIONS"
    WITH ROWID, SEQUENCE("PROMO_ID","PROMO_CATEGORY","PROMO_CATEGORY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."PROMOTIONS"
    ADD ROWID, SEQUENCE("PROMO_ID","PROMO_CATEGORY","PROMO_CATEGORY_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.MV_SALES
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PROMOTIONS.PROMO_CATEGORY_ID C1, SH.PROMOTIONS.PROMO_CATEGORY C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.PROMOTIONS,
       SH.SALES WHERE SH.SALES.PROMO_ID = SH.PROMOTIONS.PROMO_ID GROUP BY SH.PROMOTIONS.PROMO_CATEGORY_ID,
       SH.PROMOTIONS.PROMO_CATEGORY;



SQL> DROP MATERIALIZED VIEW mv_sales;

Materialized view dropped.

SQL> @/tmp/tune_dir/mv_sales_create.sql

Materialized view log created.


Materialized view log altered.


Materialized view log created.


Materialized view log altered.


Materialized view created.

SQL>

Wie zu erwarten war, erfolgt mit der neuen Materialized View nun ein Query Rewrite der Abfrage. Somit profitiert nicht nur die Summenabfrage von der Optimierung, sondern ebenfalls die Abfrage des Durchschnitts der Verkäufe. Zudem ist die optimierte Materialized View nun in der Lage, mittels inkrementellem (Fast) Refresh effizienter aktualisiert zu werden, wie ein erneutes DBMS_MVIEW.EXPLAIN_MVIEW() beweisen würde.

SQL> set autotrace traceonly explain
SQL> SELECT   p.promo_category_id
     ,        p.promo_category
     ,        AVG(s.amount_sold) AS sum_sales
     FROM     sales s
     ,        promotions p
     WHERE    s.promo_id = p.promo_id
     GROUP BY p.promo_category_id
     ,        p.promo_category;

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

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


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

SQL> exec dbms_mview.refresh('MV_SALES','F')

PL/SQL procedure successfully completed.

SQL>

Fazit

Die Verwendung von Materialized Views bietet viele Vorteile. Die mit einer spezifischen Materialized View möglichen Operationen lassen sich durch den Einsatz kleiner Tools analysieren, die zusätzlich implementiert werden müssen. Der SQL Access Advisor hilft dabei, eine existierende Materialized View zu optimieren und deren Möglichkeiten auf diese Weise zusätzlich zu verbessern.

Weitere Informationen zum Thema Analyse und Optimierung von Materialized Views finden Sie hier:

Zurück zum Anfang des Artikels

Zurück zur Community-Seite