Profilowanie zawrapowanego kodu PL/SQL

Czy zastanawialiście się co stanie się, gdy spróbujcie profilować zawrapowany kod PL/SQL? Czy dowiemy się jakie nr linii kosztują nas najbardziej? Czy dowiemy się jakie funkcje i procedury uruchamia nas zawrapowany kod?

Jeśli tak – zapraszam do lektury 🙂

Z okazji tworzenia niniejszego wpisu utworzyłem 2 pakiety, których ciała następnie zawrapowałem:

create or replace PACKAGE PACKAGE1 AS 

  PROCEDURE proc2_pkg(i_cnt2 NUMBER);
END PACKAGE1;
/
CREATE OR REPLACE PACKAGE BODY PACKAGE1 AS

  PROCEDURE proc1_pkg(i_cnt NUMBER)
  IS
   v_last_name employees.last_name%TYPE;
   x number:=0;
  BEGIN
    SELECT COUNT(*)
        INTO x
    FROM EMPLOYEES;
    
    IF x = 2 THEN
     x := x+ 1;
    else 
     -- loop
     FOR i IN 1..i_cnt LOOP
          --select 
          SELECT last_name
          INTO v_last_name
        FROM employees 
        WHERE employee_id = 100;
     END LOOP;
     --after loop
     END IF; 
  END proc1_pkg;

  PROCEDURE proc2_pkg(i_cnt2 NUMBER) AS
     x number:=0;
  BEGIN
   -- x
   x:=x+1;
   --running proc1
   proc1_pkg(i_cnt2);
  END proc2_pkg;

END PACKAGE1;
/

CREATE OR REPLACE PACKAGE PACKAGE2 AS 

  procedure proc3_pkg(i_cnt number);

END PACKAGE2;

/
CREATE OR REPLACE PACKAGE BODY PACKAGE2 AS

 procedure proc3_pkg(i_cnt number) AS
 BEGIN
 PACKAGE1.proc2_pkg(200);
 END proc3_pkg;

END PACKAGE2;

1. W celu przygotowania bazy do profilowania wykonałem (z linii poleceń):

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
@profload.sql

2. Następnie na użytkowniku, na którym dokonywałem profilowania wykonałem zawartość pliku proftab.sql (znajduje się w tej samej lokalizacji co profload.sql). I już mamy wszystko gotowe do działania 🙂

3. Jak przeprowadzić profilowanie? Pokażę jak zrobić to używając SQL i PL/SQL. Narzędzia graficzne mogą nam dodatkowo tą pracę ułatwić 🙂

BEGIN
 DBMS_PROFILER.START_PROFILER(run_comment => 'commment RS');
 PACKAGE2.proc3_pkg(200);
 DBMS_PROFILER.STOP_PROFILER;
 END;

run_comment  pojawi się w tabeli, w której poszukamy runid profilowania:

SELECT runid,
 run_date,
 run_comment,
 run_total_time
 FROM   plsql_profiler_runs
 ORDER BY runid;

Z powyższego zapytania bierzemy runid i wstawiamy do kolejnego zapytania:

SELECT u.runid,
 u.unit_number,
 u.unit_type,
 u.unit_owner,
 u.unit_name,
 d.line#,
 d.total_occur,
 d.total_time,
 d.min_time,
 d.max_time
 FROM   plsql_profiler_units u
 JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
 WHERE  u.runid = 1   -- <-- TUTAJ
 ORDER BY u.unit_number, d.line#;

Oczywiście można zamknąć to w jedno zapytanie. W przypadku, gdy nasz kod nie jest zawrapowany można wykonać join do widoków %_source w celu sprawdzenia co kryje się pod danym numerem linii.

Co zwróciło nasze zapytanie dla kodu zawrapowanego (dla  celów niniejszego wpisu ograniczyłem liczbę kolumn i wierszy) ?

Co widzimy? Jaki pakiet uruchomił się, czy dana linia wykonała się i ile czasu zajęła. Widzimy też, że w pakiecie PACKAGE1 linia (szczęśliwa) 13sta nie uruchomiła się wcale – z powodu tego, że IF nie był spełniony. Uruchomiliśmy PACKAGE2.PROC3_PKG a dowiedzieliśmy się, że został uruchomiona również procedura z PACKAGE1. Jest to o tyle istotne, że nasz kod w bazie wygląda tak:

create or replace PACKAGE BODY PACKAGE2 wrapped
 a000000
 aa
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 abcd
 b
 86 a6
 EhMzRB+blxBGLaqp0FEF9Ye6d64wg5m49TOf9b9cuJu/9MPnqbhfjnbWvlOOjuGs5JBMTObW
 ca9Whs6mIL+ycjAXnS9EnWkPSbHKLsh7RERJ6r+uJMo7pusgTVsJIDYrIJFwDt6yifWBxzV1
 4NeVRIuEOwPJpqbCLs   ......

Mam nadzieję, że ten wpis pomoże wszystkim zmagającym się z (oczywiście własnym 🙂 i nie tylko) zawrapowanym kodem. Testy zostały przeprowadzone na wersji 12c.

Dziękuję za lekturę! 🙂