SQL Server 2014: neuer Cardinality Estimator

Benjamin Nevarez weist auf den neuen cardinality estimator hin, der im SQL Server 2014 neben das alte Schätzungsverfahren tritt, dessen initiale Version mit dem SQL Server 7.0 im Dezember 1998 erstmals auftrat. Leider weist der Artikel auch gleich darauf hin, dass es nicht allzu wahrscheinlich ist, dass Microsoft detailliert Auskunft über das neue Verfahren geben wird:

I am not sure if more details about the new cardinality estimator are going to be published by Microsoft. After all, not so many details were ever published about the old cardinality estimator in 15 years; for example, how some specific cardinality estimation is calculated. On the other hand, there are new extended events which we can use to troubleshoot problems with cardinality estimation, or just to explore how it works.

In jedem Fall bleibt festzuhalten, dass Änderungen an den geschätzten Größen von Ergebnismengen massive Wirkungen auf Ausführungspläne haben, so dass es wohl ratsam wäre, die Grundregeln der neuen Schätzungen gelegentlich in Erfahrung zu bringen.

Nachtrag 10.04.2014: ein paar Details zu den Veränderungen liefert der SQL Server Blog - und fasst sie im Summary des Artikels kompakt zusammen. 

Parallel Full Segment Scans über den Buffer Cache

Tanel Poder weist in seinem Blog in zwei Artikeln darauf hin, dass Parallel Execution Slaves vor 11.2 bei der Durchführung von full segment scans (FTS, IFFS) immer direct path reads verwendeten (wobei allerdings zusätzliche single block und multi block Zugriffe aus dem Buffer Cache erfolgen konnten - etwa für chained rows, prefetching etc.). Seit 11.2 gibt es die Möglichkeit, dass solche Operationen komplett über den Buffer Cache durchgeführt werden, was als In-Memory Parallel Execution bezeichnet wird:

• When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 1? weist darauf hin, dass die über den Buffer durchgeführten Scans nicht so recht zu Exadatas Smart Scans passen ("you likely do not want to use In-Memory PX on Exadata") und erklärt, wann In-memory PX aktiv wird.
• When do Oracle Parallel Execution Slaves issue buffered physical reads – Part 2? liefert ein zugehöriges Fallbeispiel inklusive der erforderlichen Analysewerkzeuge (sprich: Scripts).

Plötzliche Änderung von Query-Laufzeiten

Jonathan Lewis hat eine interessante Liste veröffentlicht, die mögliche Gründe für eine plötzliche Verlangsamung einer Query, die vorher deutlich schneller ausgeführt wurde, aufführt - der umgekehrte Fall ist natürlich auch denkbar, führt aber seltener zu Beschwerden.

NULL IS NOT NULL-Filter und Bindevariablen

Der folgende Test verdankt sich wieder einer Fragestellung im OTN-Forum, wobei ich die eigentliche Anfrage möglicherweise etwas frei interpretiert habe. Gegeben ist eine Query, in der in einer inline-View mehrere Subqueries über UNION ALL verknüpft sind, und in der äußeren Abfrage eine Einschränkung auf einen konstanten Wert erfolgt, der einige der Subqueries bedeutungslos macht, da er ihre Ergebnisse filtert. Aber wahrscheinlich erklärt ein Beispiel die Fragestellung sehr viel deutlicher:

drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t2
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t3
as
select rownum col1
  from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 'T1')
exec dbms_stats.gather_table_stats(user, 'T2')
exec dbms_stats.gather_table_stats(user, 'T3') 

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = 'AAA';

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   1 |  VIEW                |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   4 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)

Der Plan zeigt deutlich, dass nur aus T1 Werte ausgelesen werden, während für T2 und T3 ein Filter-Prädikat "filter(NULL IS NOT NULL)" erscheint, das immer false liefern muss. Dazu passt (zumindest ungefähr) auch die Prognose der Größe der Ergebnismenge (rows = 1002). Aber was passiert, wenn statt des Literals ein Bindewert als Einschränkung verwendet wird?

var test varchar2(30);
exec :test := 'AAA';

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = :test

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   1 |  VIEW                |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  5 |    FILTER            |      |       |       |            |          |
|   6 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  7 |    FILTER            |      |       |       |            |          |
|   8 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Anstelle der "filter(NULL IS NOT NULL)" erscheinen hier ganz konventionelle Prädikate, die den Eindruck erwecken, als wäre hier eine frühzeitige Filterung ausgeschlossen - also ein Verzicht auf den Tabellenzugriff nicht möglich (was sich auch in der rows-Angabe 3000 widerspiegelt). Das ist insofern einleuchtend, als der Optimizer zum Zeitpunkt der Optimierung die Bindewerte nicht kennt und folglich auch keine Vorstellung von ihren Filtereigenschaften haben kann. Ein Blick auf die (hier nicht aufgeführten autotrace-) Statistiken der Ausführung macht aber deutlich, dass die Variante mit dem Bindewert die gleiche Anzahl an LIOs hervorruft wie die Variante mit Literal. Offenbar ist der Plan der Bindewert-Query ein wenig irreführend.

