How to Generate AWR, ADDM or ASH Reports using SQL Scripts provided by Oracle?
What is Oracle Automatic Workload Repository? What is Oracle Automatic Database Diagnostic Monitoring? How to tune your Oracle Database? What are the best Oracle Performance Tuning Tools for Oracle Database? How to run extract AWR/ADDM or ASH Reports from Oracle Database?
Performance Tuning has a major role in all over DBA Activities and is quite challenging as well. That doesn't mean that other activities are less critical but this is something that needs real expertise because in a 24/7 working environment where thousands of users are connecting to a single database (For instance, a banking system where more than thousands of transactions are made in a minute), performance of the database is everything. Imagine yourself doing an ATM transaction and for every single command that you are giving, it is taking 2 min at least to respond back, how would you feel then?
Since nowadays, we have a lot alternatives for balancing the load and increasing the Server I/O but the more we are making, the less we are having. And this will always remain a fact.
To know more about AWR, ADDM or ASH Reports that what they are and what kind of information that these reports provide, please visit my post - Difference between AWR, ADDM and ASH Reports in Oracle Performance Tuning
To know more about AWR, ADDM or ASH Reports that what they are and what kind of information that these reports provide, please visit my post - Difference between AWR, ADDM and ASH Reports in Oracle Performance Tuning
Anyway, let's move ahead with the creation of different Performance Monitoring Reports.
1) AWR Report:
Before creating any single report, make sure snapshot capturing is enable on your database. If not, you can run the below mentioned command to create one:
exec dbms_workload_repository.create_snapshot;
The Report will be generated on the basis of the snapshots taken. Take 4-5 snapshots or run the above mentioned command to create snapshots each in 5 minutes duration at least. Run the below mentioned query to check the snapshots created in your database,
SQL> select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin from dba_hist_snapshot order by 1;
SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
465 1 19/07/17 18:30:50
466 1 19/07/17 18:41:57
467 1 19/07/17 19:07:33
468 1 19/07/17 19:08:30
Now find the below mentioned guidelines to check the creation of AWR Report,
SQL> @?\rdbms\admin\awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1442968888 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1442968888 1 ORCL orcl PARAIN0
Using 1442968888 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 465 19 Jul 2017 18:41 1
466 19 Jul 2017 19:07 1
467 19 Jul 2017 19:08 1
468 19 Jul 2017 19:09 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 465
Begin Snapshot Id specified: 465
Enter value for end_snap: 468
End Snapshot Id specified: 468
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_465_468.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: arw_demo_report
Give your report a name and press ENTER, it will automatically generate a fresh AWR Report for you.
Note:- The Report will be generated at the same location from where you have connected to SQL from your Command Prompt.
To see a Demo of AWR Report generation by the above mentioned activity, please find links below:
a) AWR Report Demo Part-1
b) AWR Report Demo Part-2
c) AWR Report Demo Part-3
will be updated soon..2) ADDM Report:
Below mentioned is the script run to generate ADDM Report
SQL> @?\rdbms\admin\addmrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1442968888 ORCL 1 orcl
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1442968888 1 ORCL orcl PARAIN0
Using 1442968888 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 465 19 Jul 2017 18:41 1
466 19 Jul 2017 19:07 1
467 19 Jul 2017 19:08 1
468 19 Jul 2017 19:09 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 465
Begin Snapshot Id specified: 465
Enter value for end_snap: 468
End Snapshot Id specified: 468
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_465_468.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: addr_demo
Give your report a name and press ENTER, it will automatically generate a fresh AWR Report for you.
Note:- The Report will be generated at the same location from where you have connected to SQL from your Command Prompt.
To see a Demo of ADDR Report generation by the above mentioned activity, please find links below:
a) ADDR Report Demo Part-1
b) ADDR Report Demo Part-2
c) ADDR Report Demo Part-3
will be updated soon...3) ASH Report:
SQL> @?\rdbms\admin\ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1442968888 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1442968888 1 ORCL orcl PARAIN0
Defaults to current database
Using database id: 1442968888
Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 19-Jul-17 18:30:44 [ 109 mins in the past]
Latest ASH sample available: 19-Jul-17 20:18:05 [ 1 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time:
Report begin time specified:
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration:
Report duration specified:
Using 19-Jul-17 20:04:45 as report begin time
Using 19-Jul-17 20:19:49 as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0719_2019.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ash_report
Give your report a name and press ENTER, it will automatically generate a fresh AWR Report for you.
Note:- The Report will be generated at the same location from where you have connected to SQL from your Command Prompt.
To see a Demo of ASH Report generation by the above mentioned activity, please find links below:
a) ASH Report Demo Part-1
b) ASH Report Demo Part-2
c) ASH Report Demo Part-3
will be updated soon...
No comments:
Post a Comment