Dit verslag van Toine van Beckhoven belicht ASH (Active Session History) en het raakvlak met DBTime...

Presentatie Graham Wood tijdens themabijeenkomst OGh

DBTime en Active Session History

Door Toine van Beckhoven

Tijdens zijn presentatie op de OGh themabijeenkomst besteedde Graham Wood aandacht aan enkele van de revolutionaire nieuwe tuning mogelijkheden van Oracle 10g en 11g. In het artikel over de presentatie van Daniel Fink zijn de diagnosemogelijkheden besproken van in feite Oracle database versies 7 t/m 11g, maar Graham Wood liet zien waarin Oracle 10g en 11g de vorige versies laten verbleken. Drie zeer belangrijke termen passeerden deze avond de revue: DBTime, ADDM (Automatic Database Diagnostic Monitor) en ASH (Active Session History). In dit verslag wil ik met name ASH belichten en het raakvlak met DBTime.

DBTime

DBTime is een nieuw concept vanaf Oracle 10g, althans nieuw in de zin dat op diverse plaatsen in de kernel nieuwe instrumenten zijn ingebouwd om actieve tijd gespendeerd ‘binnen de database’ te tonen. Iedereen snapt het begrip ‘Tijd’. Niets is een begrijpelijker meetinstrument om responstijd en performance te meten en weer te geven dan verstreken tijd. Oracle kan prima meten hoe lang een sessie het RDBMS actief bezighoudt. Het weet weliswaar ook wanneer de gebruiker de database niet actief bezighoudt, maar het kan niet vertellen of in de perceptie van de gebruiker de applicatie nog bezig is (zonder de database te belasten oftewel bezig is met clientside code executie) of dat de gebruiker daadwerkelijk geen opdracht heeft gegeven. Oracle kan dus DBTime meten als ‘Tijd gespendeerd in een database call’. Die tijd kan CPU-verbruik zijn (bijvoorbeeld parsen van een SQL statement, filteren van records als gevolg van een WHERE clause of het lezen van een Oracle block uit de buffer cache), of het kan een actief wait event zijn (bijvoorbeeld een row lock, I/O of een van de redo log Waits). Er zijn in Oracle 10 ruim 800 verschillende wait events.

Waarom is DBTime een belangrijk concept? Het geeft een goede indicatie over de performance: Als het aantal actieve gebruikers toeneemt of er worden grotere transacties uitgevoerd, dan zal DBTime toenemen. Als het I/O systeem een probleem heeft, waardoor I/O tijden toenemen, dan zal DBTime toenemen. Als de server CPU bound is of wordt, dan wachten sessies langer in de CPU run queue, wat zichtwaar is in de tijd gespendeerd in de wait events voorafgaand aan de CPU behoefte van de sessies (alhoewel kunstmatig omdat de run queue moeilijk inzichtelijk te maken is vanuit Oracle). Langere wait events betekent toenemende DBTime.

Scope Of Time-based Methods
Afbeelding 1: Scope van Oracle Tuning Methodes gebaseerd op ‘Tijd’

Waar wordt DBTime dan gebruikt in Oracle? Allereerst gebruikt ADDM (Automatic Database Diagnostic Monitor) DBTime als meetinstrument voor allerlei analyses en aanbevelingen. Het meet bijvoorbeeld de totale DBTime in een meetperiode en zet alle SQL statements daartegen af. Het geeft daarmee aan dat een zeker statement 30% van de totale DBTime heeft gekost. Verder is DBTime zichtbaar in diverse V$views, zoals V$SQL (de kolom heet echter geen dbtime, maar uit zich in ELAPSED_TIME en CPU_TIME) en een nieuwe view V$SYS_TIME_MODEL. Performance tuning aan de hand van DBTime maakt vrijwel alle problemen identificeerbaar: de scope van DBTime is groter dan van YAPP en Method R zoals te zien in Afbeelding 1.

Active Session History

Stel, uw databaseversie is 9i of ‘lager’. Er komen meldingen binnen van slechter wordende performance, die ongeveer 2 uur geleden is begonnen. Wat zijn uw opties? Als u Statspack snapshots neemt elk uur heeft u in ieder geval de mogelijkheid om de Statspack rapporten van de laatste 2 uur erbij te nemen. Statspack rapporten vergen enige ervaring om te lezen en interpreteren, maar ze geven een indicatie van de gebieden waarin gezocht kan worden. Een goed begin zou de sectie ‘Top 5 Timed Events’ zijn. Dat ziet er ongeveer zo uit:

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

---------------------------------------- --------- --------

buffer busy waits 702,626 74,386 47.57

PL/SQL lock timer 9,048 45,101 28.84

CPU time 11,717 7.49

db file sequential read 2,773,149 5,821 3.72

global cache null to x 238,060 5,077 3.25

