Als PDF Downloaden!

Tipps & Tricks: September 2003
Bereich:
SQL
Erstellung:
09/2003 MM
Versionsinfo:
9.2.0.8, 10.2.0.8, 11.1.0.6
Letzte Überarbeitung:
07/2009 MM
Materialized Views zur Performanzverbesserung
Haben Sie gewusst, dass Materialized Views nicht nur zur Datenreplikation geeignet sind, sondern sich dadurch
auch Performanceverbesserungen Ihrer Abfragen erzielen lassen?
Das folgende Beispiel soll Ihnen zeigen, wie nützlich eine Materialized View in Verbindung mit der Option "Query
Rewrite" sein kann.
Dabei wird die Abfrage auf eine große Tabelle in eine Abfrage auf eine wesentlich kleinere Tabelle (MView)
umgewandelt, wodurch sich die Ausführungszeit und die Kosten der Abfrage deutlich verringern lassen.
Zu diesem Zweck erstellen wir uns zunächst eine große Tabelle BIG_EMP, basierend auf der Tabelle EMP, und
analysieren diese.
CREATE TABLE big_emp NOLOGGING AS
SELECT e.* FROM emp, emp, emp, emp, emp e;
INSERT INTO big_emp SELECT * FROM big_emp;
EXEC dbms_stats.gather_table_stats('SCOTT','BIG_EMP')
SELECT COUNT(*) FROM big_emp;
COUNT(*)
-------1075648
Nun muss die SQL*Plus-Umgebung entsprechend eingerichtet werden, damit die Zeitmessung und der
Ausführungsplan mitausgegeben werden.
SET TIMING ON AUTOTRACE ON EXPLAIN
Hinweise:
Falls an dieser Stelle eine Fehlermeldung zurückgegeben wird, muss das Skript utlxplan.sql im
<ORACLE_HOME>\RDBMS\ADMIN-Verzeichnis aufgerufen werden, das die PLAN_TABLE-Tabelle
erzeugt.
Gegebenenfalls muss über das Skript plustrce.sql im <ORACLE_HOME>\SQLPLUS\ADMIN-Verzeichnis
noch die PLUSTRACE-Rolle angelegt und dem Benutzer zugewiesen werden. Dies ist als SYS-Benutzer
durchzuführen.
Außerdem ist darauf zu achten, dass der Initialisierungs-Parameter TIMED_STATISTICS auf TRUE
gesetzt ist.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 3
Anschließend erfolgt die Abfrage auf die Tabelle BIG_EMP.
SELECT job, count(*) FROM big_emp GROUP BY job;
JOB
COUNT(*)
--------- -------ANALYST
153664
CLERK
307328
MANAGER
230496
PRESIDENT
76832
SALESMAN
307328
Abgelaufen: 00:00:03.02
Ausführungsplan
--------------------------Plan hash value: 457352942
-----------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT |
|
5|
40 | 1528 (8)| 00:00:19 |
| 1 | HASH GROUP BY
|
|
5|
40 | 1528 (8)| 00:00:19 |
| 2 | TABLE ACCESS FULL| BIG_EMP | 1095K| 8555K| 1441 (2)| 00:00:18 |
-----------------------------------------------------------------------------Der Ausführungsplan verdeutlicht, dass ein Full Table Scan auf die BIG_EMP-Tabelle mit Gesamtkosten von
1528 durchgeführt worden ist.
Damit Sie nun eine Materialized View mit der Möglichkeit des "Abfrage-Rewritings" erstellen können, müssen
Ihnen folgende Berechtigungen zugewiesen werden:
GRANT QUERY REWRITE, CREATE SNAPSHOT TO <user>;
In Ihrer eigenen Sitzung müssen Sie noch den Parameter QUERY_REWRITE_ENABLED einschalten mittels (ab
Version 10g standardmäßig gesetzt):
ALTER SESSION SET query_rewrite_enabled=true;
Daraufhin erstellen Sie sich eine Materialized View in der Sie den SELECT-Befehl auf die Tabelle BIG_EMP
hinterlegen und führen eine Analyse durch.
CREATE MATERIALIZED VIEW mv_big_emp
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT job, COUNT(*) FROM big_emp GROUP BY job;
EXEC dbms_stats.gather_table_stats('SCOTT','MV_BIG_EMP')
Bevor Sie nun die SELECT-Abfrage auf die BIG_EMP-Tabelle wiederholt ausführen, schalten Sie erneut das
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 3
Bevor Sie nun die SELECT-Abfrage auf die BIG_EMP-Tabelle wiederholt ausführen, schalten Sie erneut das
Autotracing ein und leeren Sie den Shared Pool, damit das Statement neu geparst werden muss.
SET AUTOTRACE TRACEONLY EXPLAIN
ALTER SYSTEM FLUSH SHARED_POOL;
SELECT job, COUNT(*) FROM big_emp GROUP BY job;
Abgelaufen: 00:00:00.06
Ausführungsplan
---------------------------------------------------------Plan hash value: 3824777520
-------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost |
-------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
5|
70 |
3|
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_BIG_EMP |
5|
70 |
3|
-------------------------------------------------------------------------Vergleicht man nun die beiden Ausführungspläne, fällt auf, dass die zweite Abfrage einen Full Table Scan auf
MV_BIG_EMP vornimmt, und demzufolge die Ausführungszeit und die Kosten auch deutlich geringer ausfallen.
Durch die Option ENABLE QUERY REWRITE beim Anlegen der Materialized View, leitet die Datenbank den
SELECT-Befehl auf die Tabelle BIG_EMP automatisch auf die Materialized View MV_BIG_EMP um.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 3