• Home
• Over ons
• Onze diensten
• Werken bij Orcado
• Kennis
  • Blogs
  • Whitepapers
  • Referenties
• Contact

Ik ga snel weer eens een blog schrijven zou ik lang geleden wel eens gezegd kunnen hebben. Dat zou dan inconsistent zijn met wat er hier op de site te zien is geweest.
Inconsistent zien we als een vies woord, het is een soort onwaarheid. Het ene zegt dit en het andere dat, er is er een die liegt!

In dit specifieke geval zou het meer een onverstandige belofte zijn, maar het had ook een technische reden kunnen hebben. Deze blog zou ik misschien een paar maanden geleden al geschreven kunnen hebben en vanwege een defecte communicatie nooit doorgekomen zijn. Wat je ziet is dan niet in sync met wat er geschreven is. Zou dat gebeurd zijn en de communicatie was hersteld dan zou je ineens een nieuwe blogpost met een datum ver in het verleden kunnen zien. Ziet er niet heel betrouwbaar uit, maar is in dit geval ook niet ernstig.

Dit soort situaties komen steeds vaker voor in systemen. Niet in de tijd van maanden zoals in dit (overigens zuiver hypothetische) voorbeeld, maar eerder in seconden of minuten. Dit heeft te maken met de meer gedistribueerde systemen die tegenwoordig ontwikkeld worden. De achtergrond zit hem in het zogenaamde ‘twee generaals probleem‘.
Het probleem is dat de generaals geen aanval kunnen coördineren als ze niet zeker zijn dat hun communicatie snel en volledig is. De generaals zijn in ons vakgebied computers en de communicatie loopt over een netwerk. Wil je zorgen dat alle betrokken systemen op elk punt in tijd hetzelfde laten zien dan is de consequentie wachten. Binnen een fysieke machine kan dat en dat is ook wat we gewend zijn te doen. Over een netwerk kan er verlies zijn en met gedistribueerde systemen als microservices kun je wel eens niet weten hoe vaak een stuk software waar draait.
Het gevolg is inconsistentie. Bah, ondanks de uitleg toch nog steeds niet leuk.

Je bent bijvoorbeeld een huisje aan het boeken voor de vakantie en even later blijkt dat de periode toch niet beschikbaar was. Van een systeem vinden we dit al snel een defect, maar in de buitenwereld speelt hetzelfde en zien we het als normaal. In een restaurant bestel je de dagschotel tonijn en even later komt de ober terug met de mededeling dat zojuist het laatste stuk tonijn gebruikt is en dat het toch iets anders uitzoeken wordt.

De vroegere systemen hadden als nadeel dat als het uitviel je niets had of je moest hele dure oplossingen voor failover en disaster recovery inrichten. Ook werd het heel snel heel duur als je op wilde schalen. Gedistribueerde systemen garanderen betere availability, maar er kunnen zogenaamde partitions ontstaan als er een een onderdeel niet bereikbaar is. Deze partitions zijn dan onderling niet meer consistent. We leveren dus consistentie in voor beschikbaarheid en in het kielzog daarbij winnen we snelheid en schaalbaarheid.

Om het voordeel te pakken en zo min mogelijk last te hebben van de consequenties is het zaak om gedistribueerde systemen slim op te zetten. Enerzijds vanuit de functionele hoek. Hoe ga je om met de verschillende inconsistente situaties? Hier is het zaak om zoveel mogelijk naar de buitenwereld te ontwerpen en vergelijkbare afhandelingen voor het systeem te bedenken.
Daarnaast moet de inconsistentie tijdelijk blijven, uiteindelijk moet alles weer kloppen. De oplossing daarvoor is werken met een patroon als ‘event sourcing’. Als je alle handelingen in de tijd wegschrijft in een centrale log dan is daaruit altijd weer te bepalen hoe elke situatie uitpakt ook al komt er iets te laat door. Zo kunnen de systemen op de achtergrond zelfherstellend zijn.
Stel je bijvoorbeeld de situatie van een bibliotheek voor. Je kunt in een database bijhouden welk boek aan wie uitgeleend is, maar als het terugbrengen eens niet goed verwerkt is blijft het boek uitgeleend staan. Als je het dan opnieuw uitleent staat de nieuwe klant geregistreerd bij het boek. Komt daarna de actie van het terugbrengen door dan kun je niet meer goed zien of het te laat ingeleverd is. Werken met een log als basis geeft je dan de mogelijkheid om elk punt in de tijd te reconstrueren en af te handelen.

De eigenlijke persistentie (opslag) ligt bij event sourcing niet meer zoals gebruikelijk in een database, maar in deze log. Dit levert weer andere voordelen. Dat is wellicht iets voor een andere keer, maar het kan even duren voor dat doorkomt.

Recently I was investigating on performance issues with a java application. Due to object relational mapping this application generated a lot of varying sql statements which often had plan issues.
I wanted to replay some of these statements with variatons to find a generic solution to this.
The SQL monitor reports however had a lot of bind variables of the timestamp type. In the reports these were represented in a value like this: 7874051A0B1F01
At first I just guessed the values but at some time I really needed to know if they were querying a week or a year of data.

I decided to reverse engineer the format with a testscript which generated SQL monitor reports using a range of timestamps. From the reports I digested the format and made a SQL query to translate them:

