Database Reference
In-Depth Information
•
Retention period
: How long (in minutes) the snapshots are retained. The minimum and
maximum values are 1 day and 100 years, respectively. If 0 is specified, snapshots are retained
permanently. The default value is 7 days in version 10.2, and 8 days from version 11.1 onward.
•
Top SQL statements
: The number of SQL statements that are considered top consumers
for every snapshot. Since several categories of consumers are considered (for example, top
elapsed time, top CPU utilization, and top parse calls), the actual number of SQL statements
that are stored for every snapshot can be higher than the value specified with this parameter.
The parameter accepts values between 30 and 50,000 as well as
DEFAULT
and
MAXIMUM
. Note
that
DEFAULT
means either 30 or 100, depending on the flush level used for creating the
snapshot (refer to the “Taking Snapshots” section that follows).
COLOreD SQL ID
in case you want to make sure that information about a specific sQl statement is captured in every snapshot
(that is, independent of whether it's a top consumer), from version 11.1 onward you can mark that statement's
sQl id as
colored
. to mark and unmark a sQl id as colored, the
dbms_workload_repository
package provides
the
add_colored_sql
and
remove_colored_sql
procedures, respectively. note that both procedures accept a
parameter specifying the sQl id on which the operation has to be carried out.
to know which sQl statements were marked as colored and when that happened, you can query
dba_hist_
colored_sql
and, in a 12.1 multitenant environment,
cdb_hist_colored_sql
views.
The following query shows how to display the current value of the parameters (notice that these are the default
values used from version 11.1 onward):
SQL> SELECT snap_interval, retention, topnsql
2 FROM dba_hist_wr_control;
SNAP_INTERVAL RETENTION TOPNSQL
----------------- ----------------- -------
+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
You can change the default configuration by calling the
modify_snapshot_settings
procedure of the
dbms_
workload_repository
package. For example, the following call sets the interval to 20 minutes and the retention to 35 days:
dbms_workload_repository.modify_snapshot_settings(
interval => 20,
retention => 35*60*24,
topnsql => 'DEFAULT'
);
The data belonging to AWR is stored in the
sysaux
tablespace. The amount of storage required for it strongly
depends on how the three parameters are set. In general, though, every snapshot takes at least 1 megabyte. If you
want to know how much space is currently used, you can run the following query:
SELECT space_usage_kbytes
FROM v$sysaux_occupants
