Montag, Oktober 02, 2017

Mehrere SQL_IDs zu einem Query-Text

Jonathan Lewis erläutert in seinem Blog ein - zumindest für mich überraschendes (sprich: bisher unbekanntes oder wieder vergessenes) Phänomen: zum gleichen SQL Text kann es mehrere unterschiedliche SQL_IDs geben. Eine relativ bekannte Ursache dafür ist offenbar, dass die Länge der in einer Query verwendeten Bindevariablen einen Einfluss auf die Erzeugung der SQL_IDs hat, was vermutlich ein Effekt des internen library Mechanismus ist und keine Design-Entscheidung.

Darüber hinaus erwähnt der Autor die Möglichkeit, ein Statement über die Prozedur dbms_shared_pool.markhot() - nun ja: als "hot" zu markieren, was dann sinnvoll ist, wenn sehr viele Sessions permanent die gleiche Query ausführen, so dass sehr viele Zugriffe auf den gleichen child cursor erfolgen. In diesem Fall wird eine neues Attribut in v$db_objct_cache ergänzt, dass als zusätzlicher Textbestandteil hinzukommt und damit einen neuen Hash-Wert und eine neue SQL_ID hervorruft. Dazu gibt es auch eine entsprechende unmark-Routine, mit der man die Kopien wieder reduzieren kann.

Mittwoch, September 27, 2017

Performance Feedback Optionen in 12c aktivieren und deaktivieren

Christian Antognini erläutert in seinem Blog, welche Schwierigkeiten sich in 12c ergeben, wenn man das "Performance Feedback" aktivieren oder deaktivieren möchte. Zur Erinnerung: das Performance Feedback ist ein adaptives Feature und dient zur automatischen Prüfung, ob ein initial gewählter Parallelisierungsgrad tatsächlich vorteilhaft ist. Relativ harmlos ist bei der Konfoiguration noch der Faktor, dass sich der zugehörige Parameter in 12.1 OPTIMIZER_ADAPTIVE_FEATURES nennt, während er in 12.2 (bzw. auch in 12.1, wenn man den passenden Patch installiert hat) auf den Namen OPTIMIZER_ADAPTIVE_STATISTICS hört. Daneben muss in beiden Versionen zusätzlich noch der Parameter PARALLEL_DEGREE_POLICY auf true gesetzt werden, um das Performance Feedback zu aktivieren. Weniger schön ist, dass die Reihenfolge der Setzung der Parameter eine Rolle spielt - und darüber hinaus der interne Parameter _OPTIMIZER_PERFORMANCE_FEEDBACK. Die Nacherzählung der Effekte der Reihenfolge spare ich mir an dieser Stelle.

Mittwoch, September 13, 2017

Änderungen an v$diag_alert_ext in 12.2

Vor kurzem hat Markus Flechtner in seinem Blog auf einige Änderungen hingewiesen, die sich in 12.2 für die View v$diag_alert_ext ergeben haben, mit der man die Inhalte aus alert.log und weiteren Log-Dateien via SQL abfragen kann. Was sich leider anscheinend nicht geändert hat, ist, dass dieser Zugriff weiterhin deutlich langsamer erfolgt als der über die X$-View X$DBGALERTEXT. Dafür ist v$diag_alert_ext aber in 12.2 offiziell dokumentiert, was ihre Verwendung an Stelle des X$-Objekts interessanter macht. Wichtiger ist wahrscheinlich noch, dass v$diag_alert_ext inzwischen sinnvolle Werte in der Spalte con_id enthält und dass die View in einer PDB jeweils nur die Einträge des entsprechenden Containers anzeigt. Und für mich ist noch interessanter, dass die ergänzende View V$DIAG_TRACE_FILE einen SQL-Zugriff auf Trace-Dateien ermöglicht. In Summe sieht es danach aus, dass diese Teile der Infrastruktur inzwischen eine recht hohe Stabilität gewonnen haben - in 11.2.0.3 konnte der Zugriff auf die View noch zu recht harten ORA-07445-Fehlern führen und war daher nicht unbedingt ratsam.

Donnerstag, September 07, 2017

impdp mit metadata_only content führt zu gelockten Statistiken

