Freitag, Mai 27, 2016

CBO Transformationen für count distinct Operationen

Anfang 2014 hatte ich hier einen Artikel angesprochen, der im High-Performance Blog von Persicope erschienen war und versprach, postgres Queries mit einer count distinct Operation um den Faktor 50 zu beschleunigen. Ich hatte damals selbst ein paar Tests mit Oracle durchgeführt, die zeigten, dass die Umformulierung auch dort nützlich ist, aber weitaus weniger dramatische Effekte hervorruft als bei postgres (was kurz darauf in einem weiteren Artikel bei Periscope ebenfalls angemerkt wurde. Außerdem hatte ich einen Artikel von Hubert Lubacewski verknüpft, in dem dieser zeigte, dass man durch geschicktere Umformulierung in postgres noch sehr viel mehr erreichen kann als mit den periscope-Varianten.

Jetzt hat Jonathan Lewis im Scratchpad auf den Artikel verwiesen und dabei gezeigt, dass der CBO für einfachere Beispiele durchaus dazu in der Lage ist, diese Umformung selbständig durchzuführen. Verwendet werden dabei folgende Optimizer-Transformationen:
  • place group by: verfügbar ab 11.1.0.6 (2007)
  • transform distinct aggregation: verfügbar seit 11.2.0.1 (2009)
Wie üblich ist der Optimizer aber nicht in jedem Fall dazu in der Lage, die richtige Strategie auszuwählen, so dass man ihn manchmal durch Hints auf die richtige Spur bringen muss (hier etwa: no_transform_distinct_agg).

Freitag, Mai 20, 2016

Optimizer-Features unterschiedlicher Oracle-Versionen vergleichen

Nigel Bayliss stellt im Blog der Oracle Optimizer Entwicklung ein nützliches Skript vor, mit dessen Hilfe man die Optimizer Features unterschiedlicher Oracle Releases vergleichen kann. Das Skript legt diverse Hilfstabellen an und greift auf v$session_fix_control, sys.x$ksppi und sys.x$ksppcv zu, für die man demnach Lesezugriff benötigt. Kann man natürlich auch von Hand machen, aber ein passendes Skript macht dergleichen komfortabler.

Mittwoch, Mai 11, 2016

Locks zur Sicherstellung referentieller Integrität

Jonathan Lewis wiederholt in seinem Blog zur Zeit allerlei Erläuterungen, die er schon häufiger gegeben hat, und ich wiederhole hier dann noch mal die Punkte, die es bisher trotz Wiederholung noch nicht bis in meinen aktiven Wissensbestand geschafft hatten (sondern nur eine vage Erinnerung aufrufen). Einer dieser Punkte betrifft das Verhalten der Locks zur Gewährleistung referentieller Integrität, die als RI Locks bezeichnet werden. Dabei skizziert der Autor folgendes Szenario:
  • ein Datensatz wird in eine Parent-Tabelle eingefügt, aber nicht über commit festgeschrieben.
  • in einer zweiten Session soll in eine child-Tabelle ein Datensatz eingefügt werden, der sich auf den neuen Parent-Datensatz bezieht.
  • man könnte annehmen, dass die zweite Session unmittelbar einen Fehler erhält, der darauf hinweist, dass kein parent key gefunden wurde, aber das ist nicht der Fall: tatsächlich wartet die zweite Session darauf, dass in der ersten Session ein Commit oder ein Rollback erfolgt.
  • obwohl das isolation level READ COMMITTED dafür sorgt, dass eine Session nur die Daten sehen kann, die in einer anderen Session bereits per commit festgeschrieben wurden, kann der zugehörige interne Prozess durchaus die Arbeit anderer Sessions wahrnehmen. 
  • ein Blick in v$lock zeigt, dass die zweite Session ein TX Lock im Mode 6 hält (also ein exklusives Transaktionslock, was bedeutet, dass die Session undo und redo erzeugt); außerdem wird ein weiteres Transaktionslock in Modus 4 (share) angefordert, und diese Anforderung wird von der ersten Session blockiert.
  • nach einem rollback in Session 1 wird in Session 2 dann der erwartete Hinweis auf den fehlenden parent key geliefert (ORA-02291). Nach einem commit in Session 1 kann Session 2 problemlos weiterarbeiten.
  • im Fall einer multi-statement-Transaktion in Session 2 würde nur das insert in die child-Tabelle zurückgerollt werden, nicht aber alle Statements der Transaktion.
  • das Verhalten ist das gleiche im Fall von update und delete.
  • aus dem Verhalten können sich auch deadlocks ergeben.
  • wenn in einem deadlock-Graphen ein TX wait des Typs S (share, mode 4) erscheint, sind mit hoher Wahrscheinlichkeit Indizes im Spiel (verursacht durch referentielle Integritätsregeln oder auch PK-Werte-Kollisionen oder Werte-Kollisionen in einer IOT).
Ob ich mir das jetzt besser merken kann, bleibt abzuwarten.

Mittwoch, Mai 04, 2016

Wann ist ein Full Table Scan billiger als ein Index Fast Full Scan?

Und seit wann verwende ich in meinen Überschriften Fragezeichen? Fragen über Fragen. Aber eigentlich ist der Fall, den Jonathan Lewis in seinem aktuellen Blog-Artikel beschreibt, ebenso überschaubar wie erinnerungswürdig. Seine Fragen darin lauten: wieso werden die Kosten eines Full Table Scans auf einer Tabelle vom Optimizer als niedriger berechnet als die Kosten für den Index Fast Full Scan auf einem Index der Tabelle und warum ist der Index Fast Full Scan trotzdem effizienter in Hinblick auf die Laufzeit der Ausführung. Auf die Frage nach den Kosten gibt es eine wahrscheinliche und eine eher exotische Möglichkeit:
  • die eher exotische Variante wäre: Index und Tabelle wurden mit unterschiedlichen Blockgrößen angelegt. Da eigentlich niemand (außer vielleicht dem Herrn Burleson) ohne gute Gründe (z.B. den Einsatz von Transportablen Tablespaces) auf die Idee kommt, mehrere Blockgrößen in einer Datenbank zu verwenden, tritt der Fall wohl eher selten auf.
  • die wahrscheinlichere Erklärung ist: die Tabelle ist kleiner als der Index. Da ein Index Fast Full Scan (IFFS) tatsächlich ein Full Table Scan (FTS) des Index-Segments ist, basiert das Costing auf der Anzahl der Blocks im Segment. Wenn also die Tabelle kleiner ist als der Index, dann sind auch die Kosten des FTS niedriger als die des IFFS.
Im dem Artikel zugrunde liegenden OTN-Fall war tatsächlich die (aus meiner Sicht) wahrscheinlichere Variante Nr. 2 im Spiel: die Tabelle war kleiner als der Index. Grundsätzlich kann das leicht passieren, wenn eine Tabelle sehr schmal ist, da der Index ja immer noch zusätzlich zu den indizierten Spalten die rowid des Datensatzes enthält, aber im gegebenen Fall deuteten die Autotrace-Statistiken (consistent gets) nicht darauf hin, dass der Index größer war als die Tabelle. Die Ursache dafür war Compression, die für die Tabelle eingerichtet war, während der Index nicht komprimiert wurde - wobei natürlich auch noch zu berücksichtigen ist, dass die Index Compression technisch anders funktioniert als die Table Compression und unter jeweils anderen Voraussetzungen effektiv ist. Die Verwendung der Table Compression ist dann auch der Grund dafür, dass das Costing des Optimizers in diesem Fall den weniger effizienten Plan favorisiert: die Tabelle ist zwar geringfügig kleiner als der Index, aber das Entpacken der komprimierten Daten erfordert zusätzlichen CPU-Einsatz, so dass die Ausführung mit dem IFFS ein wenig schneller abläuft als die mit dem FTS. Interessant ist im Artikel auch, wie der Herr Lewis das Vorliegen der Table Compression und das Fehlen der Index Compression aus den Zahlen des zugehörigen CBO-Traces ermittelt, wobei das keine höhere Mathematik ist.

Samstag, April 30, 2016

Materialized View Fast Refresh und Data Clustering

Zur Abwechslung kann ich hier mal wieder eine selbst erlebte Geschichte erzählen: am letzten Wochenende habe ich ältere historischen Daten aus den Aggregationstabellen eines Data Warehouses (11.2) entfernt, was den Neuaufbau diverser Materialized Views erforderlich machte. Diese Materialized Views sind auf verschiedenen Ebenen der Zeitachse definiert: für Wochen, Monate und Jahre, wobei die beiden ersten Gruppen range partitioniert sind, während für die Jahres-Ebene keine Partitionierung eingerichtet wurde: vermutlich, weil in der Regel nur wenige Jahre in den Aggregationen vorgehalten werden sollen. Meine Annahme war, dass sich die Löschungen moderat positiv auf die Performance folgender Fast Refresh Operationen auswirken sollten, da das Datenvolumen insgesamt merklich reduziert wurde. Tatsächlich liefen die Fast Refresh Operationen aber nach dem Neuaufbau deutlich langsamer als zuvor - obwohl die Ausführungspläne strukturell unverändert blieben. Dazu zunächst der Ausführungsplan und ein paar Zahlen:

Plan hash value: 956726641
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |                            |       |       |   648 (100)|          |
|   1 |  MERGE                            | MV_YEARLY_XXX              |       |       |            |          |
|   2 |   VIEW                            |                            |       |       |            |          |
|   3 |    NESTED LOOPS OUTER             |                            |   119 | 17731 |   648   (1)| 00:00:08 |
|   4 |     VIEW                          |                            |   119 | 12376 |   291   (1)| 00:00:04 |
|   5 |      SORT GROUP BY                |                            |   119 | 11067 |   291   (1)| 00:00:04 |
|   6 |       TABLE ACCESS FULL           | MLOG$_MV_MONTHLY_XXX       |   119 | 11067 |   290   (0)| 00:00:04 |
|   7 |     MAT_VIEW ACCESS BY INDEX ROWID| MV_YEARLY_XXX              |     1 |    45 |     3   (0)| 00:00:01 |
|   8 |      INDEX UNIQUE SCAN            | I_SNAP$_MV_YEARLY_XXX      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

with
statistics as (
select /* exclude */
       st.sql_id
     , st.parsing_schema_name
     , st.module
     , trunc(sn.begin_interval_time) snap_date
     , count(*) no_snapshots
     , round(sum(st.elapsed_time_delta/1000000)) ela_sec_total
     , sum(st.executions_delta) execs_total
     , sum(st.rows_processed_delta) rows_processed_total
     , sum(st.buffer_gets_delta) buffer_gets
  from dba_hist_sqlstat st
  join dba_hist_snapshot sn
    on st.snap_id = sn.snap_id and st.instance_number = sn.instance_number
 where sn.begin_interval_time >= trunc(sysdate) - 14
 group by st.parsing_schema_name
        , st.module
        , st.sql_id
        , trunc(sn.begin_interval_time)
)
,
basedata as (
select /*+ materialize */ st.sql_id
  from dba_hist_sqltext st
 where upper(st.SQL_TEXT) like '%&sql_pattern%'
   and st.sql_text not like '%/* exclude */%'
)
select st.snap_date
     , max(st.sql_id) keep (dense_rank last order by snap_date) last_sql_id
     , sum(ela_sec_total) ela_sec_total
     , sum(execs_total) execs_total
     , sum(rows_processed_total) rows_processed_total
     , round(sum(rows_processed_total)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 0) rows_per_exec
     , round(sum(buffer_gets)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 0) buffer_gets_per_exec
     , round(sum(ela_sec_total)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 2) ela_sec_per_exec
  from basedata bd
  join statistics st
    on bd.sql_id = st.sql_id
 group by st.snap_date
 order by st.snap_date;

SNAP_DATE           LAST_SQL_ID   LAST_PLAN_HASH_VALUE ELA_SEC_TOTAL EXECS_TOTAL ROWS_PROCESSED_TOTAL ROWS_PER_EXEC BUFFER_GETS_PER_EXEC ELA_SEC_PER_EXEC
------------------- ------------- -------------------- ------------- ----------- -------------------- ------------- -------------------- ----------------
16.04.2016 00:00:00 3gqhk0g658b7c            956726641          1075         153              1052262          6878                32921             7.03
18.04.2016 00:00:00 3gqhk0g658b7c            956726641           176          26               251987          9692                45211             6.77
19.04.2016 00:00:00 03zqa2r6v9hxp            956726641          1268         294              2355310          8011                36819             4.31
20.04.2016 00:00:00 03zqa2r6v9hxp            956726641           848          83               715340          8619                42348            10.22
21.04.2016 00:00:00 03zqa2r6v9hxp            956726641           864         133               771265          5799                28422             6.50
22.04.2016 00:00:00 03zqa2r6v9hxp            956726641           700          84               665256          7920                38072             8.33
23.04.2016 00:00:00 avavqbxkq8qpr            956726641         10026         171              1361158          7960                32848            58.63
24.04.2016 00:00:00 avavqbxkq8qpr            956726641          1030          31               214286          6912                29321            33.23
26.04.2016 00:00:00 avavqbxkq8qpr            956726641          9473         321              2763663          8610                35883            29.51
27.04.2016 00:00:00 avavqbxkq8qpr            956726641          3402         183              1153780          6305                27824            18.59

Hier sind mehrere Punkte, die einer Erklärung bedürfen:
  • der Execution Plan ist meiner Einschätzung nach plausibel: bei durchschnittlich weniger als 10K geänderten Datensätzen in einer Tabelle mit 160M rows ist der NL Join mit dem Zugriff über den eindeutigen I_SNAP$-Index eine sinnvolle Wahl.
  • die unterschiedlichen sql_id-s für die haben damit zu tun, dass die intern im Rahmen des Fast Refresh für Materialized Views verwendeten Statements einen OPT_ESTIMATE-Hint enthalten, der von den aktuellen Statistiken der Materialized View abhängt, was ich gelegentlich hier vermerkt hatte.
  • der Neuaufbau der MViews erfolgte am 23.04. und erhöhte die durchschnittlichen Laufzeiten von unter 10 sec. auf Werte von 20-30 sec., was angesichts der Häufigkeit der Refresh-Operationen recht massive Auswirkungen hatte - insbesondere, weil hier mehrere ähnliche MViews betroffen waren.
  • die dabei zu erledigende Arbeit ist weitgehend unverändert, jedenfalls, wenn man nach den Buffer Gets und der Anzahl der Datensätze geht.
Also ein Fall von "die Query läuft langsamer, obwohl sich nichts verändert hat"? Nun, das nicht gerade: immerhin wurden die Objekte komplett neu aufgebaut, was auf jeden Fall eine Änderung der physikalischen Struktur des Segments mit sich brachte. Leider hatte ich mir die Daten vor dem Neuaufbau nicht genau angesehen (sondern nur die DDL dazu) und konnte daher nichts über die ursprüngliche Organisation der Daten sagen. Nach dem Neuaufbau war jedenfalls zu erkennen, dass die Sortierung nach dem Jahr erfolgt war, die weitere Anordnung aber nicht der Reihenfolge der Spalten des I_SNAP$-Index entsprach (so dass dieser auch einen sehr schlechten Clustering Factor besaß). Für die Refresh Operationen wiederum nahm ich an, dass sie auf jeden Fall nach der zweiten Spalte des Index gruppiert sein müssten: nämlich einer Filialnummer. Daraus ergab sich dann die Überlegung, die Materialized Views der Jahres-Ebene nochmals neu aufzubauen: diesmal aber mit einem ORDER BY in der Definition. Dieses ORDER BY spielt nur eine Rolle bei der initialen Anlage der MView und sorgt für ein entsprechendes physikalisches Clustering in den Datenblöcken, das im Lauf der Zeit durch folgende DML-Operationen allmählich nachlassen wird (und hat natürlich auch keinen verlässlichen Einfluss auf die Sortierung von Abfrageergebnissen). Die Idee dabei war, dass sich auf diese Weise die Zahl der physical reads reduzieren lassen könnte, weil die Wahrscheinlichkeit steigt, dass mehrfach bearbeitete Blöcke noch im Buffer Cache vorliegen, wenn sie benötigt werden.

Nach dem Neuaufbau ergaben sich dann folgende Werte (gegenüber der innitialen Analysequery ergänzt um die phsical reads:

SNAP_DATE  LAST_SQL_ID   HASH_VALUE ELA_SEC_TOTAL EXECS_TOTAL ROWS_PROCESSED_TOTAL ROWS_PER_EXEC BUFFER_GETS_PER_EXEC DISK_READS_PER_EXEC ELA_SEC_PER_EXEC
---------- ------------- ---------- ------------- ----------- -------------------- ------------- -------------------- ------------------- ----------------
16.04.2016 3gqhk0g658b7c  956726641          1075         153              1052262          6878                32921                1286             7.03
18.04.2016 3gqhk0g658b7c  956726641           176          26               251987          9692                45211                1132             6.77
19.04.2016 03zqa2r6v9hxp  956726641          1268         294              2355310          8011                36819                 715             4.31
20.04.2016 03zqa2r6v9hxp  956726641           848          83               715340          8619                42348                1887            10.22
21.04.2016 03zqa2r6v9hxp  956726641           864         133               771265          5799                28422                1171             6.50
22.04.2016 03zqa2r6v9hxp  956726641           700          84               665256          7920                38072                1578             8.33
23.04.2016 avavqbxkq8qpr  956726641         10026         171              1361158          7960                32848                6048            58.63
24.04.2016 avavqbxkq8qpr  956726641          1030          31               214286          6912                29321                5662            33.23
26.04.2016 avavqbxkq8qpr  956726641          9473         321              2763663          8610                35883                3783            29.51
27.04.2016 avavqbxkq8qpr  956726641          3402         183              1153780          6305                27824                3948            18.59
28.04.2016 f1za0dqt3r803  956726641           134          50               433547          8671                37853                1606             2.68
29.04.2016 f1za0dqt3r803  956726641           433         204              1395873          6843                30404                1009             2.12
30.04.2016 f1za0dqt3r803  956726641            99          28               299080         10681                48064                1850             3.54

Sichtbar ist hier vor allem Folgendes:
  • der Plan ist immer noch der gleiche und auch die Werte für die Buffer Gets und verarbeiteten Datensätze bleiben im gleichen Bereich.
  • die durchschnittliche Anzahl der disk reads sinkt wieder von 4-6K auf unter 2K.
  • die durchschnittlichen Laufzeiten liegen jetzt zwischen 2-4 sec: also sogar deutlich niedriger als vor dem ersten Neuaufbau der Mviews
Das ist zunächst sehr zufriedenstellend. Aber was kann man daraus lernen? Vielleicht Folgendes: Das physikalische Clustering von Daten kann - insbesondere in Data Warehouse Umgebungen - einen ziemlich entscheidenden Einfluss auf die Performance von Abfragen und DML Operationen haben. Manchmal lohnt es sich hier durchaus, über eine physikalische Reorganisation nachzudenken, um die Anzahl kostspieliger physikalischer Zugriffe zu reduzieren. Dabei stellt sich dann natürlich die Frage nach der Sortierung für dieses Clustering, die von den jeweiligen Zugriffen abhängt. Außerdem ist zu überlegen, ob sich die Mühe lohnt, da die Sortierung sich im Lauf der Zeit wieder ändern wird, sofern man nicht mit read-omly Daten zu tun hat.