Dazu hat Randolf Geist im Thread die Erklärung geliefert:

the FILTER operation is always checked first if it can be fully evaluated without having to refer to the child operations of the FILTER - and in case of the expression :TEST = this is possible - so the underlying operations won't be started if the expression can be evaluated to FALSE. This could be confirmed by running the same test with rowsource statistics enabled (or via Real-Time SQL Monitoring) - the corresponding skipped child operations should be shown with STARTS = 0.

Auf die Idee der Verwendung von rowsource Statistiken hätte ich vielleicht auch kommen können - schließlich empfehle ich diese Technik im OTN-Forum regelmäßig selbst. In jedem Fall hat der Herr Geist recht, was die Vorhersage des Trace-Ergebnisses angeht:

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   1 |  VIEW                |      |      1 |   3000 |   1000 |00:00:00.01 |      71 |
|   2 |   UNION-ALL          |      |      1 |        |   1000 |00:00:00.01 |      71 |
|*  3 |    FILTER            |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   4 |     TABLE ACCESS FULL| T1   |      1 |   1000 |   1000 |00:00:00.01 |      71 |
|*  5 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   6 |     TABLE ACCESS FULL| T2   |      0 |   1000 |      0 |00:00:00.01 |       0 |
|*  7 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   8 |     TABLE ACCESS FULL| T3   |      0 |   1000 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------

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

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Die Filter-Prädikate werden also zur Laufzeit tatsächlich vor dem Zugriff auf das zugehörige Segment ausgewertet und verhindern den Zugriff - was eine effektive Strategie darstellt. Das einzige kleine Problem dabei ist, dass man den Prädikaten dieses Verhalten nicht ansieht.

Join Cardinality bei dynamic sampling

Im OTN Forum wurde heute die Frage gestellt, warum die join cardinality für eine Query mit einem Join zweier Tabellen über Spalten ohne übereinstimmende Werte bei Verwendung von dynamic sampling in 11g komplett falsch berurteilt wurde: statt einer Selektivität von 0 erwartet der Optimizer im Beispiel eine Selektivität von 10%. Ein CBO Trace zur Query erklärt das Verhalten: dynamic sampling liefert zwar akkurate cardinalities, führt aber keine Bestimmung der Wertebereiche der Join-Spalten durch und muss deshalb auf die Standardformel zur Berechnung von join selectivities zurückgreifen. Bei Verwendung persistierter Statistiken kann der Optimizer hingegen die über die HIGH_VALUE und LOW_VALUE-Angaben in user_tab_cols angezeigten Wertebereiche berücksichtigen und feststellen, dass der Join keine Ergebnisse liefern dürfte. Da Jonathan Lewis meine Erklärung mit dem Kommentar "Nice explanation" versehen hat, gehe ich davon aus, dass sie zutreffend ist.

Nachtrag 16.12.2013: die sich im Thread anschließende Diskussion und genauere Analyse des Verhaltens liefert noch eine ganze Reihe interessanter Erkenntnisse:

• bei Verwendung des neuen Auto-Werts (11) für das dynamic sampling gibt es in 12.1 recht merkwürdige Schätzungen, die auf den ersten Blick inkohärent wirken. Ursache ist die Verknüpfung von default-werten für die Basis-Tabellenstatistiken mit einem sampling der join cardinality (wobei im gegebenen Fall eigentlich kein sampling erfolgt, sondern der user query eine nahezu identische count-Operation vorausgeschickt wird). Dabei wird der korrekte Angabe allerdings nicht in allen Fällen zur Überschreibung eingesetzt.
• das CBO-Trace (Event 10053) verschweigt sampling Operationen zur Bestimmung von join cardinalities. Allerdings erscheint im Trace die Angabe "Adjusted Join Cards", die die Überschreibung der berechneten Cardinality durch den ermittelten Wert anzeigt (allerdings ohne Angabe von Gründen). Randolf Geist verweist in diesem Zusammenhang auf das (halbwegs) neue Trace Framework in 11g (das ich mir gelegentlich genauer anschauen sollte).

Ich spare mir an dieser Stelle eine detailliertere Wiedergabe der Analyseschritte und verweise auf den Thread - obwohl der Fall eine meiner besseren detektivischen Leistungen der letzten Monaten gewesen sein dürfte.

compression in 12c

Angesichts der vorhin erwähnten Untersuchung von Julian Dyke habe ich noch mal überprüft, ob sich hinsichtlich des Themas compression in 12c Grundsätzliches verändert hat, und dazu folgendes Script verwendet:

drop table t_no_compress;
drop table t_basic_compress;
drop table t_oltp_compress;

create table t_no_compress
as
select rownum id
     , 10 col1
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 100000;

create table t_basic_compress compress
as
select *
  from t_no_compress;
  
create table t_oltp_compress compress for oltp
as
select *
  from t_no_compress;
  

