Latches und Mutexes

Jonathan Lewis hat gerade auf Andrey Nikolaevs Blog Latch, mutex and beyond hingewiesen, der die erwähnten Themen tatsächlich sehr detailliert erläutert. Interessant ist z.B. gleich der erste grundlegende Artikel im Blog: Oracle latches and general Spinlocks.

Data Loading

Randolf Geist liefert in seinem Blog eine sehr lesenswerte Zusammenfassung der Dinge, die man zur Handhabung von Indizes bei Massendatenladevorgängen wissen sollte - und die eine solidere Grundlage für diverse Behauptungen gibt, die ich hier vor kurzem aufgeschrieben hatte. Neu für mich war, dass ein TRUNCATE einen Index wieder VALID werden lässt:

create table test 
as 
select rownum id 
  from dual 
connect by level <= 1000;

create index test_idx on test(id);

alter index test_idx unusable;

select index_name
     , status 
  from user_indexes 
 where table_name = 'TEST';
 
INDEX_NAME                     STATUS
------------------------------ --------
TEST_IDX                       UNUSABLE

truncate table test;

select index_name
     , status 
  from user_indexes 
 where table_name = 'TEST';

 INDEX_NAME                     STATUS
------------------------------ --------
TEST_IDX                       VALID

Hilfreich sind auch die diversen Links auf Richard Footes Erläuterung der Unterschiede zwischen unique und nonunique indexes - und auf des Herrn Geists eigene Untersuchungen zum Verhalten von Constraints.

Cardinality Feedback

Im cbo-Entwickler Blog wird erläutert, was Cardinality Feedback leistet. Allerdings klingt die Beschreibung eher nach einer Krücke:

The improved estimates used by cardinality feedback are not persisted. For this reason, it’s always preferable to use other techniques to get cardinality estimates right the first time every time, e.g. extended statistics, dynamic sampling, or SQL profiles. But for cases where these techniques do not apply, cardinality feedback can provide some relief.

Grundsätzlich ist das Konzept aber sicher zukunftsweisend, da ein Großteil meiner SQL-Tuning-Operationen auch nur darauf beruht, dass ich Cardinality-Schätzungen mit den actual rows Angaben abgleiche.

Nachtrag 05.08.2011: Kerry Osborne liefert in seinem Blog ein Beispiel dafür, dass das Cardinality Feedback in manchen Fällen zu recht instablien Plänen führt.

Excel-Zugriff auf Oracle

Beim Zugriff von Excel auf Oracle via OLEDB habe ich zuletzt beobachtet, dass sich die Zugriffszeiten für den Aufbau der Liste verfügbarer Objekte zwischen den Oracle-Versionen 10.2.0.4 und 11.1.0.7 massiv erhöht haben. Aber der Reihe nach. Das Vorgehen bei der Bestimmung der Effekte sah in meiner deutschsprachigen Oberfläche folgendermaßen aus:

• Excel 2007 starten
• Menü: Daten
• Menü: Aus anderen Quellen
• Auswahl: vom Datenverbindungsassistenten: importiert Daten ... über OLEDB
• Auswahl: Microsoft Data Access - OLEDB-Anbieter für Oracle
• Angabe von Anmeldeinformationen für Oracle-DB

Das Ergebnis ist eine Auswahlliste mit allen für den gewählten Nutzer sichtbaren Tabellen und Views. Die dabei zum Server geschickte Query ist zwar nicht besonders hübsch, aber zumindest relativ übersichtlich:

SELECT *
FROM   (SELECT NULL                             table_catalog,
               Decode (o1.owner, 'PUBLIC', NULL,
                                 o1.owner)      table_schema,
               o1.object_name                   table_name,
               Decode(o1.owner, 'SYS',
               Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE',
                                      'VIEW', 'SYSTEM VIEW',
                                      o1.object_type),
                                'SYSTEM',
               Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE',
                                      'VIEW', 'SYSTEM VIEW',
                                      o1.object_type),
                                o1.object_type) table_type,
               NULL                             table_guid,
               NULL                             description,
               NULL                             table_propid,
               NULL                             date_created,
               NULL                             date_modified
        FROM   all_objects o1
        WHERE  ( ( o1.object_type = 'TABLE'
                   AND o1.generated != 'Y' )
                  OR o1.object_type = 'VIEW' )
        UNION
        SELECT NULL                        table_catalog,
               Decode (o2.owner, 'PUBLIC', NULL,
                                 o2.owner) table_schema,
               o2.object_name              table_name,
               o2.object_type              table_type,
               NULL                        table_guid,
               NULL                        description,
               NULL                        table_propid,
               NULL                        date_created,
               NULL                        date_modified
        FROM   all_objects o2,
               all_objects o3,
               all_synonyms s
        WHERE  o2.object_type = 'SYNONYM'
               AND ( ( o3.object_type = 'TABLE'
                       AND o3.generated != 'Y' )
                      OR o3.object_type = 'VIEW' )
               AND o2.owner = s.owner
               AND o2.object_name = s.synonym_name
               AND s.table_owner = o3.owner
               AND s.table_name = o3.object_name) tables
ORDER  BY 4,
          2,
          3  ;

ALL_OBJECTS erscheint hier also dreifach, daneben noch einmal ALL_SYNONYMS. Nun zur Performance des Aufbaus der Auswahlliste:

• 10.2.0.4 (DBA): 4 sec. (19.786 rows, 321.193 consistent gets)
• 10.2.0.4 (CONNECT): 72 sec. (2.258 rows, 2.899.642 consistent gets)
• 11.1.0.7 (DBA): 39 sec. (23.152 rows, 383.950 consistent gets)
• 11.1.0.7 (CONNECT): 562 sec. (3.152 rows, 2.728.757 consistent gets)

