Index Scan und DB File Scattered Reads

Tanel Poder hat vor kurzem erläutert, wie es dazu kommen kann, dass ein index unique scan (oder auch ein index range scan) statt der erwarteten db file sequential reads das eher für FTS oder IFFS übliche Event db file scattered read hervorruft. Die kurze Antwort darauf lautet: Prefetching. Die etwas längere Erklärung zum Prefetching wäre dann:

it’s for buffer cache warmup, where after a restart or a significant buffer cache size increase Oracle decides to populate the empty buffer cache with any data as fast as possible. Oracle assumes that a random multiblock read doesn’t take that much more time than a random single block read anyway, so why not just read a bunch of additional blocks from around that single block that the index access wanted to read. The read operation will be slightly slower, but it might help to avoid multiple future single block reads as they are already in cache. This should affect things only when you restart the instance or significantly increase the buffer cache size. So on a steady state production system you shouldn’t see it.

Eine sehr viel ältere Erläuterung vom Herrn Poder zum gleichen Thema findet man in Anand's Blog.

SQL im Kino

Im Oracle WTF Blog zeigt William Robertson, dass Lisbeth Salander in Peter Finchers Verfilmung von Stieg Larsons Roman Verblendung zwar eine Meister-Hackerin sein mag, aber von SQL nicht arg viel versteht ...

Kosten für Nested Loops Join

Randolf Geist weist in seinem Blog darauf hin, dass die Standardformel für die Kostenberechnung von Nested Loop Joins unter bestimmten Bedingungen durch eine Sonderfallregel überschrieben wird. Die Standardformel lautet dabei:

Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource)

In Fällen, in denen der CBO annimmt, dass die Anzahl der Matches in den Schleifendurchläufen deutlich niedriger ist als die Anzahl der Schleifendurchläufe (was Oracle als "sparse" join bezeichnet und aus dem Verhältnis der NUM_DISTINCT-Werte der Join-Spalte(n) ableitet), greift allerdings eine andere Regel, die die Kosten massiv reduziert, was in Extremfällen dann natürlich wieder extrem falsch sein kann - dann nämlich, wenn die Reduktion der Matches viel geringer ausfällt als aufgrund des Verhältnisses der NUM_DISTINCT-Angaben zu erwarten wäre.

Randolf weist auch noch darauf hin, dass diese Spezialfallregel als Bugfix in 10.1.0.3 eingeführt wurde und über fix control deaktiviert werden kann.

Bitmap Conversion to Rowids

Ursprünglich hatte ich in diesem Eintrag eine umfangreiche Erläuterung zu Problemen des Zusammenspiels von Partition Elimination und lokalen Indizes geliefert, die nur einen kleinen Fehler hatte: das geschilderte Problem gibt es nicht (bzw. es handelte sich nur um einen Costing Effekt, der sich durch einen Hint übersteuern lässt, was mir eigentlich hätte auffallen sollen ...). Um die Menge sinnfreier Beiträge im Internet nicht zu vergrößern, habe ich diese Passage wieder gelöscht und komme gleich zum zweiten Teil meiner Beobachtungen, von dem ich hoffe, dass er etwas solider ist ... Dabei geht es um ein die Kosten der Operation BITMAP CONVERSION TO ROWIDS.

Zunächst der Testaufbau (in 11.2.0.1):

drop table t_partitioned;

create table t_partitioned (
    id  number
  , startdate date
  , col1 number
  , padding varchar2(50)
)
partition by range(startdate)
(
  partition t_partitioned_20120522 values less than (to_date('23.05.2012', 'dd.mm.yyyy'))
, partition t_partitioned_20120523 values less than (to_date('24.05.2012', 'dd.mm.yyyy'))
, partition t_partitioned_maxvalue values less than (maxvalue)
);

insert into t_partitioned
select rownum id
     , to_date('22.05.2012', 'dd.mm.yyyy') - 1 + trunc(rownum/500000) startdate
     , mod(rownum, 4) col1
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;

-- wiederholt, bis die Tabelle 64M rows erreicht.
insert into t_partitioned
select * from t_partitioned;

commit;

create bitmap index t_partitioned_col1_start_bix on t_partitioned(col1, startdate) local;

exec dbms_stats.gather_table_stats(user, 't_partitioned')

Auf dieser Datenbasis setze ich zwei harmlose Queries ab, die - so weit ich sehe - semantisch identisch sind: zunächst lasse ich die distinkten COL1-Werte für einen Tag zählen. Danach eine kleine Umformulierung, bei der die distinkten Werte in einer Subquery ermittelt werden und das Zählen in einer Rahmen-Query erfolgt:

