FTS Performance und Partitionierung

Vor fast zwei Jahren habe ich hier eine Geschichte erzählt, in der ein Full Table Scan für eine partitionierte Tabelle deutlich mehr Zeit benötigte als ein FTS auf eine nicht partitionierte Tabelle des gleichen Inhalts - und dieses Phänomen auf die Verwendung von direct path reads für den seriellen table scan verbucht. Was damals fehlte, war ein Beispiel, und das habe ich dieser Tage im OTN Forum in einem mehr oder minder geeigneten Thread untergebracht. Um hier im Blog auch mal wieder ein wenig Code zu veröffentlichen, zitiere ich mich an dieser Stelle:

-- 11.2.0.1

-- I create a simple partitioned table
-- and a corresponding non-partitioned table
-- with 1M rows
drop table tab_part;
 
create table tab_part (
    col_part number
  , padding varchar2(100)
)
partition by list (col_part)
(
    partition P00 values (0)
  , partition P01 values (1)
  , partition P02 values (2)
  , partition P03 values (3)
  , partition P04 values (4)
  , partition P05 values (5)
  , partition P06 values (6)
  , partition P07 values (7)
  , partition P08 values (8)
  , partition P09 values (9)
);
 
insert into tab_part
select mod(rownum, 10)
     , lpad('*', 100, '*')
  from dual
connect by level <= 1000000;
 
exec dbms_stats.gather_table_stats(user, 'tab_part')
 
drop table tab_nopart;
 
create table tab_nopart
as
select *
  from tab_part;
 
exec dbms_stats.gather_table_stats(user, 'tab_nopart')
 
-- my _small_table_threshold is 1777 and the partitions
-- have a size of ca. 1600 blocks while the non-partitioned table
-- contains 15360 blocks
 
-- I have to flush the buffer cache since
-- the direct path access is only used
-- if there are few blocks already in the cache
alter system flush buffer_cache;
 
-- the execution plans are not really exciting
-- select count(*) from tab_part;
----------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |  8089   (0)| 00:00:41 |       |       |
|   1 |  SORT AGGREGATE     |          |     1 |            |          |       |       |
|   2 |   PARTITION LIST ALL|          |  1000K|  8089   (0)| 00:00:41 |     1 |    10 |
|   3 |    TABLE ACCESS FULL| TAB_PART |  1000K|  8089   (0)| 00:00:41 |     1 |    10 |
----------------------------------------------------------------------------------------

-- select count(*) from tab_nopart;
-------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |  7659   (0)| 00:00:39 |
|   1 |  SORT AGGREGATE    |            |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TAB_NOPART |  1000K|  7659   (0)| 00:00:39 |
-------------------------------------------------------------------------

Auf meinem PC benötigt der FTS der partitionierten Tabelle ca. 3 sec., während der Scan der nicht partitionierten Tabelle nur 1 sec. benötigt, was in v$sesstat (unter anderem) mit folgenden Werten erläutert wird:

-- non partitioned table
NAME                                               DIFF
-------------------------------------------- ----------
table scan rows gotten                          1000000
file io wait time                                 15313
session logical reads                             15156
physical reads                                    15153
consistent gets direct                            15152
physical reads direct                             15152
DB time                                              95
 
-- partitioned table
NAME                                               DIFF
-------------------------------------------- ----------
file io wait time                               2746493
table scan rows gotten                          1000000
session logical reads                             15558
physical reads                                    15518
physical reads cache prefetch                     15202
DB time                                             295

Demnach kann die Segmentgröße von Partitionen einen erstaunlichen Einfluss auf die Laufzeiten von Full Table Scans haben, was aus dem Plan nicht unmittelbar zu ersehen ist.

Nachtrag 04.01.2015: da ich vor kurzem noch mal ein wenig mit der _small_table_threshold experimientiert habe (und dabei mal wieder feststellen musste, dass mein Gedächtnis nicht mehr viel von der Wiederverwendung von Informationen hält), hier noch der ergänzende Hinweis - den man auch bei Tanel Poder findet -, dass eine Änderung des Parameters keine Cursor-Invalidierung mit sich bringt, da er nicht Teil des optimizer environments ist.

Oracle 12c ist da

Aufmerksamere Beobachter als ich, haben es schon festgestellt: aus der Oracle Software Delivery Cloud kann man 12c für Linux x86-64 downloaden. Oder einfach aus dem Download-Bereich bei OTN.