In beiden Datenbanken wurden zunächst die Statsitiken für das Schema SYS und die Fixed Tables aktualisiert. Auffällig an den Ergebnissen ist, dass die Queries in 11.1.0.7 deutlich länger laufen als in 10.2.0.4 und dass der Benutzer mit DBA-Rechten jeweils deutlich schneller an seine Resultate kommt, als der weniger privilegierte Connect-User, obwohl dieser deutlich weniger Objekte sehen kann, zu deren Ermittlung aber die zehnfache Menge an Blockzugriffen durchführen muss.Verantwortlich für die Laufzeitunterschiede zwischen den Releases ist offenbar vor allem die View-Definition für ALL_OBJECTS, die sich für Version 10 in der Datei catalog.sql und für Version 11 in cdcore.sql findet, wobei in 11 einige Ergänzungen hinzugekommen sind (no_expand hint, neue Spalten: namespace, edition, new object_types for mining und olap elements). Die Zugriffspläne kann man sich natürlich auch noch anschauen, aber ich muss gestehen, dass ich bei komplexen dictionary queries da nicht allzu viel erkennen kann - höchstens, dass in 10 anscheinend mehr NL-Operationen durchgeführt werden, was für einen exakten Zugriff womöglich flotter ist als ein HASH Join; aber den Plan im Detail zu untersuchen ist mir zu mühsam ...

Ich habe den Fall in Charles Hoopers Blog angesprochen und dort den Vorschlag bekommen, die Query über DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE umzuleiten, was wahrscheinlich funktionieren würde. Dabei könnte man mit CTEs für den ALL_OBJECTS-Zugriff arbeiten, was vor allem für den extrem langsamen Fall mit 11.1.0.7 und Connect vermutlich lohnend wäre. Alternativ verweist der Herr Hooper auf Tom Kytes Motto "that the fastest way to do something is to not do it at all" - das wäre unter Umständen auch ein guter Workaround.

Audit-Unschärfen

Jonathan Lewis zeigt in seinem Blog, dass Auditing Objektzugriffe nicht in allen Fällen korrekt zuordnet, da in manchen Fällen (z.B. bei reinen Index-Joins) nicht die erwarteten Tabellen, sondern interne Objekte (z.B. temporäre Views) aufgeführt werden. Dabei ist das Verhalten auch nicht in sich schlüssig, sondern liefert abhängig vom execution plan unterschiedliche Resultate.

HIGH_VALUE für Interval Partitions

Heute ist mir aufgefallen, dass ich seit 10 Jahren einen Account in OTN habe, aber in den Foren noch nie eine Frage gestellt hatte. Das ist jetzt geändert: wobei der Beitrag im Database-General-Forum vermutlich auch als Blog-Eintrag funktioniert hätte. Kurz zusammengefasst geht's darum, dass interval partitions zu extrem hohen Laufzeiten führen, wenn ein weit von den tatsächlichen Inhalten abliegender HIGH_VALUE für die initiale Partition gewählt wurde. Sieht aus wie ein Bug, aber vielleicht gibt's auch noch eine plausible Erklärung für das Verhalten.

Nachtrag 26.05.2011: Jonathan Lewis hat im Thread inzwischen eine Antwort auf meine Frage gegeben, hält das Verhalten auch für einen Bug und verweist auf v$rowcache: "Check v$rowcache activity - that's where the CPU is going. You will see '100,000 - high_value' gets on on the dc_tablespaces and dc_users row cache entries when you run the query (and that's two latch hits on the rowcache latch for every get)." Außerdem hat er den Thread im Oracle Scratchpad verlinkt.

Nachtrag 27.05.2011: Greg Rahn hat das Problem inzwischen als Bug 12597302 erfasst.

Nachtrag 08.12.2011: unter der Bug-Nummer findet man in MOS zur Zeit allerdings nix...

Insert Performance mit Bitmap- und B*Tree-Indizes

Vielleicht habe ich mir die Frage schon früher gestellt, vielleicht auch schon beantwortet, vielleicht auch schon hier in diesem Blog - aber was soll's: zur Not eben eine Doublette. Ich gehe davon aus, dass Bitmap Indizes die Insert-Performance stärker beeinträchtigen als B*Tree Indizes (völlig abgesehen von locking, concurrency etc.). Dazu folgender Test (unter 11.2.0.1; EE; Noarchivelog):

drop table test;

create table test
( id number
, col1 number
, col2 number
, pad varchar2(64));

/*
create bitmap index test_col1 on test(col1);
create bitmap index test_col2 on test(col2);
*/

create index test_col1 on test(col1);
create index test_col2 on test(col2);

@ trace

insert /* btree */ into test
select rownum id
     , mod(rownum, 40) col1
     , round(rownum)/1000 col2
     , lpad('*', 40, '*')
  from dual
connect by level <= 1000000;

@ trace_end

Tatsächlich benötigt das Insert im Fall der B*Tree-Indizes 34 sec und im Fall der Bitmap-Indizes 67 sec. Hier die Details:

-- B*Tree
insert /* btree */ into test
select rownum id
     , mod(rownum, 40) col1
     , round(rownum)/1000 col2
     , lpad('*', 40, '*')
  from dual
connect by level <= 1000000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1     10.81      34.01          0      30292    2993170     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     10.82      34.01          0      30292    2993170     1000000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                       15        0.48          1.28
  log file switch completion                     18        1.81          9.33
  log buffer space                               87        0.31          5.66
  reliable message                               83        0.00          0.00
  rdbms ipc reply                                87        1.02          1.67
  control file sequential read                   84        0.27          1.38
  db file sequential read                         8        0.01          0.05
  Data file init write                            4        0.00          0.00
  db file single write                            4        0.00          0.01
  control file parallel write                    12        0.00          0.02
  enq: CF - contention                            2        0.50          0.52
  log file sync                                   1        0.08          0.08
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

-- Bitmap
insert /* bitmap */ into test
select rownum id
     , mod(rownum, 40) col1
     , round(rownum)/1000 col2
     , lpad('*', 40, '*')
  from dual
