Een eenvoudige methode om de performance van batches in Oracle systemen te verbeteren is het slim omgaan met de timing en volgorde van uitvoering. De peformance van jobs is vaak erg afhankelijk van de hoeveelheid fysieke schijfacties die uitgevoerd moeten worden wat weer volgt uit de inhoud van de buffer cache.
Vooral bij gepartitioneerde tabellen met lokale indexen is het handig om te zorgen dat partities op volgorde worden behandeld zodat de index blokken in cache maximaal hergebruikt worden.
Als er bijvoorbeeld een batch moet draaien voor item 100 t/m 120 voor elke dag van de afgelopen week op data die voornamelijk op datum is gepartitioneerd dan geeft is het het beste om op volgorde van dagen te schedulen.
Bij hash partities wordt het lastiger omdat daarbij in principe niet te achterhalen is van welke partities de job gebruik gaat maken.
Ik zeg in principe omdat de ongedocumenteerde functie tbl$or$idx$part$num daarbij kan helpen. Als je deze functie aanroept met de tabelnaam en de partition key dan levert dit de hashpartitie op waar de key in terecht komt.
Stel bijvoorbeeld dat de meeste data gepartitioneerd is op hash(item) dan kun je de batchaanroepen sorteren op tbl$or$idx$part$num(<tabelnaam>,0,0,0,item).
In specifieke situaties heb ik winsten in doorlooptijden gezien van tientallen procenten met alleen veranderen van de volgorde van aanroepen. Let wel dat ook bij dit soort aanpassingen een test vaak op zijn plaats is. Met name bij parallele uitvoering kunnen vanwege de toegenomen activiteit in dezelfde blokken locking of buffer cache waits optreden.
Signature mismatch bij het zetten van een custom SQL profile
Voor het hard vastzetten van een executieplan zijn er in Oracle verschillende methoden beschikbaar.
De meest toekomstvaste en stabiele voor 11g is nu het custom SQL profile. Dit is een variant van het door de tuning advisor aangemaakte SQL profile. Waar de tuning advisor een set hints met relatieve aanpassingen op statistieken oplevert geeft het custom profile een set directieven die een specifiek plan afdwingen. Dit is vergelijkbaar met een outline.
Oracle support levert hiervoor het script coe_xfr_sql_profile.sql (DOC ID 1487302.1), welke opgenomen is in de SQLT tool (DOC ID 215187.1).
Laatst liep ik hiermee tegen een probleem aan. Het script maakte een profile aan met de juiste hints, maar het profile werd niet gebruikt voor het betreffende statement.
Na onderzoek bleek dat de signature van het profile in DBA_SQL_PROFILES afweek van de signature zoals deze in V$SQL stond.
Hoe kon dit gebeuren? De profile was immers gemaakt vanuit hetzelfde SQL ID.
Het probleem zat hem in een truc die in het coe script gebruikt wordt om lange regels op te knippen:
| :sql_text := REPLACE(:sql_text, ‘)’, ‘)’||CHR(10)); :sql_text := REPLACE(:sql_text, ‘,’, ‘,’||CHR(10)); |
In het betreffende statement stond een conditie in ‘–‘ stijl commentaar:
| — AND to_char(a.kolom,’YYYY-MM-DD’) = b.kolom |
Het coe script maakt hier dit van:
| — AND to_char(a.kolom, ‘YYYY-MM-DD’) = b.kolom |
Hierin worden de 2e en 3e regel niet meer als commentaar gezien en verandert dus de signature van het statement.
Om dit te omzeilen heb ik het door het coe script gegenereerde script handmatig aangepast en de signature was weer gelijk.
Het profile werd opgepakt en het plan veranderde in het gewenste.
Een signature mismatch valt op doordat de aangemaakte profile niet in het ‘plan control’ tabblad verschijn in de enterprise manager en niet te zien is in de notes bij het opvragen van een dbms_xplan.display_cursor.
Het is de eerste vraag die ik hoor na het oplossen van een Oracle optimizer performance issue met grote impact.
De vraag is eigenlijk: hoe groot is het risico dat dit nog een keer gebeurt?
Het antwoord op de originele vraag is altijd: jazeker, op de tweede meestal: het risico is zeker aanwezig.
In het vervolg van het gesprek wordt de database software en zijn maker vaak aangewezen als onbetrouwbaar.
Dit is het gevolg van de onbekendheid met de database en hoe hij probeert de systemen op zijn best tot dienst te zijn.
Dat is altijd een balans tussen de effort voor het bepalen wat de beste aanpak voor een opdracht is en de effort voor het uitvoeren van die opdracht. Dit zonder te weten wat de volgende vraag aan de database is en binnen de kaders van de gestelde configuratie.
In deze laatste zin zit hem de crux.
De configuratie is meestal redelijk default en menselijke kennis van het systeem en zijn gebruik worden niet ingezet om de database te helpen.
Als een database ingericht wordt voor OLTP dan is planstabiliteit belangrijk, bij BI uitgebreide statistieken en een royale tijd voor planbepaling. Is er sprake van groei in de data? Worden tabellen gebruikt voor tijdelijke data? Verloopt het gebruik van het systeem in de dag/week (online/batch)?
Zo zijn er vele relevante vragen die kunnen helpen bij het optimaliseren van van de inrichting van de database.
Hiervoor zijn legio instrumenten, veelgebruikt zijn configuratie van databaseparameters en timing en configuratie van statistiekenbepaling. Maar ook outlines, baselines, system statistics, handmatige statistics, multi column histogrammen, hints, sql patches etc etc.
De boodschap is: denk met alle partijen goed na over je inrichting en hereik deze met enige regelmaat aan het actuele gebruik en je voorkomt het overgrote deel van de performance incidenten.
Hierbij is het belangrijk dat alle betrokken partijen hun input leveren, de dba alleen weet niet alles over de applicaties en hun gebruik en ook niet altijd alles over de belasting en ruimte in de infrastructuur.
Een goede databaseperformance met minimale incidenten is niet vanzelfsprekend zonder configuratie en beheer op maat.
Hier komt de sidebar