In Fortsetzung der hier, hier und hier aufgeführten Beobachtungen jetzt zur Auflösung: warum wird eine CTAS-Operation langsamer, wenn sie größere Ressourcen verwenden kann? Die Antwort ist, wenn ich jetzt noch mal darüber nachdenke, recht offensichtlich: weil die Operation die Ressourcen nicht verwendet.
Zwar zeigt v$sesstat die Verwendung der zugeteilten PGA-Ressourcen an, aber offenbar lügt Oracle da ziemlich dreist. Irgendetwas hatte mich in den 10032er Traces irritiert, aber ich konnte es nicht genau greifen, obwohl es ziemlich offensichtlich ist. Zunächst die Angaben im Trace für die Ausführung mit dem automatischem PGA-Management:
---- Sort Parameters ------------------------------ sort_area_size 22609920 sort_area_retained_size 22609920 sort_multiblock_read_count 15 max intermediate merge width 44
Und zum Vergleich die Angaben für die manuell vergrößerte SORT_AREA_SIZE:
---- Sort Parameters ------------------------------ sort_area_size 98304 sort_area_retained_size 65536 sort_multiblock_read_count 1 max intermediate merge width 2
Dass der sort_multiblock_read_count auf 1 gesenkt war, hatte ich registriert, nicht aber die Werte für die SORT_AREA_SIZE, die ich ja explizit auf einen sehr hohen Wert gesetzt hatte. Eine Erklärung für das Verhalten habe ich dann schließlich an unerwarteter Stelle gefunden: http://martinpreiss.blogspot.com/2008/12/manuelle-einstellung-der-sortareasize.html. In einem dort verlinkten Blog-Eintrag erläutert Jonathan Lewis, dass in 10.2.0.4 die Einstellung der SORT_AREA_SIZE nicht wirksam wird, obwohl das System behauptet, sie wäre aktiv. So sieht man in v$ses_optimizer_env den erhöhten Parameter-Wert, aber verwendet wird er deshalb noch lange nicht. Der vorgeschlagene Workaround für diesen Bug wirkt eher bizarr: man muss das ALTER SESSION-Kommando zwei Mal absetzen, dann ist die Datenbank überzeugt, dass man es tatsächlich ernst meint damit. Mehrmaliges einbeiniges Hüpfen um den Server ist anscheinend nicht erforderlich.
Nach Verwendung des - gut, ich nenn es weiterhin so - Workarounds wird die vergrößerte SORT_AREA_SIZE dann tatsächlich wirksam und die Laufzeit der Operation sinkt auf einen Wert knapp über einer Minute.
Keine Kommentare:
Kommentar veröffentlichen