Logo Oracle Deutschland Februar 2017
12.2: Ausgewählte Basisfeatures für SQL und PL/SQL Entwickler
Ulrike Schwinn

Das Programmieren von Datenbankanwendungen zu erleichtern, noch mehr Funktionalität zu bieten und dabei den Performanceansprüchen zu genügen, sind wichtige Ziele bei der Weiterentwicklung der Funktionen im Datenbankumfeld. Dies ist nicht nur in den Bereichen SQL und PL/SQL, sondern auch in anderen Bereichen wie Oracle Text, Spatial, XML oder auch bei JSON Daten zu beobachten. Performance und die Verarbeitung großer Datenmengen dürfen dabei nicht außer acht gelassen werden, sowie das Tunen und Monitoren von neuem und existierendem SQL und PL/SQL Code.

Nimmt man die Features für Entwickler von Oracle Database 12c Release 2 in Augenschein, so stellt man fest, dass beispielsweise eine Weiterentwicklung der in Release 1 eingeführten JSON Features ein wichtiges Thema ist. Neu sind die Möglichkeiten JSON Dokumente in materialisierten Views zu verwenden, zu partitionieren und/oder zusätzlich In-Memory zu speichern. Die Neuerung einen JSON "Dataguide" zu definieren, der die Struktur und den Inhalt der JSON Dokumente erfasst, eröffnet dabei ganz neue Möglichkeiten. Eine weitere interessante Neuigkeit im SQL Umfeld ist die Möglichkeit Case Insensitivität by Default zu verwenden. So besteht die Möglichkeit eine Sortierreihenfolge (auch engl. collation) für Spalten beim CREATE TABLE festzulegen - ähnlich wie bei der Festlegung auf einen Datentyp. Auf diese Weise können Spalten einer Applikation als case-insensitiv gelten, ohne dass ein Datenbank Entwickler explizit Funktionen wie UPPER oder LOWER hinzufügen muss. Weiteres dazu werden wir in separaten Blogeinträgen behandeln.

In folgenden Abschnitten möchte ich mich zuerst auf einige interessante ausgewählte Basisfeatures konzentrieren und diese an Code Beispielen illustrieren.

SQL*Plus und 12.2 Neuigkeiten


Starten wir mit dem traditionellem Werkzeug SQL*Plus. Auch hier gibt es einige Erweiterungen zum Beispiel bei den Performance Einstellungen, der Verwendbarkeit oder auch im Umfeld der Funktionalität. Ein Blick auf die speziellen SQL*Plus Umgebungseinstellungen lässt erkennen, dass dazu einige neue Parameter und Optionen eingeführt worden sind. So kennt SQL*Plus jetzt endlich eine history Funktion. Damit lässt sich die Kommandohistorie anzeigen und Kommandos aus der Historie editieren und löschen. Wie immer kann man sich eine detaillierte Funktionsbeschreibung mit help angeben lassen.

Zur Performancesteigerung gibt es jetzt die neue Option -F, mit der SQL*Plus in einem speziellen Modus mit speziellen Setupeinstellungen gestartet wird. Damit werden beispielsweise verschiedene Caches aktiviert wie LOB Prefetch (LOBPREFETCH 16384), Zeilen Prefetch (ROWPREFETCH 2) und ein Statementcaching (STATEMENTCACHE 20). Diese Werte können natürlich auch unabhängig in der SQL*Plus Session gesetzt bzw. verändert werden.

Zudem ist eine weitere Formatierungsmöglichkeit implementiert worden - nämlich die CSV Formatierung. Einfach SQL*Plus mit -M "CSV ON" starten und schon werden die Ausgaben in CSV mit einem Komma Delimiter angezeigt. Die Einstellung kann natürlich auch innerhalb von SQL*Plus vorgenommen werden. In folgendem Beispiel soll der Delimiter ein Semikolon sein.
SQL> set markup CSV on delimiter ";"
SQL> select * from dept;

