Online PR & Social Media News - Autorenportal

In einem Kundenprojekt ist uns aufgefallen, dass die Oracle-Datenbank immer langsamer agierte. Ständig wachsende Datenmengen oder auch mangelnde Optimierung in der Persistenzschicht der Anwendung waren Vermutungen. Durch einen Oracle-Experte wurden die Schwachstellen gefunden und die Gründe für Anwendungsentwickler in einem kurzen Artikel zusammengefasst.

Die ObjectCode GmbH ist bei einem Kundenprojekt darüber gestolpert, dass Anwendung und Oracle-Datenbank-Instanz über Jahre langsamer wurde. Zuerst wurde vermutet, dass das Problem durch ständig wachsenden Datenmenge und mangelnden Optimierung in der Persistenzschicht der Applikation hervorgerufen wurde. Durch einen externen Oracle-Berater wurde aber festgestellt, dass die Oracle-Datenbank immer wieder optimiert werden muss. In einem Artikel hat unsere Mitarbeiter, Michael Heyduk, seine eigenen Erfahrungen zum Thema Performance-Analyse und –Tunings für Anwendungsentwickler zusammengefasst.

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 REBUILD;

Es ist sinnvoll, alle Indizes in einem eigenen Tablespace zu speichern. Gleichzeitig mit einem Index-Rebuild kann man ggf. das Verschieben in den Tablespace „INDX" folgendermaßen durchführen:

ALTER INDEX REBUILD TABLESPACE INDX;

2.2 TABELLEN-REORGANISATION

Eine Defragmentierung von Tabellen durch deren Neuaufbau(„Rebuild") ist von Zeit zu Zeit durchzuführen:

ALTER TABLE 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 dba_tab_statistics WHERE owner='APPLICATION_XYZ';

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(' ', ' ');

CALL dbms_stats.gather_Table_stats(' ', ' ');

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: COMPIERE.OCP_CRM_CORRESPONDENCE: 88381 out of 88381 Blocks done

88381 11

Table Scan COMPIERE.PWORKITEMS

Table Scan: COMPIERE.PWORKITEMS: 82432 out of 82432 Blocks done

82432 43

Table Scan COMPIERE.PBPEPROCESSINSTS

Table Scan: COMPIERE.PBPEPROCESSINSTS: 23552 out of 23552 Blocks done

23552 66

Table Scan COMPIERE.M_TRANSACTION

Table Scan: COMPIERE.M_TRANSACTION: 19277 out of 19277 Blocks done

19277 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...

…Where where Trefferquote < 80…:

EXECUTIONS GETS GETS_PER_EXEC TREFFERQUOTE SQL_TEXT

--------------------------------------------------------------------------------------------

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 Persistenz-Frameworks (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 NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY

, dname 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 SQL-Tools 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 FROM JOIN WHERE ;

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 | 1263 (6)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | 114 | | |

|* 2 | FILTER | | | | | |

| 3 | NESTED LOOPS OUTER | | 1 | 114 | 1263 (6)| 00:00:05 |

| 4 | NESTED LOOPS OUTER | | 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 | 1 | 24 | 1 (0)| 00:00:01 |

|* 9 | INDEX UNIQUE SCAN | C_ORDER_KEY | 1 | | 0 (0)| 00:00:01 |

|* 10 | TABLE ACCESS FULL | C_DOCTYPE | 6 | 198 | 3 (0)| 00:00:01 |

| 11 | TABLE ACCESS BY INDEX ROWID | M_INOUTLINE | 1 | 12 | 2 (0)| 00:00:01 |

|* 12 | INDEX RANGE SCAN | M_INOUTLINE_ORDERLINE | 1 | | 1 (0)| 00:00:01 |

| 13 | TABLE ACCESS BY INDEX ROWID | M_INOUT | 1 | 9 | 1 (0)| 00:00:01 |

|* 14 | INDEX UNIQUE SCAN | 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 | 258 (1)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 114 | | |

|* 2 | FILTER | | | | | |

| 3 | NESTED LOOPS OUTER | | 1 | 114 | 258 (1)| 00:00:01 |

| 4 | NESTED LOOPS OUTER | | 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 | TABLE ACCESS BY INDEX ROWID| C_ORDERLINE | 85 | 3060 | 100 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | C_ORDERLINE_QTYORD | 101 | | 3 (0)| 00:00:01 |

|* 9 | TABLE ACCESS BY INDEX ROWID| C_ORDER | 1 | 24 | 1 (0)| 00:00:01 |

|* 10 | INDEX UNIQUE SCAN | C_ORDER_KEY | 1 | | 0 (0)| 00:00:01 |

|* 11 | TABLE ACCESS FULL | C_DOCTYPE | 6 | 198 | 3 (0)| 00:00:01 |

| 12 | TABLE ACCESS BY INDEX ROWID | M_INOUTLINE | 1 | 12 | 2 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | M_INOUTLINE_ORDERLINE | 1 | | 1 (0)| 00:00:01 |

| 14 | TABLE ACCESS BY INDEX ROWID | M_INOUT | 1 | 9 | 1 (0)| 00:00:01 |

|* 15 | INDEX UNIQUE SCAN | 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 SQL-Statements analysiert und nach Verursachern von hohen Kosten durchsucht. Außerdem sollten DB-Indizes in der Regel auf alle Fremdschlüssel angewendet werden.