Analytics und Sortierungen

Schon vor einiger Zeit hat Jonathan Lewis ein recht erschreckendes Beispiel für die Probleme mit umfangreichen Sortierungen beim Einsatz von Analytischen Funktionen vorgestellt. Dort erklärt er schließlich:

The code to handle analytic sorts (the WINDOW SORT operation) has some serious defects which show up if the sort spills to disc – and a sort of more the 100MB will spill to disc if you set workarea_size_policy to auto – no matter how large your pga_aggregate_target (and the derived _smm_max_size).

Vermutlich ist es in der Regel die bessere Idee, Analytics eher für übersichtlichere Datenmengen zu verwenden.

Umwandlung von parent-child-Hierarchien

nachdem ich bei ähnlichen Fragestellungen in der Vergangenheit zu recht bizarren Lösungen unter Einsatz der sys_connect_by_path-Funktion und Parsen des gelieferten Strings über substr und instr gekommen war, bin ich inzwischen klüger geworden (möglicherweise auch nach Betrachtung der Erläuterungen zur neuen recursive subquery factoring Syntax bei Rob van Wijk) und habe folgende recht harmlose Query zum Umbau einer parent-child-Hierarchie in eine nicht normalisierte Matrix mit n-Ebenen geschrieben:

with
groups as
(select groupid 
      , groupname
      , parentid
   from ext_groups)
select ig0.groupid groupid_level0
     , ig0.groupname groupname_level0
     , ig1.groupid groupid_level1
     , ig1.groupname groupname_level1
     , ig2.groupid groupid_level2
     , ig2.groupname groupname_level2
     , ig3.groupid groupid_level3
     , ig3.groupname groupname_level3
     , ig4.groupid groupid_level4
     , ig4.groupname groupname_level4
  from 
       -- Root-Ebene
       (select *
          from groups
         where parentid = 0
        ) ig0
       -- Ebene 1
     , (select *
          from groups
        ) ig1
       -- Ebene 2
     , (select *
          from groups
        ) ig2
       -- Ebene 3
     , (select *
          from groups
        ) ig3
       -- Ebene 4 (= Branch-Ebene)
     , (select *
          from groups
        ) ig4
 where ig1.parentid = ig0.groupid
   and ig2.parentid = ig1.groupid
   and ig3.parentid = ig2.groupid
   and ig4.parentid = ig3.groupid
 order by ig0.groupid
        , ig1.groupid
        , ig2.groupid
        , ig3.groupid
        , ig4.groupid

Dabei werden die relevanten Spalten der Basistabelle (in diesem Fall eine external table) ext_groups über subquery factoring als groups verfügbar gemacht, dann auf Root-Ebene alle Elemente mit der parentId 0 ausgewählt (hier könnte natürlich auch ein NULL-Wert stehen) und anschließend alle weiteren Ebenen über Joins = angeschlossen. Das Verfahren sollte auch für unbalancierte Hierarchien funktionieren, dabei müsste man sich aber noch Gedanken über die Formulierung der Outer-Joins machen.

SSAS Musings

für den unwahrscheinlichen Fall, dass außer mir jemand diese Einträge liest, hier der Hinweis auf zwei weitere (allerdings englischsprachige) Blogs, zu denen ich beitrage bzw. beitragen kann|werde:

SSAS Musings: wo diverse Einsichten zum Verhalten der SQL Server Analysis Services (SSAS) gesammelt werden
ROLAP Musings: wo relationale Dinge erhellt werden sollen (was sich unter Umständen mit diesem Blog überschneiden könnte)

Consistent Gets

Mal wieder ein Link, diesmal zum Thema constistent gets und auf den Blog von Harald van Breederode. Erläutert wird, wieso der Zugriff auf eine Tabelle, die nur einen Block umfasst, 8 consistent gets benötigt (3 sind bereits für den Zugriff auf eine leere Tabelle erforderlich, die übrigen ergeben sich aus der verwendeten array size). Erhellend sind auch die Hinweise auf die Effekte von Gruppenfunktionen, Lesekonsistenz und Veränderungen der HWM. Da es gute Gründe gibt, die Anzahl der consistent gets als zentrales Kriterium für die Performance einer Query anzusehen, sollte man (also ich) sich diese Details merken.

