Deterministische Funktionen

Dominic Brooks hat eine interessante Blog-Artikelserie zum Thema Deterministic Functions geschrieben.

Segment-Größe

In aller Regel lasse ich mir die Größe von Segmenten in MB anzeigen, da mich detailliertere Angaben nicht so sehr interessieren. Ein anderer Wert, den ich betrachte, sind häufig die Blocks. Bei näherem Hinsehen fällt mir aber gerade auf, dass ich mir nicht völlig sicher bin, was die Angaben verschiedener Quellen bedeuten. Dazu ein Test:

-- Anlage der Testtabelle
create table bi_test4
as
select rownum rn
     , mod(rownum, 2) col1
     , mod(rownum, 100) col2
     , mod(rownum, 100000) col3
     , lpad('*', 100, '*') col4
  from dual
connect by level <= 1000000;

-- Anlage von Statistiken
exec DBMS_STATS.GATHER_TABLE_STATS(user, 'bi_test4', cascade => true, estimate_percent => 100)

Die Größenangaben nehme ich üblicherweise aus USER_SEGMENTS. Laut Dokumentation bedeutet die Block-Angabe "Size, in Oracle blocks, of the segment":

select BYTES
     , BLOCKS
     , EXTENTS
  from user_segments
 where segment_name = 'BI_TEST4'

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
 142606336       8704         87

Wenn ich mir aber die Angaben aus USER_TABLES ansehe, finde ich diese 8704 Blocks nicht wieder:

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST4';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8413            0          0          0         116

Laut Doku sind die BLOCKS hier die "Number of used data blocks in the table". Zu den EMPTY_BLOCKS heisst es: "Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the ANALYZE statement." Na gut, dann fragen wir eben ANALYZE, obwohl ich davon eigentlich komplett abgekommen war, da ANALYZE bei der Statistikerhebung nicht mehr das Mittel der Wahl ist. Aber auch dazu sagt die Dokumentation etwas:

Oracle recommends using the more versatile DBMS_STATS package for gathering optimizer statistics, but you must use the ANALYZE statement to collect statistics unrelated to the optimizer, such as empty blocks, average space, and so forth.

ANALYZE liefert im gegebenen Fall Folgendes (und benötigt dazu mehr Zeit als DBMS_STATS.GATHER_TABLE_STATS):

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST4';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8413          291       1662          0         119

In der Summe komme ich damit wieder auf einen bekannten Wert: 8413 + 291 = 8704. Also sind die Blocks aus USER_TABLES diejenigen unterhalb der HighWaterMark? Klingt plausibel. Aber es gibt noch eine Meinung zum Thema, nämlich die des DBMS_SPACE-Packages, das Tom Kyte in seiner show_space-Procedure verwendet:

SQL> exec show_space('BI_TEST4')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           8,326
Total Blocks............................           8,704
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             291
Unused Bytes............................       4,767,744
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         218,368
Last Used Block.........................             221

Die 8704 werden dadurch ein weiteres Mal bestätigt, so dass ich davon ausgehe, dass es sich dabei um die tatsächliche Gesamtzahl der Blocks handelt. Ebenfalls bestätigt werden die Blocks oberhalb der HWM: ANALYZE und show_space kommen beide auf 291. Was ich aber noch nicht verstehe, ist die Zählung der gefüllten Blocks in show_space. Da würde ich annehmen, dass die Summe aus Unformatted Blocks + FS1 Blocks + ... + FS4 Blocks + Full Blocks wieder die 8413 aus USER_TABLES wäre - aber hier sind es 8326. Es fehlen also 87 Blocks. Mal sehen, ob ich die noch irgendwo finde...

Nachtrag: bei genauerer Durchsicht des bereits verlinkten AskTom-Threads findet sich ein Hinweis auf die Differenz - anscheind ergeben sich Abweichungen durch direct path Operationen. Deshalb ein weiterer Test:

create table bi_test5
as
select *
  from bi_test4
 where 1 = 0;

-- INSERT ohne APPEND-Hint statt CTAS, um direct path zu vermeiden
insert into bi_test5
select *
  from bi_test4;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'bi_test5', estimate_percent => 100)
analyze table BI_TEST5 compute statistics;

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST5';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8617           87       1775          0         119

SQL> exec show_space('BI_TEST5')
Unformatted Blocks .....................             240
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              51
Full Blocks        .....................           8,324
Total Blocks............................           8,704
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         227,072
Last Used Block.........................             512

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select 8324 + 51 + 1 + 1 + 240 from dual;

8324+51+1+1+240
---------------
           8617

Das wären dann wieder die Blocks aus user_tables - dafür passen die "Unused Blocks" aber nicht mehr zu den EMPTY_BLOCKS, die ANALYZE ermittelt hat. Und wieder bleibt eine Differenz von 87, die aber vermutlich gar nichts mit der anderen 87 zu tun hat.

Global Hints

Dion Cho zeigt in seinem Blog, wie man Global Hints neben einfachen Hints in verschachtelten Queries verwenden kann. In der Regel ist anscheinend die Verwendung von benamten Query-Blocks über den QB_NAME-Hint die geeignetste Lösung.

Reihenfolge von Join-Bedingungen

Jonathan Lewis beschreibt in seinem Blog einen Fall, in dem die Reihenfolge der Bedingungen in einem multi-column-Join massive Auswirkung auf den gewählten Zugriff hat.

Join-Order für HASH JOIN

Bei Jonathan Lewis findet man die Quiz-Frage nach der Anzahl der möglichen Ausführungspläne für einen HASH-Join mit vier Tabellen, bei dem die Reihenfolge der JOIN-Operationen über den LEADING-Hint vorgegeben ist. Die korrekte Antwort "acht" ergibt sich daraus, dass der cbo für jeden einzelnen JOIN die Reihenfolge der beiden Tabellen über eine Operation "swap join inputs" umkehren kann.

Segment compression und pctfree

Ein kurzer Test zum Verhältnis von segment compression und PCTFREE-Einstellung von Tabellen.

Zur Erinnerung: segment compression ist das seit 9i verfügbare Feature zur Komprimierung von Tabellen, das wiederholte Einträge innerhalb eines Blocks durch Platzhalter ersetzt. Es funktioniert nur für direct path inserts, direct path SQL*Loader- und CTAS-Operationen. Nicht zu verwechseln ist es mir der (kostenpflichtigen) advanced compression in Version 11, die alle DML-Operationen unterstützt.

Über PCTFREE steuert man den initialen Füllgrad einer Tabelle, gibt also an, wie viel Platz zunächst frei bleiben soll, damit folgende UPDATEs nicht zu Row-Migration führen.

Mein Test prüft, wie sich die Größe einer Tabelle abhängig vom Einsatz der beiden Optionen verändert:

-- Anlage einer Testtabelle mit drei Spalten,
-- von denen col2 immer gleich gefüllt ist
-- und gut komprimierbar sein sollte
create table test
as
select rownum rn
     , mod(rownum , 10) col1
     , lpad('*', 50, '*') col2
  from dual
connect by level < 100000

Über CTAS erstelle ich nun mehrere Kopien dieser Tabelle mit unterschiedlichen PCTFREE- und compress-Einstellungen:

create table test_pctfree0_nocompress pctfree 0
as
select *
  from test;

create table test_pctfree0_compress pctfree 0 compress
as
select *
  from test;

create table test_pctfree50_nocompress pctfree 50
as
select *
  from test;

create table test_pctfree50_compress pctfree 50 compress
as
select *
  from test;

-- Erzeugung von Statistiken für alle Tabellen
exec dbms_stats.gather_table_stats(user, 'TEST_PCTFREE0_COMPRESS') 
...

In USER_TABLES finden sich nun folgende Block-Angaben:

TABLE_NAME                       PCT_FREE     BLOCKS
------------------------------ ---------- ----------
TEST                                   10        451  -- Standardwert: PCTFREE 10
TEST_PCTFREE0_NOCOMPRESS                0        406  -- 451 minus 10%
TEST_PCTFREE50_NOCOMPRESS              50        811  -- 406 * 2
TEST_PCTFREE0_COMPRESS                  0         82  -- ca. 20% von 406
TEST_PCTFREE50_COMPRESS                50        158  -- ca. 20% von 811