exec dbms_stats.gather_table_stats(user, 't_no_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_basic_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_oltp_compress', estimate_percent=>0)  

select table_name
     , num_rows
     , blocks 
     , compression
     , compress_for
  from user_tables where table_name in ('T_NO_COMPRESS', 'T_BASIC_COMPRESS', 'T_OLTP_COMPRESS')
 order by table_name;
  
update t_no_compress set col1 = col1 + 1;  
update t_basic_compress set col1 = col1 + 1;  
update t_oltp_compress set col1 = col1 + 1;  

exec dbms_stats.gather_table_stats(user, 't_no_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_basic_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_oltp_compress', estimate_percent=>0)  

select table_name
     , num_rows
     , blocks 
     , compression
     , compress_for
  from user_tables where table_name in ('T_NO_COMPRESS', 'T_BASIC_COMPRESS', 'T_OLTP_COMPRESS')
 order by table_name;

Das Script liefert mir folgende Ergebnisse:

-- 11.1.0.7
-- vor dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T_BASIC_COMPRESS                   100000        162 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        179 ENABLED  OLTP

-- nach dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T_BASIC_COMPRESS                   100000       1138 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        508 ENABLED  OLTP

-- 12.1.0.1
-- vor dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------------------------
T_BASIC_COMPRESS                   100000        151 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        166 ENABLED  ADVANCED

-- nach dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------------------------
T_BASIC_COMPRESS                   100000       1138 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        508 ENABLED  ADVANCED

Ich will nicht behaupten, dass dieser Test alle denkbaren Fragen zum Thema beantwortet, aber auf den ersten Blick wirken die Ergebnisse ähnlich genug, um mich davon zu überzeugen, dass hier keine dramatische Verhaltensänderung im Spiel ist. Eine solche wäre aber vermutlich auch schon irgendwo erwähnt worden.

Noch einmal OLTP compression

Julian Dyke erklärt "why OLTP compression is still always not a good idea." Dazu gibt's ein kompaktes Beispiel, dass einmal mehr zeigt, dass OLTP compression bei reinen Update workloads kontraproduktiv ist.

#describe in sqlplus

Ein nettes kleines sqlplus Feature auf das Neil Chandler dieser Tage in seinem Blog hinweist: mit #desc kann man in sqlplus das describe Kommando aufrufen, während man an einem pl/sql Block arbeitet, ohne dazu die Editieroperation beenden zu müssen:

SQL> declare
  2     cnt number;
  3  begin
  4     select
  5  #desc t
 Name
 ----------------------------------------------------

 COL1

  5            count(distinct col1) into cnt from t;
  6     dbms_output.put_line(cnt);
  7  end;
  8  /
500000

PL/SQL-Prozedur erfolgreich abgeschlossen.

Repräsentation Adaptiver Pläne in ASH

Christian Antognini untersucht in seinem Blog die Darstellung adaptiver Pläne in ASH. Dabei weist er zunächst darauf hin, dass zu den unterschiedlichen Plan-Varianten unterschiedliche plan hash values vorliegen (was keine besondere Überraschung darstellt). Wenn die runtime engine während der Ausführung der Query auf den Alternativplan ausweicht, dann erscheint anschließend der plan hash value der neuen Variante in v$active_session_history. Interessant ist dabei, dass der Wechsel des hash values offenbar mit einer leichten Verzögerung dargestellt wird, also nicht exakt mit der tatsächlichen Plan-Verwendung korrespondiert, was durch das clevere Test-Arrangement sichtbar wird (bei dem für jede verarbeitete Zeile eine Wartezeit eingebaut ist). Abgesehen von dieser Ungenauigkeit entspricht das Verhalten ziemlich genau dem, was ich erwartet hätte, aber es ist immer nützlich, wenn solche Annahmen verifiziert werden können.

BATCHED im Execution Plan für 12c

Im Pythian-Blog schreibt Timur Akhmadeev über seine Tests zur Bestimmung der Bedeutung der Operation

TABLE ACCESS BY INDEX ROWID BATCHED

im Exceution Plan für 12c. Grundsätzlich ist die Sammlung und Sortierung der im Index aufgefundenen rowids vor dem Zugriff auf die Tabelle nichts Neues: sie tritt auch schon in 11g als NL-Join Batching auf - und daneben gibt es noch weitere Prefetching-Mechanismen (etwa im Kontext von db file parallel read). Der Artikel liefert eine ganze Reihe von Detailbeobachtungen, die sich allerdings nicht ohne Weiteres auf einen gemeinsamen Nenner bringen lassen, weshalb ich mir die Nacherzählung erst mal spare und stattdessen das Fazit zitiere:

In 12c Oracle changed some internal code path which deals with the batched table access. But important thing is that the batched table access is not new, so even if you disable it either explicitly with _optimizer_batch_table_access_by_rowid or implicitly with optimizer_features_enable, Oracle will still be able to utilize a similar approach as it was in 11g.

Mal sehen, ob gelegentlich jemand einen eingängigeren Reim darauf findet.