Systemstatistiken

Eine schöne und kompakte Einführung zur Rolle der Systemstatistiken für die Kostenberechnung des CBO findet man bei Richard Foote. Dort findet sich auch die grundlegende Berechnungsformel, die Jonathan Lewis in seinem CBO-Buch erläutert, und die ich vor einiger Zeit hier als Erinnerungshilfe hinterlegt hatte.

MV_CAPABILITIES_TABLE

Rob van Wijk schreibt seit einiger Zeit eine interessante und sehr umfangreiche Serie ausführlicher Blog-Artikel zu den Fehlermeldungen, mit denen Materialized Views den Nutzer verwirren wollen. Hier geht's um die MV_CAPABILITIES_TABLE und neben den Fehlermeldungen wird auch die Verwendung dieses nützlichen Hilfsmittels erläutert.

MV Refresh

Mag sein, dass ich hier nur noch Verweise unterbringe, aber sei's drum: bei Alberto Dell'Era gibt's sehr schöne Erläuterungen zu den Mechanismen, die beim fast refresh für Materialized Views im Spiel sind. Dabei betrachtet er:

• MVs mit einfachem Join
• MVs mit einer Tabelle

Da ich inzwischen eher MOLAP-Lösungen verwende, ist das für mich nicht mehr ganz so aktuell, aber trotzdem sehr interessant.

outlines

Mit Outlines habe ich mich noch nie ernsthaft beschäftigt, aber wenn ich es tun wollte, könnte ich bei Kerry Osborne dazu allerlei Interessantes finden.

optimizer_index_cost_adj

In seinem Blog zeigt Richard Foote, warum der optimizer_index_cost_adj-Parameter, der immer wieder gerne als Allheilmittel für Performanceprobleme in OLTP-Systemen verkauft wird, bei Indizes mit extremem clustering factor zu erschreckenden Laufzeiten führen kann. Je besser der cbo wird, desto fragwürdiger scheint mir das Drehen an solchen Schrauben zu werden (man muss ihm nur gute Statistkwerte geben).

Beim Einsatz des CPU-Costings (= Verwendung von Systemstatistiken) ist ein zusätzliches Setzen des Parameters noch sinnloser, da dieser eine Krücke ist, um zu erreichen, was das CPU-Costing ohnehin leistet, nämlich eine sinnvolle Gewichtung des Verhältnisses von single und multiblock I/O-Operationen zu bestimmen. Man würde also das korrekte Systemverhalten noch mal übersteuern. Auch dazu gibt's inzwischen eine umfassendere Erläuterung bei Herrn Foote.

automatische Statistikerstellung ab 10g

Mir persönlich gefällt die Automatisierung der Statistikerfassung, die in 10g mit dem GATHER_STATS_JOB realisert wurde, eigentlich recht gut, obwohl ich mir vorstellen kann, dass es Situationen geben kann, in denen man damit auch jede Menge Ärger bekommen könnte. Ein paar ganz interessante Überlegungen dazu findet man bei Martin Widlake.

Ein wenig überrascht hat mich aber, dass der Job in 11g schon nicht mehr existiert, und durch etwas Neues ersetzt wurde - ein paar einleitende Worte dazu findet man bei Tim Hall.

SYS_GUID

Bei Carsten Czarski gibt's einen interessanten Hinweis auf die Möglichkeit, die Funktion SYS_GUID zur Erzeugung global eindeutiger IDs (also z.B. für Primärschlüssel) zu verwenden. Erläutert werden dabei auch die Vor- und Nachteile gegenüber der Verwendung von Sequences.

Einführung zum Thema "Materialized Views"

wer in Jonathan Lewis' Blogroll erscheint, muss interessante Dinge schreiben, dachte ich mir, als ich dort Uwe Hesses Blog entdeckte. Diese Vermutung ließ sich dann auch recht schnell verifizieren. Der einführende Artikel zu den Materialized Views ist sehr instruktiv und bringt schön auf den Punkt, was der tiefere Sinn der Sache ist. Erinnert mich persönlich ein wenig an die kompakten Erläuterungen, die man bei Tim Hall findet - sehr gutes Einführungsmaterial, wenn man sich nicht die Zeit nehmen kann (oder möchte), die Dokumentation durchzuarbeiten.

