Performance. Blijkt in praktijk vaak slecht kwantificeerbaar, het is erg gevoelig voor subjectiviteit en het kost menig project de kop.
Presentatie Daniel Fink tijdens RedDatabase Symposium:

Effective performance for Developers and Users

Door Toine van Beckhoven

Performance. Een veel gehoord (Engels) woord. Een Nederlandse vertaling hoor je zelden. Iedereen heeft wel een idee wat ermee bedoeld wordt, maar het blijkt in praktijk vaak slecht kwantificeerbaar, het is erg gevoelig voor subjectiviteit en het kost menig project de kop. Met grote regelmaat voldoen opgeleverde applicaties direct of na verloop van tijd niet aan de snelheidsverwachtingen: OLTP schermen die traag zijn, rapporten die te lang duren, data loads die een nachtelijk window uitlopen, te lange back-up tijden, te lange restore tijden, processen die zo lang lopen dat ze elkaar in de weg gaan zitten.

Allemaal herkenbare problemen waar niet alleen hele grote applicaties, maar ook kleinere applicaties mee kampen. En dan wordt veelal te laat gedacht aan performance tuning. In elke fase van een applicatie levenscyclus kan wel iets aan de performance gedaan worden, maar des te eerder in het ontwikkelproces tuning mee wordt genomen in het ontwerp, des te meer kans van slagen de applicatie heeft. Dit is een open deur die niets nieuws weergeeft en daarom is het des te opmerkelijk dat tuning nog zo vaak een sluitpost is. Minstens een van de redenen die genoemd kan worden is dat de ontwikkeling van een applicatie gedaan wordt door ontwikkelaars. En het gros der ontwikkelaars heeft nauwelijks kennis van de instrumenten die performance beïnvloeden en de middelen om een performance probleem mee op te sporen.
De afstand tussen de ontwikkelomgeving en de database is de laatste jaren nog verder gegroeid en daarmee de afstand tussen ontwikkelaar en DBA/database specialist. Niet elk project heeft ook een database specialist die mee kan kijken. Het komt voor dat in een team van 15 ontwerpers, ontwikkelaars en projectleiders geen enkel teamlid gedetailleerde kennis heeft van de database waar de applicatie bovenop gaat draaien. Ergens tegen het eind van het project, als de performance nog flink tegenvalt, wordt in allerijl een DBA opgeroepen om de applicatie tot een acceptabel niveau te versnellen. Het lijkt vaak wel alsof de database als een noodzakelijk kwaad wordt beschouwd waar je zo min mogelijk in moet doen behalve gegevens in opslaan. Helaas zijn veel gekochte applicaties -ook heel grote- op deze leest gestoeld. Daar zijn dan ook erg vaak performance problemen mee. Database onafhankelijkheid is voer voor (performance) problemen.

Daniel Fink, een Amerikaan die tijdens het RedDatabase Symposium in Den Haag een uitgebreide presentatie gaf, startte zijn betoog ook met een aantal statements die overeenkomen met de voorgaande constatering: performance is niet het exclusieve terrein van de DBA en het gat tussen ontwikkelaar en DBA groeit. Dat was dan ook de context van zijn presentatie ‘(Introduction to) Effective Performance Tuning for Developers and Users’. In de vorige OGh Visie is verslag gedaan van de presentatie van Tapio Lahdenmäki over Index Design, in dit nummer staat de presentatie van Daniel Fink centraal. In dit artikel besteed ik aandacht aan de instrumenten om bij performance-uitdagingen diagnoses te stellen en code te profileren, aan de cost based optimizer en wat je als ontwikkelaar kunt doen om een applicatie diagnose-’vriendelijk’ te maken.

De uitdaging