connect by level <= 1000000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     29.10      67.64          0    5759962    5992713     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     29.10      67.64          0    5759962    5992713     1000000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                      102        0.29          6.17
  log file switch completion                     26        0.10          1.13
  control file sequential read                  693        0.45          7.43
  db file sequential read                        66        0.07          0.62
  Data file init write                           33        0.00          0.00
  db file single write                           33        0.00          0.04
  control file parallel write                    99        0.08          0.48
  rdbms ipc reply                                33        0.15          0.59
  log buffer space                               44        0.27          2.24
  latch: object queue header operation            1        0.00          0.00
  enq: CF - contention                            5        0.26          0.36
  log file switch (checkpoint incomplete)        12        2.10          9.48
  log file sync                                   1        0.03          0.03
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Man sieht hier zum einen, dass mein Windows7-Rechner mit dem Logging leicht überfordert ist. Darüber hinaus kann man beobachten, dass die CPU-Last für die Bitmap-Indizes deutlich höher ist, und dass für diesen Fall sehr viel mehr LIOs (current gets und consistent gets) erscheinen. Aus v$sesstat geht hervor, dass der Bitmap-Fall deutlich weniger Arbeitsspeicher nutzt (PGA, UGA), und dass er mehr undo und redo hervorruft (was möglicherweise auch mit dem Noarchivelog-Modus zusammenhängt. Sehr viele der consistent gets fallen übrigens in die Kategorie "consistent gets - examination" (und sollten demnach relativ flott sein). Auch in anderen Statistiken zeigt der Bitmap-Fall deutlich höhere Werte, aber ich bleibe erst mal bei der Beobachtung stehen, dass die Maintainance für Bitmap Indizes teurer ist, als für B*Tree Indizes.

Join Cardinality

Zu Erinnerungszwecken die Formel aus Jonathan Lewis' Cost Based Oracle (Kapitel 10, S. 265ff.) zur Join Cardinality für einfache Joins mit zwei Tabellen:

-- für einen Join der Tabellen t1 und t2 über die Spalte c1 bzw. c2 gilt:
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)

Für die Tabellen des Lewis'schen Beispiels ergibt sich:

Join Selectivity
   = ((10000 - 0)/ 10000) *
     ((10000 - 0)/ 10000) /
     greater(30,40)
   = 1/40

Join Cardinality
   = 1/40 * (400 * 200)
   = 2000

Ich habe das Beispiel des Buchs mit 11.2.0.1 durchgespielt (die Scripts kann man übrigens hier downloaden) und bekomme die gleichen cardinalities wie der Herr Lewis, aber eine geringfügig andere cost-Angabe:

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 68000 |    58 |
|*  1 |  HASH JOIN         |      |  2000 | 68000 |    58 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    28 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    28 |
-----------------------------------------------------------

Vermutlich ist die abweichende cost-Angabe mal wieder ein round/ceil-Effekt.

Nachtrag 15.02.2012: Für einen Join über mehrere Spalten wird durch die greater-Ausdrücke für alle Spalten dividiert, also:

Join Selectivity
   = ... 
/ greater (num_distinct(t1.col1),  num_distinct(t2.col1))
/ greater (num_distinct(t1.col2),  num_distinct(t2.col2))

Im Sinne einer doppelt verketteten Liste hier noch der Verweis auf einen jüngeren Eintrag zum gleichen Thema (und mit nahezu gleichem Titel).

Who's who

Vincent Rainardi, der ein sehr lesenswertes Microsoft BI Blog unterhält, hat zwei interessante Personenlisten zu den üblichen Verdächtigen erstellt:

• Who's Who in Data Warehousing
• Who's Who in SSAS

Die DWH-Liste ist recht übersichtlich, aber die zu den SSAS-Leuten ist recht umfassend. Wer dort noch jemanden vermisst, findet ihn vielleicht im SSAS-Wiki.

Vielleicht wäre es gelegentlich interessant, eine ähnliche Liste zu den Heroen der Oracle-Welt zusammen zu stellen; andererseits wäre die vermutlich ziemlich lang und besäße wohl eine große Schnittmenge mit der Liste der Oak-Table-Mitglieder, so dass man sich die Mühe vielleicht auch sparen kann.

Star Transformation Joins und Star Joins

Bei der erneuten Lektüre von Cost Based Oracle ist mir aufgefallen, dass ich eine dort (Kapitel 9, S. 253) getroffene Unterscheidung nicht mehr so recht vor Augen hatte, nämlich die zwischen Star Transformation Join und Star Join:

• Star Transformation Join: dazu hatte ich vor einigen Monaten eine Erläuterung im cbo Entwickler Blog verlinkt. Grundsätzlich handelt es sich dabei um eine Query Transformation, also den Umbau einer Query durch den cbo, der sich auf diese Weise effizientere Zugriffsmöglichkeiten eröffnet. Die Transformation ist in diesem Fall die Ergänzung zusätzlicher Subqueries mit den Einschränkungen auf die Dimensionen, die vorhandene Bitmap Indizes in der Faktentabelle nutzen. Die Ergebnisse der Dimensionszugriffe werden dann über Bitmap And zusammengefasst und zum Zugriff auf die Fakten genutzt. Anschließend wird das eingeschränkte Ergebnis der Fakten dann wieder mit den Dimensionstabellen verknüpft, um die daraus benötigten Informationen zu integrieren (die Dimensionen werden also zweifach angesprochen).
• Star Join: eine ältere Technik (verfügbar seit Oracle 7), die die Dimensionen als cartesisches Produkt verknüpft und mit dieser Einschränkung dann indiziert auf die Fakten zugreift. Jonathan Lewis hält davon nicht allzu viel und schreibt im cbo Buch: "I have yet to see a production example where it was appropriate to take advantage of the feature" (S. 258)

Viel mehr scheint zum Thema Star Join nicht (mehr) zu sagen zu sein -  jedenfalls liefert Google nicht arg viel Relevantes dazu.

Ein trauriges Missverständnis

