AWR en SQL #oracle #sql

AWR en SQL.md

SYSDATE – 0 = AUJOURD’HUI SYSDATE – 1 = HIER SYSDATE – 2 = AVANT HIER etc…

set linesize 2048
set pagesize 0
set trimout on
set trimspool on
spool awr.html
SELECT *
FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_html (
(SELECT dbid FROM v$database),
(SELECT instance_number FROM v$instance),
(SELECT MIN (snap_id)
FROM DBA_HIST_SNAPSHOT
WHERE TRUNC (BEGIN_INTERVAL_TIME) = TRUNC (SYSDATE - 0 )),
(SELECT MAX (snap_id)
FROM DBA_HIST_SNAPSHOT
WHERE TRUNC (BEGIN_INTERVAL_TIME) = TRUNC (SYSDATE - 0 )),
0
));
spool off

Les explications…

SQL> select dbid from v$database;

3728230661

SQL> select instance_number from v$instance;

1

select snap_id, BEGIN_INTERVAL_TIME from DBA_HIST_SNAPSHOT where dbid = '3728230661' and instance_number = '1' order by snap_id;

blah blah (Tout les snaps...)

select min(snap_id), max(snap_id) from DBA_HIST_SNAPSHOT where trunc(BEGIN_INTERVAL_TIME) = trunc(sysdate);

124 140

SQL>

SELECT *
FROM table (DBMS_WORKLOAD_REPOSITORY.awr_report_html (3728230661,
1,
124,
140,
0));