Freitag, Juni 29, 2012

Lokale bitmap Indizes und COUNT(*)

Zu den Vorzügen von Bitmap Indizes gehört, dass sie NULL-Werte enthalten und deshalb sehr gut zum Zählen der Datensätze einer Tabelle geeignet sind. Dieser Vorteil wird dadurch noch verstärkt, dass ein Bitmap Index in der Regel relativ klein ist - für entsprechend geclusterte Daten jedenfalls deutlich kleiner als ein adäquater B*Tree-Index. Dieser Tage ist mir allerdings aufgefallen, dass ich mir zum Zusammenspiel von lokalen Bitmap Indizes mit COUNT-Queries bisher falsche Hoffnungen gemacht habe. Meine Idee dabei war folgende:
  • Eine partitionierte Tabelle gliedert ein größeres Segment in kleinere Teil-Segmente.
  • Lokale Indizes beschränken sich immer auf eine Partition.
  • Deshalb sollte eine Query der Form: SELECT COUNT(*) FROM partitioned_table WHERE partition_key = ... dazu in der Lage sein, zunächst durch partition elimination das passende Tabellen-Segment und seine Indizes zu ermitteln und dann einen beliebigen bitmap index zu verwenden, um daraus die erforderlichen Daten zu ziehen, ohne dabei auf die Tabelle zugreifen zu müssen.
Das schien mir plausibel, ist es aber anscheinend nicht - jedenfalls funktioniert es so nicht:

drop table test_partition_bidx;
create table test_partition_bidx
( id number
, startdate date
, col1 number
, padding varchar2(100) 
)
   partition by range (startdate)
  (partition p1 values less than ( to_date('30.06.2012','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.07.2012','dd.mm.yyyy')),
   partition p3 values less than ( to_date('02.07.2012','dd.mm.yyyy')),
   partition p4 values less than ( to_date('03.07.2012','dd.mm.yyyy')),
   partition p5 values less than ( to_date('04.07.2012','dd.mm.yyyy'))
  );

-- Daten einfügen
insert into test_partition_bidx
select rownum
     , to_date('30.06.2012','dd.mm.yyyy') + trunc((rownum -1)/200000) 
     , trunc(rownum - 1/10000)
     , lpad('*', 100, '*') 
  from dual 
connect by level <= 1000000;

-- Statistiken erzeugen
exec dbms_stats.gather_table_stats(user, 'test_partition_bidx')

-- bitmap index anlegen
create bitmap index ix_test_partition_bidx on test_partition_bidx(col1) local;

-- Query-Ausführung mit autotrace
set autot on
SQL> select count(*) from test_partition_bidx where startdate = '01.07.2012';

  COUNT(*)
----------
    250000

Abgelaufen: 00:00:00.35

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

---------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |     1 |     8 |  1199   (1)| 00:00:15 |       |       |
|   1 |  SORT AGGREGATE         |                     |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                     |   250K|  1953K|  1199   (1)| 00:00:15 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL    | TEST_PARTITION_BIDX |   250K|  1953K|  1199   (1)| 00:00:15 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------

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

   3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4360  consistent gets
       4354  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Demnach funktioniert die partition elimination, aber der Zugriff erfolgt dann über FTS, obwohl der Index deutlich kleiner ist als die Tabelle (laut DBA_SEGMENTS: 3976 BLOCKS im Index gegenüber 17928 BLOCKS in der Tabelle).

Wenn man dem CBO den Index-Zugriff per Hint nahelegt, ergibt sich Folgendes:

select /*+ index (t) */ count(*) from test_partition_bidx t where startdate = '01.07.2012';

