Dienstag, Juni 28, 2011

Costing für FIRST_ROWS(n)

In Cost Based Oracle steht meiner Erinnerung nach nichts dazu, aber der Fall ist relativ einfach, so dass man dazu den Herrn Lewis wohl nicht benötigt [dafür aber den Herrn Geist - s. den Nachtrag vom 30.06.]:
(n/number_of_rows_in_table) * initial_cost
Hinweis: wie Randolf Geist in seinem Kommentar erwähnt - und in seiner unten verlinkten Präsentation näher erläutert - ist dieser einfache "proration factor" (= n / total_number_of_rows) nur ein Element innerhalb eines deutlich komplexeren Kalküls.

Dazu noch der Test, auf dem meine Annahmen basieren:


create table test_first_rows_hint
as
select rownum id
  from dual
connect by level <= 1000000;
 
exec dbms_stats.gather_table_stats(user, 'TEST_FIRST_ROWS_HINT', estimate_percent => 100)
 
select * from test_first_rows_hint;
 
1000000 Zeilen ausgewählt.
 
Abgelaufen: 00:00:03.96
 
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3818872010
 
------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |  1000K|  4882K|   780   (0)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TEST_FIRST_ROWS_HINT |  1000K|  4882K|   780   (0)| 00:00:04 |
------------------------------------------------------------------------------------------

In den folgenden Testläufen wurde die Query dann jeweils mit einem Hint versehen:
  • first_rows: 780 (wie im Fall ohne Hint)
  • first_rows(1): 2
  • first_rows(1000): 3
  • first_rows(10000): 9
  • first_rows(100000): 80 (ca. 10% der ursprünglichen Kosten bei Optimierung für 10% der Sätze)
  • first_rows(500000): 391 (ca. 50% der ursprünglichen Kosten bei Optimierung für 50% der Sätze)
Jonathan Lewis und Charles Hooper haben zum Thema übrigens noch ein paar interessante Punkte gesammelt, die über den Rahmen meiner Miniatur-Analyse hinaus gehen:
Nachtrag 30.06.2011: meine Annahme, dass das Costing für den FIRST_ROWS(n) Hint relativ simplen Regeln folgt, hat sich als deutlich zu optimistisch erwiesen, wie Randolf Geists Kommentar zu diesem Blog-Eintrag zeigt. Eine extrem detaillierte Beschreibung der Effekte der FIRST_ROWS%-Hints und ihrer sinnvollen Verwendung findet man in seiner Präsentation im Rahmen der UKOUG 2009: Everything You Wanted To Know About FIRST_ROWS_n But Were Afraid To Ask

Montag, Juni 27, 2011

Histogramme und implizite Typumwandlung

In Kyle Haileys Blog findet sich ein interessantes Beispiel für die unerfreulichen Folgen von impliziter Typumwandlung auf die Cardinalities und Costs einer Query im Zusammenhang mit Histogrammen.

Freitag, Juni 24, 2011

Troubleshooting Oracle Performance von Christian Antognini

Über Christian Antogninis Troubleshooting Oracle Performance wollte ich schon seit längerer Zeit etwas schreiben, bin aber nie dazu gekommen. Das war insofern unpassend, als das Buch aus meiner Sicht eines der besten überhaupt zum Thema "Performance Tuning in Oracle" ist. Der Herr Antognini ist seit vielen Jahren bei Trivadis tätig und er ist ein Mitglied der Tafelrunde der OakTable. Außerdem war er einer der Technical Reviewers für Jonathan Lewis' Cost Based Oracle, was allein schon ein starkes Indiz dafür ist, dass man wohl kaum jemanden finden wird, der in diesem Gebiet umfassendere Kentnisse besitzt. Troubleshooting Oracle Performance ist 2008 bei Apress erschienen, und bisher waren alle Oracle-Bücher von Apress, die ich gelesen habe, ausgesprochen lesenswert. Zu den Technical Reviewers des Bandes gehören neben Urs Meier (ebenfalls Trivadis) und Francesco Renne auch Alberto Dell'Era und Joze Senegacnik, von denen ich schon sehr viele gute Blog-Artikel und Präsentationen gesehen habe. Laut Apress-Homepage richtet sich das Buch an folgende Zielgruppe: "For application developers and database administrators involved in troubleshooting performance problems of Oracle-based applications" auf einem Level "Beginner to Advanced".

