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
 
Search WWH ::




Custom Search