It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road cut-and-paste from this article instead and the world gets a few more lines of readable code as a result. :)
On a side note, let me re-iterate the importance of increasing the AWR retention defaults. There are a few opinions about the perfect settings but everyone agrees that the defaults are a “lowest common denominator” suitable for demos on laptops but never for production servers. The values below are what I’m currently using.
BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 105 * 24 * 60, -- days * hr/day * min/hr (result is in minutes) interval => 15); -- minutes END; / SQL> select * from dba_hist_wr_control; Pivoting output using Tom Kyte's printtab.... ============================== DBID : 3943732569 SNAP_INTERVAL : +00000 00:15:00.0 RETENTION : +00105 00:00:00.0 TOPNSQL : DEFAULT PL/SQL procedure successfully completed. Elapsed: 00:00:00.10