Performancewinst met PL/SQL

December 2003 - Bij veel bedrijven wordt nog steeds gebruikt gemaakt van de programmeertalen Cobol of C voor het uitvoeren van batchverwerking. Belangrijkste reden hiervan was dat deze talen efficienter en sneller waren dan de “native” taal PL/SQL.
Sinds database versie 8i werkt Oracle echter hard aan het versnellen van PL/SQL. Bovendien zijn er allerlei andere verbeteringen doorgevoerd waardoor PL/SQL nu de geprefereerde taal is voor batchverwerking.

Dit Whitebook geeft een opsomming van bestaande en nieuwe mogelijkheden.

Native compilatie van PL/SQL (9i)

Native compilatie vertaalt PL/SQL programma’s naar de taal C. Vervolgens wordt het C programma gecompileerd in de lokale machine taal. Het compileren gebeurt transparant, de ontwikkelaar hoeft geen extra handelingen te verrichten.
Het wel of niet native compilen van programmacode is een instelling van de database, maar kan ook per sessie dynamisch bepaald worden. Het is aan te bevelen een keuze te maken om alle code wel of niet native te compileren. Het aanroepen van native compileerde programma’s vanuit niet-native gecomplieerde programma’s geeft een performance degradatie.
Volgens Oracle levert het native compileren een snelheidswinst op van maximaal 40%.

NOCOPY compiler hint (8i)

Een variabele die meegegeven wordt bij de aanroep van PL/SQL programma wordt altijd lokaal binnen dat programma in het geheugen gekopieerd (“passing by value”). Een alternatief is het gebruik van de compiler hint NOCOPY achter de definitie van de parameter in de specification van het programma.

Voorbeeld:

function speed(car in out NOCOPY) return number; 

Het resultaat hiervan is dat programma speed geen kopie van car creert, maar rechtstreeks gebruik maakt van de variabele car die binnenkomt. Voor C adepten: er wordt alleen een pointer doorgegeven (“passing by reference”).

Een voorbeeld in de PL/SQL Developer Guide laat een reductie zien van 21 seconden naar 0 (!) seconden verwerkingstijd. Volgens Oracle kan een snelheidswinst van 30% tot 200% bereikt worden.

PLS_INTEGER (8i)

Binnen PL/SQL is het datatype PLS_INTEGER geoptimaliseerd voor snelle verwerking. Indien het het waardebereik van PLS_INTEGER voldoende is geniet dit datatype de voorkeur boven NUMBER of INTEGER.

Verwerking logische expressies (generiek)

Logische expressies worden altijd verwerkt van links naar rechts. De verwerking van de expressie stopt zodra het resultaat bepaald kan worden. Bij een constructie als:

if (speed(car) > 100) or (engine_hp > 110) then 

stopt de verwerking zodra de auto harder rijdt dan 100.

Het berekenen van de snelheid van de auto kost echter meer tijd dan de vaststelling hoe krachtig de motor is. Beter is het beide delen van de expressie om te draaien.

Pinnen van packages (8i)

Oracle regelt zelf welke programmacode vastgehouden wordt in het geheugen van de machine en welke programmacode niet meer nodig is. Op het moment dat het bewuste programma weer aangeroepen wordt dan wordt de code weer in het geheugen geladen.

Dit gedrag kan voorkomen worden door enkele belangrijke en veelgebruikte packages in het geheugen te “bevriezen”. Voorbeeld:

exec dbms_shared_pool.keep('<PL/SQL code name>'); 

Table functions (9i)

Met table functions is het niet meer nodig het resultaat van complexe PL/SQL programma’s op te slaan in een Oracle tabel voor verdere bewerking. In plaats daarvan kunnen de berekende gegevens opgeslagen worden in het geheugen in een collectievariabele. Oracle kent nu de mogelijkheid rechtstreeks vanuit SQL statements gebruik te maken van collecties. Table functies kunnen als het ware beschouwd worden als views op tabellen in het geheugen.

Bulk binds (8i, 9i)

Iedere aanroep van PL/SQL naar SQL geeft een bepaalde overhead. Met bulk binding maak je gebruik van collections om een set van gegevens in 1 keer op te vragen of in 1 keer aan te bieden aan de database.

• FORALL - een collectie vanuit PL/SQL aanbieden aan de database voor insert, update of delete
• COLLECT INTO - het resultaat van een query op de database in een PL/SQL collectie plaatsen.

Oracle geeft aan dat een performancewinst van 30% mogelijk is.

Vanaf release 9i is het tevens mogelijk om eventuele exceptions die optreden tijdens de verwerking van een individuele rij af te vangen, zodat de rest van de rijen wel verwerkt kan worden.

Native dynamic SQL (8i, 9i)

Met native dynamic SQL kan binnen een PL/SQL programma dynamisch een SQL statement opgebouwd worden. De programmeur kan hierdoor het programma zelf rekening laten houden met omstandigheden in gegevens of verwerking die een andere benadering van de database vereisen.

Vanaf release 9i zijn de mogelijkheden van het gebruik van bulk binds in combinatie met native dynamic SQL verbeterd.

Interne PL/SQL optimalisaties (8i, 9i)

Oracle heeft een aantal veel gebruikte PL/SQL functies geoptimaliseerd. Volgens Oracle is hiermee een snelheidswinst bereikt van 10 tot 30% bij de overgang van 7 naar 8i. Bij de overgang van 8i naar 9i is nog eens een winst van 10% bereikt.

Welke PL/SQL verbeteringen mogen we verwachten in Oracle10g?

In een volgend Whitebook besteden we aandacht aan de PL/SQL verbeteringen in Oracle10g.