* 定义:awr报告是oracle 10g下提供的一种性能收集和分析工具,它能提供一个时间段内整个系统资源使用情况的报告,通过这个报告,我们就可以了解一个系统的整个运行情况,这就像一个人全面的体检报告。
如何分析:
* 在看awr报告的时候,我们并不需要知道所有性能指标的含义,就可以判断出问题的所在,这些性能指标其实代表了oracle内部实现,对oracle理解的越深,在看awr报告的时候,对数据库性能的判断也会越准确
* 在看性能指标的时候,心里先要明白,数据库出现性能问题,一般都在三个地方,io,内存,cpu,这三个又是息息相关的(ps:我们先假设这个三个地方都没有物理上的故障),当io负载增大时,肯定需要更多的内存来存放,同时也需要cpu花费更多的时间来过滤这些数据,相反,cpu时间花费多的话,有可能是解析sql语句,也可能是过滤太多的数据,到不一定是和io或内存有关系了
* 当我们把一条sql送到数据库去执行的时候,我们要知道,什么时候用到cpu,什么时候用到内存,什么时候用到io
1. cpu:解析sql语句,尝试多个执行计划,最后生成一个数据库认为是比较好的执行计划,不一定是最优的,因为关联表太多的时候,数据库并不会穷举所有的执行计划,这会消耗太多的时间,oracle怎么就知道这条数据时你要,另一个就不是你要的呢,这是需要cpu来过滤的
2. 内存:sql语句和执行计划都需要在内存保留一段时间,还有取到的数据,根据lru算法也会尽量在内存中保留,在执行sql语句过程中,各种表之间的连接,排序等操作也要占用内存
3. io:如果需要的数据在内存中没有,则需要到磁盘中去取,就会用到物理io了,还有表之间的连接数据太多,以及排序等操作内存放不下的时候,也需要用到临时表空间,也就用到物理io了
这里有一点说明的是,虽然oracle占用了8G的内存,但pga一般只占8G的20%,对于专用服务器模式,每次执行sql语句,表数据的运算等操作,都在pga中进行的,也就是说只能用1.6G左右的内存,如果多个用户都执行
多表关联,而且表数据又多,再加上关联不当的话,内存就成为瓶颈了,所有优化sql很重要的一点就是,减少逻辑读和物理读
如何生成awr报告:
* 1:登陆对应的数据库服务器
2:找到oracle磁盘空间(d:oracle\product\10.2.0\db_1\RDBMS\Admin)
3:执行cmd-cd d:回车
4: cd d:oracle\product\10.2.0\db_1\RDBMS\Admin 回车
5:sqlplus 用户名/密码@服务连接名(例:sqlplus carmot_esz_1/carmot@igrp)
6:执行@awrrpt.sql 回车
第一步输入类型: html
第二步输入天数: 天数自定义(如1,代表当天,如果2,代表今天和昨天。。。)
第三步输入开始值与结束值:(你可以看到上面列出的数据,snap值)
这个值输入开始,与结束
第四步输入导出表的名称:名称自定义 回车
第五步,由程序自动导完。
第六:到d:oracle\product\10.2.0\db_1\RDBMS\Admin 目录下。找到刚才生成的文件。 XXXX.LST文件
具体分析过程:
* 在分析awr报告之前,首先要确定我们的系统是属于oltp,还是olap(数据库在安装的时候,选择的时候,会有一个选项,是选择oltp,还是olap)
对于不同的系统,性能指标的侧重点是不一样的,比如,library hit和buffer hit,在olap系统中几乎可以忽略这俩个性能指标,而在oltp系统中,这俩个指标就非常关键了
* 首先要看俩个时间
Elapsed: 240.00 (mins) 表明采样时间是240分钟,任何数据都要通过这个时间来衡量,离开了这个采样时间,任何数据都毫无疑义
DB Time: 92,537.95 (mins) 表明用户操作花费的时候,包括cpu时间喝等待时间,也许有人会觉得奇怪,为什么在采样的240分钟过程中,用户操作时间竟然有92537分钟呢,远远超过了
采样时间,原因是awr报告是一个数据的集合,比如在一分钟之内,一个用户等待了30秒,那么10个用户就等待了300秒,对于cpu的话,一个cpu处理了30秒,16个cpu就是4800秒,这些时间都是以累积的方式记录在awr报告中的。
再看sessions,可以看出连接数非常多
* 为了对数据库有个整体的认识,先看下面的性能指标
1. Buffer Nowait 说明 在从内存取数据的时候,没有经历等待的比例,期望值是100%
2. Buffer Hit 说明从内存取数据的时候,buffer的命中率的比例,期望值是100%,但100%并不代表性能就好,因为这只是一个比例而已,举个例子,执行一条 sql语句,# 执行计划是需要取10000个数据块,结果内存中还真有这10000个数据块,那么比例是100%,表面上看是性能最高的,还有一个执行计划是需要500 个数据块,内存中有250个,另外250个需要在物理磁盘中取,
这种情况下,buffer hit是50%,结果呢,第二个执行计划性能才是最高的,所以说100%并不代表性能最好
3. Library Hit 说明sql在Shared Pool的命中率,期望值是100%
4. Execute to Parse 说明解析sql和执行sql之间的比例,越高越好,说明一次解析,到处执行,如果parse多,execute少的话,还会出现负数,因为计算公式是100*(1-parse/execute)
5. Parse CPU to Parse Elapsd 说明在解析sql语句过程中,cpu占整个的解析时间比例,,期望值是100%,说明没有产生等待,需要说明的是,即使有硬解析,只要cpu没有出现性能问题,也是可以容忍的,比较硬解析也有它的好处的
6. Redo NoWait 说明在产生日志的时候,没有产生等待,期望值是100%
7. Soft Parse 说明软解析的比例,期望值是100%,有一点要说明的是,不要单方面的追求软解析的高比例,而去绑定变量,要看性能的瓶颈在哪里
8. Latch Hit 说明latch的命中率,期望值是100%,latch类似锁,是一种内存锁,但只会产生等待,不会产生阻塞,和lock还是有区别的,latch是在并发的情况下产生的
9. Non-Parse CPU 说明非解析cpu的比例,越高越好,用100减去这个比例,可以看出解析sql所花费的cpu,100-99.30=0.7,说明花费在解析sql上的cpu很少
* 结合Time Model Statistics
可以看出,在整个sql执行时间(sql execute elapsed time)时间为5552019秒中,解析时间(parse time elapsed)用了36秒,硬解析时间(hard parse elapsed time)用了34秒虽然硬解析时间占了整个解析时间的绝大部分,但解析时间是花的很少的,所以可以判断出,sql的解析没有成为性能的瓶 颈,进一步推测,sql在获取数据的过程中遇到了瓶 颈
* 继续看Top 5 Timed Events,从这里可以看出等待时间在前五位的是什么事件,基本上就可以判断出性能瓶颈在什么地方
1. buffer busy waits 说明在获取数据的过程中,频繁的产生等待事件,很有可能产生了热点块,也就是说,很多会话都去读取同样的数据块,这一事件等待了5627394次,总共等待了5322924秒,平均等待时间为946毫秒,而且频率也是最高的,有95.9%,等待类别是并发
这里有一个概念:oracle操作的最小单位是块,当一个会话要修改这个块中的一条记录,会读取整个块,如果另一个会话要修改的数据也正好在这个块中,虽然这俩个
2. 会话修改的记录不一样,也会产生等待direct path write temp和direct path read temp 说明用到了临时表空间,那我们再看一下Tablespace IO Stats
各项指标都是非常高的,再根据上面的In-memory Sort是100%,没有产生磁盘排序,也就在排序的时候没有用到临时表空间,进一步推测,多个session,每个session执行的sql语句中多表关联,产生了很多中间数据,pga内存中放不下,
用到了临时表空间,也有可能是用到了lob字段,在用lob字段的时候,也会用到临时表
* 继续看SQL Statistics
根据buffer busy waits等待次数,时间,频率都是最高的,我们重点看逻辑读,物理读,和执行时间最长的sql,把排在前几位的拿出来优化
优化的原则为降低物理读,逻辑读,sql语句中的子操作执行次数尽量少,在看oracle估计出来的执行计划是看不出子操作的执行次数的,要看运行时的执行计划
* 有兴趣的话还可以看一下Segment Statistics
列出了用到的索引和表的使用情况,从这里也能看出索引和表的使用频率
* 也可以看一下Load Profile
里面列出了每秒,每个事务所产生的日志,逻辑读和物理读等指标
AWR的数据主要有两部分组成:
1)保存在内存中的系统负载和性能统计数据,主要通过v$视图查询 ;
2)mmon进程定期以快照(snapshot)的方式将内存中的AWR数据保存到SYSAUX表空间中,主要通过DBA_*视图访问。
1. AWR快照的生成
默认情况下,每隔一小时自动产生一个快照,保存最近7天的信息,可以通过以下语句查询:
SQL>select SNAP_INTERVAL,RETENTION from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
----------------------------------------------------------
+00000 01:00:00.0 +00007 00:00:00.0
可以通过以下语句修改时间间隔和保存时间(以分钟为单位):
exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention = > 10*24*60);
也可以根据需要随时手动生成快照:
exec dbms_workload_repository.create_snapshot;
2. AWR报告的生成
以sysdba运行如下命令:
@?/rdbms/admin/awrrpt.sql
3. AWR报告的分析
策略
因为AWR报告非常长,不可能从头到尾一字不漏的去看,要有选择的去看重点部分。最好能对照的来读,即和系统正常情况下的AWR报告对比,找差异。
AWR报告采用总分的形式,前面是系统的整体情况,后面是各个部分细节,一开始不要陷入细节,先分析系统的整体状况,对于后面的专题分析,要根据关注点的不同,采取跳跃式分析。
还要根据具体业务的不同,决定某种现象是否正常。
系统整体状况方面
1)Load Profile:分析系
了解系统整体负载状况,如每秒中的事务数/语句数,每秒/每事务物理读写次数(Physical Reads/Writes), 逻辑读写次数(Logical Reads/Writes),SQL语句的解析(Parse),特别是硬解析次数等。
2)Instance Efficiency Percentages:各指标都应接近100%,除了:execute to parse (70%以上)和parse cpu to parse elapsed。如果不符合,基本可以确定系统存在性能问题;但是如果反过来,即都符合,也不能说明系统完全正常,还要看实际情况。
具体状况方面
1)Top 5 Timed Events:这里列出消耗时间最多的5个等待事件,每种等待说明,都表示一种原因,如:db file sequential read表示按索引访问出现等待,db file scattered reade表示全表扫描访问出现等待事件。
2)Top N SQL:根据时间消耗,内存消耗,物理I/O等排序,对相关SQL分析执行计划
3)如果是RAC环境,需要特别关注RAC Statistic中的相关指标
4)SGA PGA分析
5)分析表空间、数据文件I/O
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时间,说明数据库比较空闲。
在79分钟里(其间收集了3次快照数据),数据库耗时11分钟,RDA数据中显示系统有8个逻辑CPU(4个物理CPU),平均每个CPU耗时1.4分钟,CPU利用率只有大约2%(1.4/79)。说明系统压力非常小。
可是对于批量系统,数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内,或者快照周期跨度太长而包含了大量的数据库空闲时间,所得出的分析结果是没有意义的。这也说明选择分析时间段很关键,要选择能够代表性能问题的时间段。
Cache Sizes
| 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等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。
Load Profile
| 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:每秒/每事务产生的redo大小(单位字节),可标志数据库任务的繁重程序。
Logical reads:每秒/每事务逻辑读的块数
Block changes:每秒/每事务修改的块数
Physical reads:每秒/每事务物理读的块数
Physical writes:每秒/每事务物理写的块数
User calls:每秒/每事务用户call次数
Parses:SQL解析的次数
Hard parses:其中硬解析的次数,硬解析太多,说明SQL重用率不高。
Sorts:每秒/每事务的排序次数
Logons:每秒/每事务登录的次数
Executes:每秒/每事务SQL执行次数
Transactions:每秒事务数
Blocks changed per Read:表示逻辑读用于修改数据块的比例
Recursive Call:递归调用占所有操作的比率
Rollback per transaction:每事务的回滚率
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的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。
Instance Efficiency Percentages (Target 100%)
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的经验,对于OLTPT系统,Buffer Hit Ratio理想应该在90%以上。
Buffer Nowait表示在内存获得数据的未等待比例。
buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG 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区。
Latch Hit:Latch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL,或者Library Cache太小,可使用绑定变更或调大Shared Pool解决。
Parse CPU to Parse Elapsd:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。
Non-Parse CPU :SQL实际运行时间/(SQL实际运行时间+SQL解析时间),太低表示解析消耗时间过多。
Execute to Parse:是语句执行与分析的比例,如果要SQL重用率高,则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
In-memory Sort:在内存中排序的比率,如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。
Soft Parse:软解析的百分比(softs/softs+hards),近似当作sql在共享区的命中率,太低则需要调整应用使用绑定变量。
Shared Pool Statistics
| 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,说明共享池中有争用,内存不足。
SQL with executions>1:执行次数大于1的sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。
Memory for SQL w/exec>1:执行次数大于1的SQL消耗内存的占比。
Top 5 Timed Events
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产生的问题。
在这里,log file parallel write是相对比较多的等待,占用了7%的CPU时间。
通常,在没有问题的数据库中,CPU time总是列在第一个。
更多的等待事件,参见本报告 的Wait Events一节。
| Begin | End |
Number of Instances: | 2 | 2 |
Global Cache Load Profile
| 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 |
|
Global Cache Efficiency Percentages (Target local+remote 100%)
Buffer access - local cache %: | 98.60 |
Buffer access - remote cache %: | 0.12 |
Buffer access - disk %: | 1.28 |
Global Cache and Enqueue Services - Workload Characteristics
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 |
Global Cache and Enqueue Services - Messaging Statistics
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
Wait Events Statistics
- Time Model Statistics
- Wait Class
- Wait Events
- Background Wait Events
- Operating System Statistics
- Service Statistics
- Service Wait Class Stats
Time Model Statistics
- Total time in database user-calls (DB Time): 663s
- 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 |
DB CPU | 514.50 | 77.61 |
sql execute elapsed time | 482.27 | 72.74 |
parse time elapsed | 3.76 | 0.57 |
PL/SQL execution elapsed time | 0.50 | 0.08 |
hard parse elapsed time | 0.34 | 0.05 |
connection management call elapsed time | 0.08 | 0.01 |
hard parse (sharing criteria) elapsed time | 0.00 | 0.00 |
repeated bind elapsed time | 0.00 | 0.00 |
PL/SQL compilation elapsed time | 0.00 | 0.00 |
failed parse elapsed time | 0.00 | 0.00 |
DB time | 662.97 |
|
background elapsed time | 185.19 |
|
background cpu time | 67.48 |
|
此节显示了各种类型的数据库处理任务所占用的CPU时间。
Back to Wait Events Statistics
Back to Top
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 |
User I/O | 66,837 | 0.00 | 120 | 2 | 11.94 |
System I/O | 28,295 | 0.00 | 93 | 3 | 5.05 |
Network | 1,571,450 | 0.00 | 66 | 0 | 280.72 |
Cluster | 210,548 | 0.00 | 29 | 0 | 37.61 |
Other | 81,783 | 71.82 | 28 | 0 | 14.61 |
Application | 333,155 | 0.00 | 16 | 0 | 59.51 |
Concurrency | 5,182 | 0.04 | 5 | 1 | 0.93 |
Commit | 919 | 0.00 | 4 | 4 | 0.16 |
Configuration | 25,427 | 99.46 | 1 | 0 | 4.54 |
Back to Wait Events Statistics
Back to Top
Wait Events
- 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 (idle events last)
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn |
SQL*Net more data from client | 27,319 | 0.00 | 64 | 2 | 4.88 |
log file parallel write | 5,497 | 0.00 | 47 | 9 | 0.98 |
db file sequential read | 7,900 | 0.00 | 35 | 4 | 1.41 |
db file parallel write | 4,806 | 0.00 | 34 | 7 | 0.86 |
db file scattered read | 10,310 | 0.00 | 31 | 3 | 1.84 |
direct path write | 42,724 | 0.00 | 30 | 1 | 7.63 |
reliable message | 355 | 2.82 | 18 | 49 | 0.06 |
SQL*Net break/reset to client | 333,084 | 0.00 | 16 | 0 | 59.50 |
db file parallel read | 3,732 | 0.00 | 13 | 4 | 0.67 |
gc current multi block request | 175,710 | 0.00 | 10 | 0 | 31.39 |
control file sequential read | 15,974 | 0.00 | 10 | 1 | 2.85 |
direct path read temp | 1,873 | 0.00 | 9 | 5 | 0.33 |
gc cr multi block request | 20,877 | 0.00 | 8 | 0 | 3.73 |
log file sync | 919 | 0.00 | 4 | 4 | 0.16 |
gc cr block busy | 526 | 0.00 | 3 | 6 | 0.09 |
enq: FB - contention | 10,384 | 0.00 | 3 | 0 | 1.85 |
DFS lock handle | 3,517 | 0.00 | 3 | 1 | 0.63 |
control file parallel write | 1,946 | 0.00 | 3 | 1 | 0.35 |
gc current block 2-way | 4,165 | 0.00 | 2 | 0 | 0.74 |
library cache lock | 432 | 0.00 | 2 | 4 | 0.08 |
name-service call wait | 22 | 0.00 | 2 | 76 | 0.00 |
row cache lock | 3,894 | 0.00 | 2 | 0 | 0.70 |
gcs log flush sync | 1,259 | 42.02 | 2 | 1 | 0.22 |
os thread startup | 18 | 5.56 | 2 | 89 | 0.00 |
gc cr block 2-way | 3,671 | 0.00 | 2 | 0 | 0.66 |
gc current block busy | 113 | 0.00 | 1 | 12 | 0.02 |
SQL*Net message to client | 1,544,115 | 0.00 | 1 | 0 | 275.83 |
gc buffer busy | 15 | 6.67 | 1 | 70 | 0.00 |
gc cr disk read | 3,272 | 0.00 | 1 | 0 | 0.58 |
direct path write temp | 159 | 0.00 | 1 | 5 | 0.03 |
gc current grant busy | 898 | 0.00 | 1 | 1 | 0.16 |
log file switch completion | 29 | 0.00 | 1 | 17 | 0.01 |
CGS wait for IPC msg | 48,739 | 99.87 | 0 | 0 | 8.71 |
gc current grant 2-way | 1,142 | 0.00 | 0 | 0 | 0.20 |
kjbdrmcvtq lmon drm quiesce: ping completion | 9 | 0.00 | 0 | 19 | 0.00 |
enq: US - contention | 567 | 0.00 | 0 | 0 | 0.10 |
direct path read | 138 | 0.00 | 0 | 1 | 0.02 |
enq: WF - contention | 14 | 0.00 | 0 | 9 | 0.00 |
ksxr poll remote instances | 13,291 | 58.45 | 0 | 0 | 2.37 |
library cache pin | 211 | 0.00 | 0 | 1 | 0.04 |
ges global resource directory to be frozen | 9 | 100.00 | 0 | 10 | 0.00 |
wait for scn ack | 583 | 0.00 | 0 | 0 | 0.10 |
log file sequential read | 36 | 0.00 | 0 | 2 | 0.01 |
undo segment extension | 25,342 | 99.79 | 0 | 0 | 4.53 |
rdbms ipc reply | 279 | 0.00 | 0 | 0 | 0.05 |
ktfbtgex | 6 | 100.00 | 0 | 10 | 0.00 |
enq: HW - contention | 44 | 0.00 | 0 | 1 | 0.01 |
gc cr grant 2-way | 158 | 0.00 | 0 | 0 | 0.03 |
enq: TX - index contention | 1 | 0.00 | 0 | 34 | 0.00 |
enq: CF - contention | 64 | 0.00 | 0 | 1 | 0.01 |
PX Deq: Signal ACK | 37 | 21.62 | 0 | 1 | 0.01 |
latch free | 3 | 0.00 | 0 | 10 | 0.00 |
buffer busy waits | 625 | 0.16 | 0 | 0 | 0.11 |
KJC: Wait for msg sends to complete | 154 | 0.00 | 0 | 0 | 0.03 |
log buffer space | 11 | 0.00 | 0 | 2 | 0.00 |
enq: PS - contention | 46 | 0.00 | 0 | 1 | 0.01 |
enq: TM - contention | 70 | 0.00 | 0 | 0 | 0.01 |
IPC send completion sync | 40 | 100.00 | 0 | 0 | 0.01 |
PX Deq: reap credit | 1,544 | 99.81 | 0 | 0 | 0.28 |
log file single write | 36 | 0.00 | 0 | 0 | 0.01 |
enq: TT - contention | 46 | 0.00 | 0 | 0 | 0.01 |
enq: TD - KTF dump entries | 12 | 0.00 | 0 | 1 | 0.00 |
read by other session | 1 | 0.00 | 0 | 12 | 0.00 |
LGWR wait for redo copy | 540 | 0.00 | 0 | 0 | 0.10 |
PX Deq Credit: send blkd | 17 | 5.88 | 0 | 0 | 0.00 |
enq: TA - contention | 14 | 0.00 | 0 | 0 | 0.00 |
latch: ges resource hash list | 44 | 0.00 | 0 | 0 | 0.01 |
enq: PI - contention | 8 | 0.00 | 0 | 0 | 0.00 |
write complete waits | 1 | 0.00 | 0 | 2 | 0.00 |
enq: DR - contention | 3 | 0.00 | 0 | 0 | 0.00 |
enq: MW - contention | 3 | 0.00 | 0 | 0 | 0.00 |
enq: TS - contention | 3 | 0.00 | 0 | 0 | 0.00 |
PX qref latch | 150 | 100.00 | 0 | 0 | 0.03 |
enq: MD - contention | 2 | 0.00 | 0 | 0 | 0.00 |
latch: KCL gc element parent latch | 11 | 0.00 | 0 | 0 | 0.00 |
enq: JS - job run lock - synchronize | 1 | 0.00 | 0 | 1 | 0.00 |
SQL*Net more data to client | 16 | 0.00 | 0 | 0 | 0.00 |
latch: cache buffers lru chain | 1 | 0.00 | 0 | 0 | 0.00 |
enq: UL - contention | 1 | 0.00 | 0 | 0 | 0.00 |
gc current split | 1 | 0.00 | 0 | 0 | 0.00 |
enq: AF - task serialization | 1 | 0.00 | 0 | 0 | 0.00 |
latch: object queue header operation | 3 | 0.00 | 0 | 0 | 0.00 |
latch: cache buffers chains | 1 | 0.00 | 0 | 0 | 0.00 |
latch: enqueue hash chains | 2 | 0.00 | 0 | 0 | 0.00 |
SQL*Net message from client | 1,544,113 | 0.00 | 12,626 | 8 | 275.83 |
gcs remote message | 634,884 | 98.64 | 9,203 | 14 | 113.41 |
DIAG idle wait | 23,628 | 0.00 | 4,616 | 195 | 4.22 |
ges remote message | 149,591 | 93.45 | 4,612 | 31 | 26.72 |
Streams AQ: qmn slave idle wait | 167 | 0.00 | 4,611 | 27611 | 0.03 |
Streams AQ: qmn coordinator idle wait | 351 | 47.86 | 4,611 | 13137 | 0.06 |
Streams AQ: waiting for messages in the queue | 488 | 100.00 | 4,605 | 9436 | 0.09 |
virtual circuit status | 157 | 100.00 | 4,596 | 29272 | 0.03 |
PX Idle Wait | 1,072 | 97.11 | 2,581 | 2407 | 0.19 |
jobq slave wait | 145 | 97.93 | 420 | 2896 | 0.03 |
Streams AQ: waiting for time management or cleanup tasks | 1 | 100.00 | 270 | 269747 | 0.00 |
PX Deq: Parse Reply | 40 | 40.00 | 0 | 3 | 0.01 |
PX Deq: Execution Msg | 121 | 26.45 | 0 | 0 | 0.02 |
PX Deq: Join ACK | 38 | 42.11 | 0 | 1 | 0.01 |
PX Deq: Execute Reply | 34 | 32.35 | 0 | 0 | 0.01 |
PX Deq: Msg Fragment | 16 | 0.00 | 0 | 0 | 0.00 |
Streams AQ: RAC qmn coordinator idle wait | 351 | 100.00 | 0 | 0 | 0.06 |
class slave wait | 2 | 0.00 | 0 | 0 | 0.00 |
db file scattered read等待事件是当SESSION等待multi-block I/O时发生的,通过是由于full table scans或index fast full scans。发生过多读操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”节中识别(在其它版本的报告中,可能是别的名称)。如果在OLTP应用中,不应该有过多的全扫描操作,而应使用选择性好的索引操作。
DB file sequential read等待意味着发生顺序I/O读等待(通常是单块读取到连续的内存区域中),如果这个等待非常严重,应该使用上一段的方法确定执行读操作的热点SEGMENT,然后通过对大表进行分区以减少I/O量,或者优化执行计划(通过使用存储大纲或执行数据分析)以避免单块读操作引起的sequential read等待。通过在批量应用中,DB file sequential read是很影响性能的事件,总是应当设法避免。
Log File Parallel Write事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的,但LGWR需要等待最后的I/O写到磁盘上才能认为并行写的完成,因此等待时间依赖于OS完成所有请求的时间。如果这个等待比较严重,可以通过将LOG文件移到更快的磁盘上或者条带化磁盘(减少争用)而降低这个等待。
Buffer Busy Waits事件是在一个SESSION需要访问BUFFER CACHE中的一个数据库块而又不能访问时发生的。缓冲区“busy”的两个原因是:1)另一个SESSION正在将数据块读进BUFFER。2)另一个SESSION正在以排它模式占用着这块被请求的BUFFER。可以在“Segments by Buffer Busy Waits”一节中找出发生这种等待的SEGMENT,然后通过使用reverse-key indexes并对热表进行分区而减少这种等待事件。
Log File Sync事件,当用户SESSION执行事务操作(COMMIT或ROLLBACK等)后,会通知 LGWR进程将所需要的所有REDO信息从LOG BUFFER写到LOG文件,在用户SESSION等待LGWR返回安全写入磁盘的通知时发生此等待。减少此等待的方法写Log File Parallel Write事件的处理。
Enqueue Waits是串行访问本地资源的本锁,表明正在等待一个被其它SESSION(一个或多个)以排它模式锁住的资源。减少这种等待的方法依赖于生产等待的锁类型。导致Enqueue等待的主要锁类型有三种:TX(事务锁), TMD(ML锁)和ST(空间管理锁)。
Back to Wait Events Statistics
Back to Top
Background Wait Events
- ordered by wait time desc, waits desc (idle events last)
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn |
log file parallel write | 5,497 | 0.00 | 47 | 9 | 0.98 |
db file parallel write | 4,806 | 0.00 | 34 | 7 | 0.86 |
events in waitclass Other | 69,002 | 83.25 | 22 | 0 | 12.33 |
control file sequential read | 9,323 | 0.00 | 7 | 1 | 1.67 |
control file parallel write | 1,946 | 0.00 | 3 | 1 | 0.35 |
os thread startup | 18 | 5.56 | 2 | 89 | 0.00 |
direct path read | 138 | 0.00 | 0 | 1 | 0.02 |
db file sequential read | 21 | 0.00 | 0 | 5 | 0.00 |
direct path write | 138 | 0.00 | 0 | 0 | 0.02 |
log file sequential read | 36 | 0.00 | 0 | 2 | 0.01 |
gc cr block 2-way | 96 | 0.00 | 0 | 0 | 0.02 |
gc current block 2-way | 78 | 0.00 | 0 | 0 | 0.01 |
log buffer space | 11 | 0.00 | 0 | 2 | 0.00 |
row cache lock | 59 | 0.00 | 0 | 0 | 0.01 |
log file single write | 36 | 0.00 | 0 | 0 | 0.01 |
buffer busy waits | 151 | 0.66 | 0 | 0 | 0.03 |
gc current grant busy | 29 | 0.00 | 0 | 0 | 0.01 |
library cache lock | 4 | 0.00 | 0 | 1 | 0.00 |
enq: TM - contention | 10 | 0.00 | 0 | 0 | 0.00 |
gc current grant 2-way | 8 | 0.00 | 0 | 0 | 0.00 |
gc cr multi block request | 7 | 0.00 | 0 | 0 | 0.00 |
gc cr grant 2-way | 5 | 0.00 | 0 | 0 | 0.00 |
rdbms ipc message | 97,288 | 73.77 | 50,194 | 516 | 17.38 |
gcs remote message | 634,886 | 98.64 | 9,203 | 14 | 113.41 |
DIAG idle wait | 23,628 | 0.00 | 4,616 | 195 | 4.22 |
pmon timer | 1,621 | 100.00 | 4,615 | 2847 | 0.29 |
ges remote message | 149,591 | 93.45 | 4,612 | 31 | 26.72 |
Streams AQ: qmn slave idle wait | 167 | 0.00 | 4,611 | 27611 | 0.03 |
Streams AQ: qmn coordinator idle wait | 351 | 47.86 | 4,611 | 13137 | 0.06 |
smon timer | 277 | 6.50 | 4,531 | 16356 | 0.05 |
Streams AQ: waiting for time management or cleanup tasks | 1 | 100.00 | 270 | 269747 | 0.00 |
PX Deq: Parse Reply | 40 | 40.00 | 0 | 3 | 0.01 |
PX Deq: Join ACK | 38 | 42.11 | 0 | 1 | 0.01 |
PX Deq: Execute Reply | 34 | 32.35 | 0 | 0 | 0.01 |
Streams AQ: RAC qmn coordinator idle wait | 351 | 100.00 | 0 | 0 | 0.06 |
Back to Wait Events Statistics
Back to Top
Operating System Statistics
Statistic | Total |
NUM_LCPUS | 0 |
NUM_VCPUS | 0 |
AVG_BUSY_TIME | 101,442 |
AVG_IDLE_TIME | 371,241 |
AVG_IOWAIT_TIME | 5,460 |
AVG_SYS_TIME | 25,795 |
AVG_USER_TIME | 75,510 |
BUSY_TIME | 812,644 |
IDLE_TIME | 2,971,077 |
IOWAIT_TIME | 44,794 |
SYS_TIME | 207,429 |
USER_TIME | 605,215 |
LOAD | 0 |
OS_CPU_WAIT_TIME | 854,100 |
RSRC_MGR_CPU_WAIT_TIME | 0 |
PHYSICAL_MEMORY_BYTES | 8,589,934,592 |
NUM_CPUS | 8 |
NUM_CPU_CORES | 4 |
NUM_LCPUS: 如果显示0,是因为没有设置LPARS
NUM_VCPUS: 同上。
AVG_BUSY_TIME: BUSY_TIME / NUM_CPUS
AVG_IDLE_TIME: IDLE_TIME / NUM_CPUS
AVG_IOWAIT_TIME: IOWAIT_TIME / NUM_CPUS
AVG_SYS_TIME: SYS_TIME / NUM_CPUS
AVG_USER_TIME: USER_TIME / NUM_CPUSar o
BUSY_TIME: time equiv of %usr+%sys in sar output
IDLE_TIME: time equiv of %idle in sar
IOWAIT_TIME: time equiv of %wio in sar
SYS_TIME: time equiv of %sys in sar
USER_TIME: time equiv of %usr in sar
LOAD: 未知
OS_CPU_WAIT_TIME: supposedly time waiting on run queues
RSRC_MGR_CPU_WAIT_TIME: time waited coz of resource manager
PHYSICAL_MEMORY_BYTES: total memory in use supposedly
NUM_CPUS: number of CPUs reported by OS
NUM_CPU_CORES: number of CPU sockets on motherboard
总的elapsed time也可以用以公式计算:
BUSY_TIME + IDLE_TIME + IOWAIT TIME
或:SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME
(因为BUSY_TIME = SYS_TIME+USER_TIME)
Back to Wait Events Statistics
Back to Top
Service Statistics
- ordered by DB Time
Service Name | DB Time (s) | DB CPU (s) | Physical Reads | Logical Reads |
ICCI | 608.10 | 496.60 | 315,849 | 16,550,972 |
SYS$USERS | 54.70 | 17.80 | 6,539 | 58,929 |
ICCIXDB | 0.00 | 0.00 | 0 | 0 |
SYS$BACKGROUND | 0.00 | 0.00 | 282 | 38,990 |
Back to Wait Events Statistics
Back to Top
Service Wait Class Stats
- Wait Class info for services in the Service Statistics section.
- Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
- Time Waited (Wt Time) in centisecond (100th of a second)
Service Name | User I/O Total Wts | User I/O Wt Time | Concurcy Total Wts | Concurcy Wt Time | Admin Total Wts | Admin Wt Time | Network Total Wts | Network Wt Time |
ICCI | 59826 | 8640 | 4621 | 338 | 0 | 0 | 1564059 | 6552 |
SYS$USERS | 6567 | 3238 | 231 | 11 | 0 | 0 | 7323 | 3 |
SYS$BACKGROUND | 443 | 115 | 330 | 168 | 0 | 0 | 0 | 0 |
Back to Wait Events Statistics
Back to Top
SQL Statistics
- SQL ordered by Elapsed Time
- SQL ordered by CPU Time
- SQL ordered by Gets
- SQL ordered by Reads
- SQL ordered by Executions
- SQL ordered by Parse Calls
- SQL ordered by Sharable Memory
- SQL ordered by Version Count
- SQL ordered by Cluster Wait Time
- Complete List of SQL Text
本节按各种资源分别列出对资源消耗最严重的SQL语句,并显示它们所占统计期内全部资源的比例,这给出我们调优指南。例如在一个系统中,CPU资源是系统性能瓶颈所在,那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中,调优的目标应该是physical IOs最多的SQL语句。
在STATSPACK报告中,没有完整的SQL语句,可使用报告中的Hash Value通过下面语句从数据库中查到:
select sql_text
from stats$sqltext
where hash_value = &hash_value
order by piece;
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 |
93 | 57 | 1 | 93.50 | 14.10 | cuidmain@HPGICCI1 (TNS V1-V3) | insert into CUID select CUID_... | |
76 | 75 | 172,329 | 0.00 | 11.52 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
58 | 42 | 1 | 58.04 | 8.75 | cumimain@HPGICCI1 (TNS V1-V3) | insert into CUMI select CUSV_... | |
51 | 42 | 1 | 50.93 | 7.68 | cusmmain@HPGICCI1 (TNS V1-V3) | insert into CUSM select CUSM_... | |
38 | 36 | 166,069 | 0.00 | 5.67 |
| select c.name, u.name from co... | |
35 | 3 | 1 | 35.00 | 5.28 | SQL*Plus | SELECT F.TABLESPACE_NAME, TO_... | |
23 | 23 | 172,329 | 0.00 | 3.46 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into iccifnsact values... | |
15 | 11 | 5 | 2.98 | 2.25 |
| DECLARE job BINARY_INTEGER := ... | |
14 | 14 | 172,983 | 0.00 | 2.16 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_AD... | |
13 | 13 | 172,337 | 0.00 | 2.00 | load_oldnewact@HPGICCI1 (TNS V1-V3) | insert into OLDNEWACT values ... | |
13 | 13 | 166,051 | 0.00 | 1.89 | icci_migact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
10 | 4 | 1 | 9.70 | 1.46 | cuidmain@HPGICCI1 (TNS V1-V3) | select CUID_CUST_NO , CUID_ID_... | |
10 | 8 | 5 | 1.91 | 1.44 |
| INSERT INTO STATS$SGA_TARGET_A... | |
8 | 8 | 172,329 | 0.00 | 1.25 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCICCS set CCSMAXOVER... | |
8 | 8 | 172,983 | 0.00 | 1.16 | load_fnsact@HPGICCI1 (TNS V1-V3) | select * from ICCIPRODCODE wh... |
Back to SQL Statistics
Back to Top
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 |
75 | 76 | 172,329 | 0.00 | 11.52 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
57 | 93 | 1 | 57.31 | 14.10 | cuidmain@HPGICCI1 (TNS V1-V3) | insert into CUID select CUID_... | |
42 | 51 | 1 | 42.43 | 7.68 | cusmmain@HPGICCI1 (TNS V1-V3) | insert into CUSM select CUSM_... | |
42 | 58 | 1 | 42.01 | 8.75 | cumimain@HPGICCI1 (TNS V1-V3) | insert into CUMI select CUSV_... | |
36 | 38 | 166,069 | 0.00 | 5.67 |
| select c.name, u.name from co... | |
23 | 23 | 172,329 | 0.00 | 3.46 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into iccifnsact values... | |
14 | 14 | 172,983 | 0.00 | 2.16 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_AD... | |
13 | 13 | 172,337 | 0.00 | 2.00 | load_oldnewact@HPGICCI1 (TNS V1-V3) | insert into OLDNEWACT values ... | |
13 | 13 | 166,051 | 0.00 | 1.89 | icci_migact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
11 | 15 | 5 | 2.23 | 2.25 |
| DECLARE job BINARY_INTEGER := ... | |
8 | 8 | 172,329 | 0.00 | 1.25 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCICCS set CCSMAXOVER... | |
8 | 10 | 5 | 1.60 | 1.44 |
| INSERT INTO STATS$SGA_TARGET_A... | |
8 | 8 | 172,983 | 0.00 | 1.16 | load_fnsact@HPGICCI1 (TNS V1-V3) | select * from ICCIPRODCODE wh... | |
4 | 10 | 1 | 3.54 | 1.46 | cuidmain@HPGICCI1 (TNS V1-V3) | select CUID_CUST_NO , CUID_ID_... | |
3 | 35 | 1 | 3.13 | 5.28 | SQL*Plus | SELECT F.TABLESPACE_NAME, TO_... |
Back to SQL Statistics
Back to Top
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: 16,648,792
- Captured SQL account for 97.9% of Total
Buffer Gets | Executions | Gets per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
3,305,363 | 172,329 | 19.18 | 19.85 | 74.57 | 76.41 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
2,064,414 | 1 | 2,064,414.00 | 12.40 | 57.31 | 93.50 | cuidmain@HPGICCI1 (TNS V1-V3) | insert into CUID select CUID_... | |
1,826,869 | 166,069 | 11.00 | 10.97 | 35.84 | 37.60 |
| select c.name, u.name from co... | |
1,427,648 | 172,337 | 8.28 | 8.58 | 12.97 | 13.29 | load_oldnewact@HPGICCI1 (TNS V1-V3) | insert into OLDNEWACT values ... | |
1,278,667 | 172,329 | 7.42 | 7.68 | 22.85 | 22.94 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into iccifnsact values... | |
1,216,367 | 1 | 1,216,367.00 | 7.31 | 42.43 | 50.93 | cusmmain@HPGICCI1 (TNS V1-V3) | insert into CUSM select CUSM_... | |
1,107,305 | 1 | 1,107,305.00 | 6.65 | 42.01 | 58.04 | cumimain@HPGICCI1 (TNS V1-V3) | insert into CUMI select CUSV_... | |
898,868 | 172,983 | 5.20 | 5.40 | 14.28 | 14.34 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_AD... | |
711,450 | 166,051 | 4.28 | 4.27 | 12.52 | 12.55 | icci_migact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
692,996 | 172,329 | 4.02 | 4.16 | 8.31 | 8.31 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCICCS set CCSMAXOVER... | |
666,748 | 166,052 | 4.02 | 4.00 | 6.36 | 6.36 | icci_migact@HPGICCI1 (TNS V1-V3) | select NEWACTNO into :b0 from... | |
345,357 | 172,983 | 2.00 | 2.07 | 7.70 | 7.71 | load_fnsact@HPGICCI1 (TNS V1-V3) | select * from ICCIPRODCODE wh... | |
231,756 | 51,633 | 4.49 | 1.39 | 5.75 | 5.83 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCIRPYV values (... |
Back to SQL Statistics
Back to Top
SQL ordered by Reads
- Total Disk Reads: 322,678
- Captured SQL account for 66.1% of Total
Physical Reads | Executions | Reads per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
66,286 | 1 | 66,286.00 | 20.54 | 57.31 | 93.50 | cuidmain@HPGICCI1 (TNS V1-V3) | insert into CUID select CUID_... | |
50,646 | 1 | 50,646.00 | 15.70 | 3.54 | 9.70 | cuidmain@HPGICCI1 (TNS V1-V3) | select CUID_CUST_NO , CUID_ID_... | |
24,507 | 1 | 24,507.00 | 7.59 | 42.01 | 58.04 | cumimain@HPGICCI1 (TNS V1-V3) | insert into CUMI select CUSV_... | |
21,893 | 1 | 21,893.00 | 6.78 | 42.43 | 50.93 | cusmmain@HPGICCI1 (TNS V1-V3) | insert into CUSM select CUSM_... | |
19,761 | 1 | 19,761.00 | 6.12 | 2.14 | 6.04 | cumimain@HPGICCI1 (TNS V1-V3) | select CUSV_CUST_NO from CUMI... | |
19,554 | 1 | 19,554.00 | 6.06 | 1.27 | 3.83 | SQL*Plus | select count(*) from CUSVAA_T... | |
6,342 | 1 | 6,342.00 | 1.97 | 3.13 | 35.00 | SQL*Plus | SELECT F.TABLESPACE_NAME, TO_... | |
4,385 | 1 | 4,385.00 | 1.36 | 1.59 | 2.43 | cusmmain@HPGICCI1 (TNS V1-V3) | select CUSM_CUST_ACCT_NO from... | |
63 | 5 | 12.60 | 0.02 | 11.17 | 14.91 |
| DECLARE job BINARY_INTEGER := ... | |
35 | 1 | 35.00 | 0.01 | 0.08 | 0.67 | SQL*Plus | BEGIN dbms_workload_repository... |
Back to SQL Statistics
Back to Top
SQL ordered by Executions
- Total Executions: 1,675,112
- Captured SQL account for 99.8% of Total
Executions | Rows Processed | Rows per Exec | CPU per Exec (s) | Elap per Exec (s) | SQL Id | SQL Module | SQL Text |
172,983 | 172,329 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | select * from ICCIPRODCODE wh... | |
172,983 | 172,329 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_AD... | |
172,337 | 172,337 | 1.00 | 0.00 | 0.00 | load_oldnewact@HPGICCI1 (TNS V1-V3) | insert into OLDNEWACT values ... | |
172,329 | 172,329 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into iccifnsact values... | |
172,329 | 172,329 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCICCS set CCSMAXOVER... | |
172,329 | 6,286 | 0.04 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
166,069 | 166,069 | 1.00 | 0.00 | 0.00 |
| select c.name, u.name from co... | |
166,052 | 166,052 | 1.00 | 0.00 | 0.00 | icci_migact@HPGICCI1 (TNS V1-V3) | select NEWACTNO into :b0 from... | |
166,051 | 166,051 | 1.00 | 0.00 | 0.00 | icci_migact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
51,740 | 51,740 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | select count(*) into :b0 fro... | |
51,633 | 51,633 | 1.00 | 0.00 | 0.00 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCIRPYV values (... |
Back to SQL Statistics
Back to Top
SQL ordered by Parse Calls
- Total Parse Calls: 182,780
- Captured SQL account for 99.0% of Total
Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
166,069 | 166,069 | 90.86 |
| select c.name, u.name from co... | |
6,304 | 6,304 | 3.45 |
| select type#, blocks, extents,... | |
2,437 | 2,438 | 1.33 |
| select file# from file$ where ... | |
1,568 | 1,568 | 0.86 |
| update seg$ set type#=:4, bloc... | |
1,554 | 1,554 | 0.85 |
| update tsq$ set blocks=:3, max... | |
444 | 444 | 0.24 |
| select blocks, maxblocks, gran... | |
421 | 421 | 0.23 |
| lock table sys.mon_mods$ in ex... | |
421 | 421 | 0.23 |
| update sys.mon_mods$ set inser... | |
86 | 86 | 0.05 |
| INSERT INTO sys.wri$_adv_messa... | |
81 | 81 | 0.04 |
| SELECT sys.wri$_adv_seq_msggro... |
Back to SQL Statistics
Back to Top
SQL ordered by Sharable Memory
No data exists for this section of the report.
Back to SQL Statistics
Back to Top
SQL ordered by Version Count
No data exists for this section of the report.
Back to SQL Statistics
Back to Top
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 |
10.96 | 11.72 | 93.50 | 57.31 | 1 | cuidmain@HPGICCI1 (TNS V1-V3) | insert into CUID select CUID_... | |
4.21 | 7.25 | 58.04 | 42.01 | 1 | cumimain@HPGICCI1 (TNS V1-V3) | insert into CUMI select CUSV_... | |
3.62 | 7.12 | 50.93 | 42.43 | 1 | cusmmain@HPGICCI1 (TNS V1-V3) | insert into CUSM select CUSM_... | |
2.39 | 6.35 | 37.60 | 35.84 | 166,069 |
| select c.name, u.name from co... | |
2.38 | 3.12 | 76.41 | 74.57 | 172,329 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
1.64 | 16.91 | 9.70 | 3.54 | 1 | cuidmain@HPGICCI1 (TNS V1-V3) | select CUID_CUST_NO , CUID_ID_... | |
1.06 | 3.02 | 35.00 | 3.13 | 1 | SQL*Plus | SELECT F.TABLESPACE_NAME, TO_... | |
0.83 | 13.76 | 6.04 | 2.14 | 1 | cumimain@HPGICCI1 (TNS V1-V3) | select CUSV_CUST_NO from CUMI... | |
0.66 | 87.90 | 0.75 | 0.42 | 444 |
| select blocks, maxblocks, gran... | |
0.50 | 13.01 | 3.83 | 1.27 | 1 | SQL*Plus | select count(*) from CUSVAA_T... | |
0.50 | 51.75 | 0.96 | 0.79 | 1,554 |
| update tsq$ set blocks=:3, max... | |
0.33 | 91.11 | 0.36 | 0.33 | 187 |
| select obj#, type#, ctime, mti... | |
0.33 | 2.47 | 13.29 | 12.97 | 172,337 | load_oldnewact@HPGICCI1 (TNS V1-V3) | insert into OLDNEWACT values ... | |
0.29 | 1.26 | 22.94 | 22.85 | 172,329 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into iccifnsact values... | |
0.25 | 10.14 | 2.43 | 1.59 | 1 | cusmmain@HPGICCI1 (TNS V1-V3) | select CUSM_CUST_ACCT_NO from... | |
0.21 | 27.92 | 0.74 | 0.74 | 1,568 |
| update seg$ set type#=:4, bloc... | |
0.20 | 3.49 | 5.83 | 5.75 | 51,633 | load_fnsact@HPGICCI1 (TNS V1-V3) | insert into ICCIRPYV values (... | |
0.17 | 1.39 | 12.55 | 12.52 | 166,051 | icci_migact@HPGICCI1 (TNS V1-V3) | insert into ICCICCS values (:... | |
0.16 | 57.64 | 0.28 | 0.24 | 39 | cusvaamain@HPGICCI1 (TNS V1-V3) | BEGIN BEGIN IF (xdb.DBMS... | |
0.14 | 74.58 | 0.19 | 0.14 | 121 |
| select o.owner#, o.name, o.nam... | |
0.11 | 64.72 | 0.18 | 0.15 | 80 | cusvaamain@HPGICCI1 (TNS V1-V3) | SELECT /*+ ALL_ROWS */ COUNT(*... | |
0.11 | 94.54 | 0.12 | 0.01 | 17 |
| delete from con$ where owner#=... | |
0.11 | 80.26 | 0.14 | 0.14 | 327 |
| select intcol#, nvl(pos#, 0), ... | |
0.08 | 19.20 | 0.42 | 0.24 | 1 |
| begin prvt_hdm.auto_execute( :... | |
0.07 | 54.97 | 0.13 | 0.13 | 83 |
| select i.obj#, i.ts#, i.file#,... | |
0.06 | 5.22 | 1.13 | 0.72 | 77 |
| select obj#, type#, flags, ... | |
0.06 | 86.50 | 0.06 | 0.06 | 45 |
| select owner#, name from con$... | |
0.06 | 8.19 | 0.67 | 0.08 | 1 | SQL*Plus | BEGIN dbms_workload_repository... | |
0.04 | 75.69 | 0.06 | 0.06 | 87 |
| select pos#, intcol#, col#, sp... | |
0.04 | 48.05 | 0.09 | 0.07 | 7 |
| select file#, block# from seg... | |
0.04 | 8.84 | 0.40 | 0.40 | 6,304 |
| select type#, blocks, extents,... | |
0.03 | 28.15 | 0.12 | 0.12 | 49 |
| delete from RecycleBin$ ... | |
0.03 | 66.23 | 0.05 | 0.05 | 85 |
| select t.ts#, t.file#, t.block... | |
0.03 | 67.03 | 0.05 | 0.05 | 38 | DBMS_SCHEDULER | update obj$ set obj#=:6, type#... | |
0.02 | 66.73 | 0.04 | 0.04 | 86 |
| INSERT INTO sys.wri$_adv_messa... | |
0.02 | 26.94 | 0.09 | 0.09 | 38 |
| delete from RecycleBin$ ... | |
0.02 | 76.76 | 0.03 | 0.03 | 51 |
| select con# from con$ where ow... | |
0.02 | 51.91 | 0.05 | 0.05 | 84 |
| select name, intcol#, segcol#,... | |
0.02 | 0.15 | 14.91 | 11.17 | 5 |
| DECLARE job BINARY_INTEGER := ... | |
0.02 | 2.12 | 1.00 | 0.99 | 8,784 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_FA... | |
0.02 | 53.82 | 0.03 | 0.03 | 39 | cusvaamain@HPGICCI1 (TNS V1-V3) | SELECT count(*) FROM user_poli... | |
0.01 | 0.10 | 14.34 | 14.28 | 172,983 | load_fnsact@HPGICCI1 (TNS V1-V3) | update ICCIFNSACT set BORM_AD... | |
0.01 | 8.29 | 0.16 | 0.13 | 421 |
| update sys.mon_mods$ set inser... | |
0.01 | 1.65 | 0.56 | 0.54 | 2 |
| insert into wrh$_latch (snap... | |
0.01 | 22.33 | 0.04 | 0.02 | 26 | load_curmmast@HPGICCI1 (TNS V1-V3) | insert into ICCICURMMAST valu... | |
0.01 | 0.08 | 7.71 | 7.70 | 172,983 | load_fnsact@HPGICCI1 (TNS V1-V3) | select * from ICCIPRODCODE wh... |
Back to SQL Statistics
Back to Top
Complete List of SQL Text
SQL Id | SQL Text |
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 | |
select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn | |
delete from RecycleBin$ where purgeobj=:1 | |
select file#, block# from seg$ where type# = 3 and ts# = :1 | |
select CUID_CUST_NO , CUID_ID_TYPE , CUID_ID_RECNO from CUID_TMP where CHGFLAG='D' | |
select blocks, maxblocks, grantor#, priv1, priv2, priv3 from tsq$ where ts#=:1 and user#=:2 | |
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 | |
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) | |
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# (+) | |
BEGIN dbms_workload_repository.create_snapshot; END; | |
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 | |
lock table sys.mon_mods$ in exclusive mode nowait | |
update ICCICCS set CCSMAXOVERDUE=GREATEST(:b0, CCSMAXOVERDUE) where FNSACTNO=:b1 | |
select count(*) from CUSVAA_TMP | |
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) | |
insert into ICCICURMMAST values (:b0, :b1, :b2) | |
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) | |
insert into ICCICCS values (:b0, '', 0, 0, 0, 0, 0, ' ', 0, 0, 0, ' ', '0', null ) | |
update ICCIFNSACT set BORM_FACILITY_NO=:b0 where BORM_MEMB_CUST_AC=:b1 | |
select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1 | |
insert into CUMI select CUSV_CUST_NO , CUSV_EDUCATION_CODE , CHGDATE from CUMI_TMP where CHGFLAG<>'D' | |
select * from ICCIPRODCODE where PRODCODE=to_char(:b0) | |
select o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj#, o.flags from obj$ o where o.obj#=:1 | |
select pos#, intcol#, col#, spare1, bo#, spare2 from icol$ where obj#=:1 | |
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 | |
SELECT /*+ ALL_ROWS */ 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 | |
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# | |
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# | |
select NEWACTNO into :b0 from OLDNEWACT where OLDACTNO=:b1 | |
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 | |
select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 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# | |
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 | |
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 | |
select con# from con$ where owner#=:1 and name=:2 | |
select owner#, name from con$ where con#=:1 | |
select CUSV_CUST_NO from CUMI_TMP where CHGFLAG='D' | |
insert into CUSM select CUSM_CUST_ACCT_NO , CUSM_STAT_POST_ADD_NO , CHGDATE from CUSM_TMP where CHGFLAG<>'D' | |
update tsq$ set blocks=:3, maxblocks=:4, grantor#=:5, priv1=:6, priv2=:7, priv3=:8 where ts#=:1 and user#=:2 | |
delete from RecycleBin$ where bo=:1 | |
SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPF' | |
select file# from file$ where ts#=:1 | |
update obj$ set obj#=:6, type#=:7, ctime=:8, mtime=:9, stime=:10, status=:11, dataobj#=:13, flags=:14, oid$=:15, spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null) | |
select count(*) into :b0 from ICCIFNSACT where BORM_MEMB_CUST_AC=:b1 | |
delete from con$ where owner#=:1 and name=:2 | |
insert into ICCICCS values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13) | |
BEGIN BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; | |
select CUSM_CUST_ACCT_NO from CUSM_TMP where CHGFLAG='D' | |
insert into CUID select CUID_CUST_NO , CUID_ID_MAIN , CUID_ID_TYPE , CUID_ID_RECNO , CUID_ID_NUMBER , CHGDATE from CUID_TMP where CHGFLAG<>'D' | |
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; | |
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; | |
SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual | |
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn | |
insert into OLDNEWACT values (:b0, :b1) |
Back to SQL Statistics
Back to Top
Instance Activity Statistics
- Instance Activity Stats
- Instance Activity Stats - Absolute Values
- Instance Activity Stats - Thread Activity
Instance Activity Stats
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 - 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 |
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 |
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 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 |
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 |
table fetch continued row | 0 | 0.00 | 0.00 |
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 |
table scans (short tables) | 169,201 | 35.79 | 30.23 |
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
Instance Activity Stats - Absolute Values
- 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
Instance Activity Stats - Thread Activity
- 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
IO Stats
Tablespace IO Stats
- 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争用。
File IO Stats
- 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 |
Buffer Pool Statistics
- 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 |
Advisory Statistics
- 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
Instance Recovery Stats
- 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
Buffer Pool Advisory
- Only rows with estimated physical reads >0 are displayed
- ordered by Block Size, Buffers For Estimate
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 Aggr Summary
- 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
PGA Aggr Target Stats
- 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
PGA Aggr Target Histogram
- 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
PGA Memory Advisory
- 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
Shared Pool Advisory
- 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 Advisory
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
Streams Pool Advisory
No data exists for this section of the report.
Back to Advisory Statistics
Back to Top
Java Pool Advisory
No data exists for this section of the report.
Back to Advisory Statistics
Back to Top
Wait Statistics
Buffer Wait Statistics
- 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
Enqueue Activity
- 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 Statistics
Undo Segment Summary
- 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
Undo Segment Stats
- 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 Statistics
Latch Activity
- "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
Latch Sleep Breakdown
- 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
Latch Miss Sources
- 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
Parent Latch Statistics
No data exists for this section of the report.
Back to Latch Statistics
Back to Top
Child Latch Statistics
No data exists for this section of the report.
Back to Latch Statistics
Back to Top
Segment Statistics
- 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
Segments by Logical Reads
- 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
Segments by Physical Reads
- 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
Segments by Row Lock Waits
当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量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
Segments by Buffer Busy Waits
No data exists for this section of the report.
Back to Segment Statistics
Back to Top
Segments by Global Cache Buffer Busy
- % 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
Segments by CR Blocks Received
- 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
Segments by Current Blocks Received
- 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 Statistics
Dictionary Cache Stats
- "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
Dictionary Cache Stats (RAC)
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 Statistics
Library Cache Activity
- "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
Library Cache Activity (RAC)
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
Memory Statistics
Process Memory Summary
- 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 Memory Summary
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
SGA breakdown difference
- 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 Statistics
Streams CPU/IO Usage
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Streams Capture
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Streams Apply
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Buffered Queues
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Buffered Subscribers
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Rule Set
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Resource Limit Stats
- 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 |
init.ora Parameters
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 |
|
- Global Enqueue Statistics
- Global CR Served Stats
- Global CURRENT Served Stats
- Global Cache Transfer Stats
Global Enqueue Statistics
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 |
Global CR Served Stats
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 |
Global CURRENT Served Stats
- 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 |
Global Cache Transfer Stats
- 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 |