WWW.BOOK.DISLIB.INFO
FREE ELECTRONIC LIBRARY - Books, dissertations, abstract
 
<< HOME
CONTACTS



Pages:   || 2 |

«Manuelles Oracle SQL Tuning Martin Decker ora-solutions.net D/A/CH Schlüsselworte Optimizer, SQL Tuning, Performance, Explain Plan, Hints, ...»

-- [ Page 1 ] --

Manuelles Oracle SQL Tuning

Martin Decker

ora-solutions.net

D/A/CH

Schlüsselworte

Optimizer, SQL Tuning, Performance, Explain Plan, Hints,

Einleitung

Der Oracle Cost-Based Optimizer ist die zentrale Komponente der Datenbank und mittlerweile recht

ausgereift. In den seltenen Fällen, in denen er allerdings keinen performanten Ausführungsplan ermitteln

kann, empfiehlt Oracle - bei entsprechender Lizenzierung - die Verwendung des SQL Tuning Advisors zur

automatischen Ermittlung von Verbesserungspotential. Der DBA sollte allerdings auch ohne diesen Assistenten in der Lage sein, das SQL Statement manuell zu analysieren. Der Vortrag gibt eine Einführung in das manuelle SQL Tuning anhand eines einfachen 2 Table-Join Beispiels. Es werden die Entscheidungen des Cost-Based Optimizers nachvollzogen und die wichtigsten Aspekte anhand des Beispiels beleuchtet, z.B.

Join Method, Join Order, Access Paths, Optimizer Goal, Statistiken und insbesondere Histogramme im Zusammenhang mit Bind-Variablen. Im Abschluss werden die verfügbaren Werkzeuge zur Veränderung eines Ausführungsplans erläutert.

Identifikation des relevanten SQL Statements

Als erstes gilt es, das relevante, problematische SQL Statement zu identifizieren, das auf PerformanceOptimierung untersucht werden soll. Hierfür gibt es verschiedene Möglichkeiten:

 AWR (Diagnostic Pack License) / Statspack Report über bestimmtes Intervall  SQL Tracing (Trace Event 10046 bzw. DBMS_MONITOR)  Active Session History (Diagnostic Pack License)  Oracle Enterprise Manager – DB Performance Page (Diagnostic Pack License)  SQL Performance Monitor (Tuning Pack License) Die Vorgehensweise zur Identifikation des relevanten Statements wird hier nicht beschrieben. Ist das SQL Statement identifiziert kann mit der eigentlichen Untersuchung bzw. der Optimierung begonnen werden.

Ablauf-Phasen

Die Vorgehensweise kann in 3 Phasen unterteilt werden:

•Ermittlung der Ausführungs-Statistiken (Elapsed Time, Buffer Gets, Disk Reads, Executions) aus V$SQLSTATS bzw. DBA_HIST_SQLSTAT Analyse •Ermittlung Ausführungsplan (DBMS_XPLAN)

•optional: Ermittlung Bind-Variablen (v$sql_bind_capture bzw. DBMS_XPLAN)

•Reproduzieren der sub-optimalen Ausführungsperformance mittels SQL*Plus Reproduktion bzw. SQL Developer Tuning •Reduzierung der Buffer Gets des Statements durch iterative Veränderung und Messung der Auswirkung Abb.1: Ablauf-Phasen beim manuellen Tuning In der Analyse-Phase geht es darum, die Performance-Daten der problematischen Ausführung zu ermitteln.

Dies sind vor allem Elapsed Time, Buffer Gets, Disk Reads und Executions. Die wichtigste Metrik hierbei sind die „Buffer Gets“. Dieser Begriff definiert die logischen I/O Operationen, um Datenblöcke aus dem Arbeitsspeicher zu lesen. Während die Metriken „Elapsed Time“ und „Disk Reads“ abhängig von der Caching-Ratio der Datenblöcke stark variieren können, bleibt die Metrik „Buffer Gets“ unabhängig vom Caching stets konstant. In der Praxis kann festgestellt werden, dass Entwicklungssysteme häufig mit sehr großen Buffer Caches und relativ kleinen Datenmengen betrieben werden. Zudem liegt der Fokus meist nur auf der Antwortzeit der Statements. Damit fällt ein problematischer Zugriffsplan sehr oft erst auf, wenn mit großen Datenmengen im Produktionsumfeld gearbeitet wird. Der DBA wird dann damit konfrontiert, im Produktionsumfeld unter Zeitdruck das Performance-Problem zu analysieren und zu beheben.