Im letzten November habe ich an dieser Stelle diverse Klagen einiger der wichtigsten SSAS-Blogger zusammengefasst, die die Präsentation des neuen Business Intelligence Semantic Models (BISM) als Ankündigung des Endes der SSAS-Entwicklung aufgefasst hatten. Die zuständigen Task-Forces bei Microsoft sind dann eilig ausgeschwärmt, um die unzufriedenen Blogger wieder auf Linie zu bringen, aber zumindest war in diesem Zusammenhang allerlei Porzellan zu Bruch gegangen.
Offenbar hat man bei Microsoft daraus etwas gelernt, denn im Gefolge des diesjährigen TechEd liefern jetzt alle in Frage kommenden Kommentatoren sehr positive Einschätzungen der Entwicklung. Hier ein paar Wortmeldungen zum Thema (ohne besondere Reihenfolge):

• T.K.  Anand - mit der offiziellen Stellungnahme
• Chris Webb
• Boyan Penev
• Teo Lachev
• Alex Whittles
• Marco Russo

Alle scheinen recht begeistert zu sein. Also vielleicht alles nur ein trauriges Missverständnis... - ich lasse mich mal überraschen.

Virtual Columns

In einem Thread bei Tutorials.de wurde dieser Tage die Frage gestellt, wie man in Oracle 10 eine Spalte automatisch mit einem durch einen HASH-Algorithmus aus einer anderen Spalte erzeugten Wert füllen kann. Nun bin ich mir zwar nicht ganz sicher, wozu die Anforderung dient, aber zumindest scheint mir das eine sehr naheliegende Verwendung für Virtual Columns zu sein - die aber nur in 11g verfügbar sind. Hier meine Antwort aus dem Thread:

In Oracle 10 kann man die Befüllung der HASH-Spalte via Trigger durchführen - obwohl Trigger (in der Regel) häßlich, schlecht wartbar und übel für die Performance sind:

-- 10.2.0.4
create table test
( string_value varchar2(32)
, hash_value number);
 
create or replace trigger hash_trigger
before insert
on test
for each row
begin
  select ora_hash(:new.string_value)
  into :new.hash_value
  from dual;
end hash_trigger;
/
 
insert into test(string_value) values ('HELLO WORLD');
 
select * from test;
 
STRING_VALUE                     HASH_VALUE
-------------------------------- ----------
HELLO WORLD                      3784902367

Viel hübscher geht das in 11g, wo man zu diesem Zweck virtual columns verwenden kann:

-- 11.1.0.7
create table test
( string_value varchar2(32)
, hash_value NUMBER GENERATED ALWAYS AS (ora_hash(string_value)) virtual);
 
insert into test(string_value) values ('HELLO WORLD');
 
select * from test;
 
STRING_VALUE                     HASH_VALUE
-------------------------------- ----------
HELLO WORLD                      3784902367

Eine schlüssige Zusammenfassung zum Thema virtual columns liefert z.B. Tim Hall.

ASSM Bug - Ursachen

Randolf Geist hat gerade einen Blog-Artikel ASSM bug reprise - part 1 veröffentlicht, in dem er eine sehr griffige Erklärung für den folgenden Bug gibt:

There is a more or less famous bug in ASSM (see bug 6918210 in MOS [...]) in versions below 11.2 that so far has been classified as only showing up in case of a combination of larger block sizes (greater the current default of 8K) and excessive row migrations. With such a combination it was reproducible that an UPDATE of the same data pattern residing in an ASSM tablespace caused significantly more work than doing the same in a MSSM tablespace, because apparently ASSM had problems finding suitable blocks to store the migrated rows.

Demnach tritt dieser Bug unter folgenden Umständen auf:

• ein Block erreicht die Obergrenze möglicher ITL-Einträge (255 für Blocks >= 16K oder der dafür verwendete Speicherplatz erreicht 50% der Blocksize: deshalb sind es für 8K Blöcke 169 Einträge)
• der Block ist aber hinsichtlich des FREE Spaces noch nicht gefüllt (ein niedriger PCTFREE-Wert erhöht also die Wahrscheinlichkeit des Auftretens)
• "The basic problem of the bug seems to be that blocks in such a state are still considered as possible candidates for inserts of migrated rows, but have then to be rejected as Oracle recognizes that no further ITL entries can be added. The more blocks get into this state, the harder Oracle has to work to find a block that really can be used for storing the migrated row."
• Für Blöcke bis 8K tritt der Fall vermutlich selten auf, da die migrierten Sätze in diesem Fall nur sehr klein sein können

Nachtrag 04.06.2011: den zweiten Teil seiner Untersuchungen zu Bug 6918210 beginnt Randolf Geist mit dem Hinweis "The most important part of the bug is that it can only be hit if many row migrations happen during a single transaction." Das war mir im ersten Teil ebenso entgangen wie der dort erscheinende Hinweis: "for each row migrated into a block a new ITL is allocated".

INSERT Statement Extention

Gary Myers erläutert in seinem Blog die INSERT Statement Extention, ein interssantes PL/SQL-Feature, das es erlaubt, einem INSERT eine Query zur Bestimmung der Spaltenliste zuzuordnen. Verständlicher wird das vermutlich durch ein Stück Code:

 insert into
    (select id, col_a, col_b, col_c
    from wide_test)
  values v_rec;

Namensgebung für Interval Partitions

Beim Übertragen von Daten aus einer nicht partitionierten in eine (datumsbasiert) intervall-partitionierte Tabelle habe ich mich heute einmal mehr darüber geärgert, dass die system-generierten Intervall-Partitionsnamen keine Semantik enthalten, so dass ich nicht aus dem Partitionsnamen auf die enthaltenen Daten schliessen kann (man kann zwar in Oracle 11 Partitionen auch über eine neue FOR (datumsangabe) Syntax ansprechen, aber damit bin ich noch nicht völlig zufrieden). Um die Semantik zu ergänzen, wollte ich die Namen auf der Basis der HIGH_VALUE-Angaben aus USER_TAB_PARTITIONS ändern, was so einfach aber nicht geht, da HIGH_VALUE eine LONG-Spalte ist, mit der man nicht sehr viel anfangen kann - was man damit machen kann, hat Adrian Billington gelegentlich gezeigt - und da gäbe es sicher auch noch elegantere Lösungen, als die, auf die ich gekommen bin. Hier jedenfalls meine Variante:

-- Anlage einer Hilfstabelle mit den Partitionsnamen 
-- und den über to_lob umgewandelten HIGH_VALUE-Angaben
create table part_rename
as
select partition_name, to_lob(high_value) text
  from user_tab_partitions
 where table_name = 'MY_TABLE_NAME';

-- Generierung von Kommandos: ALTER TABLE ... RENAME PARTITION
-- da es sich um wochenbasierte Daten handelt, 
-- setze ich den Partitionsnamen auf HIGH_VALUE - 7 Tage
select 'alter table my_table_name rename partition '
       || partition_name 
       || ' TO my_table_name_p'
       || to_char(to_date(substr(text, 11, 10), 'yyyy-mm-dd') - 7, 'yyyymmdd') 
       || ';' text
  from part_rename
 order by 1;

-- Ausführung der generierten Kommandos
...

Beim Eintreffen neuer Daten jenseits der vorhandenen Intervall-Partitionen müsste man das natürlich wiederholen. Falls jemand eine bessere Lösung für das Problem kennt, würde ich mich über sachdienliche Hinweise freuen.

ASSM Probleme

Die Herren Lewis, Geist, Poder, Rahn, Hooper etc. erwähnen immer wieder diverse Probleme, die man sich mit ASSM einhandeln kann - wobei einige dieser Probleme offenbar recht handfeste Bugs sind. Auf ein solches Phänomen hat Jonathan Lewis vor kurzem wieder in seinem Blog hingewiesen und dabei einen ziemlich interessanten OTN-Thread angesprochen, in dem es um exzessive Block-Zugriffe bei Updates (und compression und row migration) geht.

Interessant im Thread ist auch der (von Randolf Geist gebrachte) Hinweis, "that as you update a row in a table with basic compression the modified version of the row will be stored in uncompressed format." Das hatte ich auch schon mal gehört/gelesen, aber auch schon wieder vergessen.

Zuletzt noch eine kleine Entschuldigung: zuletzt hatte ich ein paar unfreundliche Worte über DELETE geäußert, eine DML-Operation die allerlei Ärger machen kann. Hiermit will ich klarstellen, dass UPDATE keinen Deut besser ist...

Nachtrag 17.05.2011: Randolf Geist hat einen weiteren umfassenden Blog-Eintrag zu besagtem ASSM-Bug veröffentlicht - und ausgehend von der Nummerierung ist es der erste Teil einer Serie. Interessant sind dabei neben dem eigentlichen Thema auch die Hinweise auf die ausgefeilten Analyse-Scripts von Adrian Billigton, Tanel Poder, Jonathan Lewis und des Herrn Geist.

Datenlöschungen (DELETE, TRUNCATE, DROP)

Dieser Tage hat Tim Hall seine Meinung geäußert, die Firma Oracle solle endlich mal ihre Marketing-Strategie ändern und aufhören zu behaupten, der Oracle Server sei einsteigerfreundlich. Geeigneter sei ein Slogan wie: "Oracle. It’s f*ckin’ complicated, but it’s really cool!”

Was mich am Oracle Server (und an mir selbst) immer wieder überrascht, ist, dass ich ohne Mühe zu so ziemlich jedem grundlegenden Feature eine lange Liste von einfachen Fragen vorlegen kann, deren Antworten ich nicht kenne.

Zum Beispiel das Zusammenspiel von Queries und Datenlöschungen: relativ harmlos sind DELETEs, die außerhalb der ändernden Session natürlich unsichtbar sind. Aber was passiert, wenn eine Tabelle via DROP gelöscht oder via TRUNCATE geleert wird, während eine Query einer anderen Session darauf zugreift? Dazu - was sonst? - ein kleiner Test:

Zunächst die Voraussetzungen: Oracle 11.2.0.1, ASSM-Tablespace, 8K Blockgröße - was, abgesehen vielleicht von der Version, in diesem Fall vermutlich mäßig relevant ist. Dazu dann eine Test-Tabelle, die so groß ist, dass ein FTS darauf auf meinem PC ca. 10 sec. dauert, was mir die Möglichkeit gibt, in der Zwischenzeit etwas auszuprobieren:

create table test
as
select rownum id
     , lpad('*', 1000, '*') pad
  from dual
connect by level <= 1000000;

Fall 1: DELETE in einer Session und Zugriff in einer zweiten Session

-- in diesem Fall enthält die test-Tabelle nur 10 rows
-- da Massen-Deletes extrem langsam sind.
-- Session 1
delete from test;

10 Zeilen wurden gelöscht.

-- Session 2
select count(*) from test;

COUNT(*)
--------
      10

Das ist jetzt erst mal völlig klar und harmlos, da die Änderung transaktional abgeschirmt ist.

Fall 2: Aufbau einer Tabelle in einer Session und gleichzeitiger Zugriff in einer zweiten Session

-- Session 1
create table test 
as
select ...

-- Session 2
select count(*) from test;
--> wartet, während die Tabelle in Session 1 aufgebaut wird und liefert anschließend:
COUNT(*)
--------
 1000000

Das Verhalten ist ebenfalls verständlich und harmlos (sofern man sich nicht über die Wartezeit in Session 2 wundert).

Fall 3: DROP TABLE in einer Session, während eine zweite Session auf die Tabelle zugreift

-- Session 2
select count(*) from test;
-- Session 1
drop table test;
--> Löschung erfolgt unverzüglich
-- Session 2
--> liefert nach Abschluß des FTS
COUNT(*)
--------
 1000000
-- Wiederholung der Query:
select count(*) from test
                     *
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden

Das überrascht mich, weil ich erwartet hätte, dass das DROP einen Fehler liefert (ressource busy) oder die Query abbricht.

Fall 4: wie 3, aber mit DROP TABLE ... PURGE, um den recyclebin aus der Rechnung zu nehmen

-- Session 2
select count(*) from test;
-- Session 1
drop table test purge;
-- Session 2
--> Query bricht ab
ORA-08103: Dieses Objekt ist nicht mehr vorhanden

Also ist tatsächlich der recyclebin dafür verantwortlich, dass die Query in Fall 3 noch ein Ergebnis liefern kann.