select /*+ monitor test 1 */ 
       count(distinct col1)
  from t_partitioned 
 where startdate = to_date('22.05.2012', 'dd.mm.yyyy');

select /*+ monitor test 2 */
       count(*)
  from (select distinct col1
          from t_partitioned
         where startdate = to_date('22.05.2012', 'dd.mm.yyyy'));

Man sollte annehmen, dass der CBO mit beiden Varianten gut umgehen könnte, aber das ist nicht der Fall, wie die Ergebnisse des SQL Monitor (DBMS_SQLTUNE.REPORT_SQL_MONITOR) zeigen:

-- Test 1
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor test 1 */ count(distinct col1) from t_partitioned where startdate = to_date('22.05.2012', 'dd.mm.yyyy')

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  DBADMIN (67:178)
 SQL ID              :  3mxmz941azrgx
 SQL Execution ID    :  16777216
 Execution Started   :  05/22/2012 21:48:43
 First Refresh Time  :  05/22/2012 21:48:43
 Last Refresh Time   :  05/22/2012 21:48:47
 Duration            :  4s
 Module/Action       :  SQL*Plus/-
 Service             :  testdb
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    4.09 |    3.54 |     0.55 |     1 |   5868 |   62 |  46MB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3761143426)
==================================================================================================================================================================================================
| Id |             Operation              |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |      Activity Detail       |
|    |                                    |                              | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |        (# samples)         |
==================================================================================================================================================================================================
|  0 | SELECT STATEMENT                   |                              |         |      |         1 |     +4 |     1 |        1 |      |       |       |          |                            |
|  1 |   SORT AGGREGATE                   |                              |       1 |      |         1 |     +4 |     1 |        1 |      |       |       |          |                            |
|  2 |    VIEW                            | VW_DAG_0                     |    2602 | 5361 |         1 |     +4 |     1 |        4 |      |       |       |          |                            |
|  3 |     HASH GROUP BY                  |                              |    2602 | 5361 |         4 |     +1 |     1 |        4 |      |       |  840K |    50.00 | Cpu (2)                    |
|  4 |      PARTITION RANGE SINGLE        |                              |    2602 | 5360 |         3 |     +2 |     1 |      32M |      |       |       |          |                            |
|  5 |       BITMAP CONVERSION TO ROWIDS  |                              |    2602 | 5360 |         3 |     +2 |     1 |      32M |      |       |       |    25.00 | Cpu (1)                    |
|  6 |        BITMAP INDEX FAST FULL SCAN | T_PARTITIONED_COL1_START_BIX |         |      |         3 |     +2 |     1 |     5836 |   62 |  46MB |       |    25.00 | db file scattered read (1) |
==================================================================================================================================================================================================

-- Test 2
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor test 2 */ count(*) from (select distinct col1 from t_partitioned where startdate = to_date('22.05.2012', 'dd.mm.yyyy'))

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  DBADMIN (67:178)
 SQL ID              :  512z4htdq43cn
 SQL Execution ID    :  16777216
 Execution Started   :  05/22/2012 21:48:49
 First Refresh Time  :  05/22/2012 21:48:49
 Last Refresh Time   :  05/22/2012 21:48:49
 Duration            :  .019299s
 Module/Action       :  SQL*Plus/-
 Service             :  testdb
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.02 |    0.02 |     0.00 |     1 |   5868 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=4286208786)
=======================================================================================================================================================================
| Id |             Operation             |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                   |                              | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
=======================================================================================================================================================================
|  0 | SELECT STATEMENT                  |                              |         |      |         1 |     +0 |     1 |        1 |       |          |                 |
|  1 |   SORT AGGREGATE                  |                              |       1 |      |         1 |     +0 |     1 |        1 |       |          |                 |
|  2 |    VIEW                           |                              |    2602 | 5361 |         1 |     +0 |     1 |        4 |       |          |                 |
|  3 |     HASH UNIQUE                   |                              |    2602 | 5361 |         1 |     +0 |     1 |        4 |  840K |          |                 |
|  4 |      PARTITION RANGE SINGLE       |                              |    2602 | 5360 |         1 |     +0 |     1 |     5836 |       |          |                 |
|  5 |       BITMAP INDEX FAST FULL SCAN | T_PARTITIONED_COL1_START_BIX |    2602 | 5360 |         1 |     +0 |     1 |     5836 |       |          |                 |
=======================================================================================================================================================================

Also über 4 Sekunden Laufzeit für Fall 1 und Sekundenbruchteile für Fall 2. Dabei sind die Kosten beider Versionen identisch und auch die Zahl der Buffer Gets. Der entscheidende Unterschied liegt offenbar darin, dass im Test 1 sehr viel Zeit in die - anscheinend überflüssige - BITMAP CONVERSION TO ROWIDS gesteckt wird; Test 2 kommt jedenfalls offenbar ohne dieses umständliche Auspacken von 32M Sätzen aus. Dass die BITMAP CONVERSION TO ROWIDS für große Datenmengen kostspielig sein kann, hat übrigens Greg Rahn gelegentlich erläutert. Warum der CBO diese relativ harmlose Transformation nicht durchführt, ist mir allerdings nicht klar.

Nachtrag 05.06.2012: Gestern habe ich den Fall im OTN-Forum präsentiert und von Jonathan Lewis folgende Antwort bekommen: "I think it's just one of those things where the optimizer code is not consistent across all cases." Außerdem liefert ein ein ähnliches Beispiel mit einer IN Subquery.

Die Grenzen von SQL

Iggy Fernandez weist auf die Third International NoCOUG SQL & NoSQL Challenge hin. Das wäre mir erst mal nicht unbedingt einen Eintrag wert, aber ich hatte das Zitat von Steven Feuerstein vergessen, das die Benennung des Siegerpreises August Order of the Wooden Pretzel begründete: "some people can perform seeming miracles with straight SQL, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens."

Index Rebuild

Dass ich gerne ein Buch über Indizes von Richard Foote hätte, habe ich - glaube ich - gelegentlich schon mal erwähnt. Aber so lange er das nicht schreibt, exzerpiere ich eben weiter seine Blog-Artikel ...

Diesmal behandelt der Herr Foote das Thema, auf welches Segment ein Index Rebuild zugreift, sofern Index und Tabelle in einem verwendbaren Zustand sind. Basis der Untersuchung sind dabei 10046er Traces:

• offline rebuild: gelesen wird immer das kleinste Segment, das die erforderlichen Daten enthält. Das kann die Tabelle sein oder der Index, für den der Neuaufbau durchgeführt wird, oder aber auch ein anderer Index, der die relevanten Daten enthält (und kleiner ist als der Index, der neu aufgebaut werden soll, wobei dieser letzte Fall vermutlich nicht extrem wahrscheinlich ist). Der Zugriff auf die  Indizes erfolgt dabei erwartungsgemäß via IFFS. Während des rebuilds wird ein table lock gehalten, so dass keine DML-Operationen auf der Tabelle erfolgen können (was ebenfalls einleuchtet).
• online rebuild: in diesem Fall wird immer die Tabelle gelesen, was höhere I/O-Last erzeugt, aber dafür kein table lock erforderlich macht und DML während des rebuilds erlaubt.

Im Fall der sekundären Indizes von IOTs muss immer das IOT-Segment gelesen werden - und auch das ist keine große Überraschung, da die "rowid guesses" nur aus dem PK kommen können.

Die Prüfung, welches Segment das kleinste und damit die geeignetste Grundlage für den Neuaufbau ist, basiert offenbar nicht auf der Block-Angabe in den Tabellen- bzw. Index-Statistiken - jedenfalls reagiert Oracle nicht auf irreführende Werte, die über dbms_stats.set_table_stats eingeführt werden. Richard Foote erklärt dazu in einem Kommentar: "An index rebuild is an expensive operation so it will be worth doing the necessary homework beforehand to get it right.".

Oracle Tools

Auf der Seite Tools habe ich damit begonnen, ein paar Links auf nützliche Scripts und Utilities aufzuführen, die ich mir bisher anderswo gemerkt hatte.

CPU-Verwendung durch REGEXP

Dom Brooks weist darauf hin, dass die CPU-Verwendung von regulären Ausdrücken in SQL recht hoch ist und dass man diesen Sachverhalt (wie viele andere Dinge) sehr gut über das sql monitoring beobachten kann

Linux Load Average

Harald van Breederode hat eine Serie zur Semantik des Linux Load Average begonnen:

• Understanding Linux Load Average – Part 1: "Based on all these experiments we can conclude that CPU utilization is clearly influencing the load average of a Linux system. If the load average is above the total number of processors in the system we could conclude that the system is overloaded but this assumes that nothing else influences the load average. Is CPU utilization indeed the only factor that drives the Linux load average?"
• Understanding Linux Load Average – Part 2: "So far we have proven that both CPU utilization and disk I/O influences the load average on a Linux system. The question remains if they are the only factors driving the load average? Or are there other factors to consider?"
• Understanding Linux Load Average – Part 3: "processes performing network I/O have an effect on the Linux Load Average"

Daraus schließt der Herr van Breederode: "Thus the 3 factors that drive the Load Average on a Linux system are processes that are on the run-queue because they:

• Run on, or are waiting for, the CPU
• Perform disk I/O
• Perform network I/O"

PK-Indizes

Jonathan Lewis hat vor kurzem eine Möglichkeit vorgestellt, um feststellen zu können, ob ein Index zur Unterstützung eines PK- oder UK-Constraints im Rahmen der Constraint-Erzeugung oder schon frühen angelegt wurde, was darüber entscheidet, ob er im Fall eines DROP CONSTRAINT-Kommandos ebenfalls gelöscht wird. Die Information findet man in der Spalte property der Tabelle ind$.

Join Elimination und vertikale Partitionierung

Beim Nachdenken über die Möglichkeiten der Join-Elimination ist mir der Gedanke gekommen, dass man darüber eine Art vertikaler Partitionierung definieren könnte. Hier ein einfaches Beispiel dazu (in 11.1.0.7). Darin existieren drei Dimensionstabellen unterschiedlicher Granularität, die somit eigentlich sehr unterschiedliche Mengen enthalten würden. Würde man sie in ihrer natürlichen hierarchischen Ordnung miteinander verknüpfen, dann könnte eine Join Elimination nur die Parent-Tabellen in FK-Beziehungen ausschließen.

Wenn man diese Tabellen aber denormalisiert (was im DWH-Kontext ja nicht unüblich ist), indem man sie mit dem Schlüssel der Tabelle mit den meisten Sätzen erweitert (im Beispiel DIM_VARIANTEN), dann erhält man eine 1:1-Beziehung zwischen allen Dimensionen. Wenn man dann zwischen den Schlüsselspalten noch wechselseitige Foreign Key-Beziehungen definiert, kann sich der Zugriff immer auf die jeweils relevante Datengrundlage beschränken:

-- Test_Join_Elimination.sql
-- Loeschung aller zugehoerigen Objekte
alter table dim_artikel drop constraint fk_a_v;
alter table dim_artikel drop constraint fk_a_s;

alter table dim_sortiment drop constraint fk_s_v;
alter table dim_sortiment drop constraint fk_s_a;

alter table dim_variante drop constraint fk_v_s;
alter table dim_variante drop constraint fk_v_a;

drop table dim_sortiment;
drop table dim_variante;
drop table dim_artikel;

-- Anlage der Dimensionstabellen mit jeweils 100M Saetzen
create table dim_variante tablespace test_ts
as
select rownum var_id
  from dual 
connect by level <= 100000;

create table dim_artikel tablespace test_ts
as
select rownum var_id
     , substr(rownum, 1, 4) art_id
     , mod(rownum, 4) + 1 saison
  from dual 
connect by level <= 100000;

create table dim_sortiment tablespace test_ts
as
select rownum var_id
     , mod(rownum, 100) + 1 wgr_id
     , 'WGR ' || to_char(mod(rownum, 100) + 1) wgr_name   
  from dual 
connect by level <= 100000;

-- Anlage von PKs und FKs
alter table dim_variante add constraint pk_dim_variante primary key (var_id);
alter table dim_artikel add constraint pk_dim_artikel primary key (var_id);
alter table dim_sortiment add constraint pk_dim_sortiment primary key (var_id);

alter table dim_artikel add constraint fk_a_v foreign key (var_id) references dim_variante(var_id);
alter table dim_artikel add constraint fk_a_s foreign key (var_id) references dim_sortiment(var_id);

alter table dim_sortiment add constraint fk_s_v foreign key (var_id) references dim_variante(var_id);
alter table dim_sortiment add constraint fk_s_a foreign key (var_id) references dim_artikel(var_id);

alter table dim_variante add constraint fk_v_s foreign key (var_id) references dim_sortiment(var_id);
alter table dim_variante add constraint fk_v_a foreign key (var_id) references dim_artikel(var_id);

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'DIM_ARTIKEL')
exec dbms_stats.gather_table_stats(user, 'DIM_SORTIMENT')
exec dbms_stats.gather_table_stats(user, 'DIM_VARIANTE')

-- View-Anlage
create or replace view dim_variante_gesamt
as
select v.var_id 
     , a.art_id
     , s.wgr_id
     , s.wgr_name
     , a.saison
  from dim_variante v 
     , dim_sortiment s
     , dim_artikel a
 where v.var_id = a.var_id
   and a.var_id = s.var_id;

-- Ermittlung der Pläne
explain plan for
select distinct saison
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct wgr_name
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct var_id
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct wgr_name, saison
  from dim_variante_gesamt;
  
select * from table(dbms_xplan.display(null, null, 'basic'));

Das Script liefert folgendes Ergebnis:

PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 1748037202

