ODCIAggregate

Carsten Czarski zeigt in seinem Blog, wie man benutzerdefinierte Aggregatsfunktionen (auch als analytische Funktionen) erzeugen kann und liefert dazu das praktische Beispiel einer Aggregatsfunktion für die Bildung eines Produktes. Die Basis dafür sind die ODCIAggregate-Routinen, die anscheinend auch hinsichtlich ihrer Performance optimiert sind.

Pro Oracle SQL

Kaum jemand schreibt so fundierte Rezensionen zu Oracle-Fachbüchern wie Charles Hooper. Sehr lesenswert ist beispielsweise seine nicht unbedingt schmeichelhafte, aber in jedem Fall gut begründete Besprechung des Buchs Oracle Tuning: The Definitive Reference Second Edition, die mit der Überschrift "1,100 Page True-False Quiz, 'Definitive' is Left as an Exercise for the Student" einsetzt. Für mich sind Hoopers Einschätzungen völlig nachvollziehbar - wobei er in der Regel zu durchaus positiven Beurteilungen kommt. Das Buch Pro Oracle SQL habe ich kürzlich aufgrund einer positiven Hooperschen Besprechung erworben, und will hier jetzt ein paar Eindrücke festhalten. Erschienen ist der Band 2010 bei Apress - und das allein kann schon als erster Hinweis auf gehobene Qualität gesehen werden, denn mir ist kein wirklich schlechtes Oracle-Buch bekannt, das in diesem Verlag erschienen wäre. Auch die Liste der Autoren, die jeweils ein oder mehrere Kapitel beigetragen haben, ist vielversprechend: Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen und Jared Still - fast alles Blog-Autoren, deren Artikel ich schon seit längerer Zeit verfolge. Alle Autoren machen ausgiebig Gebrauch von praktischen Beispielen, wodurch die Aussagen überprüfbar werden und besser nachvollzogen werden können. Im einzelnen werden folgende Themen behandelt:

• 1 Core SQL (K. Morton): knappe Einführung in sqlplus und kurze Vorstellung der grundlegenden Syntaxelemente von SQL.
• 2 SQL Execution (K. Morton): Erläuterungen zur Architektur des Oracle Servers, der Verarbeitung von Queries und einiger interner Transformationen (Subquery Unnesting, Predicate Pushing etc.); die Erklärungen sind zutreffend, aber ich bin mir nicht ganz sicher, ob die unterschiedlichen Themen so recht zusammen in ein Kapitel passen.
• 3 Access and Join Methods (K. Morton): Darstellung von Zugriffs- und Join-Möglichkeiten. Ein klar strukturiertes Kapitel mit fundierten Erläuterungen.
• 4 SQL is About Sets (K. Morton): ein kurzes Plädoyer für satzorientiertes Denken (im Gegensatz zu prozeduralem) bei der Lösung von SQL-Fragestellungen. Ich denke, das gehört zu den Dingen, die auch Tom Kyte mit schöner Regelmäßigkeit erwähnt.
• 5 It's About the Question (K. Morton): noch ein Plädoyer, diesmal dafür, Fragen zu analysieren statt SQL-Queries. Wieder ein Gedanke, den auch Tom Kyte häufig formuliert. Mir passiert es leider immer noch gelegentlich, dass ich Queries erst optimiere, ehe ich erkenne, dass sie inhaltlich nutzlos sind.
• 6 SQL Execution Plans (K. Morton): eine solide Einführung, aber das entsprechende Kapitel in Christian Antoginis Buch Troubleshooting Oracle Performance ist besser, was sicher auch an den unterschiedlichen Zielsetzungen der Bücher liegt.
• 7 Advanced Grouping (J. Still): knappe Darstellung einfacher Gruppierungen und der neueren Erweiterungen des Sprachumfangs über CUBE, ROLLUP und die ergänzenden Hilfsfunktionen. Nicht schlecht, aber doch ziemlich knapp. Mir haben die entsprechen Beiträge Rob van Wijks noch etwas besser gefallen.
• 8 Analytic Functions (R. Shamsudeen): noch eine solide, aber auch ziemlich knappe Einführung - bei AskTom findet man Beispiele für so ziemlich jede denkbare Verwendung von Analytics. Am Ende des Kapitels findet sich die Aussage, dass ein serieller Prozess nicht mehr als 5% und ein parallelisierter Zugriff nicht mehr als 30% der PGA-Ressourcen nutzen kann (S. 250) - und das stimmt seit 10.2 nicht mehr, wie Joze Senegacnik gezeigt hat. Das ist allerdings so ungefähr der einzige sachliche Fehler, den ich im Buch gefunden habe.
• 9 The Model Clause (R. Shamsudeen): kurze Einführung der Model Clause, mit der ich immer noch meine Schwierigkeiten habe. Für mich ist auch in diesem Fall Rob van Wijks Vorgehen nachvollziehbarer, der die Model Clause an sehr einfachen Beispieldaten erläutert. Aber selbst die einfachen Beispiele haben mich bisher noch nicht dazu gebracht, die Model Clause selbst einmal praktisch einzusetzen. Vielleicht mangelt's mir aber auch nur an Begeistung und Verständnis für komplexere Algorithmen ... - dass man mit der Model Clause und solider mathematischer Ausbildung eindrucksvolle Dinge machen kann, hat Alberto Dell'Era gelegentlich gezeigt.
• 10 Subquery Factoring (J. Still): erläutert die Nutzung dieses Features, das außerhalb der Oracle-Welt oft als CTE (Common Table Expression) bezeichnet wird - und das syntaktisch als With-clause erscheint. Beschrieben werden die Hints MATERIALIZE und INLINE, über die beeinflusst werden kann, ob eine CTE über TEMP TABLE TRANSFORMATION als temporäres Objekt angelegt wird oder einfach in die rahmende Query integriert wird - und die Wirkungen dieser Varianten auf die Query-Performance. Außerdem wird erläutert, wie man durch rekursive CTEs hierarchische Abfragen erzeugen kann - was als Alternative zum CONNECT BY dienen kann.
• 11 Semi-joins and Anti-joins (K. Osborne): ein Kapitel, das mir sehr gut gefallen hat. Erläutert werden zunächst die Unterschiede zwischen (NOT) IN und (NOT) EXISTS. Noch interessanter fand ich die Erklärungen zu den Anti-joins, bei denen für mich neu war, dass ausgerechnet die von mir geschätzte MINUS-Operation nicht von anti-join optimizations profitiert.
• 12 Indexes (R. Shamsudeen): ein interessantes Kapitel, das nicht allzu sehr in die Tiefe geht, aber sehr viele relevante Punkte anspricht. Zu den Bitmap Indizes wird einmal mehr behauptet, dass sie nur für Spalten mit niedriger Cardinality geeignet seien, was Richard Foote gelegentlich widerlegt hat.
• 13 Beyond the Select (K. Osborne): knappe aber erhellende Darstellung zu INSERT, UPDATE, DELETE, MERGE, TRUNCATE etc.
• 14 Transaction Processing (R. Sands): Einführung in die Transaktionssteuerung relationaler Datenbanken (ACID, Isolation Levels) und Oracles spezifische Implementierung. Frau Sands verweist gleich zu Beginn auf Tom Kytes Äußerungen zum Thema, die da doch deutlich detaillierter sind.
• 15 Testing and Quality Assurance (R. Sands): ein relativ theoretisches Kapitel zur Bedeutung von Tests. Nicht schlecht, aber ziemlich allgemein.
• 16 Plan Stability and Control (K. Osborne): für mich eins der interessantesten Themen des Buchs. Ein wenig bedauere ich, dass Kerry Osborne die verwendeten Code-Stücke nicht im gedruckten Text untergebracht hat, kann mir aber vorstellen, dass das relativ viel Platz erfordert hätte. In jedem Fall eine sehr klare Einführung zu den Themen Outlines, Profiles und Baselines. Ich erinnere mich dunkel daran, dass Osborne und Jonathan Lewis vor einiger Zeit über die Rolle der Profiles diskutiert haben, wobei Lewis die Ansicht vertrat, dass sie grundsätzliche etwas Anderes seien als Outlines und Baselines und dem cbo vor allem bessere Statistikinformationen lieferten - was zumindest plausibel klingt.

Alle Kapitel sind grundsolide, viele gehen aber nicht allzu sehr in die Tiefe. Ich nehme an, dass das dem Konzept des Buchs entspricht, aber vielleicht wäre in diesem Zusammenhang eine kurze Erklärung der Zielsetzung und des angesprochenen Leserkreises nützlich gewesen - wie man sie in vielen anderen Apress Büchern findet. Laut Apress Roadmap (auf der Rückseite des Buchs) steht der Band zwischen dem einführenden Begining Oracle SQL und Christian Antogninis Buch und das passt zu meiner Wahrnehmung.

SQL Model Clause

Die SQL Model Clause ist wahrscheinlich das Element im Oracle-SQL, mit dem ich am wenigsten anfangen kann. Gelegentlich habe ich in diesem Blog schon mal auf ein paar Artikel verwiesen, in denen Rob van Wijk das Thema genauer beleuchtet - aber meine eigenen Erkenntnisse dazu sind immer noch sehr bescheiden. Daher hier mal ein kleines Beispiel, in dem ich etwas mit Model deutlich kompakter ausdrücken kann, als das mit anderen SQL-Varianten möglich wäre:

with
data
as
(
select empno
     , deptno
     , sal sal_org
     , sal
  from emp
)
select empno
     , deptno
     , sal_org
     , sal
  from data
 model
       partition by (deptno)
       dimension by (empno)
       measures (sal, sal_org)
       rules iterate(10)
       (sal[any] = sal[cv(empno)] - sal[cv(empno)]/10)

EMPNO     DEPTNO    SAL_ORG        SAL
----- ---------- ---------- ----------
 7499         30       1600 557,885504
 7521         30       1250  435,84805
 7654         30       1250  435,84805
 7698         30       2850 993,733554
 7844         30       1500  523,01766
 7900         30        950 331,244518
 7369         20        800 278,942752
 7566         20       2975 1037,31836
 7788         20       3000 1046,03532
 7876         20       1100 383,546284
 7902         20       3000 1046,03532
 7782         10       2450 854,262178
 7839         10       5000  1743,3922
 7934         10       1300 453,281972

Hier ziehe ich den emp-Mitarbeitern in 10 Iterationen jeweils 10% ihres Gehalts ab. Interessant am Beispiel sind in erster Linie die CV-Funktion (= CurrentValue) und das ITERATE-Kommando zur Definition der Schleifenanzahl. In diesem Fall ist das inhaltlich nicht besonders spannend, aber mehr fällt mir zu Model noch nicht ein. Interessanter wäre wahrscheinlich der Fall sukzessiver Steigerungen z.B. zu Ermittlung von Zinseszins-Effekten.

Statistik

Lies, damned lies and statistics... Ein paar instruktive Basisinformationen zu Datenverteilungseffekten findet man in Craig Shallahamers Blog.

Sort V1 und V2

Nachdem ich zuletzt mehrfach über allerlei Effekte im Zusammenhang von Sortierungen geschrieben habe, hier mal ein paar Überlegungen zum (inzwischen nicht mehr ganz so) neuen Sortierverfahren V2. Zum alten Mechanismus insertion sort (V1) hat Jonathan Lewis in seinem cbo-Buch und in seinem Blog allerlei geschrieben. Grundsätzlich wird bei diesem Verfahren intern ein B*Baum gefüllt:

The problem with the V1 sort is that the “sorting” mechanism works by building a balanced binary index as it reads the data. Although the implementation is made a little more complicated by the complexities of dynamic memory allocation the basic mechanism simply stacks your unsorted data at one end of the workarea memory while dynamically maintaining an index into that data at the other end of the workarea.

Über den V2-Mechanismus schreibt Jonathan Lewis auch einiges, aber das dahinter stehende Verfahren ist für mich weniger klar erkennbar. Auch Joze Senegacniks Präsentation zum PGA-Management enthält ein paar Hinweise, aber keine umfassende Erklärung des neuen Verfahrens - aber auch Jonathan Lewis Erklärungen zum V1-Verfahren basierten auf Tests und möglicherweise werden die internen Details von V2 auch durch Tests nicht ganz klar.

Mir genügt an dieser Stelle eine phänomenologische Betrachtung der Verfahren und ihrer Leistungsfähigkeit. Dazu folgender Test:

create table test_sort
as
select rownum rn
     , mod(rownum , 10) col1
     , lpad('*', 50, '*') col2
  from dual
connect by level < 1000000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test_sort')

create index test_sort_v2_idx on test_sort(col2, col1, rn);

Index wurde erstellt.

-- Abgelaufen: 00:00:08.50

drop index test_sort_v2_idx;

alter session set "_newsort_enabled"=false;

create index test_sort_v1_idx on test_sort(col2, col1, rn);

Index wurde erstellt.

-- Abgelaufen: 00:00:16.50

Mit V2 lief die Indexerzeugung demnach in der halben Zeit. Aber etwas mehr kann man dazu dann vermutlich doch noch sagen. Aus v$sesstat kann ich in diesem Fall nicht besonders viel ableiten:

NAME                                              V2        V1     Diff
-----------------------------------------------------------------------
file io wait time                             187373      9513  -177860
session logical reads                          25270     22605    -2665
sorts (memory)                                    57         5      -52
sorts (rows)                                 1000050   1000002      -48
sorts (disk)                                       0         1        1
workarea executions - onepass                      0         2        2
DB time                                          850      1651      801
physical reads direct temporary tablespace         0      9893     9893
physical writes direct temporary tablespace        0      9893     9893
physical read total bytes                     221184  81043456 80822272
physical write total bytes                  80805888 161849344 81043456