[code language=”sql”]
select to_timestamp
( to_char (to_number (substr (:input, 1, 2), ‘xx’) – 100, ‘fm00’)
|| to_char (to_number (substr (:input, 3, 2), ‘xx’) – 100, ‘fm00’)
|| to_char (to_number (substr (:input, 5, 2), ‘xx’), ‘fm00’)
|| to_char (to_number (substr (:input, 7, 2), ‘xx’), ‘fm00’)
|| to_char (to_number (substr (:input, 9, 2), ‘xx’) – 1, ‘fm00’)
|| to_char (to_number (substr (:input, 11, 2), ‘xx’) – 1, ‘fm00’)
|| to_char (to_number (substr (:input, 13, 2), ‘xx’) – 1, ‘fm00’)
|| to_char (nvl (to_number (substr (:input, 15, 8), ‘xxxxxxxx’), 0), ‘fm000000000’)
, ‘yyyymmddhh24missff’
)
from dual
[/code]

The above timestamp representation translates to 26-5-2016 10:30:00,000000000.
For timestamps with timezones or ones with a different precision the formula will probably be similar.

Last week I was called in on a performance issue regarding a query on a datawarehouse that took about 4 hours. When looking at the execution plan in the excellent sql monitor I noticed a big full table scan yielding a whopping 125 million rows. Before I could turn around to ask the application team why this table was not partitioned my eyes were drawn to the cpu usage and wait bars in the plan. The buffer sort on the result of the full table scan was taking far more time than the scan itself!

I have seen this phenomenon a long time ago and I wouldn’t have thought this issue would still be around in the 11g r2 database.

The culprit in this is the cartesian join. The query contained a cartesian join to a calendar table which was filtered on a single day. The result in this case is that the 125 million rows are put in a sort area for the buffer sort. The sort area is way to small to hold these rows and so we end up doing this on disk using temp.

The approach the optimizer took is probably to prevent the full table to be repeated in case the cardinality estimate on the calendar was off, but the result is pretty dramatic. When I forced the use of a nested loops join the buffer sort was gone. What is left is that in 11g r2 the optimizer apparently still doesn’t have the capability to forsee the cost of the buffer sort disk operation.

For now there doesn’t seem to be an elegant way to work around this, maybe the adaptive query optimisation of 12c will resolve this in a better way.

Heel af en toe krijgt een 3rd party applicatie waar wij hier beheer voor doen een fout item via een xml message binnen.
Hiervoor biedt de applicatie een beheerscherm waar de xml aangepast kan worden en daarna opnieuw aangeboden kan worden.
Dat voldeed prima tot we er op een dag ineens 90 foute berichten kregen.

Een blik op de onderliggende Oracle database liet zien dat de berichten met foutmelding in een tabel opgeslagen worden.
Zowel de errorstack als de message staan in een clob veld.
Wat we wilden automatiseren was een node uit de xml knippen waar het betreffende item uit de foutmelding in voor kwam.

Het wordt dus een update statement op de betreffende message tabel waarbij we de payload aanpassen:

Eerst identificeren we de juiste berichten in de where clause. We weten het messagetype en de foutmelding:

Het vinden van het itemnummer in de foutmelding kan met ouderwets knippen en plakken:

Eventueel zou bovenstaande ook met een reguliere expressie kunnen.

Om zeker te zijn dat het gevallen van dit specifieke probleem zijn controleren we of het itemnummer in de xml op de juiste plaats voorkomt:

Eerst wordt hier de clob in een xmltype gezet: xmltype (lg.payload)
Vervolgens wordt een xpath expressie gemaakt met het item uit de foutmelding:

‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”<itemnummer>”]’
Hiermee wordt gecontroleerd of het betreffende nummer op de juiste plaats in de xml voorkomt.
Deze waarde wordt dan met extractvalue eruit geplukt.

Nu hebben we de juiste rijen te pakken en komt het knippen in de xml:

Eerst weer converteren naar xmltype: xmltype (lg.payload)
Dan met een iets andere xpath expressie op zoek naar de node waarin het item voorkomt (let op de plaats van de blokhaken!):
‘/Message/Payload/Forecast/ItemDemand[itemNo=”<itemnummer>”]’
Met deletexml wordt de betreffende node eruit geknipt.
Wat rest is de xmltype weer terug in een clob te veranderen, dit kan met de methode getclobval van het xmltype: .getclobval ()

Het eindresultaat:

In de applicatie konden we nu alle berichten opnieuw aanbieden en ze waren verwerkt.
Deze generieke oplossing kan vervolgens voor alle toekomstige gevallen gebruikt worden.
Dit bespaart tijd en is minder foutgevoelig dan het handmatige aanpassen.

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.

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:

In het betreffende statement stond een conditie in ‘–‘ stijl commentaar:

Het coe script maakt hier dit van:

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.

Bij het analyseren van een ongunstige plankeuze door de Oracle optimizer liep ik laatst tegen een situatie aan waar er een cardinaliteit van één in het plan stond waar er tienduizenden verwacht waren.
De index statistieken waren redelijk in lijn met het verwachte aantal rijen, waarom dan toch de misser in het plan?

Het euvel bleek in een histogram te huizen. De statistieken werden in de nacht berekend en er kwamen op de dag nieuwe records in de tabel die met de betreffende query weer opgehaald werden voor verwerking aan de hand van een jobid.
Het (frequency) histogram bevatte bijvoorbeeld de aantallen records voor jobid 100 t/m 120 en die dag werd job 121 gedraaid.
De optimizer ziet bij een bind peek in de hard parse het nieuwe jobid en denkt dat deze niet in de tabel voorkomt omdat deze boven de maximale high_value in het histogram ligt.
In de verdere berekening wordt hierdoor met één gerekend wat het ongunstige plan veroorzaakt heeft.

Hoewel dit gedrag redelijk natuurlijk volgt uit de informatie uit de statistieken vraag ik me toch af of er wel een situatie is waar dit positief uitwerkt. Er moeten wel heel vaak niet bestaande gegevens opgevraagd worden wil een plan dat hiervan uitgaat het gemiddeld winnen van een plan dat uit gaat van wel bestaande rijen.

Hier komt de sidebar