Differences

This shows you the differences between two versions of the page.

--- dba:oracle:docs:awr [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
+++ dba:oracle:docs:awr [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:awr to dba:oracle:docs:awr dodger
@@ Line 1: / Line 1: @@
+====== [DOC] AWR ======
+====== Configuration ======
+===== Actual config =====
+<code sql>
+select * from dba_hist_wr_control ;
+</code>
+or, only timings:
+<code sql>
+select
+       extract( day from snap_interval) *24*60+
+       extract( hour from snap_interval) *60+
+       extract( minute from snap_interval ) "Snapshot Interval",
+       extract( day from retention) *24*60+
+       extract( hour from retention) *60+
+       extract( minute from retention ) "Retention Interval"
+from dba_hist_wr_control
+/
+</code>
+===== retention =====
+==== modify ====
+Modify, units are minutes:
+<code sql>
+BEGIN
+DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
+  retention =>  20160);
+END;
+/
+</code>
+===== frequency =====
+Modify, units are minutes:
+<code sql>
+BEGIN
+DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
+  interval  =>  60);
+END;
+/
+</code>
+===== Number of SQL's captured on each snapshot =====
+Modify, units are minutes:
+<code sql>
+BEGIN
+DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
+  topnsql  =>  100);
+END;
+/
+</code>
+====== Basic queries ======
+===== List AWR snapshots =====
+<code sql>
+select
+  DBID,
+  snap_id,
+  snap_level,
+  to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
+from
+   dba_hist_snapshot
+order by 1;
+</code>
+===== View SQL_TEXT of selected SQL_ID =====
+<code sql>
+select SQL_TEXT from WRH$_SQLTEXT where SQL_ID='<SQL_ID>';
+</code>