Speicher-Fragmentierung für Linux-Server

Nikolay Savvinov hat zuletzt mehrere interessante Artikel zum Thema der Memory Fragmentation auf Linux-Systemen veröffentlicht, die ich hier einfach mal verlinke, ohne mich allzu intensiv mit den Inhalten zu beschäftigen:

• How to hang a server with a single ping, and other fun things we learned in a 18c upgrade: behandelt die Probleme eines Upgrades eines alten und komplexen Systems von Oracle 11 auf 18, die sich zunächst in einem hohen Load Average manifestierten und mit ps analysiert werden konnten, wobei vor allem der "wait channel" (wchan) ausgewertet wurde. Das Ergebnis deutete auf Memory Fragmentierung hin, da vor allem der Channel cma_acquie_dev sichtbar wurde, wobei CMA für "Contiguos Memory Allocator" steht. Zur Behebung der Symptome diente zunächst die Deaktivierung von NUMA, aber die eigentliche Ursache waren wohl rds-ping Operationen. Wer angesichts dieser Zusammenfassung etwas ratlos bleibt (zumindest im Bereich der Auflösung), darf das gerne auf meine mangelnde Sachkenntnis in diesen Bereichen zurückführen.
• Memory fragmentation: the silent performance killer: erklärt Memory Fragementation und zeigt, mit welchen Mitteln man die zugehörigen Effekte in Linux-Systemen analysieren kann. Hier versuche ich mich erst gar nicht an der Zusammenfassung, sondern zitiere "As usual, exact solution will depend on the specific scenario of the problem, but it would typically involve changing VM settings (such as vm.min_kbytes_free) or adjusting memory cgroup configuration."
• Where did my RAM go?: liefert ein wenig R code zur Visualisierung der Performance-Informationen.

Da ich in Linux-Zusammenhängen häufiger über Memory-Fragen stolpere, werden ich das Werkzeug möglicherweise gelegentlich zum Einsatz bringen.

Speichernutzungsdetails in V$PROCESS_MEMORY_DETAIL

Im Rahmen seiner epischen Oracle Memory Troubleshooting Reihe (die im Jahr 2009 begann) erläutert Tanel Poder die verschiedenen Informationen zur PGA-Nutzung der Prozesse, die die dynamischen Performance-Views zur Verfügung stellen:

• v$process: liefert in den Spalten pga_used_mem und pga_alloc_mem einen (in vielen Fällen bereits ausreichenden) Überblick über die PGA Nutzung.
• v$process_memory: liefert Informationen zur Verteilung dieser Ressourcennutzung auf die Bereiche SQL, PL/SQL, Java, Unused(Freeable) und Other.
• für den Bereich SQL liefert v$sql_workarea_active Details zur Verteilung des Speicherverbrauchs auf einzelne Schritte in den Ausführungsplänen.
• für den Bereich "Other" ist die Analyse etwas komplizierter und erforderte früher die Erzeugung eines PGA/UGA memory heapdump (mit Hilfe von oradebug oder durch Aktivierung eines entsprechenden Events für die Session).
• Seit 10.2 liefert v$process_memory_detail die benötigten Details. Allerdings wird diese View nur auf ausdrückliche Aufforderung gefüllt (oradebug dump pga_detail_get) - und erst dann, wenn der zugehörige Prozess wieder aktiv wird.
• Die in der Regel eher kryptischen name und heap_name Angaben aus v$process_memory_detail lassen sich dann (hoffentlich) über eine MOS-Suche auflösen.
• auf OS-Ebene kann zusätzlich pmap -x verwendet werden.

Mit diesem Vorgehen wird die Ermittlung der Informationen sehr einfach - ihre Interpretation bleibt aber auf ergänzende Erläuterungen (aus MOS oder anderer Quelle) angewiesen: ich zumindest kann mit Namen wie "kxsc: kkspsc0 2" erst einmal wenig anfangen.

Hash table size für Lookup-Ergebnisspeicherung

Jonathan Lewis hat dieser Tage in seinem Blog einen Fall untersucht, in dem ein Funktionsaufruf einen Tabellenzugriff enthält, der im Execution Plan nicht erscheint (was auch bei FILTER-Operationen vorkommen kann). Wenn man die rowsource-Statistiken und die projection-Informationen betrachtet, wird deutlich, dass der Funktionsaufruf in einem SORT UNIQUE step untergebracht ist. Festhalten kann man auf jeden Fall, dass der Plan die Operation nicht besonders deutlich abbildet.

