Details zu dbms_space.space_usage

Man lernt eine Menge, wenn man versucht, anderen Anwendern die Arbeitsweise interner Oracle-Mechanismen zu erklären. Noch mehr lernt man, wenn Jonathan Lewis die Erläuterungen redigiert, die man beigesteuert hat.

Im OTN-Forum wurde dieser Tage die Frage gestellt, auf welcher Datengrundlage die Prozedur dbms_space.space_usage ihre Aussagen zum Füllgrad von Tabellen-Blocks generiert. Da ich darauf nicht unmittelbar eine Antwort wusste und eine halbherzige Google-Suche keine Ergebnisse brachte, habe ich ein SQL Trace (Event 10046) erstellt und darin eine große Zahl von Einzelblock-Zugriffen gefunden (db file sequential read), was mich zunächst an Sampling denken ließ - allerdings würde Sampling keine globalen Aussagen gestatten. Daher habe ich mir umfangreichere Interpretationen gespart und stattdessen nur meine Beobachtungen mitgeteilt. Den Rest hat dann der Herr Lewis beigesteuert:

It's reading the bitmap space management blocks for objects in ASSM tablespace: 

fs1 -> blocks with 0 to 25% free space

fs2 -> blocks with 25 to 50% free space

fs3 -> blocks with 50 - 75% free space

fs4 -> blocks with 75 - 100% free space

FULL -> filled past the limit set by PCTFREE (and you might have set pctfree to something big like 60%, which means a block would be flagged full when it was actually 40% used) 

For index blocks the indicators are different - they're either FULL (in use in the index, so locked in place) or fs2 (available for relocation).

Die Unklarheiten bei der Semantik für Index-Blocks hatte Jonathan Lewis vor einiger Zeit in seinem Blog angesprochen. Mir war jedenfalls nicht mehr(?) bewusst, dass die space_usage-Prozedur nur für ASSM-Objekte ein Ergebnis liefert, für MSSM-Objekte hingegen "ORA-10614: Operation not allowed on this segment" - wobei die Dokumentation an dieser Stelle durchaus eindeutig ist ("This procedure can only be used on tablespaces that are created with auto segment space management"). Um die Ergebnisse der Prozedur zu formatieren, kann man übrigens ein Code-Stück von Yong Huangs Webseite (die inhaltlich so interessant wie in der Darstellung spartanisch ist) ausborgen.

Join Tree Typen in Ausführungsplänen

Kyle Hailey liefert in seinem Blog eine kompakte Erläuterung zu den drei Join Tree Typen:

• Left Deep
• Right Deep
• Bushy

Dabei beschreiben die Bezeichner die Anordnung der Elemente in einem klassisch arrangierten Ausführungsplan: beim right deep Plan stehen die am weitesten unten im Plan erscheinenden Operationen auch am weitesten rechts. Beim left deep plan hingegen steht das unterste Element relativ weit links - eine Postion versetzt zum aufrufenden (Join-)Element. Verständlicher wird der Zusammenhang mit den Abbildungen, die man bei Kyle Hailey findet, und die nachzuzeichnen ich mir hier spare. Entscheidend ist dabei, dass der right deep Plan dazu führt, dass erste Ergebnisse schneller geliefert werden können:

All of this boils down to the point that a right deep HJ can return rows earlier than a left deep HJ. A left deep HJ has to wait for each join to finished completely so that the result set can be hashed before the next step can probe it. On the other hand, in a right deep HJ, it’s not the result sets that are being hashed, but a table at each level, thus each table can be hashed without waiting for intermediary results and once these hashes are complete a probed row can flow all the way through the join tree, from bottom to top, similar to how a nested loop can start giving results early. The Left Deep HJs only have two open work areas at a time where as the Right Deep can have multiple work areas open. One of the key thing to keep in mind is how much data we have to hash. If the intermediate result sets are large (and/or growing each level) then that represents more work each step of the way.

