AWR（Automatic Workload Repository）——分析（3）！
以AWR的一个HTML格式的报告为例：DB NameDB IdInstanceInst numReleaseRACHostORCL1290103567orcl110.2.0.4.0NOlinux Snap IdSnap TimeSessionsCursors/Se
·
以AWR的一个HTML格式的报告为例：
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|---|---|---|---|---|---|
| ORCL | 1290103567 | orcl | 1 | 10.2.0.4.0 | NO | linux |
| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 17 | 07-10月-11 16:00:10 | 22 | 2.6 |
| End Snap: | 18 | 07-10月-11 17:00:14 | 22 | 2.6 |
| Elapsed: | 60.06 (mins) | |||
| DB Time: | 0.05 (mins) |
Sessions：表示采集时实例连接的会话数，这个数可以让我们了解数据库的并发用户数大概的情况，这个数值对于我们判断数据库的类型有帮助。
Cursors/Session：每个会话平均打开的游标数。
DB Time：这个数值比较重要，它表示用户操作话费的时间，包括CPU时间和等待事件。要注意它指的是用户操作的时间，而不包含数据库后台进程花费的时间。从上面的列表中我们看到，60分钟的周期当中，用户的时间占用了0.05秒，说明数据库很闲（因为只是我的测试数据库）。如果这个数值远远大于60分钟（因为它是以累积的方式记录的），说明数据库比较繁忙，那么就应该到TOP5的等待事件部分去查看究竟是什么事件占用了系统如此多的时间。
Cache Sizes
| Begin | End | |||
|---|---|---|---|---|
| Buffer Cache: | 48M | 48M | Std Block Size: | 8K |
| Shared Pool Size: | 92M | 92M | Log Buffer: | 2,856K |
这个列表列出了AWR在性能采集开始和结束的时候，数据缓冲池（Buffer Cache）和共享池（Shared Pool Size）的大小。通过前后比较可以了解体统内存消耗的变化。
Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 638.10 | 7,417.77 |
| Logical reads: | 6.96 | 80.90 |
| Block changes: | 2.20 | 25.55 |
| Physical reads: | 0.02 | 0.19 |
| Physical writes: | 0.26 | 2.99 |
| User calls: | 0.05 | 0.62 |
| Parses: | 0.76 | 8.83 |
| Hard parses: | 0.00 | 0.01 |
| Sorts: | 0.51 | 5.95 |
| Logons: | 0.02 | 0.21 |
| Executes: | 1.61 | 18.72 |
| Transactions: | 0.09 |
| % Blocks changed per Read: | 31.58 | Recursive Call %: | 99.44 |
| Rollback per transaction %: | 0.00 | Rows per Sort: | 9.12 |
Redo size：每秒（每个事务）产生的redo量。单位为bytes，从上面就可以知道数据库每秒产生了大约638/1024=0.6KB的redo信息，每个事务平均产生了7417/1024=7.2KB左右的redo信息。
Logical reads：每秒（每个事务）产生的逻辑读（对应于物理读）
Block changes：每秒（每个事务）改变的数据块数
Physical reads：每秒（每个事务）产生的物理读
Physical writes：每秒（每个事务）产生的物理写
User calls：每秒（每个事务）用户的调用次数
Parses：每秒（每个事务）分析次数
Hard parses：每秒（每个事务）硬分析次数
Sorts：每秒（每个事务）排序次数
Logons：每秒（每个事务）登录数据库次数
Executes：每秒（每个事务）sql的执行次数
Transactions：每秒（每个事务）每秒的事务数
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.77 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.89 | Soft Parse %: | 99.89 |
| Execute to Parse %: | 52.83 | Latch Hit %: | 100.00 |
| Parse CPU to Parse Elapsd %: | 14.55 | % Non-Parse CPU: | 90.85 |
Buffer Nowait %：非等待方式获取数据块百分比
Redo NoWait %：非等待方式获取redo数据百分比
Buffer Hit %：内存数据块命中率
In-memory Sort %：数据块在内存中排序的百分比
Library Hit %：共享池中sql解析的命中率
Soft Parse %：软分析在总分析数的百分比
Execute to Parse %：执行次数对分析次数的百分比
Latch Hit %：latch命中率百分比
Parse CPU to Parse Elapsd %：解析总时间中消耗CPU的时间百分比
% Non-Parse CPU：CPU非分析时间在整个CPU时间的百分比
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| control file parallel write | 1,197 | 8 | 6 | 279.9 | System I/O |
| CPU time | 2 | 64.0 | |||
| log file parallel write | 382 | 2 | 4 | 61.7 | System I/O |
| db file parallel write | 573 | 1 | 2 | 50.2 | System I/O |
| control file sequential read | 3,019 | 1 | 0 | 27.4 | System I/O |
RAC Statistics
| Begin | End | |
|---|---|---|
| Number of Instances: | 2 | 2 |
Global Cache Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Global Cache blocks received: | 8.31 | 0.58 |
| Global Cache blocks served: | 4.12 | 0.29 |
| GCS/GES messages received: | 27.19 | 1.89 |
| GCS/GES messages sent: | 36.65 | 2.55 |
| DBWR Fusion writes: | 0.39 | 0.03 |
| Estd Interconnect traffic (KB) | 111.88 |
Global Cache Efficiency Percentages (Target local+remote 100%)
| Buffer access - local cache %: | 96.55 |
| Buffer access - remote cache %: | 0.81 |
| Buffer access - disk %: | 2.63 |
Global Cache and Enqueue Services - Workload Characteristics
| Avg global enqueue get time (ms): | 0.0 |
| Avg global cache cr block receive time (ms): | 0.4 |
| Avg global cache current block receive time (ms): | 0.5 |
| Avg global cache cr block build time (ms): | 0.0 |
| Avg global cache cr block send time (ms): | 0.0 |
| Global cache log flushes for cr blocks served %: | 0.1 |
| Avg global cache cr block flush time (ms): | 0.0 |
| Avg global cache current block pin time (ms): | 0.0 |
| Avg global cache current block send time (ms): | 0.0 |
| Global cache log flushes for current blocks served %: | 0.0 |
| Avg global cache current block flush time (ms): |
Global Cache and Enqueue Services - Messaging Statistics
| Avg message sent queue time (ms): | 0.0 |
| Avg message sent queue time on ksxp (ms): | 0.2 |
| Avg message received queue time (ms): | 0.0 |
| Avg GCS message process time (ms): | 0.0 |
| Avg GES message process time (ms): | 0.0 |
| % of direct sent messages: | 29.88 |
| % of indirect sent messages: | 23.50 |
| % of flow controlled messages: | 46.62 |
这一部分只有在RAC环境下才会出现，是一些全局内存中数据发送、接收方面的性能指标，还有一些全局锁的信息，除非这个数据库在运行正常时设定了一个基线作为参照，否则这一部分性能指标值很难说是否有性能问题。
Time Model Statistics
- Total time in database user-calls (DB Time): 10.7s
- Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
- Ordered by % or DB time desc, Statistic name
| Statistic Name | Time (s) | % of DB Time |
|---|---|---|
| sql execute elapsed time | 9.42 | 88.00 |
| DB CPU | 7.66 | 71.60 |
| parse time elapsed | 2.86 | 26.68 |
| hard parse elapsed time | 2.43 | 22.67 |
| PL/SQL execution elapsed time | 1.65 | 15.39 |
| PL/SQL compilation elapsed time | 0.43 | 3.98 |
| connection management call elapsed time | 0.22 | 2.07 |
| hard parse (sharing criteria) elapsed time | 0.03 | 0.31 |
| repeated bind elapsed time | 0.01 | 0.10 |
| hard parse (bind mismatch) elapsed time | 0.01 | 0.08 |
| sequence load elapsed time | 0.00 | 0.01 |
| DB time | 10.70 | |
| background elapsed time | 22.99 | |
| background cpu time | 9.88 |
Wait Class
- s - second
- cs - centisecond - 100th of a second
- ms - millisecond - 1000th of a second
- us - microsecond - 1000000th of a second
- ordered by wait time desc, waits desc
| Wait Class | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn |
|---|---|---|---|---|---|
| System I/O | 5,986 | 0.00 | 12 | 2 | 10.01 |
| User I/O | 349 | 0.00 | 4 | 10 | 0.58 |
| Commit | 260 | 0.00 | 2 | 6 | 0.43 |
| Concurrency | 5 | 0.00 | 0 | 37 | 0.01 |
| Application | 302 | 0.00 | 0 | 1 | 0.51 |
| Other | 49 | 0.00 | 0 | 1 | 0.08 |
| Network | 6,981 | 0.00 | 0 | 0 | 11.67 |
| Configuration | 1 | 100.00 | 0 | 6 | 0.00 |
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
| Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 2 | 1 | 60 | 0.03 | 14.89 | 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := ... | |
| 1 | 1 | 2,154 | 0.00 | 13.44 | cb75rw3w1tt0s | OEM.SystemPool | begin MGMT_JOB_ENGINE.get_sche... |
| 0 | 0 | 41 | 0.01 | 4.34 | abtp0uqvdb1d3 | CALL MGMT_ADMIN_DATA.EVALUATE_... | |
| 0 | 0 | 94 | 0.00 | 4.19 | 8hk7xvhua40va | OEM.Loader | INSERT INTO MGMT_METRICS_RAW(C... |
| 0 | 0 | 2 | 0.19 | 3.48 | bgb6m4jc3rps0 | OEM.BoundedPool | begin setEMUserContext(:1, :2... |
| 0 | 0 | 119 | 0.00 | 3.32 | 2b064ybzkwf1y | OEM.SystemPool | BEGIN EMD_NOTIFICATION.QUEUE_R... |
| 0 | 0 | 18 | 0.02 | 2.70 | 130dvvr5s8bgn | select obj#, dataobj#, part#... | |
| 0 | 0 | 60 | 0.00 | 2.67 | cydnuss99swtd | OEM.SystemPool | BEGIN EM_PING.RECORD_BATCH_HEA... |
| 0 | 0 | 1,008 | 0.00 | 2.29 | 3c1kubcdjnppq | update sys.col_usage$ set eq... | |
| 0 | 0 | 4 | 0.06 | 2.27 | f787fyhjmkp61 | OEM.BoundedPool | INSERT INTO MGMT_SEVERITY(ACTI... |
| 0 | 0 | 9 | 0.02 | 1.69 | d8mayxqw0wnpv | OMS | SELECT OWNER FROM DBA_PROCEDUR... |
| 0 | 0 | 2,217 | 0.00 | 1.44 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... | |
| 0 | 0 | 1 | 0.15 | 1.42 | bunssq950snhf | insert into wrh$_sga_target_ad... | |
| 0 | 0 | 2,154 | 0.00 | 1.42 | 6gh8gj9n09vr7 | OEM.SystemPool | SELECT JOB_ID, EXECUTION_ID, ... |
| 0 | 0 | 181 | 0.00 | 1.35 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 0 | 0 | 17 | 0.01 | 1.32 | b9huk6zssjk7f | OEM.Loader | BEGIN EMD_LOADER.UPDATE_LOADER... |
| 0 | 0 | 719 | 0.00 | 1.25 | 6ssrk2dqj7jbx | select job, nvl2(last_date, ... | |
| 0 | 0 | 96 | 0.00 | 1.21 | cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_id... | |
| 0 | 0 | 12 | 0.01 | 1.14 | gz6qtapr9u99d | emagent@linux (TNS V1-V3) | /* OracleOEM */ DECLARE l_l... |
| 0 | 0 | 145 | 0.00 | 1.14 | 8swypbbr0m372 | select order#, columns, types ... | |
| 0 | 0 | 1,333 | 0.00 | 1.12 | 91h2x42zqagcm | OEM.Loader | UPDATE MGMT_CURRENT_METRICS SE... |
| 0 | 0 | 1 | 0.11 | 1.06 | 36g2yq0mxjx2y | insert into wrh$_pgastat (sn... | |
| 0 | 0 | 5 | 0.02 | 1.05 | ckd6kpty9npxk | OEM.BoundedPool | BEGIN EMD_LOADER.obtain_lock_f... |
| 0 | 0 | 422 | 0.00 | 1.03 | 04xtrk7uyhknh | select obj#, type#, ctime, mti... | |
| 0 | 0 | 96 | 0.00 | 1.00 | 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_id... |
Elapsed Time (s) ：sql执行总的时间
CPU Time (s) ：sql执行消耗cpu的时间
Executions ：sql执行次数
Elap per Exec (s)： sql'每次执行消耗的时间
% Total DB Time ：sql执行时间占总共DB Time的百分比
SQL ordered by CPU Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
| CPU Time (s) | Elapsed Time (s) | Executions | CPU per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2,154 | 0.00 | 13.44 | cb75rw3w1tt0s | OEM.SystemPool | begin MGMT_JOB_ENGINE.get_sche... |
| 1 | 2 | 60 | 0.02 | 14.89 | 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := ... | |
| 0 | 0 | 119 | 0.00 | 3.32 | 2b064ybzkwf1y | OEM.SystemPool | BEGIN EMD_NOTIFICATION.QUEUE_R... |
| 0 | 0 | 94 | 0.00 | 4.19 | 8hk7xvhua40va | OEM.Loader | INSERT INTO MGMT_METRICS_RAW(C... |
| 0 | 0 | 41 | 0.01 | 4.34 | abtp0uqvdb1d3 | CALL MGMT_ADMIN_DATA.EVALUATE_... | |
| 0 | 0 | 60 | 0.00 | 2.67 | cydnuss99swtd | OEM.SystemPool | BEGIN EM_PING.RECORD_BATCH_HEA... |
| 0 | 0 | 1,008 | 0.00 | 2.29 | 3c1kubcdjnppq | update sys.col_usage$ set eq... | |
| 0 | 0 | 1 | 0.15 | 1.42 | bunssq950snhf | insert into wrh$_sga_target_ad... | |
| 0 | 0 | 2,154 | 0.00 | 1.42 | 6gh8gj9n09vr7 | OEM.SystemPool | SELECT JOB_ID, EXECUTION_ID, ... |
| 0 | 0 | 2 | 0.07 | 3.48 | bgb6m4jc3rps0 | OEM.BoundedPool | begin setEMUserContext(:1, :2... |
| 0 | 0 | 4 | 0.04 | 2.27 | f787fyhjmkp61 | OEM.BoundedPool | INSERT INTO MGMT_SEVERITY(ACTI... |
| 0 | 0 | 719 | 0.00 | 1.25 | 6ssrk2dqj7jbx | select job, nvl2(last_date, ... | |
| 0 | 0 | 2,217 | 0.00 | 1.44 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... | |
| 0 | 0 | 12 | 0.01 | 1.14 | gz6qtapr9u99d | emagent@linux (TNS V1-V3) | /* OracleOEM */ DECLARE l_l... |
| 0 | 0 | 1,333 | 0.00 | 1.12 | 91h2x42zqagcm | OEM.Loader | UPDATE MGMT_CURRENT_METRICS SE... |
| 0 | 0 | 18 | 0.01 | 2.70 | 130dvvr5s8bgn | select obj#, dataobj#, part#... | |
| 0 | 0 | 181 | 0.00 | 1.35 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 0 | 0 | 422 | 0.00 | 1.03 | 04xtrk7uyhknh | select obj#, type#, ctime, mti... | |
| 0 | 0 | 9 | 0.01 | 1.69 | d8mayxqw0wnpv | OMS | SELECT OWNER FROM DBA_PROCEDUR... |
| 0 | 0 | 96 | 0.00 | 1.21 | cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_id... | |
| 0 | 0 | 17 | 0.00 | 1.32 | b9huk6zssjk7f | OEM.Loader | BEGIN EMD_LOADER.UPDATE_LOADER... |
| 0 | 0 | 145 | 0.00 | 1.14 | 8swypbbr0m372 | select order#, columns, types ... | |
| 0 | 0 | 5 | 0.01 | 1.05 | ckd6kpty9npxk | OEM.BoundedPool | BEGIN EMD_LOADER.obtain_lock_f... |
| 0 | 0 | 96 | 0.00 | 1.00 | 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_id... | |
| 0 | 0 | 1 | 0.02 | 1.06 | 36g2yq0mxjx2y | insert into wrh$_pgastat (sn... |
CPU Time (s)：sql消耗的cpu时间
Elapsed Time (s) ：sql执行时间
Executions ：sql执行次数
CPU per Exec (s)： 每次执行消耗cpu时间
% Total DB Time ：sql执行时间占总共DB Time的百分比
SQL ordered by Gets
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- Total Buffer Gets: 95,886
- Captured SQL account for 64.7% of Total
| Buffer Gets | Executions | Gets per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 15,745 | 60 | 262.42 | 16.42 | 1.18 | 1.59 | 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := ... | |
| 12,924 | 2,154 | 6.00 | 13.48 | 1.41 | 1.44 | cb75rw3w1tt0s | OEM.SystemPool | begin MGMT_JOB_ENGINE.get_sche... |
| 7,036 | 94 | 74.85 | 7.34 | 0.34 | 0.45 | 8hk7xvhua40va | OEM.Loader | INSERT INTO MGMT_METRICS_RAW(C... |
| 6,462 | 2,154 | 3.00 | 6.74 | 0.15 | 0.15 | 6gh8gj9n09vr7 | OEM.SystemPool | SELECT JOB_ID, EXECUTION_ID, ... |
| 5,850 | 2,217 | 2.64 | 6.10 | 0.13 | 0.15 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... | |
| 4,228 | 1,333 | 3.17 | 4.41 | 0.12 | 0.12 | 91h2x42zqagcm | OEM.Loader | UPDATE MGMT_CURRENT_METRICS SE... |
| 3,738 | 41 | 91.17 | 3.90 | 0.31 | 0.46 | abtp0uqvdb1d3 | CALL MGMT_ADMIN_DATA.EVALUATE_... | |
| 3,332 | 119 | 28.00 | 3.47 | 0.36 | 0.36 | 2b064ybzkwf1y | OEM.SystemPool | BEGIN EMD_NOTIFICATION.QUEUE_R... |
| 3,304 | 60 | 55.07 | 3.45 | 0.24 | 0.29 | cydnuss99swtd | OEM.SystemPool | BEGIN EM_PING.RECORD_BATCH_HEA... |
| 3,293 | 1,008 | 3.27 | 3.43 | 0.24 | 0.24 | 3c1kubcdjnppq | update sys.col_usage$ set eq... | |
| 2,451 | 4 | 612.75 | 2.56 | 0.15 | 0.24 | f787fyhjmkp61 | OEM.BoundedPool | INSERT INTO MGMT_SEVERITY(ACTI... |
| 2,152 | 213 | 10.10 | 2.24 | 0.05 | 0.05 | 0h6b2sajwb74n | select privilege#, level from ... | |
| 1,849 | 145 | 12.75 | 1.93 | 0.05 | 0.08 | cqgv56fmuj63x | select owner#, name, namespace... | |
| 1,542 | 181 | 8.52 | 1.61 | 0.06 | 0.14 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 1,501 | 719 | 2.09 | 1.57 | 0.13 | 0.13 | 6ssrk2dqj7jbx | select job, nvl2(last_date, ... | |
| 1,283 | 145 | 8.85 | 1.34 | 0.05 | 0.12 | 8swypbbr0m372 | select order#, columns, types ... | |
| 1,242 | 422 | 2.94 | 1.30 | 0.06 | 0.11 | 04xtrk7uyhknh | select obj#, type#, ctime, mti... | |
| 1,193 | 60 | 19.88 | 1.24 | 0.04 | 0.04 | 19v5guvsgcd1v | SELECT C.TARGET_GUID, C.METRI... | |
| 1,119 | 2 | 559.50 | 1.17 | 0.15 | 0.37 | bgb6m4jc3rps0 | OEM.BoundedPool | begin setEMUserContext(:1, :2... |
| 1,099 | 152 | 7.23 | 1.15 | 0.04 | 0.04 | 18naypzfmabd6 | OEM.Loader | INSERT INTO MGMT_SYSTEM_PERFOR... |
| 1,072 | 134 | 8.00 | 1.12 | 0.04 | 0.08 | 6129566gyvx21 | OEM.Loader | SELECT INSTANTIABLE, supertyp... |
| 981 | 9 | 109.00 | 1.02 | 0.06 | 0.18 | d8mayxqw0wnpv | OMS | SELECT OWNER FROM DBA_PROCEDUR... |
Buffer Gets ：sql执行获得的内存数据块数量
Executions ：sql执行次数
Gets per Exec： 每次执行获得的内存数据块数量
%Total ：占总数的百分比
CPU Time (s) ：消耗的cpu时间
Elapsed Time (s)： sql执行时间
SQL ordered by Reads
- Total Disk Reads: 328
- Captured SQL account for 89.3% of Total
| Physical Reads | Executions | Reads per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 97 | 60 | 1.62 | 29.57 | 1.18 | 1.59 | 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := ... | |
| 86 | 41 | 2.10 | 26.22 | 0.31 | 0.46 | abtp0uqvdb1d3 | CALL MGMT_ADMIN_DATA.EVALUATE_... | |
| 82 | 9 | 9.11 | 25.00 | 0.06 | 0.18 | d8mayxqw0wnpv | OMS | SELECT OWNER FROM DBA_PROCEDUR... |
| 19 | 18 | 1.06 | 5.79 | 0.10 | 0.29 | 130dvvr5s8bgn | select obj#, dataobj#, part#... | |
| 16 | 4 | 4.00 | 4.88 | 0.15 | 0.24 | f787fyhjmkp61 | OEM.BoundedPool | INSERT INTO MGMT_SEVERITY(ACTI... |
| 14 | 2 | 7.00 | 4.27 | 0.15 | 0.37 | bgb6m4jc3rps0 | OEM.BoundedPool | begin setEMUserContext(:1, :2... |
| 10 | 145 | 0.07 | 3.05 | 0.05 | 0.12 | 8swypbbr0m372 | select order#, columns, types ... | |
| 10 | 17 | 0.59 | 3.05 | 0.05 | 0.14 | b9huk6zssjk7f | OEM.Loader | BEGIN EMD_LOADER.UPDATE_LOADER... |
| 9 | 96 | 0.09 | 2.74 | 0.03 | 0.11 | 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_id... | |
| 9 | 5 | 1.80 | 2.74 | 0.04 | 0.11 | ckd6kpty9npxk | OEM.BoundedPool | BEGIN EMD_LOADER.obtain_lock_f... |
| 9 | 96 | 0.09 | 2.74 | 0.06 | 0.13 | cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_id... | |
| 9 | 96 | 0.09 | 2.74 | 0.04 | 0.10 | ga9j9xk5cy9s0 | select /*+ index(idl_sb4$ i_id... | |
| 7 | 96 | 0.07 | 2.13 | 0.04 | 0.09 | c6awqs517jpj0 | select /*+ index(idl_char$ i_i... | |
| 6 | 422 | 0.01 | 1.83 | 0.06 | 0.11 | 04xtrk7uyhknh | select obj#, type#, ctime, mti... | |
| 5 | 94 | 0.05 | 1.52 | 0.34 | 0.45 | 8hk7xvhua40va | OEM.Loader | INSERT INTO MGMT_METRICS_RAW(C... |
| 5 | 6 | 0.83 | 1.52 | 0.02 | 0.06 | ccqcbrv0aywad | OEM.Loader | INSERT INTO MGMT_STRING_METRIC... |
| 5 | 145 | 0.03 | 1.52 | 0.05 | 0.08 | cqgv56fmuj63x | select owner#, name, namespace... | |
| 4 | 12 | 0.33 | 1.22 | 0.05 | 0.10 | 7j23tu2qk35zj | emagent@linux (TNS V1-V3) | /* OracleOEM */ BEGIN IF (:... |
| 4 | 181 | 0.02 | 1.22 | 0.06 | 0.14 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 4 | 2,217 | 0.00 | 1.22 | 0.13 | 0.15 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... |
这部分列出了sql执行物理读的信息，按照从高到低的顺序排序。
Physical Reads ：sql物理读的次数
Executions ：sql执行次数
Reads per Exec ：sql每次执行产生的物理读
%Total ：占整个物理读的百分比
CPU Time (s)：sql执行消耗的cpu时间
Elapsed Time (s) ：sql的执行时间
SQL ordered by Executions
- Total Executions: 24,941
- Captured SQL account for 67.4% of Total
| Executions | Rows Processed | Rows per Exec | CPU per Exec (s) | Elap per Exec (s) | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 2,217 | 1,416 | 0.64 | 0.00 | 0.00 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... | |
| 2,214 | 2,214 | 1.00 | 0.00 | 0.00 | 089dbukv1aanh | EM_PING | SELECT SYS_EXTRACT_UTC(SYSTIME... |
| 2,154 | 0 | 0.00 | 0.00 | 0.00 | 6gh8gj9n09vr7 | OEM.SystemPool | SELECT JOB_ID, EXECUTION_ID, ... |
| 2,154 | 2,154 | 1.00 | 0.00 | 0.00 | cb75rw3w1tt0s | OEM.SystemPool | begin MGMT_JOB_ENGINE.get_sche... |
| 1,333 | 1,333 | 1.00 | 0.00 | 0.00 | 91h2x42zqagcm | OEM.Loader | UPDATE MGMT_CURRENT_METRICS SE... |
| 1,008 | 881 | 0.87 | 0.00 | 0.00 | 3c1kubcdjnppq | update sys.col_usage$ set eq... | |
| 719 | 60 | 0.08 | 0.00 | 0.00 | 6ssrk2dqj7jbx | select job, nvl2(last_date, ... | |
| 422 | 398 | 0.94 | 0.00 | 0.00 | 04xtrk7uyhknh | select obj#, type#, ctime, mti... | |
| 364 | 0 | 0.00 | 0.00 | 0.00 | b2gnxm5z6r51n | lock table sys.col_usage$ in e... | |
| 289 | 289 | 1.00 | 0.00 | 0.00 | 2ym6hhaq30r73 | select type#, blocks, extents,... |
Executions ：sql的执行次数
Rows Processed： sql处理的记录数
Rows per Exec ：每次执行处理的记录数
CPU per Exec (s) ：每次执行消耗的cpu时间
Elap per Exec (s) ：每次执行的时间
SQL ordered by Parse Calls
- Total Parse Calls: 7,909
- Captured SQL account for 56.8% of Total
| Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|
| 364 | 127 | 4.60 | 0v3dvmc22qnam | insert into sys.col_usage$ (ob... | |
| 364 | 1,008 | 4.60 | 3c1kubcdjnppq | update sys.col_usage$ set eq... | |
| 364 | 364 | 4.60 | b2gnxm5z6r51n | lock table sys.col_usage$ in e... | |
| 289 | 289 | 3.65 | 2ym6hhaq30r73 | select type#, blocks, extents,... | |
| 213 | 213 | 2.69 | 0h6b2sajwb74n | select privilege#, level from ... | |
| 185 | 185 | 2.34 | asvzxj61dc5vs | select timestamp, flags from ... | |
| 172 | 172 | 2.17 | 350f5yrnnmshs | lock table sys.mon_mods$ in ex... | |
| 172 | 172 | 2.17 | g00cj285jmgsw | update sys.mon_mods$ set inser... | |
| 145 | 145 | 1.83 | 8swypbbr0m372 | select order#, columns, types ... | |
| 145 | 145 | 1.83 | cqgv56fmuj63x | select owner#, name, namespace... | |
| 134 | 134 | 1.69 | 6129566gyvx21 | OEM.Loader | SELECT INSTANTIABLE, supertyp... |
| 119 | 119 | 1.50 | 2b064ybzkwf1y | OEM.SystemPool | BEGIN EMD_NOTIFICATION.QUEUE_R... |
| 112 | 172 | 1.42 | 24dkx03u3rj6k | SELECT COUNT(*) FROM MGMT_PARA... | |
| 96 | 96 | 1.21 | 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_id... | |
| 96 | 96 | 1.21 | c6awqs517jpj0 | select /*+ index(idl_char$ i_i... | |
| 96 | 96 | 1.21 | cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_id... | |
| 96 | 96 | 1.21 | ga9j9xk5cy9s0 | select /*+ index(idl_sb4$ i_id... | |
| 85 | 85 | 1.07 | 0k8522rmdzg4k | select privilege# from sysauth... |
Parse Calls ：sql分析的次数
Executions ：sql执行的次数
% Total Parses： 占整个分析次数的百分比
SQL ordered by Version Count
- Only Statements with Version Count greater than 20 are displayed
| Version Count | Executions | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|
| 42 | 819 | db78fxqxwxt7r | select /*+ rule */ bucket, end... | |
| 27 | 823 | 5ngzsfstg8tmy | select o.owner#, o.name, o.nam... | |
| 25 | 202 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... |
Version Count ：sql的版本次数
Executions ：sql的执行次数
SQL ordered by Cluster Wait Time
| Cluster Wait Time (s) | CWT % of Elapsd Time | Elapsed Time(s) | CPU Time(s) | Executions | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 1.61 | 28.98 | 5.55 | 4.23 | 85 | 791ykzdtuqb1g | serv_proc@zfyw_payoln (TNS V1-V3) | SELECT * FROM (SELECT T1.MERNO... |
| 1.18 | 3.86 | 30.45 | 28.35 | 180 | 7yha6au6npnxr | JDBC Thin Client | select ID, DESTNO, LIVETIME, C... |
| 1.15 | 65.96 | 1.74 | 0.71 | 890 | 37vq4h84t8a83 | serv_proc@zfyw_payoln (TNS V1-V3) | INSERT INTO T_PAY_ORDER_INFO(I... |
| 0.70 | 75.76 | 0.93 | 0.30 | 890 | 7jy6cgmt5rk76 | serv_proc@zfyw_payoln (TNS V1-V3) | select count(t.id)+1, to_char(... |
| 0.69 | 1.72 | 40.04 | 2.79 | 1 | 0duh9tnzbg2af | PL/SQL Developer | --3.2 激活用户数日报表.sql --无法分自主注册或沃... |
| 0.63 | 46.16 | 1.37 | 0.38 | 966 | a99gx2ch4pqzy | serv_proc@zfyw_payoln (TNS V1-V3) | INSERT INTO T_PAY_SYSTEM_JOURN... |
| 0.34 | 64.22 | 0.53 | 0.18 | 774 | 1qn9wwjqds2at | serv_proc@zfyw_payoln (TNS V1-V3) | UPDATE T_PAY_SYSTEM_JOURNAL SE... |
| 0.23 | 18.63 | 1.23 | 0.20 | 1,880 | b75t4gm1wcr5j | JDBC Thin Client | insert into t_pay_merbindagr(a... |
| 0.23 | 74.21 | 0.30 | 0.09 | 890 | d6xxn7zrpr2py | serv_proc@zfyw_payoln (TNS V1-V3) | select ID, to_char(AMOUNT), ST... |
| 0.20 | 3.08 | 6.43 | 0.15 | 1,880 | bd6qduvpaa7nh | JDBC Thin Client | select agrno, status from t_pa... |
| 0.19 | 43.74 | 0.43 | 0.10 | 935 | 0h6mmn04xmmp5 | serv_proc@zfyw_payoln (TNS V1-V3) | UPDATE T_PAY_SYSTEM_JOURNAL SE... |
| 0.16 | 2.68 | 6.12 | 0.22 | 4,398 | 2m9k4tqd4qang | JDBC Thin Client | select * from ( select t.usern... |
| 0.12 | 18.49 | 0.64 | 0.57 | 47 | 9zqn04pp4pm8p | serv_proc@zfyw_payoln (TNS V1-V3) | UPDATE T_PAY_ORD_AUTO_NOTIFY S... |
| 0.11 | 9.62 | 1.11 | 0.06 | 966 | 1kt0xkkbsbcz3 | serv_proc@zfyw_payoln (TNS V1-V3) | select t.agrno from T_PAY_MERB... |
| 0.10 | 3.19 | 3.21 | 2.55 | 1 | bd502nbh9abbs | plsqldev.exe | select s.synonym_name object_n... |
| 0.08 | 13.09 | 0.64 | 0.09 | 1,882 | fr96kbusyrt04 | JDBC Thin Client | insert into T_PAY_USRMBLNO_BIN... |
| 0.08 | 7.62 | 1.06 | 0.18 | 19,140 | aw9mx8wb9uwsu | JDBC Thin Client | SELECT :B1 || '00' || LPAD(TO_... |
| 0.08 | 1.88 | 4.17 | 0.13 | 4,176 | 5mvcw2yut4y4m | JDBC Thin Client | select count(*) from t_pay_usr... |
| 0.08 | 17.65 | 0.44 | 0.10 | 1,703 | 4m7m0t6fjcs5x | update seq$ set increment$=:2,... | |
| 0.08 | 2.11 | 3.68 | 0.22 | 18,079 | 9qjbmyfkfp6uk | JDBC Thin Client | select * from ( select f_gen_j... |
| 0.06 | 17.83 | 0.35 | 0.27 | 25 | ayahmu15nhguq | serv_proc@zfyw_payoln (TNS V1-V3) | UPDATE T_PAY_ORD_AUTO_NOTIFY S... |
| 0.06 | 16.78 | 0.34 | 0.03 | 1,889 | bj7vg2gtkat0f | JDBC Thin Client | insert into T_PAY_SMSEND t(t.i... |
| 0.02 | 1.36 | 1.27 | 0.00 | 2,270 | bf7j48bqb1nau | JDBC Thin Client | select userno from t_pay_useri... |
| 0.01 | 2.14 | 0.66 | 0.02 | 4,542 | 3gpn0hhtwbdys | JDBC Thin Client | select t.STATUS, t.REGTYPE, t.... |
| 0.01 | 5.44 | 0.21 | 0.05 | 819 | db78fxqxwxt7r | select /*+ rule */ bucket, end... | |
| 0.01 | 2.04 | 0.48 | 0.01 | 4,542 | f6kqx4vwj0zwa | JDBC Thin Client | select t.MOBILENO from T_PAY_U... |
| 0.01 | 1.38 | 0.61 | 0.03 | 5,646 | df54pnfa76mvh | JDBC Thin Client | insert into T_PAY_USERSUBSCRIB... |
| 0.01 | 25.54 | 0.03 | 0.00 | 18 | dwx4nc99k7q8z | JDBC Thin Client | select * from(select rownum rn... |
| 0.01 | 0.66 | 0.84 | 0.06 | 3,764 | cf1ff4v9jxngz | JDBC Thin Client | insert into T_PAY_CUSTOMERPWDI... |
| 0.01 | 11.51 | 0.05 | 0.04 | 4 | 7aw21806wpgzp | plsqldev.exe | select null from all_synonyms ... |
| 0.01 | 3.24 | 0.16 | 0.14 | 1 | 84qubbrsr0kfn | insert into wrh$_latch (snap_i... | |
| 0.01 | 1.31 | 0.40 | 0.01 | 1,933 | 1u5vdpuk29jk9 | JDBC Thin Client | select * from ( select count(*... |
| 0.01 | 9.83 | 0.05 | 0.02 | 132 | 4s7wm5qdphypk | plsqldev.exe | select value(p$) from "XDB"."X... |
Cluster Wait Time (s) ：集群等待时长
CWT % of Elapsd Time ：集群操作等待时长占总时长的百分比
Elapsed Time(s) ：sql执行总时长
CPU Time(s) ：sql执行消耗cpu的时间
Executions ：sql执行次数
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)