"DEPTNO";"DNAME";"LOC"
10;"ACCOUNTING";"NEW YORK"
20;"RESEARCH";"DALLAS"
30;"SALES";"CHICAGO"
40;"OPERATIONS";"BOSTON"
Möchte man mehr über SQL*Plus Einstellungen und Optionen erfahren, eignet sich die Lektüre im ( SQL*Plus User's Guide and Reference).

Wichtiger Hinweis für alle SQL*Plus Anwender: Falls Sie die Datei login.sql im lokalen Verzeichnis verwenden, müssen Sie ab jetzt umdenken. Aus Sicherheitsgründen wird die login.sql im lokalen Verzeichnis nicht mehr berücksichtigt. Die login.sql wird nur noch aus den Pfadverzeichnissen ORACLE_PATH Linux bzw. SQLPATH (Windows) gelesen.

SQL in 12.2: Erweiterungen der Basisfunktionen


Eine wichtige Neuigkeit für Entwickler und DBAs vorab: Die maximale Länge von Identifier ist von 30 auf 128 Bytes erhöht worden. Das bedeutet, dass nun Namen für Tabellen, Objekte, Prozeduren, Variablen usw. länger sein können. Das Limit ist natürlich additiv: Bei Verwendung von "schema"."table"."column" (inklusive Punkte und Hochkommata) ist somit eine Grenze von 392 Bytes möglich. Übrigens falls man die Länge überprüfen will, kann man die neue Funktion ORA_MAX_NAME_LEN_SUPPORTED verwenden.
SQL> set serveroutput on
SQL> var version varchar2(20);
SQL> var compatibility varchar2(20);
SQL> exec DBMS_UTILITY.DB_VERSION(:version,:compatibility);

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.PUT_LINE(:version) ;
12.2.0.1.0

SQL> exec DBMS_OUTPUT.PUT_LINE(DBMS_standard.ORA_MAX_NAME_LEN_SUPPORTED);
128
Mehr Informationen zu weiteren Limitierungen in der PL/SQL Programmierung kann man im PL/SQL Language Reference Guide: PL/SQL Program Limits finden.

Ein weiteres Ziel bei der Entwicklung von Features ist die Möglichkeit mehr Kontrolle im Fehlerfall zu haben. Speziell bei den Konvertierungsfunktionen wie TO_NUMBER, TO_DATE, TO_TIMESTAMP usw. kann nun der Fehler vermieden und stattdessen ein Defaultwert ausgegeben werden. Ein einfaches Beispiel liefert die TO_NUMBER Funktion: Wird als Eingabeparameter eine DATE Spalte mitgegeben, erhält man vor Release 12.2 den Fehler ORA-01722: invalid number. Ab 12.2 kann man diese Ausgabe mit einem Defaultwert umgehen wie folgendes Beispiel zeigt.
SQL> select to_number(hiredate default 42 on conversion error) ausgabe from emp where rownum=1;
 
   AUSGABE
----------
        42
Mit der neuen Funktion VALIDATE_CONVERSION ist es darüberhinaus möglich zu überprüfen, ob ein Ausdruck in einen speziellen Datentyp konvertiert werden kann. Der Wert 1 bedeutet, dass eine erfolgreiche Konvertierung möglich ist, 0 hingegen dass die Konvertierung fehlschlagen wird. Mögliche Datentypen sind dabei die numerischen und die Datums Datentypen wie DATE, NUMBER, TIMESTAMP, BINARY_DOUBLE, INTERVAL YEAR TO MONTH usw. Folgendes Beispiel demonstriert die einfache Verwendung: Im ersten Beispiel wird der Wert 1000 überprüft. Die Ausgabe ist 0, da 1000 kein Datum darstellt.
SQL> select validate_conversion('1000' as date ) ergebnis from dual;

  ERGEBNIS
----------
         0

Unter der Verwendung einer Formatmaske ergibt die Abfrage auf DATE allerdings Sinn, so dass wir den Wert 1 erhalten - eine Konvertierung ist möglich.
SQL> select validate_conversion('1996' as date ,'YYYY') ergebnis from dual;

  ERGEBNIS
----------
         1
Einige weitere interessante Funktionserweiterungen finden sich im analytischen Windowfunktionen Umfeld. Die Funktion LISTAGG, die eine Stringaggregation vornimmt, bietet nun eine Ausgabemöglichkeit bei Überschreitung der maximalen Ausgabelänge an. Statt eines ORA-01489 Fehlers kann nun eine Konstante ausgegeben werden. Dies kann auch mit einem Zähler kombiniert werden, der die Anzahl der überzähligen Zeichen ausgibt. In folgendem Beispiel ist das Trennzeichen ein Semikolon (;), die Zeichenkette, die die Überschreitung der Grenze angibt die Konstante '...' und die Anzahl der überzähligen Zeichen 3631. Leider ist es dabei nicht möglich die überschüssigen Zeichen auszugeben oder zu speichern.
SQL> set pagesize 100000 

SQL> select listagg(s, ';' on overflow truncate '...' with count)
     within group (order by r) ausgabe 
     from (select rownum r , 'x' s from dual connect by level <20000);

AUSGABE
--------------------------------------------------------------------------------
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;


x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;
x;x;x;x;x;x;x;x;...(3631)
Hinweis: Wenn Ihnen das Limit für die Grenzüberschreitung hier ungewohnt hoch vorkommen sollte: Das liegt am Parameter MAX_STRING_SIZE, der in unserer Umgebung auf EXTENDED steht.

Mit Oracle Database 12c Release 1 ist die Windowfunktion APPROX_COUNT_DISTINCT eingeführt worden. Die Idee dabei war, die Ausführung von COUNT (DISTINCT) zur Berechnung der Anzahl von verschiedenen Spalten-Ausprägungen zu beschleunigen. APPROX_COUNT_DISTINCT führt ein Sampling durch um die ungefähre Anzahl der verschiedenen Einträge einer Spalte zu berechnen und ist in der Regel um eine Vielfaches schneller als COUNT (DISTINCT). Der Wermutstropfen dabei war, dass man dazu die Funktionen im eigenen Code austauschen musste. Mit Oracle Database 12c Release 2 sind daher zusätzliche Sessionparameter eingeführt worden. Beispielsweise führt das einfache Setzen des Sessionparameters APPROX_FOR_COUNT_DISTINCT auf TRUE im Hintergrund zu einem APPROX_COUNT_DISTINCT ohne Änderung am Code. In dem Zusammenhang sind noch weitere APPROX Funktionen eingeführt worden wie zum Beispiel APPROX_COUNT_DISTINCT_AGG, APPROX_MEDIAN, APPROX_PERCENTILE usw.

PL/SQL in 12.2: SQL im PL/SQL Programmen


Gerade beim Tunen und Analysieren von PL/SQL Code stellt man sich auch häufig die Frage, ob und welche SQL Statements im PL/SQL Code verwendet wurden. Vor 12.2 war die Lösung dieser Fragestellung eher ein umständliches Unterfangen oder gar ein voneinander getrennter Vorgang. Jetzt in 12.1 ist es einfach und intuitiv möglich SQL Statements in den PL/SQL Programmen aufzuspüren.

Starten wir mit dem Hierachical Profiler, der immer noch eines der wichtigsten Werkzeuge darstellt, um Bottlenecks zu finden und Performance-Tuning in PL/SQL durchzuführen. Das Setup und die Handhabung haben sich nicht verändert. Es ist immer noch ein EXECUTE Recht auf DBMS_HPROF, ein Schreibrecht auf ein Directory und das Starten und Stoppen über DBMS_HPROF erforderlich. Führt man die Analyse mit dem Linemode Werkzeug plshprof in 12.2 durch, stellt man fest, dass DBMS_HPROF nun auch die entsprechenden SQL ID und den SQL Text mitliefert. Vor 12.2 wurden nur die Zeit aufgelistet, die ein SQL Statement verbraucht hat, aber keine Information zum SQL Code selbst mitgeführt. Jetzt wird die SQL ID und das Statement (SQL Text) sichtbar gemacht. So ist es einfach möglich den zugehörigen SQL Code zu analysieren. Folgender Screenshot zeigt ein Beispiel aus dem plshprof Report.

Eine ähnliche Idee steckt auch hinter der Erweiterung von PL/Scope. Worum handelt es sich noch einmal bei PL/Scope? Mit PL/Scope können Informationen über die Verwendung von Identifier während der Compile Zeit gesammelt und automatisch in Data Dictionary Tabellen gespeichert werden. Aufgezeichnet werden Typen, Namen, Nutzung und Vorkommen der Identfier im PL/SQL Code. So ist eine einfache Analyse der verwendeten Identifier im Code möglich. Mit dem Parameter PLSCOPE_SETTINGS lässt sich dabei die Nutzung aktivieren und einstellen.In 12.2 lassen sich damit auch SQL Statements mitloggen. Dazu wurden neue Werte wie 'IDENTIFIERS:SQL, STATEMENTS:ALL', 'IDENTIFIERS:ALL, STATEMENTS:ALL','IDENTIFIERS:PLSQL, STATEMENTS:NONE' eingeführt. Die neue View USER_STATEMENTS listet dabei die SQL Statements auf. Jede Zeile gibt dabei ein SQL Vorkommen im PL/SQL Code aus. Folgendes Beispiel zeigt eine Verwendung. In unserem Fall haben wir in der Session PLSCOPE_SETTINGS auf 'IDENTIFIERS:SQL, STATEMENTS:ALL' gesetzt. Überprüfen lässt sich die Einstellung über USER_PLSQL_OBJECT_SETTINGS.
SQL> alter session set PLSCOPE_SETTINGS='IDENTIFIERS:SQL, STATEMENTS:ALL';

SQL> select name, type, plscope_settings from user_plsql_object_settings;

NAME                           TYPE         PLSCOPE_SETTINGS
------------------------------ ------------ -----------------------------------
NT_STKNAMES                    TYPE         STATEMENTS: NONE
NT_VALUES                      TYPE         STATEMENTS: NONE
NT_VOLUME                      TYPE         STATEMENTS: NONE
P                              PROCEDURE    IDENTIFIERS:NONE
P1                             PROCEDURE    IDENTIFIERS:SQL, STATEMENTS:ALL
P2                             PROCEDURE    IDENTIFIERS:NONE
PACK1                          PACKAGE      IDENTIFIERS:NONE
PACK2                          PROCEDURE    IDENTIFIERS:NONE
Q                              PROCEDURE    IDENTIFIERS:NONE
RECIP                          FUNCTION     IDENTIFIERS:NONE
RECIP1                         FUNCTION     IDENTIFIERS:NONE
STOCK_TRACK_PACK               PACKAGE      IDENTIFIERS:SQL, STATEMENTS:ALL
STOCK_TRACK_PACK               PACKAGE BODY IDENTIFIERS:SQL, STATEMENTS:ALL

13 rows selected.
Nach der Kompilierung des Package STOCK_TRACK_PACK und der Prozedur P1, kann man jetzt nicht nur nicht nur die Informationen über die Identifier selbst selektieren sondern auch die SQL Statements (z.B. mit SQL_ID und SQL_TEXT) herausfiltern.
SQL> select name, object_name, object_type, usage, line from user_identifiers order by name;

NAME            OBJECT_NAME       OBJECT_TYPE     USAGE             LINE
--------------- ----------------- --------------- ----------- ----------
CLOSING_VALUE   STOCK_HISTORY     TABLE           DECLARATION          1
COMM            EMP               TABLE           DECLARATION          1
DEPTNO          EMP               TABLE           DECLARATION          1
EMP             P1                PROCEDURE       REFERENCE            7
EMP             EMP               TABLE           DECLARATION          1
EMPNO           P1                PROCEDURE       REFERENCE            8
EMPNO           EMP               TABLE           DECLARATION          1
Bei der Verwendung von PLSCOPE_SETTINGS mit dem Wert 'STATEMENTS:ALL', sammelt PL/Scope nicht nur SELECT und DML Statements sondern auch Statements wie EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE und FETCH. FETCH usw. So kann man beispielsweise leicht feststellen, welche Statements in welchem Package verwendet werden und diese Information beim Tuning berücksichtigen.
SQL> select object_name, object_type, line, col, sql_id, full_text
     from user_statements;

OBJECT_NAME               OBJECT_TYPE        LINE        COL SQL_ID
------------------------- ------------ ---------- ---------- -------------
FULL_TEXT
--------------------------------------------------------------------------------
STOCK_TRACK_PACK          PACKAGE BODY         46          3


STOCK_TRACK_PACK          PACKAGE BODY         23         10 6nwmx5nabvmrc
INSERT INTO STOCK_HISTORY VALUES (:B1 ,:B2 , :B3 , :B4 , :B5 ,NULL, :B6 )

P1                        PROCEDURE             5          5 2fuxfptxn10pk
SELECT ENAME FROM EMP WHERE EMPNO = :B1

Fazit

Die neuen Features vereinfachen die Programmierung und helfen beim Tuning von PL/SQL und SQL. Zum Erlernen kann man dafür das Standard Werkzeug SQL*Plus in Verbindung mit den neuen Möglichkeiten verwenden. Die Werkzeuge wie SQL Developer oder die neue Linemode Variante SQLcl (siehe auch unser Tipp unten), die per Download von OTN verfügbar sind, bieten darüberhinaus gerade beim Testen und Programmieren weitere hilfreiche Funktionen wie z.B. zum Generieren und Vergleichen von Ausführungsplänen.

Generell ist dabei immer wichtig: man sollte wissen, was man programmiert hat bzw. welche Auswirkungen der Code haben kann. Daher empfehle ich immer den eigenen Code zu überprüfen, nicht nur das Ergebnis sondern, wenn möglich, sich auch ein Bild von der Ausführungsperformance bzw. vom Ausführungsplan zu machen. Ähnliches gilt auch für PL/SQL. Um langsam laufenden PL/SQL Code zu monitoren bzw. zu tunen sollte man den Hierarchical Profiler verwenden. Dieser wie auch PL/Scope beinhalten ab 12.2 auch den SQL_TEXT und die SQL_ID, so dass das Monitoring nun umfassend möglich ist. Übrigens ob die Funktion in der Cloud oder in ihrer eigenen Umgebung (soweit verfügbar) getestet wird, spielt dabei keine Rolle: die Funktionen bleiben die gleichen.

Weitere Informationen

 

Zurück zum Anfang des Artikels

Zurück zur Community-Seite