下面是ASH报告的第一部分：
ASH Report For ORCL/orcl
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|---|---|---|---|---|---|
| ORCL | 1293815896 | orcl | 1 | 10.2.0.4.0 | NO | linux |
| CPUs | SGA Size | Buffer Cache | Shared Pool | ASH Buffer Size |
|---|---|---|---|---|
| 1 | 160M (100%) | 48M (30.0%) | 96M (60.0%) | 2.0M (1.3%) |
| Sample Time | Data Source | |
|---|---|---|
| Analysis Begin Time: | 25-11月-11 06:33:55 | V$ACTIVE_SESSION_HISTORY |
| Analysis End Time: | 25-11月-11 07:35:25 | V$ACTIVE_SESSION_HISTORY |
| Elapsed Time: | 61.5 (mins) | |
| Sample Count: | 18 | |
| Average Active Sessions: | 0.00 | |
| Avg. Active Session per CPU: | 0.00 | |
| Report Target: | None specified |
如果是以sid级别生产报告，则如下：（注意Report Target）
| Sample Time | Data Source | |
|---|---|---|
| Analysis Begin Time: | 25-11月-11 07:07:07 | V$ACTIVE_SESSION_HISTORY |
| Analysis End Time: | 25-11月-11 08:07:20 | V$ACTIVE_SESSION_HISTORY |
| Elapsed Time: | 60.2 (mins) | |
| Sample Count: | 5 | |
| Average Active Sessions: | 0.00 | |
| Avg. Active Session per CPU: | 0.00 | |
| Report Target: | SESSION_ID = 166 | 20% of total database activity |
Top User Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|---|---|---|
| CPU + Wait for CPU | CPU | 16.67 | 0.00 |
| log file sync | Commit | 16.67 | 0.00 |
这一部分是用户会话的等待事件的信息。列出了采样时段内数据库发生的显著用户等待！
Top Background Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|---|---|---|
| control file parallel write | System I/O | 27.78 | 0.00 |
| CPU + Wait for CPU | CPU | 16.67 | 0.00 |
| log file parallel write | System I/O | 16.67 | 0.00 |
| null event | Other | 5.56 | 0.00 |
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|---|
| control file parallel write | 27.78 | "3","3","3" | 27.78 | files | block# | requests |
| log file parallel write | 16.67 | "1","5","1" | 5.56 | files | blocks | requests |
| "1","6","1" | 5.56 | |||||
| "1","22","1" | 5.56 | |||||
| log file sync | 16.67 | "1010","0","0" | 5.56 | buffer# | NOT DEFINED | NOT DEFINED |
| "1385","0","0" | 5.56 | |||||
| "5057","0","0" | 5.56 |
Top Service/Module
| Service | Module | % Activity | Action | % Action |
|---|---|---|---|---|
| SYS$BACKGROUND | UNNAMED | 61.11 | UNNAMED | 61.11 |
| SYS$USERS | 16.67 | 16.67 | ||
| sqlplus@linux (TNS V1-V3) | 16.67 | UNNAMED | 16.67 | |
| SYS$BACKGROUND | MMON_SLAVE | 5.56 | Auto ADDM Slave Action | 5.56 |
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 |
|---|---|---|---|
| SELECT | 1 | 5.56 | 0.00 |
Top SQL Statements
| SQL ID | Planhash | % Activity | Event | % Event | SQL Text |
|---|---|---|---|---|---|
| 4z2at9d1natrv | 2848324471 | 5.56 | CPU + Wait for CPU | 5.56 | SELECT INSTANCE_NUMBER FROM V$... |
Top SQL using literals
No data exists for this section of the report.
这部分列出了一些未绑定变量的sql。
Complete List of SQL Text
| SQL Id | SQL Text |
|---|---|
| 4z2at9d1natrv | SELECT INSTANCE_NUMBER FROM V$INSTANCE |
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 |
|---|---|---|---|---|---|---|---|
| 165, 1 | 27.78 | control file parallel write | 27.78 | SYS | oracle@linux (CKPT) | 5/3,690 [ 0%] | 0 |
| 166, 1 | 16.67 | log file parallel write | 16.67 | SYS | oracle@linux (LGWR) | 3/3,690 [ 0%] | 0 |
| 167, 1 | 16.67 | CPU + Wait for CPU | 16.67 | SYS | oracle@linux (DBW0) | 3/3,690 [ 0%] | 0 |
| 135, 165 | 11.11 | CPU + Wait for CPU | 11.11 | SYS | sqlplus@linux (TNS V1-V3) | 2/3,690 [ 0%] | 0 |
| 135, 108 | 5.56 | null event | 5.56 | SYS | oracle@linux (m001) | 1/3,690 [ 0%] | 0 |
这部分列出了活动最频繁的会话信息。这一部分为我们提供了非常有用的信息，那就是哪些进程活动频繁。我们通过这一部分获取到某个活动频繁的会话信息，可以通过使用ashrpti.sql来生成针对这个会话的ASH性能报告，或者可以使用sql_trace对这个会话进程跟踪。这样我们就能了解到当前这个会话正在做什么。
Top Blocking Sessions
- 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 | % Activity | Event Caused | % Event | User | Program | # Samples Active | XIDs |
|---|---|---|---|---|---|---|---|
| 166, 1 | 16.67 | log file sync | 16.67 | SYS | oracle@linux (LGWR) | 3/3,690 [ 0%] | 0 |
Top Sessions running PQs
No data exists for this section of the report.
这部分列出了活动频繁的前几位并行执行的会话信息。
Top DB Objects
No data exists for this section of the report.
这部分列举了相关等待具体等待的对象。
Top DB Files
No data exists for this section of the report.
这部分列举访问的数据文件信息。
Top Latches
No data exists for this section of the report.
这部分列出latch竞争。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 |
|---|---|---|---|---|
| 06:36:00 (6.0 min) | 1 | CPU + Wait for CPU | 1 | 5.56 |
| 06:48:00 (6.0 min) | 2 | control file parallel write | 2 | 11.11 |
| 06:54:00 (6.0 min) | 2 | log file parallel write | 1 | 5.56 |
| log file sync | 1 | 5.56 | ||
| 07:00:00 (6.0 min) | 3 | CPU + Wait for CPU | 1 | 5.56 |
| control file parallel write | 1 | 5.56 | ||
| null event | 1 | 5.56 | ||
| 07:06:00 (6.0 min) | 2 | log file parallel write | 1 | 5.56 |
| log file sync | 1 | 5.56 | ||
| 07:12:00 (6.0 min) | 2 | CPU + Wait for CPU | 1 | 5.56 |
| control file parallel write | 1 | 5.56 | ||
| 07:18:00 (6.0 min) | 3 | CPU + Wait for CPU | 1 | 5.56 |
| log file parallel write | 1 | 5.56 | ||
| log file sync | 1 | 5.56 | ||
| 07:30:00 (5.4 min) | 3 | CPU + Wait for CPU | 2 | 11.11 |
| control file parallel write | 1 | 5.56 |
当我们需要对一些活动的会话做分析时，使用ASH更加方便，这样可以排除很多不需要的信息干扰，更容易定位到问题的所在；
当我们需要对数据库系统做整体性能评估时，需要分析AWR性能报告。
扫一扫
1428
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