------------------------------------------
| Id  | Operation          | Name        |
------------------------------------------
|   0 | SELECT STATEMENT   |             |
|   1 |  HASH UNIQUE       |             |
|   2 |   TABLE ACCESS FULL| DIM_ARTIKEL |
------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 762726623

--------------------------------------------
| Id  | Operation          | Name          |
--------------------------------------------
|   0 | SELECT STATEMENT   |               |
|   1 |  HASH UNIQUE       |               |
|   2 |   TABLE ACCESS FULL| DIM_SORTIMENT |
--------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 674027647

------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|   1 |  TABLE ACCESS FULL| DIM_VARIANTE |
------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 1066181588

---------------------------------------------
| Id  | Operation           | Name          |
---------------------------------------------
|   0 | SELECT STATEMENT    |               |
|   1 |  HASH UNIQUE        |               |
|   2 |   HASH JOIN         |               |
|   3 |    TABLE ACCESS FULL| DIM_ARTIKEL   |
|   4 |    TABLE ACCESS FULL| DIM_SORTIMENT |
---------------------------------------------

Demnach werden tatsächlich jeweils nur die Tabellen gelesen, die die erforderlichen Daten enthalten. Auf diese Weise kann man eine große Tabelle mit mehreren relativ unabhängigen Spaltengruppen (die ihrerseits eng miteinander zusammenhängen) in mehrere Segmente aufsplitten: also in gewisser Weise vertikal partitionieren. Allerdings wird in vielen Fällen die Verwendung geeigneter Indizes wahrscheinlich die näher liegende Vorgehensweise sein. Nachdem ich dieses Beispiel gebastelt hatte, kam mir dann auch noch der Gedanke, an den üblichen Stellen nach ähnlichen Beispielen zu suchen - und wahrscheinlich hält sich die Überraschung in Grenzen, wenn ich verrate, dass der Herr Kyte etwas Passendes im Angebot hat.

Join Elimination

Ein paar Stichworte zum Thema Join Elimination, also der Möglichkeit, einen Tabellenzugriff aus einer Abfrage heraus zu kürzen. Zunächst einige Links:

• Why are some of the tables in my query missing from the plan? - der Blog der CBO-Entwickler liefert eine Einführung zum Thema. Darin findet sich ein kleines Code-Beispiel und eine Erläuterung zu den verschiedenen Formen der Join-Elimination:
• seit 10.2 gibt es die Primary Key-Foreign Key Table Elimination, bei der der CBO auf Tabellen-Zugriffe verzichtet, die aufgrund der definierten FK-Beziehungen redundant sind.
• mit 11.1 wurde die Outer Join Table Elimination eingeführt, die keine FK-Beziehungen benötigt, sondern nur einen UK-Constraint auf der Join-Column der äußeren Tabelle (also der, die um NULL-Sätze ergänzt wird). Diese kann dann aus der Query herausgenommen werden, wenn keine ihrer Spalten in der Select-Liste erscheint.
• zu den Einschränkungen des Features gehört, dass multi-column PK-/FK-Constraints nicht unterstützt werden.
• Join Elimination - von Christian Antognini:
• erklärt auch nochmal anhand eines Beispiels die Primary Key-Foreign Key Table Elimination.
• seit 11.2 gibt es die Möglichkeit der Elimination unnötiger Self-Joins, die über den PK definiert sind.
• Join Views, ROWIDs And Query Transformations von Randolf Geist:
• daraus hatte ich hier schon mal ein paar wichtige Punkte aufgelistet.

Allen Varianten der Join Elimination ist offenbar gemeinsam, dass sichergestellt sein muss, dass:

• keine Informationen aus der eliminierten Tabelle in der Select-Liste benötigt werden (das ist jetzt keine große Überraschung ...)
• der Join keine Veränderung der Ergebnismenge bewirken kann, also keine Vergrößerung durch 1:n-Beziehungen und keine Verkleinerung durch 1:0-Verknüpfungen. Dazu müssen die Werte der Join-Spalte in der zu eliminierenden Tabelle UNIQUE sein (durch PK- oder UK-Constraint). In diesen Kontext gehört auch das Konzept der "Key-Preserved Tables", die einen Zugriff über ROWID erlauben, das in Randolf Geists Artikel ausführlich erläutert wird.

Daraus ergibt sich dann auch, dass in der Regel nur die Parent-Tabellen aus hierarchischen Tabellenbeziehungen eliminiert werden können und dass die Tabelle mit der höchsten Granularität erhalten bleibt: beim Join einer Artikel-Dimensionstabelle mit einer Sortiments-Dimensionstabelle kann also nur die übergeordnete Sortiments-Dimension eliminiert werden - ein Ausschluss der Artikel-Dimension ist auch dann nicht möglich, wenn ein Zugriff nur distinkte Werte der Sortiments-Dimension benötigt.

Einige der Beispiele zum Feature sehen nach außergewöhnlich sinnlosem SQL aus - etwa der OUTER JOIN ohne Zugriff auf die über outer join verknüpfte Tabelle. Aber für generierten SQL-Code oder für Snowflake-Dimensionen im DWH-Kontext könnte es ganz nützlich sein.

Neue Funktionen im SQL Server 2012

Tom Kyte pflegt - aus guten Gründen - darauf hinzuweisen, dass jedes RDBMS seine besonderen Eigenschaften hat, dass man mit den Best Practices des einen Systems im anderen System übel Schiffbruch erleiden kann (hier eines seiner Lieblingsbeispiele dazu) - und dass Datenbankunabhängigkeit Blödsinn ist. Ich sehe das durchaus ähnlich, aber finde es doch nett, dass die RDBMS-Architekten sich von den Möglichkeiten anderer Datenbanken inspirieren lassen (im Sinne des Dialogs eines frühen Woody-Allen-Films, in dem der Filmregisseur auf die Frage, ob eine Szene als Hommage an ein klassisches Werk zu verstehen sei, die schöne Antwort gibt: "Nein, wir haben bloß die Idee geklaut").

Aber zum Thema: Johan Ahlen (auf das bolle-Zeichen über dem A und den Akzent auf dem e verzichte ich ohne besondere Begründung) weist in seinem Blog auf ein paar schöne neue Features im SQL Server 2012 hin, nämlich tabellenunabhängige Sequenzen, die IIF-Funktion, die Format-Optionen von T-SQL ("allows you to customize the way numbers, dates, etc are converted to strings") und neue analytische Funktionen (z.B. FIRST_VALUE). Die Syntax etwa zu den Sequences ist zwar nicht identisch mit der in Oracle, aber doch so ähnlich, dass man sich recht schnell daran gewöhnen sollte.

Neue SSAS Profiler Informationen in 2012

Chris Webb erläutert in seinem Blog einige neue Counter mit Cache-Bezug, die im Event Query Subcube Verbose im SQL Server 2012 angezeigt werden. Da ich die Instrumentierung des SSAS immer noch für dürftig halte (im Vergleich zu dem, was ein vernünftiges RDBMS dieser Tage an Ausführungsinformationen und Statistiken liefert), bin ich für jede Ergänzung dankbar.

Basket Analysis mit DBMS_FREQUENT_ITEMSET

Peter Scott zeigt im Rittman/Mead-Blog wie man eine Warenkorbanalyse mit Oracle-Bordmitteln durchführen kann: nämlich mit Hilfe des Packages DBMS_FREQUENT_ITEMSET. Damit kommt man dann zu solchen Klassikern wie Bier&Windeln oder Grabkerzen&Kukident Haftcreme.

Profiles und Baselines

Maria Colgan liefert im Blog der CBO Entwickler eine kompakte Erläuterung der unterschiedlichen Rollen von Profiles (= zusätzliche Informationen für den CBO; Jonathan Lewis spricht in diesem Zusammenhang gerne von offline optimization) und Baselines (= eine Sammlung akzeptierter Pläne, von denen nur der beste zum Einsatz kommt).

Dass relativ viele Leute Probleme mit der Einordnung dieser Werkzeuge haben, liegt allerdings vielleicht auch daran, dass Oracle für das Plan Management recht viele unterschiedliche Hilfsmittel anbietet: neben den Profiles und den Baselines gibt's ja auch noch die (älteren) Outlines und die SQL Patches, zu denen Dom Brooks zuletzt einiges geschrieben hat.

Join Selectivity und Statistiken

Dieser Tage bin ich auf folgenden Effekt hereingefallen: gegeben war ein JOIN einer kleineren temporären Tabelle mit Bonpositionen und einer großen partitionierten Tabelle mit den zugehörigen Headern, der für ältere historische Daten ganz problemlos gelaufen war. Vor ein paar Tagen kippte dann der Plan beim Zugriff auf aktuellere Header und ich versuchte herauszubekommen, was passiert war (allerdings nicht, ehe ich erst mal einige Zeit damit verschwendete, ihn mit allerlei Hints wieder zurück zu biegen). Was ich sah, habe ich hier auf ein ganz simples Beispiel reduziert:

In meinem Beispiel sind die Tabellen gleichgroß und nicht partitioniert, da das eigentlich gar keine Rolle spielte. Die Inhalte sind auch alles andere als aufregend:

select col1
     , count(*)
  from t_2
 group by col1

 COL1   COUNT(*)
----- ----------
    1       5000
    0       5000