Het is duidelijk dat de meeste tijd in de periode is gegaan naar wait event ‘buffer busy waits’. Om vervolgens verder te kijken welke SQL statements dit veroorzaakt hebben, welke sessies en op welke objecten, is informatie nodig die in Statspack niet beschikbaar is. In de ter beschikking staande oracle versie kan dan nog geprobeerd worden om problematische sessies te tracen met Extended SQL Trace (een SQL trace met Waits en eventueel binds), maar omdat de problemen al gestart zijn, zal waarschijnlijk noodzakelijke informatie niet in de tracefile terechtkomen. Het vinden van de root cause wordt dan een moeilijke taak.

Sinds versie 7 heeft een handige en vooruitziende DBA wel de mogelijkheid om data te verzamelen die bovenstaande uitdaging kan versimpelen: het is bijvoorbeeld al zeer waardevol om elke seconde de resultaten van een join tussen V$SESSION en V$SESSION_WAIT voor alle sessies die ‘ACTIVE’ zijn op te slaan in een tabel. Tezamen met wat huishoudelijke taken eromheen (periodiek opslaan van SQL statements die in deze sessies werden uitgevoerd, de objecten en datafiles die benaderd zijn en het purgen van oude data) ben je dan in het bezit van een weldaad aan informatie. En dat is nu in feite wat in Oracle 10 in de kernel is ingebouwd: een ACTIVE SESSION HISTORY. Graham Wood, Kyle Hailey en enkele anderen ontwierpen onder meer ADDM en ASH en maakten daarmee het leven van de DBA verantwoordelijk voor performance tuning, een stuk aangenamer. Want de gedetailleerde informatie die in Statspack ontbrak en die zonder een eigen sampling mechanisme niet beschikbaar was, is in 10g en 11g -mits de parameter STATISTICS_LEVEL op ALL of TYPICAL staat- out of the box terug te zien tot een week oud! En dat opent de deur naar een analyse met een reeks aan dimensies (tabel 1):

Consumers

 

Resources

Sessie

  

User

 

CPU

Service

 

Waits

Module, Action

X

I/O

File

Block

Wait event

SQL statement

 

Programma

 

Tijd

Client (terminal, werkstation)

  

Database object

  

Tabel 1. Consumers versus Resources, dimensies in ASH

U kunt bijvoorbeeld de meest CPU verbruikende module of user identificeren en dat voor de hele week, voor slechts een interval of wellicht is er een trend: is een specifieke user elke dag tussen 10:15 en 10:40 de grootste consument van CPU. U kunt de Oracle blocks identificeren waar de meeste wait events voor geregistreerd staan (zogenaamde ‘hot blocks’). En uiteraard de meest resource intensieve SQL statements, waarvan u nu ook goed kunt zien of deze elke executie evenveel resources vragen. Misschien is het meest resource intensieve SQL statement wel alleen ‘duur’ indien uitgevoerd door gebruiker X en wel elke dag tussen 12:00 en 12:30.
Er is zoveel informatie beschikbaar. Zo gebruik ik zelf de ASH om het verloop van het nachtelijke ETL proces snel in kaart te brengen en kan dan via verschillende SQL statements sorteren op uitvoervolgorde, op meeste samples (= aantal seconden in ASH) en dus langst durende ETL workflow stappen. Maar ik kan zo ook snel stappen vergelijken voor elke nacht in de afgelopen week. Maar vooral gebruik ik ASH om problemen te analyseren die al voorbij zijn. Onlangs nog kon ik met ASH perfect aanduiden dat er elke ochtend tussen 07:00 en 08:00 een exceptioneel hoge load aanwezig was die de server overbelastte en ASH leidde me ook naar de oplossing. Dat kon voorheen bijna alleen echt goed met een gerichte Extended SQL trace.

Graham Wood legde in zijn presentatie uit hoe de Active Session History bij benadering werkt. Voor dit artikel is het voldoende aan te geven dat in de ASH iedere seconde alle ACTIVE sessions (sessies die tijd spenderen in een database call, hetzij door CPU verbruik, dan wel wachtend op een non-idle wait event, bijvoorbeeld een row lock, een latch of I/O) worden geselecteerd. ASH werkt met een in-memory circulaire buffer die om het uur ook naar disk geschreven wordt. De V$ACTIVE_SESSION_HISTORY view geeft de inhoud van de buffer weer, DBA_HIST_ACTIVE_SESS_HISTORY de naar disk geschreven ASH records (tot een week oud). De informatie in ASH is een groot deel van wat in V$SESSION beschikbaar is plus nog wat extra informatie. Heel interessant is dus ook informatie over blocking sessions: als er in ASH een enqueue (lock) sessie wordt weergegeven, is direct ook te zien welke de blokkerende sessie is. ASH is met een aantal Oracle parameters ook te configureren. Zo is het ook mogelijk om de default van 1 seconde naar een andere waarde te zetten of om ASH geheel af te zetten.