Ausgehend vom gegebenen Beispiel habe ich ein paar Experimente durchgeführt, die ein Ergebnis lieferten, das ich so nicht erwartet hatte:

drop table t1;
drop table t2;

-- nur 250 unterschiedliche IDs, statt 2500 im Original
create table t1 tablespace test_ts
as
select
    mod(rownum, 250)          id,
    lpad(rownum,200)    padding
from    all_objects
where   rownum <= 2500
;

create table t2 tablespace test_ts
as
select  * from t1
;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

-- Funktion als DETERMINISTIC definiert
create or replace function f (i_target in number)
return number deterministic
as
    m_target    number;
begin
    select max(id) into m_target from t1 where id <= i_target;
    return m_target;
end;
/

select  /*+ gather_plan_statistics */
    id
from    t1
minus
select
    f(id)
from    t2
;

-- letzte Ergebnisspalten aus Gründen der Übersichtlichkeit abgeschnitten
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.16 |   54670 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - STRDEF[22]
   2 - (#keys=1) "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - (#keys=1) "F"("ID")[22]
   5 - "ID"[NUMBER,22]

Die Änderung im Test liegt nur in der Reduzierung der ID-Werte und der Definition der Funktion als DETERMINISTIC. Meine Annahme war, dass der Funktionsaufruf auf diese Weise nur einmal für jeden distinkten Wert ausgeführt werden müsste, woraus sich 250 FTS auf die Tabelle T1 ergeben sollten. Daraus ergab sich die Erwartung, dass die Buffers-Angabe in step 4 bei 19250 (= 250 * 77) liegen würde. Tatsächlich lautete das Ergebnis aber 54670 (= 710 * 77). Woher kommt die Abweichung? Die Antwort lieferten mir die Kommentare von Sayan Malakshinov und Kapitel 9 (Query Transformation) in Cost Based Oracle: die intern zur Speicherung der Zwischenergebnisse des Lookups verwendete HASH TABLE ist nicht groß genug, um hash collision zu vermeiden. Um die Größe dieser Struktur zu verändern, kann man den Parameter _query_execution_cache_max_size anpassen (default: 65536; wie immer gilt, dass die Änderung von underscore-Parametern auf eigene Gefahr erfolgt):

alter session set "_query_execution_cache_max_size"=262144;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.08 |   29743 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

alter session set "_query_execution_cache_max_size"=2097152;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.06 |   19348 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

Mit den 2M für _query_execution_cache_max_size bin ich dann mit 19348 Buffers schon recht nah am erwarteten Ergebnis von 19250 - und das genügt mir in diesem Fall.

Cursor: Pin S Waits

Andrey Nikolaev erläutert in seinem Blog die Abgründe des dubiosen cursor:pin s Events, das in den undurchsichtigen Zusammenhang der Mutex-Operationen gehört:

• Mutex waits. Part 1. “Cursor: Pin S” in Oracle 10.2-11.1. Invisible and aggressive: "This is CPU consuming busy wait, almost invisible to Oracle Wait Interface and standard diagnostics tools. Patch 6904068 converts this wait into "normal" wait" 

Weitere Artikel sollen folgen.

Nested Loops Prefetching

Randolf Geist erläutert in seinem Artikel Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching einige technische Details des Prefetchings bei NL Operationen. Offenbar spielt die Sortierung der Daten in den beiden via NL verknüpften Tabellen eine entscheidende Rolle:

Table Prefetching has been introduced in Oracle 9i in order to optimize the random physical access in Nested Loop Joins, however it also seems to have a positive effect on logical I/O. The effectiveness of this optimization depends on the data order - if the data from the driving row source is in the same order as the inner row source table buffers can be kept pinned. Note that the same doesn't apply to the index lookup - even if the data is ordered by ID and consequently the same index branch and leaf blocks will be accessed repeatedly with each iteration, a buffer pinning optimization could not be observed.

Im Beispiel wird auch deutlich, wie unterschiedlich sich als unique und als non-unique definierte Indizes in diesem Zusammenhang verhalten - was möglicherweise ein Argument gegen den Einsatz von non uninque indexes für primary keys sein mag.

LIO Optimierung

Randolf Geist hat eine Artikelserie begonnen, in der er verspricht diverse Optimierungen für die Durchführung von logical I/O zu erläutern, die in den letzten Versionen des Oracle Servers eingeführt wurden. Der erste Artikel Logical I/O - Evolution: Part 1 - Baseline stellt zunächst die Voraussetzungen dar - und das in sehr strukturierter Form mit einem griffigen Beispiel und ausführlichen Erläuterungen zu den Ergebnissen. Zum Artikel noch zwei Anmerkungen:

MINIMIZE RECORDS_PER_BLOCK

Interessant ist die verwendete Option "MINIMIZE RECORDS_PER_BLOCK", die die maximale Anzahl von Datensätzen pro Block begrenzt - offenbar ein Feature, das eigentlich zur Optimierung von Bitmap Indizes eingeführt wurde. Laut Doku gilt: "The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible. [...] Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records."

Das Verhalten dieser Option entspricht übrigens nicht ganz meinen Vorstellungen, wie folgender Test (mit 11.2.0.1) zeigt:

-- Anlage einer Tabelle mit einem Datensatz in einem non-assm-Tablespace
create table t1 tablespace test_ts
as
select rownum id
  from dual;

-- Erzeugung von Statistiken
exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

-- Angaben aus USER_TABLES
select num_rows
     , blocks
  from user_tables
 where table_name = 'T1';

NUM_ROWS     BLOCKS
-------- ----------
       1          1

-- ohne minimize records_per_block
insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000         20

-- mit minimize records_per_block
-- Neuanlage der Tabelle mit einem Satz
alter table t1 minimize records_per_block;


insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000       5001 

Demnach komme ich auch zwei Sätze pro Block, obwohl vor dem ALTER TABLE nur ein Satz im Block vorliegen kann; allerdings sagt die Doku auch: "Oracle recommends that a representative set of data already exist in the table before you specify MINIMIZE" - möglicherweise ergibt sich der beobachtete Effekt also daraus, dass die Abschätzung der rows per block nicht 100% akkurat ist. Für das Beispiel des Herrn Geist ist das aber irrelevant, da er zum gewünschten rows per block-Verhältnis kommt.

Nachtrag 17.07.2011: in den Kommentaren zu Randolf Geists Blog-Eintrag findet man noch ein paar Ergänzungen zum Thema (etwa, dass der Hakan factor, der die Anzahl möglicher Sätze in einem Block angibt, in der SPARE1 Spalte von sys.tab$ zu finden ist); möglicherweise kann man über "minimize records_per_block" keine Block-Füllung mit einem einzelnen Satz hervorrufen.

Nachtrag 19.07.2011: mal wieder eine interessante Koinzindenz: Richard Foote hat gerade auch über die "minimize records_per_block" Option geschrieben.

Pinned Buffers

Im verwendeten Nested Loops Join liegt die Gesamtzahl der Blockzugriffe deutlich niedriger als erwartet, da die per FTS ermittelten Blocks der inneren Tabelle und der Root Block des Index gepinnt werden können, so dass kein Get für das cache buffers chains latch erforderlich ist - und damit kein logical I/O - vgl. dazu Jonathan Lewis Ausführungen, die ich gelegentlich hier verlinkt hatte. Die Anzahl gepinnter Blocks liefert die Statistik "buffer is pinned count". Das Pinnen der Buffer ist auch die Ursache für unterschiedliche LIO-Werte bei veränderter Arraysize:

Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the "fetchsize" or "arraysize" for bulk fetches can influence the number of logical I/Os required to process a result set.

Buffer Cache Inhalt

Arup Nanda erläutert im vierten Teil seiner hochinteressanten Reihe zu grundlegenden Konzepten des Oracle Servers die Nutzung des Buffer Caches. So wie's aussieht, werde ich hier auf jeden Beitrag dieser Serie verweisen ...

Nanda zeigt, wie es dazu kommt, dass zu einem Tabellenblock zahlreiche Kopien im Buffer Cache erscheinen. Die kurze Antwort lautet "Versionierung", aber die lange Fassung ist aufgrund der griffigen Beispiele deutlich spannender.

Beim Nachspielen des Beispiels gab's in meinem Testsystem übrigens auch Buffer mit dem Status 1st level bmb und 2nd level bmb, die anscheinend zum Thema ASSM gehören.

Nachtrag 19.04.2011: manchmal kommt es zu erstaunlichen thematischen Häufungen in der Blog-Welt: http://jonathanlewis.wordpress.com/2011/04/18/consistent-reads/, worin Jonathan Lewis erläutert, dass die LIOs beim Lesezugriff nicht nur mit der Anzahl der Blocks eines Objekts zusammenhängen, sondern sehr stark von der Existenz von undo-Informationen beeinflusst werden.

Nachtrag 10.11.2014: eine erweiterte Fassung des älteren Artikels liefert der Artikel Cache Buffer Chains Demystified.

Oracle Troubleshooting TV show

Tanel Poder hat eine Fernsehshow zum Thema Shared Pool produziert. Oder, wenn's keine Fernsehshow ist, dann doch zumindest ein extrem erhellendes Video. Normalerweise bin ich kein besonderer Freund von technischen Videos, aber der Herr Poder ist einfach großartig und liefert jede Menge Details aus der Tiefe des Systems (z.B. über die x$ksmsp - was wohl für "kernel service memory shared pool" steht - , eine Tabelle, die Informationen zu den Memory Chunks im Shared Pool liefert; und auf die man in der Produktion lieber nicht zugreifen sollte - eine Sammlung von Details zu den X$-Objekten findet man übrigens hier, wobei ich zur Qualität der Aussagen wenig sagen kann, aber zumindest werden plausible Quellen herangezogen).

WORKAREA_SIZE_POLICY - Teil 5

Nach dem wilden Experimentieren und freien Assoziieren jetzt zurück zu den Autoritäten: was sagt Jonathan Lewis in seinem Cost-Based Oracle Fundamentals-Buch zum Thema der Sortierungen? Das passende Kapitel ist Nr 13: Sorting and Merge Joins. Hier eine kurze Liste dort erläuterter Details (ohne Anspruch auf Vollständigkeit):

• beim Sortieren gibt es drei unterschiedlich effektive Varianten:
• optimal: erfolgt vollständig im Arbeitsspeicher. Dabei erfolgt die Allocation des Speichers nach Bedarf (es wird also nicht initial der komplette Speicher verwendet, der duch die SORT_AREA_SIZE definiert ist). Für 6 MB Daten benötigt Lewis in seinem Test einen Speicher von 25,5 MB, um die Sortierung optimal zu halten; ob dieses Verhältnis der Normalfall ist, wäre zu testen.
• onepass: erfolgt, wenn die Daten nicht komplett im Speicher sortiert werden können, aber es möglich ist, kleinere Teilmengen zu sortieren, die Ergebnisse auf der Platte abzulegen, und anschließend Stücke von allen vorsortierten Mengen im Speicher zusammenzuführen
• multipass: verhält sich wie die onepass-Variante, aber es gibt zu viele vorsortierte Mengen, so dass sie nicht in einem Schritt zusammengeführt werden können. Stattdessen werden größere Zwischenergebnisse zusammengeführt wieder auf der Platte abgelegt und diese dann wieder kombiniert.
• zur Analyse der Effekte nutzt JL die gleichen Werkzeuge, die ich auch bei meinen Tests eingesetzt hatte (wobei er aber kompetenter wirkt...): v$sesstat (oder auch v$mystat für die eigene Session), sowie die Trace Events 10032 und 10033 (außerdem schaut er sich auch noch Block Dumps der temp files an).
• Zum Sortieralgorithmus entwickelt Lewis die Theorie, dass intern ein (balancierter) binary insertion tree im Spiel sein könnte, und liefert Indizien, die für diese Annahme sprechen; seit 10.2 könnte aber auch ein neuer Mechanismus im Spiel sein, so dass ich mir die Details spare.
• Sortierungen mit größerer Speichernutzung benötigen auch größere CPU-Ressourcen, so dass für Systeme, in denen I/O ein kleineres Problem als die CPU-Nutzung ist, auch eine Verkleinerung der Speichernutzung zu einer Beschleunigung führen kann.
• Kapitel 13 ist ziemlich umfangreich, so dass ich zu den Erläuterungen zur WORKAREA_SIZE_POLICY ein andermal kommen werde.

Noch ein abschließender Hinweis. Das CBO-Buch stammt von 2006 und behandelt 10.1 (und in wenigen Fällen 10.2) - manche Beobachtungen sind wahrscheinlich für 11.2 nicht mehr zutreffend. Die grundlegenden Prinzipien dürften sich aber nicht verändert haben.

WORKAREA_SIZE_POLICY - Teil 4

And now ... The Punchline!

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.

WORKAREA_SIZE_POLICY - Teil 3

Anschließend an die Beobachtungen von gestern kann ich noch ein paar seltsame Effekte ergänzen - aber leider nicht viele Erklärungen dafür..

Noch nicht erwähnt hatte ich die vielleicht wichtigste Beobachtung - nämlich, dass Oracle mit der WORKAREA_SIZE_POLICY=AUTO das fragliche CREATE TABLE Statement schnell durchführt (Laufzeit < 2 min). In gewisser Weise wird die gesamte Fragestellung dadurch rein akademisch, da ja alles funktioniert, wenn man der DB nicht ins Handwerk pfuscht. Trotzdem wüsste ich gerne, wieso das System sich so verhält.

Ebenfalls noch nicht erwähnt war das Verhalten unter Oracle 11.1.0.7 (Instanz auf einem Linux-Server ohne ermittelte Systemstatistiken): dort spielt die Einstellung offenbar keine entscheidende Rolle. Mit der AUTO-Einstellung läuft die CTAS-Operation in 45 sec durch und die gleiche Laufzeit ergibt sich auch bei manueller Einstellung der SORT_AREA_SIZE und HASH_AREA_SIZE auf 100 bzw. 300 MB. In den 10046er Traces sieht man jeweils die größeren block cnt-Angaben (15). Das Problem scheint für Oracle 11 also gar keine Rolle zu spielen (wobei sich die insgesamt bessere Performance vermutlich auch den schnelleren Platten des Servers verdankt).


Bizarr ist allerdings, dass heute bei der Wiederholung der Tests auf dem Oracle 10 - System die manuelle Einstellung der PGA-Parameter auch für den Fall der 50 MB zu den langen Laufzeiten führt (nur AUTO liefert jetzt noch die schnelle Laufzeit). Was sich gegenüber dem gestrigen Versuch geändert haben könnte, ist mir im Moment noch unklar.

WORKAREA_SIZE_POLICY - Teil 2

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.

WORKAREA_SIZE_POLICY - Teil 1

Der Initialisierungsparameter WORKAREA_SIZE_POLICY dient dazu, zu bestimmen, wer für die Verteilung von PGA-Ressourcen (Process Global Area mit den Daten, die den einzelnen Prozessen zugeordnet sind) zuständig ist: AUTO überlässt die Verwaltung dem System, das die vorhandenen Ressourcen abhängig von der aktuellen Workload zuteilt (wobei die einzelne Operation meiner Erinnerung nach nicht mehr als 5% des über den Parameter PGA_AGGREGATE_TARGET definierten verfügbaren Speichers erhalten sollte (*) ). Mit der Einstellung MANUAL übernimmt der DBA die Kontrolle und kann dann die Ressourcenzuteilung über die Parameter SORT_AREA_SIZE und HASH_AREA_SIZE bestimmen. Üblicherweise funktioniert die automatische Zuteilung recht gut, aber für größere Batchoperationen mit großen Speicheranforderungen kann es sinnvoll sein, die manuelle Kontrolle zu wählen. So weit die Theorie, oder das, was ich mir davon gemerkt habe.

Deshalb hat mich das Ergebnis folgenden Szenarios überrascht (Instanz 10.2.0.4 auf einem Windows 2003er Server; Systemstatistiken wurden ermittelt): ich wollte für eine Tabellenpartition mit ca. 3.000.000 Sätzen und einer Größe von etwa 170 MB eine Aggregation anlegen und setzte dazu die Parameter SORT_AREA_SIZE und HASH_AREA_SIZE jeweils auf 300 MB:

alter session set WORKAREA_SIZE_POLICY=Manual;
alter session set sort_area_size = 300000000;
alter session set hash_area_size = 300000000;

Die abgesetzte Query besitzt einen relativ harmlosen Ausführungsplan:

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |                   |       |       |  1483 (100)|          |       |       |
|   1 |  LOAD AS SELECT          |                   |       |       |            |          |       |       |
|   2 |   SORT GROUP BY          |                   |  3029K|   395M|   883  (54)| 00:00:07 |       |       |
|   3 |    PARTITION RANGE SINGLE|                   |  3029K|   395M|   883  (54)| 00:00:07 |     1 |     1 |
|   4 |     VIEW                 |                   |  3029K|   395M|   883  (54)| 00:00:07 |       |       |
|   5 |      HASH GROUP BY       |                   |  3029K|   130M|   678  (71)| 00:00:06 |       |       |
|*  6 |       TABLE ACCESS FULL  | FACT_TABLE_xxxxxx |  3029K|   130M|   351  (43)| 00:00:03 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------

Für mich überraschend war allerdings die lange Laufzeit von ca. 13 min - dass Oracles Prognose von 7 sec recht optimistisch war, konnte man absehen, aber 13 min sind doch ziemlich viel für die Datenmenge.

Für einen zweiten Versuch setzte ich die beiden %_AREA_SIZE-Paramter auf jeweils 50MB, und erhielt einen nahezu identischen Ausführungsplan:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |                   |       |       |       | 13112 (100)|          |       |       |
|   1 |  LOAD AS SELECT          |                   |       |       |       |            |          |       |       |
|   2 |   SORT GROUP BY          |                   |  3029K|   395M|       | 12513   (5)| 00:01:38 |       |       |
|   3 |    PARTITION RANGE SINGLE|                   |  3029K|   395M|       | 12513   (5)| 00:01:38 |     1 |     1 |
|   4 |     VIEW                 |                   |  3029K|   395M|       | 12513   (5)| 00:01:38 |       |       |
|   5 |      HASH GROUP BY       |                   |  3029K|   130M|   394M| 12308   (5)| 00:01:36 |       |       |
|*  6 |       TABLE ACCESS FULL  | FACT_TABLE_xxxxxx |  3029K|   130M|       |   351  (43)| 00:00:03 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------

Erwartungsgemäß erscheint die TempSpc-Angabe, weil die Sortierung jetzt nicht mehr komplett im Memory erfolgen kann, aber davon abgesehen sind die Pläne ziemlich identisch (wobei die Kardinalitäten und Größenangaben durchaus plausibel wirken). Unerwartet ist aber, dass die tatsächliche Laufzeit bei der kleineren Speicherzuweisung von 13 min auf 1:30 min sinkt. Um den Fall klarer fassen zu können, schaute ich mir die Deltas für die Statitisken in v$sesstat an, und beobachtete folgende Unterschiede:

                                              Fall 1 (50MB)          Fall 2 (300MB)
session uga memory max                               15 MB                  300 MB
session pga memory max                               42 MB                  300 MB
physical read total bytes                           800 MB                 5700 MB
physical write total bytes                         1000 MB                 6000 MB
physical reads direct temporary tablespace          35.767                 340.613
physical writes direct temporary tablespace         35.767                 340.613
sorts (disk)                                             1                       1
sorts (rows)                                     9.088.155               9.088.695

Demnach scheint die Variante mit den 300MB den verfügbaren Speicher tatsächlich zu nutzen, dabei aber deutlich größere Leseoperationen durchzuführen und vor allem direct path Lese- und Schreiboperationen hervorzurufen. Der betroffene Rechner war während der Tests nicht unter Last, so dass Swapping und Paging als Erklärung ausscheiden.

Ich habe außerdem noch ein 10032er Trace durchgeführt, um die Sortierungsoperationen genauer analysieren zu können, aber dazu (vielleicht) später mehr.

(*) Nachtrag 16.02.2011: inzwischen weiß ich, dass die 5% für Version 10.2 (und folgende) nicht mehr gelten. Details dazu findet man hier.

Consistent Gets

Mal wieder ein Link, diesmal zum Thema constistent gets und auf den Blog von Harald van Breederode. Erläutert wird, wieso der Zugriff auf eine Tabelle, die nur einen Block umfasst, 8 consistent gets benötigt (3 sind bereits für den Zugriff auf eine leere Tabelle erforderlich, die übrigen ergeben sich aus der verwendeten array size). Erhellend sind auch die Hinweise auf die Effekte von Gruppenfunktionen, Lesekonsistenz und Veränderungen der HWM. Da es gute Gründe gibt, die Anzahl der consistent gets als zentrales Kriterium für die Performance einer Query anzusehen, sollte man (also ich) sich diese Details merken.