Ausgehend von den hier beschriebenen Beobachtungen habe ich jetzt ein wenig genauer auf die Inhalte meiner Trace-Dateien geschaut - und dabei erst einmal auf das 10046er Trace, das für mich weniger exotisch ist als das 10032er Trace für die Sortierung.
Event 10046
Im 10046er Trace für den Fall mit der auf 50 MB dimensionierten SORT- bzw. HASH_AREA_SIZE sehe ich zunächst 166 db file scattered read Events, die in der Regel 64 Blocks umfassen (bei 10240 Blocks in der Tabelle ergibt sich: 10240/64 = 160) - die Laufzeit dieser Events beträgt ca. 4 sec. Anschließend folgen dann mehr als 2.000 direct path read temp Events mit einer Laufzeit von insgesamt etwa 40 sec. Für diese Events ist der block cnt jeweils 15.
Für den Fall mit 300MB liefert das Trace für die db file scattered read Events nahezu die gleichen Werte. Für die direct path read temp Events aber vergrößert sich die Anzahl von etwa 2.000 auf über 200.000 und die Laufzeit steigt von 40 sec auf mehr als 10 min. Überraschend ist dabei auch, dass der block cnt in diesen Fällen jeweils 1 ist, was auf jeden Fall eine Erhöhung der Anzahl der Events hervorrufen muss, allerdings nicht unbedingt um den Faktor 100. Interessant ist aber natürlich vor allem die Frage, wieso die Zugriffe auf Einzelblocks erfolgen.
Event 10032
Jonathan Lewis verwendet dieses Event gelegentlich und weist auch in einem OTN-Thread mit dem vielversprechenden Titel Single block read for Sort darauf hin, aber in meinem Fall bringt es nicht viel, da seine Aktivierung offenbar dazu führt, dass der block cnt für den 50MB-Fall auf 1 sinkt und die Laufzeit und das Verhalten dem 300MB-Fall entsprechen. Im Trace erscheint die Angabe sort_multiblock_read_count = 1, aber ein explizites Setzen des Parameters _sort_multiblock_read_count auf einen anderen Wert ändert das Verhalten nicht. (Bei Steve Adams findet man eine uralte Erläuterung (Dezember 2000; das Internet hat ein erstaunliches Gedächtnis) zum sort_multiblock_read_count, die vermutlich sogar noch zutreffend ist, aber im aktuellen Fall auch nicht weiter hilft; im angesprochenen OTN-Thread erklärt Timur Akhmadeev, dass das Setzen des hidden parameters in seinen Tests ebenfalls keinen Effekt hatte; auch die im Thread genannte Option, die Parameter _smm_auto_max_io_size und _smm_auto_min_io_size zu setzen, die im MetaLink Dokument 330818.1 beschrieben wird, brachte keine Änderung des Verhaltens, wobei das insofern nicht überrascht, als diese Parameter wohl nur den Fall der automatischen PGA-Verwaltung betreffen).
Für Event 10033 ergibt sich der gleiche Effekt wie für 10032: die Aktivierung des Traces ändert das Verhalten (Prof. Heisenberg, bitte übernehmen Sie...).
In der v$ses_optimizer_env sehe ich übrigens außer den explizit gesetzten Parametern keine Unterschiede für beide Fälle (also spielen abgeleitete Parameter vermutlich keine Rolle).
Das ist jetzt auch noch nicht unbedingt ein befriedigendes Ergebnis, aber für den Moment genügt es mir.
Keine Kommentare:
Kommentar veröffentlichen