MDX-Syntax-Check

Chris Webb zeigt, wie man im SSMS überprüfen kann, ob eine MDX-Query syntaktisch korrekt ist, ohne sie tatsächlich auszuführen. Ich bin zwar vom Ausführen auch syntaktisch korrekter MDX-Queries inzwischen weitgehend abgekommen, halte das aber trotzdem für recht nützlich. Herr Webb jedenfalls sieht folgende Einsatzmöglichkeiten:

This functionality could be useful in situations where you wanted to test the syntax of an MDX query or indeed just a calculation – it would allow you to do this without actually running the query and then killing it (and some queries don’t die immediately when they’re cancelled, as you might know).

Hash table size für Lookup-Ergebnisspeicherung

Jonathan Lewis hat dieser Tage in seinem Blog einen Fall untersucht, in dem ein Funktionsaufruf einen Tabellenzugriff enthält, der im Execution Plan nicht erscheint (was auch bei FILTER-Operationen vorkommen kann). Wenn man die rowsource-Statistiken und die projection-Informationen betrachtet, wird deutlich, dass der Funktionsaufruf in einem SORT UNIQUE step untergebracht ist. Festhalten kann man auf jeden Fall, dass der Plan die Operation nicht besonders deutlich abbildet.

Ausgehend vom gegebenen Beispiel habe ich ein paar Experimente durchgeführt, die ein Ergebnis lieferten, das ich so nicht erwartet hatte:

drop table t1;
drop table t2;

-- nur 250 unterschiedliche IDs, statt 2500 im Original
create table t1 tablespace test_ts
as
select
    mod(rownum, 250)          id,
    lpad(rownum,200)    padding
from    all_objects
where   rownum <= 2500
;

create table t2 tablespace test_ts
as
select  * from t1
;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

-- Funktion als DETERMINISTIC definiert
create or replace function f (i_target in number)
return number deterministic
as
    m_target    number;
begin
    select max(id) into m_target from t1 where id <= i_target;
    return m_target;
end;
/

select  /*+ gather_plan_statistics */
    id
from    t1
minus
select
    f(id)
from    t2
;