SQL_Monitor

Bei Doug Burns konnte man dieser Tage etwas über das in Oracle 11 verfügbare SQL Monitoring mit Hilfe des SQL Developers finden. Da ich Datenbanken aber traditionsbedingt (und auch noch aus anderen Gründen) lieber über sqlplus anspreche, habe ich mir mal ein simples Analyse-Script für die zugehörigen Tabellen V$SQL_PLAN_MONITOR und V$SQL_MONITOR gebastelt, mit dem ich hoffentlich in Zukunft etwas genauer sagen kann, was eine lang laufende Query denn nun aktuell genau tut (ohne auf die vageren Aussagen aus v$bh, v$sql_workarea angewiesen zu sein):

select p.status
     , s.id
     , s.parent_id
     , lpad (' ', depth - 1) || s.operation || ' ' || s.options operation
     , s.object_name
     , p.starts
     , p.output_rows
     , (select r.buffer_gets
          from v$sql_monitor r
         where r.key = p.key) buffer_gets
     , s.cost
     , s.time
     , s.cardinality
     , s.bytes
     , s.search_columns
     , s.access_predicates
     , s.filter_predicates
     , s.projection
     , s.cpu_cost
     , s.io_cost
  from v$sql_plan_statistics_all s
  left outer join
       v$sql_plan_monitor p
    on (s.sql_id = p.sql_id and s.id = p.plan_line_id)
 where s.sql_id = '&&1'
 order by s.id;

Noch nicht besonders elegant (meine unglückliche Liebe zu skalaren Subqueries schlägt sich einmal mehr nieder), aber - so hoffe ich zumindest - erst mal verwendbar.

Nachtrag 05.08.2011: hier noch ein Link auf die Lizenzierungsinformationen: Voraussetzung für das SQL Monitoring ist das Tuning Pack. Außerdem noch der Hinweis, dass man statt des SQL-Zugriffs auf die angegebenen v$sql_plan%-Views auch die vorformatierte Variante des dbms_sqltune-Packages (report_sql_monitor) verwenden kann - wie in meinem aktuellen Script zum Thema.

XPLAN

Alberto Dell'Era (ich glaube, er ist Mitglied der OakTable) hat in seinem (noch recht jungen) BLOG ein sehr interessantes Analyse-Script vorgelegt, mit dem man sich einen riesigen Berg detaillierter Informationen zu einem gegebenen Statement liefern lassen kann (sofern man Zugriff auf die befragten Dictionary-Objekte besitzt). Ich musste zwar ein wenig probieren, bis ich das Script zum Laufen brachte (weil ich die Dokumentation mal wieder nur flüchtig überflogen hatte; ich vermute aber auch, dass man an der Bedienung noch arbeiten könnte), aber selbst wenn man das Script nicht verwenden möchte, kann man sich jede Menge Anregungen holen, was man sich beim Statement-Tuning noch alles ansehen könnte.

NLS_LANG

Da ich immer mal wieder danach suche: ein NLS_LANG-Eintrag, der Umlaute in sqlplus darstellt:

GERMAN_GERMANY.WE8PC850

Model clause

nachdem ich mich jetzt seit einigen Monaten etwas intensiver mit MDX beschäftigt habe, beginne ich allmählich auch zu begreifen, wozu die Model clause dient. Falls ich's noch genauer wissen wollte, wäre Rob van Wijks kurze Blob-Serie ein guter Einstieg, die Jonathan Lewis hier verlinkt hat.

Osterquiz

mit Laurent Schneiders netter Eastern challenge habe ich heute einige Zeit zugebracht, war aber noch ein paar Zeichen über dem Limit, als ich die Lösungen sah. Dabei ging es darum, den seltensten Job aus der emp-Tabelle zu ermitteln. Noch kürzer als die vorgeschlagenen (sehr hübschen) Lösungen wäre allerdings folgende Variante gewesen:

select job from emp where ename='KING';

Ok, tut mir leid, ich musste das einfach irgendwo unterbringen ...

Datenbanken.Grundlagen und Design von Frank Geisler