Abgelaufen: 00:00:03.35

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

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |     8 |  7873   (1)| 00:01:35 |       |       |
|   1 |  SORT AGGREGATE                     |                        |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                        |   250K|  1953K|  7873   (1)| 00:01:35 |     3 |     3 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PARTITION_BIDX    |   250K|  1953K|  7873   (1)| 00:01:35 |     3 |     3 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                        |       |       |            |          |       |       |
|   5 |      BITMAP INDEX FULL SCAN         | IX_TEST_PARTITION_BIDX |       |       |            |          |     3 |     3 |
------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5185  consistent gets
       5142  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Der Hint erzwingt den Index-Zugriff - aber nicht in gewünschter Form: statt eines INDEX FAST FULL SCANs erfolgt ein INDEX FULL SCAN und anschließend wird eine teuere BITMAP CONVERSION TO ROWIDS durchgeführt, gefolgt von einem TABLE ACCESS BY LOCAL INDEX ROWID, der eigentlich nicht erforderlich sein sollte. Die Zahl der LIOs ist dabei dann höher als für den FTS-Fall und die Laufzeit steigt dramatisch. Der Versuch, den Zugriff über einen INDEX_FFS-Hint auf einen INDEX FAST FULL SCAN zu lenken, bringt leider keine Besserung: in diesem Fall wählt der CBO wieder den FTS.

Um das Zählen der Sätze in den Partitionen zu beschleunigen, hilft ein bitmap Index auf dem partition key:


create bitmap index ix2_test_partition_bidx on test_partition_bidx(startdate) local;

select count(*) from test_partition_bidx where startdate = '01.07.2012';

Abgelaufen: 00:00:00.03

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

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |     8 |     6   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                |                         |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE       |                         |   250K|  1953K|     6   (0)| 00:00:01 |     3 |     3 |
|   3 |    BITMAP CONVERSION COUNT     |                         |   250K|  1953K|     6   (0)| 00:00:01 |       |       |
|*  4 |     BITMAP INDEX FAST FULL SCAN| IX2_TEST_PARTITION_BIDX |       |       |            |          |     3 |     3 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          7  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Hier ergibt sich dann das, was ich mir gewünscht habe: ein INDEX FAST FULL SCAN mit anschließendem BITMAP CONVERSION COUNT und ohne BITMAP CONVERSION TO ROWIDS und Tabellen-Zugriff. Die LIOs sinken auf übersichtliche 12 und die Laufzeit ist geringfügig.

Aber warum funktioniert das nur mit einem bitmap Index, der den partition key enthält (geeignet wäre auch ein mehrspaltiger bitmap index, der den partition key enthält)? Die Antwort lautet: ich weiß es nicht. Anscheinend verknüpft der CBO in diesem Fall nicht alle Informationen, die ihm zur Verfügung stehen und führt deshalb im Step 3 TABLE ACCESS BY LOCAL INDEX ROWID die Filterung filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) durch, die eigentlich redundant ist, weil sie bereits durch die partition elimination sichergestellt ist. Bis mir jemand eine bessere Erklärung liefert, gehe ich davon aus, dass es sich hier wieder um einen Fall handelt, in dem der CBO weniger clever ist, als er sein könnte.

Nachtrag 03.07.2012: Die Erklärung des Verhaltens liefert Randolf Geist in seinem Kommentar zum Artikel: ich weiß zwar, dass jede Partition nur einen Tag enthält, aber der CBO kann das nicht wissen und muss die bitmap Struktur deshalb auspacken. Mit der von Randolf vorgeschlagenen LIST-Partitionierung bekomme ich ohne Hints immer noch einen FTS, aber mit Index-Hint ergibt sich ein INDEX FULL SCAN mit folgendem BITMAP CONVERSION COUNT.

Sonntag, Juni 24, 2012

FIXED_DATE

Mike Smithers weist in seinem Blog auf den Parameter FIXED_DATE hin, mit dessen Hilfe man das aktuelle Systemdatum für die Datenbank überschreiben kann, so dass ein "select sysdate from dual" diesen gesetzten Wert liefert - nicht aber ein "select systimestamp from dual", was dann auch den abschließenden Kommentar des Herrn Smithers erklärt:
If you’re now experiencing a sudden pang of disappointment at discovering that fixed_date wasn’t quite what you were hoping for when you started reading this, then I can only empathise.
You are now experiencing what it feels like to follow England at a major football tournament !
Wahrscheinlich kann man mit dem Parameter auch jede Menge Unfug anstellen, aber zu wissen, dass es ihn gibt, ist auf jeden Fall nützlich.