Hier ein paar ungeordnete Punkte zum neuen Release, die ich möglicherweise noch ergänze:

• Dani Schnider von Trivadis weist darauf hin, dass der Oracle Warehouse Builder nicht Teil der Installation von 12.1 ist, aber in der Version 11.2.0.3 mit 12er Datenbanken verwendet werden kann. Mittelfristig wird der OWB offenbar vom ODI ersetzt, dessen Version 12c aber noch nicht veröffentlicht ist. Daher lautet sein Fazit: "there is no hurry to migrate from OWB to ODI".
• Im Blog der CBO-Entwickler verweist Maria Colgan auf einige Whitepaper zu 12c. Interessant ist dabei insbesondere die Adaptive Query Optimization, bei der eine Query während der Ausführung auf einen neuen Plan umgeleitet werden kann, wenn der Optimizer feststellt, dass seine Annahmen zur Datenverteilung nicht zutreffen.
• Jonathan Lewis erwähnt die Möglichkeit des Online Rebuild für Tabellen-Partitionen, die nur mit minimalen Locks zu Beginn und zum Ende der Operation verbunden ist.

Index-Hint-Informationen im CBO-Trace

Jonathan Lewis' neuster Artikel beschäftigt sich mit den spaltenbezogenen Index-Hints - also jener Variante, in der man im Hint die Tabelle und die Spaltenkombination angibt, für die man einen Index heranziehen möchte, und den Index-Namen nicht explizit angibt. Dazu zeigt er die entsprechenden Angaben im CBO-Trace (Event 10053), die in diesem Fall recht selbsterklärend sind. Im Beispiel wird deutlich, dass die Reihenfolge der Spalten relevant ist (ein Hint für (col1,col2) wird nicht auf einen Index (col2, col1) bezogen) und dass ein exakter Match Vorrang vor teilweiser Übereinstimmung hat (der Hint (col1, col2) wird nicht auf einen Index (col1, col2, col3) bezogen, wenn es auch einen Index (col1, col2) gibt).

SQL Server: Spool Operationen

Weiterhin neige ich dazu, Datenbank-Features anderer RDBMS mit dem Idealtypus Oracle zu vergleichen. Bei Rob Farley liest man zu Spooling Operationen in Execution Plans im SQL Server folgende Erläuterung:

The spool operator consumes a set of data, and stores it in a temporary structure, in the tempdb database. This structure is typically either a Table (ie, a heap), or an Index (ie, a b-tree). If no data is actually needed from it, then it could also be a Row Count spool, which only stores the number of rows that the spool operator consumes. A Window Spool is another option if the data being consumed is tightly linked to windows of data, such as when the ROWS/RANGE clause of the OVER clause is being used. You could maybe think about the type of spool being like whether the cotton is going onto a small bobbin to fit in the base of the sewing machine, or whether it’s a larger spool for the top.

A Table or Index Spool is either Eager or Lazy in nature. Eager and Lazy are Logical operators, which talk more about the behaviour, rather than the physical operation. If I’m sewing, I can either be all enthusiastic and get all my cotton onto the spool before I start, or I can do it as I need it. “Lazy” might not the be the best word to describe a person – in the SQL world it describes the idea of either fetching all the rows to build up the whole spool when the operator is called (Eager), or populating the spool only as it’s needed (Lazy).

Window Spools are both physical and logical. They’re eager on a per-window basis, but lazy between windows.

Demnach handelt es sich also um die temporäre Persistierung von Daten in der tempdb (also dem Äquivalent zum temporary tablespace in Oracle). In Oracle würden den Spooling Operationen demnach verschiedene Elemente im Execution Plan entsprechen - etwa die TEMP TABLE TRANSFORMATION bei der Zwischenspeicherung von CTEs. Im SQL Server sind diese Operationen vermutlich häufiger als in Oracle, da sie auch als Ersatz für eine solide Lesekonsistenz dienen müssen ("When data needs to be kept away from the original source").

Speicherplatzberechnung

Im OTN Forum wurde zuletzt die Frage aufgeworfen, wie es dazu kommen kann, dass ein Tabellen-Segment deutlich kleiner ist als die Berechnung NUM_ROWS * AVG_ROW_LENGTH erwarten lassen würde, und Jonathan Lewis hat seine Erklärungen zum Thema im eigenen Blog aufgeführt. In der Regel dürften bei diesem Effekt die folgenden beiden Ursachen im Spiel sein:

• compression: weil dbms_stats in diesem Fall die entpackten Längen ermittelt
• lobs: weil dbms_stats immer von der maximal möglichen Größe eines lob locator ausgeht

In meiner privaten Script-Sammlung gibt's ein Exemplar, das genau diese Berechnung durchführt, aber dabei den etwas nebulösen Hinweis ergänzt, dass ein niedriger Füllgrad auch Indiz für ein Statistik-Problem sein kann - und dieser Hinweis lässt sich jetzt etwas präzisieren.

Parallelisierung durch ALTER SESSION FORCE PARALLEL QUERY

Alberto Dell'Era erläutert in seinem Blog seine Vorliebe für die Aktivierung paralleler Zugriffe auf Session-Ebene. Hier einige seiner Argumente:

• man kann die Parallelisierung situationsbedingt besser steuern, als das beim Einsatz von Hints der Fall ist (da man nur ein einzelnes Kommando absetzen muss).
• man unterlässt nicht aus Versehen die Parallelisierung von Index-Zugriffen, für die man sonst explizite PARALLEL_INDEX-Hints setzen muss. Ohnehin ist es leicht beim expliziten Angeben von Parallelsierungsgraden Fehler zu machen und Angaben zu vergessen (und neue Indizes zu übersehen etc.).
• gegenüber dem expliziten Setzen des Parallelisierungsgrads auf Objektebene besteht der Vorteil des ALTER SESSION Kommandos darin, dass man nicht so leicht vergisst, einzelne Umstellungen wieder rückgängig zu machen.
• die FORCE-Option ist dabei weniger aggressiv, als der Name andeutet. Verlinkte Erläuterungen der Herren Poder und Lewis zeigen, dass der CBO immer noch die Möglichkeit hat, eine serielle Verarbeitung zu wählen.

Dazu gibt's dann noch einen Test, der das Verhalten im Detail darstellt.

DDL und Rollback

Kann man ein DDL-Kommando über ROLLBACK wieder rückgängig machen? Die Antwort auf diese Frage lautet erstaunlicherweise mal wieder: it depends. Abhängig ist die Antwort in diesem Fall von der verwendeten Datenbank. Für Oracle ist der Fall ganz einfach: ein TRUNCATE TABLE zum Beispiel ist DDL und DDL kann nicht zurückgerollt werden, da darin in implizites Commit enthalten ist:

-- 11.2.0.1
drop table t;

create table t(a number);  
insert into t(a) values(1);  
commit;  

select data_object_id 
  from user_objects
 where object_name = 'T';

DATA_OBJECT_ID
--------------
        102347

truncate table t;  
rollback;  

select data_object_id 
  from user_objects
 where object_name = 'T';

DATA_OBJECT_ID
--------------
        102348

select * from t;  

Es wurden keine Zeilen ausgewählt

Im Fall von Truncate vergibt Oracle dabei auch eine neue data_object_id: intern wird also offenbar eine leere Kopie der behandelten Tabelle angelegt und gegen das Original ausgetauscht (und die Änderung der data_object_id ergibt sich auch, wenn man das Truncate mit einem "reuse storage" verwendet).

Mit dieser Sicht der Dinge steht Oracle aber relativ alleine da, denn viele andere RDBMS haben keine Einwände dagegen, DDL wieder zurückzurollen.

-- SQL Server 2008  
drop table t; 
 
create table t(a int);   
insert into t(a) values(1);  

begin transaction  
truncate table t;  
rollback;  

select * from t;   
  a  
---  
  1  

Welche der beiden Interpretationen ist nun die korrekte - und entspricht genauer den Forderungen irgendeines ANSI SQL Standards? Ich weiß es nicht. Aber merken sollte man sich jedenfalls, dass DDL sich in unterschiedlichen RDBMS unterschiedlich verhält, wozu man in einem Thread bei dba.stackexchange.com und im posgresql-Wiki noch ein paar zusätzliche Details findet.

Thin cloning für Datenbanken

Dass Kyle Hailey ein hervorragendes Blog unterhält, wusste ich schon lange, aber mit seiner Artikelserie zum Thema Database Thin Cloning beweist er seine Meisterschaft: erst schreibt er vier sehr ausführliche und inhaltlich anspruchsvolle Artikel, die ich ziemlich lange in meinem Reader ausgelassen hatte, weil mich ihr Umfang schreckte, und schließlich ergänzt er dann noch einen fünften Artikel Database Thin Cloning: Summary, der die Ausführungen der vorangehenden Texte auf ihre Kernaussagen reduziert, so dass mir das Exzerpieren und individuelle Verlinken einfach sparen kann. Mein Dank ist ihm gewiss...

Outer Join Einschränkungen

Eine Notiz aus dem Kontext der SQL-Grundlagen, die das Verhalten von Einschränkungen in den Klauseln ON und WHERE beim Outer Join behandelt. Sie beantwortet die Frage, wie sich eine Einschränkung der Form "Spalte=Konstante" in diesen beiden Klauseln in einem Outer Joins auswirkt. Der Bezug auf ON macht deutlich, dass hier von ANSI-Syntax die Rede ist, aber das Verhalten ist das gleiche, wenn Oracles (+)-Operator im Spiel ist. Gegeben sind zwei sehr einfache Tabellen:

-- Oracle 11.2.0.1.0; was aber keine Rolle spielen sollte
drop table t1;
drop table t2;

create table t1
as
select rownum id
  from dual
connect by level <= 10;

create table t2
as
select rownum id
     , trunc((rownum + 1)/2) col2
  from dual
connect by level <= 5;

select * 
  from t1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

select *
  from t2;

        ID       COL2
---------- ----------
         1          1
         2          1
         3          2
         4          2
         5          3

Dabei ist t1 also doppelt so groß wie t2. Wenn ich beide Tabellen über die id-Spalten und einen Left Outer Join verknüpfe, dann erhalte ich ein Ergebnis mit den zehn Sätzen aus t1, wobei den ersten fünf Sätzen, die entsprechenden t2-Sätze zugeordnet sind:

select t1.id
     , t2.id
     , t2.col2
  from t1
  left outer join
       t2
    on t1.id = t2.id
 order by t1.id;

   ID         ID       COL2
----- ---------- ----------
    1          1          1
    2          2          1
    3          3          2
    4          4          2
    5          5          3
    6
    7
    8
    9
   10

So weit ist das keine Überraschung. Was aber passiert, wenn ich eine zusätzliche Einschränkung für die zweite Spalte col2 in einer WHERE-Bedingung ergänze?

select t1.id
     , t2.id
     , t2.col2
  from t1
  left outer join
       t2
    on t1.id = t2.id
 where t2.col2 = 1
 order by t1.id;

   ID         ID       COL2
----- ---------- ----------
    1          1          1
    2          2          1

Eine solche Query ist ein ziemlich klares Indiz dafür, dass der Autor nicht genau wusste, was er tat, denn durch die zusätzliche Bedingung wird der Outer Join nutzlos: hier wird zunächst der (Outer) Join erzeugt und anschließend erfolgt die Filterung auf die Ergebnissätze, die der zusätzlichen Bedingung entsprechen. Da aber alle Sätze aus t1, zu denen es in t2 keine Entsprechung gab, für die t2-Spalten NULL-Werte enthalten, kann keine von ihnen der zusätzlichen Bedingung genügen. Die Query liefert also notwendigerweise das gleiche Ergebnis, das ein Inner Join der Tabellen liefern würde. Um die Filterung von t2 vor dem Outer Join durchzuführen (bzw. eigentlich in ihm), muss die Einschränkung in der ON-Klausel erfolgen:

select t1.id
     , t2.id
     , t2.col2
  from t1
  left outer join
       t2
    on t1.id = t2.id and t2.col2 = 1
 order by t1.id;

   ID         ID       COL2
----- ---------- ----------
    1          1          1
    2          2          1
    3
    4
    5
    6
    7
    8
    9
   10

Nachtrag 17.04.2014: inzwischen ist mir klar geworden, dass die hier vorgestellte Interpretation zumindest unvollständig ist - wie hier nachzulesen ist.

Direct Path Reads für serielle Index Fast Full Scans