Als Richtwert sollte versucht werden, die Anzahl der Buffer Gets pro zurückgelieferter Zeile 10 zu halten.

Dies gilt nicht für Aggregationen (z.B. count, sum, avg, etc.) und Joins. Mit folgender Query wird die

Belastung der Query für die Datenbank ermitelt:

select sql_fulltext, child_number, plan_hash_value, buffer_gets/executions, disk_reads/executions, elapsed_time/executions from v$sqlstats where sql_id = 'sql_id' and executions 0;

Zudem wird der Ausführungsplan inklusive Bind-Variablen zum Zeitpunkt des Parsings ermittelt:

select * from table(dbms_xplan.display_cursor('sql_id', 'child_number', 'TYPICAL +PEEKDED_BINDS'));

Der Oracle Cost-Based Optimizer (CBO) erstellt zum Zeitpunkt des Parsing den Ausführungsplan. Dieser ist

abhängig von einigen Input-Parametern:

 Datenbank-Version, z.B. 11.2.0.1  Initialisierungsparameter, z.B. optimizer_features_enable=11.1.0.7.0  Objekt-Statistiken (Table, Column, Index Statistics gesammelt mit dbms_stats.gather_database|schema|table_stats)  System-Statistiken (gesammelt mit dbms_stats.gather_system_stats)  Datenbank-Schema (Tabellenstruktur, vorhandene Indizes, etc.)  Plan Stablity Informationen (Stored Outlines, ab 10g: SQL Profiles, ab 11g: SQL Plan Baselines)  Cardinality Feedback (ab 11gR2: Rück-Übermittlung der Row-Source-Operation Cardinalities nach Abschluss der Ausführung an den Optimizer)  aktuelles Datum (z.B. wenn Query die Funktion „sysdate“ enthält) Ändert sich einer dieser Input-Parameter, kann dies dazu führen, dass der Optimizer einen neuen Ausführungsplan erstellt. Ein historisch häufiges Problem hierbei ist die „Plan Instability“. D.h., durch Änderung des Ausführungsplans wird das selbe SQL Statement zeitweise performant, zu einem anderen Zeitpunkt unperformant ausgeführt. Zur Lösung dieses Problems gibt es mittlerweile mehrere verschiedene Werkzeuge, z.B. Stored Outlines (Standard Edition), SQL Profiles (Enterprise Edition), SQL Plan Baselines (Enterprise Edition).





Anschließend wird in der Reproduktions-Phase versucht, die problematische Ausführungsperformance zu reproduzieren.

Falls das Statement Bind-Variablen beinhaltet, dürfen diese vorerst nicht durch Literale ersetzt werden. Falls die Bind-Variablen vom Datentyp „DATE“ sind, empfiehlt es sich, das Statement mit SQL Developer statt SQL*Plus zu reproduzieren. Unter Umständen ist das Problem allerdings nicht reproduzierbar. Teilweise ist die Daten-Konstellation, die zum Problem führt, nur kurzzeitig vorhanden. Später, wenn der DBA die Analyse durchführt, ist das Problem aufgrund der anderen Datenkonstellation nicht mehr sichtbar. In diesen Fällen muss auf SQL Tracing mittels Trace Event 10046 oder DBMS_MONITOR ausgewichen werden.

Ist es gelungen, den selben problematischen Zugriffsplan bzw. die problematische Ausführungsdauer zu generieren so kann nun in der Tuning-Phase begonnen werden, die Ausführungsdauer und den RessourcenVerbrauch des Statements durch verschiedenste Maßnahmen zu reduzieren. Im Gegensatz zum Instance Tuning wird nicht versucht, durch Vergrößerung von Caches und Reduzierung von Disk Reads die Ausführungszeit zu verringern. Der Fokus liegt hier bei der Reduzierung der Logical I/Os (Buffer Gets) der SQL Query.

