Databases Reference
In-Depth Information
We can change the parameter online with the following statement without shutting down
the database:
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;
How to do it...
The following steps demonstrate use of AWR:
1.
To make a manual snapshot using AWR, we use the following stored procedure:
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();
With the default settings in place, AWR creates a snapshot every hour, and the
data collected are stored for seven days.
2.
To modify the interval or the grace period of the snapshots, we can use the
modify_snapshot_settings
procedure, as shown:
EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval =>
30);
EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention
=> 21600);
3.
In AWR, we can also create a baseline to compare performances. A baseline is a set
of snapshots which will be held to compare with the same kind of data in the future.
We could have, for example, a baseline for the daily transactional work and a
baseline for a batch job or a peak (quarter end). We can define a baseline indicating
the start and end snapshots to be used, and we can name it:
EXEC DBMS_WORKLOAD_REPOSITORY.create_baseline(Start_snap_id => 1,
end_snap_id => 11, baseline_name => 'Friday off-peak');
4.
To generate a report, we will use the
awrrpt.sql
script, located in the
$ORACLE_
HOME/rdbms/admin
folder. The script will ask to choose the output format (text or
HTML) and the number of days to use to filter the snapshots.
5.
Then they will be presented the list of the snapshots, according to the parameter
chosen in the previous step, and we are asked for the first and the last snapshot to
be used. The last question is about the name of the file to generate the output to.
The report generated is very similar to the Statspack report.
How it works...
As with Statspack, even AWR collects data and statistics from the database and stores them
in tables. With AWR the concept of baseline is introduced.
