CODEWITHSUNDEEP
oracle-databaseperformanceawr
Aldur
Aldur

Reputation: 3145

How do you create an Oracle Automatic Workload Repository (AWR) report?

How do you create an Oracle Automatic Workload Repository (AWR) report?

Upvotes: 1

Views: 5646

Answers (4)

Dinesh vishe
Dinesh vishe

Reputation: 3598

  1. conn / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify the Report Type

AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.

'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report

Enter value for report_type:
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual

Type Specified: html

old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual



old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual



Current Instance

2. you can schedule report by email alert also.

Upvotes: 0

Curious Techie
Curious Techie

Reputation: 195

To generate AWR report follow below steps :

  1. Take begin snap id

    set serveroutput on; DECLARE v_snap_id number ; begin v_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; dbms_output.put_line(v_snap_id); end; /

  2. Run your batch or the program you want to monitor.

  3. Take end snap id

    set serveroutput on; DECLARE v_snap_id number ; begin v_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; dbms_output.put_line(v_snap_id); end; /

  4. Go to oracle directory. e.g. in my case

    cd C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin

  5. go to sqlplus promt

    sqlplus dbusername/dbpassword@host:port/dbenv

  6. run @awrrpt command

  7. It will ask for format of the report, default is html.

  8. provide no of days, if you dont remember your snap id

  9. enter begin snap

  10. enter end snap

  11. Give report name and press enter

Your report will be generated in "admin" e.g. in my case

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin

Upvotes: 2

user330315
user330315

Reputation:

You can use dbms_workload_repository package without the need to log into the server itself.

For a text report, use e.g.:

select output 
from table(dbms_workload_repository.awr_report_text(1557521192, 1, 5390, 5392);

Or to get a HTML report, use awr_report_text() instead.

The first paramter is the DBID which can be obtained using: 

select dbid from v$database

The second one is the instance number. Only relevant for a RAC environment.

And the last two parameters are the IDs of the start and end snapshot. The available snapshots can be obtained using:

select snap_id,
       begin_interval_time
       end_interval_time
from dba_hist_snapshot
order by begin_interval_time desc;

Especially for the HTML return - which returns a CLOB - you must configure your SQL client to properly display the output. In SQL*Plus you would use set long

Upvotes: 1

Aldur
Aldur

Reputation: 3145

sqlplus into to Oracle as the DBA users. Run the report sql. Answer the questions prompted by the report to narrow down the time period

sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

The script will ask you some questions so you get a report for the time period you are interested in.

Upvotes: 1

Related Questions