Hinweis: wie man in Tanel Poders (unten verlinktem) Video zum Fehler ORA-8103 erfährt, ist diese Interpretation wohl nicht ganz korrekt, da selbst aus einer mit purge Option gelöschten Tabelle weiterhin gelesen werden kann, sofern die zugehörigen Blocks nicht überschrieben wurden. Wie es im gegebenen Fall zu einer Überschreibung gekommen sein könnte, ist mir allerdings auch nicht klar.

Fall 5: TRUNCATE TABLE in einer Session, während eine zweite Session auf die Tabelle zugreift

-- Session 2
select count(*) from test;
-- Session 1
truncate table test;
--> liefert ohne Verzögerung: Tabelle mit TRUNCATE geleert.
-- Session 2
--> Query bricht ab
ORA-08103: Dieses Objekt ist nicht mehr vorhanden

Das Verhalten entspricht dem des DROP TABLE ... PURGE aus Fall 4. Die Fehlermeldung ist ein wenig überraschend, ergibt sich aber offenbar daraus, dass die DataObjectId des Segments durch das Truncate geändert wird, wie Freek D’Hooge in einem sehr erhellenden Blog-Eintrag erläutert hat  - der mir allerdings erst begegnet ist, als ich mit meinen Tests fertig war. Die Änderung erfolgt übrigens offenbar nur dann, wenn durch das TRUNCATE tatsächlich Daten gelöscht werden:

select dataobj# from sys.obj$ where name = 'TEST';

  DATAOBJ#
----------
     72598

truncate table test;

Tabelle mit TRUNCATE geleert.

select dataobj# from sys.obj$ where name = 'TEST';

  DATAOBJ#
----------
     72599  <-- neue Id

truncate table test;

Tabelle mit TRUNCATE geleert.

select dataobj# from sys.obj$ where name = 'TEST';

  DATAOBJ#
----------
     72599  <-- Id bleibt gleich

Die Ergebnisse sind völlig einleuchtend, entsprachen aber nicht ganz meinen Annahmen, denn ich hatte in den Fällen 3 bis 5 eher den Fehler "ORA-00054: Ressource belegt und Anforderung mit NOWAIT angegeben oder Timeout abgelaufen"erwartet, der aber nur erscheint, wenn man DROP oder TRUNCATE ausführen will, während eine andere Session Transaktionen mit DML-Operationen (INSERT, UPDATE, DELETE) durchführt. Aber eigentlich ist der Fall klar: ein SELECT erzeugt kein LOCK (wie man in v$lock sehen kann), und ohne LOCK gibt es nichts, was die Löschung des Objekts verhindern kann. Ein SELECT schützt ein Objekt also nicht vor der Vernichtung.

Alles ganz grundlegende Dinge, über die ich sicher schon viel gelesen habe, aber trotzdem Dinge, die nicht zu meinem aktiven Wissensbestand zählen. Als nächstes sollte ich mal über LOCKs nachdenken.

Nachtrag 28.08.2011: Mehr zum Fehler ORA-08103 liefert Tanel Poders Hacking Session Video, das ich hier verlinkt und z.T. exzerpiert habe.

ASH Auswertungsqueries

Kyle Hailey liefert zur Zeit eine interessante Serie mit SQL-Analysequeries, die man zur Auswertung von ASH-Informationen (dba_hist_active_sess_history) verwenden kann:

• minimale, maximale und durchschnittliche Laufzeit einer Query
• Histogramm der Laufzeiten einer Query
• Zeitpunkt der langsamsten Ausführung einer Query

Bitmap Index Cost in 11.2 (Jonathan Lewis)

Obwohl ich vor einiger Zeit allerlei über Bitmap Indizes nachgedacht (und geschrieben) hatte, ist mir dabei offenbar das Thema der cbo-Kosten weitgehend entgangen. Daher hier eine kurze Zusammenfassung dessen, was sich zum Thema in Cost Based Oracle (Chapter 8, S. 181ff.) findet:

• bitmap Indizes sind in der Regel kleiner als entsprechende B*Tree Indizes und ihre Größe hängt stark von der Clusterung der Werte in der Tabelle ab (das entspricht schon mal den Ergebnissen meiner Tests)
• num_rows entspricht für gut geclusterte Werte dem Wert für distinct_keys; für scattered values ist  er höher als distinct_keys
• der clustering_factor für bitmap Indizes sagt nichts über die Clusterung der Daten aus und entspricht der num_rows-Angabe
• die Kosten für Bitmap Indizes werden von der Clusterung der Daten kaum beeinflusst, da grundsätzlich die gleiche Arithmetik zur Kostenberechnung wie im Fall von B*Tree-Indizes angewandt wird (unter Verwendung des clustering_factors)
• im Execution Plan werden für bitmap Indexes keine Kosten angegeben (gilt auch in 11.2)
• Bitmap Indizes werden nur bis zu einem gewissen Punkt kombiniert: nämlich so lange, bis die Kosten des Scannens eines weiteren Indexes höher ist als die erwartete Reduzierung der Tabellenblockzugriffe. Deshalb werden selten mehr als 3 Bitmap Indizes kombiniert (und der oft erwähnte Gender-Bitmap-Index bliebe in einem solchen Fall leicht auf der Strecke)
• insgesamt gibt's anscheinend eine Menge seltsamer Effekte beim Index-Costing, die keiner inneren Logik zu folgen scheinen
• für 11.2 lieferten die zum cbo-Buch gehörenden Beispielskripte die gleichen Ergebnisse, die auch im Buch erscheinen: demnach scheint sich in diesem Bereich sehr wenig verändert zu haben.

ATOMIC_REFRESH

Meine aufrichtige Entschuldigung an T.S. Eliott, aber: DELETE ist das grausamste SQL-Element. Wenn es erfolgreich ist, benötigt es für größere Löschoperationen extrem viel Zeit - und wenn es nicht erfolgreich ist (ROLLBACK), noch viel mehr Zeit. Wo möglich, versuche ich es zu vermeiden - und lege lieber neue Tabellen an, die dann über Ringtausch ausgewechselt werden, oder lösche Inhalte via TRUNCATE.

