oracle awr - ghdrako/doc_snipets GitHub Wiki
AWR snapshots are by default collected on a regular basis, usually every hour and by default kept for 8 days (on 12c). You can even select to collect a snapshot manually.
SQL> exec dbms_workload_repository.create_snapshot();
The data is kept in the AWR repository, which is in SYSAUX . You can change both the INTERVAL and the RETENTION (the period of time that the snapshots are kept).
If you want to disable them completely you can set the snapshot interval to 0.
SQL> column snap_interval format a20
SQL> column retention format a20
SQL> select snap_interval, retention, topnsql from dba_hist_wr_control;
SQL> SNAP_INTERVAL RETENTION TOPNSQL
-------------------- -------------------- -------------
+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
So in this case We collect snapshots every hour, keep them for 8 days and collect detailed information on the DEFAULT number of Top SQLs, which in this case is 30.
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>11520,interval=>60, topnsql=>60);
The RETENTION value and the INTERVAL value are both in minutes.
SQL> select snap_interval, retention, topnsql from dba_hist_wr_control;
SNAP_INTERVAL RETENTION TOPNSQL
-------------------- -------------------- ----------
+00000 01:00:00.0 +00008 00:00:00.0 60
Instead of collecting the default value of Top SQLs (30) we are collecting 60. We could also specify the value of TOPNSQL as a string, in this way.
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>11520,interval=>60, topnsql=>'MAXIMUM');
PL/SQL procedure successfully completed.
SQL> select snap_interval, retention, topnsql from
dba_hist_wr_control;
SNAP_INTERVAL RETENTION TOPNSQL
-------------------- -------------------- ----------
+00000 01:00:00.0 +00008 00:00:00.0 MAXIMUM
This is a little bit extreme because this means it will collect ALL SQLs.
Clean up AWR
dba_hist_wr_control
- zawiera ustawienia przechowywania danych AWR (Automatic Workload Repository) w Oracle
select * from dba_hist_wr_control;
| Kolumna | Opis |
|---|---|
| DBID | ID bazy danych (Database ID) |
| RETENTION | Czas przechowywania snapshotów AWR (np. 7 00:00:00) |
| INTERVAL | Czas między snapshotami (np. 1 00:00:00 = co godzinę) |
| TOPNSQL | Liczba TOP SQL przechowywanych per snapshot (np. 30) |
Zmiana retencji
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 20160, -- 14 dni * 24 * 60
interval => 120 -- co 2 godziny
);
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 72000, -- 172800 120 dni; 129600 90 dni; 86400 60 dni; 72000 50dni; 43200 30 dni. Current value retained if NULL.
interval => 60); -- Minutes. Current value retained if NULL.
END;
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval min", -- Co ile minut tworzony jest snapshot AWR
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval min", -- ile minut snapshoty są przechowywane
(extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ))/60/24 "Ret Interv days" -- To samo co wyżej, ale w dniach
from dba_hist_wr_control;
AWR (Automatic Workload Repository)
AWR zbiera dane o obciążeniu systemu i zapytaniach SQL co określony interwał (domyślnie 1 godzina) i przechowuje je w historycznych migawkach (snapshots). Możemy te dane wykorzystać do:
- Identyfikacji wolnych zapytań.
- Analizy zużycia zasobów (CPU, I/O, pamięć).
- Optymalizacji indeksów i statystyk.
- Wykrywania problemów wydajnościowych w bazie danych.
Tworzenie raportu AWR
Aby wygenerować raport AWR, możemy użyć SQL*Plus lub Enterprise Managera.
Metoda 1: SQL*Plus
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
Po uzyskaniu identyfikatorów migawki (snap_id), generujemy raport:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Następnie podajemy:
- Format raportu (HTML/TEXT).
- Zakres czasu (początkowa i końcowa migawka).
- Nazwę pliku wynikowego.
Metoda 2: Enterprise Manager (OEM)
- Logujemy się do Oracle Enterprise Manager.
- Przechodzimy do zakładki Performance → AWR.
- Wybieramy zakres czasu i generujemy raport.
Analiza raportu AWR pod kątem zapytań SQL
-
A. Top 10 SQL by Elapsed Time - Pokazuje najwolniejsze zapytania pod względem czasu wykonania.
- Wartość Elapsed Time (s) wskazuje całkowity czas wykonania SQL.
- Executions oznacza liczbę wywołań
-
B. Top 10 SQL by CPU Time - pokazuje zapytania, które zużywają najwięcej CPU.
- Jeśli jedno zapytanie zajmuje 80% CPU, należy sprawdzić jego plan wykonania i indeksy.
- Możliwe optymalizacje: indeksy, restrukturyzacja zapytania, zmiana algorytmów sortowania.
-
C. Top 10 SQL by I/O Wait Time -nPokazuje zapytania z największymi opóźnieniami na operacjach I/O.
- Jeśli zapytanie często wykonuje operacje FULL TABLE SCAN, warto dodać indeksy.
- Jeśli zapytanie często pobiera duże ilości danych, warto zastosować partycjonowanie tabel.
-
D. SQL Ordered by Buffer Gets - Pokazuje zapytania zużywające najwięcej operacji odczytu z pamięci (buffer cache).
- Jeśli zapytanie często pobiera te same dane, można zastosować caching (Materialized Views, Result Cache).
- Jeśli wartości są odczytywane w sposób nieefektywny, warto sprawdzić statystyki tabel i indeksów.
awr_report.sql
-- Get AWR reports: last day and diffs for 2 dayes
set serveroutput on
alter session set nls_date_format='YYYYMMDD';
column day_before new_value day_before noprint
select to_char((to_date('&1')-1)) as day_before from dual;
column db_id new_value db_id noprint
column inst_id new_value inst_id noprint
column prev_min_id new_value prev_min_id noprint
column prev_max_id new_value prev_max_id noprint
select
min(instance_number) as inst_id,
min(dbid) as db_id,
min(snap_id) as prev_min_id,
max(snap_id) as prev_max_id
from sys.wrm$_snapshot where trunc(end_interval_time)=to_date('&day_before');
column cur_min_id new_value cur_min_id noprint
column cur_max_id new_value cur_max_id noprint
select
min(snap_id) as cur_min_id,
max(snap_id) as cur_max_id
from sys.wrm$_snapshot where trunc(end_interval_time)=to_date('&1');
spool tmp_awr.sql
set linesize 300
set echo off
set term off
set heading off
set feedback off
set verify off
exec dbms_output.put_line('set echo off');
exec dbms_output.put_line('set term off');
exec dbms_output.put_line('set heading off');
exec dbms_output.put_line('set feedback off');
exec dbms_output.put_line('set verify off');
exec dbms_output.put_line('set linesize 1500');
exec dbms_output.put_line('set termout on');
exec dbms_output.put_line('spool dip_&1..html');
exec dbms_output.put_line('select output from table(DBMS_WORKLOAD_REPOSITORY.awr_report_html(&db_id, &inst_id, &cur_min_id, &cur_max_id));');
exec dbms_output.put_line('spool off');
exec dbms_output.put_line('spool dip_&1._&day_before..html');
exec dbms_output.put_line('select output from table(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(&db_id, &inst_id, &cur_min_id, &cur_max_id, &db_id, &inst_id, &prev_min_id, &prev_max_id));');
exec dbms_output.put_line('spool off');
exec dbms_output.put_line('set heading on');
exec dbms_output.put_line('set verify on');
exec dbms_output.put_line('set term on');
exec dbms_output.put_line('set feedback on');
spool off
@tmp_awr.sql
select * from (
select
--'OPERTEST' baza,
--SNAP.END_INTERVAL_TIME,
---STAT.SNAP_ID,
TO_CHAR(SNAP.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') as END_SNAPTIME, PARSING_SCHEMA_NAME
---, STAT.MODULE
,TXT.SQL_ID, PLAN_HASH_VALUE,
--ELAPSED_TIME_DELTA/1000/1000 as ELAPSED_SEC,
trunc((ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000/1000,4) as ELAPSED_SEC_per_exec,
--executions_total,
STAT.EXECUTIONS_DELTA,
---rows_processed_total,
CASE WHEN rows_processed_total = 0 THEN trunc(rows_processed_total)
WHEN rows_processed_total > 0 THEN trunc(rows_processed_total/executions_total)
ELSE rows_processed_total
END AS zwr_wierszy,
--substr(sql_text,0,20),
SQL_TEXT
from
dba_hist_sqlstat stat,
dba_hist_sqltext txt
,dba_hist_snapshot snap
where
stat.sql_id=txt.sql_id
and stat.snap_id=snap.snap_id
and SNAP.END_INTERVAL_TIME > sysdate - 90
and EXECUTIONS_DELTA > 0
--order by zwr_wierszy desc
order by 1
)
where
sql_id='cuf4u8q0dp6qn';
Zapytanie pobiera historyczne statystyki dotyczące konkretnego zapytania SQL (identyfikowanego przez SQL_ID) z bazy Oracle, wykorzystując widoki AWR: DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT i DBA_HIST_SNAPSHOT. Oblicza średni czas wykonania pojedynczego wywołania (ELAPSED_SEC_per_exec) oraz średnią liczbę zwracanych wierszy na wywołanie (zwr_wierszy), a następnie prezentuje pełny tekst SQL wraz z wartością PLAN_HASH_VALUE i czasem wykonania snapshotu dla ostatnich 90 dni. Dzięki temu można analizować wydajność danego SQL-a w przekroju czasowym oraz porównywać plany wykonania.
- end_interval_time odpowiada za czas zakończenia snapshotu, do którego odnoszą się statystyki Dokumentacja Oracle
parsing_schema_namewskazuje schemat, w którym zapytanie zostało pierwotnie parsowane Dokumentacja Oracleelapsed_time_deltaiexecutions_deltato przyrosty łącznego czasu wykonania i liczby wykonań w ramach danego snapshotu Dokumentacja Oracle- Wyrażenie
(elapsed_time_delta / executions_delta) / 1e6zamienia mikrosekundy na sekundy i oblicza średni czas na wykonanie jednego wywołania GitHub rows_processed_totaliexecutions_totalsłużą do wyliczenia średniej liczby zwróconych wierszy na jedno wywołanie; w przypadku braku zwrotów wynik zwraca zero Carlos Sierra's Tools and Tipssql_textpochodzi z widokuDBA_HIST_SQLTEXTi zawiera pełny tekst analizowanego SQL-a Dokumentacja Oraclesnap.end_interval_time > SYSDATE - 90ogranicza dane do ostatnich 90 dni Dokumentacja Oracleexecutions_delta > 0wyklucza snapshoty, w których zapytanie nie było wykonywaneDokumentacja Oracle- Ostateczne
WHERE sql_id = '...'filtruje wynik do interesującego nas konkretnego zapytania. ORDER BY 1sortuje wynik rosnąco według czasu snapshotu.