select col1
     , count(*)
  from t_4
 group by col1

 COL1   COUNT(*)
----- ----------
    1       2500
    2       2500
    3       2500
    0       2500

Zunächst die Query und der Plan:

set autot on

select count(*)
  from t_2
     , t_4
 where t_2.col1 = t_4.col1;

  COUNT(*)
----------
  25000000

Abgelaufen: 00:00:01.29

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |            |          |
|*  2 |   HASH JOIN         |      |     1 |     6 |    25   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T_2  | 10000 | 30000 |    12   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T_4  | 10000 | 30000 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("T_2"."COL1"="T_4"."COL1")

Laut Plan liefert der HASH JOIN in STEP 2 also nur einen Satz, tatsächlich aber sind es 25000000. Wenn ich die Standard-Formel zur Berechnung von Join-Cardinalities heranziehe, dann liefert sie:

Join Selectivity
   = ((num_rows(t1) - num_nulls(t1.c1))/num_rows(t1)) *
     ((num_rows(t2) - num_nulls(t2.c2))/num_rows(t2)) /
     greater (num_distinct(t1.c1),  num_distinct(t2.c2))

Join Cardinality
    = Join Selectivity * filtered cardinality (t1) * filtered cardinality (t2)

Ohne NULL-Werte ergibt sich für die Cardinality:

10000 * 10000 / greatest (2, 4) = 25000000

Das wäre also exakt richtig gewesen. Woher also die 1?

Dazu noch die Statistiken:

select table_name
     , num_rows
  from user_tables
 where table_name in ('T_2', 'T_4');

TABLE_NAME   NUM_ROWS
---------- ----------
T_2             10000
T_4             10000

select table_name
     , column_name
     , num_distinct
     , density
  from user_tab_cols
 where table_name in ('T_2', 'T_4')
   and column_name = 'COL1';

TABLE_NAME COLUMN_NAME NUM_DISTINCT    DENSITY
---------- ----------- ------------ ----------
T_2        COL1                   2         ,5
T_4        COL1                   4        ,25

Sieht erst mal ganz plausibel aus - ist es aber natürlich nicht. Deshalb hier noch das CREATE Script zum Fall:

drop table t_2;

-- initiale Füllung von T_2 mit Daten, die nicht zu T_4 passen
create table t_2
as
select rownum id
     , mod(rownum, 2) + 4 col1
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'T_2')

delete from t_2;

-- erst jetzt kommen die Daten, die zu den Werten in T_4 passen
insert into t_2
select rownum id
     , mod(rownum, 2) col1
  from dual
connect by level <= 10000;

commit;

drop table t_4;

create table t_4
as
select rownum id
     , mod(rownum, 4) col1
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'T_4')

select *
  from user_tables
 where table_name in ('T_2', 'T_4');

set autot on

select count(*)
  from t_2
     , t_4
 where t_2.col1 = t_4.col1;

Im praktischen Fall waren die Partitionsstatistiken akkurat, aber die globalen Statistiken mit den HIGH_VALUE und LOW_VALUE-Werten zu den Spalten der Header-Tabelle waren uralt, so dass die Überlappung mit den Spaltenwerten der Positionstabelle irgendwann aufhörte. Für die Positionstabelle hatte ich die Statistiken selbst erzeugt. Relevant waren also eher folgende Statistiken:

select table_name
     , column_name
     , last_analyzed
     , low_value
      , high_value
  from user_tab_cols
 where table_name in ('T_2', 'T_4')
   and column_name = 'COL1';

TABLE_NAME COLUMN_NAME LAST_ANALYZED       LOW_VALUE            HIGH_VALUE
---------- ----------- ------------------- -------------------- ----------
T_2        COL1        08.05.2012 18:42:40 C105                 C106
T_4        COL1        08.05.2012 18:42:41 80                   C104

Wobei das last_analyzed-Datum im Test nicht arg viel aussagt. Aber die Wertebereiche der Spalten sind überschneidungsfrei und das bringt den CBO unmittelbar zum - einleuchtenden - Schluss, dass der Join kein Ergebnis liefern wird (woraus er dann die 1 macht). Einmal mehr genügt es also, korrekte Statistiken zu liefern, um vom CBO korrekte Abschätzungen zu bekommen (wobei im gegebenen Fall aus der 1 dann allerlei unglückliche NL Operationen folgten):

exec dbms_stats.gather_table_stats(user, 'T_2')

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |    30   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |            |          |
|*  2 |   HASH JOIN         |      |    25M|   143M|    30   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T_2  | 10000 | 30000 |    17   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T_4  | 10000 | 30000 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("T_2"."COL1"="T_4"."COL1")

