====== [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>