Popular Posts

Generating different types of AWR Reports

Generating Automatic Workload Repository Reports

AWR- Automatic Workload Repository report shows the data captured between two point in time (Two snapshots),Here we are discussing about AWR report generation using sqlplus. We have six sql files to generate AWR reports, means in six different way we can generate this report.

Types of AWR sqls

■ The awrrpt.sql :- Generates an HTML or text report that displays statistics for a range of snapshot Ids.
■ The awrrpti.sql :- Generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
■ The awrsqrpt.sql :- Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. 
Run this report to inspect or debug the performance of a SQL statement.
■ The awrsqrpi.sql :- Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. 
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance
■ The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.
■ The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

Here awrrpt.sql is commonly used and awrddrpt.sql is very much use full when have set baseline snapshots(to compare good and bad time)

To run this report you should have DBA role granted.

Using awrrpt.sql (Common Report)

To generate the report (HTML or text) for a range of snapshot Ids, run the awrrpt.sql script at sqlplus.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for
the workload repository report.
Enter value for begin_snap: 150
Enter value for end_snap: 160
Accept the default report name or enter a report name. 
The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrrpt_1_150_160
The workload repository report is generated.

Using awrrpti.sql (Instance specific)

To specify a database and instance before entering a range of snapshot Ids, run the
awrrpti.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
First, specify whether you want an HTML or a text report. After that, a list of the
database identifiers and instance numbers displays, similar to the following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main dlsun1690
3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num)at the prompts.
Enter value for dbid: 3309173529
Using 3309173529 for database Id
Enter value for inst_num: 1
Next you are prompted for the number of days and snapshot Ids, similar to the awrrpt.sql script, before the text report is generated.

Using awrsqrpt.sql (AWR of specific SQL statement)

To generate an HTML or text report for a particular SQL statement, run the
awrsqrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for
the workload repository report.
Enter value for begin_snap: 146
Enter value for end_snap: 147
Specify the SQL Id of a particular SQL statement to display statistics.
Enter value for sql_id: 2b064ybzkwf1y
Next, accept the default report name or enter a report name. The default name is
accepted in the following example:
Enter value for report_name:
Using the report name awrsqlrpt_1_146_147.txt
The workload repository report is generated.

Using awrsqrpi.sql (AWR SQL Report-Instance specific)

To specify a database and instance before entering a particular SQL statement Id, run
the awrsqrpi.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Next, a list of the database identifiers and instance numbers displays, similar to the
following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main dlsun1690
3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num)
at the prompts.
Enter value for dbid: 3309173529
Using 3309173529 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Next you are prompted for the number of days, snapshot Ids, SQL Id and report name,
similar to the awrsqrpt.sql script, before the text report is generated.

Using awrddrpt.sql (Comparison Report)

To compare detailed performance attributes and configuration settings between two
time periods, run the awrddrpt.sql script at the SQL prompt to generate an HTML
or text report:
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids for the first time
period.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for
the first time period.
Enter value for begin_snap: 102
Enter value for end_snap: 103
Next, specify the number of days for which you want to list snapshot Ids for the
second time period.
Enter value for num_days2: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for
the second time period.
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
Next, accept the default report name or enter a report name. The default name is
accepted in the following example:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
The workload repository report is generated.

Running the awrddrpi.sql Report
To specify a database and instance before selecting time periods to compare, run the
awrddrpi.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Next, a list of the database identifiers and instance numbers displays, similar to the
following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main dlsun1690
3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num)
for the first time period at the prompts.
Enter value for dbid: 3309173529
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days for which you want to list snapshot Ids for the first time
period.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for
the first time period.
Enter value for begin_snap: 102
Enter value for end_snap: 103
Next, enter the values for the database identifier (dbid) and instance number (inst_
num) for the second time period at the prompts.
Enter value for dbid2: 3309173529
Using 3309173529 for Database Id for the second pair of snapshots
Enter value for inst_num2: 1
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days for which you want to list snapshot Ids for the second time
period.
Enter value for num_days2: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for
the second time period.
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
Next, accept the default report name or enter a report name. The default name is
accepted in the following example:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt

Hope this helps you...

3 comments: