Database Reference
In-Depth Information
Taking Snapshots
In addition to the snapshots that are automatically created by the database engine, you can manually take snapshots.
This is useful when you want to store information about a specific period of time. To take a snapshot, you have to
call one of the
create_snapshot
subroutines of the
dbms_workload_repository
package. Two subroutines exist: a
function and a procedure. Both of them accept a parameter that is used to specify the flush level (either
TYPICAL
or
ALL
, the former being the default value). If
TYPICAL
is used, the top 30 SQL statements for each category are stored. If
ALL
is used, the top 100 are stored. The only difference between the function and the procedure is that the function
returns the snapshot ID. The following query shows how to create a snapshot with the flush level
ALL
and display the
snapshot ID associated with it:
SQL> SELECT dbms_workload_repository.create_snapshot(flush_level => 'ALL') AS snap_id
2 FROM dual;
SNAP_ID
----------
738
The snapshots stored in AWR are visible in the
dba_hist_snapshot
and, in a 12.1 multitenant environment,
cdb_hist_snapshot
views:
SQL> SELECT begin_interval_time, end_interval_time,
2 decode(snap_level, 1, 'TYPICAL', 2, 'ALL', snap_level) AS snap_level
3 FROM dba_hist_snapshot
4 WHERE snap_id = 738;
BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
------------------------- ------------------------- ----------
22-APR-14 04.00.22.234 PM 22-APR-14 04.06.58.230 PM ALL
Managing Baselines
A baseline is composed of several consecutive snapshots that are marked as a baseline. Two types of baselines exist:
•
Fixed baseline
: A set of consecutive snapshots delimited by a static start snapshot ID and a
static end snapshot ID. You can create as many fixed baselines as necessary.
•
Moving window baseline
: A set of consecutive snapshots covering a specific amount of time
(specified in days) and ending with the most current snapshot. Every database has one
moving window baseline that is used by the database engine for adaptive thresholds (refer
to the
Performance Tuning Guide
manual for additional information). This type of baseline is
available from version 11.1 onward.
Managing Fixed Baselines
To create baselines, the
dbms_workload_repository
package provides several functions and procedures named
create_baseline
. They all implement the same basic functionality, although they differ in two aspects. First, the
start and the end of the baseline can be specified through either two IDs or two dates (the latter is available only
from version 11.1 onward, though). Second, only the functions return the ID associated with the new baseline. Note