Die Zahlen entsprechen recht exakt den Erwartungen: die Kompression reduziert die Netto-Datenmenge (im gegebenen Fall auf ca. 20%) und PCTFREE sorgt dann dafür, dass jeder Block den vorgesehenen Erweiterungsspielraum erhält. Die beiden Features sind also unabhängig voneinander.

Star Transformation

Die Leute von der cbo-Entwicklung erläutern hier die Arbeitsweise der Star Transformation. Grob vereinfacht geht es darum, die Einschränkungen der Dimensionstabellen über bitmap and und bitmap or zu kombinieren, und schließlich nur die relevanten Sätze aus der Faktentabelle zu lesen.

Nachtrag 27.07.2011: ein kompaktes Beispiel für die Voraussetzungen der Star Transformation liefert Laurent Schneider.

Crescent, Denali und die Zukunft von Microsofts BI-Lösungen

Dieser Tage wurden auf dem PASS Summit 2010 der nächste SQL Server Denali und die neue Reporting-Lösung Crescent vorgestellt. Zu letzterer haben sich die Marketing-Kollegen von Microsoft folgendes Sprüchlein einfallen lassen:

Project “Crescent” expands on the Self-Service BI capabilities delivered with PowerPivot for Excel by enabling customers to visualize and interact with the modeled data in a fun and meaningful way using highly interactive visualizations, animations and smart querying and present and share insights with others in the organization through rich storyboard presentation capabilities.

Was man sich unter "a fun and meaningful way" vorstellen muss, zeigt auch das bunte Video unter http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx.

Nun ja, ein neues Reporting-Tool, das sich gut mit PowerPivot versteht, hätte mich nicht besonders lange beschäftigt, aber dann meldeten sich einige der wichtigsten SSAS-Experten zu Wort, die die Tragweite der neuen Produktentwicklung klarer erläuterten. Zunächst Teo Lachev:

Alas, my excitement about Crescent, which I blogged about yesterday, ebbed as more news from PASS unfolded and I learned that Crescent will not support regular cubes. Yep, you've heard me right! Crescent generates DAX queries, which only work against the new Business Intelligence Semantic Model (BISM). What's BISM? It is the ambitious PowerPivot column-oriented store that got promoted from self-service BI to corporate BI. With a whiff of their magic hand, the SSAS team are about to put behind 15 years of traditional OLAP and MDX in favor of a new model.

BISM und das im SSAS verwendete Modell UDM passen offenbar nicht zusammen und werden nur durch die Klammer des OLAP-Servers zusammengehalten, in dem folglich mit MDX und DAX auch zwei Sprachen verwendet werden, die nicht ohne Weiteres zusammenpassen. Noch deutlicher wurde dann Chris Webb:

So this is a radical departure for Microsoft BI, one that could go badly wrong, but I can understand the reasoning for it. I’ve been impressed with the technology I’ve seen over the last few days and I know that if anyone can pull this off, the SSAS dev team can. However, the fact remains that in the short term BISM models won’t be able to handle many enterprise projects; SSAS cubes, which can, will be seen as a bad choice because they have no long-term future; and we’re all going to have to tie ourselves in knots explaining the roadmap and the positioning of these products to all of our customers.

Die offizielle Sicht auf die schönen neuen Microsoft-BI-Tools liefert http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx, aber ich lese darin vor allem eine Bestätigung dessen, was Chris Webb sagt. Seit drei Jahren beschäftige ich mich jetzt mit den SSAS und MDX - und jetzt, wo ich halbwegs weiß, was ich tue, sieht's so aus, als hätte diese Welt keine besonders große Zukunft mehr. Schade.

View-Merging

Im Blog der cbo-Entwickler finden sich in letzter Zeit einige interessante Erläuterungen zu Strategien des Optimizers. Unter anderem wurde das View-Merging in zwei Artikeln beleuchtet:

• http://blogs.oracle.com/optimizer/2010/10/optimizer_transformations_view_merging_part_1.html
- worin einfache Views behandelt werden
• http://blogs.oracle.com/optimizer/2010/10/optimizer_transformations_view_merging_part_2.html
- mit Erläuterungen zu komplexem View Merging, d.h. Views mit GROUP BY oder DISTINCT

Pipelined functions über mehrere Datenbanken

Der Titel dieses Eintrags beschreibt vermutlich eher vage, worum es geht: Jonathan Lewis erklärt in seinem Blog, wie man eine pipelined function verwenden kann, um den Hint driving site für eine über mehrere Datenbanken verteilte Operation einsetzen zu können (was zumindest für CTAS und INSERT AS SELECT ohne diesen Trick nicht funktioniert):

The idea is simple. If you can write a distributed select statement that takes advantage of the /*+ driving_site */ hint to work efficiently, you can wrap the statement in a pl/sql cursor loop and stick that loop into a pipelined function to maximise the efficiency of create or insert as select.

Da pipelined functions nicht unbedingt zu meinen Standardwerkzeugen gehören, hier auch noch ein Link auf Tim Halls einführende Erläuterung zum Thema und seine Definition der besonderen Eigenschaften dieser Objekte:

Table functions are used to return collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be complete before they are returned causing bottlenecks during large Extraction Transformation Load (ETL) operations. Pipelining negates the need to build huge collections by piping rows out of the function as they are created, allowing subsequent processing to start before the entire collection is fully populated.

Dump-Kompression

Die durch das Export-Utility expdp erzeugten Oracle-Dumps kann man meiner Erfahrung nach mit Komprimierungssoftware üblicherweise auf etwa ein Fünftel der Ursprungsgröße packen. Vor kurzem ist mir aber aufgefallen, dass sich Tabellen mit einem hohen Anteil von LOBs mit einigen Komprimierungsverfahren deutlich besser packen lassen:

Fall 1: Dumpfile eines Schemas ohne LOBs

Format      Größe(MB)     komprimiert auf       Laufzeit(min)
-------------------------------------------------------------
Ungepackt       2500                                        -
zip              500               20,00%                  10
rar              426               17,04%                   7
7z               347               13,88%                  17

Fall 2: Dumpfile eines Schemas mit hohem LOB-Anteil

Format      Größe(MB)     komprimiert auf       Laufzeit(min)
-------------------------------------------------------------
Ungepackt       2500                                        -
zip              696               27,84%                   7
rar               78                3,12%                   5
7z                62                2,48%                  13

Wahrscheinlich spielt der Inhalt der LOBs auch noch eine Rolle, aber für den Moment merke ich mir nur, dass der LOB-Anteil und das verwendete Verfahren die Größe des Dumps stark beeinflussen.

Bindvariablen auslesen