Samstag, Juni 23, 2012

Schnelle Queue-Mechanismen im SQL Server

Thomas Kejser hat mal wieder einen sehr interessanten Artikel zum SQL Server geschrieben, der aber - so weit ich sehe - allgemein für relationale Datenbanken relevant ist, und in dem es um den Definition performanter Queue-Tabellen geht. Der Herr Kejser schreibt dazu:
It can be argued that old school, relational databases and tables are not the best structures to implement durable storage for message queues. The many code paths needed in relational algebra to implement ACID properties, generic concurrency, serialization and block I/O can get in the way of a fast queue implementation, especially the naïve implementation of relational purists.
However, if we combine our knowledge of programming with database design skills, high throughput can be achieved even in the relational model and there are large, often overlooked improvements to be found.
Relativ viele der Einwände, die aus dem NoSQL-Lager gegen klassische RDBMS erhoben werden, haben wohl auch damit zu tun, dass dort das Sachwissen über relationale Datenbanken nicht immer extrem groß ist.

Das von Thomas Kejser vorgestellte Verfahren basiert darauf, die Queue mit einer fixen Länge anzulegen und Einträge über UPDATE anzupassen, statt sie über INSERT/DELETE zu verarbeiten, was massive Probleme mit Statistiken, Latches, Index Splits etc. mit sich bringt.

Zur Geschichte des AWR

Iggy Fernandez hat ein Interview mit Graham Wood, dem Erfinder von Statspack und Automatic Workload Repository (AWR) geführt, das unter anderem die Geschichte der Einführung dieser (extrem nützlichen) Instrumentierungsmechanismen erzählt.

Mittwoch, Juni 20, 2012

COPY_TABLE_STATS

Dom Brooks, der in seinem Blog gerade ausgesprochen produktiv ist und zahlreiche interessante Artikel veröffentlicht, zeigt, dass die COPY_TABLE_STATS-Routine auf Subpartitions-Ebene nicht die HIGH_VALUE- und LOW_VALUE-Angaben einträgt, die sich aus dem Partition Key ergeben, sondern einfach die kopierten Werte übernimmt - was in älteren Releases auch das Verhalten auf Partitions-Ebene war, für diese aber in aktuelleren Versionen korrigiert ist (10.2.0.5, 11.1.0.7, 11.2).

Freitag, Juni 15, 2012

Costing und manuelle Optimierung

Nachdem ich beim Versuch, diesem Eintrag einen passenderen Titel zu geben, bei epischen Beschreibungen nach Art von Kevin Closson gelandet bin, hab ich's aufgegeben und stattdessen eine kurze Überschrift mit geringer Aussagekraft gewählt ...

Worum es geht: gestern habe ich eine Query geschrieben, die die Sätze einer sehr kleinen Korrekturtabelle (3 rows) mit den entsprechenden Inhalten einer großen (partitionierten) Faktentabelle (700M rows) verbinden sollte. Auf der großen Tabelle existierte ein Index, in dem die Join-Spalte an zweiter Position erschien, wobei die führende Spalte ein Datum mit relativ wenigen distinkten Werten war. Meine Erwartung war, dass der CBO einen Plan liefern würde, der die kleine Tabelle über FTS lesen und auf die große Tabelle über NESTED LOOPS und INDEX SKIP SCAN zugreifen würde - aber das war nicht der Fall. Stattdessen ergab sich folgender Plan:

----------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |
|   1 |  HASH UNIQUE          |                        |     1 |
|*  2 |   HASH JOIN           |                        |     1 |
|   3 |    TABLE ACCESS FULL  | KORREKTUR              |     3 |
|   4 |    PARTITION RANGE ALL|                        |   708M|
|   5 |     TABLE ACCESS FULL | FAKTEN                 |   708M|
----------------------------------------------------------------