Die Event-Auswahl ist eher willkürlich, deutlich wird nur, dass die V1-Operation (bei sonst gleichen Session-Settings und in einer sonst ungenutzten Instanz) eine Sortierung auf die Platte verschieben muss. Deshalb noch ein 10032-Trace zum Thema:

alter session set events '10032 trace name context forever, level 1'; 

-- V2
---- Sort Statistics ------------------------------
Input records                             999999
Output records                            999999
Total number of comparisons performed     9841519
  Comparisons performed by in-memory sort 9841519
Total amount of memory used               91718656
Uses version 2 sort
---- End of Sort Statistics -----------------------

-- V1
---- Sort Statistics ------------------------------
Initial runs                              4
Number of merges                          1
Input records                             999999
Output records                            999999
Disk blocks 1st pass                      9893
Total disk blocks used                    9895
Total number of comparisons performed     19891137
  Comparisons performed by in-memory sort 18667731
  Comparisons performed during merge      1223406
Temp segments allocated                   1
Extents allocated                         78
Uses version 1 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  5
Block pins (for run directory)            1
Block repins (for run directory)          4
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   321
Num blocks written (with direct write)    9893
Block pins (for sort records)             9893
Cached block repins (for sort records)    3
Waits for async writes                    301
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               266
Number of blocks read synchronously       290
Number of direct async reads              1427
Number of blocks read asynchronously      9603
Waits for async reads                     314
---- End of Sort Statistics -----------------------

Die Statistiken für die V2-Sortierung vergleichsweise übersichtlich - möglicherweise wäre da ein höheres Trace-Level aussagekräftiger. Deutlich wird eigentlich nur, dass V2 deutlich weniger Vergleiche durchführt (10M statt 20M) und dass für V2 in diesem Fall keine onepass-Operation erforderlich ist. Aber in jedem Fall ist V2 offenbar sehr viel effektiver als V1 - und das genügt mir für den Moment...

Parallel Hints

Angeregt durch das Vorbild der Herren Lewis und Nanda beabsichtigt Randolf Geist, eine neue Serie mit kurzen Artikeln zu Grundlagenwissen zu schreiben. Das scheint mir aus mehreren Gründen eine gute Idee zu sein.

Im ersten Teil der Serie wird das unterschiedliche Verhalten von Parallel DML und Parallel Query hinsichtlich der Wirkung von ALTER SESSION-Kommandos und HINTs erläutert:

• If you haven't enabled Parallel DML on session level (ALTER SESSION ENABLE/FORCE PARALLEL DML) you are not able to force parallel DML execution on statement level using explicit PARALLEL hints for the DML operation. (This one is probably known)
• If you disable Parallel Query on session level (ALTER SESSION DISABLE PARALLEL QUERY) explicit PARALLEL hints on statement level for a query operation will overrule this and therefore still use Parallel Query. The session setting only overrules the object level parallel settings, not the PARALLEL hint (This is probably less known)

Interested Transaction Lists

Arup Nanda erläutert in seinem Blog die Rolle der Interested Transaction Lists (ITL), in denen in den Datenblöcken die Transaktionen verzeichnet sind, die Locks auf Rows dieser Blöcke halten. Interessant ist auch der Nachtrag zum gleichen Thema.

Automatisches PGA Management

In einem Thread im Oracle-Forum von XING hat Randolf Geist vor kurzem auf eine Präsentation von Joze Senegacnik hingewiesen, in der erläutert wird, wieviel Speicher in 10.2 bei automatischem Workarea-Management für einzelne Workarea-Operationen verwendet werden kann. Wichtig ist dabei vor allem, dass die in älteren Releases gültige Regel, dass eine (nicht parallelisierte) Operation nicht mehr als 5% der Gesamtgröße der PGA erreichen durfte, für 10.2 nicht mehr gilt.

Für 9i und 10.1 galt als Grenzwert für die Speicherzuweisung für eine einzelne SQL-Operation:

• serielle Operationen: min(5% PGA_AGGREGATE_TARGET, 100MB) (_smm_max_size hidden parameter)
• parallele Operationen: 30% PGA_AGGREGATE_TARGET / DOP (_smm_px_max_size)

Für10.2 hat Joze Senegacnik folgende Werte ermittelt: "In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) is limited to:

• for P_A_T <= 500MB the parameter _smm_max_size = 20% of P_A_T
• for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
• for P_A_T betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of P_A_T
• for P_A_T > 2560MB (2,5GB) the parameter _smm_max_size = 262,060 MB (~0,25GB)
• I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4GB.
• The maximum value for parallel operations changed from 30% to 50%"