Die einzelnen Maßnahmen sind mannigfaltig und abhängig vom Statement. Beispiele hierfür sind: Erstellung eines Index, Aktualisierung der Optimizer-Statistiken, Optimizer Hints, Aktivierung eines bestimmten Optimizer-Version, Deaktivierung von bestimmten Optimizer-Features, Ändern der Datenverteilung durch Reorganisation und Sortierung, Rewrite des SQL Statements, etc.

Beispiel Das folgende Beispiel verwendet die beiden Tabellen CUSTOMERS und ORDERS.

–  –  –

Datenverteilung:

 Customers: 100.000 rows,  Orders: 1.000.000 rows, o CUSTOMER_ID: 70% von einem einzelnen Customer, 20% von einem einzelnen anderen Customer, die restlichen 10% von 64.234 verschiedenen Customer, o ORDER_STATUS: 2 verschiedene Order-Stati, ungleichmäßig verteilt: 900.000 COMPLETED, 100.000 PENDING

–  –  –

Step 1: Analyse:

Bei der Analyse kann festgestellt werden, dass das Statement 0,353 Sekunden pro Ausführung dauert und 22300 Buffer Gets dafür aufgewendet werden müssen. Der Ausführungsplan zeigt einen Nested Loop Join und als Access-Paths werden Index Lookups verwendet. Das Statement enthält eine Bind-Variable, die auf eine bestimmte CUSTOMER_ID filtert. Damit ist klar, dass die Query nur eine einzelne Zeile aus der Tabelle CUSTOMERS treffen wird, da die Spalte CUSTOMER_ID als Primary Key definiert wurde. Für diesen Kunden werden dann alle offenen Bestellungen abgefragt und nach dem Bestelldatum (ORDER_DATE) sortiert. Beim Ausführungsplan ist zu sehen, welche Bind-Variable beim Parsing vorhanden war. (hier: 1001).

Exkurs: Binds vs. Literals

Noch ein Gedanke zur Query: Immer wieder gibt es Konflikte zwischen DBAs und Entwicklern über die Verwendung von Bind-Variablen. Der Zweck von Bind-Variablen besteht darin, den Parsing-Overhead zu reduzieren und möglichst viele Statements im Shared Pool cachen und wiederverwenden zu können. Die Query enthält eine Bind Variable für die CUSTOMER_ID und ein Literal-String für den ORDER_STATUS.

Wenn statt der Bind-Variablen für die CUSTOMER_ID ein Literal verwendet worden wäre, dann hätte das die Konsequenz, dass für jede verschiedene CUSTOMER_ID (potentiell 100.000) ein einzelnes unabhängiges SQL Statement verwendet wird. Der Effekt des Cachings von SQL Statements und dessen Metadaten wäre damit nicht möglich. Bei der Spalte „ORDER_STATUS“ gibt es nur zwei verschiedene Werte (NDV) die zudem ungleich verteilt sind: COMPLETED und PENDING. Deshalb ist dieses Prädikat ein idealer Kandidat für ein Literal. Im schlimmsten Fall gibt es dadurch zwei verschiedene SQL Statements, die bis auf den Literal-Wert identisch sind. Hingegen gibt es bei Verwendung des Literals die Möglichkeit, ein Histogramm auf die Spalte zu berechnen und damit dem Optimizer die genaue Verteilung der Rows auf die beiden Werte mitzuteilen.

Mit dem folgenden Statement wird nun die Belastung der Datenbank durch die Query ermittelt:

SQL select child_number as child, plan_hash_value as phv, buffer_gets/executions as gets_per_exe, disk_reads/executions as disk_per_exe, elapsed_time/executions ela_per_exe, executions as exe from v$sql where sql_id = 'g7c03r8pn1ymq';

–  –  –

SQL select * from table(dbms_xplan.display_cursor('g7c03r8pn1ymq','0','TYPICAL +PEEKED_BINDS'));

Plan hash value: 3311696933

---------------------------------------------------------------------------------------------Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

–  –  –

Peeked Binds (identified by position):

-------------------------------------V1 (NUMBER): 1001

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