Heute habe ich bei einer Complete Refresh-Operation für eine MView in 10.2 mit Schrecken festgestellt, dass die Löschung bestehender Inhalte über DELETE erfolgen sollte. Normalerweise hätte ich in einem solchen Fall eine PREBUILT TABLE verwendet, aber da es sich nicht um meinen Code handelte, habe ich mich stattdessen auf die Suche nach einem Mittel gegen das DELETE gemacht und bin - einmal mehr - bei Tom Kyte fündig geworden, der in einem (relativ aktuellen) Thread auf die Option ATOMIC_REFRESH hinweist, die das Refresh statt mit DELETE und INSERT mit TRUNCATE und INSERT /*+ APPEND */ durchführt. Natürlich gilt für diesen Fall: "Beware that of course the data will disappear during the refresh - as long as you are OK with that, it'll direct path."

Execution Plan

Eines der besten Kapitel in Christian Antogninis Buch Troubleshooting Oracle Performance ist Nr. 6, das sich mit dem Thema Execution Plans befasst, und dessen gekürzte Fassung auch im Web zu finden ist. Dort findet sich der treffende Satz:

I have always found it surprising how little documentation there is about how to read execution plans, especially since there seem to be so many people who are unable to correctly read them.

Der Fall wird dadurch nicht vereinfacht, dass die Oracle Doku zumindest eine massive Fehlinformation zum Thema enthält, nämlich den Satz: "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right." Dass das so nicht stimmt, hat Jonathan Lewis schon vor mehreren Jahren erklärt (und auch da scheint die Beobachtung nicht neu gewesen zu sein), und man findet eine interessante (relativ) aktuelle Diskussion zum Thema in Iggy Fernandez' Blog. Tatsächlich ist der erste Ausführungsschritt in der Regel der am weitesten oben befindliche Schritt ohne eigene Kinder (ich hoffe, dass das a) stimmt und b) halbwegs verständlich ist). Überprüfen kann man die Reihenfolge jedenfalls mit Hilfe von SQL_TRACE, wie es Charles Hooper in seinen Kommentaren in Iggy Fernandez' Blog vorgeführt hat.

Nachtrag 09.05.2011: das cbo-Team hat gerade ein White-Paper zum Thema veröffentlicht, das allerdings eher auf die allgemeinen Grundlagen als auf die Details eingeht.

column clear

Als ich angefangen habe, mit sqlplus zu arbeiten, galt, dass man column-Formatierungen nicht wieder auf ihren Ursprungswert zurücksetzen konnte. Zumindest war das der Stand, auf dem ich mich bis heute befunden habe. Tatsächlich gibt mindestens seit Version 10 die Möglichkeit, eine Formatierung auf den default Wert zurück zu setzen:

column object_name format a10

select object_id, object_name from dba_objects where rownum < 10;

 OBJECT_ID OBJECT_NAM
---------- ----------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE
           _DATA$

        41 I_IND1
        54 I_CDEF2

9 Zeilen ausgewählt.

column object_name clear

select object_id, object_name from dba_objects where rownum < 10;

 OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------------------------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        41 I_IND1
        54 I_CDEF2
        
9 Zeilen ausgewählt.

Ich sollte mal wieder anfangen, die Dokumentation zu lesen.

System Statistics (Antognini)

Hier ein paar erinnerungswürdige Punkte, die mir zum Thema system statistics in Christian Antogninis (auch wenn ich mich wiederholen sollte: überaus lesenswertem) Buch Troubleshooting Oracle Performance begegnet sind (S. 111-119):

• cpu_cost = column_position * 20. Diese Formel gilt ab 10.2 und wurde von Joze Senegacnik beobachtet (und wird im Buch durch ein übersichtliches Test-Script bestätigt). Wichtig ist vor allem, dass die CPU-Kosten steigen, wenn man auf eine weiter hinten erscheinende Spalte zugreift, was auch schon in Cost Based Oracle zu lesen ist.
• für die workload statistics führt der optimizer noch einige sanity checks durch:
• wenn sreadtim, mreadtim oder mbrc fehlen, werden die workload statistics ignoriert
• wenn mreadtim <= sreadtim werden beide Werte mit Hilfe der noworkload-Formeln neu berechnet (anscheinend wird der ermittelte mbrc statt des Parameter-Werts für db_file_multiblock_read_count verwendet)

Auszeichnung

Im Social Network XING gibt's eine Rubrik "Auszeichnungen", wo zu lesen steht: "Geben Sie hier Auszeichnungen an, die Sie erhalten haben, z. B. 'Networker des Jahres 2010'." Als meine wertvollste aktuelle Auszeichnung sehe ich zur Zeit diese hier an ...

System Statistics Bug in 11.2

Charles Hooper hat in seinem Blog einige wichtige Punkte zum Thema system statistics zusammengefasst und dabei auch noch einmal auf Christian Antogninis Artikel über einen häßlichen Bug in 11.2 hingewiesen, der sich darin äußert, dass die Werte für SREADTIM und MREADTIM extrem hoch sind.

UNUSABLE Indexes

Dass es keine besonders gute Idee ist, Massendaten in eine Tabelle mit aktiven Indizes einzufügen, gehört zu den Dingen, die man bei der Definition von ETL-Prozessen im DWH-Kontext recht schnell herausfindet. Der APPEND-Hint kann diese Probleme zwar verringern, aber effektiver ist es, die Indizes vor dem Massendatenimport zu deaktivieren und nach dem Import neu aufzubauen (ein paar Testergebnisse dazu findet man hier).

Allerdings gibt es in diesem Zusammenhang ein paar Details, die man beachten muss. Dazu mal wieder ein Test (in 11.1.0.7). Zunächst lege ich eine harmlose Heap-Tabelle an und prüfe, wie sich diverse Index-Varianten verhalten:

create table test_nonpartitioned
( id number);

create index test_nonpartitioned_ix1 on test_nonpartitioned(id);
alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');



INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;

9999 Zeilen wurden erstellt.

Funktioniert also völlig problemlos. Jetzt der gleiche Versuch mit einem unique Index:

drop table test_nonpartitioned; 

create table test_nonpartitioned
( id number);

create unique index test_nonpartitioned_ix1 on test_nonpartitioned(id);
alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');
INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;     

insert into test_nonpartitioned
*
FEHLER in Zeile 1:
ORA-01502: Index 'DBADMIN.TEST_NONPARTITIONED_IX1' oder Partition dieses Index in nicht brauchbarem Zustand 

Wenn man im Netz nach Hinweisen zu ora-01502 sucht, findet man - vor allem in älteren Beiträgen - oft den Hinweis, den Parameter skip_unusable_indexes = true zu setzen, aber für 11.1.0.7 ist das ohnehin schon der Default-Wert. Offenbar kann man einen als unique definierten Index nicht einfach vor Massendaten-INSERTs deaktivieren und später wieder aktivieren. Tom Kyte verwies bei einer ähnlichen Frage auf die Dokumentation, die erklärt:

If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

In meinem Testfall ist allerdings überhaupt kein expliziter Constraint für die Tabelle definiert:

select * from user_constraints where table_name = 'TEST_NONPARTITIONED';

Es wurden keine Zeilen ausgewählt

Aber welche Rolle spielen Constraints? Man kann bekanntlich einen Primary Key auch durch einen non-unique index garantieren lassen:

drop table test_nonpartitioned; 

create table test_nonpartitioned
( id number);

create index test_nonpartitioned_ix1 on test_nonpartitioned(id);

alter table test_nonpartitioned add constraint test_nonpartitioned_pk primary key (id);

alter table test_nonpartitioned disable constraint test_nonpartitioned_pk;

alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');

INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;       

9999 Zeilen wurden erstellt. 

Demnach ist der (deaktivierte) Constraint kein Hindernis für die Deaktivierung des Index und das anschließende Insert.

Leider habe ich weder in der Doku noch im Netz eine kompakte Erklärung des Verhaltens gefunden, die weit über "es funktioniert auf diese Weise" hinausgeht - sollte jemand eine haben, würde ich mich über einen entsprechenden Kommentar freuen.

Parallel Execution (Table + Index)

Noch ein paar wichtige Hinweise in Randolf Geists Blog:

• der Hint NO_PARALLEL ist kein sicheres Mittel, um parallele Operationenfür eine Query zu vermeiden, da immer noch ein parallelisierter Index-Zugriff möglich ist (den man unter anderem durch den Hint NO_PARALLEL_INDEX deaktivieren kann)
• außerdem kann der cbo trotz parallel Hint einen seriellen Plan wählen, wenn die Kosten niedriger sind
• für CTAS- und INSERT AS SELECT-Operationen gibt es immer zwei Bereiche, in denen Parallelisierung möglich ist: die lesende Query und die schreibende Create- bzw. Insert-Operation.

FTS-Kosten für parallel execution in 11.2

Noch ein Test, in dem ich prüfe, ob sich an den Ergebnissen des cbo-Buchs von Jonathan Lewis in 11.2 etwas geändert hat - diesmal zum Thema parallel execution. Hier der Testablauf:

-- allgemein
alter session set db_file_multiblock_read_count = 8;

-- für noworkload Test
exec dbms_stats.delete_system_stats
exec dbms_stats.set_system_stats('CPUSPEEDNW',1000000);

-- für workload Test
begin
   dbms_stats.set_system_stats('CPUSPEED', 1000000);
   dbms_stats.set_system_stats('SREADTIM', 5.0);
   dbms_stats.set_system_stats('MREADTIM', 30.0);
   dbms_stats.set_system_stats('MBRC', 12);
end;
/

-- I/O costing (mit unterschiedlichen Degree-Angaben)
select /*+ nocpu_costing parallel(t1, 2) */ max(val)
  from t1

-- cpu costing (mit unterschiedlichen Degree-Angaben)
select /*+ cpu_costing parallel(t1, 2) */ max(val)
  from t1

-- laut Randolf Geist funktioniert der Parallel-Hint seit 11.1 
-- übrigens auch ohne Alias:
select /*+ nocpu_costing parallel */ max(val)
  from t1

--> automatic DOP: Computed Degree of Parallelism is 2

Die Tests liefern folgende Ergebnisse:

Degree   I/O  NOWORKLOAD  WORKLOAD
------  ----  ----------  --------
serial  1519        2710      5001      
2        844        1506      2778
3        563        1004      1852
4        422         753      1389
5        338         602      1111
6        282         502       926
7        242         430       794
8        211         376       695

Wenn man diese Werte mit denen vergleicht, die Jonathan Lewis in der Tabelle auf S. 29 seines cbo-Buchs aufführte, gibt es sehr viele Übereinstimmungen:

• die Werte für das I/O-costing entsprechen exakt denen bei Lewis
• noworkload-Zahlen gibt's in der Tabelle des cbo-Buchs nicht
• die workload-Zahlen entsprechen für alle Degrees >= 3 exakt denen des cbo-Buchs
• für Degree = 2 ergibt sich eine Abweichung von genau 1 (2778 zu 2779); das man mal wieder ceil vs. round sein
• für Degree = serial liegt mein Wert mit 5001 deutlich unterhalb der Angabe des cbo-Buchs (5030), was sich daraus ergibt, dass ich den CPU-Anteil des costings ausgeschaltet habe (extremer Wert für CPUSPEEDNW - vgl. noch einmal Randolf Geist). Dabei wundert mich nicht die Abweichung, sondern eher, dass sie für Degrees > 1 nicht auftritt. Ein Tests mit niedrigeren CPUSPEEDNW-Angaben zeigt, dass das CPU-Element für parallele Operationen tatsächlich keine Rolle spielt. Genaueres Nachlesen zeigt aber, dass der Herr Lewis diesen Effekt auf S. 31 bereits anspricht ...

Demnach gilt auch für 11.2 die Formel, die Jonathan Lewis für 10.1 angegeben hatte:

Cost at dgree N = ceil(serial cost / (0.9 * N)