Unzureichende Hint-Angaben

Jonathan Lewis spricht in seinem Blog einen jener Fälle an, in denen die Angabe eines einzelnen (INDEX-) Hints nicht genügt, um einen gewünschten Zugriff hervorzurufen: um den erwarteten NL-Join hervorzurufen bedarf es im Beispiel zusätzlich der Hints LEADING und USE_NL. Besonders spektakulär ist der Fall dabei nicht - ich erwähne ihn vor allem deshalb, weil ich im zugrunde liegenden OTN-Thread an der Erhöhung der Hint-Anzahl für das betroffene Statement beteiligt war...

Gefahren beim Oracle-Debugging

Tanel Poder erwähnt in seinem Blog ein paar unerfreuliche Effekte, die sich bei der Verwendung von ORADEBUG und ptrace() basierten Debugging-Mechanismen ergeben können und kommt zum Schluss "don’t just run debugger or ptrace() based commands against critical background processes in production!".

MySQL: Gefangen im Nested Loop

In den letzten Wochen habe ich mich endlich mal ein wenig mit der Arbeitsweise von MySQL beschäftigt und dabei ein paar ganz interessante Dinge herausbekommen - und auch ein paar Überraschungen erlebt. Die größte Überraschung dabei war, dass MySQL Join-Operationen mit erstaunlicher Einfallslosigkeit durchführt: jeder Join ist ein Nested Loop. Ich konnte mir das zunächst nicht so recht vorstellen: was passiert denn dann beim Join großer Datenmengen, wenn man nur den NL-Join zur Verfügung hat und weder MERGE JOIN noch HASH JOIN, also nur ein Verfahren, das sehr gut für Zugriffe mit hoher Selektivität geeignet ist und erste Ergebnisse rasch zurückliefert, aber mit Massendaten wenig Freude bereitet? Die Antwort lautet: man wartet.

Dazu ein kleiner Test, der nicht so ganz fair ist, weil er die unterschiedliche Konfiguration der Datenbanken nicht berücksichtigt und außerdem einen Fall konstruiert, bei dem MySQL dumm aussehen muss. Dabei lege ich zwei Tabellen mit identischen Inhalten an, die jeweils 32 mal die Menge der Zahlen von 1 bis 1000 und eine zusätzliche Füll-Spalte enthalten:

-- Oracle 11.1.0.7
-- Windows 7
drop table t1;
drop table t2;

create table t1
as
with 
generator1
as (
select rownum col1
     , lpad('*', 100, '*') col2
  from dual
connect by level <= 1000)
,
generator2
as (
select rownum id
  from dual 
connect by level <= 32)
select generator1.*
  from generator1
     , generator2
 order by generator2.id
        , generator1.col1;
        
create table t2
as
select * from t1;        
        
exec dbms_stats.gather_table_stats(user, 'T1')        
exec dbms_stats.gather_table_stats(user, 'T2')

explain plan for
select count(*)
  from t1, t2
 where t1.col1 = t2.col1;

select count(*)
  from t1, t2
 where t1.col1 = t2.col1;

Oracle findet diese Aufgabe ziemlich banal und liefert folgende Ergebnisse:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 906334482

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   286   (3)| 00:00:04 |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |
|*  2 |   HASH JOIN         |      |  1024K|  8000K|   286   (3)| 00:00:04 |
|   3 |    TABLE ACCESS FULL| T1   | 32000 |   125K|   140   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   | 32000 |   125K|   140   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."COL1"="T2"."COL1")

SQL> select count(*)
  2    from t1, t2
  3   where t1.col1 = t2.col1;

  COUNT(*)
----------
   1024000

Abgelaufen: 00:00:00.08