Dass der FTS auf die große Faktentabelle eine ziemlich dumme Idee wäre, schien mir ziemlich offensichtlich, und deshalb habe ich mit Hints den von mir favorisierten Plan erzwungen - und stellte dabei fest, dass die Kosten des per Hint erzwungenen Plans bei 5674 lagen: gegenüber 1717K für den Plan, den sich der CBO ausgedacht hatte. Heute habe ich den Fall noch mal auf den folgenden kleineren Test (mit 11.1.0.7) reduziert:

drop table t_outer;
create table t_outer
as
select rownum id
  from dual
connect by level <= 5;

drop table t_inner;
create table t_inner
as
select trunc(rownum/10000) col1
     , rownum id
     , mod(rownum, 10) col2
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 't_outer')
exec dbms_stats.gather_table_stats(user, 't_inner')

create index t_inner_idx on t_inner(col1, id, col2);

set autot trace
-- ohne Hints
select count(*)
  from t_inner i
     , t_outer o
 where i.id = o.id;

-- mit Hints
select /*+ use_nl(o i) index_ss(i t_inner_idx) */
       count(*)
  from t_inner i
     , t_outer o
 where i.id = o.id;

Das Ergebnis ist hier ein HASH JOIN mit einem INDEX FAST FULL SCAN zum Zugriff auf die große Tabelle T_INNER - immerhin kein FTS - aber immer noch ein Plan, dessen Kosten höher sind als die des durch die Hints herbeigeführten NL mit INDEX SKIP SCAN (845 zu 518):

-- ohne Hints
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     8 |   845   (2)| 00:00:11 |
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |
|*  2 |   HASH JOIN            |             |     5 |    40 |   845   (2)| 00:00:11 |
|   3 |    TABLE ACCESS FULL   | T_OUTER     |     5 |    15 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| T_INNER_IDX |  1000K|  4882K|   836   (2)| 00:00:11 |
--------------------------------------------------------------------------------------

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

   2 - access("I"."ID"="O"."ID")


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3066  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

-- mit Hints
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |     8 |   518   (0)| 00:00:07 |
|   1 |  SORT AGGREGATE     |             |     1 |     8 |            |          |
|   2 |   NESTED LOOPS      |             |     5 |    40 |   518   (0)| 00:00:07 |
|   3 |    TABLE ACCESS FULL| T_OUTER     |     5 |    15 |     3   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN  | T_INNER_IDX |     1 |     5 |   103   (0)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   4 - access("I"."ID"="O"."ID")
       filter("I"."ID"="O"."ID")


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        543  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Dabei zeigen die consistent gets, dass die cost-Angabe durchaus mit der tatsächlich auszuführenden Arbeit korreliert. Die Frage ist (mal wieder): warum zieht der CBO den günstigeren NL-Plan nicht in Betracht? Ein Blick ins 10053er Trace zeigt nur, dass dort der INDEX SKIP SCAN für den Fall des NL-Zugriffs nicht in Betracht gezogen wird:

Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  T_OUTER[O]#0  T_INNER[I]#1

***************
Now joining: T_INNER[I]#1
***************
NL Join
  Outer table: Card: 5.00  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 3
Access path analysis for T_INNER
  Inner table: T_INNER  Alias: I
  Access Path: TableScan
    NL Join:  Cost: 12806.18  Resp: 12806.18  Degree: 1
      Cost_io: 12734.00  Cost_cpu: 1184736916
      Resp_io: 12734.00  Resp_cpu: 1184736916
  Access Path: index (index (FFS))
    Index: T_INNER_IDX
    resc_io: 825.40  resc_cpu: 141691906
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Inner table: T_INNER  Alias: I
  Access Path: index (FFS)
    NL Join:  Cost: 4173.16  Resp: 4173.16  Degree: 1
      Cost_io: 4130.00  Cost_cpu: 708488767
      Resp_io: 4130.00  Resp_cpu: 708488767
