[Oracle] ASH, AWR, AWR Diff report 생성 방법

HYEOB KIM·2023년 10월 26일
1

Oracle

목록 보기
55/58
post-custom-banner

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
profile
Devops Engineer
post-custom-banner

0개의 댓글