Angesichts des Fehlens von Indizes wird daraus ein HASH JOIN, dessen cardinality-Schätzungen völlig akkurat sind, was angesichts der Harmlosigkeit der Query nicht weiter überrascht. Nun der gleiche Fall mit MySQL - wobei dort kein dem connect by dual Verfahren entsprechender Generator zur Verfügung steht (und auch keine CTEs), so dass der Aufbau mit traditionelleren Techniken (mit einer Tabelle, die über eine gewisse Anzahl von Datensätzen verfügt) erfolgen muss:

-- MySQL 5.6.10
-- Windows 7
-- Tabellen-Engine: InnoDB

use world;

drop table t1;
drop table t2;

set @NUM = 0;

create table t1
select @NUM:=@NUM+1 col1
     , repeat('*', 100) col2
  from city t 
 limit 1000;

insert into t1
select * from t1;

insert into t1
select * from t1;

insert into t1
select * from t1;

insert into t1
select * from t1;

insert into t1
select * from t1;

create table t2
select * from t1;

explain
select count(*)
  from t1, t2
 where t1.col1 = t2.col1;

select count(*)
  from t1, t2
 where t1.col1 = t2.col1;

Das Parsing macht MySQL dabei keine Schwierigkeiten; möglicherweise hätte ich eine explizite Statistikerfassung durchführen sollen, aber die 31808 sind ja schon recht nah am korrekten Ergebnis - für den Plan hätte es aber ohnehin keinen Unterschied gemacht, denn eine andere Lösung hat die Engine nicht:

mysql> explain select count(*)  from t1, t2 where t1.col1 = t2.col1;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 31808 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 31808 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select count(*)  from t1, t2 where t1.col1 = t2.col1;
+----------+
| count(*) |
+----------+
|  1024000 |
+----------+
1 row in set (41.63 sec)

Also 0,08 sec. für Oracle und 41,63 sec. für MySQL - wie gesagt: es ist kein ganz fairer Vergleich, aber man sieht doch recht deutlich, dass MySQL für Join-Operationen dieser Art nicht ausgesprochen gut geeignet ist. Die Engine liest hier jeden Datensatz aus T1 und führt dazu dann eine Lookup-Leseoperation in T2 durch. Und da T2 für diesen Lookup keinen unterstützenden Index anbieten kann, werden das dann insgesamt 32001 Full Table Scans (im Plan: type = ALL). In den Statistiken der Session (information_schema.session_status) lässt sich diese Arbeit nicht so recht ablesen - immerhin erhöhen sich die Werte für SLOW_QUERIES (laut Doku: "The number of queries that have taken more than long_query_time seconds"), SELECT_SCAN ("The number of joins that did a full scan of the first table.") und SELECT_FULL_JOIN ("The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables."). Offensichtlich hält MySQL das Fehlen von Indizes in einem solchen Fall für ein ernstes Versäumnis. Hier also der Index zur Unterstützung des Joins:

-- MySQL
create index t2_idx on t2(col1);

mysql> explain select count(*)  from t1, t2 where t1.col1 = t2.col1;
+----+-------------+-------+------+---------------+--------+---------+---------------+-------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref           | rows  | Extra       |
+----+-------------+-------+------+---------------+--------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL   | NULL    | NULL          | 31808 | Using where |
|  1 | SIMPLE      | t2    | ref  | t2_idx        | t2_idx | 9       | world.t1.col1 |    16 | Using index |
+----+-------------+-------+------+---------------+--------+---------+---------------+-------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*)  from t1, t2 where t1.col1 = t2.col1;
+----------+
| count(*) |
+----------+
|  1024000 |
+----------+
1 row in set (0.41 sec)

Mit 32000 Index-Lookups schafft MySQL den Join also in 0,41 sec. Das ist natürlich eine Verbesserung, aber ein HASH JOIN wäre hier immer noch geeigneter, was nicht nur meine, sondern auch die Meinung des CBO im Oracle-Fall ist:

-- Oracle
create index t2_idx on t2(col1);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1807176592

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |     8 |   167   (5)| 00:00:03 |
|   1 |  SORT AGGREGATE        |        |     1 |     8 |            |          |
|*  2 |   HASH JOIN            |        |  1024K|  8000K|   167   (5)| 00:00:03 |
|   3 |    TABLE ACCESS FULL   | T1     | 32000 |   125K|   140   (1)| 00:00:02 |
|   4 |    INDEX FAST FULL SCAN| T2_IDX | 32000 |   125K|    20   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."COL1"="T2"."COL1")

Im Oracle-Fall wird aus dem FULL TABLE SCAN also ein INDEX FAST FULL SCAN, weil T2_IDX deutlich kleiner ist als T2. Um Oracle vom NL-Join zu überzeugen, muss ein USE_NL-Hint verwendet werden. In diesem Fall steigen aber die logischen Lesezugriffe (consistent gets) von 569 für den HASH JOIN auf 15486 für den NESTED LOOPS.

Auf meinen Merkzettel kommt jedenfalls die Erkenntnis, dass ich im MySQL-Kontext auf große Join-Operationen lieber verzichten sollte, bis dort HASH JOIN-Operationen möglich sind - was dann möglicherweise ad calendas graecas bedeuten mag... In Maria-DB 5.5 ist der HASH JOIN übrigens impelmentiert, wie ich bei Ovais Tariq gelesen habe, der eine sehr solide wirkende Analyse aktueller MySQL- und MariaDB-Join-Verfahren liefert.

P.S.: Ursprünglich wollte ich drüber schreiben: "To HASH and HASH Not" nach der schönen Howard Hawks Verfilmung des entsprechenden Hemingway-Romans mit Humphrey Bogart und Loren Bacall, aber dann erinnerte ich mich daran, dass ich eigentlich inhaltlich halbwegs plausible Überschriften wählen wollte, um das vorgestellte Publikum (und mich) nicht zu desorientieren. Jetzt ist der Titel dafür dann klassische B-Film-Benennung.

SQL Patch zur Ergänzung von Dynamic Sampling in einem gegebenen Statement

Der Titel ist diesmal länger als der Eintrag...

Jonathan Lewis zeigt in seinem Blog, wie man mit Hilfe eines SQL Patch (erzeugt über dbms_sqldiag_internal.i_create_patch) einem vorliegenden SQL-Statement, dessen Text man nicht verändern kann/darf, nachträglich einen dynamic_sampling-Hint spendieren kann, um z.B. Spaltenkorrelationseffekte zu behandeln. Der Artikel enthält auch Links auf Dominic Brooks Artikelserie zum Thema und auf einen entsprechenden Beitrag im Blog der CBO Entwickler - bei genauerem Hinsehen fällt mir auf, dass ich die hier auch schon mal zusammengefasst hatte.

Heat Maps mit sqlplus

Vor kurzem hat Luca Canali in seinem Blog ein SQL*plus Script vorgestellt, das eine grafische und - vor allem - farbige Visualisierung von wait event latency (insbesondere von I/O latency) in Form einer Heat Map liefert. Bei Kyle Hailey gibt's noch ein paar ergänzende Vorschläge zum Thema. Immer wieder erstaunlich, was man mit SQL*plus alles machen kann.

Ein paar Hinweise zum Farbeinsatz in SQL*plus findet man übrigens im Artikel SQL*Plus tips #6: Colorizing output von Sayan Malakshinov.

Nachtrag 31.05.2013: in einem weiteren Beitrag OraLatencyMap v1.1 and Testing with SLOB 2 testet der Herr Canali sein Visualisierungstool im Zusammenspiel mit Kevin Clossons SLOB-Werkzeug.

Deadlock Ursachen

In den letzten Wochen gab es eine bemerkenswerte Häufung von interessanten Artikeln zum Thema deadlocks:

• Arup Nanda - Application Design is the only Reason for Deadlocks? Think Again: liefert eine instruktive Einführung zum Thema, erklärt, welche Lock-Typen im Spiel sind, wie man die zugehörigen trace files liest und führt aus, in welchen Fällen sich deadlocks ergeben, die nicht auf Fehler in der Anwendungslogik zurückzuführen sind (ITL shortage, unindexed foreign keys, direct load operations, bitmap index contention, PK overlap beim INSERT)
• Jonathan Lewis - deadlocks: erklärt, dass den rowid Angaben in deadlock Graphen in vielen Fällen nicht zu trauen ist: "When I see a deadlock graph on transaction locks and the waits are for S mode I tend to assume that the information about the rows waited on is probably misleading; when the slot number for the rowid is zero this increases my confidence that the rowid is rubbish."
• Christian Antognini - ITL Deadlocks (script): liefert ein Test-Script zu Erzeugung eines Beispiels für die (auch beim Herrn Nanda erwähnte) ITL shortage. Interessant ist am Artikel neben dem Script auch die hübsche Präsentation eines Mitschnitts der Scriptausführung.

Materialized View Fast Refresh für Outer Joins in 11.2.0.3

Alberto Dell'Era erläutert in seinem Blog die aktuelle Implementierung von Fast Refresh Operationen für Materialized Views auf der Basis von Outer Join Queries:

• fast refresh of outer-join-only materialized views – algorithm, part 1: erklärt, dass eine solche Fast Refresh Operation relativ übersichtlich ist, wenn es einen unique constraint auf den Join-Spalten gibt, der sicherstellt, dass die Gesamtzahl der Datensätze der Anzahl der Sätze der outer table entspricht, weil es zu jedem Satz dieser Tabelle genau ein oder kein Gegenstück in der inner table gibt. Außerdem wird erklärt, welche Indizes zur Beschleunigung der Refresh Operationen angelegt werden können.
  
• fast refresh of outer-join-only materialized views – algorithm, part 2: erklärt die komplexeren SQL-Operationen, die im Fall eines Fast Refreshs einer Outer-Join-MV ohne unique constraint auf den Join-Spalten erforderlich sind - und erläutert die Indizes, die zur Optimierung dieser Operationen angelegt werden können.

Meine Zusammenfassung erhebt einmal mehr nicht den Anspruch, eine erschöpfende Zusammenfassung zu sein, sondern soll nur als Verweis und Erinnerungsstütze dienen.

Details zum Hakan Factor

Vor zwei Jahren habe ich hier ein paar Bemerkungen zum Hakan Factor und zur MINIMIZE RECORDS_PER_BLOCK Option notiert. Jetzt hat Jonathan Lewis in seinem Blog ein paar der Informationen ergänzt, die mir damals fehlten: zunächst liefert er eine Procedure show_hakan, die die SPARE1-Angabe in TAB$ auswertet. Darüber hinaus bestätigt er die Beobachtung, dass man den Hakan Factor über MINIMIZE RECORDS_PER_BLOCK nicht dazu bringen kann, die Satzanzahl im Block auf 1 zu reduzieren, was anscheinend daran liegt, dass der Factor immer um 1 niedriger ist als die angestrebte Satzanzahl im Block und nicht auf 0 gesetzt werden kann. Anlass für den Artikel des Herrn Lewis war dabei ein Thread im OTN Forum, in dem Matthias Rogel auf einen Artikel von Karsten Spang hinwies, der noch ein paar zusätzliche Aussagen zum Thema enthält:

