oracle – datenbankoptimierung (basics)

ORACLE – DATENBANKOPTIMIERUNG
(BASICS)
INHALT
1 Motivation ........................................................................................................................................... 1
2 Automatische, regelmäßige DB-Optimierung ..................................................................................... 2
2.1 Index-Rebuild ................................................................................................................................ 2
2.2 Tabellen-Reorganisation ............................................................................................................... 2
2.3 Statistiken ermitteln ..................................................................................................................... 3
2.4 Alles automatisiert ........................................................................................................................ 3
3 Performance-Monitoring..................................................................................................................... 4
3.1 V$SESSION_LONGOPS................................................................................................................... 4
3.2 V$SQL, V$SQL_AREA ..................................................................................................................... 5
4 Performance-Tuning durch DB-Indizes................................................................................................ 6
4.1 Fremdschlüssel-Indizes ................................................................................................................. 6
4.2 Identifizierung anhand hoher Kosten bei SQL-Statements .......................................................... 6
5 Zusammenfassung ............................................................................................................................... 9
1 MOTIVATION
In einem Kundenprojekt standen wir vor dem Problem, dass die Anwendung – und scheinbar auch
die Oracle-Datenbank-Instanz selber - über die Jahre immer langsamer geworden ist. Wir haben das
Problem lange Zeit auf die ständig anwachsenden Datenmengen und mangelnde Optimierung in der
Persistenzschicht der Applikation geschoben.
Als ein externer Oracle-Berater dann einen Blick auf die Produktiv-Datenbank geworfen hat, wurden
uns einige Nachlässigkeiten im Umgang mit der Datenbank aufgezeigt. Im Folgenden möchte ich die
wesentlichen Punkte der Performance-Analyse und -Tunings kurz skizzieren. Dies ist sicherlich kein
Oracle-Optimierungs-Workshop für Fortgeschrittene ist, sondern vielmehr wird das Rüstzeug eines
Anwendungsentwicklers beschrieben, der z.B. per JDBC-Treiber auf Oracle-Datenbanken zugreift.
Desweiteren sollte man (ggf. mit Hilfe eines Oracle-Experten) ausfindig machen, ob der
Datenbankserver selber richtig dimensioniert ist. Man kann mithilfe geeigneter SQL-Statements und
Tools leicht herausfinden, ob es z.B. einen File-IO-Engpass auf dem DB-Server gibt oder einen
Hauptspeichermangel. Wenn solche „Mängel“ vorliegen, sollten natürlich auf der Hardware-Seite
entsprechende Aufrüstungsmaßnahmen ergriffen werden.
Die folgenden Aufgaben liegen teilweise in der Verantwortung des Datenbankadministrators und
teilweise in der des Softwareentwicklers.
2 AUTOMATISCHE, REGELMÄßIGE DB-OPTIMIERUNG
Der DB-Administrator sollte dafür sorgen, dass folgende Aufgaben regelmäßig ausgeführt werden:
(Regelmäßig ist ein sehr relativer Begriff – aber das hängt stark von den einzelnen Datenbanken, der
Verfügbarkeit, der Anzahl Schreibzugriffe, etc. ab. Es schadet aber nicht, mindestens jede Woche, z.B.
am Wochenende, oder auch jede Nacht, wenn weniger oder keine Last auf der DB herrscht, die
Optimierungen automatisch durchführen zu lassen (z.B. per SQL mittels Cron-Job – wahrscheinlich
geht das auch mit Oracle-Administrationstools).
2.1 INDEX-REBUILD
Durch Analyse der Systemtabelle „USER_INDEXES“ und geeigneten SQL-Befehlen kann man
herausfinden, welche Indices degeneriert sind und neu angelegt werden sollten – aber auch, welche
Indizes überflüssig sind.
Index-Rebuild mittels:
ALTER INDEX <IndexName> REBUILD;
Es ist sinnvoll, alle Indizes in einem eigenen Tablespace zu speichern. Gleichzeitig mit einem IndexRebuild kann man ggf. das Verschieben in den Tablespace „INDX“ folgendermaßen durchführen:
ALTER INDEX <IndexName> REBUILD TABLESPACE INDX;
2.2 TABELLEN-REORGANISATION
Eine Defragmentierung von Tabellen durch deren Neuaufbau(„Rebuild“) ist von Zeit zu Zeit
durchzuführen:
ALTER TABLE <TableName> MOVE TABLESPACE USERS;
Verschiebt die Tabelle in einen anderen Tablespace und erstellt sie neu. Wenn der Tablespace
derselbe ist wie zuvor, so wird die Tabelle lediglich reorganisiert (defragmentiert).
Eine Reorganisation einer Tabelle bzw. eines DB-Index wird empfohlen, wenn sich die Datenmenge
durch viele INSERT oder DELETE-Statements oder Importe signifikant verändert hat!
2.3 STATISTIKEN ERMITTELN
Oracle benötigt u.a. zur Optimierung von SQL-Abfragen aktuelle Statistiken über alle Tabellen und
Indizes. Diese müssen regelmäßig ermittelt werden. Je nach Konfiguration der Datenbank werden
die Statistiken automatisch neu erzeugt. Bei einem Datenbankimport geschieht das typischerweise
auch automatisch.
Ermittlung, wann zuletzt Statistiken über DB-Entitäten erhoben worden sind:
SELECT owner, table_name, last_analyzed FROM
owner='APPLICATION_XYZ';
dba_tab_statistics WHERE
SELECT table_name, last_analyzed from dba_tables where Owner = 'APPLICATION_XYZ';
SELECT index_name, last_analyzed from dba_indexes where Owner = 'APPLICATION_XYZ';
EXIT;
Manuelle Ermittlung der Statistiken:
// internal DataDictionary-Stats:
call dbms_stats.gather_fixed_objects_stats();
// System-Stats:
call dbms_stats.gather_dictionary_stats();
// Perform object statistics for tables and indices
CALL dbms_stats.gather_index_stats('<User>', '<IndexName>');
CALL dbms_stats.gather_Table_stats('<User>', '<TableName>');
2.4 ALLES AUTOMATISIERT
Folgendes SQL-Skript erzeugt alle Statements, um sämtliche Indizes und Tabellen zu reorganisieren
und alle Statistiken neu zu berechnen. Diese Statements können dann regelmäßig – zum Beispiel
jedes Wochenende - ausgeführt werden.
SET PAGESIZE 0;
SET LINESIZE 200;
call dbms_stats.gather_dictionary_stats();
SPOOL tmp_OracleRebuildIndices.sql
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE INDX;' FROM dba_indexes
WHERE Owner = 'APPLICATION_XYZ';
SPOOL OFF
SPOOL tmp_OracleGatherIndexStats.sql
SELECT 'CALL dbms_stats.gather_index_stats(''COMPIERE'', '''|| index_name ||''');' FROM
dba_indexes WHERE Owner = 'APPLICATION_XYZ';
SPOOL OFF
SPOOL tmp_OracleAnalyzeIndices.sql
SELECT 'ANALYZE INDEX '||owner||'.'||index_name||' COMPUTE STATISTICS;' FROM dba_indexes WHERE
Owner = 'APPLICATION_XYZ';
SPOOL OFF
SPOOL tmp_OracleGatherTableStats.sql
SELECT 'CALL dbms_stats.gather_table_stats(''COMPIERE'', '''|| table_name ||''');' FROM
dba_tables WHERE Owner = 'APPLICATION_XYZ';
SPOOL OFF
exit;
3 PERFORMANCE-MONITORING
Oracle besitzt mit den V$_-Views sogenannte Dynamic Performance Views. Sie enthalten
dynamische Informationen zu laufenden und vergangenen SQL-Anfragen, DB-Sessions, und vielem
mehr, die vom DB-Server automatisch ermittelt und bei Abfrage durch den SYSTEM-User aktualisiert
werden. Wichtige Views sind z.B. V$SQL, V$SQLAREA, V$SESSION, V$SESSION_LONGOPS.
3.1 V$SESSION_LONGOPS
Auflisten teurer, langlaufender Aktionen mit ihrem verursachten „Aufwand“ und Laufzeit in der DB:
select OPName, Target, Message, TOTALWORK, ELAPSED_SECONDS, START_TIME, Last_Update_Time from
V$Session_LongOps where username='COMPIERE' order by TOTALWORK desc
Ergebnis z.B.:
OPNAME
TARGET
MESSAGE
TOTALWORK
ELAPSED_SECONDS
------------------------------------------------------------------------------Table Scan
COMPIERE.OCP_CRM_CORRESPONDENCE
Table Scan:
88381
Table Scan
11
COMPIERE.PWORKITEMS
Table Scan:
82432
Table Scan
COMPIERE.PWORKITEMS: 82432 out of 82432 Blocks done
43
COMPIERE.PBPEPROCESSINSTS
Table Scan:
23552
Table Scan
COMPIERE.OCP_CRM_CORRESPONDENCE: 88381 out of 88381 Blocks done
COMPIERE.PBPEPROCESSINSTS: 23552 out of 23552 Blocks done
66
COMPIERE.M_TRANSACTION
Table Scan:
19277
COMPIERE.M_TRANSACTION: 19277 out of 19277 Blocks done
18
Massnahmen:
Langlaufende Operationen oder teure “Full Table Scans“ könnten auf fehlende oder nicht optimale
DB-Indizes hindeuten. Eventuell kann man auch DB-Zugriffe in der Anwendung optimieren (weniger
Daten holen, Cachings einbauen, etc.).
3.2 V$SQL, V$SQL_AREA
3.2.1 Ermittlung der Ausführungsdauer von SQL-Abfragen (Top 10):
select * from (
select sql_text,executions,cpu_time from v$sql where executions>0
order by cpu_time/executions desc
)where rownum<10;
3.2.2 Teure SQL-Statements auflisten:
SELECT buffer_gets/(executions+1),
executions,
buffer_gets,
sql_text
FROM v$SQLAREA
WHERE buffer_gets/(executions+1) >= 20
ORDER BY 1 DESC
3.2.3 Aufspüren unperformanter SQL-Befehle:
Der folgende Befehl zeigt für die SQL-Befehle, die sich im SQL-Cache befinden, wie oft sie ausgeführt
wurden und wie viele Blockzugriffe sie zur Abarbeitung benötigten. (BUFFER_GET = Blockzugriff =
Lesen eines Speicherbereiches von der Harddisk in den Speicher  relativ teure Operation)
Außerdem wird die Trefferquote des Befehls im Datencache angezeigt. Damit lassen sich sehr schnell
"schlecht optimierte" SQL-Befehle herausfinden. Trefferquoten kleiner 70% deuten regelmäßig
darauf hin, dass eine Tabelle vollständig ohne Index-Zugriffe gelesen wird.
Da die Statistik nur die Befehle anzeigt, die sich gerade im SQL-Cache befinden, muss die folgende
Abfrage ggf. mehrfach täglich zu unterschiedlichen Zeitpunkten aufgerufen werden.
SELECT TO_CHAR(EXECUTIONS, '999G999G990') "EXECUTIONS",
TO_CHAR(BUFFER_GETS, '999G999G990') "GETS",
TO_CHAR(BUFFER_GETS/GREATEST(NVL(EXECUTIONS,1),1), '999G999G990') "GETS_PER_EXEC",
TO_CHAR (ROUND(100*(1-(DISK_READS/GREATEST(NVL(BUFFER_GETS,1),1))),2), '990D00')
TREFFERQUOTE,
SQL_TEXT
FROM V$SQL
WHERE BUFFER_GETS > 1000
ORDER BY BUFFER_GETS DESC
Diese Query sollte man nach verschiedenen Spalten filtern/sortieren, um unterschiedliche Aussagen
zu erhalten. Man kann sich z.B. SQL-Abfragen anzeigen lassen mit geringer Trefferquote („WHERE
TrefferQuote < 80“), vielen IO-Zugriffen („WHERE GETS_PER_EXEC > 10000“) , vielen Ausführungen
(„WHERE EXECUTIONS > 100“), etc.
Beispielhafte Ergebnisse:
EXECUTIONS
GETS
GETS_PER_EXEC
TREFFERQUOTE
SQL_TEXT
--------------------------------------------------------------------------------------------
276
10,696,208
38,754
99.91
SELECT AD_User.AD_User_ID,NULL...
115
1,872,464
16,282
99.70
DELETE FROM OCE_ProdInvMovement WHERE...
115
1,859,686
16,171
99.84
DELETE FROM OCE_ProductionOrderLine WHERE
29
1,853,698
63,921
91.94
SELECT SUM(ol.LineNetAmt * (CASE WHEN...
TREFFERQUOTE
SQL_TEXT
…Where where Trefferquote < 80…:
EXECUTIONS
GETS
GETS_PER_EXEC
-------------------------------------------------------------------------------------------36
653,400
18,150
78.72
SELECT OCP_DAMDocument_ID, Name FROM...
23
443,666
19,290
48.79
select MovementType, MovementQty from...
10
203,048
20,305
68.44
SELECT l.OCE_ProductionOrder_ID, ...
10
182,580
18,258
72.65
SELECT COUNT(ROWNUM) FROM OCP_DAMDocument
7
127,050
18,150
77.04
SELECT OCP_DAMDocument_ID FROM ...
4 PERFORMANCE-TUNING DURCH DB-INDIZES
Bei der Implementierung von mehrschichtigen Java-Anwendungen und Verwendung von PersistenzFrameworks (wie z.B. Hibernate) vergisst man leicht, dass datenbanknahe Optimierungen, wie die
Einführung geeigneter Datenbank-Indizes, ein enormes Potential an Performanceverbesserung
bieten.
4.1 FREMDSCHLÜSSEL-INDIZES
Generell sollten eigentlich alle Fremdschlüssel einer Tabelle einen eigenen Index besitzen.
Beispiel:
CREATE TABLE department (
deptno
, dname
NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
VARCHAR2(14)
);
CREATE TABLE employee (
empno
NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY
, ename
VARCHAR2(10)
, deptno
NUMBER(2)
);
Anlegen eines Fremdschlüssel-Indizes:
CREATE INDEX emp_deptno
ON employee(deptno)
TABLESPACE indx;
4.2 IDENTIFIZIERUNG ANHAND HOHER KOSTEN BEI SQL-STATEMENTS
Ziel: Full Table Scans auf größere Tabellen sind möglichst zu vermeiden – beispielsweise durch
Einführen eines einen DB-Index.
Vorgehen:
1) Man identifiziert anhand der obigen Monitoring-Möglichkeiten ein SQL-Statement,
welches teuer in der Ausführung ist und/oder sehr häufig ausgeführt wird.
2) Für dieses SQL-Statement lässt man einen Ausführungsplan erstellen (in vielen SQLTools und im Oracle SQL Developer möglich ; auf Kommandozeile mittels "explain
plan ..." oder „Autotrace On“)
Bsp.: Auf Kommandozeile:
SQL> set autotrace on
SQL> set linesize 200
SQL> SELECT <ColumnNames> FROM <TableName> JOIN <joins> WHERE <WhereClause>;
3) Für dieses SQL-Statement lässt man einen Ausführungsplan erstellen. Im Ausführungsplan
kann man an den einzelnen Knoten die Kosten für die Teilstatements ablesen. Dort wo hohe
Kosten sind, muss man optimieren.
4) Wenn dort "Table Access (Full)" steht, heißt das, dass kein (geeigneter) DB-Index zur
Verfügung steht und ein „Full Table Scan“ durchgeführt wird, was bei großen Tabellen
immer sehr schlecht ist, da alle Zeilen der Tabelle durchsucht werden müssen! Diese Full
Table Scans sollte man weitestgehend eliminieren, indem man passende Indices einführt.
5) Nach der Index-Erstellung sollte erneut ein Ausführungsplan erstellt werden, um zu
überprüfen, dass der Index genutzt wird und ob er kostenmäßig etwas bringt.
6) Beispiele:
Bsp. 1) Fremdschlüssel-Index
Zu optimierendes SQL-Statement:
SELECT SUM(HandlingTime) FROM M_InOut WHERE OCE_RP_DAY_ID=1013025 AND IsActive='Y';
--> Der Execution Plan zeigt einen Full Table Scan bei der M_InOut-Tabelle an.
Index erstellt:
create index M_InOut_Day on M_InOut(OCE_RP_DAY_ID);
-- Index Statistics generieren:
CALL dbms_stats.gather_index_stats('COMPIERE', 'M_InOut_Day');
Anschließend lief der Befehl deutlich schneller und mit deutlich geringeren "Kosten" ab.
Bsp. 2) Index für Spalten aus Where-Clause:
Zu optimierendes SQL-Statement:
SELECT SUM(OL.QTYORDERED)
FROM C_ORDER O
JOIN C_ORDERLINE OL ON O.C_ORDER_ID=OL.C_ORDER_ID
LEFT OUTER JOIN M_INOUTLINE ML ON ML.C_ORDERLINE_ID=OL.C_ORDERLINE_ID
LEFT OUTER JOIN M_INOUT M ON M.M_INOUT_ID=ML.M_INOUT_ID
WHERE OL.M_PRODUCT_ID=415
AND o.AD_Org_ID=1000000
AND (o.DocStatus='DR' OR o.DOCSTATUS='IP')
AND (m.DocStatus<>'CO' OR m.DocStatus IS NULL)
AND o.ISSOTRX='N'
AND o.PROCESSED='N'
AND o.C_DocTypeTarget_ID NOT IN (SELECT C_DocType_ID FROM C_DocType WHERE Name='Supplier
Claim')
AND ((ol.DatePromised IS NULL AND ol.DateDesired <= SysDate+20)
OR (ol.DatePromised IS NOT NULL AND ol.DatePromised <= SysDate+20))
Execution Plan (vor der Optimierung):
----------------------------------------------------------------------------------------------------------| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
114 |
|
1 |
|
|
1 |
114 |
|
|
|*
2 |
|
|
|
|
|
|
|
3 |
|
|
1 |
114 |
1263
(6)| 00:00:05 |
|
4 |
|
|
25 |
2625 |
1239
(6)| 00:00:05 |
|*
5 |
HASH JOIN ANTI
|
|
25 |
2325 |
1194
(6)| 00:00:04 |
|
6 |
NESTED LOOPS
|
|
36 |
2160 |
1190
(6)| 00:00:04 |
|*
7 |
TABLE ACCESS FULL
| C_ORDERLINE
|
85 |
3060 |
1105
(6)| 00:00:04 |
|*
8 |
TABLE ACCESS BY INDEX ROWID| C_ORDER
|*
9 |
|* 10 |
|
11 |
|* 12 |
|
13 |
|* 14 |
SORT AGGREGATE
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
INDEX UNIQUE SCAN
TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
INDEX UNIQUE SCAN
1263
(6)| 00:00:05 |
|
1 |
24 |
1
(0)| 00:00:01 |
| C_ORDER_KEY
|
1 |
|
0
(0)| 00:00:01 |
| C_DOCTYPE
|
6 |
198 |
3
(0)| 00:00:01 |
| M_INOUTLINE
|
1 |
12 |
2
(0)| 00:00:01 |
| M_INOUTLINE_ORDERLINE |
1 |
|
1
(0)| 00:00:01 |
| M_INOUT
|
1 |
9 |
1
(0)| 00:00:01 |
| M_INOUT_KEY
|
1 |
|
0
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Hier sieht man, dass die meisten Kosten (1105 von 1263) des SQL-Statements durch den „Table
Access Full“ auf die Tabelle C_ORDERLINE in Zeile 7 erzeugt werden. Dieser Zugriff sollte durch
Einführen eines DB-Index optimiert werden. In diesem Bsp. Werden wir alle Spalten der gejointen
Tabelle C_ORDERLINE in den Index aufnehmen, die in der Where-Bedingung abgefragt werden.
Der zweite „Table Access Full“ in Zeile 10 kann ignoriert werden, weil die Tabelle C_DOCTYPE sehr
klein ist (wenige Zeilen enthält) und ein „Full Table Scan“ kein Problem darstellt.
Neuer DB-Index:
create index C_ORDERLINE_QtyOrd on C_ORDERLINE(M_Product_ID, DatePromised, DateDesired);
CALL dbms_stats.gather_index_stats('COMPIERE', 'C_ORDERLINE_QtyOrd');
Execution Plan (nach erfolgreicher Optimierung):
----------------------------------------------------------------------------------------------------------| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
114 |
|
1 |
|
|
1 |
114 |
|
|
|*
2 |
|
|
|
|
|
|
|
3 |
|
|
1 |
114 |
|
4 |
|
|
25 |
2625 |
234
(1)| 00:00:01 |
|*
5 |
HASH JOIN ANTI
|
|
25 |
2325 |
189
(1)| 00:00:01 |
|
6 |
NESTED LOOPS
|
|
36 |
2160 |
185
(0)| 00:00:01 |
|*
7 |
|
85 |
3060 |
100
(0)| 00:00:01 |
|*
8 |
|
101 |
|
3
(0)| 00:00:01 |
|*
9 |
|* 10 |
|* 11 |
|
12 |
SORT AGGREGATE
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID| C_ORDERLINE
INDEX RANGE SCAN
| C_ORDERLINE_QTYORD
TABLE ACCESS BY INDEX ROWID| C_ORDER
INDEX UNIQUE SCAN
TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
258
258
(1)| 00:00:01 |
(1)| 00:00:01 |
|
1 |
24 |
1
(0)| 00:00:01 |
| C_ORDER_KEY
|
1 |
|
0
(0)| 00:00:01 |
| C_DOCTYPE
|
6 |
198 |
3
(0)| 00:00:01 |
| M_INOUTLINE
|
1 |
12 |
2
(0)| 00:00:01 |
|* 13 |
|
14 |
|* 15 |
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
INDEX UNIQUE SCAN
| M_INOUTLINE_ORDERLINE |
1 |
|
1
| M_INOUT
|
1 |
9 |
1
(0)| 00:00:01 |
(0)| 00:00:01 |
| M_INOUT_KEY
|
1 |
|
0
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
7) Weitere Hinweise:

Indices auf Spalten mit geringer Diversität (schwach selektiv) machen nicht so
viel Sinn! (Bsp.: IsActive (Y|N), State(a|b|c|d), AD_Client_ID (1000000,
1000001, ...))

Bei Tabellen, auf denen viel geschrieben wird, sind Indices teuer, da diese
permanent aktualisiert werden müssen.
5 ZUSAMMENFASSUNG
Man sollte daran denken, Oracle‘s Statistiken regelmäßig zu aktualisieren– sowohl interne
Statistiken über die Systemtabellen als auch Statistiken über benutzerdefinierte Tabellen und Indizes.
Mit veralteten Statistiken läuft man Gefahr, dass Oracle falsche SQL-Optimierungen vornimmt.
Außerdem sollte man von Zeit zu Zeit Indizes rebuilden und Tabellen reorganisieren.
Die meisten Performancegewinne erzielt man mit guten DB-Indizes. Diese erstellt man, indem man
den Ausführungsplan (execution plan) von komplexen und/oder häufig auszuführenden SQLStatements analysiert und nach Verursachern von hohen Kosten durchsucht. Außerdem sollten DBIndizes in der Regel auf alle Fremdschlüssel angewendet werden.