kkofmx: index filter:"I"."ID"="O"."ID"

  Access Path: index (FullScan)
    Index: T_INNER_IDX
    resc_io: 3048.00  resc_cpu: 221706149
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
 ***** Logdef predicate Adjustment ****** 
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ****** 
    NL Join : Cost: 15310.54  Resp: 15310.54  Degree: 1
      Cost_io: 15243.00  Cost_cpu: 1108559981
      Resp_io: 15243.00  Resp_cpu: 1108559981

  Best NL cost: 4173.16
          resc: 4173.16  resc_io: 4130.00  resc_cpu: 708488767
          resp: 4173.16  resp_io: 4130.00  resc_cpu: 708488767

Einen guten Grund für das Verhalten des CBO habe ich bisher noch nicht gefunden.

Nachtrag 16.06.2012: Hier noch ein Link auf einen Thread im OTN-Forum Database - General, den ich zum Thema begonnen habe, und in dem Nikolay Savvinov auf einige Bugs mit SKIP SCANs hinweist. Relevant scheint dabei vor allem der Hinweis auf die identischen ACCESS und FILTER Prädikate im günstigen Plan zu sein.

Mittwoch, Juni 13, 2012

Truncate für Partitionen

Referentielle Constraints können beim TRUNCATE einer Partition (ALTER TABLE ... TRUNCATE PARTITION ...) den Fehler "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" hervorrufen. Jonathan Lewis zeigt, wie man dieses Problem mit Hilfe von Partition Exchange und NOVALIDATE Constraints umgehen kann.

Montag, Juni 11, 2012

Windows-Tools für DBAs

Wahrscheinlich haben sich die Herren nicht abgesprochen, aber Tim Hall und Howard Rogers haben Listen interessanter Windows-Tools veröffentlicht:
Wobei ich nur die Absprache bezweifele, mir einen Zusammenhang aber durchaus vorstellen könnte ...