• Unterschiede im Hakan Factor sind eine wahrscheinliche Ursache für "ORA-14642: Bitmap index mismatch" beim Partition Exchange.
• da der Hakan Factor die Anzahl der rows pro Block angibt, muss er für alle Indizes der Tabellenpartitionen identisch sein, um bit Operationen (AND/OR) zu ermöglichen.
• mit ALTER TABLE ... MINIMIZE RECORDS_PER_BLOCK wird der aktueller aktuelle Maximalwert für die Sätze in einem Block bestimmt und als Limit für folgende INSERTs gesetzt. Das Kommando kann nur ausgeführt werden, wenn noch keine bitmap indizes für die Tabelle angelegt sind.
• Zur Interpretation von SPARE1 liefert Karsten Spang folgende Informationen:
• "Lower bits (at least 12, perhaps as many as 15): Håkan factor"
• "0×08000: MINIMIZE RECORDS_PER_BLOCK in effect"
• "0×10000: Seems to mean that the Håkan factor has been fixed higher than the value calculated from non-null columns."
• "0×20000: Table compression is enabled"
• mit Hilfe von Event 14529 kann man dafür sorgen, dass eine CTAS-Operation eine neue Tabelle mit dem Hakan Factor der Quell-Tabelle anlegt, was zur Lösung von Problemen mit ORA-14642 beitragen kann.

Da die angesprochenen Details nicht offiziell dokumentiert sind, handelt es sich natürlich bei allen Angaben um - begründete - Vermutungen.

Berechnungskorrektur für den CLUSTERING_FACTOR

Richard Foote weist in seinem Blog auf eine wichtige Errungenschaft von Release 12c hin, die es als Patch hinab in die Versionen 11.1.0.7, 11.2.0.2 und 11.2.0.3 geschafft hat: die lange erwartete Korrektur des CLUSTERING_FACTOR (CF) durch einen ergänzenden zweiten Parameter für die Funktion sys_op_countchg. Zur Erinnerung: der CF ist ein Maß für die Sortierung einer Tabelle hinsichtlich des zugehörigen Index: ist die Sortierung von Tabelle und Index identisch, dann ergibt sich ein günstiger CF, der der Anzahl der Tabellenblocks entspricht. Weicht die Sortierung deutlich voneinander ab, so dass ein Index Range Scan von einem Index Block aus in sehr viele Tabellenblocks springen muss, dann ergibt sich ein ungünstiger CF, der im Extremfall der Anzahl der Zeilen in der Tabelle entspricht. Neben der Anzahl der Leaf-Blocks ist der CF die entscheidende Komponente in der Kostenberechung für Index-Zugriffe (sofern sie einen Tabellenzugriff hervorrufen und nicht auf die Indexstruktur beschränkt bleiben).

Das größte Problem des CF war in der Vergangenheit, dass er keine Historie zuletzt betrachteter Blocks berücksichtigte: der Wechsel zwischen wenigen Tabellenblocks, auf die abwechselnd zugegriffen wurde, führte demnach zu einem sehr hohen CF, obwohl die Blocks in einem solchen Fall vermutlich im Cache gehalten wurden und einen sehr effizienten Zugriff gestatteten. Ein Fall, der sich ungünstig auf die CF-Berechnung auswirkt, ist ASSM, da dabei ja explizit mehrere Blocks parallel für eingehende Inserts verwendet werden. Mit Patch 15830250 (der die zutreffende Beschreibung "Index Clustering Factor Computation Is Pessimistic" trägt) wurde der Funktion sys_op_countchg, die für die CF-Berechnung verantwortlich ist, ein zweiter Parameter spendiert, der die Anzahl der zuletzt besuchten Blocks angibt, die bei der CF-Berechnung berücksichtigt werden sollen. Im Artikel des Herrn Foote gibt's neben einer etwas ausführlicheren Erläuterung auch noch ein schönes Testbeispiel. Der Vollständigkeit halber hier noch der Verweis auf Jonathan Lewis' Kommentar zum Thema und Martin Deckers Artikel, der die Auswirkung des Patches als erster angesprochen hatte.

Nachtrag 19.05.2013: in einem zweiten Artikel Clustering Factor Calculation Improvement Part II (Blocks On Blocks) zeigt Richard Foote, dass die TABLE_CACHED_BLOCKS Angabe in der Regel keine massiv ungünstigen Wirkungen auf die CF-Berechnung hat und gibt eine übersichtliche mathematische Erklärung dafür.

Fillfactor im SQL Server

