Troubleshooting
Problem
Steps for creating Automatic Workload Repository (AWR) reports in Oracle 10g
Symptom
This solution illustrates the steps for creating the Automatic Workload
Repository (AWR) reports in Oracle
10g.
===================================================
PART:
Warehouse Management System 7.5SP1 Platform
DATABASE: Oracle 10.1.0
WEB
BROWSER: Internet Explorer - 6.0 sp1
=======================================
Cause
Resolving The Problem
Step1:
----------
Run the following SQL and note down the original
settings, ie, Snapshot Interval and Retential Interval:
Select extract(
day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract(
minute from snap_interval ) "Snapshot Interval", extract( day from retention)
*24*60+ extract( hour from retention) *60+ extract( minute from retention )
"Retention Interval"
from dba_hist_wr_control;
Now run the following
PL/SQL block in your SQL session. This will reduce the snapshot interval to 10
mins and retention days to 30 days.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention =>
43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 10); -- Minutes. Current value retained if
NULL.
END;
/
NOTE:
> It is important to follow Step1 BEFORE you
start the testing.
> You can keep the snapshot interval according to the
duration of your test. Say if the duration is 5 mins then you can keep the
interval as 5 mins.
> Please revert back to the original settings.
Step2:
----------
Copy awrrpti.sql and awrinput.sql to
some temp dir. You can find these files
@<ORACLE_HOME>/rdbms/admin/l
Step3:
----------
Set the
following attributes in awrrpti.sql file:
define
report_type='html';
define begin_snap = <snap_id> -- for begin and end
IDs values see below
define end_snap = <snap_id>
define
report_name = 'C:\awrrpt_1_3541_3543.html';
For begin_snap and end_snap
values, please run the following query:
select * from dba_hist_snapshot
where dbin=<dbid> order by snap_id desc;
begin_snap = snap_id for which
End_Interval_Time = Start time of the testing.
End_snap = snap_id for which
Begin_Interval_Time = End time of the testing.
You can get the dbid by
using the following query:
select distinct
(case when cd.dbid
= wr.dbid and
cd.name = wr.db_name
and
ci.instance_number = wr.instance_number
and
ci.instance_name = wr.instance_name
then '* '
else ' '
end) || wr.dbid dbbid
, wr.instance_number instt_num
, wr.db_name dbb_name
,
wr.instance_name instt_name
, wr.host_name host
from
dba_hist_database_instance wr, v$database cd, v$instance
ci;
Step4:
----------
Now run the awrrpti.sql in your sql
session, it will prompt you to enter the "dbid". Select the relevant "dbid"
from the list and then enter the corresponding inst_num and press enter. The
system will generate the report at the location specified in
awrrpt.sql.
References:
-------------------
You can refer to
Solution# 15753 for creating and dropping snapshots.
Historical Number
PRI49729
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21540182