-- letzte Ergebnisspalten aus Gründen der Übersichtlichkeit abgeschnitten
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.16 |   54670 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - STRDEF[22]
   2 - (#keys=1) "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - (#keys=1) "F"("ID")[22]
   5 - "ID"[NUMBER,22]

Die Änderung im Test liegt nur in der Reduzierung der ID-Werte und der Definition der Funktion als DETERMINISTIC. Meine Annahme war, dass der Funktionsaufruf auf diese Weise nur einmal für jeden distinkten Wert ausgeführt werden müsste, woraus sich 250 FTS auf die Tabelle T1 ergeben sollten. Daraus ergab sich die Erwartung, dass die Buffers-Angabe in step 4 bei 19250 (= 250 * 77) liegen würde. Tatsächlich lautete das Ergebnis aber 54670 (= 710 * 77). Woher kommt die Abweichung? Die Antwort lieferten mir die Kommentare von Sayan Malakshinov und Kapitel 9 (Query Transformation) in Cost Based Oracle: die intern zur Speicherung der Zwischenergebnisse des Lookups verwendete HASH TABLE ist nicht groß genug, um hash collision zu vermeiden. Um die Größe dieser Struktur zu verändern, kann man den Parameter _query_execution_cache_max_size anpassen (default: 65536; wie immer gilt, dass die Änderung von underscore-Parametern auf eigene Gefahr erfolgt):

alter session set "_query_execution_cache_max_size"=262144;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.08 |   29743 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

alter session set "_query_execution_cache_max_size"=2097152;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.06 |   19348 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

Mit den 2M für _query_execution_cache_max_size bin ich dann mit 19348 Buffers schon recht nah am erwarteten Ergebnis von 19250 - und das genügt mir in diesem Fall.

Baseline-Zuordnung in SPM

Maria Colgan erläutert, dass die Zuordnung von Baselines zu SQL-Queries über Signaturen (das SQL_HANDLE in DBA_SQL_PLAN_BASELINES) erfolgt, die über Schema-Grenzen hinweg identisch sind. Da bei der Berücksichtigung von Baselines aber immer auch das Schema ausgewertet wird, ist das kein Problem - alles andere hätte mich auch gewundert.

Testdatengenerierung im SQL Server

Aaron Bertrand stellt im SQLPerformance-Blog einige Möglichkeiten zur Generierung von Sequenzen und Testdaten im SQL Server vor:

• Generate a set or sequence without loops – part 1: stellt einige Verfahren zur Datengenerierung vor (Verwendung einer vorliegenden Tabelle, CTE, rekursive CTE) und misst die Laufzeiten zur Erzeugung von 1000 rows.
• Generate a set or sequence without loops – part 2: testet die Laufzeiten bei der Genereirung größerer Datenvolumen. Dabei ist das Verfahren mit der Verwendung einer rekursiven CTE konkurrenzlos langsam, während die übrigen Vorgehensweisen eine ähnliche Performance aufweisen.
  
• Generate a set or sequence without loops – part 3: zeigt, wie man mit unterschiedlichen Generierungsverfahren lückenlose Sequenzen erzeugen kann, und vergleicht die Performance dieser Verfahren.

Gelegentlich könnte ich die Ergebnisse mal mit Jonathan Lewis' Überlegungen zur Testdatenerzeugung im SQL Server vergleichen.

Cardinalities für HAVING

Randolf Geist erläutert in seinem Blog die Cardinality-Schätzungen des CBO für Gruppierungen mit einer HAVING clause. Während die Kalkulation für GROUP BY in vielen (aber längst nicht allen) Fällen recht solide sind, kommen beim HAVING immer default-Werte ins Spiel, was natürlich zu recht unerfreulichen Resultaten führen kann. Problematisch wird der Effekt vor allem dann, wenn die Gruppierung nicht am Ende der auszuführenden Operationen steht und von ihr weitere Schritte abhängen (was sich z.B. auch durch GROUP BY PLACEMENT-Transformationen ergeben kann), die dann auf fehlerhaften Cardinalities beruhen. Als Workaround für das im Artikel vorgestellte Problem kommen die (nicht dokumentierten, aber immer wieder nützlichen) Hints CARDINALITY und OPT_ESTIMATE in Frage, die aber natürlich das Wissen um die tatsächliche Größe der Ergebnismenge voraussetzen - und in Produktionssystemen in der Regel nichts zu suchen haben - oder die Verwendung eines zusätzlichen ROWNUM-Prädikates, das das Pushing der Bedingung einer rahmenden Query als HAVING-caluse in die umrahmte View verhindert; wobei diese Variante dann wiederum andere Schwierigkeiten hervorrufen kann.

Details zur INTERNAL_FUNCTION

Tanel Poder liefert in seinem Blog einige Informationen zum Auftreten der INTERNAL_FUNCTION in den Prädikat-Informationen eines execution plans. Sie tritt einerseits bei impliziten Typkonvertierungen auf, kann aber auch an Stelle komplexerer OR-Verknüpfungen erscheinen und ist das Ergebnis einer UNPARSE-Operation, mit der DBMS_XPLAN die textuelle Repräsentation des Ausführungsplans aus dem internen Plan erstellt. Dabei gilt:

By the way, why do I keep saying “binary” execution plan and in double quotes? It’s because I want to emphasize that the real execution plan that Oracle executes is not in the text form like we see on the screen, the text is just generated for humans, for troubleshooting reasons. The execution plan is not a real executable binary (as in oracle.exe) either, it’s not directly fed to the CPUs for execution. The physical execution plan in the library cache child cursor is a bunch of opcodes, object_ids and pointers for defining the hierarchy and order of rowsource execution. It’s the SQL execution engine, which then loops through these opcodes, decodes them and knows what to do (which rowsource function to call) next.

Interessanterweise ist EXPLAIN PLAN bei der UNPARSE-Operation anscheinend manchmal geschickter und schafft es, OR-Prädikate aufzulösen, die DBMS_XPLAN in die INTERNAL_FUNCTION übersetzt.

Compression in Oracle

Jonathan Lewis hat mit einer Artikelserie zum Thema Compression in Oracle für redgates großartige All Things Oracle Seite begonnen. Hier ein paar Punkte daraus, die mir besonders erinnerungswürdig erscheinen (aber kein vollständiges Exzerpt darstellen sollen):

Compression in Oracle – Part 1: Basic Table Compression

• enthält eine Testreihe mit diversen unterschiedlichen Operationen, die zeigt, dass basic compression nur bei direct path operationen (CTAS, INSERT APPEND, ALTER TABLE ... MOVE) wirksam ist und dass sie implizit den pctfree-Wert auf 0 setzt.
• basic (und OLTP) compression führen eigentlich keine compression, sondern eine deduplication durch, indem sie wiederholte token im block in Symboltabellen speichern und dann darauf referenzieren.
• Oracle kann die Reihenfolge der Werte im Block umstellen und mehrere Elemente in einer Kombination zusammenfassen, um die Komprimierung zu verbessern - "Oracle can rearrange the order that the columns are stored in this block to put those tokens side by side, and create a new token the represents the combination of the two individual tokens". Solche Umstellungen der Spaltenreihenfolge sind in der Angabe "perm_9ir2[4]={ 2 0 1 3 }" im Block dump sichtbar: "for this block Oracle has rearranged (permuted) the order the columns are stored so that the thing stored at column 2 in this block is column 0 in the table definition, column 0 is really column 1, column 1 is column 2 and column 3 is (still) column 3".
• in den row-informationen des block dumps kann man die Wirkung der compression in der Differenz zwischen der Angabe tl (= total length) und den Angaben der Spaltenlänge + 1 lock byte + 1 flag byte + 4 byte für die 4 column length Angaben + 1 byte column count (cc) erkennen. Im Beispiel beträgt die tl tatsächlich nur 5 byte, die die bindmp (bind map) Angabe enthalten: "These five bytes are the flag byte (0x2c = ‘–H-FL’), the lock byte, the “stored” column count – i.e. the number of columns stored at this location, which is just one – and the next two bytes tell us that that one “column” is a token representing 4 consecutive and we need to look at token 0×31 of the token table".
• im Beispiel verweist die bindmp-Angabe wiederum auf weitere (sub)token und enthält auch Angaben zur Anzahl der Verwendung von token (also zur Häufigkeit der Ersetzungen).
• für das Lesen der Spalten-Werte ist somit keine decompression erforderlich, Oracle "simply re-constructs the row you need by hopping back and forth between the row directory and the row pieces (the code may even avoid visiting some tokens – the single column ones – if the column values aren’t needed for the SQL statement)".
• das Springen zwischen den token ist CPU-intensiv. "As a side effect, because Oracle will have to hold (pin) the block for some time to reconstruct rows, you may find that your code will do fewer “consistent gets – examination” which means more activity on the “cache buffers chains” latch. Of course, we hope that the extra CPU time will be offset by the smaller number of physical reads we may have to do because we’ve packed our rows into a smaller number of blocks, which may allow us to keep more data cached for longer."

Compression in Oracle – Part 2: Read-Only Data

• beim Löschen eines Satzes müssen zusätzlich die Angaben zur Häufigkeit der Verwendung eines Komprimierungs-Tokens korrigiert werden: der Aufwand von DELETEs ist demnach im Fall komprimierter Tabellen größer als in Fällen ohne Komprimierung.
• es kann vorkommen, dass ein nicht mehr verwendetes Token mit der Verwendungshäufigkeitsangabe 0 im Block stehen bleibt.
• UPDATEs führen zur Deaktivierung der compression für den veränderten Satz (also zu einer row expanison) - allerdings nur, wenn das UPDATE komprimierte Attribute betrifft. Ein Rollback bringt einen solchen Satz allerdings wieder zurück in den komprimierten Zustand.
• Oracle lässt trotz PCTFREE = 0 einige Byte pro Block für minimale Reorganisationsoperationen frei. Für moderate UPDATE-Häufigkeiten wäre ein PCTFREE-Wert > 0 sinnvoll.
• seit 11.2.0.3 sollte ein Update, das keine tatsächliche Änderung darstellt, auch keine expansion ausführen. Allerdings scheint das nicht immer zu funktionieren.

Compression in Oracle – Part 3: OLTP Compression

• OLTP compression lässt den Standardwert für PCTFREE stehen (also in der Regel 10)
• sie wirkt (erwartungsgemäß) auch bei conventional path operations
• die Statistiken zum Thema heap block compress haben eigentlich nicht unmittelbar mit dem Feature compression zu tun, sondern zählen, wie häufig der free space im Block reorganisiert wurde (und die row pieces zum Ende des Blocks hin verschoben wurden).
  
• die Anzahl der heap block compress Operationen ist im Beispiel deutlich höher als die Blockanzahl, was ein Hinweis darauf ist, dass jeder Block mehrfach reorganisiert werden muss
• die compression erfolgt sukzessive, wenn neue Einfügeoperationen Platz benötigen. Dabei wird der neu hinzugefügte Satz, der die Operation auslöst, selbst nicht komprimiert.
• reine Update-Operationen triggern die OLTP compression nicht, wie Randolf Geist gelegentlich gezeigt hat.
• OLTP compression "doesn’t compress for all operations, it compresses only for inserts, and the benefits it has over basic compression are that (a) it leaves 10% of the block free for updates, and (b) it doesn’t require direct path inserts to trigger compression. Given the limitations on how it works you may find that the problems it brings might make it something you want to avoid."
• der Komprimierungseffekt für OLTP compression ist (etwas) niedriger als der für direct path compression.

Compression in Oracle – part 4: Basic Index Compression

• index compression betrifft nur die führenden Index-Spalten.
• die Informationen im row heap eines Index-Blocks sind nicht (notwendigerweise) sortiert (können es temporär aber sein): die Sortierung liegt nur im row directory vor.
• die Komprimierung ist eine Deduplizierung der n führenden Spalten.
• anders als im Fall der table compression kann ein token im Fall der index compression nur an der passenden Stelle (= Spalte) verwendet werden.
• im block dump erscheinen für die dedulizierten token prefixes: "each takes 7 bytes (len = 7) consisting of the flag byte (flag:) that shows it’s a prefix entry, a lock byte (that never seems to get used) and, for each column (and there is only one), a length byte and the four bytes that make up the prefix". Dazu kommt eine Angabe prc: "(possibly “prefix usage count”) reporting the number of suffixes associated with this prefix."
• für den Rest des Index-Eintrags findet sich die Angabe "psno (possibly “prefix sequence number”), that tells us which prefix the suffix belongs to; again this item is derived, not something stored with the row."
• die byte Adresse eines Index-Eintrags erscheint in Brackets [...] hinter der row#-Angabe. Diese Angaben erscheinen (als hex Werte) im row directory (in umgekehrter Reihenfolge). Dort finden sich auch die Angaben zu den Startwerten der deduplizierten token (ebenfalls in umgekehrter Reihenfolge). Aus den Angaben kann Oracle die prc- und psno-Werte ableiten.

Compression in Oracle – part 5: Costs of Index Compression

• zeigt, dass bereits ein Präfix von nur einem Byte Länge zu sinnvoller compression führt, sofern es pro Präfix in der Regel mindestens vier Datensätze gibt (was anhand eines Beispiels belegt und theoretisch erklärt wird: pro Satz werden zwei Byte eingespart(Inhalt + Length Byte), was gegen den Overhead von prefix (4 Byte) und prefix directory (4 Byte) zu rechnen ist).
• eine Reduzierung der Index-Größe kann allerdings höhere contention für einzelnen Index-Blocks hervorrufen.
• beim Select ist der Overhead größer, wenn es sehr viele Präfixe und nur wenige zugehörige rows gibt. Insgesamt ist er aber in der Regel nicht signifikant (diese Einschätzung deckt sich mit meinen Erfahrungen).
• durch die Compression ändert sich das Costing des Index (da darin die LEAF_BLOCKS eine zentrale Komponente sind): ein komprimierter Index kann dadurch interessanter werden als ein anderer nicht komprimierter Index, der bis dahin die erste Wahl des CBO gewesen ist.
• für DML-Operationen müssen im komprimierten Index zusätzliche Änderungen durchgeführt werden (Aktualisierung des prefix directory (usage counter) und der zugehörigen row). Für DML-Operationen ist der Overhead der Compression deutlich größer als im Fall von Select-Zugriffen.

Row Chaining und Session-Statistiken

Jonathan Lewis hat in seiner neuesten Quiz Night nach der Anzahl der Sätze in einer Tabelle gefragt, für die in den Session-Statistiken für table scan rows gotten ein Wert von 329922 erschien und außerdem 645 table fetch continued row aufgetreten sind. Tatsächlich enthielt die Tabelle nur 10000 rows, aber für den Zugriff auf das zweite row piece werden im Fall von row chaining - und unabhängig davon, ob es Block-Grenzen überschreitet - deutlich höhere Werte für table scan rows gotten angegeben, als man erwarten würde. Wenn man das Beispiel mit wenigen Datensätzen nachvollzieht, dann ergibt sich zunächst ein Muster für die Berechnung der table scan rows gotten, das folgendermaßen aussieht:

table scan rows gotten
rows       piece1          piece2
----       ------          ------
   1            2               4
   2            4              12
   3            6              24
   4            8              40
   5           10              60
...
  10           20             220

Bis dahin sieht's für die Angabe für piece 2 nach folgender Formel aus:

rows * ((rows * 2) +2)

Allerdings ändert sich das Muster relativ bald, wenn man höhere Satzanzahlen erreicht. Ich hatte vor kurzem mal ein paar Beobachtungen zum row chaining notiert, aber da mein Beispiel nur einen einzigen Satz enthielt, war mir das seltsame Verhalten der table scan rows gotten-Angabe nicht aufgefallen.

Nachtrag 16.01.2013: In einem ziemlich ausführlichen Kommentar falsifiziert der Herr Lewis meine Hypothese, dass die Statistik table fetch continued row etwas über die Anzahl der block-übergreifenden chainings aussagt. Ich habe dann auch noch einen übersichtlichen Test mit einer Tabelle mit 300 Spalten und 100 rows durchgeführt, bei dem ich im block dump 10 block-überschreitende Verkettungen beobachtete, aber für table fetch continued row beim Zugriff auf eins Spalte des zweiten pieces den Wert 24 erhalte (während die Statistik für Zugriffe auf Spalten des ersten pieces immer 0 liefert). Anscheinend hat die Angabe weder zur Anzahl der betroffenen Blocks (im Test 5) noch zum block-übergreifenden chaining einen direkten Bezug. Mein Test unterstützte immerhin eine weitere Annahme, nämlich die, dass die table scan rows gotten für den Zugriff auf das erste piece der Gesamtzahl der vorhandenen pieces entspricht, denn bei 100 rows und 10 block-überschreitenden Verkettungen kam ich für die Statistik auf den Wert 210.