In Brent Ozars Blog erläutert Kendra Little die Bedeutung des Fillfactors im SQL Server. Grundsätzlich entspricht der Fillfactor in etwa der PCTFREE-Angabe für Oracle-Blocks, dient also dazu, Platz für spätere Einfügungen zu reservieren. Allerdings betrifft der Fillfactor nur Index-Strukturen, was insofern kein extreme Einschränkung ist, als der Index (in Form des Clustered Index) ja die Standard-Struktur zur Datenhaltung im SQL Server darstellt. Hier ein paar zusätzliche Stichpunkte (wie üblich ohne Anspruch auf Vollständigkeit):

• der fill factor kann auf Instanzebene (sp_configure) oder für einen bestimmten Index gesetzt werden; die Angabe auf Instanzebene ist vermutlich nutzlos (weil viel zu allgemein)
• er wird verwendet, um index page splits (des Typs 50:50) zu vermeiden, da diese schwach gefüllte pages hervorrufen (können)
• vorgeschlagen wird eine "good index maintenance solution that checks index fragmentation and only acts on indexes that are fairly heavily fragmented". Rebuilds also nur in geeigneten Fällen.
• der fill factor gilt nicht für heap Tabellen, aber mit denen hat man im SQL Server bekanntlich nicht allzu viel Freude.
• der fill factor wirkt sich nicht auf neue pages aus, die am Ende des Index eingefügt werden (also im Fall von 99:1 splits)
• der fill factor spielt keine Rolle für LOB pages

Insgesamt ist Frau Little eher skeptisch im Hinblick auf eine sinnvolle Verwendung des fill factor. Um ihn nutzbringend einzusetzen, muss man eine ziemlich klare Vorstellung von den Einfügeoperationen in den Index haben.

Gescheiterte Oracle-BI-Produkte

Mark Rittman nennt 10 Oracle-BI-Produkte "You May Not Have Heard Of…". Oder auch: wünschen würde, nie davon gehört zu haben...

Historisierungsverfahren im Vergleich

Dani Schnider (und nicht Snider, wie ich häufiger behauptet habe...) vergleicht im Trivadis-Blog die Historisierungsverfahren des Data Vault Modeling von Dan Linstedt mit der master data Versionierungstechnik, die er (und seine Kollegen) im Buch Data Warehousing mit Oracle vorstellt. Das Data Vault Modeling ist mir erstmals vor ein paar Jahren begegnet, als der (von mir sehr geschätzte) Thomas Kejser im Kimball Forum eine recht heftige Debatte mit dem Herrn Linstedt austrug. Ernsthaft auseinandergesetzt habe ich mich mit dem Thema allerdings nicht, möglicherweise abgeschreckt von der eigenwilligen Begriffswahl im Vault Modeling.

Im Vergleich mit der (jedenfalls auf begrifflicher Ebene) deutlich zugänglicheren Technik des Trivadis-Buchs wird das Data Vault Modeling dann aber recht gut beschreibbar:

• der head table des Trivadis-Modells entspricht der Hub in der Vault: enthalten ist hier jeweils ein versionsunabhängiger Schlüssel für jede business entity.
• die version tables des Trivadis-Modells unterscheiden sich von den Satellites der Vault dadurch, dass die version table nur veränderliche Attribute enthält (die statischen Informationen werden in der head table gehalten), während die Satellites alle Attribute - also auch die unveränderlichen - enthalten.
• die Links der Vault sind immer many-to-many relationships, wodurch das Datenmodell sehr flexibel bleibt.

Dani Schniders Artikel liefert noch weitere Details, aber mir genügt diese Gegenüberstellung in erster Näherung. Ich bin ein großer Freund derartiger idealtypischer Vergleiche, bei denen ein unbekanntes Konzept durch Gegenüberstellung zu einem bekannten Konzept erklärt wird: auch der SQL Server ist für mich ein merkwürdig verzerrtes Oracle-System (was man natürlich auch umgekehrt sehen kann).