--------------------------------------------------access("C"."CUSTOMER_ID"=:V1) 5 - filter("O"."ORDER_STATUS"='PENDING') 6 - access("O"."CUSTOMER_ID"=:V1) Die Spalte „Rows“ definiert die „Cardinality“, d.h. die geschätzte Anzahl der Zeilen die mit dieser Operation zurückgeliefert wird. Bitte beachten Sie, dass alle Informationen des gezeigten Ausführungsplans lediglich Schätzungen des Optimizers zum Zeitpunkt des Parsings sind! Es ist hier nicht ersichtlich, an welcher Zeile (Row Source Operation) des Ausführungsplans der Großteil der Buffer Gets aufgewendet wird. Für die weitere Analyse ist deshalb diese Darstellung des Ausführungsplans unzureichend.

Step 2: Reproduktion

Bei der Reproduktion wird nun der Optimizer Hint „GATHER_PLAN_STATISTICS“ eingefügt. Direkt nach dem eigentlichen SQL Statement wird dann der Ausführungsplan mittels DBMS_XPLAN abgefragt, wobei der Format-Parameter auf „ALLSTATS LAST“ gesetzt wird. Um den eigentlichen Query-Output auszublenden, kann „termout off“ verwendet werden. Beachten Sie ebenfalls die Definition der Bind Variable in SQL*Plus Syntax. Beim Datentyp ist darauf zu achten, dass der selbe Datentyp verwendet wird, wie vorher im Ausführungsplan angezeigt wurde.

set termout off variable v1 number;

exec :v1 := 1001;

select /*+ GATHER_PLAN_STATISTICS */ * from DEMO.CUSTOMERS C,

DEMO.ORDERS O

WHERE O.CUSTOMER_ID = C.CUSTOMER_ID -- join predicate AND C.customer_id = :v1 -- filter predicate and O.order_status = 'PENDING' --filter predicate order by order_date -- sorting ;

set termout on set lines 300 set pages 1000 select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 3311696933

–  –  –



Pages:   || 2 |


Similar works:

«Deutscher Bundestag Drucksache 18/5373 18. Wahlperiode 01.07.2015 Gesetzentwurf der Abgeordneten Michael Brand, Kerstin Griese, Kathrin Vogler, Dr. Harald Terpe, Michael Frieser, Dr. Eva Högl, Halina Wawzyniak, Elisabeth Scharfenberg, Dr. Claudia Lücking-Michel, Ansgar Heveling, Artur Auernhammer, Heike Baehrens, Dorothee Bär, Norbert Barthle, Dr. Matthias Bartke, Bärbel Bas, Volker Beck (Köln), Maik Beermann, Sybille Benning, Ute Bertram, Steffen Bilger, Burkhard Blienert, Dr. Maria...»

«Lizzie Doron Es war einmal eine Familie Roman Suhrkamp ebook suhrkamp Tel Aviv, Anfang der neunziger Jahre: Helena, Elisabeths Mutter, ist gestorben. Während der Schiwa, der sieben Trauertage, ist Elisabeth wieder in dem kleinen Viertel, in dem sie in den fünfziger und sechziger Jahren aufgewachsen ist, ein Viertel, in dem Überlebende der Shoah damals versuchten, sich ein neues Leben aufzubauen. Während Elisabeth in der Wohnung ihrer Mutter Schiwe sitzt und alte Bekannte bewirtet, werden...»

«See discussions, stats, and author profiles for this publication at: https://www.researchgate.net/publication/237461006 REMARKS ON THE FLUCTUATIONS OF BLUEFIN TUNA CATCHES IN TURKISH WATERS Article · January 2009 CITATIONS READS 2 authors: Firdes Saadet Karakulak Isik KEMAL Oray Istanbul University Istanbul University 70 PUBLICATIONS 576 CITATIONS 40 PUBLICATIONS 394 CITATIONS SEE PROFILE SEE PROFILE Available from: Firdes Saadet Karakulak Retrieved on: 19 May 2016 SCRS/2008/070...»