Nachtrag 07.10.2014: offenbar ist mir (mal wieder) entgangen, dass der Herr Rogers (mal wieder) eine Webseite aus dem Verkehr gezogen hat (oder zumindest aus dem öffentlichen Zugriff.

Freitag, Juni 08, 2012

Tabellen-Statistiken vergleichen

Maria Colgan erläutert im Blog der CBO-Entwickler, wie man Tabellen- und Partitionsstatistiken mit Hilfe der Funktion DBMS_STATS.DIFF_TABLE_STATS abgleichen kann, was hilfreich sein kann, wenn man prüfen will, ob eine veränderte Statistikerhebungsstrategie zu einer Veränderung der Qualität der Statistiken führt. Im gegebenen Fall ging es um die Einführung inkrementeller Statistiken und den Einsatz der auto_sample_size statt eines niedrigen estimate_percent Wertes.

FORCE_MATCH für SQL Profiles

Karen Morton hat dieser Tage ein paar wichtige Details zur force_match Option für SQL Profiles erläutert. Zunächst liefert sie eine handliche Definition:
what the force matching signature does is to treat literals in the SQL like binds (think along the lines of cursor_sharing = force). When force matching is in place, all the literals are treated as binds and the same signature is generated for any SQL that differs only by their literals (white space and case are handled already)
Darüber hinaus weist sie darauf hin, dass force_match nicht greift, wenn in einer Query gleichzeitig Literale und Bindewerte erscheinen: "If both literal values and bind variables are in the SQL text, or if force_match is set to FALSE (default), then literal values are not normalized."

Mittwoch, Juni 06, 2012

Histogramme für Spalten mit extremer Ungleichverteilung

Mal wieder ein mäßig präziser Titel. Gemeint ist Folgendes: wenn in einer Spalte ein bestimmter Wert für nahezu sämtliche Sätze vorliegt und andere Werte extrem selten erscheinen, dann ist es nicht unwahrscheinlich, dass die seltenen Werte in einem mit auto_sample_size erzeugten Histogramm übersehen werden, was dann zur Folge hat, dass ihre Cardinality massiv überschätzt wird. Dazu ein Beispiel mit 11.2.0.1:

create table t_predominant
as
select rownum id
     , case when mod(rownum, 30000) = 10000 then 2
            when mod(rownum, 30000) = 20000 then 3
       else 1 end col_skew
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;

select col_skew, count(*)
  from t_predominant
 group by col_skew;

COL_SKEW   COUNT(*)
-------- ----------
       1     999933
       2         34
       3         33

begin
  dbms_stats.gather_table_stats(
      user
    , 't_predominant'
    , estimate_percent=>dbms_stats.auto_sample_size
    , method_opt=>'for columns col_skew size 254'
  );
end;
/

Ich lege also eine Tabelle mit 1M rows an, von denen fast alle in der col_skew den Wert 1 enthalten. Nur in 33 bzw. 34 Fällen erscheinen die Werte 2 und 3. Anschließend erzeuge ich explizit Histogramme für die Spalte col_skew mit dem Standard-Wert für estimate_percent. Ein Blick auf die Cardinality-Schätzungen beim Zugriff, zeigt, dass sich der CBO für die seltenen Fälle massiv verkalkuliert:

explain plan for
select count(*) from T_PREDOMINANT where COL_SKEW = 1;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     3 |  2453   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE    |               |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_PREDOMINANT |   999K|  2929K|  2453   (1)| 00:00:30 |
------------------------------------------------------------------------------------

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

   2 - filter("COL_SKEW"=1)

explain plan for
select count(*) from T_PREDOMINANT where COL_SKEW = 2;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     3 |  2453   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE    |               |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_PREDOMINANT |   500K|  1464K|  2453   (1)| 00:00:30 |
------------------------------------------------------------------------------------

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

   2 - filter("COL_SKEW"=2)

Für den Fall mit "col_skew = 1" ist das Ergebnis präzise, aber woher kommen die abwegigen 500K für den Fall "col_skew = 2". Die Antwort findet man bei Jonathan Lewis: "If the value you supply does not appear in the histogram, but is inside the low/high range of the histogram then the cardinality will be half the cardinality of the least frequently occurring value that is in the histogram."

select column_name
     , num_distinct
     , low_value
     , high_value
     , histogram
     , num_buckets
     , sample_size
  from dba_tab_cols
 where table_name = 'T_PREDOMINANT'
   and column_name = 'COL_SKEW';

COLUMN_NAME     NUM_DISTINCT LOW_VALUE  HIGH_VALUE HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE
--------------- ------------ ---------- ---------- --------------- ----------- -----------
COL_SKEW                   3 C102       C104       FREQUENCY                 1        5568

Der Wert 2 liegt zwischen LOW_VALUE und HIGH_VALUE, erscheint aber offensichtlich nicht im Histogramm und deshalb berechnet sich seine Cardinality als ("cardinality of the least frequently occurring value")/2: im gegebenen Fall ist die "cardinality of the least frequently occurring value" die des einzigen im Histogramm enthaltenen Wertes - also 1000K, so dass sich die 500K für die Werte 2 und 3 ergeben. Die sample_size von 5568 ist dabei ein besonderer Effekt der auto_sample_size, auf den Randolf Geist gelegentlich hingewiesen hat (der verlinkte Kommentar weist auch explizit auf den hier angesprochenen Fall der Nichtberücksichtigung seltener Werte im Histogramm hin).

Allerdings ergibt sich in meinem Test-Fall die gleiche Cardinality von 500K auch für Werte jenseits der LOW_VALUE, HIGH_VALUE-Grenzen:

explain plan for
select count(*) from T_PREDOMINANT where COL_SKEW = 4;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     3 |  2453   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE    |               |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_PREDOMINANT |   500K|  1464K|  2453   (1)| 00:00:30 |
------------------------------------------------------------------------------------

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

   2 - filter("COL_SKEW"=4)

explain plan for
select count(*) from T_PREDOMINANT where COL_SKEW = 10000000000000;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     3 |  2453   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE    |               |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_PREDOMINANT |   500K|  1464K|  2453   (1)| 00:00:30 |
------------------------------------------------------------------------------------

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

   2 - filter("COL_SKEW"=10000000000000)

Jonathan Lewis schreibt dazu: "If the value you supply is outside the low/high range of the histogram Oracle starts with the “half the least popular” value, then applies the normal (for 10g) linear decay estimate so that the cardinality drops the further outside the known range your requested value falls." Das scheint hier nicht einzutreten, aber möglicherweise liegt hier ein ähnlicher Fall vor, wie der von Randolf Geist im Zusammenhang mit column groups beschriebene: "If there is only a single distinct value in the statistics then the "out-of-range" detection of the optimizer is not working correctly."

Um ein brauchbares Histogramm zu erhalten, ändere ich in meinem Testfall die estimate_percent auf 100:

begin
  dbms_stats.gather_table_stats(
      user
    , 't_predominant'
    , estimate_percent=>100
    , method_opt=>'for columns col_skew size 254'
  );
end;
/

explain plan for
select count(*) from T_PREDOMINANT where COL_SKEW = 2;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     3 |  2453   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE    |               |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_PREDOMINANT |    34 |   102 |  2453   (1)| 00:00:30 |
------------------------------------------------------------------------------------

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

   2 - filter("COL_SKEW"=2)

Ein paar alternative Lösungen zum Problem findet man bei Nikolay Savvinov, der sich dabei wiederum auf Vorschläge von Jonathan Lewis bezieht, nämlich die Anlage eines FBI (und eine Anpassung der zugehörigen Queries) bzw. die manuelle Erzeugung geeigneter Histogramme.

Beim Herrn Savvinov habe ich dann noch einen Link auf einen weiteren Artikel von Randolf Geist entdeckt, der so ziemlich alles, was ich hier gerade aufgeschrieben habe, ausführlich ausführt und erläutert - sogar das Beispiel ist nahezu identisch mit dem, das ich mir gebastelt habe - und noch allerlei zusätzliche Informationen liefert. Vielleicht hätte ich dort erst mal suchen sollen ...

Montag, Juni 04, 2012

Doppelter Outer-Join

Jonathan Lewis zeigt in seinem Blog, wieso der Zugriff auf dba_subpart_col_statistics zu einem FTS der internen Tabelle tabcompart$ ("the thing that identifies the (logical) partition objects that make up a composite partitioned table") führt: Ursache ist, dass in der Definition der View der Outer Join einer Tabelle an zwei andere Tabellen enthalten ist, was normalerweise zu "ORA-01417: a table may be outer joined to at most one other table" führt. Um das Problem zu vermeiden, wird einer der Outer Joins in einer non-mergeable View verborgen, was dann im gegebenen Fall einen Index-Zugriff ausschließt. Immer wieder erstaunlich, welch seltsame SQL-Stücke im Data Dictionary zu finden sind ...

Nachtrag 07.07.2012: Mike Smithers zeigt, dass ora-01417 bei Verwendung der ANSI-Outer-Join-Syntax nicht auftritt, was für Jonathan Lewis' Fragestellung aber wohl eher irrelevant ist.

Freitag, Juni 01, 2012

ORA-08006 und row movement

Todor Botev hat ein Problem, mit dem ich heute konfrontiert wurde, bereits vor einiger Zeit beschrieben: ein MERGE-Statement ändert den partition key eines Satzes einer partitionierten Tabelle mit row movement - bzw. scheitert daran mit einem missverständlichen ora-08006-Fehler "specified row no longer exists". Über Update lässt sich die Operation hingegen problemlos durchführen. Schade, denn ich formuliere Update-Logik inzwischen fast immer mit MERGE - und kann auch nicht ganz nachvollziehen, wieso beide Operationen unterschiedlich behandelt werden.

Und leider ist das row movement in meinem Fall unvermeidlich, da die Partitionierung zur Beschränkung eines relevanten Bereichs einer sehr großen Tabelle dient, und nachträgliche Zuordnungen in diesen Bereich vorkommen können.

In MOS habe ich zum Thema übrigens nichts Sachdienliches gefunden.