Mittwoch, September 30, 2015

Master Notes auf MOS

Jonathan Lewis hat in seinem Blog eine Liste mit Master Notes erstellt, die als Dokumente in der "Knowledge Base" des Oracle-Supports (MOS, die Älteren kennen es noch als Metalink) zu finden sind. Dabei handelt es sich um konzeptionelle Erläuterungen zu zentralen Mechanismen der Datenbank - enthalten sind solche Themen wie "Overview of Data Dictionary (1500058.1)", "Privileges And Roles (1347470.1)", "Overview of Oracle Background Processes (1503146.1)" etc. Dazu gibt er noch den folgenden Tipp:
If you search MoS with either of the expressions “overview of” or “Master Note” and a word describing the feature you’re researching then there’s a fair chance that you’ll find a document that catalogues a number of articles about the topic you’re interested in.
Zum besseren Verständnis zentraler Systemkomponenten sind diese Master Notes sicher ziemlich nützlich.

Freitag, September 25, 2015

Wissenswertes zum Umgang mit ORM-Tools

Sigrid Keydana hat für das diesjährige Trivadis TechEvent eine sehr schöne Präsentation zu den Möglichkeiten und Schwierigkeiten des Einsatzes von Object Relational Mapping (ORM) Tools erstellt, in der sie unter anderem erklärt:
  • warum Entwickler überhaupt auf die Idee kommen, dergleichen einzusetzen.
  • wo die Schwierigkeiten bei der Abbildung von Klassen auf Datenbankobjekte liegen ("Object-Relational Impedance Mismatch").
  • welche Vorgehensweisen beim data fetching zur Verfügung stehen und worauf man dabei zu achten hat (Lazy vs. Eager Fetch, das "n+1 SELECTs" Problem, Prefetching etc.).
Die Präsentation enthält neben ein paar Links zu grundlegenden Artikeln, konzeptionelle Erläuterungen und praktische Beispiele und fasst das Thema aus meiner Sicht sehr nachvollziehbar zusammen.

