Performance unter Oracle
Werbung
Performance unter Oracle .consulting .solutions .partnership DOAG Regionaltreffen 12/2002 Hannover Andreas Habl msg systems ag Performance unter Oracle - Agenda .consulting .consulting.solutions .solutions.partnership .partnership • • • • • • • • • msg systems ag Motivation und Rahmenbedingungen Optimizer Outlines Materialized View Tracing SQL - Grundregeln PL/SQL - Tipps Fragen © msg systems ag, Andreas Habl, Dezember 02 2 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle das unternehmen .consulting .solutions .partnership msg systems ag © msg systems ag, Andreas Habl, Dezember 02 3 Überblick msg systems ag .consulting .consulting.solutions .solutions.partnership .partnership Als produktbasiertes Lösungs- und Service-Haus agieren wir unter den Top 25 der IT-Berater und Systemintegratoren in Deutschland. Kerngeschäft: Branchenspezifische Gesamtlösungen (Beratung, Anwendung, Systemintegration) Gründung: 1980 Geschäftssitz: Ismaning / München Vorstand: Hans Zehetmaier (Vorsitzender) Herbert Enzbrenner Karl-Martin Klein Pius Pflügler Volker Reichenbach Mitarbeiter: > 1400 © msg systems ag, Andreas Habl, Dezember 02 4 Umsatz- und Mitarbeiterentwicklung .consulting .consulting.solutions .solutions.partnership .partnership © msg systems ag, Andreas Habl, Dezember 02 5 Vision und Strategie .consulting .consulting.solutions .solutions.partnership .partnership Wir wollen als markttreibendes, produktbasiertes Lösungsund Servicehaus mit klaren Branchenschwerpunkten attraktiver Partner für unsere Kunden sein. .consulting .solutions .partnership mit mit -- -Branchenspezifisches Branchenspezifisches Kooperation mit --- Kooperation Kooperation Branchenspezifisches Standardsoftware --Standardsoftware Standardsoftware Technologiepartnern Know-how Know-how Technologiepartnern Individuelle - -Individuelle Know-how Technologiepartnern - Anwendungssysteme Individuelle Anwendungssysteme Service vor Anwendungssysteme - -Technologie-- Service vorOrt Ort Technologiekompetenz --kompetenz Technologiekompetenz Verlässlichkeitvor und Ort --- Verlässlichkeit Service - Systemintegration und Methodenberatung -- Systemintegration Systemintegration Glaubwürdigkeit - Methodenberatung Glaubwürdigkeit SAP Beratung -- Methodenberatung - Verlässlichkeit und - SAP Beratung Glaubwürdigkeit - SAP Beratung .. © msg systems ag, Andreas Habl, Dezember 02 6 Struktur .consulting .consulting.solutions .solutions.partnership .partnership msg systems ag München Berlin Chemnitz Frankfurt/M. Hamburg Hannover Köln Passau Stuttgart Basel Zürich Wien Chicago New York weitere Landesgesellschaften Tochterunternehmen / Beteiligungen msg msg systems systems gmbh, gmbh, Österreich Österreich msg msg Consulting Consulting für für Versicherungen Versicherungen GmbH GmbH msg msg systems systems AG, AG, Schweiz Schweiz msg msg netzwerkservice netzwerkservice gmbh gmbh msg msg systems systems Inc, Inc, USA USA msg msg systems systems nord nord gmbh gmbh msg msg at.NET at.NET GmbH GmbH CONPLAN CONPLAN GmbH GmbH Gillardon Gillardon financial financial software software AG AG innovas innovas GmbH GmbH PREVO-System PREVO-System AG, AG, Schweiz Schweiz DELVIN DELVIN GmbH GmbH © msg systems ag, Andreas Habl, Dezember 02 7 Standorte .consulting .consulting.solutions .solutions.partnership .partnership Chicago New York Hamburg Hannover Köln Berlin Chemnitz Frankfurt/M. Stuttgart München/ Ismaning Basel © msg systems ag, Andreas Habl, Dezember 02 Passau Wien Zürich 8 Leistungsspektrum .consulting .consulting.solutions .solutions.partnership .partnership Know-how für zukunftssichere integrierte Lösungen Leistungsspektrum - Full-Service für Business-Lösungen - Individuelle Anwendungsentwicklung - SAP-Beratung und -Entwicklung Branchenlösungen - Versicherungen - Finanzdienstleistungen - Gesundheitswesen Seit 1996 nach DIN EN ISO 9001 zertifiziert © msg systems ag, Andreas Habl, Dezember 02 9 Kooperationen (Auszug) .consulting .consulting.solutions .solutions.partnership .partnership © msg systems ag, Andreas Habl, Dezember 02 10 Branchen und Kunden (Auszug) •• Allianz Allianz •• Allianz Allianz World World Wide Wide Care Care •• ASPECTA ASPECTA •• AXA AXA Colonia Colonia •• Basler Basler Versicherungen,CH Versicherungen,CH •• Bayer. Bayer. Beamten Beamten Vers. Vers. •• DBV DBV Winterthur Winterthur •• GARANTA, GARANTA, CH CH •• Gerling Gerling •• Gothaer Gothaer Rück Rück •• Helvetia Helvetia Patria, Patria, CH CH •• HUK-Coburg HUK-Coburg •• Mannheimer Mannheimer •• Münchener Münchener Rück Rück •• NÜRNBERGER NÜRNBERGER Vers. Vers. •• Quelle Quelle Versicherungen Versicherungen •• BHW BHW •• BMW BMW Financial Financial Services Services •• Commerzbank Commerzbank •• Deutsche Deutsche Bank Bank •• DZ-Bank DZ-Bank •• Dresdner Dresdner Bank Bank •• Deutsche Deutsche Börse Börse Systems Systems •• HELABA HELABA •• Hamburgische Hamburgische Landesbank Landesbank •• IZB IZB SOFT SOFT •• Bayerische Bayerische LandesbauLandesbausparkasse sparkasse (LBS) (LBS) •• Sparkassen Sparkassen Informatik Informatik •• SI-BW SI-BW •• Bausparkasse Bausparkasse Schwäbisch Schwäbisch Hall Hall •• Stadtsparkasse Stadtsparkasse Köln Köln •• Sparkassenversicherung Sparkassenversicherung •• WestLB WestLB •• Vereinte Vereinte Versicherungen Versicherungen •• Wüstenrot Wüstenrot Hypothekenbank Hypothekenbank •• VersicherungsVersicherungskammer kammer Bayern Bayern •• BARMER BARMER Ersatzkasse Ersatzkasse •• Bau Bau BerufsgenossenBerufsgenossenschaft schaft Rheinland/Westf. Rheinland/Westf. •• BundesinnungskrankenBundesinnungskrankenkasse kasse Gesundheit(BIG) Gesundheit(BIG) •• Deutsche Deutsche KrankenKrankenversicherung versicherung (DKV) (DKV) •• IKK-Bundesverband IKK-Bundesverband •• IKK IKK Münsterland Münsterland •• IKK IKK Sachsen-Anhalt Sachsen-Anhalt •• IKK IKK Westfalen-Lippe Westfalen-Lippe •• Johanniter-Krankenhaus Johanniter-Krankenhaus Bonn Bonn •• Kaiserswerther Kaiserswerther Diakonie Diakonie •• SIGNAL SIGNAL Krankenvers. Krankenvers. •• Techniker Techniker Krankenkasse Krankenkasse •• Vereinte Vereinte Krankenvers. Krankenvers. •• ... ... Industrie/ Behörden/ Dienstleistungen •• Allstate, Allstate, USA USA •• Bayerische Bayerische Landesbank Landesbank Gesundheitswesen •• ADAC-Versicherung ADAC-Versicherung Finanzdienstleistungen Versicherungen .consulting .consulting.solutions .solutions.partnership .partnership •• AUDI AUDI AG AG •• BerliKomm BerliKomm •• Berliner Berliner Wasserbetriebe Wasserbetriebe •• BMW BMW AG AG •• BRK BRK •• Brunata Brunata •• Bundeswehr/-Marine Bundeswehr/-Marine •• Deutsche Deutsche BA BA •• Europ. Europ. Patentamt Patentamt •• Gebühreneinzugszentrale Gebühreneinzugszentrale (GEZ) (GEZ) •• Girmes Girmes GmbH GmbH •• Hoffmann Hoffmann Werkzeuge Werkzeuge •• Hessischer Hessischer Rundfunk Rundfunk •• Kirch Kirch Gruppe Gruppe •• Rolls Rolls Royce Royce Deutschland Deutschland •• Deutsche Deutsche Telekom Telekom •• VW VW AG AG •... •... •• ... ... •• ... ... © msg systems ag, Andreas Habl, Dezember 02 11 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle Motivation und Rahmenbedingungen © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 12 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Motivation für Performancebetrachtungen • Funktionalität und Performance sind zu gleichen Teilen der Ausschlag für die Akzeptanz einer Applikation und den Erfolg eines Projekts • Komplexität steigt stetig (Anforderungen bezüglich Datenmengen, Auswertbarkeit, Verfügbarkeit usw.) • Softwarequalität • Kostendruck © msg systems ag, Andreas Habl, Dezember 02 13 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Einflussfaktoren für Performance • Softwareplattform (Betriebssystem, Netzwerk) • Hardware • Oracle Konfiguration (Instanzkonfiguration) • Daten und Datendesign (Menge, Datenverteilung, Datenmodell, Indizierung) • Applikation (Design, Architektur, Entwicklung = Datenzugriff) © msg systems ag, Andreas Habl, Dezember 02 14 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Potenziale der Einflussfaktoren Faktor Steigerungsraten Hardware bis 25 % Betriebssystem bis 50 % Oracle Konfiguration bis 100 % Datenbankdesign bis 1000 % Datenverteilung/Indizierung bis 1000 % SQL-Statements bis ? (LECCOTECH Studie) © msg systems ag, Andreas Habl, Dezember 02 15 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Hauptursache 60 % der PerformanceProbleme im Datenbankbereich werden durch die Applikation, insbesondere durch den SQL-Zugriff, verursacht. Quelle von Performanceproblemen 40% 60% (Oracle und Gartner Studie) Andere Folge: Applikation Ein Großteil der Performanceprobleme liegen nicht im unmittelbaren Einflussbereich des DBA. © msg systems ag, Andreas Habl, Dezember 02 16 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Performance als Teamwork Entwickler QualitätsManagement Designer Oracle DBA © msg systems ag, Andreas Habl, Dezember 02 17 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Auswirkungen auf Projektarbeit • Sensibilisierung der Projektmitarbeiter (SQL-Guide, Workshops) • Einbindung des Themas „SQL und Performance“ in den Softwareentwicklungsprozess (insbesondere Prüfung im Qualitätsprozess) • aktive Betreuung der Entwickler und Designer während der gesamten Projektdauer durch DBA • Bereitstellung geeigneter Umgebungen • Verschiebung von Verantwortlichkeiten • bei Notwendigkeit strikte Trennung von fachlichem und technischen Modell • Tuning nicht zum Selbstzweck sondern in definiertem Rahmen © msg systems ag, Andreas Habl, Dezember 02 18 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership DB - Umgebung • Standard: verschiedene Umgebungen für Entwicklung, Test und Abnahme • eindeutige Versionierung des Datenbankmodells und Hinterlegung im Datenbestand • Bereitstellung von Testdaten (notfalls manuell) durch DBA • produktionsnahe Bedingungen • Massendaten mit realitätsnaher Datenverteilung • Massendatengenerierung • wenn möglich Statistiken aus Produktion (Export von Statistiken) © msg systems ag, Andreas Habl, Dezember 02 19 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Testumgebung • • • • • • Schaffung gleicher Bedingungen für Testläufe Daten-Baseline (cold-Backup, Ex-/Import) jeweils nur eine Änderung testen Testläufe tracen konsistente Last der Hardware Beachten -> erstes Ausführen von SQL beinhaltet das Laden in die SQLAREA • Verwendung eigener Protokollfunktion • sinnvollen Output erzeugen (Table, LogFile) • Verwendung von Timerfunktionen • DBMS_APPLICATION_INFO für RealTime-Info © msg systems ag, Andreas Habl, Dezember 02 20 Performance unter Oracle - Rahmenbedingungen .consulting .consulting.solutions .solutions.partnership .partnership Identifizierung kritischer Zugriffe und Verantwortungsdelegation • möglichst frühzeitiges Identifizieren (Vermeiden) von kritischen Zugriffen durch DBA • Mitwirkung bei fachlicher Spezifikation • Analyse der Fachspezifikation • Empfehlungen vor DV-Konzept • Verantwortungsdelegation • Kapselung kritischer Zugriffe (Views) • schnellere Zielerreichung während der Entwicklung • bessere Wartbarkeit/Flexibilität und Kontrolle durch DBA (kritische Zugriffe unterliegen der höchsten Änderungsrate) © msg systems ag, Andreas Habl, Dezember 02 21 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle Optimizer © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 22 Performance unter Oracle - Optimizer .consulting .consulting.solutions .solutions.partnership .partnership SQL-Processing © msg systems ag, Andreas Habl, Dezember 02 23 Performance unter Oracle - Optimizer .consulting .consulting.solutions .solutions.partnership .partnership Überblick Optimizer • erstellt Execution-Plan • Ansicht des Execution-Plan mit „explain plan“ oder „set autotrace on“ in SQL*Plus • plan_table notwendig ($ORACLE_HOME\rdbms\admin\utlxplan.sql) • ist beeinflussbar • Init.ora-Parameter • Session-Parameter • Hints • DataDictionary • Unterscheidung Rule-Based (RBO) / Cost-Based (CBO) • Mischung von RBO und CBO dringendst vermeiden © msg systems ag, Andreas Habl, Dezember 02 24 Performance unter Oracle - Optimizer .consulting .consulting.solutions .solutions.partnership .partnership Rule-Based-Optimizer • ältere Variante • benutzt festes Regelwerk zur Erstellung des ExecutionPlans anhand • Aufbau des Statements • interner Rangliste von Zugriffsmethoden • relativ leicht nachvollziehbar • Fehlertoleranz für Statements ist sehr gering • kollabiert gelegentlich bei zu vielen Joins • keinerlei Weiterentwicklung durch Oracle • unterstützt keine neuen Features (partitioned tables, erweiterte star query, materialized views) • in Oracle 10 (i?) nicht mehr enthalten !!! © msg systems ag, Andreas Habl, Dezember 02 25 Performance unter Oracle - Optimizer .consulting .consulting.solutions .solutions.partnership .partnership Cost-Based-Optimizer • • • • • • • neuere Version benutzt Statistiken über Tabellen und Indizes fehlertoleranter und „intelligenter“ als RBO deutlich weniger Probleme bei komplexen Queries schwer nachvollziehbar unterstützt alle Oracle-Funktionalitäten in Version 7.x nicht empfehlenswert; ab 8.0.5 benutzbar; ab 8i sehr stabil • Extensible Option für eigene Definitionen • Praxiserfahrung: • CBO auch ohne Statistiken (es werden DefaultStatistiken benutzt) in der Regel besser als RBO © msg systems ag, Andreas Habl, Dezember 02 26 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle Outlines © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 27 Performance unter Oracle - Outlines .consulting .consulting.solutions .solutions.partnership .partnership Outlines - Stabilität des Execution-Plans • • • • • • • • • ermöglicht ein gleiches Verhalten von Queries in verschiedenen Umgebungen sind Execution-Plans für einzelne Queries Execution-Plan wird im Dictionary gespeichert: • Statement „Create Outline“ • System-Parameter „CREATE_STORED_OUTLINE“ Bildung von Kategorien (Batch vs. OLTP) Export / Import von Outlines (Package OUTLN_PKG) Verwendung der Outlines durch System-Parameter „USE_STORED_OUTLINES“ Outline-Dictionary im Schema OUTLN Outlines sind Grundlage verschiedener Performancetools nach der Hammer-Methode (draufschlagen – abwarten was rauskommt) ab 9i ist Editieren der Outlines über OEM möglich © msg systems ag, Andreas Habl, Dezember 02 28 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle Materialized View © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 29 Performance unter Oracle - Materialzed View .consulting .consulting.solutions .solutions.partnership .partnership Materialized View Überblick • Grundprinzip ist ein alter Hut -- in ersten 8i-Releases create materialized view test_mv …; snapshot created. • • • • • • • Materialized View und Snapshot sind synonym zu verwenden Ergebnis der Materialized View wird als Schemaobjekt physikalisch gespeichert Möglichkeit der Replizierung und Verteilung von Daten in verschiedensten Umgebungen (lokal, verteilt, mobil) können genutzt werden, um oft benötigte Auswertungen dramatisch zu beschleunigen verschiedene Aktualisierungen möglich (Zeitintervalle, manuell, bei Änderungen) können direkt angesprochen werden Cost-Based-Optimizer ist erforderlich © msg systems ag, Andreas Habl, Dezember 02 30 Performance unter Oracle - Materialzed View .consulting .consulting.solutions .solutions.partnership .partnership Materialized View / Query Rewrite • • • • Query Rewrite ist die eigentliche Neuerung bei aktiviertem Query Rewrite wird bei bei jeder query geprüft, ob sie mit der Materialized View kompatibel ist – wenn ja, wird die query umgeschrieben (rewrite) und profitiert von dem bereits vorliegenden Ergebnis der Materialized View Applikation muss die Materialized View nicht kennen Voraussetzungen: • User muss Systemprivileg „QUERY REWRITE“ besitzen • Parameter QUERY_REWRITE_ENABLED auf Session oder Instanzebene: • FALSE : kein rewrite • TRUE : cost-based rewrite (CBO prüft Kosten) • FORCE : rewrite erzwungen • (QUERY_REWRITE_ENABLED ist auch Voraussetzung für function-based indexes) © msg systems ag, Andreas Habl, Dezember 02 31 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle Tracing © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 32 Performance unter Oracle - Tracing .consulting .consulting.solutions .solutions.partnership .partnership Tracing Überblick • • • Tracing ist auf Session oder Instanzebene möglich detaillierte Informationen: • Execution Plan • Parse, execute and fetch counts • CPU and elapsed time • Physical and logical reads • number of rows processed • misses on the library cache • commit and rollback immer zwei Schritte: • Erzeugen des Trace • Formatieren des Trace im tkprof © msg systems ag, Andreas Habl, Dezember 02 33 Performance unter Oracle - Tracing .consulting .consulting.solutions .solutions.partnership .partnership Tracing Vorgehen • • • • • Initialisierungsparameter setzen • TIMED_STATISTICS (optional) • MAX_DUMP_FILE_SIZE (optional) • USER_DUMP_DESTINATION Trace einschalten • Sessionebene: alter session set sql_trace = true (als Parameter des Programms vorsehen) • Instanceebene: alter system set sql_trace = true (nicht empfehlenswert) • beliebige Session: dbms_system.set_sql_trace_in_session formatierten Output mit tkprof erstellen • tkprof tracefile outputfile explain=user/pwd sys=no Output interpretieren optional in DB speichern © msg systems ag, Andreas Habl, Dezember 02 34 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle SQL - Grundregeln © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 35 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership der meist begangene „Fehler“ / Zuweisung beim Join Es sollen Name, Anschriftstatus und Hausnummer zu person_id = 10 selektiert werden. select from where and and prs.name, ans.status, hau.nummer personen prs, anschriften ans, haeuser hau prs.person_id = 10 prs.person_id = ans.pers_person_id ans.haus_haus_id = hau.haus_id select from where and and prs.name, ans.status, hau.nummer personen prs, anschriften ans, haeuser hau prs.person_id = 10 ans.pers_person_id = prs.person_id hau.haus_id = ans.haus_haus_id Regel in der where-clause: Ungekannte = Bekannte © msg systems ag, Andreas Habl, Dezember 02 36 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership schlecht lesbare Statements select from where and and and and and and and name, vorname, status, nummer personen a, anschriften b, haeuser c a.person_id = 10 c.historisch = ‘Y’ b.pers_person_id = a.person_id c.nummer >= 10 a.name like ‘MEI%’ b.status = ‘X’ c.haus_id = b.haus_haus_id c.nummer <= 50 © msg systems ag, Andreas Habl, Dezember 02 select from where and and and and and and prs.name, prs.vorname, ans.status, hau.nummer personen prs, anschriften ans, haeuser hau prs.person_id = 10 prs.name like ‘MEI%’ ans.pers_person_id = prs.person_id ans.status = ‘X’ hau.haus_id = ans.pers_person_id hau.nummer between 10 and 50 hau.historisch = ‘Y’ 37 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership Subqueries aus Bequemlichkeit Es sollen alle Personen mit Anschriftstatus = „Y“ selektiert werden. select from where name, vorname, personen, person_id in (select pers_person_id from anschriften where status = ‘Y’) select from where and prs.name, prs.vorname, personen prs, anschriften ans, ans.status = ‘Y’ prs.person_id = ans.pers_person_id Regel: Join vor Subquery, weil Subqueries den Optimizer einschränken © msg systems ag, Andreas Habl, Dezember 02 38 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership Vermeidung von Sorts beim group by Es sollen die Personen mit dem Datum ihrer letzten Lohnsteuerklasse selektiert werden. select prs.person_id prs.name, max(lst.datum) from personen prs, lst_karte lst, where lst.pers_person_id = prs.person_id group by prs.person_id, prs.name =>Sort über gesamte Ergebnismenge select from where prs.person_id prs.name, lst.datum personen prs, (select max(datum) datum, pers_person_id from lst_karten group by pers_person_id) lst lst.pers_person_id = prs.person_id =>Sorts über kleinere Ergebnismengen Einsatz hängt von der Datenverteilung ab © msg systems ag, Andreas Habl, Dezember 02 39 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership Unnötige Sorts in Views create view personen_view as select * from personen order by name; -- select auf view mit order by auf andere Spalte select name, vorname … from personen_view order by vorname; • • • • seit 8i (8.1.5) ist ein „order by“ in Views erlaubt wird oft aus Versehen verwendet bei select auf View mit „order by“ mit ebenfalls „order by“ nach einer anderen Spalte => dann wird zweimal sortiert „order by“ in Views macht nur in Spezialfällen Sinn © msg systems ag, Andreas Habl, Dezember 02 40 Performance unter Oracle - Grundregeln .consulting .consulting.solutions .solutions.partnership .partnership Subquerys: In / Exists -- Beispiel IN non-correlated -- Beispiel EXISTS correlated select name from personen where person_id in (select pers_person_id from anschriften where status = ‘Y’) select prs.name from personen prs where exists (select pers_person_id from anschriften where pers_person_id = prs.person_id and status = ‘Y’) • • • • • siehe „Subquerys aus Bequemlichkeit“ – wenn möglich Join IN führt „inner Query“ zuerst und einmalig aus (geeignet für große Datenmengen) EXISTS führt zu jeder Row der „outer Query“ die „inner Query“ aus (geeignet für kleinere Datenmengen) ob IN oder EXISTS schneller ist vom Einzelfall abhängig Zitat: „ You must understand the number of rows to be processed” © msg systems ag, Andreas Habl, Dezember 02 41 .consulting .consulting.solutions .solutions.partnership .partnership Performance unter Oracle PL/SQL-Tips © msg systems ag, Andreas Habl, Dezember 02 .consulting .solutions .partnership 42 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership DBMS_APPLICATION_INFO • Mechanismus zum Publizieren von real-timeInformationen • kein commit notwendig • SET_MODULE Procedure • Update von v$session und v$sqlarea • Spalten „module“ und „actions“ • v$session wird unmittelbar aktualisiert • SET_SESSION_LONGOPS • Update auf v$session_longops © msg systems ag, Andreas Habl, Dezember 02 43 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership DBMS_APPLICATION_INFO - Codebeispiel DECLARE lv_count PLS_INTEGER := 0; lv_start_time PLS_INTEGER; BEGIN lv_start_time := DBMS_UTILITY.GET_TIME; FOR cur_employee_rec IN cur_employee LOOP lv_count := lv_count + 1; -- Employee processing logic... IF MOD(lv_count, 1000) = 0 THEN DBMS_APPLICATION_INFO.SET_MODULE (‘Anzahl Zeilen: ' || lv_count, ‘Dauer: ' || (DBMS_UTILITY.GET_TIME lv_start_time)/100 || ' sec'); END IF; END LOOP; END; / © msg systems ag, Andreas Habl, Dezember 02 44 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership DBMS_APPLICATION_INFO - Outputbeispiel select username, module, action from v$session where username = ‘HABLA’; USERNAME - - - HABLA HABLA MODULE - - - - - - - - - - - - SQL*Plus Anzahl Zeilen: 1000 USERNAME - - - HABLA HABLA MODULE - - - - - - - - - - - - SQL*Plus Anzahl Zeilen: 50000 © msg systems ag, Andreas Habl, Dezember 02 ACTION - - - - - - - - Dauer: 10.64 sec ACTION - - - - - - - - Dauer: 180.32 sec 45 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership PL/SQL Tables • • • • • • PL/SQL Tables sind ähnlich Arrays Benutzung für temp. Speichern und table lookups nicht geeignet für große Datenmengen benötigen Memory pro User seq. scan auf PL/SQL Table ist schneller als DB–Read Benutzung von Table-Indexes möglich DECLARE TYPE temp_table IS TABLE OF VARCHAR2(50) INDEX BY BINARY INTEGER; v_temp_table temp_table; BEGIN FOR v_count IN 1..100 LOOP v_temp_table(v_count) := ‘Beispiel’; END LOOP; END; / © msg systems ag, Andreas Habl, Dezember 02 46 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Bulk-Binding mit Collections • • bei der Ausführung von sql in pl/sql kommt es zu einer Kommunikation zwischen pl/sql-engine und sql-engine mit Kontextwechseln Bulk-Binding reduziert Kommunikation und Kontextwechsel bei der Verwendung von Collections DECLARE TYPE liste IS VARRAY (100) OF NUMBER; nummer LISTE := LISTE (1024, 2754, 5432); BEGIN FORALL i IN nummer.first .. nummer.last UPDATE sal = 1.1 * sal WHERE mgr = nummer(i); END; / • • • FORALL übergibt einmalig Collection an sql-engine in normaler Schleife würde dies bei jedem Durchlauf geschehen mit RETURNING ... BULK COLLECT INTO Rückgabe möglich © msg systems ag, Andreas Habl, Dezember 02 47 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership ROWID • • • durch Benutzung der ROWID Performance verbessern ROWID ist der schnellste Zugriff auf einen Datensatz ROWID ist schneller als unique index - Zugriff DECLARE CURSOR cur_employee IS SELECT employee_id, salary, ROWID FROM employee; v_new_salary NUMBER; BEGIN FOR cur_employee_rec IN cur_employee LOOP v_new_salary := cur_employee_rec.salary * 1.1; UPDATE employee SET salary = v_new_salary WHERE rowid = cur_employee_rec.ROWID; END LOOP; COMMIT; END; / © msg systems ag, Andreas Habl, Dezember 02 48 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Datentypenkonformität • • • bei Vergleichen von Variablen und Konstanten sollte der gleiche Datentyp verwendet werden bei unterschiedlichen Datentypen konvertiert Oracle einen der Werte die Konvertierung erzeugt Overhead DECLARE v_temp_count NUMBER := 10; BEGIN IF v_temp_count = ‘1’ THEN … © msg systems ag, Andreas Habl, Dezember 02 49 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership PLS_INTEGER versus NUMBER • • • • • PLS_INTEGER existiert seit PL/SQL Version 2.2 Range ab 8i: -2**31 .. 2**31 PLS_INTEGER benutzt weniger Speicher als NUMBER Operationen mit PLS_INTEGER benutzen „machine arithmetic“ und sind schneller als Operationen mit NUMBER gut verwendbar für Zähler und Integer-Operationen DECLARE v_temp_count PLS_INTEGER := 10; BEGIN IF v_temp_count = 1 THEN … © msg systems ag, Andreas Habl, Dezember 02 50 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Reihenfolge IF Statements • • • IF Statements sind typischerweise nach Gesichtspunkten der Lesbarkeit sortiert Verbesserung der Performance durch die Umsortierung der IF Statements nach zu erwartender Häufigkeit besonders bei ELSIF IF status = 3 THEN bonus = 100; ELSIF status = 1 THEN bonus = 50; ELSE bonus = 10; END IF; © msg systems ag, Andreas Habl, Dezember 02 51 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership PL/SQL in Datenbank speichern • durch Speichern von PL/SQL in der Datenbank ergeben sich folgende Vorteile: • PL/SQL Code ist bereits compiliert (p-code) • Möglichkeit den Code in den Shared Pool zu pinnen • Umsetzung von Securitymechanismen auf Datenbankebene • Vermeidung von Code-Redundanz und Versionskonflikten • Abhängigkeiten zwischen dem Code können abgefragt werden • weniger Speicherverbrauch, weil nur eine Kopie des Codes im Speicher gehalten wird • kürzere Ladezeiten bei Benutzung von Packages, weil gesamtes Package wird bei erster Verwendung in Speicher geladen © msg systems ag, Andreas Habl, Dezember 02 52 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Pinning Objects • • • • • Objekte im Shared Pool werden über LRU (Least Recently Used) entfernt ein Objekt im Shared Pool benötigt zusammenhängenden Speicher (Vermeidung von Fragmentierung notwendig) manuelle geladene Objekte im Shared Pool unterliegen nicht der LRU manuelles Laden vermeidet Fragmentierung und gewährleistet, dass Objekte nicht entladen werden DBMS_SHARED_POOL.KEEP Procedure • Laden und Entladen von Objekten im Shared Pool DBMS_SHARED_POOL.KEEP ( name VARCHAR2, flag CHAR DEFAULT ‘P’); DBMS_SHARED_POOL.KEEP (‘MY_SOURCE’, ‘P’); © msg systems ag, Andreas Habl, Dezember 02 53 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Schleifen-Logik • PL/SQL code mit Schleifen-Logik sind Kandidaten für Performancesteigerungen aufgrund der Anzahl der Durchläufe • Logik ist auf Verbesserungsmöglichkeiten zu prüfen • auch kleine Verbesserungen lohnen sich in der Summe • etwa durch: • PL/SQL Tables • Verwendung ROWID • Bulk-Binding • PLS_INTEGER versus NUMBER • Datentypkonformität • ... © msg systems ag, Andreas Habl, Dezember 02 54 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership weitere PL/SQL Performance Tips • • • • • • Beobachtung und Anpassung des Shared Pool Ausführung möglichst auf dem Server Benutzung von temporary tables Anpassung der Rollback-Segmente begrenzte Nutzung von dynamischen SQL Anwendung der Oracle PL/SQL-Packages © msg systems ag, Andreas Habl, Dezember 02 55 Performance unter Oracle - PL/SQL-Tips .consulting .consulting.solutions .solutions.partnership .partnership Vielen Dank für Ihre Aufmerksamkeit ! .consulting .solutions .partnership Referenten: Andreas Habl – Systemingenieur, msg systems ag Berlin http://www.msg.de © msg systems ag, Andreas Habl, Dezember 02 [email protected] 56