Tanel Poder erläutert in seinem Blog, wie man in 11.2 mit Hilfe der View V$SQL_MONITOR die Bindewerte einer aktuellen Query ermitteln kann - und warum das über das ältere Hilfsmittel V$SQL_BIND_CAPTURE nicht (zuverlässig) möglich war (was ich bisher eigentlich angenommen hatte; allerdings muss ich mich im DWH-Bereich selten um Bindewerte kümmern.

NoSQL

Eine Menge Leute haben in letzter Zeit allerlei Interessantes über NoSQL geschrieben. Da das Thema für mich nur von akademischem Interesse ist, hier nur der Link auf Curt Monashs Zusammenfassung dazu.

Inside Oracle - Julian Dyke

Von Julian Dyke stammt eine extrem hilfreiche Präsentation zum Thema Bitmap Indizes, auf die meiner Erinnerung nach selbst Richard Foote und Jonathan Lewis gelegentlich verweisen. Seit kurzem unterhält der Herr Dyke jetzt ein Blog, und ich kann mir fast nicht vorstellen, dass darin nicht allerlei Interessantes auftauchen wird.

Shrink LOB Segment

Vor ein paar Jahren habe ich hier beschrieben, wie man Lob-Segmente verkleinern kann, und das Verfahren seither auch noch ein paar Mal verwendet. Seit heute bin ich vorsichtiger, da ich Bug 5636728 begegnet bin: "FALSE ORA-1555 WHEN READING LOBS AFTER SHRINK". Die SHRINK-Operation lief erfolgreich, aber anschließend scheitern diverse Queries, die auf LOBs zugreifen, mit ora-01555-Fehlern, obwohl gar keine gleichzeitigen DML-Operationen im Spiel sind (wobei Lobs ihre Rollback-Informationen anscheinend selbst enthalten, so dass die Rollback Segmente in diesem Fall gar keine Rolle spielen). Besonders unerfreulich ist, dass auch die Dump-Erstellung mit diesem Fehler abbricht - es scheint keine Möglichkeit zu geben, die Lobs zu reorganisieren und wieder verfügbar zu machen. Da Oracle auch keinen Workaround anbieten kann, ist man auf die letzte Sicherung vor dem SHRINK angewiesen. Nein, das ist nicht ganz richtig, Oracle nennt doch einen Workaround: "Avoid using SHRINK on tables with LOB columns". Herzlichen Dank!

Grouping - Nachtrag

Im Januar hatte ich hier auf einen Artikel von Rob van Wijk verwiesen, der den ambitionierten Titel All about grouping trägt - und diesem Anspruch auch ziemlich gerecht wird. Jetzt hat der Herr van Wijk allerdings noch einen Nachtrag geliefert, der den Sonderfall des Gruppierens ohne GROUP BY näher erläutert und ebenfalls lesenswert ist.

Nologging für Indizes

Nur als kurze Aktennotiz: beim Neuaufbau von Indizes in einer Oracle 11.1 Datenbank hatte ich heute den Eindruck, dass die nologging-Option nicht den erwarteten Einfluss auf die Laufzeit hatte: mit und ohne logging benötigte die Operation die gleiche Zeit. Eine Prüfung der redo size in v$sesstat bestätigte die Annahme, dass hier tatsächlich kein Unterschied bestand.

In einer Oracle 10 Datenbank konnte ich für die gleichen Operationen deutliche Unterschiede der Laufzeiten und der Redo-Nutzung beobachten - und fing schon an, über eine grundsätzliche Umgestaltung der Logik für das Logging von CREATE INDEX-Operationen in Oracle 11 nachzudenken (da auch die Pläne der Queries sehr ähnlich aussahen).

Erst ein 10046er Trace zeigte mir dann meinen Denkfehler: die Unterschiede im Verhalten ergaben sich nur indirekt aus dem Logging. Unmittelbar verantwortlich war der Zugriff, der für 10.2.0.4 über scattered read und für 11.1.0.7 über direct path read temp erfolgte - und für direct path Operationen erfolgt aus naheliegenden Gründen kein Logging. Ein paar weiterführende Erläuterungen zur Möglichkeit der Verwendung von direct reads für FTS liefert Christian Antognini.

Hash und Nested Loop Joins - Teil 2

Zum Thema der Entscheidung zwischen Hash Joins und Nested Loop Joins hat auch Charles Hopper zuletzt einiges geschrieben - und wie beim Herrn Hopper üblich sind das recht umfangreiche Artikel mit einem sehr hohen Anteil nachvollziehbarer Tests (was ich durchaus schätze):

• http://hoopercharles.wordpress.com/2010/10/01/first-table-is-550mb-second-table-is-26gb-nested-loops-or-full-table-scan/
• http://hoopercharles.wordpress.com/2010/10/02/first-table-is-550mb-second-table-is-26gb-%E2%80%93-nested-loops-or-full-table-scan-2/
• http://hoopercharles.wordpress.com/2010/10/03/first-table-is-550mb-second-table-is-26gb-nested-loops-or-full-table-scan-3/
• http://hoopercharles.wordpress.com/2010/10/04/first-table-is-550mb-second-table-is-26gb-nested-loops-or-full-table-scan-4/

Wahrscheinlich wäre es lohnend, die Einflussfaktoren zusammenfassend aufzuschreiben.

Hash und Nested Loop Joins

Tanel Poder fragt in seinem Blog nach dem fundamentalsten Unterschied zwischen Hash und Nested Loop Joins und gibt anschließend die Antwort zur Frage:

• http://blog.tanelpoder.com/2010/09/28/q-the-most-fundamental-difference-between-hash-and-nested-loop-joins/ - enthält die Frage und erstaunlich viele Kommentare dazu, die zeigen, dass der Herr Poder zu den ersten Adressen in Oracle-Performance-Fragen gehört.
• http://blog.tanelpoder.com/2010/10/06/a-the-most-fundamental-difference-between-hash-and-nested-loop-joins/ - liefert die Antwort;

und die lautet:

"Hash joins can not look up rows from the inner (probed) row source based on values retrieved from the outer (driving) row source, nested loops can."

Ausführlicher und mit Beispielen führt er das dann unter folgender Adresse aus: http://tech.e2sn.com/oracle/sql/the-fundamental-difference-between-nested-loops-and-hash-joins. Interessant ist das allemal, obwohl ich mir fast etwas Tiefgründigeres erwartet hatte. Beinahe noch interessanter fand ich Christian Antogninis Kommentar zur Frage:

Honestly, I’m not able to say what is the *most fundamental difference*. In fact, I see four main differences:
1) HJ processes the right input at most one time; NL might process the right input many times
2) HJ are not able to apply join conditions through indexes; NL are able to do so
3) HJ does not support cross joins, theta joins, and partitioned outer joins; NL supports all types of joins
4) HJ joins supports all types of trees; NL do not support right-deep and zig-zag trees

Nr. 1 war mir halbwegs klar, Nr. 2 leuchtet ein (und wird in Tanel Poders Artikel schön illustriert), aber Nr. 3 und Nr. 4 sind mir völlig neu. Bei Tom Kyte liest man immer wieder, dass er jeden Tag etwas Neues über Oracle lernt. Ich lerne zwar auch, aber leider vergesse ich schneller, als ich lerne...

Index Skip Scan

Viele Fälle, in denen Index Skip Scans etwas Positives bewirkt haben, sind mir noch nicht begegnet, aber einige, in denen sie ziemlich unerfreuliche Folgen hatten. Ich hatte bisher noch nicht arg viel über die Frage nachgedacht, wann ein Skip Scan sinnvoll ist, aber Hemant Chitale liefert in seinem Blog eine sehr einleuchtende Erklärung:

Therefore, an Index Skip Scan is usable if the number of distinct values for the leading column(s) is sufficiently low that the number of "skips" in the Index does not become significant.

SSAS DistinctCount MeasureGroups

In aller Regel werden vermutlich mehr als 90% aller Kennzahlen im SSAS mit der Aggregationsfunktion SUM behandelt, aber die meisten andere Funktionenverhalten sich grundsätzlich relativ ähnlich wie die Summenfunktion und können mit ihr gemeinsam in einer MeasureGroup enthalten sein. Eine Ausnahme bildet die DistinctCountFunktion.

Grundsätzlich dient DistinctCount dazu, die Anzahl der unterschiedlichen Elemente in einer Menge zu bestimmen. Ein praktisches Beispiel wäre z.B. die Anzahl der Filialen, in denen der Artikel 4711 in einem bestimmten Monat verkauft wurde. Als SQL wäre das also etwa:

SELECT item_id
     , count(distinct store_id) 
  from …

Wenn man im BIDS eine neue DistinctCount-Kennzahl anlegt, wird automatisch eine zugehörige MeasureGroup angelegt. Der Grund dafür ist, dass die MeasureGroup nach dem Attributwert sortiert werden muss – und daraus ergibt sich dann auch, dass man für jede einzelne DistinctCount-Kennzahl eine neue MeasureGroup erhält.

Für das Processing bedeutet dieser Sachverhalt, dass die zugehörige SQL-Query (die nur die Schlüsselfelder und keine Kennzahlen abruft) ein ORDER BY enthält, und diese Sortierung kann für eine große Datenmenge natürlich ziemlich teuer werden. Deshalb lohnt es sich bereits, die Datenmenge durch Partitionierung zu verkleinern (wobei eine entsprechende Partitionierung der relationalen Tabelle natürlich besonders günstig wäre).

