Het gemak van xmltype

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:

update message_error_log lg
set lg.payload = ?

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

 where lg.message_type = 1234
and    lg.exception_type = ‘com.exception.ItemNotFoundException’

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

to_char (substr (lg.stacktrace
,                instr (lg.stacktrace, ‘item does not exist:’) + 19
,                  (  instr (lg.stacktrace
,                            ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)

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:

and    extractvalue (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
) is not null

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:

 update message_error_log lg
set lg.payload =
deletexml (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
).getclobval ()

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:

update message_error_log lg
set lg.payload =
deletexml (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
).getclobval ()
where lg.message_type = 1234
and    lg.exception_type = ‘com.exception.ItemNotFoundException’
and    extractvalue (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
) is not null

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.

Buffer cache hit ratio verbeteren door tactisch schedulen

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.

Oplossing voor ORA-02050 en ORA-02051

Onlangs liep ik tegen een vervelend probleem aan: een in de ontwikkel- en testomgeving prima draaiend batchprogramma gaf in de acceptatieomgeving opeens allerlei foutmeldingen.

Wat was er aan de hand? Een aanzienlijke stored procedure (package) in een Oracle 11g database had tot doel data van een tweetal databases te converteren naar een derde. Dit gebeurde in een grote loop, met allerlei cursoren waarbij in 75% van de gevallen met databaselinks werd gewerkt. Na elke doorloop van de buitenste loop werd een commit uitgevoerd. Hier ging het mis: na de commit trad een foutmelding op: ORA-02050 (transaction string rolled back, some remote DBs may be in-doubt) gevolgd door ORA-02051 (another session in same transaction failed). De integriteit van de database die over de databaselink wordt benaderd ten opzicht van de opgehaalde dataset wordt betwijfeld en Oracle draait de hele transactie terug. Verschil tussen ontwikkel en test aan de ene kant en acceptatie was de hoeveelheid data-activiteit. In acceptatie waren behoorlijk veel transacties in de gelinkte databases, in ontwikkel en test vrijwel geen.

Belangrijk om te weten is dat Oracle ook bij het uitsluitend opvragen van data via een databaselink toch een transactie start. Zolang er geen commit wordt gegeven, blijven de opgevraagde rijen dus in de sessie gereserveerd.

De oplossing voor het probleem is derhalve, om direct na het opvragen van de rij(en) via een databaselink een commit uit te voeren. Meestal is het echter in een grote loop, waarin allerlei controles dienen te worden uitgevoerd niet wenselijk om tussentijdse commits uit te voeren. Vandaar dat ik er voor gekozen heb om alle cursoren te vervangen door functies, die via een autonomous_transaction pragma een aparte sessie starten om de data op te halen. Deze kan vervolgens afgesloten worden met een commit en het probleem is verholpen.

Een voorbeeld:

Oude situatie

[code type=”sql”]
declare

cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;

begin

for r_tst in c_tst(‘FOO’) loop

end loop;

commit;
end;
[/code]

Nieuwe situatie

[code type=”sql”]
declare
type t_tst is table of tst_tabel%rowtype index by binary_integer;
l_tst t_tst;

function get_tst(p_parameter)
return t_tst
is
pragma autonomous_transaction;
l_tst t_tst;

cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;

begin
open c_tst(p_parameter);
fetch c_tst bulk collect into l_tst;
close c_tst;
commit;
return l_tst;
end get_tst;

begin
l_tst := get_tst(‘FOO’);
for i in 1..l_tst.count loop

end loop;
commit;
end;
[/code]

Beetje meer code, maar wel opgelost.

Hier komt de sidebar

Volg ons op

© Orcado B.V. | 1999 - 2013