SQL Monitoring in 12c (Präsentation von Tanel Poder)

In seiner OOW Präsentation SQL Monitoring in Oracle Database 12c liefert Tanel Poder eine schöne Übersicht zum SQL Monitoring in 12c, was in Anbetracht der Überschrift womöglich nicht allzu sehr überrascht. Ich spare mir eine komplette Nacherzählung (die bei Folien ohnehin einen gewissen Anteil an Interpretation enthalten würde), sondern beschränke mich auf die Auflistung von Punkten, die mir erinnerungswürdig erscheinen - was vielleicht nur eine freundlichere Umschreibung dafür ist, dass ich sie vergessen hatte:

• das Monitoring erfolgt in 12c:
• wenn der MONITOR Hint verwendet wird
• bei parallel queries
• nach 5 Sekunden CPU/IO für serielle Queries (gesteuert über _sqlmon_threshold)
• es unterscheidet zwischen "duration" (= wall-clock time from the execution start) und "db time" (= amount of database time your session (and its PX slaves, if any) spent executing this SQL).
• die "Other" Spalte im HTML-Report enthält in 12c wichtige Zusatzinformationen (HASH JOIN row source, JOIN FILTER CREATE row source, Exadata Details).
• Bindewerte sind im Report erfasst (ebenso wie in der zugrunde liegenden Spalte v§sql_monitor.bind_xml.
• für häufig ausgeführte OLTP queries ist die Verwendung des MONITOR-Hints keine gute Idee, da die runtime metrics für jede Ausführung in einer eigenen Struktur in der SGA gespeichert werden müssen.
• natürlich hat der Herr Poder ein Skript zur Repräsentation der Informationen in sqlplus - nämlich asqlmon.sql.
• in 12c gibt es AWR-Tabellen zur Persistierung dieser Daten (DBA_HIST_REPORTS, DBA_HIST_REPORTS_DETAILS).
• große Pläne werden per default nicht erfasst (_sqlmon_max_planlines=300). 

Keine besonders aufsehenerregenden Änderungen, aber doch ein paar nette Ergänzungen - insbesondere die AWR-Integration finde ich nützlich.

Oracle Tools: pathfinder, SQLd360, eDB360

Ein paar Links von Mauro Pagano und Carlos Sierra:

• Mauro Pagano: Using SQLd360 – introduction video
• Carlos Sierra: Using eDB360 – introduction video
• Mauro Pagano: Introducing Pathfinder, is there a better plan for my SQL?

Zu den ersten beiden Links ist nicht viel zu sagen, das sind kurze Einführungs-Videos zu den jeweiligen Tools. Der dritte Artikel stellt das Pathfinder Tool vor, das eine recht brachiale Form der Suche nach besseren Ausführungsplänen verwendet: es führt eine gegebene Query mit jedem in der Datenbank möglichen CBO Parameter- und fix_control-Setting durch. Als Ergbnis wird ein html file generiert, das die Resourcen-Nutzung der unterschiedlichen Ausführungen angibt. Interessant ist die Nutzung des Tools sicher nur für Queries mit einer niedrigen Gesamtlaufzeit. Was auch immer man sonst zur Vorgehensweise sagen will: das Verfahren dürfte ziemlich lückenlos ausprobieren, was über eine solche Parametersetzung ausprobiert werden kann.

Lesekonsistenzprobleme mit PL/SQL und Result Cache

Jonathan Lewis hat sich in den letzten Wochen mit einigen Fällen beschäftigt, bei denen die Verwendung des Result Caches oder von mehreren SQL Queries innerhalb einer PL/SQL-Operation Probleme mit der Lesekonsistenz hervorrufen kann:

• Result Cache: liefert das Fazit "Do not mix the pl/sql result cache with database queries. The cache is public but (unlike the buffer cache) it is not guaranteed to give you read-consistency." Im zugehörigen Beispiel wird eine Temporary Table verwendet, für die der Result Cache laut Dokumentation nicht vorgesehen ist, aber die Probleme scheinen nicht auf diesen Fall begrenzt zu sein. Offenbar gibt es in diesem Zusammenhang eine ganze Reihe von Problemen und Bugs - einige entsprechende Fälle hat Sayan Malakshinov in seinem Kommentar verknüpft.
• Result Cache 2: behandelt ein Konsistenzproblem, das sich ergeben kann, wenn man PL/SQL-Funktionen in skalaren Subqueries verwendet. Das vorgestellte Beispiel ist ziemlich komplex und betrifft eine Reporting-Fragestellung und zu den möglichen Workarounds gehören die explizite Setzung einer Tabelle oder eines Tablespaces auf read-only, das Einfrieren der SCN für die Transaktion (read-only) und die Verwendung von deterministischen Funktionen. Ich spare mir die detailliertere Wiedergabe der Ausführungen, aber das Fazit lautet: "The moment you create a PL/SQL function that uses the result cache or deterministic option you have to ensure that nobody uses that function without ensuring that their code has handled the consistency threat properly. It’s too easy to forget, with the passing of time, that certain procedures have to be adopted when particular coding strategies are used."
• Read Consistency: befasst sich mit dem grundsätzlicheren Problem "Any time you execute more than one SQL statement in a PL/SQL procedure the results of executing that procedure may not be self-consistent unless you have explicitly locked your session SCN to a fixed value!!!!!", dass Jonathan Lewis mehrere Ausrufezeichen wert ist - und die sieht man bei ihm sonst sehr selten!! Oracles Standard-Konsistenz-Versprechen ist auf Statement-Ebene beschränkt: wer mehr Konsistenz benötigt - sei es innerhalb einer PL/SQL-Prozedur oder innerhalb eines Berichts auf Basis mehrerer SQL-Queries - muss das passende Isolation-Level wählen. Dazu gibt es ein Code-Beispiel, aber der Fall ist eigentlich auch ohne dieses ziemlich eindeutig.

Ich erhebe keinen Anspruch darauf, die Artikel halbwegs plausibel zusammengefasst zu haben - wie man sieht, habe ich mir sogar das Übersetzen der Kernpunkte weitgehend gespart: mir ging es nur darum die Links zu erfassen, um sie gelegentlich wiederfinden zu können.

Spalten als Zeilen ausgeben mit SQLcl

Ein Feature, das ich in psql für postgres sehr schätze, ist die Möglichkeit, durch das Kommando \x die "erweiterte Anzeige" zu aktivieren, die Datensätze in einer Record-Darstellung präsentiert, also:

select * from t;

a | b | c
---+---+---
1 | 2 | 3
4 | 5 | 6

--> Switch display with \x

select * from t;

-[ RECORD 1 ]
a | 1
b | 2
c | 3
-[ RECORD 2 ]
a | 4
b | 5
c | 6

Tom Kyte hat für diesen Zweck vor vielen Jahren die Funktion print_table bereitgestellt, die als Parameter den Query-Text erhält - und in der Vergangenheit habe ich damit oft gearbeitet. Allerdings hätte ich lieber eine Lösung, die ohne die Anlage eines Objekts in der Datenbank auskommt, und daher habe ich vor einiger Zeit die Idee A simple output pivot option for sqlplus (or SQLcl) bei OTN untergebracht - vor allem in der Hoffnung, dass da gelegentlich jemand vom SQL Developer Team hinein schauen könnte. Das ist jetzt geschehen und Kris Rice hat eine Lösung mit einem JS File für SQLcl ergänzt. Das ist zwar noch nicht die builtin-Funktion, die ich gerne hätte, aber schon eine richtig gute Lösung. Danke dafür.

Oracle-Package für HTTP/HTTPS

Sayan Malakshinov hat zuletzt zwei Artikel veröffentlicht, in denen er sein GitHub Package XT_HTTP vorstellt, mit dessen Hilfe man auf HTTP- bzw. HTTPS-Seiten zugreifen kann, ohne Zertifikate importieren zu müssen. Die aktuelle Version enthält einen ergänzenden Timeout-Parameter, eine Suchoption auf Basis von regulären Ausdrücken (PCRE) und den Support für plsqldoc (ein Hilfsmittel zur automatischen Generierung von Dokumentation; ähnlich wie javadoc). Sicher sehr nützlich für entsprechende Fragestellungen.

Der Artikel hat noch den zusätzlichen Pluspunkt, dass er im Beispiel belegt, dass ich meiner moralischen Verpflichtung zur Teilnahme am Oracle Database Developer Choice Award nachgekommen bin und tatsächlich auch zu den Up-Votern gehörte. Den Hintergrund zu dieser Bemerkung kann man bei Tim Hall finden - bzw. bei den bei ihm verlinkten Artikeln; und grundsätzlich geht es darum, dass die Teilnahme an der Abstimmung eine sehr kostengünstige Möglichkeit ist, sich bei den nominierten Mitgliedern der Oracle-Community zu bedanken. Wenn ich daran denke, wie viele gute Ideen ich mir im Laufe der Zeit bei Adrian Billington, Stew Ashton, Matthias Rogel und eben auch dem Herrn Malakshinov ausgeborgt habe (um nur einige zu nennen), dann ist ein Dank da durchaus nicht unangemessen...

Zusammenspiel von log writer und foreground Prozessen beim Commit

Wenn Frits Hoogland, dessen Beiträge in der Regel ziemlich komplexe Analysen beinhalten, seinen Artikel How the log writer and foreground processes work together on commit mit den Worten beginnen lässt "warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer", dann sollte ich so einen Hinweis vielleicht lieber ernst nehmen. Nach der wiederholten Lektüre des Textes bin ich zum Ergebnis gekommen, dass ich in diesem Fall nicht dazu in der Lage bin, ein halbwegs plausibles Exzerpt zu schreiben, weil der Artikel dafür zu viele technische Details beinhaltet, die ich nicht aus dem Kontext reißen mag und auch nicht ausreichend klar erläutern könnte, ohne den Artikel komplett zu übersetzen - und dazu bin ich, Gott sei Dank, nicht verpflichtet. Sollte ich aber jemals in eine Situation kommen, in der das Wissen um die exakten Zusammenhänge bei der Ermittlung der SCN im Kontext von private redo strands eine Rolle spielt und die Auswirkungen der Verwendung der Funktion kcscur3() relevant sind, dann wüsste ich, wo ich nachzuschauen hätte. Und das gleiche gilt für den Fall, dass ich mir ausgefeilte Analysestrategien für die Untersuchung interner Oracle-Operationen ausdenken müsste. Der Artikel kann als vertiefende Ergänzung zu Jonathan Lewis' Buch Oracle Core betrachtet werden - und viel tiefer geht es nicht...

Estimate_Percent und Histogramme

Dass die Sample-Größe bei der Statistikerfassung via dbms_stats ein schwieriges Thema ist, habe ich wohl schon gelegentlich erwähnt - bzw. die Artikel anderer Autoren nacherzählt, die darüber geschrieben haben. Und ein besonders heikler Teilbereich dieses Themas sind die Histogramme. Und wahrscheinlich gehe ich auf den neuen Scratchpad-Artikel von Jonathan Lewis hier vor allem deshalb noch einmal intensiver ein, weil der Herr Lewis im zugehörigen OTN-Thread meine Einschätzung des gegebenen Falls bestätigt hat - und lobende Erwähnungen von Jonathan Lewis heben meine Stimmung ganz beträchtlich.

Im Artikel geht es um Folgendes: für eine relativ große Tabelle werden täglich neue Statistiken auf Basis einer Sample-Größe von einem Prozent erstellt. In der Folge ergeben sich für eine auf die Tabelle zugreifende einfache Query zwei Pläne: ein effektiver Plan mit Full Table Scan und ein ineffektiver Plan mit Index Range Scan, bei dem die Cardinality der einschränkenden Bedingungen offenbar massiv unterschätzt wird. Im Fall des Index Range Scans erfolgt dabei ein Access über eine Id-Spalte (mit order_id = 0, was ja oft ein häufig erscheinender Sonderfall ist) und eine anschließende Filterung über zwei weitere Spalten (bucket_type = 'P' und sec_id > 0). Auffällig ist dabei noch, dass der Filter Step nur noch eine geringe Reduzierung der Cardinality mit sich bringt, was darauf hin deutet, dass zumindest für bucket_type ein Histogram existiert, dass dem Optimizer mitteilt, dass diese Bedingung nicht selektiv ist. Um das Verhalten zu überprüfen, wird ein Test erstellt, der ein Datenmuster erzeugt, dass zu solchen Effekten führen könnte. Entscheidend ist dabei, dass für die order_id eine massive Ungleichverteilung definiert wird: 5% der Werte enthalten den Wert 0 und sind am Ende der Tabelle geclustert. Anschließend werden die Statistiken einmal mit estimate_percent => 1 und einmal mit mit der default auto_sample_size erzeugt. Wie im OTN-Fall ergeben sich zwei Pläne: bei Verwendung des expliziten Prozentwertes ergibt sich ein ein Index Range Scan und bei Verwendung von auto_sample_size ein Full Table Scan. Ursache ist, dass der Optimizer im Fall des 1% samples nicht erkannte, dass hier ein Histogramm nützlich sein könnte und daher eine Gleichverteilung annahm - und damit die Cardinality für den prädominaten Fall natürlich unterschätzt; und das, obwohl das 1% sample für die Histogrammerstellung eine größere Anzahl von Datensätzen überprüft als die auto_sample_size - nämlich 10000 gegenüber 5500 (diesen unerfreulich niedrigen und nicht anpassbaren Standardwert habe ich hier schon häufiger erwähnt). Warum die beiden Verfahren zu unterschiedlichen Einschätzung kommen, kann auch Jonathan Lewis auf Anhieb nicht erklären: zwar gibt es kleinere Unterschiede zwischen den sql traces der beiden Strategien, aber die erklären das unterschiedliche Verhalten hinsichtlich der Histogramme nicht - und auch die Dokumentation schweigt dazu. Aber entscheidend ist an dieser Stelle zunächst, dass das 1% sample die Gefahr mit sich bringt, data skew für stark geclusterte Werte zu übersehen, was die Instabilität der Pläne in der OTN-Frage erklärt.