IT 研究室 ( 前IT DBA's 資訊站): 如何採集(AWR/ ASH) report

1.先得知ADDM snapshot 的時間區間
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot
where
begin_interval_time between to_timestamp('2018-08-04 12:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2018-08-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
order by begin_interval_time ;

2.如何使用command line產出AWR報告了
登入sqlplus後執行 @?/rdbms/admin/ashrpt.sql

ref:
https://oraclespin.com/2009/09/27/how-to-generate-awr-report/

3.如何使用command line產出ASH(Active Session History)報告

[oracle@db01 ~]$ cd /home/oracle/

[oracle@db01 ~]$ . oraenv

ORACLE_SID = [oracle] ? orcl

The Oracle base has been set to /u01/app/oracle

[oracle@db01 ~]$ export ORACLE_SID=orcl1

[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 12 15:52:53 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productio

With the Partitioning, Real Application Clusters, Automatic Storage Managem

ent, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> @?/rdbms/admin/ashrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

xxxxxxxxx ORCL 1 orcl1

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

xxxxxxxxx 2 ORCL orcl2 db02

* xxxxxxxxx 1 ORCL orcl1 db01

Defaults to current database

Using database id: xxxxxxxxxx

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: 03-Aug-18 23:59:28 [ 12469 mins in the pas

Latest ASH sample available: 12-Aug-18 15:48:15 [ 0 mins in the pas

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]

-- mples: 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

-- mples: -1:15 (SYSDATE - 1 Hr 15 Mins)

-- -25 (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 07/05/18 14:00:00

Report begin time specified: 07/05/18 14:00:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 120 如果要收集連續兩小時區間的information, 這邊就指定為120 min

Report duration specified: 120

Using 05-Aug-18 14:00:00 as report begin time

Using 05-Aug-18 16: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 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: