ASH基於v$session每秒進行一次取樣，記錄當前活動會話等待事件（不活動的不取樣）
ASH透過記憶體所記錄的資料是有限的，為了儲存歷史記錄，引入了AWR，ASH資訊取樣被寫到AWR中，ASH寫滿了以後會將資訊寫到AWR負載庫，ASH資料完全寫出是不可能的，一般只寫收集資料量的10%，

ASH記憶體中的資訊透過v$active_session_history查詢，而已經寫出到AWR負載庫的ASH資訊，透過AWR基礎表（wrh$active_session_hist)查詢。
下面舉一個例子生成ASH報告

ASH以v$active_session_history檢視為基礎，生成ASH報表

ASH報告時間精確到分鐘，提供比AWR更詳細的歷史會話資訊，作為對AWR的補充

HZH@hzh%11gR2>@?/rdbms/admin/ashrpt.sql

Current Instance

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

DB Id DB Name Inst Num Instance

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

609310700 HZH 1 hzh

Specify the Report Type

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

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

Defaults to 'html'

Enter value for report_type: text

Type Specified: text

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

* 609310700 1 HZH hzh SLES-10-01

* 609310700 1 HZH hzh bogon

Defaults to current database

Using database id: 609310700

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: 16-Oct-14 22:45:10 [ 667 mins in the past]

Latest ASH sample available: 17-Oct-14 09:51:38 [ 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: 09:30

Report begin time specified: 09:30

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 17-Oct-14 09:30:00 as report begin time

Using 17-Oct-14 09:51:54 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_1017_0951.txt. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/ash_kingsql

Using the report name /home/oracle/ash_kingsql

Summary of All User Input

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

Format : TEXT

DB Id : 609310700

Inst num : 1

Begin time : 17-Oct-14 09:30:00

End time : 17-Oct-14 09:51:54

Slot width : Default

Report targets : 0

Report name : /home/oracle/ash_kingsql

ASH Report For HZH/hzh

DB Name DB Id Instance Inst Num Release RAC Host

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

HZH 609310700 hzh 1 11.2.0.1.0 NO SLES-10-01

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

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

1 858M (100%) 540M (62.9%) 300M (35.0%) 2.0M (0.2%)

Analysis Begin Time: 17-Oct-14 09:30:00

Analysis End Time: 17-Oct-14 09:51:54

Elapsed Time: 21.9 (mins)

Begin Data Source: V$ACTIVE_SESSION_HISTORY

End Data Source: V$ACTIVE_SESSION_HISTORY

Sample Count: 246

Average Active Sessions: 0.19

Avg. Active Session per CPU: 0.19

Report Target: None specified

//這個位置以下的內容都是重點！

Top User Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Avg Active

Event Event Class % Event Sessions

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

CPU + Wait for CPU CPU 34.15 0.06

log buffer space Configuration 2.85 0.01

log file switch (checkpoint incompl Configuration 1.63 0.00

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

Top Background Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Avg Active

Event Event Class % Activity Sessions

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

log file parallel write System I/O 24.80 0.05

CPU + Wait for CPU CPU 16.26 0.03

db file async I/O submit System I/O 9.35 0.02

control file parallel write System I/O 4.07 0.01

Log archive I/O System I/O 2.03 0.00

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

Top Event P1/P2/P3 Values DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Event % Event P1 Value, P2 Value, P3 Value % Activity

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

Parameter 1 Parameter 2 Parameter 3

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

log file parallel write 24.80 "1","3","1" 8.54

files blocks requests

"1","4","1" 3.25

"1","1","1" 1.63

db file async I/O submit 9.35 "20","0","0" 1.63

requests interrupt timeout

"15","0","0" 1.22

"23","0","0" 1.22

control file parallel write 4.07 "2","21","2" 1.22

files block# requests

Log archive I/O 2.03 "1","256","4294967295" 2.03

count intr timeout

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

Top Service/Module DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Service Module % Activity Action % Action

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

SYS$BACKGROUND UNNAMED 59.35 UNNAMED 59.35

SYS$USERS SQL*Plus 36.59 UNNAMED 36.59

sqlplus@SLES-10-01 (TNS 2.03 UNNAMED 2.03

UNNAMED 1.22 UNNAMED 1.22

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

Top Client IDs DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top SQL Command Types DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

-> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs

with the given SQL Command Type found over all the ASH samples

in the analysis period

Distinct Avg Active

SQL Command Type SQLIDs % Activity Sessions

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

PL/SQL EXECUTE 1 20.73 0.04

INSERT 2 15.04 0.03

SELECT 9 3.66 0.01

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

Top Phases of Execution DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Avg Active

Phase of Execution % Activity Sessions

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

SQL Execution 39.43 0.07

PLSQL Execution 3.66 0.01

Hard Parse 2.03 0.00

Parse 2.03 0.00

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

Top SQL with Top Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Sampled #

SQL ID Planhash of Executions % Activity

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

Event % Event Top Row Source % RwSrc

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

764a3gsmzf4r1 N/A 1 20.73

CPU + Wait for CPU 17.89 ** Row Source Not Available ** 17.89

** SQL Text Not Available **

N/A 1 20.73

log buffer space 2.44 ** Row Source Not Available ** 2.44

** SQL Text Not Available **

fph62wwsqnnrk N/A 28 14.63

CPU + Wait for CPU 12.60 ** Row Source Not Available ** 12.60

INSERT INTO HZH3 VALUES (:B1 )

N/A 28 14.63

log file switch (checkpoint in 1.22 ** Row Source Not Available ** 1.22

INSERT INTO HZH3 VALUES (:B1 )

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

Top SQL with Top Row Sources DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Sampled #

SQL ID PlanHash of Executions % Activity

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

Row Source % RwSrc Top Event % Event

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

764a3gsmzf4r1 N/A 1 20.73

** Row Source Not Available ** 20.73 CPU + Wait for CPU 17.89

** SQL Text Not Available **

fph62wwsqnnrk N/A 28 14.63

** Row Source Not Available ** 14.63 CPU + Wait for CPU 12.60

INSERT INTO HZH3 VALUES (:B1 )

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

Top SQL using literals DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top Parsing Module/Action DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Module Action % Activ Event

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

sqlplus@SLES-10-01 (TNS V1-V3) 2.03 CPU + Wa

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

Top PL/SQL Procedures DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

-> 'PL/SQL entry subprogram' represents the application's top-level

entry-point(procedure, function, trigger, package initialization

or RPC call) into PL/SQL.

-> 'PL/SQL current subprogram' is the pl/sql subprogram being executed

at the point of sampling . If the value is 'SQL', it represents

the percentage of time spent executing SQL for the particular

plsql entry subprogram

PLSQL Entry Subprogram % Activity

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

PLSQL Current Subprogram % Current

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

SYS.DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT 2.03

SQL 2.03

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

Top Java Workload DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top Call Types DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

Call Type Count % Activity Avg Active

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

V8 Bundled Exec 95 38.62 0.07

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

Top Sessions DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

-> '# Samples Active' shows the number of ASH samples in which the session

was found waiting for that particular event. The percentage shown

in this column is calculated with respect to wall clock time

and not total database activity.

-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH

when the session was waiting for that particular event

-> For sessions running Parallel Queries, this section will NOT aggregate

the PQ slave activity into the session issuing the PQ. Refer to

the 'Top Sessions running PQs' section for such statistics.

Sid, Serial# % Activity Event % Event

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

User Program # Samples Active XIDs

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

49, 25 36.59 CPU + Wait for CPU 31.71

HZH sqlplus@SLES-10-01 (TNS V1-V3) 78/1,314 [ 6%] 31

log buffer space 2.44

6/1,314 [ 0%] 6

log file switch (checkpoint in 1.63

4/1,314 [ 0%] 1

11, 1 31.30 log file parallel write 24.80

SYS oracle@SLES-10-01 (LGWR) 61/1,314 [ 5%] 0

CPU + Wait for CPU 5.28

13/1,314 [ 1%] 0

10, 1 9.76 db file async I/O submit 9.35

SYS oracle@SLES-10-01 (DBW0) 23/1,314 [ 2%] 0

22, 1 4.88 CPU + Wait for CPU 3.25

SYS oracle@SLES-10-01 (ARC3) 8/1,314 [ 1%] 0

18, 5 4.47 CPU + Wait for CPU 2.44

SYS oracle@SLES-10-01 (ARC0) 6/1,314 [ 0%] 0

Log archive I/O 1.22

3/1,314 [ 0%] 0

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

Top Blocking Sessions DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

-> Blocking session activity percentages are calculated with respect to

waits on enqueues, latches and "buffer busy" only

-> '% Activity' represents the load on the database caused by

a particular blocking session

-> '# Samples Active' shows the number of ASH samples in which the

blocking session was found active.

-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH

when the blocking session was found active.

Blocking Sid (Inst) % Activity Event Caused % Event

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

User Program # Samples Active XIDs

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

11, 1( 1) 5.28 log buffer space 2.85

SYS oracle@SLES-10-01 (LGWR) 77/1,314 [ 6%] 0

log file switch (checkpoint in 1.63

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

Top Sessions running PQs DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top DB Objects DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top DB Files DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Top Latches DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

No data exists for this section of the report.

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

Activity Over Time DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)

-> Analysis period is divided into smaller time slots

-> Top 3 events are reported in each of those slots

-> 'Slot Count' shows the number of ASH samples in that slot

-> 'Event Count' shows the number of ASH samples waiting for

that event in that slot

-> '% Event' is 'Event Count' over all ASH samples in the analysis period

Slot Event

Slot Time (Duration) Count Event Count % Event

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

09:36:00 (2.0 min) 1 control file sequential read 1 0.41

09:38:00 (2.0 min) 3 db file sequential read 2 0.81

CPU + Wait for CPU 1 0.41

09:40:00 (2.0 min) 5 CPU + Wait for CPU 5 2.03

09:42:00 (2.0 min) 2 db file async I/O submit 2 0.81

09:44:00 (2.0 min) 1 CPU + Wait for CPU 1 0.41

09:46:00 (2.0 min) 1 CPU + Wait for CPU 1 0.41

09:48:00 (2.0 min) 232 CPU + Wait for CPU 115 46.75

log file parallel write 61 24.80

db file async I/O submit 21 8.54

09:50:00 (1.9 min) 1 CPU + Wait for CPU 1 0.41

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

End of Report

Report written to /home/oracle/ash_kingsql

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

我的QQ 1749160152

我的郵箱 hongzhuohui@kingsql.com

我的百科洪卓輝

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

【kingsql分享】ASH的研究和分析

相關文章