oracle sql trace - ghdrako/doc_snipets GitHub Wiki

SQL Trace to narzędzie diagnostyczne w Oracle Database, które umożliwia śledzenie wykonania zapytań SQL na poziomie sesji. Działa poprzez rejestrowanie szczegółowych informacji o wykonaniu zapytań, takich jak:

  • Czas wykonania zapytań.
  • Ilość operacji wejścia/wyjścia (I/O).
  • Liczba przeczytanych, zapisanych i zmodyfikowanych bloków.
  • Wykorzystanie pamięci PGA/SGA.
  • Sposób wykonania zapytań (czy używa indeksów, czy pełnego skanowania tabel).

Pliki śledzenia SQL Trace można później analizować przy użyciu narzędzia TKPROF lub innych metod.

Włączenie SQL Trace dla sesji

Włączenie SQL Trace dla bieżącej sesji

ALTER SESSION SET sql_trace = TRUE;
...
ALTER SESSION SET sql_trace = FALSE;

Włączenie SQL Trace dla innej sesji (DBA)

SELECT sid, serial#, username, program
FROM v$session
WHERE username = 'HR';

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);
...
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, FALSE);

Włączenie SQL Trace dla całej bazy danych (uważnie!)

ALTER SYSTEM SET sql_trace = TRUE;

ALTER SYSTEM SET sql_trace = FALSE;

Lokalizacja plikow SQL Trace

SHOW PARAMETER user_dump_dest;

Przykładowa ścieżka do pliku:

/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_12345.trc

Analiza wyników SQL Trace za pomocą TKPROF

Przetworzenie pliku .trc za pomocą TKPROF

W terminalu lub SQL*Plus wykonujemy:

tkprof orcl_ora_12345.trc output_file.txt sort=execpu

Opcja sort=execpu oznacza, że wyniki zostaną posortowane według użycia CPU.

Struktura raportu TKPROF

Po przetworzeniu pliku .trc otrzymujemy raport zawierający m.in.:

Czas wykonania SQL (elapsed time, CPU time).
Liczbę odczytanych bloków (consistent gets, physical reads).
Plan wykonania (Execution Plan).

Przykładowy fragment raportu:

SELECT emp_name FROM employees WHERE emp_id = :1;

call     count       cpu    elapsed       disk      query    current     rows
------- ------  -------- ----------  -------- ---------- ----------  -------
Parse        1      0.01       0.02         0         1         0         0
Execute      1      0.00       0.01         0         0         1         1
Fetch        1      0.02       0.03         2        10         0         1
------- ------  -------- ----------  -------- ---------- ----------  -------
total        3      0.03       0.06         2        11         1         1
  1. Interpretacja raportu TKPROF

Podstawowe wskaźniki do analizy:

Parametr Znaczenie
cpu Czas CPU zużyty przez zapytanie.
elapsed Całkowity czas wykonania zapytania.
disk Liczba operacji odczytu z dysku (duża wartość = problem).
query Liczba logicznych odczytów bloków (wysokie wartości mogą wskazywać na potrzebę indeksów).
current Liczba aktualizacji i blokad (wysoka wartość może sugerować konflikt współbieżności).
rows Liczba zwróconych wierszy (jeśli niski wynik przy dużym query, zapytanie jest nieefektywne).

Przykładowe interpretacje:

  • Jeśli disk jest wysoki → zapytanie powoduje dużo operacji I/O → można dodać indeksy lub poprawić struktury tabel.
  • Jeśli cpu jest wysoki, a elapsed niski → problemem jest złożoność obliczeniowa zapytania.
  • Jeśli rows jest niski, a query wysoki → zapytanie skanuje zbyt wiele danych bez potrzeby.