AI 助理
备案控制台
开发者社区 数据库 文章 正文

ASH Report 解析

2023-07-14 257
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云解析 DNS，旗舰版 1个月
全局流量管理 GTM，标准版 1个月
公共DNS（含HTTPDNS解析），每月1000万次HTTP解析
简介： ASH Report 解析

ASH Report For EDADBT/edadbt

DB Name DB Id Instance Inst num Release RAC Host
EDADBT 1157804911 edadbt 1 12.2.0.1.0 NO p1edadb01t
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size In-memory Area Size
4 9,632M (100%) 7,776M (80.7%) 884M (9.2%) Not Available 0.0M (0.0%)


  Sample Time Data Source
Analysis Begin Time:

05-Sep-18 12:00:00

DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 4532
Analysis End Time:

05-Sep-18 23:59:59

DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 4533

+ V$ACTIVE_SESSION_HISTORY
Elapsed Time: 720.0 (mins)  
Sample Count: 3,307  
Average Active Sessions: 0.77  
Avg. Active Session per CPU: 0.19  
Report Target: None specified  

ASH Report

Top Events

Load Profile

Top SQL

Top PL/SQL

Top Java

Top Call Types

Top Sessions

Top Objects/Files/Latches

Activity Over Time


Back to Top

Top Events

  • Top User Events
  • Top Background Events
  • Top Event P1/P2/P3 Values


Back to Top

Top User Events

Event Event Class % Event Avg Active Sessions
CPU + Wait for CPU CPU 80.22 0.61
db file sequential read User I/O 11.1 0.08
db file parallel read User I/O 2.9 0.02
direct path read temp User I/O 1.57 0.01

这一部分是用户会话的等待事件的信息。列出了采样时段内数据库发生的显著用户等待！

Top Background Events

Event Event Class % Activity Avg Active Sessions
db file async I/O submit System I/O 1.15 0.01

Back to Top Events

Back to Top

Top Event P1/P2/P3 Values

Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
db file sequential read 11.19 1,"1449","1" 0.03 file# block# blocks
db file parallel read 2.9 3,"128","128" 0.82 files blocks requests
direct path read temp 1.57 207,"765696","64" 0.03 file number first dba block cnt
db file async I/O submit 1.15 2004,"0","0" 0.06 requests interrupt timeout

这部分信息显示了在抽样会话活动中占很高百分比的等待事件的参数值它通过总的等待时间(%Event)百分比进行排序后被显示.对于每一个等待事件p1,p2,p3的值与等待事件参数parameter

Load Profile

  • Top Service/Module
  • Top Client IDs
  • Top SQL Command Types
  • Top Phases of Execution

Back to Top

Top Service/Module

Service Module % Activity Action % Action
SYS$USERS UNNAMED 64.47 UNNAMED 64.47
edadbt JDBC Thin Client 32.3 UNNAMED 32.3
SYS$BACKGROUND UNNAMED 1.94 UNNAMED 1.94

这部分信息显示了在抽样会话活动中占很高百分比的服务和模块信息

Top Client IDs

No data exists for this section of the report.

这部分信息显示了在抽样会话活动中占很高百分比的客户端的id信息它是数据库会话中应用程序的特定标识符

Top SQL Command Types

'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

SQL Command Type Distinct SQLIDs % Activity Avg Active Sessions
PL/SQL EXECUTE 17 64.95 0.50
SELECT 81 17.72 0.14
INSERT 74 12.97 0.10

这部分信息显示了在抽样会话活动中占很高百分比的sql命令类型比如select或update

Top Phases of Execution

Phase of Execution % Activity Avg Active Sessions
SQL Execution 96.10 0.74
Java Execution 64.47 0.49

Back to Load Profile

Back to Top

Top SQL

  • Top SQL with Top Events
  • Top SQL with Top Row Sources
  • Top SQL using literals
  • Top Parsing Module/Action
  • Complete List of SQL Text

Back to Top

Top SQL with Top Events

SQL ID FullPlanhash Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text
0sbv4vu4qscxu     1 21.08 CPU + Wait for CPU 21.08 ** Row Source Not Available ** 21.08 DECLARE job BINARY_INTEGER := ...
3mvuvwgyu1chg     1 21.08 CPU + Wait for CPU 21.08 ** Row Source Not Available ** 21.08 DECLARE job BINARY_INTEGER := ...
8kkrj1sdcak68     1 11.37 CPU + Wait for CPU 11.37 ** Row Source Not Available ** 11.37 DECLARE job BINARY_INTEGER := ...
6ykr736rxbadn     1 9.71 CPU + Wait for CPU 9.71 ** Row Source Not Available ** 9.71 DECLARE job BINARY_INTEGER := ...
c0z5w1ptawhmw 1988401559 570450625 103 3.18 CPU + Wait for CPU 2.48 INDEX - RANGE SCAN 2.48 SELECT COUNT(*) FROM CF_CHAMBE...

这部分安装sql的活动频度列出了前6位的sql语句。空值说明没有采样到数据。

Top SQL with Top Row Sources

SQL ID FullPlanHash PlanHash Sampled # of Executions % Activity Row Source % RwSrc Top Event % Event SQL Text
0sbv4vu4qscxu     1 21.08 ** Row Source Not Available ** 21.08 CPU + Wait for CPU 21.08 DECLARE job BINARY_INTEGER := ...
3mvuvwgyu1chg     1 21.08 ** Row Source Not Available ** 21.08 CPU + Wait for CPU 21.08 DECLARE job BINARY_INTEGER := ...
8kkrj1sdcak68     1 11.37 ** Row Source Not Available ** 11.37 CPU + Wait for CPU 11.37 DECLARE job BINARY_INTEGER := ...
6ykr736rxbadn     1 9.71 ** Row Source Not Available ** 9.71 CPU + Wait for CPU 9.71 DECLARE job BINARY_INTEGER := ...
c0z5w1ptawhmw 1988401559 570450625 103 3.18 INDEX - RANGE SCAN 3.18 CPU + Wait for CPU 2.48 SELECT COUNT(*) FROM CF_CHAMBE...

Back to Top SQL

Back to Top

Top SQL using literals

No data exists for this section of the report.

这部分列出了一些未绑定变量的sql。

Top Parsing Module/Action

No data exists for this section of the report.

Back to Top SQL

Back to Top

Complete List of SQL Text

SQL Id SQL Text
0sbv4vu4qscxu DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN tyne_job_util.run_job(289); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
3mvuvwgyu1chg DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN tyne_job_util.run_job(237); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
6ykr736rxbadn DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN tyne_job_util.run_job(230); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
8kkrj1sdcak68 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN tyne_job_util.run_job(236); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
c0z5w1ptawhmw SELECT COUNT(*) FROM CF_CHAMBER_HST_T WHERE STEP_ID = :B6 AND GLASS_ID = :B5 AND EQUIP_ID = :B4 AND SUB_EQUIP_ID = :B3 AND GLASS_START_TIME BETWEEN :B2 AND :B1

这部分是列出了所有的sql语句。

Top PL/SQL Procedures

'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
EDAAPP.TYNE_JOB_UTIL.RUN_JOB 64.47 SQL 64.47
EDAAPP.DS_CELL_DATA_FETCH_UTIL.FETCH_DATA 4.05 SQL 4.05
EDALDR.CF_PDS_LOADER.LOAD_CHAMBER_HST 3.99 SQL 3.99
EDALDR.CELL2_FILE_LOADER.LOAD_DATA 3.3 SQL 3.27
EDALDR.ARRAY_PDS_LOADER.LOAD_CHAMBER_HST 3.14 SQL 3.14

Back to Top

Top Java Workload

Module % Module Entry SQL ID % Activity
  64.47 0sbv4vu4qscxu 21.08
  64.47 3mvuvwgyu1chg 21.08
  64.47 8kkrj1sdcak68 11.37

Back to Top

Top Call Types

Call Type Count % Activity Avg Active Sessions
V8 Bundled Exec 1,025 30.99 0.24
LOGOFF 384 11.61 0.09

Back to Top

Top Sessions

  • Top Sessions
  • Top Blocking Sessions
  • Top Sessions running PQs

Back to Top

Top Sessions

'# 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
1,833,983 21.08 CPU + Wait for CPU 21.08 EDAAPP oracle@p1edadb01t (J001) 697/4,320 [ 16%] 0
40,740,014 21.08 CPU + Wait for CPU 21.08 EDAAPP oracle@p1edadb01t (J000) 697/4,320 [ 16%] 0
220, 8484 11.37 CPU + Wait for CPU 11.37 EDAAPP oracle@p1edadb01t (J002) 376/4,320 [ 9%] 0
601, 9692 9.71 CPU + Wait for CPU 9.71 EDAAPP oracle@p1edadb01t (J003) 321/4,320 [ 7%] 0
405, 8989 3.21 CPU + Wait for CPU 2.6 EDALDR JDBC Thin Client 86/4,320 [ 2%] 84

这部分列出了活动最频繁的会话信息。这一部分为我们提供了非常有用的信息，那就是哪些进程活动频繁。我们通过这一部分获取到某个活动频繁的会话信息，可以通过使用ashrpti.sql来生成针对这个会话的ASH性能报告，或者可以使用sql_trace对这个会话进程跟踪。这样我们就能了解到当前这个会话正在做什么。

Top Blocking Sessions

No data exists for this section of the report.

这一部分列举了被阻塞的会话信息。

Top Sessions running PQs

No data exists for this section of the report.

这部分信息显示了哪些在抽样会话活动中占很高百分比的正处于等待的并行查询

Top Objects/Files/Latches

  • Top DB Objects
  • Top DB Files
  • Top Latches

Back to Top

Top DB Objects

With respect to Application, Cluster, User I/O, buffer busy waits and In-Memory DB events only.


Tablespace name is not available for reports generated from the root PDB of a consolidated database.

Object ID % Activity Event % Event Object Name (Type) Tablespace
86272 2.15 db file sequential read 2.15 EDASYS.CELL_DEFECT_T_PK.P_180801 (INDEX PARTITION) EDASYS_DATA_L
112261 1.66 direct path read temp 1.57 EDAAPP.EDATOOL_DATA06_S (TABLE) SYSTEM
86275 1.54 db file sequential read 1.54 EDASYS.CELL_DEFECT_T_PK.P_180901 (INDEX PARTITION) EDASYS_DATA_L
82334 1.33 db file sequential read 1.06 EDASYS.ARRAY_GLASS_HST_T.P_180801 (TABLE PARTITION) EDASYS_DATA_M
82028 1.03 db file parallel read 1.03 EDASYS.ARRAY_CHAMBER_HST_T_PK.P_180801 (INDEX PARTITION) EDASYS_INDEX_M

Back to Top Objects/Files/Latches

Back to Top

Top DB Files

No data exists for this section of the report.

这部分信息显示了在抽样会话活动中占访问量很高百分比的数据库文件

Top Latches

这部分信息显示了在抽样会话活动中占很高百分比的闩锁信息

闩锁是一种简单低级别串行化机制用来保护sga中的共享数据结构.比如闩锁保护当前访问数据库和缓冲区缓存中数据块结构的用户列表.当维护或查找这些结构时服务器或后台进程请求持有闩锁的时间是非常短暂的.闩锁的实现依赖于操作系统特别是一个进程等待获取一个闩锁多长时间.

Activity Over Time

  • 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 Time (Duration) Slot Count Event Event Count % Event
12:00:00 (20.0 min) 452 CPU + Wait for CPU 399 12.07
12:00:00 (20.0 min) 452 db file sequential read 20 0.6
12:00:00 (20.0 min) 452 direct path read temp 12 0.64
12:20:00 (70.0 min) 2114 CPU + Wait for CPU 1774 53.64
12:20:00 (70.0 min) 2114 db file sequential read 182 5.5
12:20:00 (70.0 min) 2114 db file parallel read 59 1.78
13:30:00 (70.0 min) 741 CPU + Wait for CPU 509 15.39
13:30:00 (70.0 min) 741 db file sequential read 168 5.08
13:30:00 (70.0 min) 741 db file parallel read 35 1.06

这部分列举了各种等待的细粒度显示。

这一部分是ash报告信息最丰富的一部分.这部分信息对于长时间周期的ash报告来说因为在分析期间它提供了关于活动和工作负载概要深层次的详细信息.activity over time会被分成10个时段.每个时段的大小基于分析所持续的时间.第一个和最后一个时段是奇怪.所有内部时段是相等的大小它们可以相互比较.例如,如果分析时间持续10分钟那么所有的时段将会是每个一分钟.然后如果分析时间持续9分30秒,那么外部的时段可能是每个15秒内部的时段可能每个1分钟

特定时段中每个时段包含的信息如下:

列                               描述

slot time(持续时间)      时段的持续时间

solt count                  在时段中抽样会话的数量

event                          在时段中顶级的三个等待事件

event count                ash抽样等待的等待事件的数量

%event                       ash抽样等待的等待事件在整个分析期间所占的百分比


当比较内部时段时,通过识别异常的event count和slot count列执行一个倾斜分析.event count列的异常指示了在抽样会话中等待的等待事件数量增加了.slot count列的异常指示活动的会自豪感增加了,因为ash数据只从活动会话中进持抽样说明数据库的

文章标签：
云解析DNS
Java
Perl
SQL
数据库管理
数据库
缓存
数据库连接
myysophia
目录
相关文章
技术小能手
|
Oracle 关系型数据库 数据库
你不知道的事：AWR 基线和 AWR Compare Period Report 功能介绍
技术小能手
1778 0
穆辞
|
Shell
History displays the time information
For those of you who use terminals a lot, one of the most common commands is probably history, which allows you to view the history of terminal commands executed
穆辞
116 0
玄学酱
|
SQL 监控 BI
70.4. SQL Monitoring Report
玄学酱
1382 0
玄学酱
|
算法 BI
2.2. vmstat - Report virtual memory statistics
玄学酱
1027 0
科技探索者
RVTools: Automate report
科技探索者
1741 0
技术小胖子
|
SQL Oracle 关系型数据库
【ASH】如何导出视图DBA_HIST_ACTIVE_SESS_HISTORY的查询结果数据
技术小胖子
1193 0
科技小能手
|
Oracle 关系型数据库
Know more about AWR Parse Statistics
科技小能手
839 0
科技小能手
|
SQL 存储 关系型数据库
Extract SQL Plan from AWR
科技小能手
1135 0
科技小能手
|
关系型数据库 Oracle SQL
How to understand goldengate report file statistics
科技小能手
845 0
科技小能手
Active Session History (ASH) performed an emergency flush
科技小能手
1367 0