U kunt de genoemde twee database views gebruiken voor eigen SQL datamining, maar ASH geeft een zeer snelle blik op problemen wanneer de informatie erin bekeken wordt via Enterprise Manager (Database Control of Grid Control). De Top Activity pagina (zie afbeelding 2, rechterkant) geeft de ASH weer in grafische vorm en pieken worden dan direct duidelijk. Of zoals Graham aangaf: ‘Click on the big stuff’ (de hoogste pieken). ASH laat dan de op het gekozen moment actieve sessies zien met drilldown naar de sessie, SQL statement, username en dergelijke. Aangezien ASH elke seconde wordt gesampled en het actieve sessies betreft, is de DBTime bij benadering gelijk aan het aantal samples voor elke seconde. Dus als om 11:01:22 uur 13 actieve sessies worden ge‹dentificeerd in ASH, is er bij benadering op dat tijdstip ook 13 seconden aan DBTime ontstaan door alle sessies. In een drukke database kan DBTime dan ook makkelijk groter zijn dan het aantal ‘muurklok’ seconden in de meetperiode. Een aardige vuistregel is dat het aantal actieve sessies niet al te lang hoger moet zijn dan het aantal beschikbare CPU’s. OEM toont ook de ‘Maximum CPU’ lijn in de ASH grafiek zodat snel te zien is of piekmomenten langdurig boven deze lijn zitten.

Oracle Enterprise Manager
Afbeelding 2: OEM Performance page, rechts Top Activity met diverse view opties

In Oracle 11 is de hoeveelheid beschikbare informatie in ASH nog met heel zinvolle items uitgebreid:

  • De row source van het execution plan wordt zichtbaar gemaakt in ASH (extra kolommen: sql_plan_line_id, sql_plan_operation, sql_plan_options)! Ofwel het wordt ineens duidelijk wat de duurste stappen zijn in het executieplan!
  • Extra kolommen met CPU-informatie (de componenten worden zichtbaar): ‘in parse’, ‘in hard parse’, ‘in Java’, ‘in PL/SQL execution’, ‘in PL/SQL compilation’

 

Velen zullen zich afvragen of het samplen van active sessions iedere seconde niet veel overhead vraagt. Die vraag werd ook jarenlang gesteld ten aanzien van de parameter timed_statistics=TRUE. Kyle Hailey heeft daarop het volgende antwoord: als er gesampled zou worden met PL/SQL (een methode die we dadelijk zien als alternatief voor ASH in 10g/11g), dan kost dat ongeveer 1% van de CPU als er 10 actieve sessies tegelijkertijd zijn; met Direct Memory Access (DMA) is het nog eens 200 maal goedkoper dan in PL/SQL en aangezien ASH in Oracle 10g is ingebouwd in de kernel zal het nog goedkoper zijn dan DMA. Dat is voor verreweg de meeste databases een te verwaarlozen overhead. En het alternatief is een drastische teruggang in performance instrumentarium. Datzelfde geldt voor timed_statistics. Het is eigenlijk geen discussie: beide zijn van dermate grote informatieve waarde dat performance tuning zonder ASH en vooral timed_statistics zeer lastig is of eigenlijk onmogelijk.

Simulated ASH

Wat nu als u geen Oracle 10 of 11 ter beschikking heeft? In dat geval komt de genoemde PL/SQL sampling als mogelijkheid naar voren om ASH te simuleren. Zelf heb ik in een Oracle 9 omgeving veel baat gehad bij een zelf geschreven sampling package: menigmaal kon ik vragen beantwoorden vanuit de supportorganisatie die betrekking hadden over gebeurtenissen uren of dagen geleden. Ik heb als leidraad voor de package hoofdstuk 4 van het eveneens aanbevelenswaardige boek ‘Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning’ van Oracle Press genomen.
Eerder genoemde Kyle Hailey heeft de ASH simulatie nog een stuk grondiger aangepakt en een lichtgewicht ASH grafische interface geschreven. Deze is tevens geschikt voor Oracle 7 t/m 9 omdat Kyle er een SASH (Simulated ASH) bij heeft geschreven: een eigen set van repository tabellen en sampling package in PL/SQL die te installeren is op alle versies vanaf 7. Niet alle functionaliteit van 10g ASH is dan voorhanden, omdat bijvoorbeeld het opvragen van een ASH rapport voorbehouden is aan versie 10g en verder. Tevens is in oudere versies niet alle informatie beschikbaar in V$SESSION en V$SESSION_WAIT, welke voor een zelf geschreven sampling mechanisme de basis vormen. Voor meer informatie, boeiende ASH presentaties en de tool zelf verwijs ik naar de website: http://ashmasters.com/.

Conclusie

Performance tuning in de twee meest recente versie van het Oracle RDBMS heeft nieuwe dimensies gekregen. De Active Session History en DBTime zijn twee features die voor mij inmiddels zo vanzelfsprekend zijn geworden dat ik het echt mis als ik met een versie 8 of 9 database werk. Graham Wood wist in zijn presentatie naar mijn mening goed duidelijk te maken wat het belang van deze nieuwe functionaliteit is voor op de Oracle database gebaseerde applicaties. De volle zaal bij Oracle in De Meern gaf aan dat presentaties als deze meer dan gewenst zijn.

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