Troubleshooting Oracle Performance gliedert sich in fünf Abschnitte, die wiederum in Einzelkapitel unterteilt sind:
  1. Foundations: 
    • in Kapitel 1 wird erläutert, wie man mit Performance-Fragestellungen grundsätzlich umgehen sollte - also z.B., dass Performance schon bei der Requirement Analyse und beim Design berücksichtigt werden muss - und nicht erst nach dem Rollout; welche Rolle automatisierte Tests spielen; dass man von den Wahrnehmungen der Anwender (und den Einzelzugriffen) und nicht von System-Ratios ausgehen sollte etc.
    • Kapitel 2 führt diverse "Key Concepts" ein, die für die Performance relevant sind: Selectivity + Cardinality, Cursor, Parsing, Bind Variables. Besonders die Erläuterungen zu Cursors und Parsing fand ich sehr prägnant - hier helfen die auch an anderen Stellen verwendeten Flußdiagramme, um komplexe Zusammenhänge und Abläufe zu erklären.
  2. Identification:
    • Kapitel 3 trägt den Titel "Identifying Performance Problems" und erläutert recht ausführlich, auf welchen Ebenen Probleme auftreten können (Applikation, Netzwerk, Datenbank etc.) und wie man sie analysieren kann. Sehr umfangreich (S. 59 - 106) und erhellend sind dabei die Erläuterungen zum SQL_TRACE (10046) und zur Formatierung der Trace-Files über TKPROF und Antogninis eigenes TVD$XTAT-Tool (dessen einziges Manko der unhandliche Name ist ...).
  3. Query Optimizer:
    • Kapitel 4 "System and Object Statistics" finde ich besonders gelungen. Es beginnt mit den System Statistics (Noworkload, Workload), erläutert Object- und Columns-Statsitics und liefert eine sehr verständliche Erklärung zu Histogrammen (und Oracles Namensgebung in diesem Bereich). Darüber hinaus werden auch Spezialthemen wie Extended Statistics (zur Beschreibung von Korrelationen über mehrere Spalten) angesprochen. Es folgen umfangreiche Hinweise zum sinnvollen Einsatz von dbms_stats und zur Gestaltung einer effektiven Statistkerfassungsstrategie (ein Thema, das mir dieser Tage gerade besonders relevant erscheint).
    • Kapitel 5 "Configuring the Query Optimizer" beschäftigt sich mit diversen Instanz-Parametern, die das Verhalten des cbo beeinflussen (optimizer_feature_enabled, db_file_multiblock_read_count, optimizer_dynamic_sampling, optimizer_index_caching und optimizer_index_cost_adj etc.) und mit den unterschiedlichen Optionen zum PGA-Management.
  4. Optimization:
    • Kapitel 6 "Execution Plans" ist vielleicht das wichtigste Kapitel im Buch: ich habe noch keine bessere Einführung in die Interpretation von Zugriffsplänen gelesen. Zunächst werden diverse Hilfsmittel vorgestellt (v$-Views, dbms_xplan, 10053er Trace), dann folgt eine umfangreiche Darstellung "Interpreting Execution Plans" (S. 221-245), die sich mit den Operationstypen und ihrer Repräsentation in den Plänen beschäftigt. In späteren Kapiteln (9, 10) wird die Arbeitsweise der einzelnen Operation dann noch ausführlicher erläutert.
    • Kapitel 7 "SQL Tuning Techniques" beschäftigt sich mit den unterschiedlichen Möglichkeiten der SQL-Optimierung und ihren jeweiligen Vor- und Nachteilen: Änderung von physikalischen Strukturen (Indizes), Umformulierung von Queries, Verwendung von Hints, Änderungen des Execution Environment, Anlage von SQL Profiles, Outlines, Baselines. Besonders hilfreich sind die Abschnitte "When to use it" und "Pitfalls and Fallacies".
    • Kapitel 8 "Optimizations" beschäftigt sich sehr intensiv mit dem Parsing. Hier fand ich die Reihenfolge der Darstellung nicht ganz einleuchtend, weil die Code-Beispiele zum Verhalten und zur Verhaltensänderungen erst ganz am Schluss erscheinen. Allerdings ist Parsing auch nicht unbedingt mein Thema (da ich kein OLTP-System betreue).
    • Kapitel 9 "Optimizing Data Access" hat mir wieder sehr gut gefallen. Hier wird erläutert, wie man ineffektive Zugriffe bestimmen kann. Unter anderem erscheint die Daumenregel, dass:
      • 5 LIOs pro Row gut
      • 10-15 LIOs pro Row ok
      • mehr als 20 LIOs pro Row zu viel
      sind.Wie alle Daumenregeln ist das natürlich abhängig von den Rahmenbedingungen. Weitere wichtige Hinweise betreffen den Einfluß der Read Consistency (Block Rekonstruktion) und den Prefetching (Arraysize in sqlplus) auf die Anzahl der LIOs. Es folgen umfangreiche Erläuterungen zur Partitionierung, zur Indizierung (B*Tree, Bitmap), zu IOTs (Hinweis: IOT mit Overflow ist eigentlich sinnfrei) und zum Single-Table Hash Cluster (der für Lookup-Tables mit Key-Zugriff ideal ist).
    • Kapitel 10 "Optimizing Joins" ist ebenfalls hochinteressant und erläutert zunächst unterschiedliche Join Trees (Left-Deep, Reght-Deep, Zig-zag, Bushy Trees - was ich bisher noch nirgendwo in systematischer Darstellung gesehen hatte), die verschiedenen Join Typen (Cross, Theta, Equi, Self, Outer, Semi, Anti) und die verschiedenen Abarbeitungsstrategien: Nested Loops, Hash Join, Merge Join, die hier sehr anschaulich erklärt und umfassend dargestellt werden. Interessant fand ich den (meinen Beobachtungen entsprechenden) Hinweis, dass Merge Joins nicht häufig verwendet werden, das NL und Hash Join in der Regel effizienter sind - sie erscheinen üblicherweise nur dann, wenn die anderen Methoden nicht verwendet werden können (Hash Joins unterstützen keine Cross, Theta und Partitioned Outer Joins). Wichtig ist auch die Erläuterung dazu, wann die unterschiedlichen Methoden erste Ergebnisse liefern können:
      • NL: unmittelbar
      • Merge Join: erst nach Abschluss des Sortierens beider Input-Mengen
      • Hash Join: nach Abschluss des Aufbaus der Hash Table
      was durchaus einleuchtet, wenn man sich die innere Logik der Verfahren bewußt macht. Außerdem werden noch diverse interne Transformationen inklusive der Star Transformation erläutert (deren Logik ich hier sehr schön erklärt fand.
    • Kapitel 11 "Beyond Data Access and Join Optimization" erläutert (sehr detailliert) die Verwendung von Materialized Views und von Parallelisierung - also klassischen DWH/DSS-Features.
    • Kapitel 12 "Optimizing the Physical Design" beschäftigt sich mit der Wahl von Datentypen, mit Row Migration und Row Chaining, Contention und Compression.
    • Appendixes: mit einem Verzeichnis der download-baren Dateien und einer Literaturliste
    Wer dieses Buch als "Beginner" komplett liest und versteht, ist vermutlich kein "Beginner" mehr, sondern dann schon ziemlich "Advanced". Eine ähnlich fundierte und kompakte Darstellung zum Thema Oracle-Performance habe ich bisher noch nicht gesehen. Die einzigen kleineren Schwächen sehe ich in einigen Strukturentscheidungen, die dazu führen, dass einige Kapitel (vor allem am Anfang des Buches) nicht ganz homogen wirken. Außerdem werden manche Punkte an mehreren Stellen im Buch wiederholt, was aber durchaus auch als Vertiefung verstanden werden kann. Zu Beginn entschuldigt sich der Autor für sein Englisch, das tatsächlich an ein paar Stellen etwas holprig wirkt - aber ich bin der Letzte, der da aus seinem Glashaus heraus Steine werfen sollte. Insgesamt für mich ein großartiges Buch, das bei mir meistens irgendwo in der Nähe meines Arbeitsplatzes herumliegt.

      Mittwoch, Juni 22, 2011

      Row Migration

      Heute habe ich relativ lange über das Thema Row Migration nachgedacht. Ausgangspunkt war eine partitionierte Tabelle, die im April neu erzeugt worden war, und deren seither gefüllte Partitionen durchschnittlich deutlich weniger Sätze aufnehmen konnten, als die im Rahmen des initialen Ladens erstellten Partitionen.

      PARTITION_NAME       BLOCKS   NUM_ROWS COMPRESS ROWS_PER_BLOCK AVG_ROW_LEN PCT_FREE
      ---------------- ---------- ---------- -------- -------------- ----------- --------
      XXX_201101           233677   33187356 ENABLED             142          86       10
      XXX_201102           105034   15609036 ENABLED             149          87       10
      XXX_201103           139202   20494440 ENABLED             147          86       10
      XXX_201104           192264   19593779 ENABLED             102          87       10
      XXX_201105           254736   19182945 ENABLED              75          87       10
      XXX_201106           189427   14029238 ENABLED              74          87       10
      

      Da die Länge der Sätze sehr stabil (und Row Chaining sehr unwahrscheinlich) sein sollte, war meine Theorie, dass die häufigen UPDATEs in der Tabelle in Verbindung mit der Compression zu Row Migration führen könnten - da die Standard Compression (und für Updates anscheinend auch die OLTP Compression, wie Randolf Geist gelegentlich gezeigt hat) Sätze nach dem Update nicht wieder komprimiert, so dass sogar eine logische Verkleinerung eines Eintrags zu einer physikalischen Vergrößerung führt. Oder in der Zusammenfassung des Herrn Geist: "Mixing compression with a significant number of updates is a bad idea in general".

      Nachdem ich mir die Details zur Row Migration halbwegs vollständig zusammengesucht hatte, bin ich dann auf Tanel Poders Artikel Detect chained and migrated rows in Oracle – Part 1 gestoßen, der offenbar so ziemlich alle relevanten Informationen enthält. Mit dem Befehl ANALYZE ... LIST CHAINED ROWS konnte ich für einige Partition bestimmen, dass tatsächlich ca. 1-2% der Sätze chained (bzw. in diesem Fall eher migrated) sind. Ob das ein echtes Problem ist, ist mir dabei noch nicht ganz klar - aber günstig ist es bestimmt nicht.

      Dienstag, Juni 21, 2011

      Fixed Object Statistics

      Mike Dietrich erläutert, dass fixed object stats die Laufzeit von Recompilations nach einem Upgrade senken können.

      Fixed Table Stats erzeugt man übrigens mittels
      exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
      
      Ihre Existenz und die Kardinalitäts-Angaben kann man mit folgender Query für eine gegebene Tabelle überprüfen:

      select v.name
           , ts.analyzetime
           , ts.rowcnt
        from v$fixed_table v
           , sys.tab_stats$ ts
       where v.object_id = ts.obj#
         and v.name = '&table_name';
      

      Sonntag, Juni 19, 2011

      Hash Partitioning

      Ich habe mich schon häufiger gefragt, wozu HASH Partitioning eigentlich gut sein könnte, war aber immer zu träge, der Frage im Detail nachzugehen. Das soll nun korrigiert werden. Dazu zunächst die Definition des Concept Guides (11.2):
      In hash partitioning, the database maps rows to partitions based on a hashing algorithm that the database applies to the user-specified partitioning key. The destination of a row is determined by the internal hash function applied to the row by the database. The hashing algorithm is designed to evenly distributes rows across devices so that each partition contains about the same number of rows.

      Hash partitioning is useful for dividing large tables to increase manageability. Instead of one large table to manage, you have several smaller pieces. The loss of a single hash partition does not affect the remaining partitions and can be recovered independently. Hash partitioning is also useful in OLTP systems with high update contention. For example, a segment is divided into several pieces, each of which is updated, instead of a single segment that experiences contention.
      Demnach gilt:
      • die Zuordnung von Sätzen zu Partitionen wird über eine Hash Funktion gesteuert (was jetzt keine große Überraschung ist), die auf den Partition Key angewandt wird
      • Hash Partitioning dient zum Aufteilen einer großen Datenmenge auf mehrere Partitionen, die besser administrierbar sind (und die Gefahr von Contention vermindern)
      Der VLDB Guide (wieder 11.2) liefert noch den ergänzenden Hinweis: "Hash partitioning is also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not historical or has no obvious partitioning key." Außerdem erfährt man dort: "You cannot change the hashing algorithms used by partitioning." Hash Partitioning ist also offenbar vor allem (bzw. fast ausschließlich) dann interessant, wenn der Partition Key keine vernünftige Range- oder List-Partitionierung gestattet. Das macht das Verfahren für die meisten praktischen Fälle, mit denen ich bisher konfrontiert wurde, ziemlich uninteressant - fast immer gibt der Partition Key eine sinnvollere Verteilungsstrategie her. Darüber hinaus habe ich bei Tom Kyte in Expert Oracle Database Architecture gerade noch mal den Hinweis gelesen, dass man für die Anzahl von Hash Partitionen immer eine "power of 2" verwenden sollte, da sonst die Gleichverteilung auf der Strecke bleiben kann. Nun stammt meine Ausgabe des Buchs aus dem Jahr 2005 und behandelt die Versionen 9 und 10, weshalb ich mal kurz überprüfen will, ob diese Aussage auch für 11.2 noch gültig ist. Und, um der Trägheit noch stärker entgegen zu wirken, verwende ich nicht das elegante Testverfahren des Herrn Kyte, sondern extemporiere etwas weitaus Simpleres (nämlich ein Script, das mir eine Anaylsequery generiert):

      -- test_hash_partitioning.sql
      drop table test_hash_partitioned;
      
      create table test_hash_partitioned
      partition by hash(id)
      partitions &&partition_count
      as
      select rownum id
        from dual
      connect by level <= 32000;
      
      select 'select ''' || partition_name || ''' partition_name,  count(*) row_count from '
             || table_name || ' partition (' || partition_name || ')' part1
           , case when partition_position < &partition_count then ' union all ' else ';' end part2
        from user_tab_partitions
       where table_name = 'TEST_HASH_PARTITIONED';
      
      Das Ergebnis der generierten Hilfsquery lautet für den Fall mit 4 Partitionen:
      PARTITIO  ROW_COUNT
      -------- ----------
      SYS_P481       7893
      SYS_P482       7916
      SYS_P483       8246
      SYS_P484       7945
      
      Das kann als Gleichverteilung durchgehen. Fall 2: 5 Partitionen:
      PARTITIO  ROW_COUNT
      -------- ----------
      SYS_P485       3981
      SYS_P486       7916
      SYS_P487       8246
      SYS_P488       7945
      SYS_P489       3912
      
      Das wiederum ist nicht gleichverteilt, aber entspricht sehr genau dem Ergebnis beim Herrn Kyte. Hier noch die Werte für die Fälle mit 6, 7 und 8 Partitionen:
      -- 6 Partitionen
      PARTITIO  ROW_COUNT
      -------- ----------
      SYS_P510       3981
      SYS_P511       3988
      SYS_P512       8246
      SYS_P513       7945
      SYS_P514       3912
      SYS_P515       3928
      
      -- 7 Partitionen
      PARTITIO  ROW_COUNT
      -------- ----------
      SYS_P516       3981
      SYS_P517       3988
      SYS_P518       4112
      SYS_P519       7945
      SYS_P520       3912
      SYS_P521       3928
      SYS_P522       4134
      
      -- 8 Partitionen
      PARTITIO  ROW_COUNT
      -------- ----------
      SYS_P523       3981
      SYS_P524       3988
      SYS_P525       4112
      SYS_P526       3954
      SYS_P527       3912
      SYS_P528       3928
      SYS_P529       4134
      SYS_P530       3991
      

      Fall 8 ist dann wieder gleichverteilt, was zeigt, dass Tom Kytes Aussagen in diesem Punkt auf für 11.2.0.1 unverändert gelten.

      Donnerstag, Juni 16, 2011

      Graph-Abbildung in SQL

      Dieser Tage wurde ich danach gefragt, wie man die transitive Zusammengehörigkeit von Gruppen in SQL abbilden kann - also mehr oder minder die grundlegende Fragestellung der sozialen Netzwerke. Statt darüber nachzudenken, habe ich die Suchmaschine meines Vertrauens befragt, und von dieser einen Link erhalten. Die Lösung sind hierarschiche Queries mit CTEs oder CONNECT BY.

      Mittwoch, Juni 15, 2011

      FTS Kosten mit Flashback Query

      Randolf Geist zeigt in seinem Blog, das die Kosten für FTS-Zugriffe im Zusammenhang mit Flashback Query unerwartet hoch sind: sie entsprechen ziemlich genau der Anzahl der Einzelblocks (es wird also kein Multiblock-Read-Faktor berücksichtigt).

      Samstag, Juni 11, 2011

      Freitag, Juni 10, 2011

      10046er Trace-Files parsen

      Kyle Hailey stellt ein (von Clive Bostock erstelltes) Perl-Script vor, mit dessen Hilfe man 10046er Trace-Files parsen und daraus zusammenfassende Summaries und Histogramme erzeugen kann.

      DBMS_STATS und Cursor Invalidation

      Gestern hatte ich sowohl im SQL Server als auch in Oracle mit dem Effekt zu tun, dass Zugriffspläne mit extrem ungünstigen NL-Operationen erzeugt wurden, weil die Column-Statistiken behaupteten, dass ein Wert, nach dem eine Tabelle gefiltert werden sollte, in der entsprechenden Tabelle nicht vorkäme. Im Oracle-Fall ergab die Analyse folgende Zusammenhänge:
      • es existierte eine Query mit extremer Laufzeit
      • im Execution Plan finden sich sehr viele NESTED LOOPS, obwohl Massendaten verarbeitet werden.
      • Ausgangspunkt für die NLs ist offenbar die Kardianlität einer Menge, die auf 1 geschätzt wurde, obwohl tatsächlich 1.500.000 Sätze vorlagen. Da für einen NL jeder Ergebnissatz in der äußeren Tabelle einen Zugriff auf die innere Tabelle hervorruft, war das natürlich eine ziemlich gravierende Fehlannahme
      • das Filterkriterium für die auf 1 geschätzte Tabelle war TYP = 61
      • in USER_TABLES waren als NUM_ROWS für die fragliche Tabelle ca. 2.000.000 angegeben, tatsächlich enthielt die Tabelle aber über 15.000.000 Sätze
      • in USER_TAB_COLS wurde für die TYP-Spalte unter NUM_DISTINCT eine 1 angegeben - und der LOW_VALUE (= HIGH_VALUE) war 67; tatsächlich gab es aber 9 Typen in der Tabelle und jeder davon erschien über 1.500.000 mal
      • ich erinnere mich, dass in Cost Based Oracle erläutert wird, wie die Cardinality-Schätzungen jenseits der LOW- und HIGH_VALUE Grenzen langsam absinken. Wenn es aber nur einen distinkten Wert gibt, dann sackt die Schätzung extrem schnell ab:
      create table test_1_value
      as
      select 42 col1
        from dual
      connect by level <= 1000000;
      
      exec dbms_stats.gather_table_stats(user, 'TEST_1_VALUE')
      
      select *
        from TEST_1_VALUE
       where COL1 = 42;
      
      --------------------------------------------------
      | Id  | Operation         | Name         | Rows  |
      --------------------------------------------------
      |   0 | SELECT STATEMENT  |              |  1000K|
      |*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |  1000K|
      --------------------------------------------------
      
      select *
        from TEST_1_VALUE
       where COL1 = 43;
      
      --------------------------------------------------
      | Id  | Operation         | Name         | Rows  |
      --------------------------------------------------
      |   0 | SELECT STATEMENT  |              |     1 |
      |*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |     1 |
      --------------------------------------------------
      
      select *
        from TEST_1_VALUE
       where COL1 = 41;
      
      --------------------------------------------------
      | Id  | Operation         | Name         | Rows  |
      --------------------------------------------------
      |   0 | SELECT STATEMENT  |              |     1 |
      |*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |     1 |
      --------------------------------------------------
      

      Demnach können in einem solchen Fall, in dem vermutlich die Statistiken nach einer initialen Füllung nicht mehr aktualisiert werden, extreme Fehleinschätzungen auftreten.

      Aber das alles passt noch nicht zum Titel dieses Blog-Eintrags. Nach der Aktualisierung der Statistiken wurde die Query vom OWB erneut ausgeführt - und ich stellte mit Schrecken fest, dass die Aktualisierung zwar meine Testquery auf einen sinnvollen Zugriff geführt hatte, die OWB-Query aber immer noch den absurden NL-Plan verwendete. Verantwortlich dafür war offenbar mein DBMS_STATS-Aufruf, der mit den default-Einstellungen arbeitete - so dass der Parameter no_invalidate auf den Standard-Wert gesetzt blieb. Diesen Parameter erläutert die Doku folgendermaßen:
      Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
      Wenn man diesen Angaben weiter folgt, stößt man irgendwann auf eine Erläuterung der Rolling Cursor Invalidations with DBMS_STATS in Oracle10g [ID 557661.1], die darauf hinauslaufen, dass ein Cursor nicht direkt invalidiert wird, sondern erst mit einer Verzögerung von 5 Stunden (18000 sec.). In einem OLTP-System mag das auch ganz sinnvoll sein, aber in einem DWH passt es leider gar nicht. Weitere Details zum Verhalten findet man auch hier und hier.

      Mittwoch, Juni 08, 2011

      Hints richtig setzen

      In schöner Koinzidenz zu meinem harmlosen (und unvorteilhaft benannten) View-Hint-Eintrag hat Jonathan Lewis einen Artikel über das korrekte Setzen von Hints und die Verwendung von benamten Query-Blocks geschrieben.

      Hints für eine Query auf eine View

      Im OTN Forum wurde heute die beliebte Frage gestellt, warum der cbo für eine Query keinen Index-Zugriff wählt - und das trotz eines Index-Hints. Wie Jonathan Lewis regelmäßig erläutert, sind Hints ja eigentlich keine Hints (soll heissen: Hinweise), sondern klare Direktiven: der cbo muss sich an sie halten, sofern er nach Abschluss seiner internen Optimierungs-Umformungen einen Zugriff auswählt, in dem sie berücksichtigt werden können. Im gegebenen Fall wurde der Hint allerdings ignoriert, weil er sich auf eine Tabelle bezog, die Query selbst aber eine darauf aufbauende View ansprach. Dazu ein Beispiel (das ich in ähnlicher Form auch im Thread untergebracht hatte):
      create table test
      as
      select * from dba_objects;
      
      create index test_idx on test(OWNER);
      
      -- beim Zugriff auf die Basistabelle wird der Hint berücksichtigt
      select /*+ index (test test_idx) */ * from test where owner like '%SYS%';
       
      ------------------------------------------------
      | Id  | Operation                   | Name     |
      ------------------------------------------------
      |   0 | SELECT STATEMENT            |          |
      |   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
      |*  2 |   INDEX FULL SCAN           | TEST_IDX |
      ------------------------------------------------
       
      create view v_test as select * from test;
      
      -- der Hint spricht die Basistabelle an - und wird ignoriert, weil
      -- hier die View abgefragt wird
      select /*+ index (test test_idx) */ * from v_test where owner like '%SYS%';
       
      ----------------------------------
      | Id  | Operation         | Name |
      ----------------------------------
      |   0 | SELECT STATEMENT  |      |
      |*  1 |  TABLE ACCESS FULL| TEST |
      ----------------------------------
      
      -- wenn der Hint die View anspricht, wird er berücksichtigt
      select /*+ index (v_test test_idx) */ * from v_test where owner like '%SYS%';
      
      ------------------------------------------------
      | Id  | Operation                   | Name     |
      ------------------------------------------------
      |   0 | SELECT STATEMENT            |          |
      |   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
      |*  2 |   INDEX FULL SCAN           | TEST_IDX |
      ------------------------------------------------
      
      Dass der Index-Zugriff im Test-Beispiel (ähnlich wie im OTN-Thread) wahrscheinlich eher weniger sinnvoll ist, steht auf einem anderen Blatt.

      Freitag, Juni 03, 2011

      Serverparametrisierung MSMDSRV.ini

      Bei Durchsicht des Analysis Services Operations Guide (Abschnitt 2.3.2, S. 14 f.) ist mir klar geworden, dass mir die Memory-Parameter in msmdsrv.ini regelmäßig entfallen. Daher an dieser Stelle ein paar kurze Definitionen:
      • LowMemoryLimit: der Arbeitsspeicher (in % oder Byte), den der SSAS nicht wieder freigibt, nachdem er ihn einmal bekommen hat. Dieser Speicher wird per default nicht beim Systemstart allokiert.
        • Counter:MSOLAP:Memory\LowMemoryLimit
        • PreAllocate: sorgt dafür, dass schon beim Systemstart Speicher für den SSAS allokiert wird (was dann interessant sein kann, wenn der SSAS sich einen Server mit anderen Applikationen teilen muss). Für Windows Server 2008 ist diese Einstellung nicht mehr besonders relevant.
      • TotalMemoryLimit: der Schwellwert (Arbeitsspeicher in % oder Byte), ab dessen Überschreitung der SSAS damit beginnt, Speicher wieder freizugeben. Die Angabe ist keine Obergrenze für den Speicherverbrauch des Prozesses.
        • Counter: MSOLAP:Memory\TotalMemoryLimit
      • HardMemoryLimit (verfügbar ab SSAS 2008): eine aggressivere Variante zu TotalMemoryLimit, die Sessions beendet, wenn der Schwellwert überschritten wird.
      • LimitSystemFileCacheMB: beschränkt die Größe des OS-Caches, damit dieser nicht mit dem SSAS-Prozess kollidiert.
      • OLAP\Process\AggregationMemoryLimitMin (bzw. %Max): bestimmt die Größe des zum Aufbau von Indizes und Aggregationen verwendeten Arbeitsspeichers. Der Min-Wert gilt pro Partition: "if you start five concurrent partition processing jobs with AggregationMemoryMin = 10, an estimated 50 percent (5 x 10%) of reserved memory is allocated for processing". Deshalb kann es in großen Systemen, die über sehr viel Arbeitsspeicher verfügen, sinnvoll sein, den Min-Wert zu reduzieren (weniger % oder sogar eine fixe Byte-Angabe), um eine höhere Parallelisierung zu ermöglichen.
      Möglicherweise werde ich diese Liste gelegentlich erweitern.

      Analysis Services Operations Guide

      Ich beschwere mich gerne und regelmäßig darüber, dass es in der SSAS-Welt nur sehr wenige brauchbare Informationsquellen zu komplexeren Fragestellungen gibt. Neben dem Performance Guide gab es in diesem Bereich lange Zeit recht wenig. Anscheinend hat sich die Lage nun aber deutlich verbessert, da der Analysis Services 2008R2 Operations Guide veröffentlicht wurde, dessen Liste der Contributors and Technical Reviewers so ziemlich jeden enthält, der in Sachen SSAS Rang und Namen hat.

      Donnerstag, Juni 02, 2011

      ANALYZE und DBMS_STATS

      Um mich endlich mal dauerhaft daran zu erinnern, was die Unterschiede in der Statistikerhebung über ANALYZE TABLE ... COMPUTE STATISTICS und DBMS_STATS.GATHER_TABLE_STATS sind, hier mal wieder ein Test. Zunächst der Hinweis der Oracle-Doku (hier 11.1):
      Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS.
      Das könnte man natürlich auch gleich als Hinweis darauf deuten, dass ich mir diesen Test schenken kann, aber jetzt habe ich damit angefangen und werde es auch zu einem Ende bringen...

      -- Anlage zweier einfacher und identischer Tabellen
      create table test_analyze
      as
      select rownum id
           , mod(rownum, 10) col1
           , lpad('*', 20, '*') pad
        from dual
      connect by level <= 1000;
      
      create table test_dbms_stats
      as
      select rownum id
           , mod(rownum, 10) col1
           , lpad('*', 20, '*') pad
        from dual
      connect by level <= 1000;
      
      analyze table test_analyze compute statistics;
      -- Tabelle wurde analysiert.
      -- Abgelaufen: 00:00:00.18 
      
      exec dbms_stats.gather_table_stats(user, 'TEST_DBMS_STATS')
      -- PL/SQL-Prozedur erfolgreich abgeschlossen.
      -- Abgelaufen: 00:00:02.04 
      
      select table_name
           , num_rows
           , blocks
           , avg_space
           , avg_row_len
           , avg_cached_blocks
           , avg_cache_hit_ratio
           , last_analyzed
           , global_stats
        from user_tab_statistics
       where table_name in ('TEST_DBMS_STATS', 'TEST_ANALYZE');
      
      TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO LAST_ANA GLO
      ---------------- ---------- ---------- ---------- ----------- ----------------- ------------------- -------- ---
      TEST_ANALYZE           1000          8       1488          31                                       02.06.11 NO
      TEST_DBMS_STATS        1000          8          0          28                                       02.06.11 YES
      
      select table_name
           , column_name
           , global_stats
           , avg_col_len
        from user_tab_cols
       where table_name in ('TEST_DBMS_STATS', 'TEST_ANALYZE')
       order by column_name, table_name
      
      TABLE_NAME                     COLUMN_NAME                    GLO AVG_COL_LEN
      ------------------------------ ------------------------------ --- -----------
      TEST_ANALYZE                   COL1                           NO            2
      TEST_DBMS_STATS                COL1                           YES           3
      TEST_ANALYZE                   ID                             NO            3
      TEST_DBMS_STATS                ID                             YES           4
      TEST_ANALYZE                   PAD                            NO           20
      TEST_DBMS_STATS                PAD                            YES          21
      

      Demnach liegen die Unterschiede darin, dass:
      • ANALYZE die AVG_SPACE-Angabe liefert
      • beide Versionen unterschiedliche GLOBAL_STATS-Werte enthalten
      • die AVG_ROW_LEN bei DBMS_STATS immer um 1 größer ist als bei ANALYZE
      • ANALYZE schneller ein Ergebnis liefert
      Nach diesem relativ harmlosen Test habe ich dann auch noch bei Google nachgefragt und zunächst bei AskTom einen deutlich detaillierteren Test zum gleichen Thema gefunden, der auch noch zeigt, dass ANALYZE neben der AVG_SPACE-Angabe auch noch EMPTY_BLOCKS und CHAIN_CNT füllt: "dbms_stats won't gather things not used by the CBO such as chain_cnt/avg_space in particular". Außerdem weist Tom Kyte auf den unterschiedlichen Umgang mit partitionierten Tabellen hin:
      ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.  DBMS_Stats won't do that.
      Darüber hinaus liefert Jonathan Lewis eine einleuchtende Erklärung dafür, warum DBMS_STATS mehr Zeit benötigt: ANALYZE tut einfach deutlich weniger, während DBMS_STATS mit jedem neuen Release weitere Aufgaben bekommt. In Cost Based Oracle (Cap 12, S. 322f.) erklärt der Herr Lewis auch noch den Unterschied der Länge in AVG_ROW_LEN: DBMS_STATS enthält ein (oder mehrere) zusätzliche Length Byte(s).
      Interessant ist auch noch die Sammlung von Einzelbeobachtungen, die man auf Yong Huangs unscheinbarer aber durchaus lesenswerter Web-Seite findet. Demnach gilt:
      • die MOS-Dokumente 237293.1 und 237537.1 zeigen die Probleme von ANALYZE bei der Statistikerfassung für partitionierte Tabellen
      • ANALYZE benötigt weniger Platz für Sortierungen im temporären Tablespace (was vermutlich zu Jonathan Lewis Aussage passt, dass DBMS_STATS mehr zu tun hat)
      • die LAST_ANALYZED-Angabe passt für ANALYZE zum Startzeitpunkt der Analyse, während sie für DBMS_STATS den Endzeitpunkt liefert (das könnte ich gelegentlich noch mal prüfen)
      • beide Kommandos führen ein implizites COMMIT durch. Dieser Punkt war mir dieser Tage auch schon einmal schmerzhaft bewusst geworden, als mein Versuch, die Statistikerfassung einer ETL-Operation zu verbessern, die vorhandene Transaktionslogik torpedierte (was mir immerhin vor Produktivsetzung klar wurde). Einleuchtend ist das Verhalten natürlich schon, da Statistiken ja nicht Privatsache einer Session sind.
      P.S.: nach der Bedeutung der GLOBAL_STATS-Spalte in USER_TABLES (oder inzwischen USER_TAB_STATISTICS) hatte ich vor mehreren Jahren im C.D.O.-Forum gefragt und darauf eine Antwort von Christian Antognini bekommen, die mir jetzt auch deutlich klarer ist als damals...