ein weiteres Buch des mitp-Verlags, das ich mir angesehen habe, ist
dieses: eine sehr solide Einführung zum Thema Datenbanken von Frank Geisler. Der Band beschäftigt sich mit vielen grundlegenden Fragen, erläutert, wozu Datenbanken grundsätzlich dienen, welche unterschiedlichen Systeme im Lauf der Zeit entwickelt wurden, führt in grundlegende Modellierungstechniken ein, gibt einen Überblick über die relationale Algebra und SQL und erklärt die zentralen Funktionen eines RDBMS (Transaktionsverwaltung). Darüber hinaus werden auch weiterführende Themen behandelt, etwa die Besonderheiten von Data Warehouses und die Verwendung objektrelationaler Mechanismen. Das Buch ist dabei klar gegliedert, lesbar geschrieben und liefert ausführliche Erläuterungen und Beispiele. Sehr gut gefallen haben mir die immer wieder eingeschalteten historischen Erläuterungen zur Herkunft von Techniken und Paradigmen - viele technische Bücher sprechen nur vom aktuellen Zustand, der bei der Drucklegung oft schon überholt ist. Den diadaktischen und Einführungscharakter des Bandes unterstreichen die Fragen und Aufgaben am Ende jedes Kapitels, zu denen dann auch die Lösungen enthalten sind. Insgesamt ein gutes Einführungsbuch, das ungefähr 95% der Fragen, die in Datenbankforen immer wieder gestellt werden, beantworten dürfte.

diagnostic events

mal wieder ein erinnerungswürdiger Blog-Eintrag bei Tanel Poder: hier findet man ein paar Erklärung dazu, was mit "10046 trace name context forever, level 12" eigentlich gemeint ist.

Oracle 11g von Lutz Fröhlich

Neben dem Oracle Survival Guide hatte mir der mitp-Verlag vor Weihnachten auch noch Lutz Fröhlichs Oracle 11g - Das umfassende Handbuch zu Rezensionszwecken überlassen - und nachdem sich meine Begeisterung für die schmale Überlebenshilfe doch eher in Grenzen gehalten hatte, wurde ich vom zweiten Buch positiv überrascht: oft sind Einführungsbände zu neuen Oracle-Releases ja nicht viel mehr als behutsam umformulierte Kompilationen aus der Dokumentation - aber das trifft auf dieses Buch nicht zu.

Die knapp 800 Seiten des Handbuchs gliedern sich in fünf größere Kapitel:

- Installation, Architektur und Infrastruktur
- Einführung in die Oracle-Datenbankadministration
- Erweiterte Datenbankadministration
- Data Warehouse
- Hochverfügbarkeit

Wie die Überschriften bereits andeuten, beginnt die Darstellung mit allgemeinen und grundlegenden Erläuterungen (Wie installiert man Oracle? Wie interagieren die Komponenten des Servers miteinander? Wie funktionieren Backup + Recovery?) und schreitet dann zu komplexeren Themen fort (Performanceoptimierung, Massendatenverarbeitung im DWH, RAC etc.). Dabei liefern die einführenden Kapitel eine knappe, aber präzise Übersicht über die grundsätzliche Architektur des Oracle Servers, während die späteren Kapitel fundierte Einführungen zu den jeweiligen Spezialthemen geben. Gut gefallen hat mir die didaktische Gestaltung der einzelnen Kapitel, in denen die grundsätzlichen Erklärungen jeweils in einem ausgewogenen Verhältnis zu den praktischen Beispielen stehen, wobei in der Regel sowohl der Kommandozeilen-Zugriff über sqlplus als auch der Zugriff über die Web-GUI dargestellt werden. Die verwendeten Beispiele sind jeweils auf das Wesentliche beschränkt, so dass man eine kompakte Zusammenfassung der erforderlichen Schritte erhält. Neben dem grundsätzlichen Vorgehen wird dann auch dargelegt, wie die entsprechenden Operationen in den Views des data dictionary überwacht werden können. Darüber hinaus werden immer wieder Hinweise auf die praktische Relevanz der vorgestellten Techniken gegeben (und gefallen hat mir daran auch, dass diese Hinweise weitgehend meinen eigenen Erfahrungen entsprechen).

