这篇文章主要介绍“怎么理解oracle AWR报告”，在日常操作中，相信很多人在怎么理解ORACLE AWR报告问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解ORAC
这篇文章主要介绍“怎么理解oracle AWR报告”，在日常操作中，相信很多人在怎么理解ORACLE AWR报告问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解ORACLE AWR报告”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
ORACLE AWR报告详细分析
AWR 是 Oracle 10g 版本 推出的新特性， 全称叫Automatic Workload Repository-自动负载信息库
AWR 是通过对比两次快照(snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分。
WORKLOAD REPOSITORY report for
DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
ICCI | 1314098396 | ICCI1 | 1 | 10.2.0.3.0 | YES | HPGICCI1 |
| Snap Id | Snap Time | Sessions | Cursors/Session |
Begin Snap: | 2678 | 25-Dec-08 14:04:50 | 24 | 1.5 |
End Snap: | 2680 | 25-Dec-08 15:23:37 | 26 | 1.5 |
Elapsed: |
| 78.79 (mins) |
|
|
DB Time: |
| 11.05 (mins) |
|
|
DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。
db time= cpu time + wait time（不包含空闲等待） （非后台进程）
说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间
DB time = cpu
time + all of nonidle wait event time
在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPU），
平均每个CPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。
列出下面这两个来做解释：
Report A:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4610 24-Jul-08 22:00:54 68 19.1
End Snap: 4612 24-Jul-08 23:00:25 17 1.7
Elapsed: 59.51 (mins)
DB Time: 466.37 (mins)
Report B:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3098 13-Nov-07 21:00:37 39 13.6
End Snap: 3102 13-Nov-07 22:00:15 40 16.4
Elapsed: 59.63 (mins)
DB Time: 19.49 (mins)
服务器是AIX的系统，4个双核cpu,共8个核:
/sbin> bindprocessor -q
The available processors are: 0 1 2 3 4 5 6 7
先说Report
A,在snapshot间隔中，总共约60分钟，cpu就共有60*8=480分钟，DB
time为466.37分钟
则：cpu花费了466.37分钟在处理Oralce非空闲等待和运算上(比方逻辑读)
也就是说cpu有 466.37/480*100% 花费在处理Oracle的操作上，这还不包括后台进程
看Report B，总共约60分钟，cpu有 19.49/480*100% 花费在处理Oracle的操作上
很显然，Report B中服务器的平均负载很低。
从awr report的Elapsed time和DB
Time就能大概了解db的负载。
可是对于批量系统，数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内，
或者快照周期跨度太长而包含了大量的数据库空闲时间，所得出的分析结果是没有意义的.
这也说明选择分析时间段很关键，要选择能够代表性能问题的时间段。
| Begin | End |
|
|
Buffer Cache: | 3,344M | 3,344M | Std Block Size: | 8K |
Shared Pool Size: | 704M | 704M | Log Buffer: | 14,352K |
显示SGA中每个区域的大小（在AMM改变它们之后），可用来与初始参数值比较。
shared pool主要包括library cache和dictionary cache。
library cache用来存储最近解析（或编译）后sql、PL/SQL和Java classes等。
dictionary cache用来存储最近引用的数据字典。
发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。
因此shared pool的设置要确保最近使用的数据都能被cache。
| Per Second | Per Transaction |
Redo size: | 918,805.72 | 775,912.72 |
Logical reads: | 3,521.77 | 2,974.06 |
Block changes: | 1,817.95 | 1,535.22 |
Physical reads: | 68.26 | 57.64 |
Physical writes: | 362.59 | 306.20 |
User calls: | 326.69 | 275.88 |
Parses: | 38.66 | 32.65 |
Hard parses: | 0.03 | 0.03 |
Sorts: | 0.61 | 0.51 |
LoGons: | 0.01 | 0.01 |
Executes: | 354.34 | 299.23 |
Transactions: | 1.18 |
|
% Blocks changed per Read: | 51.62 | Recursive Call %: | 51.72 |
Rollback per transaction %: | 85.49 | Rows per Sort: | ######## |
显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。
单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，然而
Logons大于每秒1~2个、Hard
parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
Redo size：每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。
Logical reads：每秒/每事务逻辑读的块数.平决每秒产生的逻辑读的block数。Logical Reads= Consistent Gets + DB
Block Gets
Block changes：每秒/每事务修改的块数
Physical reads：每秒/每事务物理读的块数
Physical writes：每秒/每事务物理写的块数
User calls：每秒/每事务用户call次数
Parses：SQL解析的次数.每秒解析次数，包括fast
parse，soft parse和hard parse三种数量的综合。
软解析每秒超过300次意味着你的"应用程序"效率不高，调整session_cursor_cache。
在这里，fast parse指的是直接在PGA中命中的情况（设置了session_cached_cursors=n）；
soft parse是指在shared
pool中命中的情形；hard parse则是指都不命中的情况。
Hard parses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。
每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好，也可能是共享池设置不合理。
这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。但该参数设置为similar时，存在bug，可能导致执行计划的不优。
Sorts：每秒/每事务的排序次数
Logons：每秒/每事务登录的次数
Executes：每秒/每事务SQL执行次数
Transactions：每秒事务数.每秒产生的事务数，反映数据库任务繁重与否。
Blocks changed per Read：表示逻辑读用于修改数据块的比例.在每一次逻辑读中更改的块的百分比。
Recursive Call：递归调用占所有操作的比率.递归调用的百分比，如果有很多PL/SQL，那么这个值就会比较高。
Rollback per
transaction：每事务的回滚率.看回滚率是不是很高，因为回滚很耗资源 ,如果回滚率过高,
可能说明你的数据库经历了太多的无效操作 ,过多的回滚可能还会带来Undo Block的竞争
该参数计算公式如下: Round(User
rollbacks / (user commits + user rollbacks) ,4)* 100% 。
Rows per Sort：每次排序的行数
注:
Oracle的硬解析和软解析
提到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。
当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(prase)
利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
4、执行sql，返回结果(execute and return)
其中，软、硬解析就发生在第三个过程里。
Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
假设存在，则将此sql与cache中的进行比较；
假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析
Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
Buffer Hit %: | 98.72 | In-memory Sort %: | 99.86 |
Library Hit %: | 99.97 | Soft Parse %: | 99.92 |
Execute to Parse %: | 89.09 | Latch Hit %: | 99.99 |
Parse CPU to Parse Elapsd %: | 7.99 | % Non-Parse CPU: | 99.95 |
本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit
Ratio 也称Cache Hit
Ratio，
Library Hit
ratio也称Library
Cache Hit ratio。
同Load Profile一节相同，这一节也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。
在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。
根据Oracle的经验，对于OLTP系统，Buffer Hit Ratio理想应该在90%以上。
Buffer Nowait表示在内存获得数据的未等待比例。在缓冲区中获取Buffer的未等待比率
Buffer Nowait的这个值一般需要大于99%。否则可能存在争用，可以在后面的等待事件中进一步确认。
buffer hit表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。
对于一般的OLTP系统，如果此值低于80%，应该给数据库分配更多的内存。
数据块在数据缓冲区中的命中率，通常应在95%以上。否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size。
一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的db
file sequential read）
但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。命中率的突变，往往是一个不好的信息。
如果命中率突然增大，可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引，
如果命中率突然减小，可以检查top physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。
当redo
buffer达到1M时，就需要写到redo log文件，所以一般当redo buffer设置超过1M，不太可能存在等待buffer空间分配的情况。
当前，一般设置为2M的redo buffer，对于内存总量来说，应该不是一个太大的值。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，
Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。
低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。
如果library hit ratio低于90%，可能需要调大shared
pool区。
STATEMENT在共享区的命中率，通常应该保持在95%以上，否则需要要考虑：加大共享池；使用绑定变量；修改cursor_sharing等参数。
Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。
要确保Latch Hit>99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library
Cache太小，可使用绑定变更或调大Shared
Pool解决。
要确保>99%，否则存在严重的性能问题。当该值出现问题的时候，我们可以借助后面的等待时间和latch分析来查找解决问题。
Parse CPU to Parse
Elapsd：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。
计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time
elapsed)。
即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%，意味着CPU等待时间为0，没有任何等待。
Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。
计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果这个值比较小，表示解析消耗的CPU时间过多。
与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。
Execute to Parse：是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
计算公式为：Execute to Parse =100 *
(1 - Parses/Executions)。
本例中，差不多每execution 5次需要一次parse。所以如果系统Parses > Executions，就可能出现该比率小于0的情况。
该值<0通常说明shared pool设置或者语句效率存在问题，造成反复解析，reparse可能较严重,或者是可能同snapshot有关，通常说明数据库性能存在问题。
In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。
考虑调大PGA(10g)。如果低于95%，可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，
注意这两个参数设置作用的范围时不同的，SORT_AREA_SIZE是针对每个session设置的，PGA_AGGREGATE_TARGET则时针对所有的sesion的。
Soft Parse：软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率，太低则需要调整应用使用绑定变量。
sql在共享区的命中率，小于<95%,需要考虑绑定，如果低于80%，那么就可以认为sql基本没有被重用。
| Begin | End |
Memory Usage %: | 47.19 | 47.50 |
% SQL with executions>1: | 88.48 | 79.81 |
% Memory for SQL w/exec>1: | 79.99 | 73.52 |
Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，
如果太小，说明Shared
Pool有浪费，而如果高于90，说明共享池中有争用，内存不足。
这个数字应该长时间稳定在75%～90%。如果这个百分比太低，表明共享池设置过大，带来额外的管理上的负担，从而在某些条件下会导致性能的下降。
如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。
在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内.
SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。
在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。
在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。
Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。
这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。
这个数字将在总体上与% SQL with
executions>1非常接近，除非有某些查询任务消耗的内存没有规律。
在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，
执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。
小结：通过ORACLE的实例有效性统计数据，我们可以获得大概的一个整体印象，然而我们并不能由此来确定数据运行的性能。当前性能问题的确定，
我们主要还是依靠下面的等待事件来确认。我们可以这样理解两部分的内容，hit统计帮助我们发现和预测一些系统将要产生的性能问题，由此我们
可以做到未雨绸缪。而wait事件，就是表明当前数据库已经出现了性能问题需要解决，所以是亡羊补牢的性质。
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
CPU time |
| 515 |
| 77.6 |
|
SQL*Net more data from client | 27,319 | 64 | 2 | 9.7 | Network |
log file parallel write | 5,497 | 47 | 9 | 7.1 | System I/O |
db file sequential read | 7,900 | 35 | 4 | 5.3 | User I/O |
db file parallel write | 4,806 | 34 | 7 | 5.1 | System I/O |
这是报告概要的最后一节，显示了系统中最严重的5个等待，按所占等待时间的比例倒序列示。当我们调优时，总希望观察到最显著的效果，
因此应当从这里入手确定我们下一步做什么。
例如如果‘buffer
busy wait’是较严重的等待事件，我们应当继续研究报告中Buffer Wait和File/Tablespace
IO区的内容，
识别哪些文件导致了问题。如果最严重的等待事件是I/O事件，我们应当研究按物理读排序的SQL语句区以识别哪些语句在
执行大量I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，就需要察看详细的LATCH
统计识别哪些LATCH产生的问题。
一个性能良好的系统，cpu
time应该在top 5的前面，否则说明你的系统大部分时间都用在等待上。
在这里，log file parallel write是相对比较多的等待，占用了7%的CPU时间。
通常，在没有问题的数据库中，CPU time总是列在第一个。
更多的等待事件，参见本报告 的Wait Events一节。
| Begin | End |
Number of Instances: | 2 | 2 |
| Per Second | Per Transaction |
Global Cache blocks received: | 4.16 | 3.51 |
Global Cache blocks served: | 5.97 | 5.04 |
GCS/GES messages received: | 408.47 | 344.95 |
GCS/GES messages sent: | 258.03 | 217.90 |
DBWR Fusion writes: | 0.05 | 0.05 |
Estd Interconnect traffic (KB) | 211.16 |
|
Buffer access - local cache %: | 98.60 |
Buffer access - remote cache %: | 0.12 |
Buffer access - disk %: | 1.28 |
Avg global enqueue get time (ms): | 0.1 |
Avg global cache cr block receive time (ms): | 1.1 |
Avg global cache current block receive time (ms): | 0.8 |
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 %: | 3.5 |
Avg global cache cr block flush time (ms): | 3.9 |
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.4 |
Avg global cache current block flush time (ms): | 3.0 |
Avg message sent queue time (ms): | 0.0 |
Avg message sent queue time on ksxp (ms): | 0.3 |
Avg message received queue time (ms): | 0.5 |
Avg GCS message process time (ms): | 0.0 |
Avg GES message process time (ms): | 0.0 |
% of direct sent messages: | 14.40 |
% of indirect sent messages: | 77.04 |
% of flow controlled messages: | 8.56 |
Wait Events Statistics
SQL Statistics
Instance Activity Statistics
IO Stats
Buffer Pool Statistics
Advisory Statistics
Wait Statistics
Undo Statistics
Latch Statistics
Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Streams Statistics
Resource Limit Statistics
init.ora Parameters
Time Model Statistics
Wait Class
Wait Events
Background Wait Events
Operating System Statistics
Service Statistics
Service Wait Class Stats
Back to Top
COUNT(*...
0.11
94.54
0.12
0.01
17
bwt0pmxhv7qk7
delete from con$ where owner#=...
0.11
80.26
0.14
0.14
327
53saa2zkr6wc3
select intcol#, nvl(pos#, 0), ...
0.08
19.20
0.42
0.24
1
d92h4rjp0y217
begin prvt_hdm.auto_execute( :...
0.07
54.97
0.13
0.13
83
7ng34ruy5awxq
select i.obj#, i.ts#, i.file#,...
0.06
5.22
1.13
0.72
77
0hhmdwwgxbw0r
select obj#, type#, flags, ...
0.06
86.50
0.06
0.06
45
a2any035u1qz1
select owner#, name from con$...
0.06
8.19
0.67
0.08
1
1uk5m5qbzj1vt
SQL*Plus
BEGIN dbms_workload_repository...
0.04
75.69
0.06
0.06
87
6769wyy3yf66f
select pos#, intcol#, col#, sp...
0.04
48.05
0.09
0.07
7
0pvtkmrrq8usg
select file#, block# from seg...
0.04
8.84
0.40
0.40
6,304
2ym6hhaq30r73
select type#, blocks, extents,...
0.03
28.15
0.12
0.12
49
b52m6vduutr8j
delete from RecycleBin$ ...
0.03
66.23
0.05
0.05
85
1gu8t96d0bdmu
select t.ts#, t.file#, t.block...
0.03
67.03
0.05
0.05
38
btzq46kta67dz
DBMS_SCHEDULER
update obj$ set obj#=:6, type#...
0.02
66.73
0.04
0.04
86
3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_messa...
0.02
26.94
0.09
0.09
38
0k8h717b8guhf
delete from RecycleBin$ ...
0.02
76.76
0.03
0.03
51
9vtm7gy4fr2ny
select con# from con$ where ow...
0.02
51.91
0.05
0.05
84
83taa7kaw59c1
select name, intcol#, segcol#,...
0.02
0.15
14.91
11.17
5
djs2w2f17nw2z
DECLARE job BINARY_INTEGER := ...
0.02
2.12
1.00
0.99
8,784
501v412s13r4m
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCIFNSACT set BORM_FA...
0.02
53.82
0.03
0.03
39
bdv0rkkssq2jm
cusvaamain@HPGICCI1 (TNS V1-V3)
SELECT count(*) FROM user_poli...
0.01
0.10
14.34
14.28
172,983
7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCIFNSACT set BORM_AD...
0.01
8.29
0.16
0.13
421
g00cj285jmgsw
update sys.mon_mods$ set inser...
0.01
1.65
0.56
0.54
2
84qubbrsr0kfn
insert into wrh$_latch (snap...
0.01
22.33
0.04
0.02
26
44au3v5mzpc1c
load_curmmast@HPGICCI1 (TNS V1-V3)
insert into ICCICURMMAST valu...
0.01
0.08
7.71
7.70
172,983
5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3)
select * from ICCIPRODCODE wh...
Back to
SQL Statistics
Back to Top
对于出现在上面的可疑的sql语句，我们可以查看语句相关的执行计划，然后分析相关索引等是否合理。
通过语句查看执行计划的方法:
SELECT id,parent_id,LPAD(' ',4*(LEVEL-1))||operation||' '||options||' '||object_name "Execution plan" ,cost,cardinality,bytes
FROM (
SELECT p.* FROM v$sql_plan p,v$sql s WHERE p.address = s.ADDRESS
AND p.hash_value = s.HASH_VALUE
and p.hash_value = '&hash_value'
)
CONNECT BY PRIOR id = parent_id
START WITH id = 0;
查看，分析，优化索引等在这里就不再一一描述了。
SQL Id | SQL Text |
04xtrk7uyhknh | select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null |
0hhmdwwgxbw0r | select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn |
0k8h717b8guhf | delete from RecycleBin$ where purgeobj=:1 |
0pvtkmrrq8usg | select file#, block# from seg$ where type# = 3 and ts# = :1 |
0v9t4qb1zb2b | select CUID_CUST_NO , CUID_ID_TYPE , CUID_ID_RECNO from CUID_TMP where CHGFLAG='D' |
104pd9mm3fh9p | select blocks, maxblocks, grantor#, priv1, priv2, priv3 from tsq$ where ts#=:1 and user#=:2 |
1crajpb7j5tyz | INSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS ) SELECT :B3 , :B2 , :B1 , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS FROM V$SGA_TARGET_ADVICE |
1dm3bq36vu3g8 | insert into iccifnsact values (:b0, :b1, :b2, null , null , :b3, :b4, GREATEST(:b5, :b6), null , :b7, :b8, null , :b9, :b10, :b6, null , null , null , null , null , :b12, null , null , null , :b13, :b14, null , null , :b15, :b16, :b17) |
1gu8t96d0bdmu | select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) |
1uk5m5qbzj1vt | BEGIN dbms_workload_repository.create_snapshot; END; |
2ym6hhaq30r73 | select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3 |
350f5yrnnmshs | lock table sys.mon_mods$ in exclusive mode nowait |
38apjgr0p55ns | update ICCICCS set CCSMAXOVERDUE=GREATEST(:b0, CCSMAXOVERDUE) where FNSACTNO=:b1 |
38gak8u2qm11w | select count(*) from CUSVAA_TMP |
3m8smr0v7v1m6 | INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13) |
44au3v5mzpc1c | insert into ICCICURMMAST values (:b0, :b1, :b2) |
49ms69srnaxzj | insert into ICCIRPYV values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13, :b14, :b15, :b16, :b17, :b18, :b19, :b20, :b21, :b22, :b23, :b24, :b25, :b26, :b27, :b28, :b29, :b30, :b31, :b32, :b33, :b34, :b35, :b36, :b37, :b38, :b39, :b40, :b41, :b42, :b43, :b44, :b45, :b46, :b47, :b48, :b49, :b50, :b51) |
4vja2k2gdtyup | insert into ICCICCS values (:b0, '////////////////////////', 0, 0, 0, 0, 0, ' ', 0, 0, 0, ' ', '0', null ) |
501v412s13r4m | update ICCIFNSACT set BORM_FACILITY_NO=:b0 where BORM_MEMB_CUST_AC=:b1 |
53saa2zkr6wc3 | select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1 |
569r5k05drsj7 | insert into CUMI select CUSV_CUST_NO , CUSV_EDUCATION_CODE , CHGDATE from CUMI_TMP where CHGFLAG<>'D' |
5c4qu2zmj3gux | select * from ICCIPRODCODE where PRODCODE=to_char(:b0) |
5ngzsfstg8tmy | select o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj#, o.flags from obj$ o where o.obj#=:1 |
6769wyy3yf66f | select pos#, intcol#, col#, spare1, bo#, spare2 from icol$ where obj#=:1 |
6z06gcfw39pkd | SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE), '999, 999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999, 999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999, 999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)), '999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME |
78m9ryygp65v5 | SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION = :B1 |
7gtztzv329wg0 | select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user# |
7ng34ruy5awxq | select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.disTKEy, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# |
7v9dyf5r424yh | select NEWACTNO into :b0 from OLDNEWACT where OLDACTNO=:b1 |
7wwv1ybs9zguz | update ICCIFNSACT set BORM_ADV_DATE=:b0, BOIS_MATURITY_DATE=:b1, BOIS_UNPD_BAL=:b2, BOIS_UNPD_INT=:b3, BOIS_BAL_FINE=:b4, BOIS_INT_FINE=:b5, BOIS_FINE_FINE=:b6, BORM_LOAN_TRM=:b7, BORM_FIVE_STAT=:b8, BOIS_ARREARS_CTR=:b9, BOIS_ARREARS_SUM=:b10 where BORM_MEMB_CUST_AC=:b11 |
83taa7kaw59c1 | select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol# |
4qubbrsr0kfn | insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time from v$latch order by hash |
9qgtwh76xg6nz | update seg$ set type#=:4, blocks=:5, extents=:6, minexts=:7, maxexts=:8, extsize=:9, extpct=:10, user#=:11, iniexts=:12, lists=decode(:13, 65535, NULL, :13), groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17, 0, NULL, :17), scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 |
9vtm7gy4fr2ny | select con# from con$ where owner#=:1 and name=:2 |
a2any035u1qz1 | select owner#, name from con$ where con#=:1 |
a7nh7j8zmfrzw | select CUSV_CUST_NO from CUMI_TMP where CHGFLAG='D' |
Back to
SQL Statistics
Back to Top
Instance Activity Stats
Instance Activity Stats - Absolute Values
Instance Activity Stats - Thread Activity
Back to Top
Statistic | Total | per Second | per Trans |
CPU used by this session | 23,388 | 4.95 | 4.18 |
CPU used when call started | 21,816 | 4.61 | 3.90 |
CR blocks created | 2,794 | 0.59 | 0.50 |
Cached Commit SCN referenced | 237,936 | 50.33 | 42.50 |
Commit SCN cached | 3 | 0.00 | 0.00 |
DB time | 583,424 | 123.41 | 104.22 |
DBWR checkpoint buffers written | 402,781 | 85.20 | 71.95 |
DBWR checkpoints | 9 | 0.00 | 0.00 |
DBWR fusion writes | 255 | 0.05 | 0.05 |
DBWR object drop buffers written | 0 | 0.00 | 0.00 |
DBWR thread checkpoint buffers written | 221,341 | 46.82 | 39.54 |
DBWR transaction table writes | 130 | 0.03 | 0.02 |
DBWR undo block writes | 219,272 | 46.38 | 39.17 |
DFO trees parallelized | 16 | 0.00 | 0.00 |
PX local messages recv'd | 40 | 0.01 | 0.01 |
PX local messages sent | 40 | 0.01 | 0.01 |
PX remote messages recv'd | 80 | 0.02 | 0.01 |
PX remote messages sent | 80 | 0.02 | 0.01 |
Parallel operations not downgraded | 16 | 0.00 | 0.00 |
RowCR - row contention | 9 | 0.00 | 0.00 |
RowCR attempts | 14 | 0.00 | 0.00 |
RowCR hits | 5 | 0.00 | 0.00 |
SMON posted for undo segment recovery | 0 | 0.00 | 0.00 |
SMON posted for undo segment shrink | 9 | 0.00 | 0.00 |
SQL*Net roundtrips to/from client | 1,544,063 | 326.62 | 275.82 |
active txn count during cleanout | 276,652 | 58.52 | 49.42 |
application wait time | 1,620 | 0.34 | 0.29 |
auto extends on undo tablespace | 0 | 0.00 | 0.00 |
background checkpoints completed | 7 | 0.00 | 0.00 |
background checkpoints started | 9 | 0.00 | 0.00 |
background timeouts | 21,703 | 4.59 | 3.88 |
branch node splits | 337 | 0.07 | 0.06 |
buffer is not pinned count | 1,377,184 | 291.32 | 246.01 |
buffer is pinned count | 20,996,139 | 4,441.37 | 3,750.65 |
bytes received via SQL*Net from client | 7,381,397,183 | 1,561,408.36 | 1,318,577.56 |
bytes sent via SQL*Net to client | 149,122,035 | 31,544.22 | 26,638.45 |
calls to get snapshot scn: kcmgss | 1,696,712 | 358.91 | 303.09 |
calls to kcmgas | 433,435 | 91.69 | 77.43 |
calls to kcmgcs | 142,482 | 30.14 | 25.45 |
change write time | 4,707 | 1.00 | 0.84 |
cleanout - number of ktugct calls | 282,045 | 59.66 | 50.38 |
cleanouts and rollbacks - consistent read gets | 55 | 0.01 | 0.01 |
cleanouts only - consistent read gets | 2,406 | 0.51 | 0.43 |
cluster key scan block gets | 21,886 | 4.63 | 3.91 |
cluster key scans | 10,540 | 2.23 | 1.88 |
cluster wait time | 2,855 | 0.60 | 0.51 |
commit batch/immediate performed | 294 | 0.06 | 0.05 |
commit batch/immediate requested | 294 | 0.06 | 0.05 |
commit cleanout failures: block lost | 2,227 | 0.47 | 0.40 |
commit cleanout failures: callback failure | 750 | 0.16 | 0.13 |
commit cleanout failures: cannot pin | 4 | 0.00 | 0.00 |
commit cleanouts | 427,610 | 90.45 | 76.39 |
commit cleanouts successfully completed | 424,629 | 89.82 | 75.85 |
commit immediate performed | 294 | 0.06 | 0.05 |
commit immediate requested | 294 | 0.06 | 0.05 |
commit txn count during cleanout | 111,557 | 23.60 | 19.93 |
concurrency wait time | 515 | 0.11 | 0.09 |
consistent changes | 1,716 | 0.36 | 0.31 |
consistent gets | 5,037,471 | 1,065.59 | 899.87 |
由consistent gets，db block gets和physical reads这三个值，我们也可以计算得到buffer hit ratio，计算的公式如下： buffer hit ratio = 100*(1-physical reads /(consistent gets+ db block gets))，例如在这里，我们可以计算得到：buffer hit ratio =100*(1-26524/(16616758+2941398))= 99.86 | |||
consistent gets - examination | 2,902,016 | 613.87 | 518.40 |
consistent gets direct | 0 | 0.00 | 0.00 |
consistent gets from cache | 5,037,471 | 1,065.59 | 899.87 |
current blocks converted for CR | 0 | 0.00 | 0.00 |
cursor authentications | 434 | 0.09 | 0.08 |
data blocks consistent reads - undo records applied | 1,519 | 0.32 | 0.27 |
db block changes | 8,594,158 | 1,817.95 | 1,535.22 |
db block gets | 11,611,321 | 2,456.18 | 2,074.19 |
db block gets direct | 1,167,830 | 247.03 | 208.62 |
db block gets from cache | 10,443,491 | 2,209.14 | 1,865.58 |
deferred (CURRENT) block cleanout applications | 20,786 | 4.40 | 3.71 |
dirty buffers inspected | 25,007 | 5.29 | 4.47 |
脏数据从LRU列表中老化，A value here indicates that the DBWR is not keeping up。如果这个值大于0，就需要考虑增加DBWRs。 dirty buffers inspected: This is the number of dirty (modified) data buffers that were aged out on the LRU list. You may benefit by adding more DBWRs.If it is greater than 0, consider increasing the database writes. | |||
drop segment calls in space pressure | 0 | 0.00 | 0.00 |
enqueue conversions | 6,734 | 1.42 | 1.20 |
enqueue releases | 595,149 | 125.89 | 106.31 |
enqueue requests | 595,158 | 125.90 | 106.32 |
enqueue timeouts | 9 | 0.00 | 0.00 |
enqueue waits | 7,901 | 1.67 | 1.41 |
exchange deadlocks | 1 | 0.00 | 0.00 |
execute count | 1,675,112 | 354.34 | 299.23 |
free buffer inspected | 536,832 | 113.56 | 95.90 |
这个值包含dirty，pinned，busy的buffer区域，如果free buffer inspected - dirty buffers inspected - buffer is pinned count的值还是比较大，表明不能被重用的内存块比较多，这将导致latch争用，需要增大buffer cache | |||
free buffer requested | 746,999 | 158.01 | 133.44 |
gc CPU used by this session | 9,099 | 1.92 | 1.63 |
gc cr block build time | 13 | 0.00 | 0.00 |
gc cr block flush time | 143 | 0.03 | 0.03 |
gc cr block receive time | 474 | 0.10 | 0.08 |
gc cr block send time | 36 | 0.01 | 0.01 |
gc cr blocks received | 4,142 | 0.88 | 0.74 |
gc cr blocks served | 10,675 | 2.26 | 1.91 |
gc current block flush time | 23 | 0.00 | 0.00 |
gc current block pin time | 34 | 0.01 | 0.01 |
gc current block receive time | 1,212 | 0.26 | 0.22 |
gc current block send time | 52 | 0.01 | 0.01 |
gc current blocks received | 15,502 | 3.28 | 2.77 |
gc current blocks served | 17,534 | 3.71 | 3.13 |
gc local grants | 405,329 | 85.74 | 72.41 |
gc remote grants | 318,630 | 67.40 | 56.92 |
gcs messages sent | 1,129,094 | 238.84 | 201.70 |
ges messages sent | 90,695 | 19.18 | 16.20 |
global enqueue get time | 1,707 | 0.36 | 0.30 |
global enqueue gets async | 12,731 | 2.69 | 2.27 |
global enqueue gets sync | 190,492 | 40.30 | 34.03 |
global enqueue releases | 190,328 | 40.26 | 34.00 |
global undo segment hints helped | 0 | 0.00 | 0.00 |
global undo segment hints were stale | 0 | 0.00 | 0.00 |
heap block compress | 108,758 | 23.01 | 19.43 |
hot buffers moved to head of LRU | 18,652 | 3.95 | 3.33 |
immediate (CR) block cleanout applications | 2,462 | 0.52 | 0.44 |
immediate (CURRENT) block cleanout applications | 325,184 | 68.79 | 58.09 |
index crx upgrade (positioned) | 4,663 | 0.99 | 0.83 |
index fast full scans (full) | 13 | 0.00 | 0.00 |
index fetch by key | 852,181 | 180.26 | 152.23 |
index scans kdiixs1 | 339,583 | 71.83 | 60.66 |
leaf node 90-10 splits | 34 | 0.01 | 0.01 |
leaf node splits | 106,552 | 22.54 | 19.03 |
lob reads | 11 | 0.00 | 0.00 |
lob writes | 83 | 0.02 | 0.01 |
lob writes unaligned | 83 | 0.02 | 0.01 |
local undo segment hints helped | 0 | 0.00 | 0.00 |
local undo segment hints were stale | 0 | 0.00 | 0.00 |
logons cumulative | 61 | 0.01 | 0.01 |
messages received | 20,040 | 4.24 | 3.58 |
messages sent | 19,880 | 4.21 | 3.55 |
no buffer to keep pinned count | 0 | 0.00 | 0.00 |
no work - consistent read gets | 1,513,070 | 320.06 | 270.29 |
opened cursors cumulative | 183,375 | 38.79 | 32.76 |
parse count (failures) | 1 | 0.00 | 0.00 |
parse count (hard) | 143 | 0.03 | 0.03 |
parse count (total) | 182,780 | 38.66 | 32.65 |
通过parse count (hard)和parse count (total)，可以计算soft parse率为： 100-100*(parse count (hard)/parse count (total)) =100-100*(1-6090/191531)=96.82 | |||
parse time cpu | 27 | 0.01 | 0.00 |
parse time elapsed | 338 | 0.07 | 0.06 |
physical read IO requests | 82,815 | 17.52 | 14.79 |
physical read bytes | 2,643,378,176 | 559,161.45 | 472,200.46 |
physical read total IO requests | 98,871 | 20.91 | 17.66 |
physical read total bytes | 2,905,491,456 | 614,607.04 | 519,023.13 |
physical read total multi block requests | 24,089 | 5.10 | 4.30 |
physical reads | 322,678 | 68.26 | 57.64 |
physical reads cache | 213,728 | 45.21 | 38.18 |
physical reads cache prefetch | 191,830 | 40.58 | 34.27 |
physical reads direct | 108,950 | 23.05 | 19.46 |
physical reads direct temporary tablespace | 108,812 | 23.02 | 19.44 |
physical reads prefetch warmup | 0 | 0.00 | 0.00 |
physical write IO requests | 223,456 | 47.27 | 39.92 |
physical write bytes | 14,042,071,040 | 2,970,360.02 | 2,508,408.55 |
physical write total IO requests | 133,835 | 28.31 | 23.91 |
physical write total bytes | 23,114,268,672 | 4,889,428.30 | 4,129,022.63 |
physical write total multi block requests | 116,135 | 24.57 | 20.75 |
physical writes | 1,714,120 | 362.59 | 306.20 |
physical writes direct | 1,276,780 | 270.08 | 228.08 |
physical writes direct (lob) | 0 | 0.00 | 0.00 |
physical writes direct temporary tablespace | 108,812 | 23.02 | 19.44 |
physical writes from cache | 437,340 | 92.51 | 78.12 |
physical writes non checkpoint | 1,673,703 | 354.04 | 298.98 |
pinned buffers inspected | 10 | 0.00 | 0.00 |
prefetch clients - default | 0 | 0.00 | 0.00 |
prefetch warmup blocks aged out before use | 0 | 0.00 | 0.00 |
prefetch warmup blocks flushed out before use | 0 | 0.00 | 0.00 |
prefetched blocks aged out before use | 0 | 0.00 | 0.00 |
process last non-idle time | 4,730 | 1.00 | 0.84 |
queries parallelized | 16 | 0.00 | 0.00 |
recursive calls | 1,654,650 | 350.01 | 295.58 |
recursive cpu usage | 2,641 | 0.56 | 0.47 |
redo blocks written | 8,766,094 | 1,854.32 | 1,565.93 |
redo buffer allocation retries | 24 | 0.01 | 0.00 |
redo entries | 4,707,068 | 995.70 | 840.85 |
redo log space requests | 34 | 0.01 | 0.01 |
redo log space wait time | 50 | 0.01 | 0.01 |
redo ordering marks | 277,042 | 58.60 | 49.49 |
redo size | 4,343,559,400 | 918,805.72 | 775,912.72 |
redo subscn max counts | 2,693 | 0.57 | 0.48 |
redo synch time | 408 | 0.09 | 0.07 |
redo synch writes | 6,984 | 1.48 | 1.25 |
redo wastage | 1,969,620 | 416.64 | 351.84 |
redo write time | 5,090 | 1.08 | 0.91 |
redo writer latching time | 1 | 0.00 | 0.00 |
redo writes | 5,494 | 1.16 | 0.98 |
rollback changes - undo records applied | 166,609 | 35.24 | 29.76 |
rollbacks only - consistent read gets | 1,463 | 0.31 | 0.26 |
rows fetched via callback | 342,159 | 72.38 | 61.12 |
session connect time | 1,461 | 0.31 | 0.26 |
session cursor cache hits | 180,472 | 38.18 | 32.24 |
session logical reads | 16,648,792 | 3,521.77 | 2,974.06 |
session pga memory | 37,393,448 | 7,909.94 | 6,679.79 |
session pga memory max | 45,192,232 | 9,559.64 | 8,072.92 |
session uga memory | 30,067,312,240 | 6,360,225.77 | 5,371,081.14 |
session uga memory max | 61,930,448 | 13,100.33 | 11,062.96 |
shared hash latch upgrades - no wait | 6,364 | 1.35 | 1.14 |
shared hash latch upgrades - wait | 0 | 0.00 | 0.00 |
sorts (disk) | 4 | 0.00 | 0.00 |
磁盘排序一般不能超过5％。如果超过5％，需要设置参数PGA_AGGREGATE_TARGET或者 SORT_AREA_SIZE，注意，这里SORT_AREA_SIZE是分配给每个用户的，PGA_AGGREGATE_TARGET则是针对所有的session的一个总数设置。 | |||
sorts (memory) | 2,857 | 0.60 | 0.51 |
内存中的排序数量 | |||
sorts (rows) | 42,379,505 | 8,964.66 | 7,570.47 |
space was found by tune down | 0 | 0.00 | 0.00 |
space was not found by tune down | 0 | 0.00 | 0.00 |
sql area evicted | 7 | 0.00 | 0.00 |
sql area purged | 44 | 0.01 | 0.01 |
steps of tune down ret. in space pressure | 0 | 0.00 | 0.00 |
summed dirty queue length | 35,067 | 7.42 | 6.26 |
switch current to new buffer | 17 | 0.00 | 0.00 |
table fetch by rowid | 680,469 | 143.94 | 121.56 |
这是通过索引或者where rowid=语句来取得的行数，当然这个值越大越好。 | |||
table fetch continued row | 0 | 0.00 | 0.00 |
这是发生行迁移的行。当行迁移的情况比较严重时，需要对这部分进行优化。 检查行迁移的方法： 1） 运行$ORACLE_HOME/rdbms/admin/utlchain.sql 2） analyze table table_name list chained rows into CHAINED_ROWS 3） select * from CHAINED_ROWS where table_name='table_name'; 清除的方法： 方法1：create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows); Delete from table_name where rowed in (select head_rowid from chained_rows); Insert into table_name select * from table_name_tmp; 方法2：create table table_name_tmp select * from table_name ; truncate table table_name insert into table_name select * from table_name_tmp 方法3：用exp工具导出表，然后删除这个表，最后用imp工具导入这表 方法4：alter table table_name move tablespace tablespace_name，然后再重新表的索引 上面的4种方法可以用以消除已经存在的行迁移现象，但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致，所以需要调整PCT_FREE参数的值。 | |||
table scan blocks gotten | 790,986 | 167.32 | 141.30 |
table scan rows gotten | 52,989,363 | 11,208.99 | 9,465.77 |
table scans (long tables) | 4 | 0.00 | 0.00 |
longtables就是表的大小超过buffer buffer* _SMALL_TABLE_THRESHOLD的表。如果一个数据库的大表扫描过多，那么db file scattered read等待事件可能同样非常显著。如果table scans (long tables)的per Trans值大于0，你可能需要增加适当的索引来优化你的SQL语句 | |||
table scans (short tables) | 169,201 | 35.79 | 30.23 |
short tables是指表的长度低于buffer chache 2％（2％是有隐含参数_SMALL_TABLE_THRESHOLD定义的，这个参数在oracle不同的版本中，有不同的含义。在9i和10g中，该参数值定义为2%，在8i中，该参数值为20个blocks，在v7中，该参数为5个blocks）的表。这些表将优先使用全表扫描。一般不使用索引。_SMALL_TABLE_THRESHOLD值的计算方法如下（9i,8K）： (db_cache_size/8192)*2％。 注意：_SMALL_TABLE_THRESHOLD参数修改是相当危险的操作 | |||
total number of times SMON posted | 259 | 0.05 | 0.05 |
transaction lock background get time | 0 | 0.00 | 0.00 |
transaction lock background gets | 0 | 0.00 | 0.00 |
transaction lock foreground requests | 0 | 0.00 | 0.00 |
transaction lock foreground wait time | 0 | 0.00 | 0.00 |
transaction rollbacks | 294 | 0.06 | 0.05 |
tune down retentions in space pressure | 0 | 0.00 | 0.00 |
undo change vector size | 1,451,085,596 | 306,952.35 | 259,215.00 |
user I/O wait time | 11,992 | 2.54 | 2.14 |
user calls | 1,544,383 | 326.69 | 275.88 |
user commits | 812 | 0.17 | 0.15 |
user rollbacks | 4,786 | 1.01 | 0.85 |
workarea executions - onepass | 1 | 0.00 | 0.00 |
workarea executions - optimal | 1,616 | 0.34 | 0.29 |
write clones created in background | 0 | 0.00 | 0.00 |
write clones created in foreground | 11 | 0.00 | 0.00 |
Back to
Instance Activity Statistics
Back to Top
Statistics with absolute values (should not be diffed)
Statistic | Begin Value | End Value |
session cursor cache count | 3,024 | 3,592 |
opened cursors current | 37 | 39 |
logons current | 24 | 26 |
Back to
Instance Activity Statistics
Back to Top
Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic | Total | per Hour |
log switches (derived) | 9 | 6.85 |
Back to
Instance Activity Statistics
Back to Top
Tablespace IO Stats
File IO Stats
Back to Top
通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。
在这里主要关注Av Rd(ms)列 (reads per millisecond)的值，一般来说，大部分的磁盘系统的这个值都能调整到14ms以下，oracle认为该值超过20ms都是不必要的。如果该值超过1000ms，基本可以肯定存在I/O的性能瓶颈。如果在这一列上出现######，可能是你的系统存在严重的I/O问题，也可能是格式的显示问题。
当出现上面的问题，我们可以考虑以下的方法：
1）优化操作该表空间或者文件的相关的语句。
2）如果该表空间包含了索引，可以考虑压缩索引，是索引的分布空间减小，从而减小I/O。
3）将该表空间分散在多个逻辑卷中，平衡I/O的负载。
4）我们可以通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT来调整读取的并行度，这将提高全表扫描的效率。但是也会带来一个问题，就是oracle会因此更多的使用全表扫描而放弃某些索引的使用。为解决这个问题，我们需要设置另外一个参数OPTIMIZER_INDEX_COST_ADJ=30（一般建议设置10－50）。
关于OPTIMIZER_INDEX_COST_ADJ＝n：该参数是一个百分比值，缺省值为100，可以理解为FULL SCAN COST/INDEX SCAN COST。当n%* INDEX SCAN COST<FULL SCAN COST时，oracle会选择使用索引。在具体设置的时候，我们可以根据具体的语句来调整该值。如果我们希望某个statement使用索引，而实际它确走全表扫描，可以对比这两种情况的执行计划不同的COST，从而设置一个更合适的值。
5）检查并调整I/O设备的性能。
ordered by IOs (Reads + Writes) desc
Tablespace | Reads | Av Reads/s | Av Rd(ms) | Av Blks/Rd | Writes | Av Writes/s | Buffer Waits | Av Buf Wt(ms) |
ICCIDAT01 | 67,408 | 14 | 3.76 | 3.17 | 160,261 | 34 | 6 | 0.00 |
UNDOTBS1 | 10 | 0 | 12.00 | 1.00 | 57,771 | 12 | 625 | 0.02 |
TEMP | 15,022 | 3 | 8.74 | 7.24 | 3,831 | 1 | 0 | 0.00 |
USERS | 68 | 0 | 5.44 | 1.00 | 971 | 0 | 0 | 0.00 |
SYSAUX | 263 | 0 | 5.48 | 1.00 | 458 | 0 | 0 | 0.00 |
SYSTEM | 32 | 0 | 5.94 | 1.00 | 158 | 0 | 3 | 23.33 |
UNDOTBS2 | 6 | 0 | 16.67 | 1.00 | 6 | 0 | 0 | 0.00 |
显示每个表空间的I/O统计。根据Oracle经验，Av Rd(ms) [Average Reads in milliseconds]不应该超过30，否则认为有I/O争用。
Back to
IO Stats
Back to Top
ordered by Tablespace, File
Tablespace | Filename | Reads | Av Reads/s | Av Rd(ms) | Av Blks/Rd | Writes | Av Writes/s | Buffer Waits | Av Buf Wt(ms) |
ICCIDAT01 | /dev/rora_icci01 | 5,919 | 1 | 4.30 | 3.73 | 15,161 | 3 | 1 | 0.00 |
ICCIDAT01 | /dev/rora_icci02 | 7,692 | 2 | 4.12 | 3.18 | 16,555 | 4 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci03 | 6,563 | 1 | 2.59 | 3.80 | 15,746 | 3 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci04 | 8,076 | 2 | 2.93 | 3.11 | 16,164 | 3 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci05 | 6,555 | 1 | 2.61 | 3.31 | 21,958 | 5 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci06 | 6,943 | 1 | 4.03 | 3.41 | 20,574 | 4 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci07 | 7,929 | 2 | 4.12 | 2.87 | 18,263 | 4 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci08 | 7,719 | 2 | 3.83 | 2.99 | 17,361 | 4 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci09 | 6,794 | 1 | 4.79 | 3.29 | 18,425 | 4 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci10 | 211 | 0 | 5.31 | 1.00 | 6 | 0 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci11 | 1,168 | 0 | 4.45 | 1.00 | 6 | 0 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci12 | 478 | 0 | 4.23 | 1.00 | 6 | 0 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci13 | 355 | 0 | 5.13 | 1.00 | 6 | 0 | 0 | 0.00 |
ICCIDAT01 | /dev/rora_icci14 | 411 | 0 | 4.91 | 1.00 | 6 | 0 | 1 | 0.00 |
ICCIDAT01 | /dev/rora_icci15 | 172 | 0 | 5.29 | 1.00 | 6 | 0 | 1 | 0.00 |
ICCIDAT01 | /dev/rora_icci16 | 119 | 0 | 7.23 | 1.00 | 6 | 0 | 1 | 0.00 |
ICCIDAT01 | /dev/rora_icci17 | 227 | 0 | 6.26 | 1.00 | 6 | 0 | 1 | 0.00 |
ICCIDAT01 | /dev/rora_icci18 | 77 | 0 | 8.44 | 1.00 | 6 | 0 | 1 | 0.00 |
SYSAUX | /dev/rora_SYSAUX | 263 | 0 | 5.48 | 1.00 | 458 | 0 | 0 | 0.00 |
SYSTEM | /dev/rora_SYSTEM | 32 | 0 | 5.94 | 1.00 | 158 | 0 | 3 | 23.33 |
TEMP | /dev/rora_TEMP | 3,653 | 1 | 5.67 | 6.61 | 827 | 0 | 0 |
|
TEMP | /dev/rora_TEMP2 | 2,569 | 1 | 4.42 | 6.70 | 556 | 0 | 0 |
|
TEMP | /dev/rora_TEMP3 | 1,022 | 0 | 2.50 | 16.86 | 557 | 0 | 0 |
|
TEMP | /dev/rora_TEMP5 | 7,778 | 2 | 12.43 | 6.46 | 1,891 | 0 | 0 |
|
UNDOTBS1 | /dev/rora_UNDO0101 | 10 | 0 | 12.00 | 1.00 | 57,771 | 12 | 625 | 0.02 |
UNDOTBS2 | /dev/rora_UNDO0201 | 6 | 0 | 16.67 | 1.00 | 6 | 0 | 0 | 0.00 |
USERS | /dev/rora_USERS | 68 | 0 | 5.44 | 1.00 | 971 | 0 | 0 | 0.00 |
Back to
IO Stats
Back to Top
Standard block size Pools D: default, K: keep, R: recycle
Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
P | Number of Buffers | Pool Hit% | Buffer Gets | Physical Reads | Physical Writes | Free Buff Wait | Writ Comp Wait | Buffer Busy Waits |
D | 401,071 | 99 | 15,480,754 | 213,729 | 437,340 | 0 | 0 | 634 |
这里将buffer poll细分，列举default、keep、recycle三种类型的buffer的详细情况。在这份报告中，我们的系统中只使用Default size的buffer pool。这里的3个waits统计，其实在前面的等待时间中已经包含，所以可以参考前面的描述。关于命中率也已经在前面讨论。所以，其实这段信息不需要怎么关注。
Back to Top
Instance Recovery Stats
Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory
Back to Top
B: Begin snapshot, E: End snapshot
| Targt MTTR (s) | Estd MTTR (s) | Recovery Estd IOs | Actual Redo Blks | Target Redo Blks | Log File Size Redo Blks | Log Ckpt Timeout Redo Blks | Log Ckpt Interval Redo Blks |
B | 0 | 11 | 369 | 2316 | 5807 | 1883700 | 5807 |
|
E | 0 | 98 | 116200 | 1828613 | 1883700 | 1883700 | 5033355 |
|
Back to
Advisory Statistics
Back to Top
Only rows with estimated physical reads >0 are displayed
ordered by Block Size, Buffers For Estimate
这是oracle的对buffer pool的大小的调整建议。从advisory的数据看，当然buffer是越大，物理读更小，随着buffer的增大，对物理读的性能改进越来越小。当前buffer 设置为5,120M，物理读因子=1。我们可以看到，buffer pool在3G之前的扩大，对物理读的改善非常明显，之后，这种改善的程度越来越低。
P | Size for Est (M) | Size Factor | Buffers for Estimate | Est Phys Read Factor | Estimated Physical Reads |
D | 320 | 0.10 | 38,380 | 1.34 | 10,351,726 |
D | 640 | 0.19 | 76,760 | 1.25 | 9,657,000 |
D | 960 | 0.29 | 115,140 | 1.08 | 8,365,242 |
D | 1,280 | 0.38 | 153,520 | 1.04 | 8,059,415 |
D | 1,600 | 0.48 | 191,900 | 1.02 | 7,878,202 |
D | 1,920 | 0.57 | 230,280 | 1.01 | 7,841,140 |
D | 2,240 | 0.67 | 268,660 | 1.01 | 7,829,141 |
D | 2,560 | 0.77 | 307,040 | 1.01 | 7,817,370 |
D | 2,880 | 0.86 | 345,420 | 1.01 | 7,804,884 |
D | 3,200 | 0.96 | 383,800 | 1.00 | 7,784,014 |
D | 3,344 | 1.00 | 401,071 | 1.00 | 7,748,403 |
D | 3,520 | 1.05 | 422,180 | 0.99 | 7,702,243 |
D | 3,840 | 1.15 | 460,560 | 0.99 | 7,680,429 |
D | 4,160 | 1.24 | 498,940 | 0.99 | 7,663,046 |
D | 4,480 | 1.34 | 537,320 | 0.99 | 7,653,232 |
D | 4,800 | 1.44 | 575,700 | 0.99 | 7,645,544 |
D | 5,120 | 1.53 | 614,080 | 0.98 | 7,630,008 |
D | 5,440 | 1.63 | 652,460 | 0.98 | 7,616,886 |
D | 5,760 | 1.72 | 690,840 | 0.98 | 7,614,591 |
D | 6,080 | 1.82 | 729,220 | 0.98 | 7,613,191 |
D | 6,400 | 1.91 | 767,600 | 0.98 | 7,599,930 |
Back to
Advisory Statistics
Back to Top
PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % | W/A MB Processed | Extra W/A MB Read/Written |
87.91 | 1,100 | 151 |
Back to
Advisory Statistics
Back to Top
B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
Auto PGA Target - actual workarea memory target
W/A PGA Used - amount of memory used for all Workareas (manual + auto)
%PGA W/A Mem - percentage of PGA memory allocated to workareas
%Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
%Man W/A Mem - percentage of workarea memory under manual control
| PGA Aggr Target(M) | Auto PGA Target(M) | PGA Mem Alloc(M) | W/A PGA Used(M) | %PGA W/A Mem | %Auto W/A Mem | %Man W/A Mem | Global Mem Bound(K) |
B | 1,024 | 862 | 150.36 | 0.00 | 0.00 | 0.00 | 0.00 | 104,850 |
E | 1,024 | 860 | 154.14 | 0.00 | 0.00 | 0.00 | 0.00 | 104,850 |
Back to
Advisory Statistics
Back to Top
Optimal Executions are purely in-memory operations
Low Optimal | High Optimal | Total Execs | Optimal Execs | 1-Pass Execs | M-Pass Execs |
2K | 4K | 1,385 | 1,385 | 0 | 0 |
64K | 128K | 28 | 28 | 0 | 0 |
128K | 256K | 5 | 5 | 0 | 0 |
256K | 512K | 79 | 79 | 0 | 0 |
512K | 1024K | 108 | 108 | 0 | 0 |
1M | 2M | 7 | 7 | 0 | 0 |
8M | 16M | 1 | 1 | 0 | 0 |
128M | 256M | 3 | 2 | 1 | 0 |
256M | 512M | 1 | 1 | 0 | 0 |
Back to
Advisory Statistics
Back to Top
When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB) | Size Factr | W/A MB Processed | Estd Extra W/A MB Read/ Written to Disk | Estd PGA Cache Hit % | Estd PGA Overalloc Count |
128 | 0.13 | 4,652.12 | 2,895.99 | 62.00 | 0 |
256 | 0.25 | 4,652.12 | 2,857.13 | 62.00 | 0 |
512 | 0.50 | 4,652.12 | 2,857.13 | 62.00 | 0 |
768 | 0.75 | 4,652.12 | 2,857.13 | 62.00 | 0 |
1,024 | 1.00 | 4,652.12 | 717.82 | 87.00 | 0 |
1,229 | 1.20 | 4,652.12 | 717.82 | 87.00 | 0 |
1,434 | 1.40 | 4,652.12 | 717.82 | 87.00 | 0 |
1,638 | 1.60 | 4,652.12 | 717.82 | 87.00 | 0 |
1,843 | 1.80 | 4,652.12 | 717.82 | 87.00 | 0 |
2,048 | 2.00 | 4,652.12 | 717.82 | 87.00 | 0 |
3,072 | 3.00 | 4,652.12 | 717.82 | 87.00 | 0 |
4,096 | 4.00 | 4,652.12 | 717.82 | 87.00 | 0 |
6,144 | 6.00 | 4,652.12 | 717.82 | 87.00 | 0 |
8,192 | 8.00 | 4,652.12 | 717.82 | 87.00 | 0 |
Back to
Advisory Statistics
Back to Top
SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
Shared Pool Size(M) | SP Size Factr | Est LC Size (M) | Est LC Mem Obj | Est LC Time Saved (s) | Est LC Time Saved Factr | Est LC Load Time (s) | Est LC Load Time Factr | Est LC Mem Obj Hits |
304 | 0.43 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
384 | 0.55 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
464 | 0.66 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
544 | 0.77 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
624 | 0.89 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
704 | 1.00 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
784 | 1.11 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
864 | 1.23 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
944 | 1.34 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,024 | 1.45 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,104 | 1.57 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,184 | 1.68 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,264 | 1.80 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,344 | 1.91 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
1,424 | 2.02 | 78 | 7,626 | 64,842 | 1.00 | 31 | 1.00 | 3,206,955 |
Back to
Advisory Statistics
Back to Top
SGA Target Size (M) | SGA Size Factor | Est DB Time (s) | Est Physical Reads |
1,024 | 0.25 | 9,060 | 9,742,760 |
2,048 | 0.50 | 7,612 | 7,948,245 |
3,072 | 0.75 | 7,563 | 7,886,258 |
4,096 | 1.00 | 7,451 | 7,748,338 |
5,120 | 1.25 | 7,423 | 7,713,470 |
6,144 | 1.50 | 7,397 | 7,680,927 |
7,168 | 1.75 | 7,385 | 7,666,980 |
8,192 | 2.00 | 7,385 | 7,666,980 |
Back to
Advisory Statistics
Back to Top
No data exists for this section of the report.
Back to
Advisory Statistics
Back to Top
No data exists for this section of the report.
Back to
Advisory Statistics
Back to Top
Buffer Wait Statistics
Enqueue Activity
Back to Top
ordered by wait time desc, waits desc
Class | Waits | Total Wait Time (s) | Avg Time (ms) |
data block | 3 | 0 | 23 |
undo header | 616 | 0 | 0 |
file header block | 8 | 0 | 0 |
undo block | 7 | 0 | 0 |
Back to
Wait Statistics
Back to Top
only enqueues with waits are shown
Enqueue stats gathered prior to 10g should not be compared with 10g data
ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason) | Requests | Succ Gets | Failed Gets | Waits | Wt Time (s) | Av Wt Time(ms) |
FB-Format Block | 14,075 | 14,075 | 0 | 7,033 | 3 | 0.43 |
US-Undo Segment | 964 | 964 | 0 | 556 | 0 | 0.32 |
WF-AWR Flush | 24 | 24 | 0 | 14 | 0 | 9.00 |
HW-Segment High Water Mark | 4,223 | 4,223 | 0 | 37 | 0 | 1.22 |
CF-Controlfile Transaction | 10,548 | 10,548 | 0 | 58 | 0 | 0.67 |
TX-Transaction (index contention) | 1 | 1 | 0 | 1 | 0 | 35.00 |
TM-DML | 121,768 | 121,761 | 6 | 70 | 0 | 0.43 |
PS-PX Process Reservation | 103 | 103 | 0 | 46 | 0 | 0.65 |
TT-Tablespace | 9,933 | 9,933 | 0 | 39 | 0 | 0.54 |
TD-KTF map table enqueue (KTF dump entries) | 12 | 12 | 0 | 12 | 0 | 1.42 |
TA-Instance Undo | 18 | 18 | 0 | 13 | 0 | 0.38 |
PI-Remote PX Process Spawn Status | 16 | 16 | 0 | 8 | 0 | 0.50 |
MW-MWIN Schedule | 3 | 3 | 0 | 3 | 0 | 0.67 |
DR-Distributed Recovery | 3 | 3 | 0 | 3 | 0 | 0.33 |
TS-Temporary Segment | 14 | 11 | 3 | 3 | 0 | 0.33 |
AF-Advisor Framework (task serialization) | 14 | 14 | 0 | 1 | 0 | 1.00 |
js-Job Scheduler (job run lock - synchronize) | 2 | 2 | 0 | 1 | 0 | 1.00 |
UL-User-defined | 2 | 2 | 0 | 1 | 0 | 1.00 |
MD-Materialized View Log DDL | 6 | 6 | 0 | 2 | 0 | 0.00 |
Back to
Wait Statistics
Back to Top
Undo Segment Summary
Undo Segment Stats
Back to Top
Undo从9i开始，回滚段一般都是自动管理的，一般情况下，这里我们不需要太重点关注。
在这里，主要关注pct waits，如果出现比较多的pct waits，那就需要增加回滚段的数量或者增大回滚段的空间。另外，观察一下各个回滚段使用的情况，比较理想的是各个回滚段上Avg Active比较均衡。
在oracle 9i之前，回滚段时手工管理的，可以通过指定optimal值来设定一个回滚段收缩的值，如果不设定，默认也应当为initial+(minextents-1)*next extents ，这个指定的结果，就是限制了回滚段不能无限制的增长，当超过optimal的设定值后，在适当的时候，oracle会shrinks到optimal大小。但是9i之后，undo一般都设置为auto模式，在这种模式下，我们无法指定optimal值，好像也没有默认值，所以无法shrinks，回滚段就会无限制的增长，一直到表空间利用率达到为100％，如果表空间设置为自动扩展的方式，这种情况下，就更糟糕，undo将无限制的增长。在这里，我们也可以看到，shrinks的值为0，也就是说，从来就没收缩过。
Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
STO - Snapshot Too Old count, OOS - Out of Space count
Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS# | Num Undo Blocks (K) | Number of Transactions | Max Qry Len (s) | Max Tx Concurcy | Min/Max TR (mins) | STO/ OOS | uS/uR/uU/ eS/eR/eU |
1 | 219.12 | 113,405 | 0 | 6 | 130.95/239.25 | 0/0 | 0/0/0/13/24256/0 |
Back to
Undo Statistics
Back to Top
Most recent 35 Undostat rows, ordered by Time desc
End Time | Num Undo Blocks | Number of Transactions | Max Qry Len (s) | Max Tx Concy | Tun Ret (mins) | STO/ OOS | uS/uR/uU/ eS/eR/eU |
25-Dec 15:18 | 182,021 | 74,309 | 0 | 5 | 131 | 0/0 | 0/0/0/13/24256/0 |
25-Dec 15:08 | 57 | 170 | 0 | 3 | 239 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:58 | 68 | 31 | 0 | 2 | 229 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:48 | 194 | 4,256 | 0 | 4 | 219 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:38 | 570 | 12,299 | 0 | 5 | 209 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:28 | 36,047 | 21,328 | 0 | 6 | 200 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:18 | 70 | 907 | 0 | 3 | 162 | 0/0 | 0/0/0/0/0/0 |
25-Dec 14:08 | 91 | 105 | 0 | 3 | 154 | 0/0 | 0/0/0/0/0/0 |
Back to
Undo Statistics
Back to Top
Latch Activity
Latch Sleep Breakdown
Latch Miss Sources
Parent Latch Statistics
Child Latch Statistics
Back to Top
Latch是一种低级排队机制，用于防止对内存结构的并行访问，保护系统全局区(SGA)共享内存结构。Latch是一种快速地被获取和释放的内存锁。如果latch不可用，就会记录latch free miss 。
有两种类型的Latch:willing to wait和（immediate）not willing to wait。
对于愿意等待类型(willing-to-wait)的latch,如果一个进程在第一次尝试中没有获得latch,那么它会等待并且再尝试一次,如果经过_spin_count次争夺不能获得latch, 然后该进程转入睡眠状态，百分之一秒之后醒来，按顺序重复以前的步骤。在8i/9i中默认值是_spin_count=2000。睡眠的时间会越来越长。
对于不愿意等待类型(not-willing-to-wait)的latch，如果该闩不能立即得到的话，那么该进程就不会为获得该闩而等待。它将继续执行另一个操作。
大多数Latch问题都可以归结为以下几种:
没有很好的是用绑定变量(library cache latch和shared pool cache)、重作生成问题(redo allocation latch)、缓冲存储竞争问题(cache buffers LRU chain)，以及buffer cache中的存在"热点"块(cache buffers chain)。
另外也有一些latch等待与bug有关，应当关注Metalink相关bug的公布及补丁的发布。
当latch miss ratiOS大于0.5%时，就需要检查latch的等待问题。
如果SQL语句不能调整，在8.1.6版本以上，可以通过设置CURSOR_SHARING = force 在服务器端强制绑定变量。设置该参数可能会带来一定的副作用，可能会导致执行计划不优，另外对于Java的程序，有相关的bug，具体应用应该关注Metalink的bug公告。
下面对几个重要类型的latch等待加以说明：
1) latch free：当‘latch free’在报告的高等待事件中出现时，就表示可能出现了性能问题，就需要在这一部分详细分析出现等待的具体的latch的类型，然后再调整。
2) cache buffers chain：cbc latch表明热块。为什么这会表示存在热块？为了理解这个问题，先要理解cbc的作用。ORACLE对buffer cache管理是以hash链表的方式来实现的（oracle称为buckets，buckets的数量由_db_block_hash_buckets定义）。cbc latch就是为了保护buffer cache而设置的。当有并发的访问需求时，cbc会将这些访问串行化，当我们获得cbc latch的控制权时，就可以开始访问数据，如果我们所请求的数据正好的某个buckets中，那就直接从内存中读取数据，完成之后释放cbc latch，cbc latch就可以被其他的用户获取了。cbc latch获取和释放是非常快速的，所以这种情况下就一般不会存在等待。但是如果我们请求的数据在内存中不存在，就需要到物理磁盘上读取数据，这相对于latch来说就是一个相当长的时间了，当找到对应的数据块时，如果有其他用户正在访问这个数据块，并且数据块上也没有空闲的ITL槽来接收本次请求，就必须等待。在这过程中，我们因为没有得到请求的数据，就一直占有cbc
latch，其他的用户也就无法获取cbc latch，所以就出现了cbc latch等待的情况。所以这种等待归根结底就是由于数据块比较hot的造成的。
解决方法可以参考前面在等待事件中的3)buffer
busy wait中关于热块的解决方法。
3) cache buffers lru chain：该latch用于扫描buffer的LRU链表。三种情况可导致争用：1）buffer cache太小 ；2）buffer cache的过度使用，或者太多的基于cache的排序操作；3）DBWR不及时。解决方法：查找逻辑读过高的statement，增大buffer cache。
4) Library cache and shared
pool 争用：
library cache是一个hash table，我们需要通过一个hash buckets数组来访问（类似buffer cache）。library cache latch就是将对library cache的访问串行化。当有一个sql（或者PL/SQL procedure，package，function，trigger）需要执行的时候，首先需要获取一个latch，然后library cache latch就会去查询library cache以重用这些语句。在8i中，library cache latch只有一个。在9i中，有7个child latch，这个数量可以通过参数_KGL_LATCH_ COUNT修改（最大可以达到66个）。当共享池太小或者语句的reuse低的时候，会出现‘shared pool’、‘library cache pin’或者 ‘library cache’ latch的争用。解决的方法是：增大共享池或者设置CURSOR_SHARING=FORCE|SIMILAR ，当然我们也需要tuning SQL statement。为减少争用，我们也可以把一些比较大的SQL或者过程利用DBMS_SHARED_POOL.KEEP包来pinning在shared pool中。
shared pool内存结构与buffer cache类似，也采用的是hash方式来管理的。共享池有一个固定数量的hash buckets，通过固定数量的library cache latch来串行化保护这段内存的使用。在数据启动的时候，会分配509个hash buctets，2*CPU_COUNT个library cache latch。当在数据库的使用中，共享池中的对象越来越多，oracle就会以以下的递增方式增加hash buckets的数量：509,1021,4093,8191,32749,65521,131071,4292967293。我们可以通过设置下面的参数来实现_KGL_BUCKET_COUNT，参数的默认值是0，代表数量509，最大我们可以设置为8，代表数量131071。
我们可以通过x$ksmsp来查看具体的共享池内存段情况，主要关注下面几个字段：
KSMCHCOM—表示内存段的类型
ksmchptr—表示内存段的物理地址
ksmchsiz—表示内存段的大小
ksmchcls—表示内存段的分类。recr表示a
recreatable piece currently in use that can be a candidate for flushing when
the shared pool is low in available memory; freeabl表示当前正在使用的，能够被释放的段； free表示空闲的未分配的段； perm表示不能被释放永久分配段。
降低共享池的latch 争用，我们主要可以考虑如下的几个事件：
1、使用绑定变量
2、使用cursor sharing
3、设置session_cached_cursors参数。该参数的作用是将cursor从shared pool转移到pga中。减小对共享池的争用。一般初始的值可以设置为100，然后视情况再作调整。
4、设置合适大小的共享池
5) Redo Copy：这个latch用来从PGA中copy redo records到redo log buffer。latch的初始数量是2*COU_OUNT，可以通过设置参数_LOG_SIMULTANEOUS_COPIES在增加latch的数量，减小争用。
6) Redo allocation：该latch用于redo log buffer的分配。减小这种类型的争用的方法有3个：
增大redo log buffer
适当使用nologging选项
避免不必要的commit操作
7) Row cache objects：该latch出现争用，通常表明数据字典存在争用的情况，这往往也预示着过多的依赖于公共同义词的parse。解决方法：1）增大shared pool 2）使用本地管理的表空间，尤其对于索引表空间
Latch事件 | 建议解决方法 |
Library cache | 使用绑定变量; 调整shared_pool_size. |
Shared pool | 使用绑定变量; 调整shared_pool_size. |
Redo allocation | 减小 redo 的产生； 避免不必要的commits. |
Redo copy | 增加 _log_simultaneous_copies. |
Row cache objects | 增加shared_pool_size |
Cache buffers chain | 增大 _DB_BLOCK_HASH_BUCKETS ； make it prime. |
Cache buffers LRU chain | 使用多个缓冲池；调整引起大量逻辑读的查询 |
注：在这里，提到了不少隐藏参数，也有利用隐藏参数来解决latch的方法描述，但是在实际的操作中，强烈建议尽量不要去更改隐藏参数的默认值。
"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
"Pct Misses" for both should be very close to 0.0
Latch Name | Get Requests | Pct Get Miss | Avg Slps /Miss | Wait Time (s) | NoWait Requests | Pct NoWait Miss |
ASM db client latch | 11,883 | 0.00 |
| 0 | 0 |
|
AWR Alerted Metric Element list | 18,252 | 0.00 |
| 0 | 0 |
|
Consistent RBA | 5,508 | 0.02 | 0.00 | 0 | 0 |
|
FOB s.o list latch | 731 | 0.00 |
| 0 | 0 |
|
JS broadcast add buf latch | 6,193 | 0.00 |
| 0 | 0 |
|
JS broadcast drop buf latch | 6,194 | 0.00 |
| 0 | 0 |
|
JS broadcast load blnc latch | 6,057 | 0.00 |
| 0 | 0 |
|
JS mem alloc latch | 8 | 0.00 |
| 0 | 0 |
|
JS queue access latch | 8 | 0.00 |
| 0 | 0 |
|
JS queue state obj latch | 218,086 | 0.00 |
| 0 | 0 |
|
JS slv state obj latch | 31 | 0.00 |
| 0 | 0 |
|
KCL gc element parent latch | 2,803,392 | 0.04 | 0.01 | 0 | 108 | 0.00 |
KJC message pool free list | 43,168 | 0.06 | 0.00 | 0 | 14,532 | 0.01 |
KJCT flow control latch | 563,875 | 0.00 | 0.00 | 0 | 0 |
|
KMG MMAN ready and startup request latch | 1,576 | 0.00 |
| 0 | 0 |
|
KSXR large replies | 320 | 0.00 |
| 0 | 0 |
|
KTF sga latch | 23 | 0.00 |
| 0 | 1,534 | 0.00 |
KWQMN job cache list latch | 352 | 0.00 |
| 0 | 0 |
|
KWQP Prop Status | 5 | 0.00 |
| 0 | 0 |
|
MQL Tracking Latch | 0 |
|
| 0 | 94 | 0.00 |
Memory Management Latch | 0 |
|
| 0 | 1,576 | 0.00 |
OS process | 207 | 0.00 |
| 0 | 0 |
|
OS process allocation | 1,717 | 0.00 |
| 0 | 0 |
|
OS process: request allocation | 73 | 0.00 |
| 0 | 0 |
|
PL/SQL warning settings | 226 | 0.00 |
| 0 | 0 |
|
SGA IO buffer pool latch | 20,679 | 0.06 | 0.00 | 0 | 20,869 | 0.00 |
SQL memory manager latch | 7 | 0.00 |
| 0 | 1,575 | 0.00 |
SQL memory manager workarea list latch | 439,442 | 0.00 |
| 0 | 0 |
|
Shared B-Tree | 182 | 0.00 |
| 0 | 0 |
|
Undo Hint Latch | 0 |
|
| 0 | 12 | 0.00 |
active checkpoint queue latch | 7,835 | 0.00 |
| 0 | 0 |
|
active service list | 50,936 | 0.00 |
| 0 | 1,621 | 0.00 |
arcHive control | 5 | 0.00 |
| 0 | 0 |
|
begin backup scn array | 72,901 | 0.00 | 0.00 | 0 | 0 |
|
business card | 32 | 0.00 |
| 0 | 0 |
|
cache buffer handles | 331,153 | 0.02 | 0.00 | 0 | 0 |
|
cache buffers chains | 48,189,073 | 0.00 | 0.00 | 0 | 1,201,379 | 0.00 |
cache buffers lru chain | 891,796 | 0.34 | 0.00 | 0 | 991,605 | 0.23 |
cache table scan latch | 0 |
|
| 0 | 10,309 | 0.01 |
channel handle pool latch | 99 | 0.00 |
| 0 | 0 |
|
channel operations parent latch | 490,324 | 0.01 | 0.00 | 0 | 0 |
|
checkpoint queue latch | 671,856 | 0.01 | 0.00 | 0 | 555,469 | 0.02 |
client/application info | 335 | 0.00 |
| 0 | 0 |
|
commit callback allocation | 12 | 0.00 |
| 0 | 0 |
|
compile environment latch | 173,428 | 0.00 |
| 0 | 0 |
|
dml lock allocation | 243,087 | 0.00 | 0.00 | 0 | 0 |
|
dummy allocation | 134 | 0.00 |
| 0 | 0 |
|
enqueue hash chains | 1,539,499 | 0.01 | 0.03 | 0 | 263 | 0.00 |
enqueues | 855,207 | 0.02 | 0.00 | 0 | 0 |
|
error message lists | 64 | 0.00 |
| 0 | 0 |
|
event group latch | 38 | 0.00 |
| 0 | 0 |
|
file cache latch | 4,694 | 0.00 |
| 0 | 0 |
|
gcs drop object freelist | 8,451 | 0.19 | 0.00 | 0 | 0 |
|
gcs opaque info freelist | 38,584 | 0.00 | 0.00 | 0 | 0 |
|
gcs partitioned table hash | 9,801,867 | 0.00 |
| 0 | 0 |
|
gcs remaster request queue | 31 | 0.00 |
| 0 | 0 |
|
gcs remastering latch | 1,014,198 | 0.00 | 0.33 | 0 | 0 |
|
gcs resource freelist | 1,154,551 | 0.03 | 0.00 | 0 | 771,650 | 0.00 |
gcs resource hash | 3,815,373 | 0.02 | 0.00 | 0 | 2 | 0.00 |
gcs resource scan list | 4 | 0.00 |
| 0 | 0 |
|
gcs shadows freelist | 795,482 | 0.00 | 0.00 | 0 | 779,648 | 0.00 |
ges caches resource lists | 209,655 | 0.02 | 0.00 | 0 | 121,613 | 0.01 |
ges deadlock list | 840 | 0.00 |
| 0 | 0 |
|
ges domain table | 366,702 | 0.00 |
| 0 | 0 |
|
ges enqueue table freelist | 487,875 | 0.00 |
| 0 | 0 |
|
ges group table | 543,887 | 0.00 |
| 0 | 0 |
|
ges process hash list | 59,503 | 0.00 |
| 0 | 0 |
|
ges process parent latch | 908,232 | 0.00 |
| 0 | 1 | 0.00 |
ges process table freelist | 73 | 0.00 |
| 0 | 0 |
|
ges resource hash list | 862,590 | 0.02 | 0.28 | 0 | 72,266 | 0.01 |
ges resource scan list | 534 | 0.00 |
| 0 | 0 |
|
ges resource table freelist | 135,406 | 0.00 | 0.00 | 0 | 0 |
|
ges synchronous data | 160 | 0.63 | 0.00 | 0 | 2,954 | 0.07 |
ges timeout list | 3,256 | 0.00 |
| 0 | 4,478 | 0.00 |
global KZLD latch for mem in SGA | 21 | 0.00 |
| 0 | 0 |
|
hash table column usage latch | 59 | 0.00 |
| 0 | 1,279 | 0.00 |
hash table modification latch | 116 | 0.00 |
| 0 | 0 |
|
job workq parent latch | 0 |
|
| 0 | 14 | 0.00 |
job_queue_processes parameter latch | 86 | 0.00 |
| 0 | 0 |
|
kks stats | 384 | 0.00 |
| 0 | 0 |
|
ksuosstats global area | 329 | 0.00 |
| 0 | 0 |
|
ktm global data | 296 | 0.00 |
| 0 | 0 |
|
kwqbsn:qsga | 182 | 0.00 |
| 0 | 0 |
|
lgwr LWN SCN | 6,547 | 0.18 | 0.00 | 0 | 0 |
|
library cache | 235,060 | 0.00 | 0.00 | 0 | 22 | 0.00 |
library cache load lock | 486 | 0.00 |
| 0 | 0 |
|
library cache lock | 49,284 | 0.00 |
| 0 | 0 |
|
library cache lock allocation | 566 | 0.00 |
| 0 | 0 |
|
library cache pin | 27,863 | 0.00 | 0.00 | 0 | 0 |
|
library cache pin allocation | 204 | 0.00 |
| 0 | 0 |
|
list of block allocation | 10,101 | 0.00 |
| 0 | 0 |
|
loader state object freelist | 108 | 0.00 |
| 0 | 0 |
|
longop free list parent | 6 | 0.00 |
| 0 | 6 | 0.00 |
message pool operations parent latch | 1,424 | 0.00 |
| 0 | 0 |
|
messages | 222,581 | 0.00 | 0.00 | 0 | 0 |
|
mostly latch-free SCN | 6,649 | 1.43 | 0.00 | 0 | 0 |
|
multiblock read objects | 29,230 | 0.03 | 0.00 | 0 | 0 |
|
name-service memory objects | 18,842 | 0.00 |
| 0 | 0 |
|
name-service namespace bucket | 56,712 | 0.00 |
| 0 | 0 |
|
name-service namespace objects | 15 | 0.00 |
| 0 | 0 |
|
name-service pending queue | 6,436 | 0.00 |
| 0 | 0 |
|
name-service request | 44 | 0.00 |
| 0 | 0 |
|
name-service request queue | 57,312 | 0.00 |
| 0 | 0 |
|
ncodef allocation latch | 77 | 0.00 |
| 0 | 0 |
|
object queue header heap | 37,721 | 0.00 |
| 0 | 7,457 | 0.00 |
object queue header operation | 2,706,992 | 0.06 | 0.00 | 0 | 0 |
|
object stats modification | 22 | 0.00 |
| 0 | 0 |
|
parallel query alloc buffer | 939 | 0.00 |
| 0 | 0 |
|
parallel query stats | 72 | 0.00 |
| 0 | 0 |
|
parallel txn reco latch | 630 | 0.00 |
| 0 | 0 |
|
parameter list | 193 | 0.00 |
| 0 | 0 |
|
parameter table allocation management | 68 | 0.00 |
| 0 | 0 |
|
post/wait queue | 4,205 | 0.00 |
| 0 | 2,712 | 0.00 |
process allocation | 46,895 | 0.00 |
| 0 | 38 | 0.00 |
process group creation | 73 | 0.00 |
| 0 | 0 |
|
process queue | 175 | 0.00 |
| 0 | 0 |
|
process queue reference | 2,621 | 0.00 |
| 0 | 240 | 62.50 |
qmn task queue latch | 668 | 0.15 | 1.00 | 0 | 0 |
|
query server freelists | 159 | 0.00 |
| 0 | 0 |
|
query server process | 8 | 0.00 |
| 0 | 7 | 0.00 |
queued dump request | 23,628 | 0.00 |
| 0 | 0 |
|
redo allocation | 21,206 | 0.57 | 0.00 | 0 | 4,706,826 | 0.02 |
redo copy | 0 |
|
| 0 | 4,707,106 | 0.01 |
redo writing | 29,944 | 0.01 | 0.00 | 0 | 0 |
|
resmgr group change latch | 69 | 0.00 |
| 0 | 0 |
|
resmgr:actses active list | 137 | 0.00 |
| 0 | 0 |
|
resmgr:actses change group | 52 | 0.00 |
| 0 | 0 |
|
resmgr:free threads list | 130 | 0.00 |
| 0 | 0 |
|
resmgr:schema config | 7 | 0.00 |
| 0 | 0 |
|
row cache objects | 1,644,149 | 0.00 | 0.00 | 0 | 321 | 0.00 |
rules engine rule set statistics | 500 | 0.00 |
| 0 | 0 |
|
sequence cache | 360 | 0.00 |
| 0 | 0 |
|
session allocation | 535,514 | 0.00 | 0.00 | 0 | 0 |
|
session idle bit | 3,262,141 | 0.00 | 0.00 | 0 | 0 |
|
session state list latch | 166 | 0.00 |
| 0 | 0 |
|
session switching | 77 | 0.00 |
| 0 | 0 |
|
session timer | 1,620 | 0.00 |
| 0 | 0 |
|
shared pool | 60,359 | 0.00 | 0.00 | 0 | 0 |
|
shared pool sim alloc | 13 | 0.00 |
| 0 | 0 |
|
shared pool simulator | 4,246 | 0.00 |
| 0 | 0 |
|
simulator hash latch | 1,862,803 | 0.00 |
| 0 | 0 |
|
simulator lru latch | 1,719,480 | 0.01 | 0.00 | 0 | 46,053 | 0.00 |
slave class | 2 | 0.00 |
| 0 | 0 |
|
slave class create | 8 | 12.50 | 1.00 | 0 | 0 |
|
sort extent pool | 1,284 | 0.00 |
| 0 | 0 |
|
state object free list | 4 | 0.00 |
| 0 | 0 |
|
statistics aggregation | 280 | 0.00 |
| 0 | 0 |
|
temp lob duration state obj allocation | 2 | 0.00 |
| 0 | 0 |
|
threshold alerts latch | 202 | 0.00 |
| 0 | 0 |
|
transaction allocation | 211 | 0.00 |
| 0 | 0 |
|
transaction branch allocation | 77 | 0.00 |
| 0 | 0 |
|
undo global data | 779,759 | 0.07 | 0.00 | 0 | 0 |
|
user lock | 102 | 0.00 |
| 0 | 0 |
|
Back to
Latch Statistics
Back to Top
ordered by misses desc
Latch Name | Get Requests | Misses | Sleeps | Spin Gets | Sleep1 | Sleep2 | Sleep3 |
cache buffers lru chain | 891,796 | 3,061 | 1 | 3,060 | 0 | 0 | 0 |
object queue header operation | 2,706,992 | 1,755 | 3 | 1,752 | 0 | 0 | 0 |
KCL gc element parent latch | 2,803,392 | 1,186 | 11 | 1,176 | 0 | 0 | 0 |
cache buffers chains | 48,189,073 | 496 | 1 | 495 | 0 | 0 | 0 |
ges resource hash list | 862,590 | 160 | 44 | 116 | 0 | 0 | 0 |
enqueue hash chains | 1,539,499 | 79 | 2 | 78 | 0 | 0 | 0 |
gcs remastering latch | 1,014,198 | 3 | 1 | 2 | 0 | 0 | 0 |
qmn task queue latch | 668 | 1 | 1 | 0 | 0 | 0 | 0 |
slave class create | 8 | 1 | 1 | 0 | 0 | 0 | 0 |
Back to
Latch Statistics
Back to Top
only latches with sleeps are shown
ordered by name, sleeps desc
Latch Name | Where | NoWait Misses | Sleeps | Waiter Sleeps |
KCL gc element parent latch | kclrwrite | 0 | 8 | 0 |
KCL gc element parent latch | kclnfndnewm | 0 | 4 | 6 |
KCL gc element parent latch | KCLUNLNK | 0 | 1 | 1 |
KCL gc element parent latch | kclbla | 0 | 1 | 0 |
KCL gc element parent latch | kclulb | 0 | 1 | 1 |
KCL gc element parent latch | kclzcl | 0 | 1 | 0 |
cache buffers chains | kcbnew: new latch again | 0 | 2 | 0 |
cache buffers chains | kclwrt | 0 | 1 | 0 |
cache buffers lru chain | kcbzgws | 0 | 1 | 0 |
enqueue hash chains | ksqcmi: if lk mode not requested | 0 | 2 | 0 |
event range base latch | No latch | 0 | 1 | 1 |
gcs remastering latch | 69 | 0 | 1 | 0 |
ges resource hash list | kjlmfnd: search for lockp by rename and inst id | 0 | 23 | 0 |
ges resource hash list | kjakcai: search for resp by resname | 0 | 13 | 0 |
ges resource hash list | kjrmas1: lookup master node | 0 | 5 | 0 |
ges resource hash list | kjlrlr: remove lock from resource queue | 0 | 2 | 33 |
ges resource hash list | kjcvscn: remove from scan queue | 0 | 1 | 0 |
object queue header operation | kcbo_switch_q_bg | 0 | 3 | 0 |
object queue header operation | kcbo_switch_mq_bg | 0 | 2 | 4 |
object queue header operation | kcbw_unlink_q | 0 | 2 | 0 |
object queue header operation | kcbw_link_q | 0 | 1 | 0 |
slave class create | ksvcreate | 0 | 1 | 0 |
Back to
Latch Statistics
Back to Top
No data exists for this section of the report.
Back to
Latch Statistics
Back to Top
No data exists for this section of the report.
Back to
Latch Statistics
Back to Top
Segments by Logical Reads
Segments by Physical Reads
Segments by Row Lock Waits
Segments by ITL Waits
Segments by Buffer Busy Waits
Segments by Global Cache Buffer Busy
Segments by CR Blocks Received
Segments by Current Blocks Received
Back to Top
DBA_HIST_SEG_STAT
desc DBA_HIST_SEG_STAT
v$sesstat
v$statname
Total Logical Reads: 16,648,792
Captured Segments account for 85.2% of Total
Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Logical Reads | %Total |
ICCI01 | ICCIDAT01 | ICCICCS_PK |
| INDEX | 1,544,848 | 9.28 |
ICCI01 | ICCIDAT01 | CUSCAD_TMP |
| TABLE | 1,349,536 | 8.11 |
ICCI01 | ICCIDAT01 | ICCIFNSACT_PK |
| INDEX | 1,268,400 | 7.62 |
ICCI01 | ICCIDAT01 | IND_OLDNEWACT |
| INDEX | 1,071,072 | 6.43 |
ICCI01 | ICCIDAT01 | CUID_PK |
| INDEX | 935,584 | 5.62 |
Back to
Segment Statistics
Back to Top
Total Physical Reads: 322,678
Captured Segments account for 64.2% of Total
Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Physical Reads | %Total |
ICCI01 | ICCIDAT01 | CUID_TMP |
| TABLE | 116,417 | 36.08 |
ICCI01 | ICCIDAT01 | CUMI_TMP |
| TABLE | 44,086 | 13.66 |
ICCI01 | ICCIDAT01 | CUSM_TMP |
| TABLE | 26,078 | 8.08 |
ICCI01 | ICCIDAT01 | CUSVAA_TMP_PK |
| INDEX | 19,554 | 6.06 |
ICCI01 | ICCIDAT01 | CUID |
| TABLE | 259 | 0.08 |
Back to
Segment Statistics
Back to Top
当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量INSERT操作。
No data exists for this section of the report.
Back to
Segment Statistics
Back to Top
Segments by ITL Waits
No data exists for this section of the report.
Back to
Segment Statistics
Back to Top
No data exists for this section of the report.
Back to
Segment Statistics
Back to Top
% of Capture shows % of GC Buffer Busy for each top segment compared
with GC Buffer Busy for all segments captured by the Snapshot
Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | GC Buffer Busy | % of Capture |
SYS | SYSTEM | TSQ$ |
| TABLE | 2 | 100.00 |
Back to
Segment Statistics
Back to Top
Total CR Blocks Received: 4,142
Captured Segments account for 95.6% of Total
Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | CR Blocks Received | %Total |
SYS | SYSTEM | USER$ |
| TABLE | 1,001 | 24.17 |
SYS | SYSTEM | TSQ$ |
| TABLE | 722 | 17.43 |
SYS | SYSTEM | SEG$ |
| TABLE | 446 | 10.77 |
SYS | SYSTEM | OBJ$ |
| TABLE | 264 | 6.37 |
SYS | SYSTEM | I_OBJ2 |
| INDEX | 174 | 4.20 |
Back to
Segment Statistics
Back to Top
Total Current Blocks Received: 15,502
Captured Segments account for 84.8% of Total
Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Current Blocks Received | %Total |
ICCI01 | ICCIDAT01 | CUSM_TMP |
| TABLE | 5,764 | 37.18 |
ICCI01 | ICCIDAT01 | CUMI_TMP |
| TABLE | 2,794 | 18.02 |
ICCI01 | ICCIDAT01 | CUID_TMP |
| TABLE | 2,585 | 16.68 |
SYS | SYSTEM | SEG$ |
| TABLE | 361 | 2.33 |
SYS | SYSTEM | TSQ$ |
| TABLE | 361 | 2.33 |
Back to
Segment Statistics
Back to Top
Dictionary Cache Stats
Dictionary Cache Stats (RAC)
Back to Top
/* 库缓存详细信息，。
Get Requests：get表示一种类型的锁，语法分析锁。这种类型的锁在引用了一个对象的那条SQL语句的语法分析阶段被设置在该对象上。每当一条语句被语法分析一次时，Get Requests的值就增加1。
pin requests：pin也表示一种类型的锁，是在执行发生的加锁。每当一条语句执行一次，pin requests的值就增加1。
reloads：reloads列显示一条已执行过的语句因Library Cache使该语句的已语法分析版本过期或作废而需要被重新语法分析的次数。
invalidations：失效发生在一条已告诉缓存的SQL语句即使已经在library cache中，但已被标记为无效并迎词而被迫重新做语法分析的时候。每当已告诉缓存的语句所引用的对象以某种方式被修改时，这些语句就被标记为无效。
pct miss应该不高于1％。
Reloads /pin requests <1％，否则应该考虑增大SHARED_POOL_SIZE。
该部分信息通过v$librarycache视图统计得到：
select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER', 'INDEX');
"Pct Misses" should be very low (< 2% in most cases)
"Final Usage" is the number of cache entries being used
Cache | Get Requests | Pct Miss | Scan Reqs | Pct Miss | Mod Reqs | Final Usage |
dc_awr_control | 86 | 0.00 | 0 |
| 4 | 1 |
dc_constraints | 59 | 91.53 | 0 |
| 20 | 1,350 |
dc_files | 23 | 0.00 | 0 |
| 0 | 23 |
dc_global_oids | 406 | 0.00 | 0 |
| 0 | 35 |
dc_histogram_data | 673 | 0.15 | 0 |
| 0 | 1,555 |
dc_histogram_defs | 472 | 24.36 | 0 |
| 0 | 4,296 |
dc_object_grants | 58 | 0.00 | 0 |
| 0 | 154 |
dc_object_ids | 1,974 | 6.13 | 0 |
| 0 | 1,199 |
dc_objects | 955 | 19.58 | 0 |
| 56 | 2,064 |
dc_profiles | 30 | 0.00 | 0 |
| 0 | 1 |
dc_rollback_segments | 3,358 | 0.00 | 0 |
| 0 | 37 |
dc_segments | 2,770 | 2.56 | 0 |
| 1,579 | 1,312 |
dc_sequences | 9 | 33.33 | 0 |
| 9 | 5 |
dc_table_scns | 6 | 100.00 | 0 |
| 0 | 0 |
dc_tablespace_quotas | 1,558 | 28.50 | 0 |
| 1,554 | 3 |
dc_tablespaces | 346,651 | 0.00 | 0 |
| 0 | 7 |
dc_usernames | 434 | 0.00 | 0 |
| 0 | 14 |
dc_users | 175,585 | 0.00 | 0 |
| 0 | 43 |
outstanding_alerts | 57 | 71.93 | 0 |
| 0 | 1 |
Back to
Dictionary Cache Statistics
Back to Top
Cache | GES Requests | GES Conflicts | GES Releases |
dc_awr_control | 8 | 0 | 0 |
dc_constraints | 88 | 22 | 0 |
dc_histogram_defs | 115 | 0 | 0 |
dc_object_ids | 143 | 101 | 0 |
dc_objects | 253 | 111 | 0 |
dc_segments | 3,228 | 49 | 0 |
dc_sequences | 17 | 3 | 0 |
dc_table_scns | 6 | 0 | 0 |
dc_tablespace_quotas | 3,093 | 441 | 0 |
dc_users | 8 | 1 | 0 |
outstanding_alerts | 113 | 41 | 0 |
Back to
Dictionary Cache Statistics
Back to Top
Library Cache Activity
Library Cache Activity (RAC)
Back to Top
"Pct Misses" should be very low
Namespace | Get Requests | Pct Miss | Pin Requests | Pct Miss | Reloads | Invali- dations |
BODY | 105 | 0.00 | 247 | 0.00 | 0 | 0 |
CLUSTER | 3 | 0.00 | 4 | 0.00 | 0 | 0 |
INDEX | 13 | 46.15 | 26 | 42.31 | 5 | 0 |
SQL AREA | 56 | 100.00 | 1,857,002 | 0.02 | 32 | 12 |
TABLE/PROCEDURE | 179 | 35.75 | 3,477 | 8.02 | 63 | 0 |
TRIGGER | 323 | 0.00 | 386 | 0.00 | 0 | 0 |
Back to
Library Cache Statistics
Back to Top
Namespace | GES Lock Requests | GES Pin Requests | GES Pin Releases | GES Inval Requests | GES Invali- dations |
BODY | 5 | 0 | 0 | 0 | 0 |
CLUSTER | 4 | 0 | 0 | 0 | 0 |
INDEX | 26 | 22 | 6 | 17 | 0 |
TABLE/PROCEDURE | 1,949 | 285 | 63 | 244 | 0 |
Back to
Library Cache Statistics
Back to Top
Process Memory Summary
SGA Memory Summary
SGA breakdown difference
Back to Top
B: Begin snap E: End snap
All rows below contain absolute values (i.e. not diffed over the interval)
Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
ordered by Begin/End snapshot, Alloc (MB) desc
| Category | Alloc (MB) | Used (MB) | Avg Alloc (MB) | Std Dev Alloc (MB) | Max Alloc (MB) | Hist Max Alloc (MB) | Num Proc | Num Alloc |
B | Other | 136.42 |
| 5.25 | 8.55 | 24 | 27 | 26 | 26 |
| Freeable | 13.50 | 0.00 | 1.50 | 1.11 | 3 |
| 9 | 9 |
| SQL | 0.33 | 0.16 | 0.03 | 0.03 | 0 | 2 | 12 | 10 |
| PL/SQL | 0.12 | 0.06 | 0.01 | 0.01 | 0 | 0 | 24 | 24 |
E | Other | 138.65 |
| 4.78 | 8.20 | 24 | 27 | 29 | 29 |
| Freeable | 14.94 | 0.00 | 1.36 | 1.04 | 3 |
| 11 | 11 |
| SQL | 0.39 | 0.19 | 0.03 | 0.03 | 0 | 2 | 15 | 12 |
| PL/SQL | 0.18 | 0.11 | 0.01 | 0.01 | 0 | 0 | 27 | 26 |
Back to
Memory Statistics
Back to Top
这部分是关于SGA内存分配的一个描述，我们可以通过show sga等命令也可以查看到这里的内容。
Fixed Size:
oracle 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了SGA 各部分组件的信息，可以看作引导建立SGA的区域。
Variable Size:
包含了shared_pool_size、java_pool_size、large_pool_size 等内存设置。
Database Buffers:
指数据缓冲区，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分内存。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size。
Redo Buffers:
指日志缓冲区，log_buffer。对于logbuffer，我们会发现在v$parameter、v$sgastat、v$sga的值不一样。v$parameter是我们可以自己设定的值，也可以设定为0，这时候，oracle降会以默认的最小值来设置v$sgastat的值，同时v$sga也是最小的值。v$sgastat的值是基于参数log_buffer的设定值，再根据一定的计算公式得到的一个值。v$sga的值，则是根据v$sgastat的值，然后选择再加上8k－11k的一个值，得到min(n*4k)的一个值。就是说得到的结果是4k的整数倍，也就是说v$sga是以4k的单位递增的。
SGA regions | Begin Size (Bytes) | End Size (Bytes) (if different) |
Database Buffers | 3,506,438,144 |
|
Fixed Size | 2,078,368 |
|
Redo Buffers | 14,696,448 |
|
Variable Size | 771,754,336 |
|
Back to
Memory Statistics
Back to Top
ordered by Pool, Name
N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
Pool | Name | Begin MB | End MB | % Diff |
java | free memory | 16.00 | 16.00 | 0.00 |
large | PX msg pool | 1.03 | 1.03 | 0.00 |
large | free memory | 14.97 | 14.97 | 0.00 |
shared | ASH buffers | 15.50 | 15.50 | 0.00 |
shared | CCursor | 8.58 | 8.85 | 3.09 |
shared | KQR L PO | 8.75 | 8.80 | 0.55 |
shared | db_block_hash_buckets | 22.50 | 22.50 | 0.00 |
shared | free memory | 371.80 | 369.61 | -0.59 |
shared | gcs resources | 66.11 | 66.11 | 0.00 |
shared | gcs shadows | 41.65 | 41.65 | 0.00 |
shared | ges big msg buffers | 13.75 | 13.75 | 0.00 |
shared | ges enqueues | 7.44 | 7.56 | 1.63 |
shared | ges reserved msg buffers | 7.86 | 7.86 | 0.00 |
shared | library cache | 10.78 | 10.93 | 1.41 |
shared | row cache | 7.16 | 7.16 | 0.00 |
shared | sql area | 27.49 | 28.50 | 3.67 |
| buffer_cache | 3,344.00 | 3,344.00 | 0.00 |
| fixed_sga | 1.98 | 1.98 | 0.00 |
| log_buffer | 14.02 | 14.02 | 0.00 |
Back to
Memory Statistics
Back to Top
Streams CPU/IO Usage
Streams Capture
Streams Apply
Buffered Queues
Buffered Subscribers
Rule Set
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
No data exists for this section of the report.
Back to
Streams Statistics
Back to Top
only rows with Current or Maximum Utilization > 80% of Limit are shown
ordered by resource name
Resource Name | Current Utilization | Maximum Utilization | Initial Allocation | Limit |
gcs_resources | 349,392 | 446,903 | 450063 | 450063 |
gcs_shadows | 400,300 | 447,369 | 450063 | 450063 |
Back to Top
Parameter Name | Begin value | End value (if different) |
audit_file_dest | /oracle/app/oracle/admin/ICCI/adump |
|
background_dump_dest | /oracle/app/oracle/admin/ICCI/bdump |
|
cluster_database | TRUE |
|
cluster_database_instances | 2 |
|
compatible | 10.2.0.3.0 |
|
control_files | /dev/rora_CTL01, /dev/rora_CTL02, /dev/rora_CTL03 |
|
core_dump_dest | /oracle/app/oracle/admin/ICCI/cdump |
|
db_block_size | 8192 |
|
db_domain |
|
|
db_file_multiblock_read_count | 16 |
|
db_name | ICCI |
|
dispatchers | (PROTOCOL=tcp) (SERVICE=ICCIXDB) |
|
instance_number | 1 |
|
job_queue_processes | 10 |
|
open_cursors | 800 |
|
pga_aggregate_target | 1073741824 |
|
processes | 500 |
|
remote_listener | LISTENERS_ICCI |
|
remote_login_passWordfile | EXCLUSIVE |
|
sga_max_size | 4294967296 |
|
sga_target | 4294967296 |
|
sort_area_size | 196608 |
|
spfile | /dev/rora_SPFILE |
|
thread | 1 |
|
undo_management | AUTO |
|
undo_retention | 900 |
|
undo_tablespace | UNDOTBS1 |
|
user_dump_dest | /oracle/app/oracle/admin/ICCI/udump |
|
Back to Top
Global Enqueue Statistics
Global CR Served Stats
Global CURRENT Served Stats
Global Cache Transfer Stats
Back to Top
Statistic | Total | per Second | per Trans |
acks for commit broadcast(actual) | 18,537 | 3.92 | 3.31 |
acks for commit broadcast(logical) | 21,016 | 4.45 | 3.75 |
broadcast msgs on commit(actual) | 5,193 | 1.10 | 0.93 |
broadcast msgs on commit(logical) | 5,491 | 1.16 | 0.98 |
broadcast msgs on commit(wasted) | 450 | 0.10 | 0.08 |
dynamically allocated gcs resources | 0 | 0.00 | 0.00 |
dynamically allocated gcs shadows | 0 | 0.00 | 0.00 |
false posts waiting for scn acks | 0 | 0.00 | 0.00 |
flow control messages received | 0 | 0.00 | 0.00 |
flow control messages sent | 2 | 0.00 | 0.00 |
gcs assume cvt | 0 | 0.00 | 0.00 |
gcs assume no cvt | 9,675 | 2.05 | 1.73 |
gcs ast xid | 1 | 0.00 | 0.00 |
gcs blocked converts | 7,099 | 1.50 | 1.27 |
gcs blocked cr converts | 8,442 | 1.79 | 1.51 |
gcs compatible basts | 45 | 0.01 | 0.01 |
gcs compatible cr basts (global) | 273 | 0.06 | 0.05 |
gcs compatible cr basts (local) | 12,593 | 2.66 | 2.25 |
gcs cr basts to PIs | 0 | 0.00 | 0.00 |
gcs cr serve without current lock | 0 | 0.00 | 0.00 |
gcs dbwr flush pi msgs | 223 | 0.05 | 0.04 |
gcs dbwr write request msgs | 223 | 0.05 | 0.04 |
gcs error msgs | 0 | 0.00 | 0.00 |
gcs forward cr to pinged instance | 0 | 0.00 | 0.00 |
gcs immediate (compatible) converts | 2,998 | 0.63 | 0.54 |
gcs immediate (null) converts | 170,925 | 36.16 | 30.53 |
gcs immediate cr (compatible) converts | 0 | 0.00 | 0.00 |
gcs immediate cr (null) converts | 722,748 | 152.88 | 129.11 |
gcs indirect ast | 306,817 | 64.90 | 54.81 |
gcs lms flush pi msgs | 0 | 0.00 | 0.00 |
gcs lms write request msgs | 189 | 0.04 | 0.03 |
gcs msgs process time(ms) | 16,164 | 3.42 | 2.89 |
gcs msgs received | 1,792,132 | 379.09 | 320.14 |
gcs out-of-order msgs | 0 | 0.00 | 0.00 |
gcs pings refused | 0 | 0.00 | 0.00 |
gcs pkey conflicts retry | 0 | 0.00 | 0.00 |
gcs queued converts | 2 | 0.00 | 0.00 |
gcs recovery claim msgs | 0 | 0.00 | 0.00 |
gcs refuse xid | 0 | 0.00 | 0.00 |
gcs regular cr | 0 | 0.00 | 0.00 |
gcs retry convert request | 0 | 0.00 | 0.00 |
gcs side channel msgs actual | 437 | 0.09 | 0.08 |
gcs side channel msgs logical | 21,086 | 4.46 | 3.77 |
gcs stale cr | 3,300 | 0.70 | 0.59 |
gcs undo cr | 5 | 0.00 | 0.00 |
gcs write notification msgs | 23 | 0.00 | 0.00 |
gcs writes refused | 3 | 0.00 | 0.00 |
ges msgs process time(ms) | 1,289 | 0.27 | 0.23 |
ges msgs received | 138,891 | 29.38 | 24.81 |
global posts dropped | 0 | 0.00 | 0.00 |
global posts queue time | 0 | 0.00 | 0.00 |
global posts queued | 0 | 0.00 | 0.00 |
global posts requested | 0 | 0.00 | 0.00 |
global posts sent | 0 | 0.00 | 0.00 |
implicit batch messages received | 81,181 | 17.17 | 14.50 |
implicit batch messages sent | 19,561 | 4.14 | 3.49 |
lmd msg send time(ms) | 0 | 0.00 | 0.00 |
lms(s) msg send time(ms) | 0 | 0.00 | 0.00 |
messages flow controlled | 15,306 | 3.24 | 2.73 |
messages queue sent actual | 108,411 | 22.93 | 19.37 |
messages queue sent logical | 222,518 | 47.07 | 39.75 |
messages received actual | 474,202 | 100.31 | 84.71 |
messages received logical | 1,931,144 | 408.50 | 344.97 |
messages sent directly | 25,742 | 5.45 | 4.60 |
messages sent indirectly | 137,725 | 29.13 | 24.60 |
messages sent not implicit batched | 88,859 | 18.80 | 15.87 |
messages sent pbatched | 1,050,224 | 222.16 | 187.61 |
msgs causing lmd to send msgs | 61,682 | 13.05 | 11.02 |
msgs causing lms(s) to send msgs | 85,978 | 18.19 | 15.36 |
msgs received queue time (ms) | 911,013 | 192.71 | 162.74 |
msgs received queued | 1,931,121 | 408.50 | 344.97 |
msgs sent queue time (ms) | 5,651 | 1.20 | 1.01 |
msgs sent queue time on ksxp (ms) | 66,767 | 14.12 | 11.93 |
msgs sent queued | 215,124 | 45.51 | 38.43 |
msgs sent queued on ksxp | 243,729 | 51.56 | 43.54 |
process batch messages received | 120,003 | 25.38 | 21.44 |
process batch messages sent | 181,019 | 38.29 | 32.34 |
Back to Top
Statistic | Total |
CR Block Requests | 10,422 |
CURRENT Block Requests | 251 |
Data Block Requests | 10,422 |
Undo Block Requests | 2 |
TX Block Requests | 20 |
Current Results | 10,664 |
Private results | 4 |
Zero Results | 5 |
Disk Read Results | 0 |
Fail Results | 0 |
Fairness Down Converts | 1,474 |
Fairness Clears | 0 |
Free GC Elements | 0 |
Flushes | 370 |
Flushes Queued | 0 |
Flush Queue Full | 0 |
Flush Max Time (us) | 0 |
Light Works | 2 |
Errors | 0 |
Back to Top
Pins = CURRENT Block Pin Operations
Flushes = Redo Flush before CURRENT Block Served Operations
Writes = CURRENT Block Fusion Write Operations
Statistic | Total | % <1ms | % <10ms | % <100ms | % <1s | % <10s |
Pins | 17,534 | 99.96 | 0.01 | 0.03 | 0.00 | 0.00 |
Flushes | 77 | 48.05 | 46.75 | 5.19 | 0.00 | 0.00 |
Writes | 255 | 5.49 | 53.73 | 40.00 | 0.78 | 0.00 |
Back to Top
Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays
Busy (Busy) - Block Transfer impacted by Remote Contention
Congested (Congst) - Block Transfer impacted by Remote System Load
ordered by CR + Current Blocks Received desc
|
| CR | Current | ||||||
Inst No | Block Class | Blocks Received | % Immed | % Busy | % Congst | Blocks Received | % Immed | % Busy | % Congst |
2 | data block | 3,945 | 87.20 | 12.80 | 0.00 | 13,324 | 99.71 | 0.26 | 0.04 |
2 | Others | 191 | 100.00 | 0.00 | 0.00 | 2,190 | 96.48 | 3.52 | 0.00 |
2 | undo header | 11 | 100.00 | 0.00 | 0.00 | 2 | 100.00 | 0.00 | 0.00 |
Back to Top
End of Report
[FF1]OLAP:联机分析处理OLTP:联机事务处理
OLAP是主要应用数据仓库系统
OLTP是一般的项目开发用到的基本的、日常的事务处理；比如数据库记录的增、删、改、查。
到此，关于“怎么理解ORACLE AWR报告”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！
--结束END--
本文标题: 怎么理解ORACLE AWR报告
本文链接: https://www.lsjlt.com/news/65250.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0