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;
Advertisements

One Response to Oracle Automatic Workload Repository

  1. Dawid says:

    Trochę późno, ale jak to mawiają lepiej późno niż wcale…
    Uważaj “Z pomocą nadchodzi domyślnie włączony mechanizm Automatic Workload Repository (AWR)” – domyślnie nie znaczy, że ten mechanizm jest ogólnodostępny. Niestety AWR wymaga licencji na Oracle Diagnostic Pack.

    http://www.oracle.com/technetwork/oem/pdf/511880.pdf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: