|
Bijna iedere DBA heeft wel eens een ora-1000 error, "Maximum open cursors exceeded" gekregen. Dit artikel gaat over de init.ora parameters die invloed hebben op open cursors, het verschil tussen open en cached cursors, het sluiten van cursors en het monitoren van de open en cached cursors. Open cursors
Open cursors nemen ruimte in beslag in de shared pool, in de library cache. Om er voor te zorgen dat een sessie niet de library cache opvult, of de CPU met miljoenen parse requests blokkeert, zetten we de init.ora parameter OPEN_CURSORS.
OPEN_CURSORS set het maximum aantal open cursors die iedere sessie op kan hebben, per sessie. Als OPEN_CURSORS op 1000 is gezet, dan kan iedere sessie 1000 cursors tegelijk open hebben. Als een enkele sessie het aangegeven aantal cursors open heeft en er nog een wil openen, dan zal er een ora-1000 volgen.
De default waarde van OPEN_CURSORS is 50, maar Oracle beveelt een minimum van 500 te zetten voor de meeste applicaties. Sommige applicaties zullen er zelfs meer nodig hebben zoals web applicaties.
Session cached cursors
Er zijn 2 hoofd init.ora parameters die invloed hebben op cursors, en ze worden vaak door elkaar gehaald. Een is de OPEN_CURSORS, en de ander SESSION_CACHED_CURSORS.
SESSION_CACHED_CURSORS set het aantal van 'cached closed cursors' die elke sessie mag hebben. Je kunt de waarde van SESSION_CACHED_CURSORS hoger of lager zetten dan OPEN_CURSORS of ergens daar tussen. Deze parameter heeft geen effect op de ora-1000's of op het aantal, dat je open hebt. Aan de andere kant heeft OPEN_CURSORS geen invloed op het aantal, dat gecached zijn. Ze hebben du geen onderlinge relatie.
Als SESSION_CACHED_CURSORS niet wordt gezet, zal het default de waarde 0 krijgen en zal er voor je sessie ook geen cursor gecached worden. ( uiteraard zullen ze wel gecached worden in de schared pool, maar je sessie zal deze dan moeten zoeken). Als het wel is gezet zal er een parse request uitgevoerd worden, Oracle checkt de library cache om te kijken of er meer dan 3 parse request voor dat statement zijn uitgevoerd. Als dat zo is zal Oracle de sessie cursor die aan dat statement hangt, in de sessie cursor cache verplaatsen. Vervolgens zullen parse requests voor dat statement die door de zelfde sessie zijn uitgevoerd, vanuit session cursor cache gevuld worden, en op die manier zelfs een soft parse voorkomen. Technisch gezien kan een parse niet geheel voorkomen worden echter een soft parse is sneller en heeft minder CPU nodig.
In de session cursor cache, Onderhoud Oracle de cached cursors gebruik makend van een LRU lijst. Als er eenmaal meer SESSION_CACHED_CURSORS zijn geclosed, dan cursors gecached, dan zal Oracle beginnen met het droppen van cached cursors die aan het eind van de LRU lijst staan, indien het ruimte moet maken om en nieuwe cursor te kunnen cachen.
Waarom cache cursors?
Het meest voor de hand liggende antwoord is dat dit parse time zal verminderen waardoor er weer snellen ge-execute zal worden. Dit is vooral zo bij applicaties als Oracle Forms, waar het switchen tussen de verschillende forms er voor zorgt dat alle geopende cursors die door het eerste form zijn geopend, gesloten zullen worden. Het terug switchen zal identieke cursors openen. Op deze manier zal caching cursors door sessies het reparsen echt.
Er is echter nog een voordeel. Omdat een sessie niet in de library cache hoeft te zoeken naar eerder gebruikte SQL, zal caching cursors door sessies resulteren in minder library cache en shared pool latches. In erg drukke OLTP omgevingen zijn dit vaak bottlenecks. Door het verminderen van latching resulteert in verminderen van' latch waits', waardoor er weer snelheid gewonnen wordt.
Het Monitoren van open cursors
Er bestaat een hoop verwarring tussen open cursors en cached cursors. Dit komt onder andere door de namen van de dynamische performance views die worden gebruikt om ze te monitoren: ·v$open_cursor laat de cached cursors door de sessie zien, niet de cursors die nu open zijn. Als je wil weten hoeveel cursors een sessie open heeft, moet je niet in de view v$open_cursor kijken. Deze laat voor elke sessie zien hoeveel cursors er in de cursor cache aanwezig zijn, maar niet de cursors die echt open zijn.
Om de open cursors te monitoren, moet je de v$sesstat view bekijken where name='opened cursors current'. Dit geeft het aantal huidige geopende cursors door de sessie:
--totaal cursors open, door sessie
SQL>select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Als je meerdere applicatie hebt draaien met meerdere webservers is het misschien handiger om te monitoren per username en machine:
--totaal cursors open, door username & machine
SQL>select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
Het Tunen van OPEN_CURSORS
Het beste advies voor het tunen van OPEN_CURSORS is om het niet te tunen. Zet het zo hoog dat je er geen omkijken naar hebt. Als je sessies tegen de limiet van OPEN_CURSORS aanlopen, kan je het altijd verhogen. Het doel van het tunen in deze is om nooit tegen een ora-1000 aan te lopen tijden normale operaties.
Als je de waarde van OPEN_CURSORS op een hoog niveau zet wil dit niet zeggen dat iedere sessie ook dat aantal zal openen. Cursors worden geopend op een basis van noodzaak. En als je applicatie een cursor leak heeft zal hij dat in ieder geval laten zien.
Om te zien of de waarden voor OPEN_CURSORS hoog genoeg staan, kan je de v$sesstat monitoren:
SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
1953 2500
Nadat je de waarde hebt verhoogd, blijf dan even monitoren om te kijken of je applicatie niet weer naar de nieuwe waarde van OPEN_CURSORS blijft lopen. Als dat namelijk zo is dan kan het zijn dat je applicatie een cursor leak heeft in zijn code. Met andere woorden, je applicatie opent wel cursors maar sluit ze niet wanneer hij klaar is.
Een DBA kan hier niets meer aan doen. De applicatie developer's moeten door de code spitten en de cursors vinden die open blijven. Als tijdelijke oplossing is het enige wat je eigenlijk kan doen is de OPEN_CURSORS waard erg hoog zetten en tijden schedulen waarop de applicatie sessies worden gesloten en weer geopend.
Monitoren van de sessie
De v$sesstat view geeft ook een statistiek mee om het aantal cursors dat elke sessie in zijn sessie cursor cache heeft:
--session cached cursors, by session
SQL>select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count' ;
Je kunt ook direct zien wat er in de session cursor cache zit door de v$open_cursor uit te lezen. De v$open_cursor laat sessie cached cursors zien by SID, en geeft de eerste paar characters van het statement en het sql_id, zodat je daadwerkelijk kunt zien waar de cursors voor zijn:
SQL>select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid
;
Het Tunen van SESSION_CACHED_CURSORS
Als er voor kiest om SESSION_CACHED_CURSORS te gebruiken om een applicatie te helpen die constant cursors aan het sluiten en openen is, kan je de effectiviteit via nog 2 statistieken in de v$sesstat view. De statistiek "session cursor cache hits" laat zien hoe vaak een statement waar de sessie, een parse aanvraag voor heeft verzonden, in de sessie cursor_cache is gevonden. Met andere woorden , dat hij niet door de libarary cache heeft moeten gaan. Je kunt deze vergelijken met de statistiek "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" , om te zien hoeveel er echt parses zijn voorgekomen:
SQL> select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;
CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
106 210 104
Monitor dit samen met de sessie cursor cache count.
--session cached cursors, voor een opgegegeven SID, vergeleken met het maximum
SQL>select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;
Als de sessie cursor cache count 'maxed out' is, session_cursor_cache_hits is vergeleken met alle parses en je vermoed dat de applicatie dezelfde queries herhaaldelijk voor parsen aanbiedt, dan kan het verhogen van de SESSION_CURSOR_CACHE_COUNT met latch contentie een beetje verlichting geven voor performance. Indien de applicatie niet herhaaldelijk dezelfde queries voor parsen aanbiedt, dan zal de session_cursor_cache_hits laag zijn en de sessie cursor cache count 'maxed out' zijn, maar caching cursors door sessies zal niet helpen. Als je applicatie bijvoorbeeld veel SQL gebruikt dat niet geshared kan worden, dan zal het verhogen van deze parameter niet helpen.
|
