ASH report
SQL> @?/rdbms/admin/ashrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
old 2: lower( (case when '&&report_type' IS NULL
new 2: lower( (case when 'html' IS NULL
old 3: then '&&default_report_type'
new 3: then 'html'
old 4: when '&&report_type' <> 'text'
new 4: when 'html' <> 'text'
old 6: else '&&report_type' end) ) report_type
new 6: else 'html' end) ) report_type
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
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2004825418 ORACLE 1 oracle
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2004825418 1 ORACLE oracle khyup
Defaults to current database
Using database id: 2004825418
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: 17-Mar-23 22:26:30 [ ###### mins in the past]
Latest ASH sample available: 26-Oct-23 20:15:20 [ 0 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: 10/26/23 19:50
Report begin time specified: 10/26/23 19:50
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 10
Report duration specified: 10
Using 26-Oct-23 19:50:00 as report begin time
Using 26-Oct-23 20:00:00 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 AWR_(PDB/ROOT)_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 CONTAINER name (eg: NAME from V$PDBS) report target:
Defaults to NULL: (% and _ wildcards allowed)
CONTAINER report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_1026_2000.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ashrpt_1_1026_2000.html
Using the report name ashrpt_1_1026_2000.html
Summary of All User Input
-------------------------
Format : HTML
DB Id : 2004825418
Inst num : 1
Begin time : 26-Oct-23 19:50:00
End time : 26-Oct-23 20:00:00
Slot width : Default
Report targets : 0
Report name : ashrpt_1_1026_2000.html
AWR report
SQL> @?/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: html
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
2004825418 ORACLE 1 oracle oracle
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2004825418 1 ORACLE oracle khyup
Using 2004825418 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: 7
Listing the last 7 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
oracle ORACLE 1 26 Oct 2023 19:24 1
2 26 Oct 2023 19:45 1
3 26 Oct 2023 19:47 1
4 26 Oct 2023 19:48 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_1_1_2.html
Using the report name awrrpt_1_1_2.html
AWR Diff report
SQL> @?/rdbms/admin/awrddrpt.sql
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
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
2004825418 2004825418 ORACLE 1 1 oracle
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2004825418 1 ORACLE oracle khyup
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2004825418 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
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:
Listing all Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
oracle ORACLE 1 26 Oct 2023 19:24 1
2 26 Oct 2023 19:45 1
3 26 Oct 2023 19:47 1
4 26 Oct 2023 19:48 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
First Begin Snapshot Id specified: 1
Enter value for end_snap: 2
First End Snapshot Id specified: 2
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2004825418 1 ORACLE oracle khyup
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2004825418 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
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_days2:
Listing all Completed Snapshots
1 26 Oct 2023 19:24 1
2 26 Oct 2023 19:45 1
3 26 Oct 2023 19:47 1
4 26 Oct 2023 19:48 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 3
Second Begin Snapshot Id specified: 3
Enter value for end_snap2: 4
Second End Snapshot Id specified: 4
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1_1_3.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrdiff_1_1_1_3.html
Using the report name awrdiff_1_1_1_3.html
Devops Engineer