Proč a jak používat Oracle Profiler?

V této části bych rád na příkladě ukázal, proč a jak používat Oracle Profiler a k čemu je dobré rozšíření oraclovského databázového modulu nazvané rkEdit Oracle Profiler.

Mějme následující úlohu: Vytvořte balíček pro matematické výpočty a v něm funkci pro výpočet faktoriálu. Dále vytvořte tabulku, kam budete ukládat pětici primární klíč, číslo, jeho faktoriál, jméno uživatele, který záznam vytvořil, a datum s časem, kdy byl záznam vytvořen. Primární klíč se bude plnit v triggeru ze sekvence začínající hodnotou 1 000 000 a vzrůstající po hodnotě 10. Jméno uživatele a datum s časem se rovněž budou plnit v triggeru. Nakonec vytvořte proceduru, která tabulku naplní čísly a odpovídajícími hodnotami faktoriálu v rozsahu 0 až n, kde n bude parametr procedury.

Začněme tabulkou a sekvencí, to je nejjednodušší:

CREATE TABLE Factorial
(
  IDFactorial NUMBER NOT NULL PRIMARY KEY,
  NumberValue NUMBER NOT NULL CHECK (NumberValue >= 0),
  Factorial NUMBER NOT NULL,
  CreatedBy VARCHAR2 (200) NOT NULL,
  CreatedAt DATE NOT NULL
);

CREATE SEQUENCE FactorialSeq START WITH 1000000 INCREMENT BY 10;

Nyní vytvořme trigger, který pracuje dle požadavku v zadání.

CREATE OR REPLACE TRIGGER FactorialBefIns
  BEFORE INSERT ON Factorial FOR EACH ROW
BEGIN
  SELECT FactorialSeq.NextVal
    INTO :new.IDFactorial
    FROM DUAL;
  :new.CreatedBy := USER;
  :new.CreatedAt := SYSDATE;
END FactorialBefIns;


Na řadě je balíček matematických funkcí. My zde budeme mít pouze jednu pro výpočet faktoriálu.

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PACKAGE MATH_PKG
IS
  FUNCTION Factorial (a_Number NUMBER) RETURN NUMBER;
END MATH_PKG;
/* << PL/SQL BLOCK % END >> */

Výpočet faktoriálu budeme provádět v cyklu. Kód bude vypadat následovně (pro jednoduchost budeme předpokládat, že uživatel bude zadávat vždy kladná čísla a tedy není nutno ošetřovat okrajové podmínky).

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PACKAGE BODY MATH_PKG
IS
  FUNCTION Factorial (a_Number NUMBER)
  RETURN NUMBER
  IS
  BEGIN
    IF a_Number in (0, 1) THEN
      RETURN 1;
    ELSE
      DECLARE
        i NUMBER := 2;
        vResult NUMBER := 1;
      BEGIN
        LOOP
          EXIT WHEN i > a_Number;
          vResult := vResult * i;
          i := i + 1;
        END LOOP;
        RETURN vResult;
      END;
    END IF;
    END Factorial;
END MATH_PKG;
/* << PL/SQL BLOCK % END >> */

Nakonec zbylo vytvoření procedury, která bude plnit tabulku. Řekněme, že ještě neumíme zcela dobře programovat v PL/SQL a tedy výpočet faktoriálu budeme vkládat do proměnné jedním z pomalých způsobů:

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PROCEDURE InsertFactorials (aMaxNumbers NUMBER)
IS
  i NUMBER := 0;
  vFactorial NUMBER;
BEGIN
  LOOP
    EXIT WHEN i = aMaxNumbers;

    SELECT Math_Pkg.Factorial (i) --sice správný, ale zbytečně pomalý výpočet
      INTO vFactorial
      FROM DUAL;

    INSERT INTO Factorial (NumberValue, Factorial)
    VALUES (i, vFactorial);
    i := i + 1;

    COMMIT;
  END LOOP;
END InsertFactorials;
/* << PL/SQL BLOCK % END >> */

Nezbývá nám tedy již nic jiného, než proceduru spustit a otestovat, zda jsou výpočty v pořádku. Nechme naplnit tabulku čísly do 5 000:

/* << PL/SQL BLOCK % BEGIN >> */
BEGIN
  InsertFactorials (aMaxNumbers => 5000);
END;
/* << PL/SQL BLOCK % END >> */

SELECT * FROM Factorial;

Kód funguje tak, jak má. Jenže my nyní chceme odhalit místa, kde se tráví nejvíce času a ty pokud možno zoptimalizovat. Zde nastupuje jednak Oracle Profiler, který má za úkol získat informace o běhu programu, a jednak rkEdit Oracle Profiler, který tyto data zobrazí tak, abychom mohli slabá místa nalézt.

Spustíme tedy uvedenou proceduru ještě jednou, tentokráte ale s Profilerem (v menu Oracle v rkEditu je na to uveden příkaz). Výsledek může vypadat tak, jak jej vidíme na speciální stránce (jde o HTML report, který lze z rkEdit Oracle Porfileru získat, podobně lze dostat i XML report).

Zde vidíme jediný dosud provedený běh, podívejme se na něj podrobněji (operace Drill Down, v HTML reportu stačí kliknout na libovolnou položku v tabulce). Zde získáme přehled o tom, jak dlouho se který kód vykonával. Nejvíce času spotřebovala procedura InsertFactorials, tedy se na ní podíváme podrobněji. Opět provedeme operaci Drill Down (v HTML reportu klikněte na řádek s uvedenou procedurou) a dostaneme přehled o jednotlivých řádcích kódu, které se vykonaly (při opětovné operaci Drill Down získáme kompletní zdrojový kód s informaci o vykonaných řádcích). Znovu nalezneme řádek, kde se tráví nejvíce času. Vidíme, že to je získávání hodnoty faktoriálu. Zkušenější programátor ví, že takto získávat hodnotu je opravdu časově náročné a tedy zvolí mnohem elegantnější postup:

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PROCEDURE InsertFactorials (aMaxNumbers NUMBER)
IS
  i NUMBER := 0;
  vFactorial NUMBER;
BEGIN
  LOOP
    EXIT WHEN i = aMaxNumbers;

    vFactorial := Math_Pkg.Factorial (i); -- mnohem lepší způsob

    INSERT INTO Factorial (NumberValue, Factorial)
    VALUES (i, vFactorial);
    i := i + 1;

    COMMIT;
  END LOOP;
END InsertFactorials;
/* << PL/SQL BLOCK % END >> */

Opět spustíme generování faktoriálu s Profilerem a podíváme se, jak se kód chová nyní (viz html report).

Vidíme, že kód je o něco rychlejší a že procedura InsertFactorials již evidentně není tou nejpomalejší částí. Přesto se podíváme, zda ještě nelze něco vylepšit. Nejvíce času spotřebuje vlastní INSERT příkaz, ale s tím nic neuděláme (ve skutečnosti udělat můžeme - podívat se, zda je možné zoptimalizovat trigger, ale k tomu se ještě dostaneme). Druhou nejpomalejší částí je řádek, který data COMMITuje. Pokud se podíváme na vlastní kód, jistě nás napadne dát tento příkaz až za cyklus. Proveďme tak a kód procedury může vypadat následovně (Ne vždy je ale tato operace žádoucí. Pokud např. provádíme náročný výpočet, je lepší průběžně COMMITovat a v případě pádu spojení navázat na již hotové výsledky než vše dělat úplně od začátku):

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PROCEDURE InsertFactorials (aMaxNumbers NUMBER)
IS
  i NUMBER := 0;
  vFactorial NUMBER;
BEGIN
  LOOP
    EXIT WHEN i = aMaxNumbers;

    vFactorial := Math_Pkg.Factorial (i); -- mnohem lepší způsob

    INSERT INTO Factorial (NumberValue, Factorial)
    VALUES (i, vFactorial);
    i := i + 1;
  END LOOP;

  COMMIT;
END InsertFactorials;
/* << PL/SQL BLOCK % END >> */

Opět kód pusťme s Profilerem a podívejme se na výsledek. V přehledu běhů vidíme, že celý běh a procedura InsertFactorials spotřebovává ještě méně času. Pokud se na ní znovu podíváme, zjistíme, že nejvíce času zabere INSERT a ostatní je vzhledem k němu zanedbatelné (V rkEdit Profileru jde tuto položku schovat, čímž je lépe vidět, co se můžeme pokusit dále optimalizovat. V tomto případě se však spokojme s právě uvedeným výsledkem.).

Vraťme se tedy zpět k jednotlivým kódům (operace Drill Up). Nyní nejvíce času zabírá volání faktoriálu (resp. se nejvíce času tráví v balíčku MATH_PKG, ale protože víme, že tam je jediná funkce, můžeme říct uvedené). Můžeme jednak zkusit opět prozkoumat kód a vylepšit jej (určitě to ještě jde), ale my se pokusíme o něco jiného. Existuje další způsob, jak faktoriál počítat a to rekursí. Změňme tedy kód funkce následovně:

/* << PL/SQL BLOCK % BEGIN >> */
CREATE OR REPLACE PACKAGE BODY MATH_PKG
IS
  FUNCTION Factorial (a_Number NUMBER)
  RETURN NUMBER
  IS
  BEGIN
    IF a_Number IN (0, 1) THEN
      RETURN 1;
    ELSE
      RETURN a_Number * Factorial (a_Number-1);
    END IF;
  END Factorial;
END MATH_PKG;
/* << PL/SQL BLOCK % END >> */

A opět spusťme proceduru InsertFactorials. Výsledek najdete zde. Vidíme, že počítání faktoriálu je téměř čtyřnásobně časově náročnější a proto se vrátíme k původnímu algoritmu, který případně vylepšíme.

Poslední jednotkou je trigger, ale na něm bohužel nic vylepšit nejde. Jednotky nazvané anonymní blok ponechme stranou, v tomto případě jde jenom o „balast“, kterým je obklopeno spouštění procedury.

Závěr

Na jednoduchém případě bylo ukázáno, že funkční kód nemusí být nutně ten nejrychlejší. Optimalizace běhu kódu (profilování) je velmi důležitá součást vývoje aplikací.

Databázový server Oracle disponuje možnostmi, které umožňují profilování provádět. Výsledek je bohužel pouze uložen do tabulek a o zobrazení se musí postarat někdo jiný. Nástroj, který toto zobrazení umí uživateli podat, je právě rkEdit Oracle Profiler.

Je správné poznamenat, že jde opravdu pouze o zobrazení, byť ve formě, která dokáže velmi dobře pomoci při hledání úzkých míst v PL/SQL kódu. Pokud chcete optimalizovat kód opravdu pečlivě, musíte mít velmi dobré znalosti jak databázového serveru Oracle, tak i platformy a hardwaru, na které Oracle běží.

Poznámka

Příklady byly prováděny na počítači s procesorem Athlon 2000+ a 512 MB RAM. Operačním systémem byly Windows XP Professional SP1a, databáze byla Oracle 9i RC2.