Right deep Pläne gibt es dabei nur für Hash Joins während Nested Loops Joins immer eine left deep Anordnung besitzen. Der bushy tree schließlich ergibt sich beim Einsatz von Views oder Subqueries, die als non mergeable gekennzeichnet sind, und in solchen Plänen existieren mehr als zwei Datenquellen auf der am weitesten eingerückten Ebene (was wiederum in der Abbildung klarer wird als in meinem Verbalisierungsversuch).

Der Artikel umfasst zusätzlich noch eine Darstellung der untersuchten Pläne in VST Diagrammen (VST = Visual SQL Tuning, die Darstellungsform des Embarcadero DB Optimizers) und schließt mit dem wichtigen Hinweis, dass die Semantik der Hints USE_HASH und USE_NL sich insofern unterscheidet, dass immer die innere Tabelle des Joins angegeben wird, was allerdings im Fall des NL in Hinblick auf die Ausführungsreihenfolge die zweite Tabelle ist, während es für den HJ die erste Tabelle ist (die als Hash Map in den Speicher geladen wird).

Lateral und Cross Apply in 12c

In 12c hat Oracle weitere Anstrengungen unternommen, den Anforderungen des ANSI SQL-Standards bzw. den Herausforderungen der Konkurrenz zu entsprechen und neue Klauseln für die Spezifizierung von Join-Operationen eingeführt:

• cross outer apply clause: die Klausel existiert in zwei Varianten:
• cross apply: erlaubt einen korrelierten cross join - nur die Sätze der linken Tabelle werden berücksichtigt, zu denen die korrelierende Bedingung in der Operation auf der rechten Seite ein Ergebnis liefert (an dieser Stelle verweise ich auf das Beispiel der Dokumentation, da ich meinen beschreibenden Satz möglicherweise selbst bei erneuter Lektüre nicht mehr nachvollziehen kann).
• outer apply: eine ähnliche Variante zum outer join, die ebenfalls eine korrelierte Abfrage (oder einen entsprechenden table Operator) auf der rechten Seite erlaubt.
• lateral: erlaubt die Verwendung einer korrelierenden Operation auf der rechten Seite eines einfachen Joins. Intern wurde dieses Schlüsselwort schon deutlich früher unterstützt, war aber nicht in Benutzerabfragen erlaubt, wie man bei Jonathan Lewis nachlesen kann.

Während lateral zum ANSI Standard gehört (und seit Version 9.3 auch von postgres unterstützt wird), sind die apply-Operatoren anscheinend aus T-SQL übernommen, was meine private Theorie unterstützt, dass die SQL-Dialekte der großen RDBMS immer stärker konvergieren.

Nachtrag 23.05.2014: zu diesen neuen Syntaxelementen gibt es auch schon eine Unterstützung im Trivadis CodeChecker, den zu erwähnen ich hier offenbar vergessen hatte.

Data Masking mit 12c

Sieht auf den ersten Blick eher bizarr aus, hat aber seine Anwendungsfälle... Gavin Sooma schreibt über das Cloud Control Data Masking Pack, mit dessen Hilfe  in 12c vertrauliche oder in anderer Weise sensitive Daten durch mehr oder minder zufällig generierte Daten ersetzt werden können. "Ersetzt" bedeutet in diesem Fall: physikalisch ersetzt - es geht also um eine semantische Änderung von Daten: und das ist natürlich nicht unbedingt etwas, das man in einer Produktivdatenbank durchführen möchte. Die Dokumentation für 11.2 erläutert den Einsatzbereich des Features folgendermaßen:

Enterprises run the risk of breaching sensitive information when copying production data into non-production environments for the purposes of application development, testing, or data analysis. Oracle Data Masking helps reduce this risk by irreversibly replacing the original sensitive data with fictitious data so that production data can be shared safely with non-production users.

Das klingt dann doch wieder einleuchtend. 

Unusable Indexes und Hints

Bei Jonathan Lewis findet sich ein Artikel, in dem gezeigt wird, dass Index-Hints den Optimizer zur Index-Verwendung zwingen, auch wenn der Index sich im Zustand UNUSABLE befindet, so dass der Zugriff in einem Fehler endet:

ORA-01502: index 'XXX' or partition of such index is in unusable state

Anschließend an den Artikel haben Matthias Rogel, Dom Brooks und Mohamed Houri einige Kommentare mit Links auf eigene Artikel ergänzt, die unter anderem zeigen, dass das Verhalten zwar dokumentiert sein mag, aber nicht unbedingt konsistent zu anderen Fällen erscheint. Auch Plan Baselines könnten durch die Verwendung solcher Hints unnötige und überraschende Fehler hervorrufen.

Darstellung des Execution Plans

Gestern hatte ich einen größtenteils harmlosen Kommentar zum Artikel Best practice for the sending of an Oracle execution plan von Franck Pachot abgegeben. Der Artikel beschäftigt sich mit den unterschiedlichen Möglichkeiten der Erzeugung von Ausführungsplänen mit rowsource Informationen für Anwender mit und ohne Tuning-Pack-Lizenz und liefert genau die Informationen von denen man als regelmäßiger Besucher von Oracle-Foren - also etwa der OTN-Foren, wo man den Herrn Pachot in letzter Zeit häufiger sieht -, hoffen würde, dass die Anfragenden sie kennen und berücksichtigen würden. Mein Kommentar zur HTML-Version eines via SQL Monitor erzeugten Plans lautete nun ungefähr, dass ich die grafische Version zwar hübsch und ansprechend finde, aber Pläne normalerweise lieber in sqlplus erzeuge, weil ich in der textuellen Version gerne copy&paste, Suchoperationen etc. verwende. Dazu hat der Autor nicht nur eine zustimmende Antwort gegeben, sondern gleich noch einen Artikel Oracle SQL Monitoring reports in flash, html, text hinterher geschickt, der erläutert, wie man das in der HTML-Version eingebettete (und seit 12c gepackte) XML mit Hilfe des sqlmonitorText.xsl XSLT Templates weiterverarbeiten und den grafischen in einen klassischen Plan zurückverwandeln kann und insbesondere, dass sich die erzeugte HTML-Repräsentation mit Hilfe eines Linux Text-Web-Browsers wie elink in eine ganz einfache textuelle Version übertragen lässt - was vor allem zur exakteren Bestimmung der Einrückungstiefe bei komplexeren Plänen wichtig ist. An dieser Stelle noch einmal mein expliziter Dank an Franck Pachot für diese interessante Fortsetzung.

On Update Cascade Foreign Keys in postgres

Oracle unterstützt diese Option nicht, aber der SQL Server kennt sie und postgres auch: die Möglichkeit, einen Foreign Key als ON UPDATE CASCADE zu definieren und dadurch die Änderung eines Primary Keys der Parent Tabelle an die Child Tabelle zu propagieren und die Fremdschlüsselwerte dort entsprechend zu korrigieren:

dbadmin=# create table t_parent (pid int primary key);
CREATE TABLE
dbadmin=# create table t_child (id int primary key, pid int references t_parent on update cascade);
CREATE TABLE
dbadmin=# insert into t_parent(pid) values(1);
INSERT 0 1
dbadmin=# insert into t_child(id, pid) values(1, 1);
INSERT 0 1
dbadmin=# insert into t_child(id, pid) values(2, 1);
INSERT 0 1
dbadmin=# insert into t_child(id, pid) values(3, 1);
INSERT 0 1
dbadmin=# update t_parent set pid = 42 where pid = 1;
UPDATE 1
dbadmin=# select * from t_child;
 id | pid
----+-----
  1 |  42
  2 |  42
  3 |  42
(3 Zeilen)

Ich vermute, die Puristen werden an dieser Stelle einwenden, dass man einen Primary Key grundsätzlich nicht ändert. Und über die Performance solcher kaskadierender Änderungen bei größerem Änderungsvolumen will ich lieber nicht nachdenken - aber in manchen Konstellationen könnte dieses Feature ausgesprochen nützlich sein.

Wörter zählen mit regexp_count

Zu den Dingen, die ich seit Jahren auf meiner Erledigungsliste habe, gehört eine intensivere Beschäftigung mit regulären Ausdrücken. In der Zwischenzeit verlinke ich hier ein weiteres Mal Adrian Billington, der unter anderem eine umfassende Erläuterung zur regexp_count-Funktion liefert, die den Bestand der regexp-Funktionen in 11g ergänzte, und mit der sich die Anzahl des Auftretens eines Pattern in einem String ermitteln lässt. Und um es nicht beim Link zu belassen, hier noch ein Minimalbeispiel zum Zählen von Wörtern:

with
basedata as (
select 'eins zwei drei vier fuenf' col1 from dual
)
select regexp_count(col1,'\w+') wc
  from basedata

        WC
----------
         5

Descending Index Quiz

Jonathan Lewis hat heute mal wieder eine Quizfrage gestellt und ausnahmsweise bin ich ziemlich schnell auf die Lösung gekommen. Die Frage lautet: wieso liefert ein descending Index einen duplicate Key Fehler, während ein entsprechender aufsteigender Index fehlerlos angelegt werden kann:

SQL> create unique index t1_i1 on t1(v1 desc);
create unique index t1_i1 on t1(v1 desc)
                                *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
 
 
SQL> create unique index t1_i1 on t1(v1);
 
Index created.

Die zugehörige Tabellendefinition könnte ungefähr folgendermaßen aussehen:

create table t1(v1 number);

insert into t1(v1) values (NULL);

insert into t1(v1) values (NULL);

Die Erklärung dafür lautet: ein descending index ist ein FBI (function based index) auf einer zusätzlichen generierten Spalte, bei der ein 0xFF an das Einerkomplement jedes Werts der Ursprungsspalte angehängt wird, und diese Ergänzung macht aus der NULL dann einen Wert, der als Duplikat betrachtet wird. Den normalen Index ohne die desc-Angabe interessieren die NULLS nicht, was die fehlerlose Anlage erklärt. Und den entscheidenden Hinweis zum Thema habe ich in Richard Footes hier verlinktem Artikel gefunden (und dort in einem Kommentar des Herrn Lewis).

Komprimierungseffektivität bestimmen mit dbms_compression.get_compression_ratio

Heute wurde im OTN Forum die Frage diskutiert, ob dbms_compression.get_compression_ratio für bereits komprimierte Tabellen eine Aussage zur Effektivität der Komprimierung liefert: also ob sich durch Updates so viele Entpackungen von Datensätzen ergeben haben, dass eine erneute Komprimierung sinnvoll wäre. Ich habe zur Beantwortung mein Standardverfahren eingesetzt:

1. Nachdenken
2. Zu keinem eindeutigen Ergebnis kommen
3. Test erstellen und ausprobieren

Der Test in 11.2.0.1 dazu sah etwa folgendermaßen aus - und bediente sich aus ähnlichen Beispielen von Christian Antognini und Neil Johnson (die, hätte ich sie sorgfältig gelesen, die im Raum stehende Frage bereits beantwortet hätten):

-- Test mit einem User mit DBA-Rechten

-- Anlage eines Tablespace für temporäre Testobjekte
create tablespace scratch datafile 'E:\ORACLE_DATA\TEST\SCRATCH.DBF' size 50M;

-- Anlage einer Test-Tabelle
drop table t;

create table t 
as
select rownum id
     , lpad('*', 50, '*') compressable_col
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'T')  

-- Aufruf der Prozedur zur Bestimmung der Komprimierungseffekte
DECLARE
  l_blkcnt_cmp       BINARY_INTEGER;
  l_blkcnt_uncmp     BINARY_INTEGER;
  l_row_cmp          BINARY_INTEGER;
  l_row_uncmp        BINARY_INTEGER;
  l_cmp_ratio        NUMBER;
  l_comptype_str     VARCHAR2(100);