Für den Zugriff von MDX-Queries spielt die Partitionierung im Cube dann erneut eine wichtige Rolle: die Storage Engine bestimmt über die in den Header-Files enthaltenen Id-Ranges der Attribute, ob eine Partition im Rahmen einer Abfrage durchsucht werden muss (ähnliche – mäßig clevere – Range-Operationen macht der SSAS häufiger). Wenn die Partitionen nach dem DistinctCount-Attribut eingeteilt sind, dann ergibt sich z.B., dass Partition1 die Informationen für den Range Store_Id 1-10 enthält, Partition2 den Range Store_Id 11-20 etc. Wären die Partitionen nicht nach der Store_Id eingeteilt, dann würde sich für Partition1 und Partition2 wahrscheinlich jeweils ein Range Store_Id 1-20 ergeben – und entsprechend müssten beide Partitionen gelesen und die Ergebnisse anschließend gemischt werden. Das Beispiel ist jetzt ziemlich vereinfachend, aber das Prinzip sollte so aussehen. In der Praxis wäre vermutlich zusätzlich eine zeitliche Partitionierung sinnvoll, wie man in dem unter http://www.microsoft.com/downloads/en/details.aspx?FamilyID=65DF6EBF-9D1C-405F-84B1-08F492AF52DD erhältlichen WhitePaper Analysis Services Distinct Count Optimization nachlesen kann.

Man kann sich auch leicht ausrechnen, dass DistinctCount-Kennzahlen den Speicherbedarf eines Cubes deutlich erhöhen können. Ihr Einsatz sollte folglich auch genau durchdacht werden.

Visuelle Darstellung für SQL-Joins

Eine sehr schöne visuelle Darstellung von Join-Operationen liefert http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg. Ich würd es ja hier als Grafik unterbringen, bin mir aber nicht sicher, was das Urheberrecht dazu sagt, und halte mich daher feige zurück. Interessant sind auch die Erläuterungen dazu unter http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx?msg=2919602.

Der cbo und die Kosten

Jonathan Lewis schreibt in seinem Blog über ein ziemlich beunruhigendes Verhalten des cost based optimizers (cbo):

I’ve said for a very long time that in principle, ignoring bugs and restrictions, the optimizer will always choose the lowest cost option during its search for an execution path. It turns out that this isn’t true.

Anscheinend ergeben sich solche Fälle für bind variables und auch für “unknown value at compile time” (was z.B. im Fall von fine-grained access control eine Rolle spielen kann). Da geht mal wieder ein zentraler Merksatz dahin...

Nachtrag:hier noch ein Link auf einen Blog-Artikel, in dem Jonathan Lewis den Zusammenhang zwischen cost-Angabe und Laufzeit erläutert.

Scalar Subqueries

Eine sehr interessante Darstellung wichtiger Vorteile scalarer Subqueries findet man hier. Allerdings wird nicht ausgeführt, wann ein outer join die geeignetere Lösung für eine entsprechende Fragestellung ist, was auch ganz interessant wäre. Bei einer sehr großen Zahl von Lookups, die nicht gecacht werden können, würde ich die scalaren Subqueries inzwischen eher vermeiden (also wenn die Satzanzahl im Ergebnis, zu der die Lookups durchgeführt werden sollen, sehr groß ist); wenn mir noch eine plausiblere Bestimmung einfällt, liefere ich sie nach.

Datentyp Long

Adrian Billington hat in seinem Blog vermutlich alles Wissenswerte über den Datentyp Long aufgeschrieben und vor allem die Möglichkeiten erläutert, mit denen man Long-Werte auslesen kann.

Interval Partitions

Partitionen sind eines der wichtigsten Hilfsmittel, um große Datenmengen sinnvoll verwalten und performant abfragen zu können. In Oracle 11 gibt es nun (endlich) die Möglichkeit, Partitionen bei Bedarf automatisch vom System anlegen zu lassen. Dazu ein kleines Beispiel: Ich lege eine partitionierte Tabelle mit genau einer Spalte, einer Partition und einer Intervall-Angabe an:

SQL> r
  1  create table test_interval_partition
  2  (col1 number)
  3  partition by range (col1)
  4  interval (1000)
  5  (partition test_p1 values less than (1000)
  6* )

Tabelle wurde erstellt.

"Interval" gibt dabei an, welchen Bereich ein automatisch angelegtes Intervall umfassen soll. Bei der Füllung der Tabelle werden nun nach Bedarf neue Intervalle angelegt. Zunächst ein Wert, der in die initial angelegte Partition fällt:

SQL> insert into TEST_INTERVAL_PARTITION values (999);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1

Jetzt folgt ein Wert, der außerhalb der ersten Partition liegt:

SQL> insert into TEST_INTERVAL_PARTITION values (1000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2

Um den Wert unterbringen zu können, legt das System eine neue Partition SYS_P25 an, die erwartungsgemäß an Position 2 erscheint. Nun ein deutlich höherer Wert:

SQL> insert into TEST_INTERVAL_PARTITION values (10000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P26         11000                       3

Das System legt eine weitere Partition SYS_P26 an Position 3 an. Jetzt ein Wert für den Raum zwischen den beiden automatisch erzeugten Partitionen:

SQL> insert into TEST_INTERVAL_PARTITION values (5000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P27         6000                        3
TEST_INTERVAL_PARTITION        SYS_P26         11000                       4

Die neue Partition SYS_P27 rückt an Position 3 und SYS_P26 auf Position 4.

Funktioniert also alles ziemlich genau so, wie man es erwarten würde. Interval Partitioning funktioniert übrigens nur für NUMBER und DATE Partitionsschlüssel, aber andere würde ich ohnehin nicht definieren wollen.

In Harald van Breederodes Blog finden sich übrigens noch ein paar interessante Hinweise zum Löschen von interval partitions:

• http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/ 
• http://prutser.wordpress.com/2010/07/26/dropping-interval-partitions-revisited/ 

Und im Rittman Blog schreibt Peter Scott über den Neuaufbau von lokalen Indizes für interval partitions:

• http://www.rittmanmead.com/2010/08/07/more-on-interval-partitioning/

Fragmentation

Jonathan Lewis hat in den letzten Wochen wieder allerlei interessante Blog-Einträge veröffentlicht, darunter auch eine weit gefasste Begriffserklärung für den Terminus Fragmentation. In dem Teil, der den Begriff im Kontext von Indizes erläutert, findet sich eine sehr erinnerungswürdige Kurzzusammenfassung zum Thema der Wiederverwendung von Speicherplatz in Tabellen und Indizes nach Satzlöschungen:

we have to remember that there is a difference between index deletions and table deletions that makes the subsequent behaviour different. When a transaction deletes a row from a table it can reduce the row to a stub of just a few bytes before the commit takes place and immediately make use of the space this reduction frees up in the table block; when a transaction deletes a row from an index it has to leave the entire index entry in place and flag it as deleted – it cannot immediately reuse the space, it has to wait until after the commit.

Eine Operation, die in einer Transaktion Sätze löscht und einfügt, sollte einen Index demnach deutlich wachsen lassen; dazu ein kleiner Test, in dem ich eine Tabelle mit Index anlege, alle Sätze der Tabelle lösche und anschließend identische Sätze einfüge:

--  Test1: ohne commit nach Satzlöschung
SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL>  exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                    estimate_percent=>dbms_stats.auto_sample_size)

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              2197

Ohne Commit wächst der Index also tatsächlich auf die doppelte Größe.

--  Test2: mit commit nach Satzlöschung
 SQL> drop table test;

Tabelle wurde gelöscht.

SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

 SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL> exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                   estimate_percent=>dbms_stats.auto_sample_size)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099 

In diesem Fall wächst der Index nicht, da der Platz nach dem Commit freigegeben wurde. Der Herr Lewis hat also recht, was mich jetzt aber auch nicht besonders überrascht...

Für einen unique index würde das hier verwendete Beispiel übrigens eine Wiederverwendung des Speicherplatzes zeigen, da dies eine Besonderheit dieses Index-Typs ist. Bei Richard Foote findet man eine umfangreichere Erläuterung dieses Verhaltens.

Einschränkungen für Compression-Operationen

Noch ein erhellender Artikel von Randolf Geist, diesmal zu den Einschränkungen für table und index compression in verschiedenen Oracle-Versionen.

MDX-Ergebnisse in TSQL einbetten

Der Blog-Artikel MDX + T-SQL: Combining relational and multi-dimensional data into one query result set erläutert detailliert, wie man die Ergebnisse einer MDX-Abfrage über die Verwendung von LinkedServer und OpenQuery in eine SQL-Abfrage integrieren kann. Eine sinnvolle Verwendung dieser Option ist vermutlich die Verknüpfung der Inhalte von SSAS-DMVs, über die Vidas Matelis gelegentlich geschrieben hatte.

Tabellen mit mehr als 255 Spalten

Randolf Geist hat mal wieder einen sehr interessanten Blog-Artikel geschrieben, diesmal über das Thema der Statistikerhebung für Tabellen mit mehr als 255 Spalten. Zunächst erläutert er darin, warum es grundsätzlich keine besonders gute Idee ist, Oracle-Tabellen mit so vielen Spalten zu verwenden:

Oracle stores rows of conventional heap tables with more than 255 columns (where at least one column value after the 255th is non-null) in multiple row pieces even when the entire row may fit into a single block (and up to 11.2 doesn't support basic and OLTP heap table compression on tables with more than 255 columns). This leads to something that is sometimes called "intra-row chaining" which means that Oracle needs to follow the row piece pointer to access columns after the 255th one leading to multiple logical I/Os per row, up to four for a row approaching the hard limit of 1,000 columns.

Dann folgt ein nettes Stück Test-Code zum Erzeugen einer solchen Tabelle; ich habe solche Tabellen allerdings auch schon produktiv gesehen, man kommt schnell zu einer solchen Spaltenanzahl, wenn man unterschiedliche Dinge in eine Tabelle packt und durch Satzarten unterscheidet - aber ich schweife ab...

Und schließlich dann das titelgebende Thema der Probleme beim Anlegen von Statistiken für solche Monster (um's ganz kurz zu machen: die Statistikerfassung muss die Tabelle mehrfach lesen, um die Statistiken anzulegen). Wie oft beim Herrn Geist ist der Artikel etwas länger, aber das ist kein Schade.

SSAS-Instanz-Monitoring mit Perfmon

zuletzt habe ich mich damit beschäftigt, wie man die Serverbelastung auf einem Windows Server 2003 beim Processing (oder auch im normalen Abfrage-Betrieb) einer Analysis Services Datenbank (SQL Server 2008) sinnvoll überwachen kann. Das Tool der Wahl war dabei perfmon. Damit kann man ein neues „Leistungsindikatorenprotokoll“ (vermutlich ist die englische Version „Counter Log“) anlegen, diverse Indikatoren hinzufügen, eine Protokolldatei im blg-Format angeben und bei Bedarf auch einen Zeitplan angeben. Wichtig ist anscheinend, dass man auf der Seite „Allgemein“ bei „Ausführen als“ statt "standard" einen echten Benutzer angibt. Das so erstellte Protokoll kann man sich dann folgendermaßen anzeigen lassen:

• Unter Konsolenstamm/Systemmonitor im Kontextmenü „Neues Fenster“ auswählen
• In der Symbolleiste über dem (leeren) Anzeigefenster die Eigenschaften auswählen (Strg + Q; bzw. das 4 Symbol von rechts)
• Im Tab „Quelle“ die erzeugte Protokolldatei angeben und unter „Daten“ die gewünschten Indikatoren

So weit, so gut. Allerdings macht mir diese Form der Protokollierung wenig Freude, da es z.B. nicht möglich zu sein scheint, die Fenster für die Indikatorenwahl zu resizen, und weil mir Diagramme in manchen Fällen weniger sagen als Zahlen.

Alternativ kann man die Protokollierung aber auch in eine Datenbank schreiben lassen, und das ist dann schon mehr nach meinem Geschmack. Die Anleitung unter http://www.netadmintools.com/art577.html erläutert das Vorgehen recht umfassend. Hier noch mal die Kurzfassung:

• Anlage einer (zunächst leeren) ProtokolldatenbankDefinition einer ODBC-Connection (System-DSN); abweichend von der Anleitung musste ich statt der festen Portangabe die (per default eingestellt) Option „Dynamically determine port“ verwenden, da der Connection-Tests mit Port 1433 fehlschlug
• Anlage eines Counter Logs mit der Auswahl einer „SQL-Datenbank“ als Protokolldateityp und Einstellung der ODBC-DSN über „Konfigurieren“; Start des Logs
• Wenn keine Rechteprobleme auftreten (s. Ereignislog), dann werden jetzt in der Datenbank drei Tabellen angelegt
• DisplayToID: mit Informationen zu den Protokollierungsläufen
• CounterDetails: mit den Indikatoren und ihren Ids (was ich für deutlich übersichtlicher halte als die Darstellung in den Auswahlfenstern, zumal man darin via SQL natürlich sortieren und filtern kann)
• CounterData: die protokollierten Werte mit Zeitstempeln

Man kann diese Informationen joinen und darauf dann allerlei Queries absetzen und auf diese Weise dann wahrscheinlich auch herausbekommen, welche Indikatoren überhaupt interessant sind und protokolliert werden sollten.

Die Metadaten einer Protokollierung können im html-Format gespeichert werden (im Kontextmenü eines einzelnen Protokolls: Einstellungen speichern unter ...). Aus einer solchen Sicherungen lässt sich dann auch ein neues Protokoll erzeugen (im Kontextmenü des Knotens Leistungsindikatorenprotokoll: Neue Protokolleinstellungen von ...)

Der Punkt, an dem ich mir Probleme vorstellen kann, sind mal wieder die Berechtigungen (lokale oder Domänen-Accounts; Admin-Rechte; Zugriff auf perfmon und DB etc.), aber zumindest prinzipiell funktioniert das Loggen in eine DB offenbar problemlos.

Vermutlich könnte man die Ergebnisse auch noch irgendwie mit den Inhalten einer OLAPQueryLog-Tabelle verknüpfen, dazu vielleicht gelegentlich mehr.

Für Windows 2008 sieht das Vorgehen übrigens anders aus, denn dort kann man kein ODBC-Ziel für die Protokollierung angeben. Stattdessen ist aber Folgendes möglich: man kann eine DataCollector-Log-Datei (die normalerweise im blg-Format angelegt wird) mit Hilfe des relog-Tools an ein ODBC-Ziel schicken, also auf Kommandozeile:

relog C:\PerfLogs\Admin\2010-07-12\DataCollector012010-07-12_1445.blg -o SQL:pmon!

Wobei der Name der ODBC-Verbindung ist. In der Zieldatenbank werden dadurch die drei perfmon-log-Tabellen angelegt (CounterData, CounterDetails, DisplayToID), und mit den geloggten Daten gefüllt. Theoretisch kann man die Logs mit relog auch noch Filtern (oder auch in andere Log-Formate konvertieren).Vermutlich kann man den reolg-Befehl als automatischen Task ans Ende der Log-Erstellung setzen.

current SQL

Zu meinen Hobbies gehört das Schreiben und Überarbeiten von SQL-Scripts. Aufgrund der häufigen Überarbeitungen ist es eigentlich nicht sinnvoll, solche Scripte in einem statischen Blog unterzubringen, aber da der Blog relativ gut erreichbar ist, während meine google-site in gesicherten Netzen oft geblockt wird, bringe ich dieses doch einmal hier unter. Besonders aufregend ist es nicht, aber für mich ziemlich hilfreich: zunächst suche ich mir die Module aus, die SQL-Queries abgesetzt haben, und dann folgt eine Query auf v$sql, die man auf ein Modul, ein Schema, eine Laufzeit in Sekunden oder eine SQL_ID einschränken kann.

set pagesize 100
column module format a60
column SORTS format 9999999
column row_cnt format 9999999
column execs format 99999999
column substr_sql format a50
column parsing_schema_name format a30
column row_cnt format 999999999

select module, count(*)
  from v$sql
 group by module
 order by module;

select to_char(sysdate, 'hh24:mi:ss') curtime
     , parsing_schema_name
     , sql_id
     , child_number
     , substr(sql_text, 1, 100) sql_text
     , executions
     , round(elapsed_time/1000000) elapsed_sec
     , round(elapsed_time/case when executions = 0 then 1 else executions end /1000000) ela_sec_per_exec
     , buffer_gets
     , trunc(buffer_gets/decode(executions, 0, 1, executions)) lio_per_exec
     , rows_processed
     , round(cpu_time/1000000) cpu_time
     , round(user_io_wait_time/1000000) user_io_wait_time
     , round(plsql_exec_time/1000000) plsql_exec_time
     , round(java_exec_time/1000000) java_exec_time
     , to_char(last_active_time, 'dd.mm.yyyy hh24:mi') last_active_time
     , parse_calls
     , disk_reads
     , direct_writes
     , fetches
     -- , serializable_aborts
     -- , end_of_fetch_count
     -- , loads
     -- , version_count
     -- , invalidations
     -- , avg_hard_parse_time
     -- , application_wait_time
     -- , concurrency_wait_time
     -- , cluster_wait_time
     , px_servers_executions
     , sorts
     , sharable_mem
     -- , total_sharable_mem
     , typecheck_mem
  from v$sql
 where upper(module) like upper('%&module%') 
   and parsing_schema_name like upper('%&schema%')
   and round(elapsed_time/1000000) >= nvl('&secs', 0)  
   and sql_id like '%&sql_id%'
 order by elapsed_time desc;

SQL_PLAN_BASELINE

Eigentlich wollte ich seit einiger Zeit etwas über Chrsitian Antogninis hochinteressantes Buch Troubleshooting Oracle Performance schreiben, in dem ich schon seit geraumer Zeit lese, aber irgendwie komme ich nicht dazu. Stattdessen kam mir bei der Lektüre in seinem Blog die Idee, mal einen Blick auf das Thema SQL Plan Management in Oracle 11 zu werfen. Laut Oracle-Doku dient das Plan Management zu Folgendem:

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Man kann also für ein Statement einen offiziell abgesegneten Plan festlegen, der für alle folgenden Ausführungen der Query maßgeblich ist. Wenn ein potentiell besserer Plan berechnet wird, dann wird er von der Datenbank gespeichert, aber erst nach expliziter Bestätigung durch den DBA für weitere Ausführungen verwendet. Bei Tim Hall findet sich eine instruktive Einführung in das Thema.

Trotzdem folgt hier ein kurzer Versuch: Zunächst lege ich eine Testtabelle an:

-- Session 1 
create table test_baseline(col1 not null, col2, col3)
as
select rownum col1
     , lpad(' ', 200, '*') col2
     , lpad(' ', 200, '*') col3
  from dual
connect by level <= 1000000

In einer zweiten Session aktiviere ich jetzt das Erfassen von Plan Baselines, zähle die Sätze meiner Testtabelle und deaktiviere die Planerfassung wieder:

-- Session 2 
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
SELECT /* baseline_test */ COUNT(*) FROM test_baseline;
ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

Der Zugriff kann natürlich nur über FTS erfolgen, da noch kein Index für die Tabelle existiert:

SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.90

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

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 10800   (1)| 00:02:32 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |   814K| 10800   (1)| 00:02:32 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement


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

Den fehlenden Index lege ich nun in Session 1 an:

SQL> create index test_baseline_idx on test_baseline(col1);

Index wurde erstellt.

Abgelaufen: 00:00:07.45
SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.93

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

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 10830   (1)| 00:02:32 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |  4701K| 10830   (1)| 00:02:32 |
----------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement


Statistiken
----------------------------------------------------------
        112  recursive calls
         23  db block gets
      28684  consistent gets
      28726  physical reads
       4592  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der Index wird vom cbo offenbar kalt lächelnd ignoriert, aber immerhin liefert autotrace auch die Begründung dafür: SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement.

Details zur Baseline liefert die View DBA_SQL_PLAN_BASELINES:

select *
  from DBA_SQL_PLAN_BASELINES
 where PLAN_NAME = 'SYS_SQL_PLAN_8713193eb8066074';

-- hier nur die Spalte SQL_HANDLE
SQL_HANDLE
------------------------
SYS_SQL_0c6637d282f8ddb7

Mit Hilfe der Prozedur evolve_sql_plan_baseline aus dem Package dbms_spm kann ich dann prüfen, ob eine geeigneterer Plan zum Statement existiert und diesen gegebenenfalls akzeptieren:

If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').

Der Vergleich der Pläne fällt (natürlich) zugunsten des indizierten Zugriffs aus:

SQL> r
  1  SELECT dbms_spm.evolve_sql_plan_baseline(
  2                sql_handle => 'SYS_SQL_9f943b5f8713193e',
  3                plan_name  => '',
  4                time_limit => 10,
  5                verify     => 'yes',
  6                commit     => 'yes'
  7              )
  8*   FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_9F943B5F8713193E',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_9f943b5f8713193e
  PLAN_NAME  =
  TIME_LIMIT = 10
  VERIFY     = yes
  COMMIT     = yes

Plan: SYS_SQL_PLAN_8713193efefc82f9
-----------------------------------
  Plan was verified: Time used 1,77 seconds.
  Passed performance criterion: Compound improvement ratio >= 25,65
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            1469            104             14,13
  CPU Time(ms):                1456            103             14,14
  Buffer Gets:                28587           1110             25,75
  Disk Reads:                 27260           1099              24,8
  Direct Writes:                  0              0
  Fetches:                      608             32                19
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

Offenbar gibt's da eine Verbesserungsmöglichkeit, weil der neue Plan (mit Index-Zugriff) effizienter ist, so dass der neue Plan akzeptiert wird.

SQL> SELECT * 
       FROM table(dbms_xplan.display_sql_plan_baseline
                             ('SYS_SQL_0c6637d282f8ddb7', NULL, 'basic'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_0c6637d282f8ddb7
SQL text: SELECT /* baseline */ COUNT(*) FROM test_baseline
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7b8066074
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2118400422

--------------------------------------------
| Id  | Operation          | Name          |
--------------------------------------------
|   0 | SELECT STATEMENT   |               |
|   1 |  SORT AGGREGATE    |               |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |
--------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7fefc82f9
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3816238667

---------------------------------------------------
| Id  | Operation             | Name              |
---------------------------------------------------
|   0 | SELECT STATEMENT      |                   |
|   1 |  SORT AGGREGATE       |                   |
|   2 |   INDEX FAST FULL SCAN| TEST_BASELINE_IDX |
---------------------------------------------------

Wenn ich die Testquery jetzt noch einmal ausführe, verwendete sie nun auch den Index, da der verbesserte Plan über den Prozeduraufruf akzeptiert wurde:

SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.29

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

-----------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |     1 |   430   (2)| 00:00:07 |
|   1 |  SORT AGGREGATE       |                   |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_BASELINE_IDX |   814K|   430   (2)| 00:00:07 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_8713193efefc82f9" used for this statement


Statistiken
----------------------------------------------------------
         20  recursive calls
         14  db block gets
       1187  consistent gets
        161  physical reads
       5916  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ich hatte mit diesem Test ein paar Schwierigkeiten, aber nach einem Blick in Tim Halls Erläuterungen wurde der Fall deutlich klarer.

SQL Server und Oracle

Jonathan Lewis hat für das simple-talk journal eine interessante Reihe von Artikeln begonnen, in denen er SQL Server Konzepte mit den entsprechenden Oracle-Konzepten vergleicht (was mir deshalb gut gefällt, weil ich den SQL Server auch durch den idealtypischen Vergleich mit Oracle zu begreifen versuche):

• Oracle to SQL Server: Crossing the Great Divide, Part 1: behandelt terminologische Fragen, die Rolle von Instanzen, Datenbanken und Schemas, die Verwendung von Daten- und Logdateien und diverse spezifische Oracle-Konstrukte (dual, rownum, CTAS) und ihre Entsprechungen im SQL Server.
• Oracle to SQL Server: Crossing the Great Divide, Part 2: behandelt verschiedene Wege zur Erzeugung von Testdaten mit bestimmten Eigenschaften (wie Jonathan Lewis sie üblicherweise für Oracle-Tests verwendet)
• Oracle to SQL Server, Crossing the Great Divide, Part 3: erläutert die Details der Datenspeicherung (Pages, Extents) für Tabellen und Indizes. Ein Ergebnis der Untersuchung ist, dass die Verwendung von Heap Tabellen (also Tabellen ohne clustered index) bei Updates anscheinend zu Problemen führen kann, da mit Row-Migration zu rechnen ist (weil der SQL Server offenbar keinen Platz für Datenänderungen reserviert). Vermutlich sollte man einfach immer einen cli definieren, da sein Fehlen an verschiedenen Stellen unerfreuliche Effekte hervorrufen kann.
• Oracle to SQL Server: Putting the Data in the Right Place: erläutert das Verhalten von clustered indexes hinsichtlich der Speichernutzung.

In allen Teilen finden sich interessante Analysescripts, die auf den internen DMVs basieren. Im Rahmen der Erläuterungen hat sich auch meine Erinnerung bestätigt, dass eine Tabelle mit clustered index im SQL Server nur ein physikalisches Objekt umfasst: die Blattknoten des Index enthalten die pages der Tabelle.

Interne Repräsentation von Number-Werten

Auf Steve Adams ixora-Seite findet man eine kurze Erläuterung zur internen Repräsentation von NUMBER-Werten. Die interne Darstellung entspricht einer wissenschaftlichen Notation mit Exponent und Mantisse. Dabei enthält das erste Byte der Repräsentation ein Bit für das Vorzeichen und den Exponenten, dann folgt die Mantisse, wobei jedes Byte normalerweise zwei Dezimalstellen darstellt. Mit der Funktion DUMP kann man die interne Repräsentation einer Zahl darstellen lassen:

select dump(1.23) from dual;

DUMP(1.23)
---------------------
Typ=2 Len=3: 193,2,24

select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

select dump(1230000) from dual;

DUMP(1230000)
---------------------
Typ=2 Len=3: 196,2,24

Der Exponent ändert sich (193 -> 194 -> 196), während die Mantisse identisch bleibt (2, 24). Bei Gelegenheit sollte ich mir die Details noch etwas genauer anschauen.

SQL_TRACE für Einzelstatements

noch ein Link - eigene Gedanken mache ich mir dieser Tage nicht ...
Diesmal auf Tanel Poders Blog, in dem er erwähnt, dass es in 11.2 möglich ist, SQL Trace für eine bestimmte SQL-Id zu aktivieren. Das sieht dann etwa folgendermaßen aus:

alter session set events 'sql_trace[SQL: 9hb0kku1ncc9b] 
              {callstack: fname opiexe} 
              plan_stat=all_executions,wait=true,bind=true';

Bei Tanel Poder erscheint noch die Option {pgadep: exactdepth 0}, die in meinem System (11.1.0.7.0) nicht funktioniert hat.

Nachtrag 17.11.2011: Maria Colgan beschreibt im Blog der cbo Entwickler das entsprechende Verfahren zur Erstellung eines Optimizer-Traces (Event 10053).

Locks

Und noch eine Referenz auf Jonathan Lewis' Blog, wo er die Matrix der Lock-Typen in Oracle darstellt.

32bit und 64bit

In Jonathan Lewis' Blog findet sich ein sehr interessantes Fallbeispiel, in dem der Umstieg von 32bit auf 64bit zu Problemen mit der Speichernutzung führt.

Three kind of lies

"There are three kinds of lies: lies, damned lies, and statistics."

Benjamin Disraeli (behauptet jedenfalls Mark Twain)

Und was ist mit BI?

SQL Server DMVs

Allmählich komme ich dahinter, dass die Dynamic Management Views (DMVs) des SQL Servers ähnlich viele Details über den Zustand des Systems liefern wie die v$- bzw. dba/all/user-Views in Oracle (was nicht allzu sehr verwundern sollte, da die Speicherung interner Verwaltungsinformationen in Tabellenform meiner Erinnerung nach zu den Anforderungen Codds an ein RDBMS gehört). Die folgenden Analysequeries laufen alle auf Version 2008.

So kann man sich z.B. die teuersten Queries des Datenbank-Caches anzeigen lassen:

select top 50
       SUBSTRING(  st.text
                 , (qs.statement_start_offset/2) + 1
                 , ((CASE qs.statement_end_offset
                          WHEN -1 THEN DATALENGTH(st.text)
                          ELSE qs.statement_end_offset
                     END - qs.statement_start_offset)/2) + 1) AS StatementText
     , qs.last_execution_time
     , qs.execution_count
     , qs.total_elapsed_time/1000000 total_elapsed_sec
     , qs.last_elapsed_time/1000000 last_elapsed_sec
     , qs.total_logical_reads
     , qs.last_logical_reads
     , qp.query_plan
  from sys.dm_exec_query_stats qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  
 cross apply sys.dm_exec_query_plan(qs.plan_handle) AS qp  
 order by qs.last_elapsed_time desc

Oder die Queries, für die laut Optimizer Indizes fehlen:

select st.text
     , qp.query_plan
     , qs.*
  from Sys.dm_exec_query_stats qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) st  
 cross apply sys.dm_exec_query_plan(qs.plan_handle) qp  
 where convert(nvarchar(max), qp.query_plan) like '%MissingIndex%'

Oder die aktuell laufenden Abfragen:

select ses.login_name
     , ses.host_name
     , ses.program_name
     , st.text
     , req.session_id
     , req.start_time
     , req.status
     , req.command
     , req.blocking_session_id
     , req.cpu_time/1000 cpu_secs
     , req.total_elapsed_time/1000 ela_secs
     , req.logical_reads
     , qp.query_plan
  from sys.dm_exec_requests req
 inner join sys.dm_exec_sessions ses
    on req.session_id = ses.session_id
 cross apply sys.dm_exec_sql_text(req.sql_handle) AS st  
 cross apply sys.dm_exec_query_plan(req.plan_handle) AS qp

Oder auch die aktuell verbundenen Sessions

select ses.login_name
     , ses.*
  from sys.dm_exec_sessions ses
 where login_name not in ('sa', 'jira')
 order by ses.login_name

Detailliertere Erläuterungen zu den verwendeten DMVs findet man hier. Auf den untergeordneten Seiten findet man dort auch allerlei Analysequeries.

MDX Scope

Mit MDX habe ich mich in den letzten beiden Jahren relativ viel beschäftigt, darüber hier aber noch kein Wort verloren. Dafür gab's mehrere Gründe, die aber - wenn überhaupt - ein andermal genannt werden sollen. Ein wichtiger Punkt ist allerdings, dass mir MDX nicht besonders liegt, und dass ich mir wichtige Details einfach nicht merken kann. Um dem abzuhelfen, versuche ich hier ein paar Notizen abzulegen.

Ein erstes Konzept, das mir regelmäßig Unbehagen verursacht, ist der SCOPE. Ein SCOPE ist ein Teilbereich eines Cubes (also ein Subcube), für den bestimmte Sonderregeln definiert werden. Man kann in ihm Überschreibungen festlegen, also dafür sorgen, dass Kennzahlen im SCOPE anders gefüllt werden als außerhalb des SCOPEs. Interessant ist dabei vor allem das unterschiedliche Verhalten von Basiskennzahlen und berechneten Kennzahlen (calculated measures). Dazu ein Beispiel. Im folgenden MDX-Script wird ein calculated measure test_calc definiert. Außerdem existiert im Cube eine Basiskennzahl test_mat, die zunächst mit NULL gefüllt ist. Beide Kennzahlen werden in einem SCOPE auf Artikelebene überschrieben:

CALCULATE; 

CREATE MEMBER CURRENTCUBE.[Measures].[test_calc]
 AS null, 
VISIBLE = 1  ; 

scope [DIM_PRODUCT].[ITEMID].[ITEMID].members; 
test_calc = 1;
test_mat = 1;
end scope;

In der Artikeldimension (DIM_PRODUCT) sind über Attributbeziehungen mehrere Ebenen definiert: über der Artikelebene liegen mehere Warengruppenebenen. Auf der Ebene der SCOPE-Definition, also auf der Artikelebene (ITEMID), liefern beide Kennzahlen den gleichen Wert:

Auf der übergeordneten Warengruppenebene (ITEMGROUPID LEVEL1) hingegen wird die Basiskennzahl weiter aggregiert, während für die berechnete Kennzahl kein Wert definiert ist:

Der SCOPE bestimmt den Ausschnitt des Würfels, in dem eine neue Berechnungslogik gilt, aber für Basiskennzahlen betrifft die Zuordnung alle höheren Ebenen, während für Calculations keine Aggregation von Werten erfolgt, sondern die Berechnung auf jeder Ebene durchgeführt wird.

MINUS und EXCEPT

Noch ein Eintrag zum SQL Server: in dessen SQL-Dialekt fehlt das Schlüsselwort MINUS, mit dem man in Oracle die Elemente einer Menge bestimmen kann, die in der zweiten Menge fehlen. Allerdings fehlt tatsächlich nur das Schlüsselwort - ihm entspricht beim SQL Server EXCEPT:

-- Oracle
select 1 from dual
union
select 2 from dual
minus
select 2 from dual;

-- SQL Server
select 1
union
select 2
except
select 2

MS SQL Kompression

nur als kurze Notiz: ähnlich wie in Oracle kann man auch im SQL Server das physikalische Speichervolumen einer Tabelle reduzieren: zum einen durch Verwendung der kleinsten geeigneten Datentypen (row compression) und darüber hinaus durch Ersetzung wiederkehrender Inhalte durch Platzhalter und die Verwendung einer Hash Map zur Auflösung dieser Ersetzungen (page compression). Die Komprimierung kann über die SSMS-GUI erfolgen oder auch über folgendes Kommando:

USE [Name_der_Datenbank]
ALTER TABLE [dbo].[Name_der_Tabelle] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) 

SQL*Net Compression

In Jonathan Lewis' Blog findet sich ein Beitrag zum Thema der SQL*Net Compression, die eine Reduzierung der Daten, die vom Server zum Client transportiert werden, ermöglicht. Grundsätzlich funktioniert das offenbar so, dass für jeden Satz entschieden wird, ob die Daten einer Spalte identisch mit den Inhalten der entsprechenden Spalte im letzten transferierten Satz sind - und auf diese Weise spielt die Sortierung der Daten eine wichtige Rolle für die Effizienz dieser Komprimierung (die eigentlich eine "de-duplication" ist). Interessanterweise kann ich die Ergebnisse nachvollziehen, wenn ich mit einer String-Spalte teste, wie sie Jonathan Lewis verwendet, also:

create table test
as
select rownum rn
     , lpad(dbms_random.string('U',2),40,'X')  v1
  from dual
connect by level < 10000;

-- Ergebnis:
-- 464825  bytes sent via SQL*Net to client - unsortiert
-- 118679  bytes sent via SQL*Net to client - sortiert nach v1

Wenn ich aber eine harmlose nummerische Spalte mit Wiederholungen verwende, sehe ich zwar auch Unterschiede zwischen der sortierten und der unsortierten Ausführung, aber sie gehen seltsamerweise in die andere Richtung:

create table test
as
select rownum rn
     , mod(rownum, 3)  v1
  from dual
connect by level < 10000;


-- Ergebnis:
-- 81653  bytes sent via SQL*Net to client - 
unsortiert
-- 81653  bytes sent via SQL*Net to client - sortiert nach rn


-- 90971  bytes sent via SQL*Net to client - sortiert nach v1

Mag sein, dass sich NUMBER-Werte bezüglich der Komprimierung tatsächlich anders verhalten. Oder mir entgeht noch irgendetwas Entscheidendes.

Nachtrag: nachdem mir auf Anhieb keine plausible Erklärung eingefallen war, habe ich Jonathan Lewis im Blog danach gefragt und folgende Antwort bekommen:

I think this may be a result of a fixed overhead for compression (or de-duplication, which might be a slightly better term). I think a row probably has to say “this column is the same as last time”, so I would look for a fixed size token to represent “same as last row”.
Test: create a table of 1,000,000 rows with a constant (e.g. 1000000001) and a variable (e.g. 1000000001 + rownum) column, set a large arraysize (5,000 is the max) then select each column in turn with autotrace traceonly statistics. See if the difference in volume is significant. It is, but there is a minimum volume of data per row which means you don’t see much of a saving with a single column of small numbers.

Meiner Erfahrung nach bekommt man auf thematisch passende Fragen in den Blogs meiner Sidebar immer gute Antworten (so war's jedenfalls auch bei Richard Foote und Charles Hooper).

Unschärfen mit dem Datentyp NUMBER

Dass ich es mit numerischen Datentypen nicht besonders genau nehme, gehört zu den schlechten Angewohnheiten, die ich bei der Arbeit mit Oracle im Lauf der Zeit angenommen habe. Da für Oracle ohnehin alles NUMBER ist, neige ich dazu, die Definitionen von Precision und Scale nur als Constraints zu betrachten, mit denen der Bereich erlaubter Werte eingegrenzt werden kann. Das ist aber offenbar nur ein Teil der Geschichte, wie der folgende Test zeigt:

Im praktischen Fall wollte ich mir aus einer Kennzahl eine Stammdateninformation extrahieren, die darin enthalten war. Die folgende Query zeigt die Struktur der Daten: neben einer Artikelnummer liegt eine Mengenangabe (QUANTITY) vor und eine zusätzliche Angabe, die ursprünglich als Menge * Packungsgröße (QUANTITY_BASEQUANTITY * QUANTITY) gebildet worden war:

select ITEMID
     , QUANTITY_BASEQUANTITY
     , QUANTITY
  from fact_sales
 where ITEMID = 334150
   and rownum < 10;

ITEMID QUANTITY_BASEQUANTITY   QUANTITY
------- --------------------- ----------
 334150                   4,8          1
 334150                   4,8          1
 334150                   9,6          2
 334150                   9,6          2
 334150                   9,6          2
 334150                   4,8          1
 334150                   9,6          2
 334150                   4,8          1
 334150                   4,8          1

Meine Vermutung war, dass die Packungsgrößen durch eine Division QUANTITY_BASEQUANTITY/QUANTITY eindeutig pro Artikel zu ermitteln wäre:

create table item_salesquantity
as
select distinct ITEMID
     , QUANTITY_BASEQUANTITY/QUANTITY Salesquantity
  from fact_sales;

select distinct salesquantity
  from item_salesquantity 
 where ITEMID = 334150;

SALESQUANTITY
-------------
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8

8 Zeilen ausgewählt.

Das kam etwas überraschend: auf den ersten Blick sehen alle Werte identisch aus, aber da DISTINCT sie unterscheidet, sind sie's offenbar nicht. Mit VSIZE lässt sich die physikalische Größe der Werte bestimmen:

select distinct salesquantity, vsize(SALESQUANTITY)
  from item_salesquantity 
 where ITEMID = 334150
 order by vsize(SALESQUANTITY);

SALESQUANTITY VSIZE(SALESQUANTITY)
------------- --------------------
          4,8                    3
          4,8                   10
          4,8                   10
          4,8                   11
          4,8                   11
          4,8                   21
          4,8                   21
          4,8                   21

8 Zeilen ausgewählt.

Offenbar gibt es also tatsächlich Unterschiede und diese beruhen anscheinend darauf, dass schon die zugrunde liegenden Werte bezüglich der VSIZE unterschiedlich sind:

create table item_salesquantity_noagg
as
select ITEMID
     , QUANTITY_BASEQUANTITY
     , QUANTITY
     , QUANTITY_BASEQUANTITY/QUANTITY Salesquantity
  from fact_sales;
  
select distinct QUANTITY_BASEQUANTITY
     , vsize(QUANTITY_BASEQUANTITY)
     , salesquantity
     , vsize(SALESQUANTITY)
  from item_salesquantity_noagg
 where ITEMID = 334150
 order by QUANTITY_BASEQUANTITY;
 
QUANTITY_BASEQUANTITY VSIZE(QUANTITY_BASEQUANTITY) SALESQUANTITY VSIZE(SALESQUANTITY)
--------------------- ---------------------------- ------------- --------------------
                  4,8                           10           4,8                   10
                  9,6                           10           4,8                   10
                 14,4                           10           4,8                   21
                 19,2                           10           4,8                   11
                   24                            2           4,8                    3
                 28,8                           10           4,8                   10
                 33,6                           10           4,8                   21
                 38,4                           10           4,8                   11
                 43,2                           10           4,8                   21
                   48                            2           4,8                    3

10 Zeilen ausgewählt.

Die Moral von der Geschichte ist wohl, dass ich NUMBER-Werte weniger indifferent behandeln sollte. Als kurzfristiger Fix diente eine Rundung, die das gewünschte Resultat brachte:

create table item_salesquantity_round
as
select distinct ITEMID
     , round(QUANTITY_BASEQUANTITY/QUANTITY, 4) Salesquantity
  from fact_sales; 

select distinct salesquantity, vsize(SALESQUANTITY)
  from item_salesquantity_round

where ITEMID = 334150
 order by vsize(SALESQUANTITY);

SALESQUANTITY VSIZE(SALESQUANTITY)
------------- --------------------
          4,8                    3