«In: Hannes Kniffka (ed.), Indigenous grammar across cultures. Frankfurt: Lang, 2001, 543-57. Language terms and categories The development of linguistic tradition in Irish 1 Introduction The question of linguistic terminology in Irish is one which might not at first sight give rise to undue interest. The basic terminology for morphology established definitively for the western world by Priscian at around 500 AD was largely adopted by Irish writers. Indeed many references to the debt which...»

«189 Excavations at the Geometrie Kebaran Site of Neve David, Israel A preliminary report by Daniel Kaufman, Harfa The Sire and Excavation The site of Neve David is situated at the foot of the western slope of Mt. Carmel (Fig. 1) on the north bank of Nahal Siah at its outlet to the coastal plain, 60 m above sea level and 1 000 m from the present shoreline (32 o 49 'NI 34 o 47 'E). This is an ecotonal setting allowing easy access to two primary environmental zones: the slopes of Mt. Carmel and...»

«DIPLOMARBEIT Titel der Diplomarbeit „Das Amerikabild Fredrika Bremers und ihre Vermittlungsarbeit in Schweden“ Verfasserin Michaela Pichler angestrebter akademischer Grad Magistra (Mag.) Wien, 2012 Studienkennzahl lt. Studienblatt: A057/393 Studienrichtung lt. Studienblatt: Individuelles Diplomstudium: Literaturund Verlagskunde Betreuer: Dr. Ernst Grabovszki Böckerna ha blivit mitt käraste sällskap och betraktelsen en vän, som följer mig livet igenom och låter mig suga honung ur...»

«G. Gönnert, B. Pflüger & J.-A. Bremer Von der Geoarchäologie über die Küstendynamik zum Küstenzonenmanagement Coastline Reports 9 (2007), ISSN 0928-2734, ISBN 978-3-9811839-1-7 S. 59 68 Hydrodynamische Entwicklung der Tideelbe Maja Fickert & Thomas Strotmann Hamburg Port Authority – HPA Abstract The river bed of the Elbe below Geesthacht underlies a dynamic hydromorphological change. Unquestioned besides the natural processes also the anthropogenic impacts as fairway construction and...»

«Religion fällt nicht vom Himmel1 Die ersten Jahrhunderte des Islam Vorbemerkung: Der Islam ist bei uns angekommen. Mit ihm müssen wir uns auseinandersetzen. Der Vorsitzenden des Zentralrats der Muslime Elyas (2000) sagt: „Der Islam regelt als ganzheitliche Lehre alle Bereiche des Lebens, setzt den ethischen Rahmen für die zwischenmenschlichen Beziehungen und liefert die Grundsätze, nach denen sich politisches Handeln und ein Staatsaufbau orientieren sollte.“ Das ist Islamismus, die...»

«MASTERARBEIT Titel der Masterarbeit Das Sprachregister in gedolmetschten Gerichtsverhandlungen       Verfasserin Caroline Wagner (BA)      angestrebter akademischer Grad Master of Arts (MA) Wien, im August 2011       Studienkennzahl lt. Studienblatt: A 065 342 375     Studienrichtung lt. Studienblatt: Masterstudium Dolmetschen Englisch Polnisch Betreuerin: Ao. Univ.-Prof. Dr. Mira Kadric-Scheiber      Danksagung An dieser Stelle möchte ich mich...»

«IV. Évfolyam 3. szám 2009. szeptember Négyesi Imre negyesi.imre@zmne.hu TRAGBARE UND FELDINFORMATIK-GERÄTE II. Absztrakt/Abstract Das Feld ein und beträchtliche Verbesserungen geschah in der Entwicklung der tragbaren Informatik-Systeme, und sie geschehen. Die zwei Basisprobleme im Fall von den größeren Feldgeräten im Fall vom transportfähigen, den tragbaren Geräten obwohl, die die Aufgaben in der Hand mit einer Waffe in den verschiedenen battlefront Situationen rechtskräftig sein...»





 
<<  HOME   |    CONTACTS
2016 www.book.dislib.info - Free e-library - Books, dissertations, abstract

Materials of this site are available for review, all rights belong to their respective owners.
If you do not agree with the fact that your material is placed on this site, please, email us, we will within 1-2 business days delete him.