Mittwoch, September 28, 2016

dbms_redefinition mit virtuellen Spalten

Nach längerer Zeit mal wieder etwas selbst Gebasteltes: im OTN Forum General Database Discussions wurde kürzlich ein Fall vorgestellt, in dem die Verschiebung einer Tabelle in einen anderen Tablespace mit Hilfe von dbms_redefinition.redef_table in einen Fehler führt, weil der vereinfachte automatisierte Prozess versucht, eine virtuelle Spalte durch ein Insert zu befüllen. Dazu ein Beispiel, das den OTN Fall noch mal reduziert:

drop table t;
drop table t_int;

create table t (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace users
;

alter table t add constraint t_pk primary key (id);

insert into t (id, col_1, col_2)
values (1, 42, null);

commit;

select * from t;

        ID      COL_1      COL_2 COL_VIRTUAL
---------- ---------- ---------- -----------
         1         42                     42

select table_name, tablespace_name from user_tables where table_name = 'T';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS


exec dbms_redefinition.redef_table (uname=>user, tname=>'t', table_part_tablespace=> 'EXAMPLE');  
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12018: following error encountered during code generation for "C##TEST"."REDEF$_T92713"
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 1

Zur Erinnerung: rdbms_redefinition überführt die Daten der Originaltabelle in eine Interim-Tabelle und tauscht die beiden Objekte intern gegeneinander aus. Änderungen (also DML-Operationen), die sich nach dem Start der Reorganisation in der Quelltabelle ergeben, werden intern vermerkt und in der Zieltabelle vor Abschluss der Umstellung nachgezogen, so dass die Operation komplett online ablaufen kann. Die Prozedur redef_table ist dabei eine vereinfachte Variante, sie bietet ein:
single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.
Im gegebenen Fall übersieht die Prozedur aber offenbar, dass man eine virtuelle Spalte nicht einfach mit einem Insert füllen kann, sondern ausklammern muss. Um das zu erreichen, kann man die Einzelschritte, die in redef_table zusammengefasst sind, einzeln aufrufen:

create table t_int (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace example
;

declare
    l_colmap varchar(512);
begin
    l_colmap := 'id, col_1, col_2';
    dbms_redefinition.start_redef_table (  
        uname => user
      , orig_table => 't'
      , int_table => 't_int'
      , col_mapping => l_colmap );

end;
/

exec dbms_redefinition.finish_redef_table ( user, 't', 't_int' );

select table_name, tablespace_name from user_tables where table_name = 'T';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              EXAMPLE

Im Rahmen der Prozedur start_redef_table kann man die interim Tabelle t_int explizit angeben (nachdem man sie zuvor passend definiert hat) und über ein col_mapping die Liste der Spalten aufführen, die übertragen werden sollen. Das ist zwar nicht ganz so komfortabel wie der redef_table-Aufruf, führt aber zum gewünschten Ergebnis.

Montag, September 26, 2016

Histogramme für Spalten mit PK/UK

Jonathan Lewis hat dieser Tage in einer Diskussion der Oracle-L Mailing-Liste darauf hingewiesen, dass auch eine Spalte mit einem PK oder UK von einem Histogramm profitieren kann - und diese Aussage jetzt in seinem Blog erläutert und mit einem Beispiel versehen. Interessant ist ein solches Histogramm dann, wenn die Werteverteilung zwischen Minimum und Maximum sehr uneinheitlich ist, so dass sich große Bereiche ergeben, in denen fast keine Daten existieren, während in anderen Bereichen gleichen Umfangs sehr viele Ergebnisse zu finden sind. In seinem Beispiel erfolgt eine Abfrage auf einen solchen sparse-besetzten Bereich und Oracle erkennt anschließend bei der Statistikerfassung (mit Standardeinstellungen), dass hier ein Histogramm nützlich ist. Das die Anlage von Histogrammen begründende Phänomen "data skew" (sprich: Ungleichverteilung) betrifft also nicht nur das Auftreten ungleichmäßig vieler Datensätze für einen gegebene Wert, sondern auch die ungleiche Verteilung in bestimmten Wertebereichen für eindeutige Werte.

Dienstag, September 20, 2016

Parallel Hint auf Statement Ebene

Christian Antognini erläutert in seinem Blog die unterschiedlichen Ausprägungsformen, der seit 11.2 verfügbaren Variante des parallel Hints auf Statement-Ebene, die neben den Hint auf Objekt-Ebene getreten ist, der es seit 11.1 erlaubt, den dem Objekt zugeordneten DOP (degree of parallelism) zu überschreiben. Für den parallel Hint auf Statement-Ebene gibt es dabei folgende Möglichkeiten:
  • parallel (default): erzwingt die Verwendung des allgemeinen (also von den beteiligten Objekten unabhängigen) default DOP (der sich errechnet als: CPU_COUNT * PARALLEL_THREADS_PER_CPU * Anzahl, der an der Ausführung beteiligten Instanzen).
  • parallel (manual): aktiviert den manuellen DOP, wenn für mindestens eines der beteiligten Objekte ein Parallelisierungsgrad angegeben ist (default oder ein Wert > 1). Dieser Wert ist abhängig von den Parallelisierungs-Definitionen der beteiligten Objekten.
  • parallel (auto): aktiviert den automatischen DOP (nicht aber parallel statement queuing und in-memory parallel execution). Die Entscheidung über den Einsatz der Parallelisierung liegt damit bei Optimizer.
  • parallel: entspricht weitgehend parallel(auto). Der einzige Unterschied ist anscheinend, dass der Parameter PARALLEL_MIN_TIME_THRESHOLD beim Einsatz von parallel ohne (auto) nicht berücksichtigt wird.
  • parallel (n): setzt die Parallelisierung auf den vorgegebenen Wert.
Der Artikel ergänzt diese Aussagen um praktische Beispiele, bei denen die unterschiedlichen Hints für ein Test-Szenario jeweils unterschiedliche Effekte hervorrufen.

Dienstag, September 13, 2016

Template Datenbanken in Postgres

Daniel Westermann erklärt im DBI Blog die Rolle der Template-Datenbanken in postgres, über die ich mir bisher nicht allzu viele Gedanken gemacht habe. Im Artikel erläutert er folgende Punkte:
  • in einem neu installierten postgres-Cluster existieren zunächst drei Datenbanken: postgres, template0 und template1.
  • die Datenbank postgres ist der default-Einstiegspunkt für viele Applikationen (etwa den pgadmin), kann aber - wie jede andere Datenbank - gelöscht werden, wenn keine Connections zur Datenbank existieren.
  • man kann sich anschließend aber problemlos mit der Datenbank template1 verbinden und in dieser über das Kommando "create database postgres;" die Standarddatenbank neu erzeugen.
  • Eine Datenbank, die über "create database" erzeugt wird, stellt eine exakte Kopie der Datenbank template1 dar. Werden in template1 neue Objekte erzeugt, so sind sie somit auch in den danach aus dem Template erzeugten DBs vorhanden.
  • es ist möglich, an Stelle von template1 eine andere Template-Datenbank zu verwenden. Dazu dient die Syntax "create database ... template ....;"
  • template0 erlaubt keine Connections (datallowconn=f in pg_database) und dient als unveränderliches Basis-Template.
  • Änderungen an den Attributen in pg_database erfolgen als Update: damit kann man beispielsweise das Attribut datistemplate anpassen, um eine Datenbank zum Template zu machen (wodurch sie übrigens unlöschbar wird). Ich finde es zwar immer wieder unheimlich, wenn an Dictionary-Tabellen Anpassungen durch DML ausgeführt werden, aber in postgres ist das offenbar so vorgesehen.
Erstaunlich ist für mich dabei vor allem, wie wenig ich bisher über die Rolle der Template-Datenbanken nachgedacht habe...

Montag, September 05, 2016

Dictionary Metadaten in der Multitenant Infrastruktur

Franck Pachot hat seit 2014 eine Reihe interessanter Artikel zu den internen Implementierungsdetails der Dictionary-Zugriffe im Multitenant-Kontext veröffentlicht. Ich spare mir eine detaillierte Zusammenfassung der umfangreichen Inhalte und beschränke mich auf die Verweise:
Die Wahrscheinlichkeit ist hoch, dass ich damit nicht alle Artikel zum Thema erfasst habe, aber mir ging es eher darum, das Thema überhaupt einmal zu notieren, da ich bisher in Sachen Multitenant noch solide Wissenslücken habe.