inoffizielle display_cursor Dokumentation

Eines der wichtigsten - aber leider offiziell nicht besonders gut dokumentierten - Hilfsmittel bei der Analyse von SQL-Zugriffsproblemen mit Oracle ist sei Release 10 die Prozedur dbms_xplan.display_cursor, zu der Kyle Hailey in seinem Blog eine ziemlich umfassende Erläuterung liefert (inklusive seiner Hilfs-Scripts zur grafischen Darstellung der Abweichung von E- und A-rows bei der Verwendung von rowsource statistics). Da mein Standard-Vorschlag bei Performance-Problemen die Erzeugung eines Plans mit rowsource Statistiken ist (über den Parameter statistics_level oder einfach mit Hilfe des gather_plan_statistics Hints), werde ich auf diesen Artikel im OTN-Forum in Zukunft vermutlich häufiger verweisen.

sqlplus output Komprimierung

Eine nette Idee, die Frits Hoogland in seinem Blog vorstellt: die Komprimierung von sqlplus output mit Hilfe einer named pipe:

This solution will look familiar to “older” Oracle DBA’s: this was how exports where compressed from the “original” export utility (exp).

Der Vorteil dieser Variante ist, dass eine Komprimierung während der Erzeugung des Ergebnisses möglich ist - die Ziel-Datei muss also nicht erst in normaler Größe angelegt und dann komprimiert werden.

Oracle 12c: in database archiving

Julian Dontcheff hat dieser Tage in seinem Blog eine ganze Reihe von Links zum Thema in-database archiving zusammengetragen und ein paar Bemerkungen zu den Performance-Aspekten dieses neuen Features gemacht - und mich damit auf die Idee gebracht, das Verhalten dieser Option etwas genauer zu betrachten. Ausgangspunkt war dabei die folgende Definition aus der Dokumentation:

In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance.

Was genau bedeutet dabei "without compromising application performance"? Dazu ein kleiner Test mit 12.1.0.1 auf meinem Windows-PC. Ich lege eine Tabelle mit zwei Spalten und der Option row archival an, die dafür sorgt, dass man Datensätze vor dem Optimizer verbergen kann:

drop table t1;

create table t1 (
    id number
  , padding varchar2(20)
) row archival;

insert /*+ append */ into t1(id, padding)
select rownum id, lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

commit;

select file_id, block_id, blocks
  from dba_extents
where segment_name = 'T1';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       1096          8
      9       1104          8
      9       1112          8
      9       1120          8
      9       1128          8
      9       1136          8
      9       1144          8

alter system dump datafile 9 block 1104;

Ein Blick in den block dump zeigt, dass die row archival Option intern über die Anlage einer zusätzlichen Spalte realisiert ist:

data_block_dump,data header at 0x777007c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x0777007c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Vor die Spalten Id und Padding wird somit eine interne Spalte gestellt. Besonders überraschend ist diese Beobachtung eher nicht, wenn man bedenkt, dass die Archivierungs-Markierung eines Datensatzes als Update durchgeführt wird:

update t1 set ORA_ARCHIVE_STATE = 2 where id <= 5000;
commit;

Das Update setzt die Hälfte der Datensätze in der Tabelle auf einen ORA_ARCHIVE_STATE <> 0 und macht sie damit für den Optimizer unsichtbar. Eine einfache Query ohne Einschränkung bringt anschließend eine sinnvolle cardinality-Schätzung:

explain plan for
select *
  from t1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   131K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 |   131K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("T1"."ORA_ARCHIVE_STATE"='0')

Ganz offensichtlich ist hier also keine schwarze Magie im Spiel, sondern nur eine virtuelle Spalte, die als default-Filterkriterium eingesetzt wird. Die Frage, die sich mir im Anschluss stellt, ist: gibt es jenseits der simplen Handhabung (und wahrscheinlich des Verhaltens bei komplexeren Operationen) einen prinzipiellen Unterschied zwischen dieser neuen Option und der Verwendung einer expliziten sichtbaren Spalte zur Kennzeichnung historischer Daten? Dazu das komplementäre Beispiel:

drop table t2;

create table t2 (
    archive_flag number
  , id number
  , padding varchar2(20)
) ;

insert /*+ append */ into t2(archive_flag, id, padding)
select 0 archive_flag
     , rownum id
     , lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

select file_id, block_id, blocks
  from dba_extents
 where segment_name = 'T2';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       3904          8
      9       3912          8
      9       3920          8
      9       3928          8
      9       3936          8
      9       3944          8
      9       3952          8

alter system dump datafile 9 block 3912;

Der zugehörige block dump scheint mir von der Version des impliziten Archivierungsverfahren kaum unterscheidbar zu sein: die Satzanzahl und die Angaben zum verwendeten Speicherplatz sind identisch und auch bei der Beschreibung der Datensätze gibt es nur marginale Differenzen (die sich daraus ergeben, dass ich mit einem NUMBER-Wert gearbeitet habe, statt mit VARCHAR2(4000) als was ORA_ARCHIVE_STATE tatsächlich definiert ist).

data_block_dump,data header at 0x1545807c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x1545807c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Natürlich muss man in diesem Fall das Flag bei Zugriffen explizit angeben, aber davon abgesehen, scheint der Unterschied bei den internen Strategien zunächst eher marginal zu sein:

update t2 set archive_flag = 2 where id <= 5000;
commit;

exec dbms_stats.gather_table_stats(user, 'T2')

explain plan for
select *
  from t2
 where archive_flag = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   136K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |  5000 |   136K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ARCHIVE_FLAG"=0)

Insofern würde ich auf Anhieb erst einmal behaupten, dass das Feature sicherlich administrative Vereinfachungen mit sich bringt - aber große Geheimnisse scheinen mir hinsichtlich der Performance-Eigenschaften der Operation eher nicht zu bestehen. Ich muss allerdings zugeben, dass ich über die elaborierteren Effekte des Features ("updates to archived data can be deferred during application upgrades to improve the performance of upgrades") und die vorgeschlagenen Komprimierungsverfahren noch nicht nachgedacht habe.

Postgres: csv Export

Das Thema ist andernorts schon hundertfach erläutert worden, aber da ich inzwischen mehrfach danach gesucht habe, hier eine kurze Notiz zu den beiden Möglichkeiten, csv-Dateien für die Ergebnisse von postgres-Queries zu erzeugen.

COPY-Kommando in PSQL

In diesem Fall erfolgt der Aufruf in psql und schreibt die csv-Datei test.csv ins aktuelle Arbeitsverzeichnis:

-- in psql
-- mit header und Semikolon als Feld-Delimiter
test=# \copy (select * from t) to test.csv with csv header delimiter ';'

PSQL-Aufruf mit Ausgabe in Datei

Hier erfolgt der Aufruf in der Shell und erzeugt die angegebene csv-Datei:

-- Beispiel mit windows cmd
-- --pset footer: unterdrückt feedback zur Anzahl glieferter Zeilen
-- -A: unaligned output mode; ohne die Einstellung liefert psql eine tabellarische Darstellung
-- -F: definiert den Feldseparator (hier also das Semikolon)
-- -c: Angabe der auszuführenden Query
psql.exe -U postgres -d test --pset footer -A -F; -c "select * from t" > c:\temp\test.csv

SSAS: Sortierung und Compression