BEGIN
      dbms_compression.get_compression_ratio(
        -- input parameters
        scratchtbsname   => 'SCRATCH',                           -- scratch tablespace
        ownname          => user,                                -- owner of the table
        tabname          => 'T',                                 -- table name
        partname         => NULL,                                -- partition name
        comptype         => dbms_compression.comp_for_oltp,      -- compression algorithm
        subset_numrows   => 1000000,
        -- output parameters
        blkcnt_cmp       => l_blkcnt_cmp,    -- number of compressed blocks
        blkcnt_uncmp     => l_blkcnt_uncmp,  -- number of uncompressed blocks
        row_cmp          => l_row_cmp,       -- number of rows in a compressed block
        row_uncmp        => l_row_uncmp,     -- number of rows in an uncompressed block
        cmp_ratio        => l_cmp_ratio,     -- compression ratio
        comptype_str     => l_comptype_str   -- compression type
      );
      dbms_output.put_line('type: '|| l_comptype_str);
      dbms_output.put_line('blocks comp: '|| l_blkcnt_cmp);
      dbms_output.put_line('blocks uncomp: '|| l_blkcnt_uncmp);
      dbms_output.put_line('ratio: '|| to_char(l_cmp_ratio,'99.999'));
END;
/

type: "Compress For OLTP"
blocks comp: 203
blocks uncomp: 1024
ratio:   5.000

select blocks from dba_segments where segment_name = 'T';

    BLOCKS
----------
      9216

Die "blocks uncomp" haben also nichts mit der tatsächlichen Größe des Basisobjekts zu tun - und bereits die erforderliche Angabe des "scratchtbsname" konnte vermuten lassen, dass hier temporäre Objekte und Sampling im Spiel sind. Diese Annahme bestätigt dann auch das zugehörige SQL-Trace:

create table "TEST".DBMS_TABCOMP_TEMP_UNCMP tablespace "SCRATCH" nologging as
  select /*+ FULL("TEST"."T") */ *  from "TEST"."T"  sample block( 22) mytab
 
create table "TEST".DBMS_TABCOMP_TEMP_CMP organization heap  tablespace
  "SCRATCH" compress for oltp nologging as select /*+ FULL("TEST"."T") */ *
  from "TEST".DBMS_TABCOMP_TEMP_UNCMP mytab

Hier wird also via CTAS eine nicht komprimierte Tabelle aus einem Sample des Quellobjekts erzeugt und zu diesem Sample wird dann anschließend eine komprimierte Version angelegt. Ungefähr so hätte ich das wahrscheinlich auch gemacht, wenn ich einen entsprechenden Test manuell durchgeführt hätte. Zur Beantwortung der ursprünglichen Frage könnte die Procedure demnach nur indirekt dienen.

Histogramme löschen

Jonathan Lewis zeigt, wie man in 10g Histogramme löschen kann - ab 11g gibt es zu diesem Zweck den DBMS_STATS.DELETE_COLUMN_STATS-Parameter col_stat_type, mit dem die Löschung explizit durchgeführt werden kann. Neben dem Verfahren erklärt der Autor auch noch einmal seine grundsätzliche Strategie der expliziten Anlage der tatsächlich benötigten Histogramme:

my preferred method of collecting statistics is to use method_opt => ‘for all columns size 1' (i.e. no histograms) and then run scripts to create the histograms I want. This means that after any stats collection I need to run code that checks to see which tables have new stats, and then re-run any histogram code that I’ve written for that table.

Mit den neuen Histogrammtypen in 12c sieht der Herr Lewis aber seltener die Notwendigkeit der manuellen Erzeugung von Histogrammen, die er an diversen Stellen erläutert hat.

Komplexe Gruppenbildung mit Analytics, Model clause oder MATCH_RECOGNIZE

Stew Ashton (der zu den regelmäßigen Beiträgern im OTN Forum gehört, wenn es um komplexere SQL-Fragestellungen geht) liefert in seinem Blog einige Varianten zur Lösung des Problems "Group records that are within 6 hours of the first record in the group", zu dem Tom Kyte bei AskTom schrieb: "I don't see offhand a way to do that with analytics." Anstelle von Analytics kommen zwei andere Möglichkeiten in Frage: die Model clause und seit 12c auch die MATCH_RECOGNIZE clause, wobei erstere von der Möglichkeit der Verwendung prozeduraler Logik und letztere von der Möglichkeit der Mustererkennung profitiert. Ich spare mir eine genauere Beschreibung der Lösungen - habe aber den Eindruck, dass die Syntax von MATCH_RECOGNIZE vergleichsweise handhabbar sein sollte (im Vergleich zur Model clause, die mir noch immer wenig Freude bereitet).