Dass Oracle in Version 11 unter bestimmten Umständen große serielle Full Table Scans (FTS) mit Hilfe von Direct Path Reads durchführen kann, ist schon häufig erwähnt worden (hier im Blog mindestens in drei Artikeln) - zuletzt hatte Tanel Poder darauf hingewiesen, dass die Entscheidung für diesen Zugriff seit 11.2.0.2 zum Teil auf den Objektstatistiken basiert. Jetzt hat Frits Hogland sich mit dem Verhalten der Direct Path Reads im Fall von Index Fast Full Scans (IFFS) beschäftigt - die mit dem FTS bekanntlich eng verwandt sind: beide Operationen lesen ein komplettes Segment über multiblock I/O. Zunächst nennt er dabei noch einmal die bekannten Voraussetzungen für direct path reads beim seriellen FTS:

• The segment is bigger than 5 * _small_table_threshold.
• Less than 50% of the blocks of the table is already in the buffercache.
• Less than 25% of the blocks in the buffercache are dirty.

Nach einem Beispiel zum Verhalten der FTS folgt dann eine Untersuchung der IFFS und deren Verhalten wird offenbar nicht durch _small_table_threshold, sondern über den Parameter _very_large_object_threshold gesteuert, der dabei eine ähnliche Wirkung zeigt: kurz gesagt erfolgt ein IFFS offenbar, wenn die Größe des Index-Segments > _very_large_object_threshold * 5 ist. Allerdings werden die beiden Parameter in unterschiedlichen Einheiten angegeben (_small_table_threshold in blocks, _very_large_object_threshold anscheinend in megabyte) und die default-Werte sind unterschiedlich definiert (_small_table_threshold dynamisch als 2% der Größe des buffer caches, _very_large_object_threshold statisch als 500; was Sayan Malakshinov in seinem Kommentar allerdings in Frage stellt). Wahrscheinlich ist in diesem Zusammenhang noch weitere Analyse erforderlich.

Neues im SQL Server 2014

Brent Ozar gibt in seinem Blog einen raschen Überblick über ein paar interessante neue Features des SQL Server 2014. Hier eine Auswahl der angesprochenen Punkte:

• Cache frequently used data on SSDs: gespeichert werden in diesem Zusatz-Cache offenbar nur Daten, die nicht von aktuellen DML-Operationen betroffen sind (only clean pages, no dirty pages), so dass es durch die SSDs kein Risiko des Datenverlusts gibt.
• More online maintenance operations: betroffen ist der Neuaufbau von Index-Partitionen, wobei es mit Parametern wie WAIT_AT_LOW_PRIORITY anscheinend möglich ist, auf einen geeigneten Moment zu warten.
• diverse Verbesserungen für die Hochverfügbarkeitslösung AlwaysOn.
• diverse Verbesserungen für die cloud-Lösung Azure.
• Hekaton: specialized in-memory OLTP tables: die neue In-Memory-Erweiterung des SQL Servers, die ich gelegentlich schon mal erwähnt hatte.

Man kann jedenfalls nicht sagen, dass Microsoft sich nicht bemühen würde, den SQL Server weiterzuentwickeln.

Ergänzend hier noch der Verweis auf Microsofts offizielle Kommentare im SQL Server Blog.

SQL Server: Variablenzuweisung im Update

Jamie Thomson zeigt in seinem Blog ein SQL-Feature des SQL Servers, das gar nicht wie SQL aussieht: die Möglichkeit, eine Variable in der SET clause eines Updates zuzuweisen und zu inkrementieren. Das vorgestellte Beispiel dazu ist:

UPDATE [#DimProduct] 
   SET @MaxSurrogateKey = [ProductId] = @MaxSurrogateKey + 1
 WHERE [ProductId] IS NULL; 

Der Spalte ProductId wird also der Wert der Variablen @MaxSurrogateKey + 1 zugewiesen. Ob man damit viel anfangen kann, wäre ein anderes Thema. Interessant (und lustig) ist jedenfalls noch Adam Machanics Kommentar zum Artikel:

Jamie: "If identities were sufficient for all use cases then we wouldn't have sequences?" Wrong. If Oracle didn't have sequences then we wouldn't have sequences.
I'm not sure why you wouldn't use a ROW_NUMBER here. It would be supported, documented, and deterministic. Clever is not always a good thing.

Erinnerungswürdig ist das Feature jedenfalls insofern, als man sich ziemlich darüber wundern könnte, wenn es in freier Wildbahn begegnete.

Nachtrag 03.06.2013: mein Kollege Thomas hat mich gerade darauf hingewiesen, dass ich im Fall der Zuweisung solide daneben gelegen habe (nämlich, dass ProductId auf @MaxSurrogateKey gesetzt werden würde), was jetzt korrigiert ist.