Zależności między obiektami bazodanowymi w Oracle

Na jakiś czas skończyłem zabawę z JBoss, Maven oraz EJB. Dla odmiany zagłębie się na chwile w otchłań Oracle’owej bazy danych. Przygoda ta będzie wiązała się z wyszukiwaniem wszystkich komponentów wykorzystujących dany obiekt bazodanowy. Oracle w swojej uprzejmości dostarcza widok DEPTREE oraz bardziej przyjazny dla oczu IDEPTREE. Perspektywy te mają za zadanie wskazać wszystkie miejsca wykorzystania danej funkcji, procedury, pakietu, widoku oraz tabeli (mam nadzieję, że wymieniłem wszystkie). W celu uzupełnienia danych prezentowanych przez wcześniej wspomniane perspektywy, należy wykonać jako SYS skrypt $ORACLE_HOME/RDBMS/admin/utldtree.sql, a następnie wygenerować informację o interesującym Nas obiekcie:

EXECUTE deptree_fill('TABLE','HR','EMPLOYEES');

Operacja to spowoduje uzupełnienie widoków IDEPTREE oraz DEPTREE odpowiednimi danymi.

SELECT * FROM ideptree;

Wszystko wygląda pięknie. No może poza brakiem takich informacji jak np. numery linii, w których znajduje się dane odwołanie. Bez informacji owej można sobie jednak poradzić. Problem powstaje gdy grupujemy funkcje oraz procedury w pakiety i mamy na celu wyszukanie wywołań konkretnej z nich. DEPTREE obsługuje jedynie całe pakiety, ewentualnie uwzględniając podział na specyfikację oraz ciało.

Trochę dłubaniny i napisałem prosty skrypt umożliwiający wyszukanie wszystkich wystąpień danego fragmentu kodu. Przedstawia się on następująco:

CREATE OR REPLACE TYPE usage_location IS OBJECT (
   object_type VARCHAR2(50),
   object_name VARCHAR2(50),
   line_number NUMBER,
   CONSTRUCTOR FUNCTION usage_location(object_type VARCHAR2
                                     , object_name VARCHAR2
                                     , line_number NUMBER) RETURN SELF AS RESULT
   );

CREATE OR REPLACE TYPE BODY usage_location AS
   CONSTRUCTOR FUNCTION usage_location(object_type VARCHAR2
                                     , object_name VARCHAR2
                                     , line_number NUMBER) RETURN SELF AS RESULT
   AS
   BEGIN
      SELF.object_type := object_type;
      SELF.object_name := object_name;
      SELF.line_number := line_number;
      RETURN;
   END;
END;

CREATE OR REPLACE TYPE usage_location_list
IS TABLE OF usage_location;

CREATE OR REPLACE FUNCTION find_usage(p_text IN VARCHAR2
                                    , p_owner VARCHAR2 := user) RETURN usage_location_list PIPELINED
IS
   c_view_type_name CONSTANT VARCHAR2(10) := 'VIEW';

   v_view_source CLOB;
   v_location usage_location;
BEGIN
   -- Checking views
   FOR r IN (SELECT * FROM all_views WHERE owner = p_owner)
   LOOP
      SELECT dbms_metadata.get_ddl(c_view_type_name, r.view_name)
        INTO v_view_source
        FROM dual;
      IF (dbms_lob.instr(LOWER(v_view_source), LOWER(p_text)) != 0) THEN
         v_location := NEW usage_location(c_view_type_name, r.view_name, NULL);
         PIPE ROW ( v_location );
      END IF;
   END LOOP;

   -- Checking packages & triggers
   FOR r IN ( SELECT *
                FROM all_source
               WHERE owner = p_owner
                 AND LOWER(text) like LOWER('%' || p_text || '%') )
   LOOP
      v_location := NEW usage_location(r.type, r.name, r.line);
      PIPE ROW ( v_location );
   END LOOP;
END;

SELECT *
  FROM TABLE ( find_usage('employees') );

Na początku tworzymy typ obiektowy usage_location z jednym konstruktorem. Typ ten określa wszystkie właściwości pojedynczego wystąpienia danego obiektu, jakie będziemy prezentować: rodzaj komponentu, jego nazwę oraz numer linii. Kolejny krok polega na zdefiniowaniu kolekcji wspomnianego typu, którą zwracać będzie docelowa funkcja pipeline’owa find_usage. Polecam zapoznanie się z jej ciałem. Na końcu skryptu zaprezentowano przykładowe wywołanie. W celu odszukania konkretnej procedury umieszczonej w pakieci napisalibyśmy coś w stylu: SELECT * FROM TABLE ( find_usage(‘mypackage.myprocedure’) );. Kod PL/SQL posłuży zapewne czytelnikom w celach edukacyjnych.

Advertisements

Oracle Automatic Workload Repository

Ostatnimi czasy zastanawiałem się nad sposobem wyśledzenia najdłużej trwających zapytań wykonywanych na bazie danych Oracle. Potrzeba ta powstała podczas pracy z modułem raportowym, gdyż tam czas wykonania kwerendy sięgał kilku minut. Z pomocą nadchodzi domyślnie włączony mechanizm Automatic Workload Repository (AWR). AWR automatycznie tworzy snapshot’y w ustalonych odstępach czasowych. Na podstawie snapshot’ów (wykonując skrypt PL/SQL dostarczony z bazą) generować można raporty prezentujące szczegóły wykonania konkretnego zapytania SQL, w formie tekstowej lub HTML. Ponadto, podczas tworzenia snapshot’u wypełniane zostają tabele dba_hist_sqlstat, dba_hist_sqltext, dba_hist_sql_summary, oraz dba_hist_sql_plan, zawierające informacje między innymi o czasie wykonania zapytań, ich treść, plan wykonania, koszt odczytów z dysku twardego i obliczeń CPU.

Zarządzanie snapshot’ami

Aby sprawdzić aktualne ustawienia bazy, odpytać należy perspektywę dba_hist_wr_control. Kolumna snap_interval prezentuje odstępy czasowe, po których automatycznie tworzony będzie kolejny snapshot. Wartość retention definiuje zaś okres ważności danego zrzutu. Istnieje możliwość zmiany wartości domyślnych korzystając z funkcji dbms_workload_repository.modify_snapshot_settings.

EXEC dbms_workload_repository.modify_snapshot_settings ( retention => 43200
                                                       , interval  => 1440
                                                       );

W podanym przykładzie, snapshot tworzony będzie codziennie i ważny (przetrzymywany w bazie danych) miesiąc. Informacje na temat aktualnie dostępnych zrzutów udostępnia perspektywa dba_hist_snapshot. W celu stworzenia snapshot’u ręcznie, należy wykonać procedurę dbms_workload_repository.create_snapshot. Istnieje także możliwość usumięcia zakresu zrzutów:

EXEC dbms_workload_repository.drop_snapshot_range ( low_snap_id  => 581
                                                  , high_snap_id => 582
                                                  );

Przygotowanie raportu

Na potrzeby owego tutoriala stworzę dwa snapshot’y i wykonam jedno zapytanie SQL między nimi, którego statystyki znajdą się w raporcie. Wykonywane czynności prezentuje poniższy listing. W skrócie są to:

  • Zalogowanie się na użytkownika SYS.
  • Wykonanie snapshot’u.
  • Wykonanie instrukcji: SELECT * FROM hr.employees;.
  • Ponowne wykonanie snapshot’u.
  • Sprawdzenie sql_id wykonanego w międzyczasie zapytania:
SELECT sql_id
  FROM dba_hist_sqltext
 WHERE LOWER ( TRIM ( TO_CHAR ( SUBSTR ( sql_text, 1, 100 ) ) ) ) = 'select * from hr.employees';
  • Wykonanie skryptu $ORACLE_HOME/RDBMS/ADMIN/awrsqrpt.sql i podanie niezbędnych informacji.
C:\>sqlplus /nolog
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> EXEC dbms_workload_repository.create_snapshot;
SQL> select * from hr.employees;
-- Results...
SQL> EXEC dbms_workload_repository.create_snapshot;
SQL> SELECT sql_id FROM dba_hist_sqltext WHERE LOWER ( TRIM ( TO_CHAR ( SUBSTR ( sql_text, 1, 100 ) ) ) ) = 'select * from hr.employees';

SQL_ID
-------------
5rx8n86bhv3y5

SQL> @E:\Programy\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2555341586 XE                  1 xe

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2555341586        1 XE           xe           LUKASZIBM

Using 2555341586 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
xe           XE                 856 24 Sie 2010 13:59      1
 857 24 Sie 2010 15:00      1
 858 24 Sie 2010 15:14      1
 859 24 Sie 2010 15:14      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 858
Begin Snapshot Id specified: 858

Enter value for end_snap: 859
End   Snapshot Id specified: 859

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 5rx8n86bhv3y5
SQL ID specified:  5rx8n86bhv3y5

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_858_859.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrsqlrpt_1_858_859.html

Uwaga: narzędzie sqlplus uruchomić należy z folderu, do którego użytkownik systemowy, z uprawnieniami którego działa baza danych, ma możliwość zapisu. Przykładowy raport zobaczyć można tutaj.

Użyteczne zapytanie

Po przeanalizowaniu widoków dba_hist_sql*, skonstruowałem jedno zapytanie pokazujące instrukcje SQL od najbardziej do najmniej kosztownej, wraz z czasem ich wykonania oraz użytkownikiem, który je zlecił. Wybrane na tej podstawie sql_id posłużyć mogą jako argument dla tworzonych raportów.

SELECT dhs.sql_id                                                  "SQL_ID"
     , NUMTODSINTERVAL(dhs.elapsed_time_total / 1000000, 'SECOND') "DURATION"
     , dhs.parsing_schema_name                                     "USER"
     , ( SELECT dhsp.timestamp
           FROM dba_hist_sql_plan dhsp
          WHERE dhsp.sql_id = dhs.sql_id
            AND ROWNUM = 1
       )                                                           "QUERY TIME"
     , dhss.begin_interval_time                                    "SNAPSHOT BEGIN"
     , dhss.end_interval_time                                      "SNAPSHOT END"
     , dhst.sql_text                                               "STATEMENT"
  FROM dba_hist_sqlstat dhs
     , dba_hist_sqltext dhst
     , dba_hist_snapshot dhss
 WHERE dhs.sql_id = dhst.sql_id
   AND dhs.snap_id = dhss.snap_id
--   AND LOWER(dhst.sql_text) LIKE LOWER('%employees%') -- Opcjonalny warunek
 ORDER BY dhs.elapsed_time_total DESC;