Dynamic Sampling Beobachtungen

Dieser Tage hat Randolf Geist auf der Seite All Things Oracle (wo man eine ziemlich illustre Beiträgerschar versammelt hat) eine Serie zum dynamic sampling gestartet:

• Dynamic Sampling (I), an Introduction – Part 1: erklärt die Grundlagen des Features, darunter folgende zentrale Punkte:
  
• dynamic sampling ruft overhead hervor, da zusätzliche Queries auf rekursiver Ebene abgesetzt werden.
• das Verhalten hängt von der Existenz von Objektstatistiken ab.
• es existieren sanity-checks, die beeinflussen, ob die Ergebnisse des dynamic sampling tatsächlich berücksichtigt werden.
• dynamic sampling erzeugt nur eine relativ schmale Auswahl von Statistik-Informationen (Size in Blocks, Cardinality, Selectivity, NDV)
• dbms_xplan zeigt die Verwendung von  dynamic sampling  (gleiches gilt auch für autotrace und explain plan)
• außerdem erwähnt der Herr Geist noch einige der nützlichen Trace-Hilfsmittel aus 11g
• Dynamic Sampling (I), an Introduction – Part 2: bringt die ausführlichen Test-Scripts, die beim Herrn Geist natürlich nicht fehlen dürfen ("you might wonder if I let you get away without an actual test case, and of course I won’t do so"). Dabei liefert er die Details aus den CBO-Traces (Event 10053)
• Dynamic Sampling (II) – Controlling the Activity – Part 1: erläutert den Zusammenhang zwischen dem Parameter OPTIMIZER_DYNAMIC_SAMPLING und dem Hint DYNAMIC_SAMPLING: auf Statement-Ebene (oder cursor-Ebene) entspricht die Wirkung des Hints der des Parameters, aber auf Tabellen-Ebene ist die Bedeutung eine andere. Der Statement-Level-Hint führt ab Level 3 das Sampling auch dann durch, wenn table statistics existieren, aber nur für den Fall, dass expressions (also Funktionsaufrufe) für (mindestens) eine Bedingung enthalten sind. Ab Level 4 werden auch mögliche Korrelationseffekte überprüft (wenn also mehrere Bedingungen kombiniert sind).
• Dynamic Sampling (II) – Controlling the Activity – Part 2: erläutert, dass auch bei der Verwendung von dynamic sampling immer noch auf die Tabellenstatistiken zugegriffen wird, um basic table size und cardinality zu bestimmen - nur die selectivity der Prädikate wird neu bestimmt, so dass unter Umständen immer noch auf fehlerhafte Angaben zurückgegriffen wird. Um das zu vermeiden, kann man den (nicht wirklich dokumentierten) Hint DYNAMIC_SAMPLING_EST_CDN verwenden, der dazu führt, dass auch die table blocks und base cardinality Angaben durch das dynamic sampling ermittelt werden. Darüber hinaus wird die table-Level-Variante des  DYNAMIC_SAMPLING-Hints untersucht. Diese Variante führt in jedem Fall zum Sampling, also unabhängig von den Bedingungen für den Statement-Level-Hint. Allerdings finden auch in diesem Fall sanity checks statt, so dass das Ergebnis des Samplings nicht in jedem Fall verwendet wird. Ein solcher Fall ist z.B. das Fehlen von Prädikaten in der Query. Es folgen noch Hinweise auf das Verhalten im Fall von Parallelisierung (die Parallelisierung des Samplings kann oberhalb der Parallelisierung der Query liegen) und beim Vorliegen Benutzer-definierter Statistiken (oder Profiles: in diesen Fällen wird dynamic sampling nicht verwendet, in der Annahme, dass sie jemand etwas bei der Statistikdefinition gedacht haben dürfte).
• Dynamic Sampling (III) – Real-Life Data – Part I: beschäftigt sich mit dem Problem der physikalischen Clusterung von Daten (in der Regel basierend auf der Ankunft der Daten in der heap Tabelle), die vom dynamic_sampling nicht sinnvoll berücksichtigt werden kann, da die Anzahl der betrachteten Blocks normalerweise relativ klein ist. Es folgt ein interessantes Fallbeispiel, in dem der Zusammenhang zweier korrelierter Spalten nicht erkannt wird und weder dynamic_sampling, noch extended statistics, noch ein Index für die Spaltenkombination zu brauchbaren cardinality Abschätzungen führt.
• Dynamic Sampling (III) – Real-Life Data – Part II: setzt die Untersuchung des vorangehenden Artikels fort und liefert eine Lösung für das Problem: nämlich die Verwendung des Hints  DYNAMIC_SAMPLING_EST_CDN, der dazu führt, dass die Index-Statistiken des zuvor für die Spaltenkombination definierten Index berücksichtigt werden. Allerdings gibt es eine ganze Reihe von Rahmenbedingungen, die in diesem Fall für die Berücksichtigung der Index-Statistiken gegeben sein müssen - und die ich hier jetzt nicht detailliert nacherzähle.

--------------------------------------------------------------------------------------------------

Vor Veröffentlichung des dritten Artikels hatte ich mich mit der Frage beschäftigt, in welchen Fällen dynamic sampling bei der Verwendung von Hints wirksam wird, wenn bereits Objekt-Statistiken für eine Tabelle existieren (wobei mein Ergebnis nicht vollständig ist, wie man im angesprochenen Artikel nachlesen kann - unter anderem deshalb, weil meine Test-Queries keine Bedingungen enthalten, weshalb der Statement-Level-Hint ziemlich witzlos ist). Dazu ein ganz einfacher Test:

create table test_dynamic_sampling
as
select rownum id
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'test_dynamic_sampling')

exec dbms_monitor.session_trace_enable()

-- ohne Hint
select count(*) 
  from test_dynamic_sampling;

-- Statement-Level-Hint ohne Angabe eines Sampling Levels
select /*+ dynamic_sampling */ 
       count(*) 
  from test_dynamic_sampling;

-- Statement-Level-Hint mit Angabe eines Sampling Levels
select /*+ dynamic_sampling(10) */ 
       count(*) 
  from test_dynamic_sampling;

-- Table-Level-Hint mit Sampling Level 2
select /*+ dynamic_sampling(test_dynamic_sampling 2) */ 
       count(*) 
  from test_dynamic_sampling;

-- Table-Level-Hint mit Sampling Level 10
select /*+ dynamic_sampling(test_dynamic_sampling 10) */ 
       count(*) 
  from test_dynamic_sampling

exec dbms_monitor.session_trace_disable()

Im Trace-File erscheint nur für die Queries mit einem expliziten Tabellen-bezogenen Hint (also die beiden letzten Fälle) die rekursive Query zum dynamic sampling. Diese Queries haben folgende Struktur (hier behutsam formatiert):

-- für /*+ dynamic_sampling(test_dynamic_sampling 2) */
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) 
       opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
       NVL(SUM(C1),:"SYS_B_0")
     , NVL(SUM(C2),:"SYS_B_1") 
  FROM (SELECT /*+ NO_PARALLEL("TEST_DYNAMIC_SAMPLING") FULL("TEST_DYNAMIC_SAMPLING") NO_PARALLEL_INDEX("TEST_DYNAMIC_SAMPLING") */ 
               :"SYS_B_2" AS C1
             , :"SYS_B_3" AS C2 
          FROM "TEST_DYNAMIC_SAMPLING" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "TEST_DYNAMIC_SAMPLING") SAMPLESUB;

-- für /*+ dynamic_sampling(test_dynamic_sampling 10) */
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) 
       NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ 
       NVL(SUM(C1),:"SYS_B_0")
     , NVL(SUM(C2),:"SYS_B_1") 
  FROM (SELECT /*+ NO_PARALLEL("TEST_DYNAMIC_SAMPLING") FULL("TEST_DYNAMIC_SAMPLING") NO_PARALLEL_INDEX("TEST_DYNAMIC_SAMPLING") */ 
               :"SYS_B_2" AS C1
             , :"SYS_B_3" AS C2 
          FROM "TEST_DYNAMIC_SAMPLING" "TEST_DYNAMIC_SAMPLING") SAMPLESUB;

Der - nicht unerwartete - Unterschied der beiden OPT_DYN_SAMP-Queries liegt im Sampling, das im Fall des Levels 10 entfällt, da dabei die komplette Tabelle gelesen wird.

Dass der Hint mit Tabellen-Bezug das dynamic sampling hervorruft, hätte ich wahrscheinlich der Dokumentation entnehmen können, mit Sicherheit aber einem zugehörigen Artikel von Jonathan Lewis.

Nachtrag 30.03.2012: wie schon oben angesprochen hat dieser Test ein paar signifikante Schwächen. Unter anderem erfolgt zwar in den beiden letzten Fällen ein Sampling, aber das Ergebnis wird wieder verworfen, wie man im vierten Artikel der Geist'schen Reihe lesen kann: "The results weren’t used because statistics exist and no predicate is applied for which the selectivity could have been determined using Dynamic Sampling. Since by default only the selectivity is taken from Dynamic Sampling the result of the Dynamic Sampling will be ignored. Now one might argue that performing Dynamic Sampling in such a case is pointless in first place, but that is the way the code currently works." Dass das Sampling nicht greift, zeigt bereits autotrace:

select /*+ dynamic_sampling(test_dynamic_sampling 2) */
       count(*)
  from test_dynamic_sampling;

  COUNT(*)
----------
   1000000

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3738815026

------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |     1 |   433   (3)| 00:00:06 |
|   1 |  SORT AGGREGATE    |                       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_DYNAMIC_SAMPLING |  1000K|   433   (3)| 00:00:06 |
------------------------------------------------------------------------------------

select /*+ dynamic_sampling(test_dynamic_sampling 2) */
       count(*)
  from test_dynamic_sampling
 where id < 1000;

  COUNT(*)
----------
       999

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3738815026

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |     1 |     4 |   436   (3)| 00:00:06 |
|   1 |  SORT AGGREGATE    |                       |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DYNAMIC_SAMPLING |    19 |    76 |   436   (3)| 00:00:06 |
--------------------------------------------------------------------------------------------

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

   2 - filter("ID"<1000)

Note
-----
   - dynamic sampling used for this statement

Nur im zweiten Fall mit einer Bedingung erscheint der Hinweis "dynamic sampling used for this statement" - wobei die Rows-Angabe in diesem Fall ziemlich daneben liegt, was an der Größe des samples liegt und in meinem Test erst ab Level 7 zu einem akkuraten Ergebnis führt. Man könnte hier noch allerlei Beispiele anfügen, aber die findet man auch alle schon in Randolf Geists Artikelserie.