Angemessene Indexgröße

Für All Things Oracle hat Jonathan Lewis vor einigen Wochen einen Artikel geschrieben, in dem er erläutert, wie man die angemessene Größe eines B*Tree Index bestimmen kann. Darin geht er davon aus, dass ein stabiler B*Tree Index in der Regel etwa 70% des verfügbaren Speichers in den Leaf-Blocks auslastet, während etwa 30% freier Speicher in den Leaf-Blocks verfügbar bleibt. Unter dieser Voraussetzung ist es möglich eine grobe Abschätzung zu geben, wie groß ein Index in etwa sein sollte - und daraus kann man dann ableiten, ob unter Umständen einer der seltenen Fälle vorliegt, in denen ein Index-Rebuild tatsächlich einmal eine gute Idee sein könnte. Seine Formel zur Berechnung der angemessenen Indexgröße enthält folgende Elemente:

• sum(user_tab_cols.avg_col_len + 1) für alle Index-Spalten
  
• 6 byte für die rowid (8 byte im Fall eines globally partitioned index)
• 1 byte, wenn der Index non-unique ist
• 2 byte pro row
• 2 byte pro row directory Eintrag

Der im Leaf-Block verfügbare Platz wird berechnet als: Blöckgröße - 200 Byte (für das block directory) und muss den pctfree-Wert berücksichtigen. Damit kann man dann berechnen, wie viele Index-Einträge in einen Leaf-Block passen und wie groß der Index insgesamt sein muss. Die Formel sollte für nicht komprimierte Indizes nicht mehr als 1 oder 2% von der tatsächlichen Index-Größe nach einem rebuild abweichen - und auf seiner Webseite hat der Herr Lewis auch ein entsprechendes Skript bereitgestellt.

Ein klassischer Fall, in dem ein Index von einem rebuild profitieren kann, ist ein fifo (first in first out) Processing, bei der einzelne Einträge stehen bleiben - und da ein Index-Block erst wieder verfügbar wird, wenn alle Einträge daraus gelöscht wurden, machen solche Überbleibsel Probleme. Solche Fälle kann man recht komfortabel mit dem dbms_space-Package analysieren und auch zu diesem Zweck gibt es ein Skript beim Herrn Lewis.

Ein weiteres Hilfsmittel zur Analyse von Indizes ist der Treedump den man über ein trace event erstellen lassen kann. Das Vorgehen dabei und einige grundlegende Analysetechniken (etwa die Verwendung von grep) werden im Artikel erläutert, aber in diesem Fall spare ich mir die detaillierte Nacherzählung. Festzuhalten bleibt, dass der Treedump eine genauere Analyse der Index-Struktur erlaubt und es möglich macht, ungewöhnliche Fälle exakter zu bestimmen.

OTN Appreciation Day: Instrumentation

And now to something completely different: an entry in english. Some days ago Tim Hall suggested the introduction of an "OTN Appreciation Day" (did I miss some important letters?):

So taking that as the basis, and considering OTN is all about community, I figured it would be fun if we got as many people as possible to write a small blog post about their favourite Oracle feature and we all post them on the same day.

Today my feedly reader is flooded with articles of the naming pattern 'OTN Appreciation Day%' and so I came to the conclusion that adding some lines here should not hurt.

What is my favorite Oracle feature? There are many great things I could mention, but since I have worked frequently with other RDBMS it's quite easy to name the element which makes the main difference to me: Oracle's superior instrumentation. To make it clear: there are many very good relational database management systems available and they all have their strengths. And I would not at all claim that Oracle's database is better than any competitor in every given area (- since this would be complete nonsense...); but there is one thing that Oracle got right long before the competition: the instrumentation of database operations, the awesome wait interface and the large number of instructive trace events (Ok, maybe two things; maybe three things - and of course "fear, surprise, and ruthless efficiency...and an almost fanatical devotion to the Pope"). In Oracle there is no need to guess if switching parameter X could make something more efficient, no need to claim that in "real world systems it is always a good idea to...". Oracle's instrumentation makes it simple to measure the effects: we can build different test cases and check what differences the internal statistics show. To me this instrumentation made the difference between Oracle and many other RDBMS for many years. Nowadays the difference is not as big as it used to be, since other vendors started to add good instrumentation to their code too - but as far as I can see Oracle's advantage is still there. And in my opinion the instrumentation of RDBMS is also a big advantage in comparison to all the new shiny NoSQL solutions out there: without doubt they have their areas - but I guess they do not have the kind of instrumentation our good old legacy databases can provide.

That's it: now I promise to stop my mistreatment of the english language...

Wissenswertes über Pivot und Unpivot

Eine schöne und ausführliche Darstellung dessen, was man mit den Funktionen PIVOT und UNPIVOT anstellen kann, liefert Chris Saxon im AllThingsSQL Blog. Ich neige immer noch dazu, diese Transformationen manuell zu erzeugen, aber irgendwann werde ich mich an die Funktionen gewöhnt haben - hoffe ich ...

500 Byte Limit für Hints in SQL Profiles und SQL Patches

Mohamed Houri weist in einem aktuellen Artikel darauf hin, dass (Outline) Hints mit einer Länge von mehr als 500 Bytes zu Problemen bei der Erzeugung von SQL Profiles führen können, da der zu ihrer Erzeugung üblicherweise verwendete Typ sys.sqlprof_attr als "varray(2000) of varchar2(500)" definiert ist. Glücklicherweise akzeptiert die Prozedur dbms_sqltune.import_sql_profile statt sqlprof_attr Angaben auch CLOBs, so dass die Beschränkung an dieser Stelle umgangen werden kann. In einem Kommentar zum Artikel weist Jonathan Lewis darauf hin, dass die gleiche Einschränkung auch für SQL Patches gilt - allerdings in diesem Fall, ohne dass ein effektiver Workaround existierte.