Insbesondere unter DBAs gelten ORM-Tools ja bisweilen als Teufelswerk, aber- so banal das auch klingt - hier gilt mal wieder: für jedes Werkzeug gibt es den geeigneten Anwendungsbereich und die geeignete Verwendung. Zumindest habe ich in der Vergangenheit auch Anwendungen gesehen, die über Hibernate völlig plausibles SQL erzeugten; neben solchen, denen das nicht so ganz uneingeschränkt gelang... Dass die Generierung von SQL-Code anhand von Regelwerken kein triviales Problem ist, hat man allerdings auch schon bei Tools wie dem Oracle Warehouse Builder (OWB) gesehen, dessen generierter Code auch manchmal suboptimal ausgefallen ist.

    Samstag, September 19, 2015

    Überflüssige Indizes anhand von Statistiken erkennen

    Bereits vor einigen Wochen hat Jonathan Lewis einen Beitrag veröffentlicht, in dem er zeigt, wie man anhand der Statistiken in v$segstat (bzw. v$segment_statistics) bestimmen kann, ob die Lesezugriffe für einen Index nur für dessen Maintenance erforderlich, oder ob auch Zugriffe über den Index erfolgt sind. Ganz grob gilt dabei, dass ein Index mit blevel = 2 dann vermutlich nicht für Zugriffe verwendet wird, wenn die Anzahl der db block changes etwa ein Drittel der logical reads beträgt. Natürlich weist der Herr Lewis explizit darauf hin, dass sich auf der Basis dieser Beobachtung keine exakten Aussagen treffen lassen - man kann nur Kandidaten ausfindig machen, die dann genauer zu untersuchen wären. Außerdem werden die Zahlen massiv fragwürdig, wenn ein Commit im Test ergänzt wird: aber dadurch ergeben sich keine false positives in der Richtung, dass ein für Zugriffe verwendeter Index als Lösch-Kandidat vorgeschlagen werden würde. Insgesamt ist das Verfahren insofern cum grano salis zu nehmen, aber trotzdem ein interessanter Ansatz, der insbesondere in unübersichtlichen Landschaften mit vielen "historisch begründeten" Indizes einen Einstieg liefern kann. Natürlich umfasst der Artikel ein umfangreiches Beispiel, das ich hier (wie üblich) nicht wiedergebe.

    Freitag, September 18, 2015

    Index Advanced Compression und mehrspaltige Indizes

    Vor einem knappen Jahr hatte Richard Foote in seinem Blog mehrere Artikel zum Thema Index Advanced Compression veröffentlicht ("Advanced Index Compression" hätte mir besser gefallen). Jetzt hat der Autor offenbar eine neue Artikelserie begonnen, die sich mit der Wirkung des Features für mehrspaltige Indizes beschäftigt. Damit ich es nicht später vergesse, hier noch mal der explizite Hinweis, dass die Index Advanced Compression eine Lizenzierung der Advanced Compression Option erfordert. Wie üblich werde ich versuchen, alle Folgeartikel zu erfassen, kann aber nichts versprechen:
    • Index Advanced Compression: Multi-Column Index Part I (There There): wie üblich gibt es ein Beispiel und in diesem Beispiel wird eine Tabelle angelegt, deren erste Spalte unique ist, während die zweite nur 10 distinkte Werte enthält und die dritte immer den gleichen (erstaunlicherweise den String "David Bowie"; ich frage mich, wie der Herr Foote seine Passwörter auswählt). Dazu wird ein Index angelegt, dessen führendes Attribut die zweite Tabellen-Spalte ist (also die mit 10 unterschiedlichen Werten), der dann die eindeutige Id als zweite Spalte folgt. Zunächst wird gezeigt, dass eine Compression über beide Spalten den Index größer werden lässt, als er es ohne Komprimierung wäre. Wird die Compression auf die erste Spalte beschränkt, so ergibt sich eine Größenreduzierung für den Index. Diese Entscheidung für den passenden Komprimierungsgrad wird durch die Index Advanced Compression hinfällig, da dabei die Bestimmung des optimalen Verfahrens automatisch erfolgt. In einem zweiten Schritt wird das Beispiel dadurch modifiziert, dass in der eindeutigen Id-Spalte für einen größeren Bereich (10%) die Werte auf einen einheitlichen Wert gesetzt werden. Mit der manuell gesetzten Compression über eine bzw. zwei Spalten ergeben sich ähnliche Werte wie für den initialen Fall. Durch die Index Advanced Compression ist für diesen Fall aber eine Verbesserung möglich, da das Feature dafür sorgt, dass die dafür geeigneten Abschnitte des Index über beide Spalten komprimiert werden, während für den Rest der Struktur nur die erste Spalte komprimiert (soll heißen: dedupliziert) wird. Also zur Abwechslung mal eine automatische Option bei Oracle, die eine deutliche Verbesserung gegenüber der manuellen Vorgehensweise darstellt (das klingt jetzt vielleicht abfälliger, als es gemeint ist, aber ich hatte zuletzt mit AMM zu tun).
    • Index Advanced Compression: Multi-Column Index Part II (Blow Out): weist darauf hin, dass die Spaltenreihenfolge auch mit der Index Advanced Compression relevant bleibt: eine führende Spalte mit wenigen Wiederholungen ist weiterhin kein geeigneter Kandidat für Komprimierung. Gut komprimierbar ist mit dem neuen Verfahren hingegen ein Index, bei dem in einem bestimmten Bereich viele Wiederholungen für die führende Spalte erscheinen, während in der Regel kaum Wiederholungen auftreten. In diesem Fall kann Oracle den gut komprimierbaren Bereich komprimieren und den Rest des Index unkomprimiert lassen.
    Wie gesagt: ich versuche den oder die Folgeartikel zu ergänzen.

    Freitag, September 11, 2015

    Lokale und globale Hints

    Es gibt Blog-Einträge, deren Inhalt man in einem Satz zusammenfassen kann. Bei den Artikeln von Stefan Koehler in seinem SAP on Oracle Blog ist das in der Regel nicht der Fall, was vermutlich einer der Gründe dafür ist, dass ich mir mit dem Exzerpieren seiner Beiträge in der Regel relastiv viel Zeit lasse - manchmal so lange, dass sie rechtzeitig wieder aus meinem Blog-Reader verschwunden sind, ehe ich sie berücksichtige. Vor drei Wochen erschien der Artikel Insights into SQL hints - Embedded global and local hints and how to use them, der zunächst auf grundlegende Artikel (einen eigenen und einen von Jonathan Lewis) verweist und die Verwendung von Mauro Paganos SQLd360 empfieht. Nach dem Hinweis, dass Hints nur in Ausnahmefällen als "last resort" verwendet werden sollten (dem ich vorbehaltlos zustimme) bildet die Erläuterung, was denn eigentlich ein "embedded hint" ist, den eigentlichen Eigenstieg in den Artikel: "embedded hints" sind solche, die in SQL Statements verwendet werden - und insofern zu unterscheiden von "outline hints", die in outlines und baselines verwendet werden. Ich schenke mir diese terminologische Genauigkeit und rede in der Folge nur noch von Hints.

    Die nächste definitorische Aussage unterscheidet zwischen globalen und lokalen Hints:
    • local hint: gelten für den query block, in dem sie erscheinen. Es folgen Informationen zu ihrem Auftreten im SAP-Kontext, aber SAP ist nicht mein Thema.
    • global hint: werden mit einem vorangestellten @-Symbol vor der Angabe des query-Blocks, auf den sie sich beziehen, ausgezeichnet (und die Hints in outlines sind immer globale Hints): sie erscheinen also an einer zentralen Stelle (zu Beginn der Query) und sind nicht über den Text der Query verteilt. Sie sind insbesondere bei der Definition von Queries, die auf Views zugreifen, relevant, da einer Ergänzung der Hints im View-Text in der Regel keine Option ist.
    Es folgt ein umfangreiches (SAP-)Beispiel, das zeigt, dass ein FULL-Hint für eine Query mit View-Zugriff kalt lächelnd ignoriert wird, weil er aufgrund einer Transformation gegenstandslos wird: der Query-Block, dem der Hint zugeordnet ist, wird im Rahmen der Transformation in einen anderen Query-Block gemerget. Um den Hint wirksam werden zu lassen, muss man ihn entweder in der View ergänzen oder als globalen Hint für einen Query-Block, der die Transformation übersteht (bzw. durch sie erzeugt wird).

    War gar nicht so umfangreich, wie mir jetzt auffällt - aber wie üblich hat der Herr Koehler seine Ausführung mustergültig mit Belegen und Detail-Erläuterungen versehen.

    Samstag, September 05, 2015

    Vereinfachter csv-Import mit SQL Loader Express in 12c

    Ich will nicht behaupten, dass es mich tief erschüttert hätte, dass Tom Kyte vor einigen Tagen eine Auszeit angekündigt und seine AskTom-Seite an Chris Saxon und Connor McDonald übergeben hat - aber völlig unberührt gelassen, hat es mich jedenfalls nicht. Meine Beschäftigung mit Oracle-Datenbanken hat im Jahr 2000 begonnen und AskTom war gerade in den ersten Jahren der Ort, an dem ich nach Erklärungen suchte für alle Verhaltensweisen des RDBMS, die ich nicht verstanden hatte - und das waren zunächst ziemlich viele. Die Bücher, die der Herr Kyte im Lauf der Jahre veröffentlicht hat, stehen immer in Griffweite im Regal und obwohl ich AskTom in den letzten Jahren nur noch selten besucht habe - unter anderem, weil die interessanten Threads irgendwann unendlich lang wurden - bedauere ich Toms Abschied; und freue mich andererseits darüber, dass er plant, weitere Bücher zu schreiben, und dass er so kompetente Nachfolger für die Weiterführung von AskTom gefunden hat.

    Was mich zum eigentlichen Thema bringt: in seinem eigenen Blog (den ich auch seit vielen Jahren verfolge) hat Connor McDonald (von dem hier auch noch irgendwo ein Buch steht) vor kurzem einen Artikel zu einem interessanten neuen Feature in 12c veröffentlicht: dem SQL Loader Express. Dieser leistet etwas ganz Simples: er erlaubt ein vereinfachtes Einlesen von csv-Dateien - ein Feature, das mir bei Oracle seit vielen Jahren gefehlt hat, weil dieses Einlesen in anderen RDBMS schon seit langer Zeit sehr viel einfacher durchzuführen war. Dazu ein minimales Beispiel (das noch deutlich minimaler ist als das des Herrn McDonald und die entsprechenden Erläuterungen des von ihm verlinkten White Paper):

    -- csv-Datei emp.txt mit folgenden Daten
    7902,FORD,ANALYST
    7844,TURNER,SALESMAN
    7788,SCOTT,ANALYST
    7654,MARTIN,SALESMAN
    7566,JONES,MANAGER
    7369,SMITH,CLERK
    7876,ADAMS,CLERK
    7900,JAMES,CLERK
    7698,BLAKE,MANAGER
    7782,CLARK,MANAGER
    7521,WARD,SALESMAN
    7934,MILLER,CLERK
    7499,ALLEN,SALESMAN
    7839,KING,PRESIDENT
    
    -- Anlage einer entsprechenden Tabelle emp im Schema test
     create table emp(
      empno    number(4,0),
      ename    varchar2(10),
      job      varchar2(9)
    );
    
    -- Aufruf des sqlldr im Verzeichnis, in dem sich die csv-Datei befindet
    sqlldr userid=test/test data=emp.txt table=emp
    
    SQL*Loader: Release 12.1.0.2.0 - Production on Fri Oct 3 00:57:18 2014
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    Express Mode Load, Table: EMP
    Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
    
    Table EMP:
      14 Rows successfully loaded.
    
    Check the log files:
      emp.log
      emp_%p.log_xt
    for more information about the load.
    

    Das zugehörige Log zeigt sehr detailliert, was im Hintergrund geschieht:

    Express Mode Load, Table: EMP
    Data File:      emp.txt
      Bad File:     emp_%p.bad
      Discard File:  none specified
    
     (Allow all discards)
    
    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation:    none specified
    Path used:      External Table
    
    Table EMP, loaded from every logical record.
    Insert option in effect for this table: APPEND
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    EMPNO                               FIRST     *   ,       CHARACTER
    ENAME                                NEXT     *   ,       CHARACTER
    JOB                                  NEXT     *   ,       CHARACTER
    
    Generated control file for possible reuse:
    OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
    LOAD DATA
    INFILE 'emp.txt'
    APPEND
    INTO TABLE EMP
    FIELDS TERMINATED BY ","
    (
      EMPNO,
      ENAME,
      JOB
    )
    End of generated control file for possible reuse.
    
    created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle
    
    enable parallel DML: ALTER SESSION ENABLE PARALLEL DML
    
    creating external table "SYS_SQLLDR_X_EXT_EMP"
    
    CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
    (
      "EMPNO" NUMBER(4),
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9)
    )
    ORGANIZATION external
    (
      TYPE oracle_loader
      DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp_%p.bad'
        LOGFILE 'emp_%p.log_xt'
        READSIZE 1048576
        FIELDS TERMINATED BY "," LRTRIM
        REJECT ROWS WITH ALL NULL FIELDS
        (
          "EMPNO" CHAR(255),
          "ENAME" CHAR(255),
          "JOB" CHAR(255)
        )
      )
      location
      (
        'emp.txt'
      )
    )REJECT LIMIT UNLIMITED
    
    executing INSERT statement to load database table EMP
    
    INSERT /*+ append parallel(auto) */ INTO EMP
    (
      EMPNO,
      ENAME,
      JOB
    )
    SELECT
      "EMPNO",
      "ENAME",
      "JOB"
    FROM "SYS_SQLLDR_X_EXT_EMP"
    
    dropping external table "SYS_SQLLDR_X_EXT_EMP"
    
    Table EMP:
      14 Rows successfully loaded.
    

    Demnach umfasst die Operation folgende Schritte:
    • Generierung eines wiederverwendbaren sqlldr control files.
    • Erzeugung eines temporären directories für das Verzeichnis, in dem die csv-Datei liegt und in dem der sqlldr-Aufruf erfolgte.
    • Aktivierung von parallel dml für die Session.
    • Anlage einer external table basierend auf der Struktur der csv-Datei.
    • parallelisiertes Insert Append zur Übertragung der Daten der External Table in die Zieltabelle.
    • Drop der External Table.
    Das Verfahren erspart eine Menge Definitionsaufwand und kann als External Table Generator verwendet werden (wie David Aldridge in seinem Kommentar bemerkt).

    Nachtrag 14.09.2015: offenbar auch wieder ein Thema, das aktuell ein gewisses Interessae hervorruft - jedenfalls hat auch Oren Nakdimon ein schönes entsprechendes Beispiel veröffentlicht.

    Donnerstag, September 03, 2015

    Fehlende Foreign Keys mit dem SQL Developer ermitteln

    Regelmäßig erinnert mich Jeff Smith mit seinen Artikeln daran, dass es doch eine gute Idee wäre, endlich mal dazu überzugehen, den SQL Developer zu meinem Standardwerkzeug zu machen. In einem aktuellen Beitrag weist er beispielsweise darauf hin, dass der SQL Developer Data Modeller anhand von Spaltennamen und Datentypen dazu in der Lage ist, über eine Option "Discover Foreign Keys" Vorschläge zur Erzeugung fehlender FK-Constraints zu generieren. Das erfordert natürlich eine konsistente Namensgebung, so dass das Verfahren in vielen Datenmodellen, die mir im Laufe der Jahre begegnet sind, an seine Grenzen gekommen wäre. Aber ein nettes Feature ist das allemal.