oracle 804, speicher-tuning

[Peregrin]

hi,

umgebung:

os aix 4.3

dbms oracle 804

problem:

auf unserem datenbankserver wollte ich aus tuning-gruenden das speicher-verhalten optimieren.

da bei grossen abfragen viele io-zugriffe durchgefuehrt wurden, habe ich den "shared pool" vergroessert, einen "large pool" und eine sort_area angelegt.

aus der ini-datei:

shared_pool_size = 48000000

LARGE_POOL_SIZE = 150000000

SORT_AREA_SIZE = 16000000

SORT_AREA_RETAINED_SIZE = 0

mit diesen einstellungen gab es keine io-zugriffe mehr, aber es kam nach einiger zeit zu nicht mehr freigegebenem speicherplatz im large pool

ausserdem wurden bei einigen abfragen bis zu 110 mb speicher belegt.

bei zwei grossen abfragen gleichzeitig kommt die fehlermeldung "ora-04031, unable to allocate 4160 bytes of shared memory"

durch vergroesserung des shared_pool kann dies umgangen werden, aber da physikalisch nicht mehr hauptspeicher zur verfügung steht, paged er dann wieder und erzeugt io-zugriffe.

nach dem zuruecksetzten der large_pool- und sort_area-werte laeuft die db (bisher) wieder stabil.

frage:

hat jmd schon einmal aehnliche erfahrungen gemacht?

gibt es einstellungen, welche ich noch vornehmen kann, um die db dazu zu bekommen, abfragen im hauptspeicher durchzufuehren?

danke im voraus

[chweiss]

Hallo,

Du sprichst von zu vielen IOs. Wie ist denn Deine Hit Ratio?

Bei zu vielen PIOs (physical IOs) - im Gegensatz zu LIOs (logical IOs) - würde ich eher erst db_block_buffer vergrößern.

Im Shared Pool liegen doch "nur" die geparsten Statements, Structures, Cursurs, etc.?

ORA-04031 tritt im Allgemeinen dann auf, wenn der Shared Pool zu sehr fragmentiert ist und nicht mehr genügend zusammenhängender Speicher zur Verfügung steht.

Beim obigen Fehler sollte erst versucht werden, die Applikation zu tunen, sprich SQL-Statements, Cursors, etc. zu sharen. In Deinem Fall also beide großen Abfragen aufeinander abstimmen.

Was genau bedeutet, "Die Anfragen haben bis zu 110MB belegt"?

Ohne Dein Hit Ratio zu kennen:

Ich würde large_pool_size erstmal wieder disablen und etwas auf large_pool_size draufschlagen.

Interessant sind auch SHARED_POOL_RESERVED_SIZE und SHARED_POOL_RESERVED_MIN_ALLOC. Schaue sie Dir mal an.

Dein Problem mit dem IO hast Du evtl. schon mit den 16MB sort_area_size aus der Welt geschafft?!

v$sgastat, v$shared_pool_reserved, etc. sind auch interessant...

P.S.: Oracle 8.0.4 ist doch schon etwas älter. Vielleicht sollte man auch noch nach Bugfixes ausschau halten. Es gab immer mal wieder häßliche memory leaks im shared pool, die gefixt wurden...

Gruß,

Christian

[Peregrin]

hi christian,

danke erstmal für die antwort.

ich muss gleich sagen, ich bin kein dba und kenne mich mit dem dbms oracle auch eher aus sicht der anwendungsentwicklung aus.

die hit ration habe ich noch nicht überprüft, war mir bis gerade eben auch kein wirklicher begriff.

die ios habe ich über den systemmonitor festgestellt. da es hier um plattenzugriffe geht, vermute ich, dass es physical ios sind.

über das tunen der statements muss ich mir erst noch einige gedanken machen (habe nur einen teil der anwendung selber entwickelt, den rest von kollegen üernommen)

Ich würde large_pool_size erstmal wieder disablen und etwas auf large_pool_size draufschlagen.

ich vermute, du meinst den shared pool vergrössern?

den large pool habe ich wieder abgeschaltet und der shared pool vergrössert. das hat auch wirkung gezeigt.

auf die 110 mb speicherbelegung im large pool kam ich durch auswertung der v$sgastat während eine abfrage lief. dort war der freie platz mit 40mb angegeben (150mb - 40mb = 110 mb)

die ios sind weniger geworden, soweit richtig. allerding nicht völlig verschwunden. sind aber auch momentan nicht mehr im kritischen bereich.

ca 1 sort-to-disc gegen 120 sort-in-memory (aus der v$sysstat).

bugfixes werde ich mal überprüfen.

nochmals danke, und wenn ich was herausfinde, melde ich mich... :-)