‘Winners know why they win’. Iemand die snel de juiste diagnoses stelt, bezit veelal de sleutel tot een succesvol diagnose proces: solide kennis van de technologie en gebruiken van de juiste instrumenten. Het stellen van de juiste diagnose voor het vinden van de ‘root cause’ van een performance probleem is vaak niet eenvoudig, zeker niet omdat er vaak veel symptomen zijn die veroorzaakt worden door een kettingreactie geïnitieerd door de root cause zelf.
De grote uitdaging is derhalve het vinden van de root cause(s) van een performance probleem. Maar een even belangrijke uitdaging is vervolgens het implementeren van de juiste oplossing. Dat is soms evident, maar vaak ook (zeker bij een third party applicatie) helemaal niet zo simpel, vooral niet als er geen controle is over de SQL statements of stored procedures die worden uitgevoerd.
In Oracle versie ‘6’ was er heel weinig houvast voor het vinden van een root cause. In versie 7 werd de Wait interface geïntroduceerd, maar nog nauwelijks gebruikt of bekend bij het grote publiek. Het was de tijd dat de aandacht werd gericht op de buffer cache hit ratio (bchr): die moest vooral heel erg hoog zijn, bijvoorbeeld 99%. Als de bchr 70% was dan had je een performance probleem. Inmiddels weten we beter: de database met 99% buffer cache kan met grotere performance problemen kampen dan de database met 70% bchr. Connor McDonald schreef eens een PL/SQL routine die je elke gewenste bchr kon geven die je maar wenste (http://www.oracledba.co.uk/tips/choose.htm). Niet alleen de bchr is achterhaald, in wezen is tunen op basis van bijna elke ratio te vergelijken met het rijden in een auto met een blinddoek om: het geeft heel weinig houvast.
Recentere tuning methodes richten zich veel meer op ‘Tijd’ (DBTime), op resource profielen en op basis van de Active Session History (ASH). In deze OGh Visie staat tevens een verslag van de presentatie van Graham Wood, die gericht was op DBTime en ASH. Het is op zijn minst opmerkelijk dat in een veelgeprezen boek van Oracle Press ‘Oracle Database 10g Performance Tuning Tips & Techniques’ enerzijds met geen enkel woord gerept wordt over ASH en DBTime, maar nog altijd de buffer cache hit ratio als zeer belangrijk wordt genoemd! Het lijkt wel een Oracle 7 Tuning boek met enkele 10g features daarin vermeld, maar de belangrijkste zijn vergeten. Daniel Fink gaf het ook aan: ‘Performance is niet: dashboards, ratio’s en kunstmatige metrics; performance gaat alleen over Tijd, een concept dat iedereen snapt’.
Performance gaat ook over activiteit en ‘(work)load’. Activiteit is de hoeveelheid werk die gebruikers van het systeem vragen en de load is de hoeveelheid systeemresources nodig om het gevraagde werk af te handelen. Recent heb ik bij een klant de dagelijkse ETL van het OLTP systeem naar het OLAP systeem met 30% in tijd verkort door een gelijkblijvende activiteit (het aanmaken van een drietal aggregatietabellen) met een sterk verminderde load (gebruik makend van Fast refreshable materialized views). Qua logische activiteit verandert er niets, alleen gebeurt het een stuk efficiënter.
Er wordt wel eens gezegd: de snelste query is degene die niet uitgevoerd wordt en zo is het maar net: alle niet noodzakelijke load moet van een systeem gemeden worden, want dat biedt ruimte aan andere load die wel noodzakelijk is. Een Oracle feature als partitioning vermindert ook de load die nodig is om hetzelfde resultaat (activiteit) te bereiken: in plaats van FULL table scans worden indien juist opgezet hooguit FULL partition scans gedaan.

Het stellen van de diagnose

Als een applicatie niet de gewenste performance biedt, staan er verschillende diagnose-instrumenten ter beschikking. Van SQL interfaces (te bekijken met SQL*Plus of een IDE als TOAD of SQL Developer) tot profilers en Enterprise manager. Een eerste indruk kan al verkregen worden via de wait interface van Oracle: een set (G)V$-views die weergeeft waar een sessie zijn tijd aan het spenderen is: met actief wachten (met informatie over welk event), inactief wachten of CPU verbruikend (al is het laatste strikt genomen niet te zien in de wait interface, maar wel afleidbaar). V$SESSION_WAIT gejoined met V$SESSION is dan in alle versies (vanaf V7) een goed startpunt, al zit sinds 10g in V$SESSION alleen ook al voldoende informatie. 10g heeft ook een V$SESSION_WAIT_HISTORY toegevoegd met daarin de 10 meest recente wait events van elke ingelogde sessie. De Nederlander Anjo Kolk heeft mede een white paper geschreven, genaamd ‘YAPP’ (Yet Another Performance Profiling’ method), die gestoeld is op het gebruik van de wait interface. Deze white paper wordt nog vaak aangehaald – lees het boek ‘Oracle Insights, Tales of The Oak Table’ maar eens - en biedt nog steeds goede aanknopingspunten om een performanceprobleem gestructureerd te analyseren. De meeste wait events hebben ook nog maximaal 3 parameters, waarvan de waardes in V$SESSION(_WAIT) getoond worden. Zo toont V$SESSION(_WAIT) voor het event ‘db file scattered read’ (het lezen van meerdere Oracle blocks in één I/O) zowel de file, het block id en het aantal blocks dat gelezen wordt vanaf het genoemde block id. Dat is gedetailleerde informatie! Hetzelfde geldt voor latch waits, locks (en queues) en andere events. Oracle is wat dat aangaat zeer rijk geïnstrumenteerd, zoals al eerder gezegd.
Niet alleen de wait interface biedt informatie, ook de zeer rijk bijgehouden statistieken op allerlei niveaus zijn van grote waarde en dan met name als ze bekeken worden vanuit het perspectief van een ‘delta’: hoe hoog zijn de statistieken opgelopen sinds een vorige meting? Tom Kyte heeft een relatief simpele, maar doeltreffende PL/SQL package geschreven, genaamd ‘RunStats’ waarmee je snel de impact op diverse statistieken kunt zien als een gegeven statement gedraaid wordt. In zijn boeken gebruikt hij deze ook regelmatig om het effect van verschillende implementaties van dezelfde oplossing te tonen: de beste is vaak degene die de minste ‘resources’ vergt. Voorbeelden van belangrijke statistieken, genoemd door Fink, zijn:

  • CPU used by this session - in centiseconden weergegeven hoeveelheid verbruikte CPU tijd
  • Execute count - telling van aantal SQL statements uitgevoerd
  • Physical reads - totaal aantal Oracle blocks dat echt van disk moest worden gelezen
  • Session logical reads - aantal Oracle blocks dat gelezen is, ongeacht of een Block in de buffer cache stond of van disk moest worden gelezen
  • Parse count (total)/(hard) - totaal aantal parses en totaal aantal hard parses. Het aantal hard parses moet een fractie zijn van het totaal aantal parses
  • Sorts (memory)/(disk) - aantal sorteringen in memory en op disk. Bij voorkeur moeten sorteringen in geheugen plaatsvinden
De statistieken worden bijgehouden op sessie- (V$SESSSTAT, V$SESS_IO) en op systeemniveau (V$SYSSTAT). Net als voor de wait interface geldt ook voor de statistieken dat degene op sessie niveau het meest veelzeggend zijn (de scope is beter): systeem waits en statistieken zijn totalen over alle sessies en dat maakt individuele performance problemen onzichtbaar. Kijk op sessie niveau!
De meest gedetailleerde informatie over een problematische sessie is te verkrijgen door een Extended SQL Trace. ‘Optimizing Oracle Performance’ van Cary Millsap is een uitstekend boek over dit onderwerp. Extended SQL Trace levert een trace file op met daarin nagenoeg op volgorde (er zijn wat geniepige haken en ogen aan de volgorde) alle activiteit die een sessie gedaan heeft. In zo’n trace file valt dan bijvoorbeeld snel op als de sessie voor een SQL statement extreem veel single block reads moest doen. Het is ook de enige manier om van een sessie alle bind variable waardes te zien (zie noot).

Noot: bind variables zijn fantastisch voor het delen van SQL en dus het vermijden van teveel dure (CPU) hard parses, maar zijn daarentegen een probleem voor de optimizer. In een huidig Oracle 10g Siebel project (OLTP systeem) zien we hele wisselende responstijden voor bijvoorbeeld een query die alle personen moet tonen woonachtig in een door de gebruiker op te geven plaats (City). Oracle doet vanf versie 9i aan zogeheten ‘bind variable peeking’: elke keer dat het statement niet in de shared SQL area staat en dus geparsed (hard) moet worden, zal het statement geoptimaliseerd worden volgens de waarde voor City die toevallig door die gebruiker is ingegeven. Nu scheelt het nogal of de plaats Parijs wordt gevraagd of de plaats Goirle. De betreffende Siebel query zou in het eerste geval heel anders geoptimaliseerd worden dan in het tweede (indien er histogrammen berekend zijn). Maar de gebruikers die vlak erna dezelfde query uitvoeren en een volledig andere waarde voor City kiezen, zijn veroordeeld tot het executieplan van de ‘eerste’ gebruiker. Oracle 11g maakt voor dit probleem een sprong voorwaarts door een feature die ‘Adaptive Cursor Sharing’ heet: het monitort executies van elk statement met bind variabelen en bepaalt aan de hand van selectiviteit van de bind variabelen of een statement geshared kan worden (soft parse) of dat het opnieuw geparsed (hard) moet worden als child cursor , leidend tot een hopelijk beter executieplan voor de gegeven bind variabelen. Beide plannen blijven dan naast elkaar als shared SQL in de SGA staan.

Een Extended SQL trace file is erg lastig te lezen en daarom levert Oracle bij elke versie ‘tkprof’ (Transient Kernel Profiler). Tkprof profileert een trace file en maakt er een leesbaarder rapport van met allerlei sorteermogelijkheden en opties. Fink gaf aan van al die opties er consistent drie te nemen:
Tkprof ontw_ora_453.trc out.txt Waits=yes sys=no sort=exeela,fchela, prsela
Dit genereert een rapport met de naam ‘out.txt’ voor tracefile ‘ontw_ora_453.trc’ met wait informatie erin, geen recursive sql (systeem gegenereerde SQL) en de statements gesorteerd op elapsed time voor executes, fetches en parses.
Naast tkprof zijn er nog 3rd party producten die van een Extended trace file een leesbaar rapport maken. De Hotsos profiler (gebaseerd op eerder genoemd boek van Cary Millsap) is mogelijk de beste en bekendste, maar zeker niet gratis. Een handige Russische programmeur heeft wel een gratis kloon van het commerciële Hotsos profiler gemaakt, genaamd ‘orasrp’ (http://www.oracledba.ru/orasrp/). Deze tool maakt een heel mooi HTML rapport van de trace file, met de bind variabelen en wait events per statement. Dat het lezen en parsen van een ruwe tracefile niet evident is blijkt zeer goed uit genoemd boek van Cary Millsap, een profiling tool is dan extreem waardevol. Ze tonen ook aan dat event ‘SQL*Net message from client’ ten onrechte altijd als een onbelangrijk event wordt beschouwd: zodra dit event significant bijdraagt aan de responstijd is het niet onbelangrijk. Een gebruiker die even niets doet is namelijk niet de enige reden voor het optreden van dit event!

Cost based optimizer

Menigmaal heb ik mensen horen zeggen dat ze de Cost Based Optimizer van Oracle waardeloos vinden: het doet niet wat het moet doen. Maar dit heeft voor een groot deel te maken met onbegrip. Voor de Cost Based Optimizer is het allereerst noodzakelijk dat de objectstatistieken accuraat genoeg zijn: table statistics, column statistics, index statistics en partition statistics. En aanbevelenswaardig sinds Oracle 9 zijn ook de system statistics (wat zijn de ‘kosten’ van single Block en multiblock reads en hoe snel zijn de CPU’s). Door de optimizer deze informatie over het ‘systeem’ en de objecten te geven (via de built-in package DBMS_STATS), kan het al veel beter zijn werk doen. Erg vaak wordt dit vergeten of zijn maar een deel van de statistieken op peil. Lastige situaties zijn tabellen die volatile zijn: waar continue inserts en deletes op plaatsvinden. Maar dan nog geldt ook: al zijn de statistieken accuraat genoeg, er zijn situaties waar een optimizer niet mee om kan gaan. Één van die situaties is bijvoorbeeld kolomafhankelijkheid. Een kolom ‘geslacht’ en een kolom ‘aanhef’ zijn niet geheel onafhankelijk: een persoon van het mannelijk geslacht zal hopelijk niet gauw ‘mevrouw’ genoemd worden of ‘Jonkvrouwe’. De optimizer tot en met Oracle 10 echter heeft van zulke afhankelijkheden geen weet. Vanaf Oracle 11 zijn ook zulke statistieken mogelijk en dat is zeer goed nieuws: door de definitie van kolomgroepen kunnen statistieken voor combinaties van kolommen berekend worden. Voor wie echt wil snappen wat de Cost Based Optimizer allemaal in aanmerking neemt bij het bepalen van een executieplan, kan ik het boek van Jonathan Lewis aanbevelen: ‘Cost based Fundamentals’, tezamen met een serie white papers van Wolfgang Breitling (zoals ‘Fallacies of the Cost Based Optimizer’). Beide hebben een goede website met veel informatie. Genoemde boeken en white papers behoren tot mijn favoriete leesvoer ten aanzien van performance tuning en niet voor niets werden de meeste ook genoemd door Fink. De Cost Based Optimizer is veel intelligenter dan de Rule Based Optimizer. Maar het moet gezegd dat er soms teveel intelligentie in wordt gestopt. Bepaalde hidden Oracle parameters willen dan wel eens uitkomst bieden.

Diagnose vriendelijke programma’s en gouden regels

Waar kan een ontwikkelaar nu rekening mee houden bij zijn applicaties (in de ruimste zin van het woord)? Instrumenteren met behulp van DBMS_APPLICATION_INFO is erg prettig voor een DBA die jouw applicatie moet analyseren of tracen. De module, action en client_info waardes zijn rechtstreeks te zien in de V$SESSION view. Als een architectuur gekozen kan worden, is de meest diagnose vriendelijke die waarbij SQL tracing mogelijk is. Applicatie connection pooling maakt tracen zeer lastig tot onmogelijk, ondervind ik momenteel aan den lijve bij een (IBM) FileNet applicatie bovenop Oracle: een gebruikerssessie is daar niet gelijk aan een Oracle sessie. Vind dan maar eens de achtereenvolgens uitgevoerde statements van dezelfde gebruiker! Die staan helaas verspreid over meerdere trace files en tussen statements van andere gebruikers. En helaas is er geen identificatie mogelijk omdat alle gebruikers de database benaderen via dezelfde database user. Daar is aanzienlijk minder eer te behalen dan in een specifiek voor Oracle geschreven applicatie.

Enkele gouden regels voor schaalbare en snelle applicaties zijn:

  • Minimaliseer het aantal Logical I/O’s! Voer geen SQL statements uit die onnodig zijn en haal niet meer records op dan nodig (open deuren die vaak dicht blijven)
  • Elimineer alle zinloze workload van een systeem; spreid zinvolle workload waar mogelijk. Dit betekent ook: archiveren van oude data
  • Tune een applicatie voordat gedacht wordt aan extra of snellere CPU’s, meer memory e.d.: er zijn zelfs voorbeelden genoeg van applicaties die trager werden na een CPU upgrade. In elk systeem kan vaak meer dan de helft van het onnodige werk verwijderd worden.
  • Maak gebruik van Oracle features als partitioning, constraint declaraties, niet te veel maar ook niet te weinig indexen, index-organized tables, DBMS_STATS: al deze elimineren onnodig werk dat de Oracle kernel moet uitvoeren en maken activiteit sneller met minder workload.

Performance tuning is niet makkelijk, maar er zijn wel een paar hele eenvoudige uitgangspunten. En Oracle biedt veel diagnose mogelijkheden. Daniel Fink’s bedoeling was de ontwikkelaar bewust maken van zijn rol in de tuning van zijn applicaties. Met dit verslag over deze presentatie, aangevuld met eigen ervaringen en materiaal uit een reeks aanbevelenswaardige boeken, hoop ik een aanzet te geven tot eerste verbeteringen en het opwekken van nieuwsgierigheid.

Toine van Beckhoven (www.jom-it.nl) is zelfstandig en allround Oracle specialist. Oracle Performance Tuning en PL/SQL hebben zijn grootste interesse.