Der Titel sagt eigentlich schon alles, was ich hier ausführen wollte: Frank Pachot weist darauf hin, dass ein auf Metadaten beschränkter Import automatisch die Statistiken lockt, um dafür zu sorgen, dass sie nicht vom nächsten Statistikerfassungslauf mit relativ uninteressanten 0-rows-Einträgen überschrieben werden. Auf diese Weise kann man mit einem metadata_only Import recht gut die Ausführungspläne des CBO vom Quellsystem nachvollziehen. Was den Herrn Pachot dabei ebenso irritiert wie mich ist, dass dieses Verhalten seit 10.2 gilt - und es ihm erst jetzt aufgefallen ist; und mir dann noch etwas später...

Mittwoch, August 09, 2017

Optimizerstrategien in Postgres und Oracle

Vor einigen Wochen hat Franck Pachot via Twitter die erfreuliche Ankündigung gemacht, eine Artikelserie mit einem Vergleich der Optimizerstrategien in Postgres und Oracle beginnen zu wollen. Noch erfreulicher ist, dass es nicht bei der Ankündigung geblieben ist, sondern dass die Artikelserie zeitnah begonnen hat. Vor ein paar Jahren hatte ich einen Artikel geschrieben, der ein paar einführende Punkte zum gleichen Thema erfassen sollte, aber dabei war der Idealtyp Oracle nur in meinem Kopf als Vergleichsmaßstab im Spiel gewesen - insofern ist das Vorgehen von Monsieur Pachot das plausiblere:
  • Postgres vs. Oracle access paths – intro: erklärt die Zielsetzung der Serie und die grundsätzliche Vorgehensweise. Insbesondere weist der Autor darauf hin, dass es ihm nicht um eine Bewertung, sondern um den Vergleich der ihm bekannten Strategien des Oracle Optimizers mit denen bei Postgres. Die verwendeten Versionen sind Oracle 12.2 und Postgres 9.6.2 und die jeweiligen Ausführungspläne werden mit dbms_xplan bzw. explain analyze erzeugt.
  • Postgres vs. Oracle access paths I – Seq Scan: hier werden zunächst zwei Tabellen identischer Struktur in beiden Systemen angelegt, die jeweils einen unique index auf einer id-Spalte erhalten. Anschließend erfolgt die Berechnung der Kosten für den seq scan bei Postgres bzw. den Full Table Scan in Oracles Nomenklatur. Für Postgres basiert die Berechnung auf der Anzahl der pages in der Tabelle, der Anzahl der Ergebniszeilen des Scans, der Anzahl der ausgegebenen Zeilen und den Parametern seq_page_cost, cpu_tuple_cost und cpu_operator_cost. Auch für Oracle wird das costing vorgestellt, aber hauptsächlich auf die einschlägigen Ausführung der Herren Lewis und Antognini verwiesen. Erklärt werden auch die Buffer-Informationen in der analyze explain Ausgabe: "shared hit" bedeutet, dass die Buffer aus dem Cache gelesen wurden, "shared read" weist auf einen physikalischen Lesevorgang auf der Platte hin. Mit Hilfe von strace wird dann genauer untersucht, was auf OS-Seite abläuft: über lseek() werden die Start und Endpositionen des Lesevorgangs bestimmt und die zugehörigen pages dann über read() sequentiell gelesen.
  • Postgres vs. Oracle access paths II – IndexOnlyScan: erläutert einen entscheidenden Unterschied zwischen den MVCC Implementierungen von Oracle und Postgres: bei Postgres werden nur die Tabellendaten versioniert, bei Oracle zusätzlich auch die Inhalte der Indizes. Daher musste für die sum(n)-Query des ersten Artikels bei Postgres ein seq scan erfolgen, während Oracle ohne Verwendung eines FULL-Hints einen Index-Zugriff gewählt hätte. Nur nach einem vorherigen vacuum und der Setzung eines internen visibility flags kann für deine entsprechende Query ein index-only scan erfolgen. In diesem Fall ergibt sich die Kostenrechnung bei Postgres aus den Faktoren ("Anzahl Index-Blocks" * random_page_cost) +  ("Anzahl Index-Einträge" * cpu_index_tuple_cost) + ("Anzahl Ergebniszeilen" * cpu_tuple_cost).
  • Postgres vs. Oracle access paths III – Partial Index: erläutert das Verhalten von Index-Only Scans hinsichtlich der Existenz von NULL-Werten: bei Oracle muss eine Spalte einen NOT NULL Constraint erhalten (oder ein NOT NULL in der zugehörigen Abfrage ergänzt werden), damit der Index-Only Scan möglich wird, weil Indizes bei Oracle NULL-Werte ausklammern (jedenfalls für einen single column index). Alternativ kann man den Index in Oracle auch um eine zweite Spalte erweitern, die eine Konstante enthält, was den Index allerdings vergrößert. Mit Hilfe von Function Based Indexes (FBI) kann man in Oracle die Indizierung auf bestimmte Werte einschränken, muss die entsprechende Bedingung aber dann auch so in der Query verwenden. Die Implementierung von Partial Indexes in Postgres ist flexibler: hier kann man im create index Kommando einfach eine where-Bedingung einfügen. Für die Verwendung des entsprechenden Index ist keine Anpassung der Query erforderlich.
  • Postgres vs. Oracle access paths IV – Order By and Index: erläutert die Unterschiede von INDEX FULL SCAN und INDEX FAST FULL SCAN in Oracle und die sich daran anschließenden Effekte hinsichtlich der Sortierung (Variante 1 ist sortiert, Variante 2 nicht). Bei Postgres entspricht der Index Only Scan dem Index Full Scan bei Oracle - liefert also ein sortiertes Ergebnis (wobei jeweils veränderte collations eine zusätzliche Sortierung erforderlich machen können - für beide RDBMS ist es möglich, Indizes zu erzeugen, die collations unterstützen, die nicht der default der gegebenen Datenbank sind).
  • Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX: in Postgres liefert der Ausführungsplan zwei cost-Angaben: die startup-Kosten und die Gesamt-Kosten. Um in Oracle eine Kostenangabe für einen Teil der Ergebnismenge zu erhalten, muss man den first_rows_n Parameter verwenden. Erläutert wird auch das Vorgehen bei "fetch first rows" Operationen und beim MIN/MAX Zugriff. Für letzteren gibt es bei Oracle eine spezielle Operation im Plan (INDEX FULL SCAN (MIN/MAX)), während Postgres einen Plan verwendet, der stark dem der "fetch first rows" Operation entspricht.
  • Postgres vs. Oracle access paths VI – Index Scan: untersucht das Verhalten unterschiedlicher Index-Scan-Operationen und insbesondere die zugehörigen costing-Angaben. In beiden RDBMS gibt es die Möglichkeit des index only scan ohne Tabellenzugriff. Im Fall eines Tabellenzugriffs erscheint bei Oracle der zusätzliche Step TABLE ACCESS BY INDEX ROWID, Postgres belässt es beim Index Scan using . Die Einschränkung für den Index-Zugriff erscheint bei Oracle unterhalb des Plans als Access-Prädikat, bei Postgres wird sie als "Index Cond" angegeben. Im Artikel wird mit Hilfe von strace genauer untersucht, wie das Postgres costing für einen Range Scan ausfällt und dabei kommt heraus, dass der Planner im Beispiel den clustering factor berücksichtigt und die Kosten auf Basis von Sequential reads berechnet (für die gut sortierten Daten des Tests). Für Oracle ergeben sich random reads (db file sequential reads = single block reads). In beiden RDBMS sind die Abarbeitung und das Costing letztlich ähnlich, wobei sich allerdings die Einheit unterscheidet: Postgres rechnet mit sequential reads, Oracle mit random reads. Allerdings gilt das nur im gegebenen Test-Szenario mit einer hinsichtlich des Index gut geclusterten Tabelle.
  • Postgres vs. Oracle access paths VII – Bitmap Index Scan: ...
  • Postgres vs. Oracle access paths VIII – Index Scan and Filter: ...
  • Postgres vs. Oracle access paths IX – Tid Scan: ...
  • Postgres vs. Oracle access paths X – Update: ...
  • Postgres vs. Oracle access paths XI – Sample Scan
Ich gehe davon aus, dass die Serie noch weitere Artikel umfassen wird, die ich dann voraussichtlich auch noch hier nacherzählen werde.