Da mir die Header-Tabelle nicht gehört, habe ich allerdings nicht ihre, sondern die Statistiken der Positionstabelle angepasst und dazu dbms_stats.set_column_stats verwendet, wie Tony Hasler es in seinem Blog vor einiger Zeit vorgeführt hat.

Eine Alternative wäre übrigens wahrscheinlich die Anlage von Histogrammen in der Positionstabelle gewesen, wie man (natürlich) bei Jonathan Lewis in Kapitel 10: Join Cardinality von Cost-Based Oracle nachlesen kann (S. 280ff.). In meinem Beispiel funktioniert das allerdings nur, wenn ich für beide Tabellen Histogramme erzeuge, dann allerdings unabhängig von der Überschneidung der Wertebereiche (was vermutlich so nicht beabsichtigt ist).

HASH JOIN Arbeitsweise

Bei der neuerlichen Lektüre des Kapitels 12 in Cost-Based Oracle von Jonathan Lewis wurde mir klar, dass es nicht schaden würde, einige der dort aufgeführten Punkte noch mal aufzuschreiben:

• Bei der Durchführung eines HASH JOINs wird die INPUT-Menge mit Hilfe einer internen hashing Funktion in das Äquivalent eines in-memory single-table hash clusters konvertiert (sofern genügend Speicher zur Verfügung steht)
• Anschließend werden die Daten der PROBE Menge gelesen und die hashing Funktion wird für jeden Satz auf die Join Spalte(n) angewendet, um zu prüfen, ob es einen entsprechenden Satz im in-memory hash cluster gibt.
• Aufgrund der Verwendung der hashing Funktion kann ein HASH JOIN nur bei equality Bedingungen verwendet werden (und bei not exists), aber nicht bei range Bedingungen.
• einschränkende Bedingungen auf eine der beiden Mengen können einen Index-Zugriff erlauben; die Join-Bedingung kann aber nicht zum indizierten Zugriff auf die PROBE Menge verwendet werden.
• als INPUT wird die kleinere Menge verwendet, bestimmt über Anzahl Sätze * Satzlänge (= avg_col_len in user-tab_columns); wobei es Unterschiede zwischen der Statistik-Ermittlung mit dbms_stats und mit ANALYZE gibt (Stichwort: length byte).
• da die Join-Bedingung nicht als Restriktion für die PROBE-Menge betrachtet wird, erfolgt häufig keine Partition Elimination im Fall einer partitionierten Tabelle (der CBO kann im Rahmen der Transformation allerdings eine pruning subquery ergänzen).
• Wenn die INPUT Menge kleiner als die für die Session verfügbare hash_area_size ist (bzw. der über automatisches Speichermanagement zugewiesene Speicher), dann fällt der HASH JOIN in die Kategory OPTIMAL der workarea executions. Größere Mengen werden über ONEPASS oder MULTIPASS Operationen verarbeitet.
• Die Prüfung der Join Matches erfolgt auf folgendem Weg
• Beim Lesen der INPUT Menge baut Oracle ein Array von hash buckets im Arbeitsspeicher auf; um hash Kollisionen zu vermeiden, verwendet Oracle von vornherein relativ viel Speicher, um die Daten auf eine möglichst große Anzahl von Buckets verteilen zu können.
• Die PROBE Menge wird (über den geeignetsten Mechanismus) gelesen und der für einen Satz passende Bucket ermittelt.
• Wenn dieser Bucket leer ist, kann der Satz ignoriert werden. Ist er es nicht, so muss ein exakter Vergleich der Werte erfolgen, da die Möglichkeit der hash collision besteht. Ob ein bucket leer ist oder nicht, wird in einer bitmap Struktur erfasst.
• Details zum HASH JOIN liefert Event 10104.
• Der verfügbare Arbeitsspeicher (hash_area_size für manuelles Speichermanagement) wird in einzelne Partitionen aufgeteilt.

Die Details zu den ONEPASS- und MULTIPASS-Operationen liefere ich (wahrscheinlich) später nach.

LOB Speicherung

Eigentlich wollte ich heute mal wieder ein wenig experimentieren und die Ergebnisse hier dokumentieren, aber leider ist dabei nichts Substantielles herausgekommen... Stattdessen also wieder ein Link: diesmal auf Tanel Poders Erklärung zur Speicherung von LOBs - inklusive instruktiver Grafik.

Partition Exchange für Interval Partitioning

Dani Schnider von Trivadis erläutert, wie man Partition Exchange für eine Tabelle mit Intervall-Partitionierung verwenden kann. Der schwierigste Teil dabei ist die Bestimmung der passenden Partition und ihres vom System generierten Namens.