Wobei ich neidlos anerkenne, dass der Titel, den Danny Lee seinem Artikel, auf den ich mich hier beziehe, gegeben hat, deutlich hübscher ist: Analysis Services Multidimensional: It is the Order of Things (was sich übrigens nicht auf Michel Foucaults berühmtes Buch, sondern auf einen Dialog von Commander Sisko mit einem Jem'Hadar-Krieger bezieht, was nicht verwundert, denn wir sind hier ja nicht mehr in Kansas...).

Worum es dabei geht? Um den Zusammenhang zwischen Sortierung der relationalen Basisdaten und Größe des erzeugten Cubes (und insbesondere der MeasureGroups) - oder anders ausgedrückt: um den Einfluss der Sortierung auf die Komprimierbarkeit der Daten. In einem vorgestellten Beispiel reduziert die Sortierung die Cube-Größe dabei um 43% (was zu meinen eigenen - internen - Notizen passt, die ich gerade noch mal nachgeschlagen habe). Dabei gilt grundsätzlich, dass die compression um so stärker ausfällt, je kleiner der Range von DataIds ist, die für ein Segment berücksichtigt werden müssen. Neben der verbesserten Komprimierung erlaubt die Sortierung dabei auch eine effektivere Segment Elimination bei der Filterung nach Attributen. Da die Ordnung nach einem Attribut in der Regel zu einer größeren Unordnung für andere Attribute führt, gilt für Fakten, dass man bei ihrer Sortierung mit den Attributen beginnen sollte, die sich am häufigsten wiederholen - im Beispiel des Artikels ergäbe sich die Sortierreihenfolge (Date, Time, Product, Sales).

Das sind natürlich keine ganz neuen Einsichten, sondern Informationen, die man auch in den einschlägigen Publikationen zum Thema finden kann (die im Artikel auch verlinkt sind), aber furchtbar viel Neues gibt's zum Thema (multidimensional) SSAS wohl auch nicht zu sagen, was ich immer noch bedauere.

Falsche Ergebnisse durch Partition Exchange ohne Validation

Jonathan Lewis schreibt dieser Tage so viel, dass ich Mühe habe, mit der Lektüre zu folgen - vom Aufschreiben der zentralen Punkte ganz zu schweigen. Aber das heutige Quiz zeigt einen ziemlich gemeinen Effekt, der mir nur noch ganz vage in Erinnerung war und dort lieber einen besseren Platz bekommen sollte, nämlich die Tatsache, dass sich Oracle beim Partition Exchange without validation darauf verlässt, dass der Auftraggeber schon wissen wird, warum er etwas tut - und deshalb auch abwegige Ergebnisse liefern kann. Im Beispiel lieferte ein SELECT DISTINCT den gleichen Wert doppelt, da er einmal aus der korrekten Partition und einmal aus einer anderen Partition gelesen wurde, die eigentlich für ganz andere Werte vorgesehen war, aber ohne Prüfung durch den Partitionsaustausch an die unpassende Stelle gelangte. Wahrscheinlich irritieren mich solche Effekte in relationalen Datenbanken deutlich mehr als anderswo, weil ich damit rechne, dass das RDBMS mich schon davon abhalten wird, ungeheuren Blödsinn anzustellen.

Systemstatistiken im Exadata Modus

Seit Release 11.2.0.4 gibt es die Systemstatistiken in einer neuen Geschmacksrichtung: dem "exadata mode". Das hört sich vielleicht dramatischer an als es ist, da die Ermittlung von "exadata mode" system statistics exakt der Ermittlung von noworkload system statistics entspricht - abgesehen davon, dass der MBRC auf den Wert des Parameters db_file_multiblock_read_count gesetzt wird. Daher ergeben sich nur dann Unterschiede zwischen dem "exadata mode" und den herkömmlichen noworkload system statistics, wenn der Initialisierungsparameter db_file_multiblock_read_count nicht explizit gesetzt wurde, denn in diesem Fall verwendet Oracle den Standardwert 8 (im dictionary als hidden parameter _db_file_optimizer_read_count hinterlegt). Diese Zusammenfassung basiert auf der Grundlage von:

• System Statistics – “Exadata” Mode von Kerry Osborne mit einer kurzen Vorstellung des neuen Modus.
• System Statistics Gathered in Exadata Mode – When Are They Relevant? von Christian Antognini, der nicht nur den Unterschied zwischen "exadata mode" und noworkload system statistics erläutert, sondern eine umfassende, aber trotzdem handliche Darstellung des Costings für Full Table Scans liefert.

Christian Antogninis Artikel ist ein hervorragender Einstieg zum Thema CBO-Kalkulationen - und wahrscheinlich werde ich darauf noch häufiger verweisen (so wie auf seine Anleitung zum Lesen von Ausführungsplänen).

Zur Definition von Hints

So wie der Herr Kyte gerne über bind variables - bzw. ihr Fehlen - zu reden (fast hätte ich gesagt: zu schwadronieren) pflegt, wird Jonathan Lewis nicht müde zu betonen, dass Hints keine vagen Vorschläge, sondern Direktiven für den Optimizer sind, denen der CBO zu folgen hat (sofern sie syntaktisch korrekt und für den transformierten plan relevant sind und kein Bug im Spiel ist). In einem aktuellen Artikel sammelt er die Aussagen zum Thema aus Dokumentationen von 8 bis 12, wobei merkwürdigerweise ausgerechnet Version 12 die Aussagen abschwächt und von "instruct" statt von "force" spricht. Aber vielleicht ist das nur ein Akt der Höflichkeit.

Komplexe SQL-Lösungen

Rob van Wijk hat endlich mal wieder einen Artikel veröffentlicht. Darin liefert er eine Lösung für das Problem der Verteilung von Tabellen stark unterschiedlicher Größe in gleichgroße Gruppen (wobei Größe einfach die Anzahl an Bytes ist). Aufgrund der deutlichen Größenunterschiede genügt es nicht, die Tabellen einfach über Modulo n Gruppen zuzuordnen, da dabei die erste Gruppe sehr viel größer werden würde als die n-te. Seine Lösung verwendet daher eine Model clause und folgende Vorgehensweise:

• order all tables by size in descending order
• place the N largest tables in groups 1 .. N
• iterate over the tables with a non-empty size in descending order and add the table to the first group encountered whose size is below the running average size of the groups
• iterate over the empty tables and add the table to the first group encountered whose total number of tables is below the average number of tables per group

Wie bei Verwendung der Model clause üblich, ist das verwendete SQL alles andere als intuitiv - jedenfalls für mich - , aber effektiv.

In einem Nachtrag zum Artikel verweist der Herr van Wijk dann noch auf einen ähnlichen Artikel im Blog von Brendan Furey, der mir bisher komplett entgangen war, und der eine wahre Fundgrube für SQL-Lösungen zu klassischen Optimierungsproblemen darstellt (etwa zum Travelling Salesman Problem). Dabei liefern die Artikeln nicht nur komplexe SQL-Lösungen, sondern auch sehr hübsche grafische Visualisierungen zu den Fragestellungen. Wenn ich mal wieder an die Grenzen meiner algorithmischen Möglichkeiten komme, wäre das ein guter Platz zum Suchen.

View-Export mit 12c

Carsten Czarski stellt in seinem Blog ein nettes kleines Feature des Data Pump in 12c vor: die Möglichkeit, Views als Tabellen zu exportieren, was die Notwendigkeit einer Materialisierung solcher Views über CTAS beseitigt. Dieses Feature hätte mir dieser Tage Arbeit sparen können, aber bei meinem Applikations-Umzug von einem RDBMS in ein anderes war Oracle 12c nicht im Spiel - eigentlich nicht einmal Oracle, es sei denn, man ließe MySQL unter dieser Flagge segeln...

Der Artikel des Herrn Czarski enthält neben allerlei Details noch einige Links auf seine älteren Erläuterungen zum DBMS_DATAPUMP Package, das die Einbindung des Exports in PL/SQL ermöglicht, und zur Verknüpfung von Data Pump und DBMS_SCHEDULER.