Neben Einführungen zu bestimmten neueren Features des 11er Releases, die ich mir noch nicht genauer angesehen hatte, und Erläuterungen zu Optionen, die ich noch nie praktisch verwendet habe (z.B. Stream), sind mir auch ein paar sehr nette Tricks begegnet, die ich noch nicht kannte, so z.B. die (bereits in meinem letzten Blog-Eintrag angesprochene) Möglichkeit des spool-Exports nach Excel (Kapitel 7.2). Andererseits scheint mir der Band aber auch für Einsteiger nicht ungeeignet, da die Darstellung nie auf erforderliche grundsätzliche Erläuterungen verzichtet. Zu den Vorzügen des Textes zählt der klare und präzise Stil des Autors, der dafür sorgt, dass die Lektüre an keiner Stelle mühsam wird. Offensichtliche Fehler habe ich im Band nicht gefunden (außer den falsch zugeordneten Screenshots auf S.56) - insgesamt aus meiner Sicht also eine sehr empfehlenswerte Lektüre.

Export von Query-Results aus sqlplus

in Lutz Fröhlichs Oracle 11-Buch habe ich folgende hübsche Möglichkeit gefunden, Query-Ergebnisse nach Excel zu exportieren:

1. in sqlplus: set markup html on
2. spool aktivieren (mit einer htm-Zieldatei)
3. Query ausführen
4. erzeugtes spool-file ggf. anpassen (man kann überflüssige Ausgabe natürlich auch über sqlplus-Einstellungen unterdrücken)
5. Dateiendung in xls ändern und mit Excel öffnen

Und, wenn ich schon dabei bin, hier auch noch mal der Klassiker zur Erzeugung von csv-Dateien:

1. in sqlplus: set colsep ";" (oder welches Trennzeichen auch immer)
2. spool aktivieren + Query ausführen

Oracle Survival Guide

nachdem mir der mitp-Verlag freundlicherweise zwei Oracle-Bücher zu Rezensionszwecken überlassen hat, gibt's in diesem Blog jetzt auch Buchbesprechungen. Die erste davon betrifft den Oracle Survival Guide von Cornel Brücher, Frank Jüdes, Mario Haupt, Uwe Küchler und Andreas Takano, erschienen 2008 - hier der zugehörige Link.

Der relativ schmale Band (knapp 300 Seiten) wendet sich an Einsteiger im Bereich der Programmierung mit Oracle-Datenbanken und bemüht sich, eine möglichst knappe Zusammenfassung relevanter Informationen zu liefern. Die Autoren sehen ihr Buch als "'Schweizer Messer' für Oracle-Entwickler und -Supporter" (was ein Oracle-Supporter ist, weiß ich nicht ganz genau, habe aber eine Theorie) und begründen ihre kompakte Darstellung damit, dass man aus den üblichen Einführungen zum Thema in der Regel höchstens 10% für das Tagesgeschäft benötige. Der Band beginnt mit einer Einführung zum Thema SQL (25 Seiten), stellt PL/SQL vor (20 Seiten), erläutert (sehr knapp) Datentypen in SQL und PL/SQL und sql*plus als Client-Tool, beschreibt die Tools SQL*Loader, exp und imp, gibt ein paar Informationen zum data dictionary und Hinweise zu Installation und sql-net-Zugriff. Dann folgt ein recht umfangreiches Kapitel zum DB-Zugriff über diverse Programmiersprachen (von C++ über Java, C#, Perl, Php bis zu Ruby - insgesamt ca. 70 Seiten). Die folgenden Kapitel beschäftigen sich mit Oracle-LDAP, mit UNIX und dem Vi-Editor und mit XML in der Datenbank. Den Abschluss bildet ein Kapitel mit Tipps und Tricks, in dem weitere Hinweise zur Arbeit mit SQL und PL/SQL enthalten sind.

Beim Zusammenfassen der Kapitelliste ist mir noch mal zu Bewusstsein gekommen, dass das eine sehr bunte Mischung ist, und dass ich der Gliederung nicht in allen Punkten folgen konnte. Manche Kapitel sind auch so knapp geworden, dass ich mich frage, ob da ein Kapitel überhaupt noch sinnvoll ist - das zu sql*plus umfasst z.B. 1,5 Seiten. Neben der Quantität scheint mir auch die Qualität der Einzelkapitel nicht einheitlich zu sein. Nun gehöre ich wahrscheinlich nicht genau in die Zielgruppe hinein, da es nicht das erste Buch zum Thema ist, das ich lese. Außerdem habe ich in den letzten Jahren nur noch Darstellungen von Autoren wie Tom Kyte, Cary Millsap und Jonathan Lewis gelesen, die einen sehr hohen Anspruch an ihre Bücher stellen, so dass meine Anforderungen an ein Buch zu Oracle recht hoch sind. Unter diesen Vorbedingungen muss ich sagen, dass mir die Lektüre des Bändchens nicht viel gebracht hat. Ein erstes Problem ist, dass an vielen Stellen der Hinweis fehlt, für welches Release des Oracle-Servers Aussagen gemacht werden (so etwa wenn im SQL-Kapitel die Funktion SYS_CONNECT_BY_PATH angesprochen wird, die meiner Erinnerung nach erst in 10g eingeführt wurde). Darüber hinaus sind viele Aussagen so knapp zusammengefasst, dass wichtige Informationen unter den Tisch fallen (z.B. bei der Behandlung von NULL-Werten im SQL-Kapitel). Einige Aussagen sind auch unabhängig von Verkürzungen sehr zweifelhaft (etwa wenn auf S. 36 der Vorschlag gemacht wird, ein Commit für jeweils 10.000 Sätze in einer PL/SQL-Schleife unterzubringen, um das Volllaufen von Rollback-Segmenten zu verhindern - bei AskTom kann man zu diesem Vorschlag einige unfreundliche Kommentare lesen). Bei der Vorstellung der Oracle-Utilities habe ich mich gewundert, dass exp und imp statt der aktuelleren Werkzeuge expdp und impdp vorgestellt wurden und dass der Abschnitt zum sql*loader so groß ausfiel, während die External Tables relativ kurz behandelt wurden. Ob man mit den Installations-Hinweisen des Buches viel anfangen kann, weiß ich auch nicht - da würde ich mich wahrscheinlich eher auf eines der umfassenden (allerdings englischsprachigen) Tutorials verlassen, die man im Netz finden kann (z.B. bei Tim Hall auf der OracleBase-Seite). Die ausführliche Darstellung der Programmierschnittstellen zum Oracle-Server stellt vermutlich das Kernstück des Bandes dar - und ist ein Thema, das mich (einen dritt- oder viertklassigen Programmierer) nur am Rande interessiert. Dieses Kapitel könnte jemandem, der erste Versuche unternimmt, mit einer Oracle-Datenbank zu kommunizieren, weiterhelfen.

Davon abgesehen enthält das Buch viele Angaben, die ohne den Kontext nur schwer einzuordnen sind. In diversen Datenbank-Foren begegnet man immer wieder Fragen, die zu den hier gegebenen Antworten passen, Fragen die eine schnelle und kurze Antwort suchen, bei denen aber oft recht schnell klar wird, dass der behandelte Sachverhalt eigentlich viel komplexer ist, als der Fragesteller annimmt, so dass die kurze Antwort ihm mittelfristig wahrscheinlich große Probleme bereitet - die vermeidbar wären, wenn er sich erst einmal mit den Basics des Themas befasst hätte. Das soll jetzt nicht heißen, dass kompakte Darstellung grundsätzlich problematisch wären, aber ab einem bestimmtem Grad der Komprimierung beginnen die Gefahren. Wenn ein Buch dann nicht ausreichend klar strukturiert ist, leidet der praktische Nutzen darunter.

Zu den positiven Aspekten des Bandes zählt immerhin noch, dass bei der Erstellung des Buches "keine Gürteltiere zu Schaden gekommen sind", und auch sonst merkt man, dass die Autoren wohl ihren Spaß an der Produktion des Buchs hatten. Nun bin ich aber eigentlich gar kein Datenbankspezialist, sondern Literaturwissenschaftler und in dieser Eigenschaft habe ich dann doch auch ein wenig unter diversen stilistischen Mängeln gelitten, aber das ist eine andere Geschichte, die ein andermal erzählt werden soll (oder auch nicht).

star transformation

wieder mal ein Thema, über das ich schon mehrfach gelesen habe, ohne auf die Idee zu kommen, mir die Details zu merken. Da der Herr Rittman hier auch den Verweis auf Jonathan Lewis' ältere Erläuterung der star transformation eingebaut hat, sollte ich alle relevanten Informatonen wieder finden können.