Oracle - AWR - 教你如何看 - 图文

/u01/ora10g/db_1/rdbms/admin 可直接执行1-2.1-3即可 1.查看当前的AWR保存策略

select * from dba_hist_wr_control; DBID,SNAP_INTERVAL,RETENTION,TOPNSQL

860524039,+00 01:00:00.000000,+07 00:00:00.000000,DEFAULT 以上结果表示,每小时产生一个SNAPSHOT，保留7天 2.调整AWR配置

AWR配置都是通过dbms_workload_repository包进行配置

2.1调整AWR产生snapshot的频率和保留策略，如：如将收集间隔时间改为30 分钟一次。并且保留5天时间（注：单位都是为分钟）：

exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

2.2关闭AWR,把interval设为0则关闭自动捕捉快照 2.3手工创建一个快照

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); 2.4 查看快照

select * from sys.wrh$_active_session_history 2.5手工删除指定范围的快照 exec WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 22, high_snap_id => 32, dbid => 3310949047); 2.6创建baseline

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1') 2.7删除baseline exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => ' apply_interest_1', cascade => FALSE);

3.生产AWR报告

$ORACLE_HOME/rdbms/admin/awrrpt.sql WORKLOAD REPOSITORY report for DB Name ICCI DB Id Instance Inst num Release 1 10.2.0.3.0 RAC YES Host HPGICCI1 1314098396 ICCI1 Begin Snap: End Snap: Elapsed: DB Time: Snap Id 2678 2680 Snap Time 25-Dec-08 14:04:50 25-Dec-08 15:23:37 78.79 (mins) 11.05 (mins) Sessions 24 26 Cursors/Session 1.5 1.5 DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。 在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPU），平均每个CPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。 可是对于批量系统，数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内，或者快照周期跨度太长而包含了大量的数据库空闲时间，所

得出的分析结果是没有意义的。这也说明选择分析时间段很关键，要选择能够代表性能问题的时间段。 Report Summary Cache Sizes

Buffer Cache: Shared Pool Size: Begin 3,344M 704M End 704M Log Buffer: 8K 14,352K 3,344M Std Block Size: 显示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

Redo size: Logical reads: Block changes: Per Second 918,805.72 3,521.77 1,817.95 Per Transaction 775,912.72 2,974.06 1,535.22 Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Logons: Executes: Transactions: % Blocks changed per Read: Rollback per transaction %: 68.26 362.59 326.69 38.66 0.03 0.61 0.01 354.34 1.18 51.62 Recursive Call %: 85.49 Rows per Sort: 57.64 306.20 275.88 32.65 0.03 0.51 0.01 299.23 51.72 ######## 显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，然而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 parse)和硬解析(hard parse)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程： 1、语法检查(syntax check) 检查此sql的拼写是否语法。 2、语义检查(semantic check)

诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。 3、对sql语句进行解析(parse)

利用内部算法对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 %: Buffer Hit %: Library Hit %: Execute to Parse %: Parse CPU to Parse Elapsd %: 100.00 Redo NoWait %: 98.72 In-memory Sort %: 99.97 Soft Parse %: 89.09 Latch Hit %: 7.99 % Non-Parse CPU: 100.00 99.86 99.92 99.99 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

Memory Usage %: % SQL with executions>1: % Memory for SQL w/exec>1: Begin 47.19 88.48 79.99 End 47.50 79.81 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 CPU time log file parallel write db file sequential read db file parallel write Waits Time(s) Avg Wait(ms) 5,497 7,900 4,806 515 64 47 35 34 2 9 4 7 % Total Call Time Wait Class 77.6 9.7 Network 7.1 System I/O 5.3 User I/O 5.1 System I/O SQL*Net more data from client 27,319 这是报告概要的最后一节，显示了系统中最严重的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一节。

RAC Statistics Number of Instances: Begin 2 End 2

Global Cache Load Profile

Global Cache blocks received: Global Cache blocks served: GCS/GES messages received: GCS/GES messages sent: DBWR Fusion writes: Estd Interconnect traffic (KB) Per Second 4.16 5.97 408.47 258.03 0.05 211.16 Per Transaction 3.51 5.04 344.95 217.90 0.05 Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %: Buffer access - remote cache %: Buffer access - disk %: 98.60 0.12 1.28 Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms): Avg global cache cr block receive time (ms): Avg global cache current block receive time (ms): Avg global cache cr block build time (ms): Avg global cache cr block send time (ms): Global cache log flushes for cr blocks served %: Avg global cache cr block flush time (ms): Avg global cache current block pin time (ms): Avg global cache current block send time (ms): Global cache log flushes for current blocks served %: Avg global cache current block flush time (ms): 0.1 1.1 0.8 0.0 0.0 3.5 3.9 0.0 0.0 0.4 3.0 Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): Avg message sent queue time on ksxp (ms): Avg message received queue time (ms): Avg GCS message process time (ms): Avg GES message process time (ms): % of direct sent messages: % of indirect sent messages: % of flow controlled messages: 0.0 0.3 0.5 0.0 0.0 14.40 77.04 8.56 Main Report ? ? 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 Back to Top

Time Model Statistics

? ? ?

Total time in database user-calls (DB Time): 663s

Statistics including the word \contribute to the DB time statistic

Ordered by % or DB time desc, Statistic name Statistic Name DB CPU sql execute elapsed time parse time elapsed PL/SQL execution elapsed time hard parse elapsed time connection management call elapsed time hard parse (sharing criteria) elapsed time repeated bind elapsed time PL/SQL compilation elapsed time failed parse elapsed time DB time background elapsed time background cpu time Time (s) % of DB Time 514.50 482.27 3.76 0.50 0.34 0.08 0.00 0.00 0.00 0.00 662.97 185.19 67.48 77.61 72.74 0.57 0.08 0.05 0.01 0.00 0.00 0.00 0.00 此节显示了各种类型的数据库处理任务所占用的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

Waits 66,837 28,295 1,571,450 210,548 81,783 333,155 5,182 919 25,427 %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn 0.00 0.00 0.00 0.00 71.82 0.00 0.04 0.00 99.46 120 93 66 29 28 16 5 4 1 2 3 0 0 0 0 1 4 0 11.94 5.05 280.72 37.61 14.61 59.51 0.93 0.16 4.54 Wait Class User I/O System I/O Network Cluster Other Application Concurrency Commit Configuration 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)

%Time -outs 0.00 0.00 0.00 0.00 0.00 0.00 2.82 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Total Wait Time (s) 64 47 35 34 31 30 18 16 13 10 10 9 8 4 3 3 3 3 2 2 Avg wait (ms) 2 9 4 7 3 1 49 0 4 0 1 5 0 4 6 0 1 1 0 4 Waits /txn 4.88 0.98 1.41 0.86 1.84 7.63 0.06 59.50 0.67 31.39 2.85 0.33 3.73 0.16 0.09 1.85 0.63 0.35 0.74 0.08 Event SQL*Net more data from client log file parallel write db file sequential read db file parallel write db file scattered read direct path write reliable message SQL*Net break/reset to client db file parallel read gc current multi block request control file sequential read direct path read temp gc cr multi block request log file sync gc cr block busy enq: FB - contention DFS lock handle control file parallel write gc current block 2-way library cache lock Waits 27,319 5,497 7,900 4,806 10,310 42,724 355 333,084 3,732 175,710 15,974 1,873 20,877 919 526 10,384 3,517 1,946 4,165 432 name-service call wait row cache lock gcs log flush sync os thread startup gc cr block 2-way gc current block busy SQL*Net message to client gc buffer busy gc cr disk read direct path write temp gc current grant busy log file switch completion CGS wait for IPC msg gc current grant 2-way kjbdrmcvtq lmon drm quiesce: ping completion enq: US - contention direct path read enq: WF - contention ksxr poll remote instances library cache pin ges global resource directory to be frozen wait for scn ack log file sequential read undo segment extension rdbms ipc reply ktfbtgex enq: HW - contention gc cr grant 2-way enq: TX - index contention enq: CF - contention PX Deq: Signal ACK latch free buffer busy waits KJC: Wait for msg sends to complete log buffer space enq: PS - contention enq: TM - contention IPC send completion sync PX Deq: reap credit log file single write enq: TT - contention enq: TD - KTF dump entries read by other session LGWR wait for redo copy PX Deq Credit: send blkd enq: TA - contention latch: ges resource hash list enq: PI - contention write complete waits enq: DR - contention 22 3,894 1,259 18 3,671 113 1,544,115 15 3,272 159 898 29 48,739 1,142 9 567 138 14 13,291 211 9 583 36 25,342 279 6 44 158 1 64 37 3 625 154 11 46 70 40 1,544 36 46 12 1 540 17 14 44 8 1 3 0.00 0.00 42.02 5.56 0.00 0.00 0.00 6.67 0.00 0.00 0.00 0.00 99.87 0.00 0.00 0.00 0.00 0.00 58.45 0.00 100.00 0.00 0.00 99.79 0.00 100.00 0.00 0.00 0.00 0.00 21.62 0.00 0.16 0.00 0.00 0.00 0.00 100.00 99.81 0.00 0.00 0.00 0.00 0.00 5.88 0.00 0.00 0.00 0.00 0.00 2 2 2 2 2 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 76 0 1 89 0 12 0 70 0 5 1 17 0 0 19 0 1 9 0 1 10 0 2 0 0 10 1 0 34 1 1 10 0 0 2 1 0 0 0 0 0 1 12 0 0 0 0 0 2 0 0.00 0.70 0.22 0.00 0.66 0.02 275.83 0.00 0.58 0.03 0.16 0.01 8.71 0.20 0.00 0.10 0.02 0.00 2.37 0.04 0.00 0.10 0.01 4.53 0.05 0.00 0.01 0.03 0.00 0.01 0.01 0.00 0.11 0.03 0.00 0.01 0.01 0.01 0.28 0.01 0.01 0.00 0.00 0.10 0.00 0.00 0.01 0.00 0.00 0.00 enq: MW - contention enq: TS - contention PX qref latch enq: MD - contention latch: KCL gc element parent latch enq: JS - job run lock - synchronize SQL*Net more data to client latch: cache buffers lru chain enq: UL - contention gc current split enq: AF - task serialization latch: object queue header operation latch: cache buffers chains latch: enqueue hash chains SQL*Net message from client gcs remote message DIAG idle wait ges remote message Streams AQ: qmn slave idle wait Streams AQ: qmn coordinator idle wait Streams AQ: waiting for messages in the queue virtual circuit status PX Idle Wait jobq slave wait Streams AQ: waiting for time management or cleanup tasks PX Deq: Parse Reply PX Deq: Execution Msg PX Deq: Join ACK PX Deq: Execute Reply PX Deq: Msg Fragment Streams AQ: RAC qmn coordinator idle wait class slave wait 3 3 150 2 11 1 16 1 1 1 1 3 1 2 1,544,113 634,884 23,628 149,591 167 351 488 157 1,072 145 1 40 121 38 34 16 351 2 0.00 0.00 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 98.64 0.00 93.45 0.00 47.86 100.00 100.00 97.11 97.93 100.00 40.00 26.45 42.11 32.35 0.00 100.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12,626 9,203 4,616 4,612 4,611 4,611 4,605 4,596 2,581 420 270 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 8 14 195 31 27611 13137 9436 29272 2407 2896 269747 3 0 1 0 0 0 0 0.00 0.00 0.03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 275.83 113.41 4.22 26.72 0.03 0.06 0.09 0.03 0.19 0.03 0.00 0.01 0.02 0.01 0.01 0.00 0.06 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（事务锁）, TM D（ML锁）和ST（空间管理锁）。

Back to Wait Events Statistics Back to Top

Background Wait Events

?

ordered by wait time desc, waits desc (idle events last)

%Time -outs 0.00 0.00 83.25 0.00 0.00 5.56 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.66 0.00 0.00 0.00 0.00 0.00 0.00 73.77 Total Wait Time (s) 47 34 22 7 3 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 50,194 Avg wait (ms) 9 7 0 1 1 89 1 5 0 2 0 0 2 0 0 0 0 1 0 0 0 0 516 Waits /txn 0.98 0.86 12.33 1.67 0.35 0.00 0.02 0.00 0.02 0.01 0.02 0.01 0.00 0.01 0.01 0.03 0.01 0.00 0.00 0.00 0.00 0.00 17.38 Event log file parallel write db file parallel write events in waitclass Other control file sequential read control file parallel write os thread startup direct path read db file sequential read direct path write log file sequential read gc cr block 2-way gc current block 2-way log buffer space row cache lock log file single write buffer busy waits gc current grant busy library cache lock enq: TM - contention gc current grant 2-way gc cr multi block request gc cr grant 2-way rdbms ipc message Waits 5,497 4,806 69,002 9,323 1,946 18 138 21 138 36 96 78 11 59 36 151 29 4 10 8 7 5 97,288 gcs remote message DIAG idle wait pmon timer ges remote message Streams AQ: qmn slave idle wait Streams AQ: qmn coordinator idle wait smon timer Streams AQ: waiting for time management or cleanup tasks PX Deq: Parse Reply PX Deq: Join ACK PX Deq: Execute Reply Streams AQ: RAC qmn coordinator idle wait Back to Wait Events Statistics Back to Top

634,886 23,628 1,621 149,591 167 351 277 1 40 38 34 351 98.64 0.00 100.00 93.45 0.00 47.86 6.50 100.00 40.00 42.11 32.35 100.00 9,203 4,616 4,615 4,612 4,611 4,611 4,531 270 0 0 0 0 14 195 2847 31 27611 13137 16356 269747 3 1 0 0 113.41 4.22 0.29 26.72 0.03 0.06 0.05 0.00 0.01 0.01 0.01 0.06 Operating System Statistics

Statistic NUM_LCPUS NUM_VCPUS AVG_BUSY_TIME AVG_IDLE_TIME AVG_IOWAIT_TIME AVG_SYS_TIME AVG_USER_TIME BUSY_TIME IDLE_TIME IOWAIT_TIME SYS_TIME USER_TIME LOAD OS_CPU_WAIT_TIME RSRC_MGR_CPU_WAIT_TIME NUM_CPUS NUM_CPU_CORES Total 0 0 101,442 371,241 5,460 25,795 75,510 812,644 2,971,077 44,794 207,429 605,215 0 854,100 0 8 4 PHYSICAL_MEMORY_BYTES 8,589,934,592 NUM_LCPUS： NUM_VCPUS： AVG_BUSY_TIME： AVG_IDLE_TIME： AVG_IOWAIT_TIME： AVG_SYS_TIME： AVG_USER_TIME： BUSY_TIME： IDLE_TIME： IOWAIT_TIME： SYS_TIME： 如果显示0，是因为没有设置LPARS

同上。

BUSY_TIME / NUM_CPUS IDLE_TIME / NUM_CPUS IOWAIT_TIME / NUM_CPUS SYS_TIME / NUM_CPUS

USER_TIME / NUM_CPUSar o

time equiv of %usr+%sys in sar output time equiv of %idle in sar time equiv of %wio in sar 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

DB Time (s) DB CPU (s) Physical Reads Logical Reads 608.10 54.70 0.00 0.00 496.60 17.80 0.00 0.00 315,849 6,539 0 282 16,550,972 58,929 0 38,990 Service Name ICCI SYS$USERS ICCIXDB SYS$BACKGROUND 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)

User I/O Total Wts 59826 6567 443 Service Name ICCI SYS$USERS SYS$BACKGROUND User I/O Wt Time 8640 3238 115 Concurcy Total Wts 4621 231 330 Concurcy Wt Time 338 11 168 Admin Total Wts 0 0 0 Admin Wt Time 0 0 0 Network Total Wts 1564059 7323 0 Network Wt Time 6552 3 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;

Back to Top

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

% Elap Totaper l DB ExeTimc (s) e Elapsed Time (s) 93 76 58 51 38 35 CPU ExecutionTims e (s) 57 75 42 42 36 3 SQL Id SQL Module SQL Text 1 93.50 14.10 d8z0u8hgj8xdy cuidmain@HPGICCI1 (TNS V1-V3) 172,329 0.00 11.52 4vja2k2gdtyup load_fnsact@HPGICCI1 (TNS V1-V3) 8.75 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 7.68 ackxqhnktxnbc cusmmain@HPGICCI1 (TNS V1-V3) 5.67 7gtztzv329wg0 5.28 6z06gcfw39pkd SQL*Plus insert into CUID select CUID_... insert into ICCICCS values (:... insert into CUMI select CUSV_... insert into CUSM select CUSM_... select c.name, u.name from co... SELECT F.TABLESPACE_NAME, TO_... insert into iccifnsact values... DECLARE job BINARY_INTEGER := ... update ICCIFNSACT set BORM_AD... insert into OLDNEWACT values ... insert into ICCICCS values (:... select CUID_CUST_NO , CUID_ID_... 1 58.04 1 50.93 166,069 0.00 1 35.00 23 15 14 13 13 10 23 11 14 13 13 4 172,329 5 172,983 172,337 166,051 1 0.00 2.98 0.00 0.00 0.00 9.70 3.46 1dm3bq36vu3gload_fnsact@HPGICCI1 8 (TNS V1-V3) 2.25 djs2w2f17nw2z 2.16 7wwv1ybs9zguload_fnsact@HPGICCI1 z (TNS V1-V3) 2.00 gmn2w09rdxn1load_oldnewact@HPGIC4 CI1 (TNS V1-V3) 1.89 chjmy0dxf9mbj icci_migact@HPGICCI1 (TNS V1-V3) 1.46 0yv9t4qb1zb2b cuidmain@HPGICCI1 (TNS V1-V3) 10 8 5 1.91 1.44 1crajpb7j5tyz INSERT INTO STATS$SGA_TARGET_A... update ICCICCS set CCSMAXOVER... select * from ICCIPRODCODE wh... 8 8 8 8 172,329 172,983 0.00 0.00 1.25 38apjgr0p55ns load_fnsact@HPGICCI1 (TNS V1-V3) 1.16 5c4qu2zmj3guload_fnsact@HPGICCI1 x (TNS V1-V3) 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 Totaper l DB ExeTimc (s) e CPU Time (s) 75 57 42 42 36 23 14 13 13 11 8 8 Elapsed Time (s) 76 93 51 58 38 23 14 13 13 15 8 10 Executions 172,329 SQL Id SQL Module SQL Text 0.00 11.52 4vja2k2gdtyup load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:... insert into CUID select CUID_... insert into CUSM select CUSM_... insert into CUMI select CUSV_... select c.name, u.name from co... insert into iccifnsact values... update ICCIFNSACT set BORM_AD... insert into OLDNEWACT values ... insert into ICCICCS values (:... DECLARE job BINARY_INTEGER := ... update ICCICCS set CCSMAXOVER... INSERT INTO STATS$SGA_TARGET_A... select * from ICCIPRODCODE wh... select CUID_CUST_NO , CUID_ID_... SELECT F.TABLESPACE_NAME, TO_... 1 57.31 14.10 d8z0u8hgj8xdy cuidmain@HPGICCI1 (TNS V1-V3) 1 42.43 1 42.01 166,069 172,329 172,983 172,337 166,051 5 172,329 5 0.00 0.00 0.00 0.00 0.00 2.23 0.00 1.60 7.68 ackxqhnktxnbc cusmmain@HPGICCI1 (TNS V1-V3) 8.75 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 5.67 7gtztzv329wg0 3.46 1dm3bq36vu3gload_fnsact@HPGICCI1 8 (TNS V1-V3) 2.16 7wwv1ybs9zguload_fnsact@HPGICCI1 z (TNS V1-V3) 2.00 gmn2w09rdxn1load_oldnewact@HPGIC4 CI1 (TNS V1-V3) 1.89 chjmy0dxf9mbj icci_migact@HPGICCI1 (TNS V1-V3) 2.25 djs2w2f17nw2z 1.25 38apjgr0p55ns load_fnsact@HPGICCI1 (TNS V1-V3) 1.44 1crajpb7j5tyz 8 4 3 8 10 35 172,983 1 1 0.00 3.54 3.13 1.16 5c4qu2zmj3guload_fnsact@HPGICCI1 x (TNS V1-V3) 1.46 0yv9t4qb1zb2b cuidmain@HPGICCI1 (TNS V1-V3) 5.28 6z06gcfw39pkd SQL*Plus 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

CPU Time (s) Buffer Gets 3,305,363 2,064,414 1,826,869 1,427,648 1,278,667 1,216,367 1,107,305 898,868 Executions 172,329 Gets per Exec 19.18 %Total Elapsed Time (s) SQL Id SQL Module SQL Text 19.85 74.57 76.41 4vja2k2gdtyuload_fnsact@HPGICCIp 1 (TNS V1-V3) 93.50 d8z0u8hgj8xdcuidmain@HPGICCI1 y (TNS V1-V3) 37.60 7gtztzv329wg 0 13.29 gmn2w09rdxn14 insert into ICCICCS values (:... insert into CUID select CUID_... select c.name, u.name from co... 1 2,064,414.00 166,069 11.00 12.40 57.31 10.97 35.84 172,337 8.28 8.58 12.97 load_oldnewact@HPGIinsert into CCI1 (TNS V1-V3) OLDNEWACT values ... insert into iccifnsact values... 172,329 7.42 7.68 22.85 22.94 1dm3bq36vu3load_fnsact@HPGICCIg8 1 (TNS V1-V3) 50.93 ackxqhnktxnbc 1 1,216,367.00 1 1,107,305.00 172,983 5.20 7.31 42.43 cusmmain@HPGICCI1 insert into (TNS V1-V3) CUSM select CUSM_... insert into CUMI select CUSV_... update ICCIFNSACT set BORM_AD... 6.65 42.01 58.04 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 14.34 7wwv1ybs9zgload_fnsact@HPGICCIuz 1 (TNS V1-V3) 5.40 14.28 711,450 166,051 4.28 4.27 12.52 12.55 chjmy0dxf9micci_migact@HPGICCI1 insert into bj (TNS V1-V3) ICCICCS values (:... 8.31 38apjgr0p55nload_fnsact@HPGICCIs 1 (TNS V1-V3) update ICCICCS set CCSMAXOVER... 692,996 172,329 4.02 4.16 8.31 666,748 166,052 4.02 4.00 6.36 6.36 7v9dyf5r424yicci_migact@HPGICCI1 select h (TNS V1-V3) NEWACTNO into :b0 from... 7.71 5c4qu2zmj3gload_fnsact@HPGICCIux 1 (TNS V1-V3) 5.83 49ms69srnaxload_fnsact@HPGICCIzj 1 (TNS V1-V3) select * from ICCIPRODCODE wh... insert into ICCIRPYV values (... 345,357 172,983 2.00 2.07 7.70 231,756 51,633 4.49 1.39 5.75 Back to SQL Statistics Back to Top

SQL ordered by Reads

? ?

Physical Reads Total Disk Reads: 322,678

Captured SQL account for 66.1% of Total

Reads %Totper al Exec CPU TimElapsed Time Executions SQL Id SQL Module SQL Text e (s) 66,286 50,646 1 66,286.00 1 50,646.00 1 24,507.00 1 21,893.00 1 19,761.00 1 19,554.00 1 6,342.00 1 4,385.00 5 12.60 20.54 57.31 15.70 3.54 (s) 93.50 d8z0u8hgj8xdcuidmain@HPGICinsert into CUID select y CI1 (TNS V1-V3) CUID_... 9.70 0yv9t4qb1zb2cuidmain@HPGICselect b CI1 (TNS V1-V3) CUID_CUST_NO , CUID_ID_... 58.04 569r5k05drsj7 50.93 ackxqhnktxnbc cumimain@HPGICinsert into CUMI select CI1 (TNS V1-V3) CUSV_... cusmmain@HPGIinsert into CUSM select CCI1 (TNS V1-V3) CUSM_... 24,507 21,893 19,761 7.59 42.01 6.78 42.43 6.12 2.14 6.04 a7nh7j8zmfrzcumimain@HPGICselect w CI1 (TNS V1-V3) CUSV_CUST_NO from CUMI... 3.83 38gak8u2qm1SQL*Plus 1w 35.00 6z06gcfw39pkd 2.43 cp5duhcsj72q0 14.91 djs2w2f17nw2z SQL*Plus select count(*) from CUSVAA_T... SELECT F.TABLESPACE_NAME, TO_... 19,554 6,342 6.06 1.27 1.97 3.13 4,385 1.36 1.59 cusmmain@HPGIselect CCI1 (TNS V1-V3) CUSM_CUST_ACCT_NO from... DECLARE job BINARY_INTEGER := ... BEGIN dbms_workload_repository... 63 0.02 11.17 0.01 0.08 35 1 35.00 0.67 1uk5m5qbzj1SQL*Plus vt Back to SQL Statistics Back to Top

SQL ordered by Executions

? ?

Total Executions: 1,675,112

Captured SQL account for 99.8% of Total

CPU per Exec (s) 0.00 Elap per Exec (s) Executions 172,983 Rows Processed 172,329 Rows per Exec 1.00 SQL Id SQL Module SQL Text select * from ICCIPRODCODE wh... update ICCIFNSACT set BORM_AD... 0.00 5c4qu2zmj3gux load_fnsact@HPGICCI1 (TNS V1-V3) 0.00 7wwv1ybs9zguz load_fnsact@HPGICCI1 (TNS V1-V3) 0.00 gmn2w09rdxn14 0.00 1dm3bq36vu3g8 0.00 38apjgr0p55ns 172,983 172,329 1.00 0.00 172,337 172,337 1.00 0.00 load_oldnewact@HPGICCIinsert into 1 (TNS V1-V3) OLDNEWACT values ... load_fnsact@HPGICCI1 (TNS V1-V3) load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values... update ICCICCS set CCSMAXOVER... insert into ICCICCS values (:... select c.name, u.name from co... 172,329 172,329 1.00 0.00 172,329 172,329 1.00 0.00 172,329 6,286 0.04 0.00 0.00 4vja2k2gdtyup load_fnsact@HPGICCI1 (TNS V1-V3) 166,069 166,069 1.00 0.00 0.00 7gtztzv329wg0 166,052 166,052 1.00 0.00 0.00 7v9dyf5r424yh icci_migact@HPGICCI1 (TNS V1-V3) select NEWACTNO into :b0 from... insert into ICCICCS values (:... select count(*) into :b0 fro... insert into ICCIRPYV values (... 166,051 166,051 1.00 0.00 0.00 chjmy0dxf9mbj icci_migact@HPGICCI1 (TNS V1-V3) 0.00 bu8tnqr3xv25q load_fnsact@HPGICCI1 (TNS V1-V3) 0.00 49ms69srnaxzj load_fnsact@HPGICCI1 (TNS V1-V3) 51,740 51,633 51,740 51,633 1.00 1.00 0.00 0.00 Back to SQL Statistics Back to Top

SQL ordered by Parse Calls

? ?

Parse Calls 166,069 6,304 2,437 1,568 1,554 444 421 421 86 81 Total Parse Calls: 182,780

Captured SQL account for 99.0% of Total

% Total Parses SQL Module Executions 166,069 6,304 2,438 1,568 1,554 444 421 421 86 81 SQL Id SQL Text select c.name, u.name from co... select type#, blocks, extents,... select file# from file$ where ... update seg$ set type#=:4, bloc... update tsq$ set blocks=:3, max... select blocks, maxblocks, gran... lock table sys.mon_mods$ in ex... update sys.mon_mods$ set inser... INSERT INTO sys.wri$_adv_messa... SELECT sys.wri$_adv_seq_msggro... 90.86 7gtztzv329wg0 1.33 bsa0wjtftg3uw 0.85 aq4js2gkfjru8 0.23 350f5yrnnmshs 0.23 g00cj285jmgsw 3.45 2ym6hhaq30r73 0.86 9qgtwh66xg6nz 0.24 104pd9mm3fh9p 0.05 3m8smr0v7v1m6 0.04 f80h0xb1qvbsk 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 CWT % of Elapsed CPU Time(Executions SQL Id SQL Module SQL Text Wait Time (s) 10.96 4.21 3.62 2.39 2.38 1.64 Elapsd Time 11.72 7.25 7.12 6.35 3.12 16.91 Time(s) 93.50 58.04 50.93 37.60 76.41 9.70 s) 57.31 42.01 42.43 35.84 74.57 3.54 1 d8z0u8hgj8xdy cuidmain@HPGICCI1 (TNS V1-V3) 1 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 1 ackxqhnktxnbc insert into CUID select CUID_... insert into CUMI select CUSV_... cusmmain@HPGICCI1 insert into CUSM select (TNS V1-V3) CUSM_... select c.name, u.name from co... 166,069 7gtztzv329wg0 172,329 4vja2k2gdtyup load_fnsact@HPGICCI1 insert into ICCICCS (TNS V1-V3) values (:... 1 0yv9t4qb1zb2b cuidmain@HPGICCI1 (TNS V1-V3) 1 6z06gcfw39pkd SQL*Plus select CUID_CUST_NO , CUID_ID_... SELECT F.TABLESPACE_NAME, TO_... select CUSV_CUST_NO from CUMI... select blocks, maxblocks, gran... select count(*) from CUSVAA_T... update tsq$ set blocks=:3, max... select obj#, type#, ctime, mti... insert into OLDNEWACT values ... 1.06 3.02 35.00 3.13 0.83 13.76 6.04 2.14 1 a7nh7j8zmfrzw cumimain@HPGICCI1 (TNS V1-V3) 444 104pd9mm3fh9p 0.66 0.50 0.50 0.33 0.33 0.29 0.25 87.90 13.01 51.75 91.11 2.47 1.26 10.14 0.75 3.83 0.96 0.36 13.29 22.94 2.43 0.42 1.27 0.79 0.33 12.97 22.85 1.59 1 38gak8u2qm11SQL*Plus w 1,554 aq4js2gkfjru8 187 04xtrk7uyhknh 172,337 gmn2w09rdxn14 172,329 1dm3bq36vu3g8 1 cp5duhcsj72q0 load_oldnewact@HPGICCI1 (TNS V1-V3) load_fnsact@HPGICCI1 insert into iccifnsact (TNS V1-V3) values... cusmmain@HPGICCI1 select (TNS V1-V3) CUSM_CUST_ACCT_NO from... update seg$ set type#=:4, bloc... 0.21 0.20 0.17 0.16 0.14 0.11 27.92 3.49 1.39 57.64 74.58 64.72 0.74 5.83 12.55 0.28 0.19 0.18 0.74 5.75 12.52 0.24 0.14 0.15 1,568 9qgtwh66xg6n z 51,633 49ms69srnaxzj load_fnsact@HPGICCI1 insert into ICCIRPYV (TNS V1-V3) values (... 166,051 chjmy0dxf9mbicci_migact@HPGICCI1 insert into ICCICCS j (TNS V1-V3) values (:... 39 cn1gtsav2d5jh cusvaamain@HPGICCIBEGIN BEGIN IF 1 (TNS V1-V3) (xdb.DBMS... 121 5ngzsfstg8tmy select o.owner#, o.name, o.nam... 80 78m9ryygp65vcusvaamain@HPGICCISELECT /*+ 5 1 (TNS V1-V3) ALL_ROWS */ COUNT(*... 17 bwt0pmxhv7qk7 327 53saa2zkr6wc3 delete from con$ where owner#=... select intcol#, nvl(pos#, 0), ... begin prvt_hdm.auto_execute( :... select i.obj#, i.ts#, i.file#,... select obj#, type#, flags, ... 0.11 0.11 0.08 94.54 80.26 19.20 0.12 0.14 0.42 0.01 0.14 0.24 1 d92h3rjp0y217 0.07 0.06 54.97 5.22 0.13 1.13 0.13 0.72 83 7ng34ruy5awx q 77 0hhmdwwgxbw0r 0.06 0.06 86.50 8.19 0.06 0.67 0.06 0.08 45 a2any035u1qz 1 1 1uk5m5qbzj1vt SQL*Plus select owner#, name from con$... BEGIN dbms_workload_repository... select pos#, intcol#, col#, sp... select file#, block# from seg... select type#, blocks, extents,... delete from RecycleBin$ ... select t.ts#, t.file#, t.block... update obj$ set obj#=:6, type#... INSERT INTO sys.wri$_adv_messa... delete from RecycleBin$ ... select con# from con$ where ow... select name, intcol#, segcol#,... DECLARE job BINARY_INTEGER := ... 0.04 0.04 0.04 0.03 0.03 0.03 0.02 0.02 0.02 0.02 0.02 0.02 0.02 0.01 0.01 75.69 48.05 8.84 28.15 66.23 67.03 66.73 26.94 76.76 51.91 0.15 2.12 53.82 0.10 8.29 0.06 0.09 0.40 0.12 0.05 0.05 0.04 0.09 0.03 0.05 14.91 1.00 0.03 14.34 0.16 0.06 0.07 0.40 0.12 0.05 0.05 0.04 0.09 0.03 0.05 11.17 0.99 0.03 14.28 0.13 87 6769wyy3yf66f 7 0pvtkmrrq8usg 6,304 2ym6hhaq30r7 3 49 b52m6vduutr8j 85 1gu8t96d0bdm u 38 btzq46kta67dz DBMS_SCHEDULER 86 3m8smr0v7v1m6 38 0k8h617b8guh f 51 9vtm7gy4fr2ny 84 83taa7kaw59c1 5 djs2w2f17nw2z 8,784 501v412s13r4m 39 bdv0rkkssq2jm 172,983 7wwv1ybs9zguz 421 g00cj285jmgsw load_fnsact@HPGICCI1 update ICCIFNSACT set (TNS V1-V3) BORM_FA... cusvaamain@HPGICCISELECT count(*) FROM 1 (TNS V1-V3) user_poli... load_fnsact@HPGICCI1 update ICCIFNSACT set (TNS V1-V3) BORM_AD... update sys.mon_mods$ set inser... insert into wrh$_latch (snap... insert into ICCICURMMAST valu... 0.01 0.01 0.01 1.65 22.33 0.08 0.56 0.04 7.71 0.54 0.02 7.70 2 84qubbrsr0kfn 26 44au3v5mzpc1load_curmmast@HPGIc CCI1 (TNS V1-V3) 172,983 5c4qu2zmj3guload_fnsact@HPGICCI1 select * from x (TNS V1-V3) ICCIPRODCODE wh... Back to SQL Statistics

Back to Top

Complete List of SQL Text

SQL Id SQL Text 04xtrk7select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where uyhknh owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null 0hhmdselect obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and wwgxbto_number(bitand(flags, 16)) = 16 order by dropscn w0r 0k8h61delete from RecycleBin$ where purgeobj=:1 7b8guhf 0pvtkmselect file#, block# from seg$ where type# = 3 and ts# = :1 rrq8us

g 0yv9t4select CUID_CUST_NO , CUID_ID_TYPE , CUID_ID_RECNO from CUID_TMP where CHGFLAG='D' qb1zb2b 104pd9select blocks, maxblocks, grantor#, priv1, priv2, priv3 from tsq$ where ts#=:1 and user#=:2 mm3fh9p 1crajpbINSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , 7j5tyz 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 1dm3binsert into iccifnsact values (:b0, :b1, :b2, null , null , :b3, :b4, GREATEST(:b5, :b6), null , :b7, :b8, q36vu3null , :b9, :b10, :b6, null , null , null , null , null , :b12, null , null , null , :b13, :b14, null , null , :b15, :b16, :b17) g8 1gu8t9select 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$, 6d0bdt.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, mu 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# (+) 1uk5mBEGIN dbms_workload_repository.create_snapshot; END; 5qbzj1vt 2ym6hselect type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, haq30r65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and 73 block#=:3 350f5yrlock table sys.mon_mods$ in exclusive mode nowait nnmshs 38apjgrupdate ICCICCS set CCSMAXOVERDUE=GREATEST(:b0, CCSMAXOVERDUE) where FNSACTNO=:b1 0p55ns 38gak8select count(*) from CUSVAA_TMP u2qm11w 3m8smINSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) r0v7v1VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13) m6 44au3vinsert into ICCICURMMAST values (:b0, :b1, :b2) 5mzpc1c 49ms6insert into ICCIRPYV values 9srnax(:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13, :b14, :b15, :b16, :b17, :b18, :b19, :b20, :bzj 21, :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) 4vja2kinsert into ICCICCS values (:b0, '////////////////////////', 0, 0, 0, 0, 0, ' ', 0, 0, 0, ' ', '0', null ) 2gdtyup 501v41update ICCIFNSACT set BORM_FACILITY_NO=:b0 where BORM_MEMB_CUST_AC=:b1 2s13r4m 53saa2select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1 zkr6wc3 569r5kinsert into CUMI select CUSV_CUST_NO , CUSV_EDUCATION_CODE , CHGDATE from CUMI_TMP where 05drsj7 CHGFLAG<>'D' 5c4qu2select * from ICCIPRODCODE where PRODCODE=to_char(:b0) zmj3gux 5ngzsfselect o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj#, o.flags from stg8tmobj$ o where o.obj#=:1 y 6769wselect pos#, intcol#, col#, spare1, bo#, spare2 from icol$ where obj#=:1 yy3yf66f 6z06gcSELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE), '999, 999') \fw39pk(MB)\d \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 78m9rySELECT /*+ ALL_ROWS */ COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND ygp65vV.OBJECT_NAME = :B2 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND 5 V.FUNCTION = :B1 7gtztzvselect c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and 329wgc.owner# = u.user# 0 7ng34rselect i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, uy5awi.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), xq 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# 7v9dyfselect NEWACTNO into :b0 from OLDNEWACT where OLDACTNO=:b1 5r424yh 7wwv1update ICCIFNSACT set BORM_ADV_DATE=:b0, BOIS_MATURITY_DATE=:b1, BOIS_UNPD_BAL=:b2, ybs9zgBOIS_UNPD_INT=:b3, BOIS_BAL_FINE=:b4, BOIS_INT_FINE=:b5, BOIS_FINE_FINE=:b6, uz BORM_LOAN_TRM=:b7, BORM_FIVE_STAT=:b8, BOIS_ARREARS_CTR=:b9, BOIS_ARREARS_SUM=:b10 where BORM_MEMB_CUST_AC=:b11 83taa7select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, kaw59-127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), c1 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# 84qubbinsert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, rsr0kfn 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 9qgtwhupdate seg$ set type#=:4, blocks=:5, extents=:6, minexts=:7, maxexts=:8, extsize=:9, extpct=:10, user#=:11, 66xg6niniexts=:12, lists=decode(:13, 65535, NULL, :13), groups=decode(:14, 65535, NULL, :14), cachehint=:15, z hwmincr=:16, spare1=DECODE(:17, 0, NULL, :17), scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 9vtm7gselect con# from con$ where owner#=:1 and name=:2 y4fr2ny a2any0select owner#, name from con$ where con#=:1 35u1qz1 a7nh7jselect CUSV_CUST_NO from CUMI_TMP where CHGFLAG='D' 8zmfrzw ackxqhinsert into CUSM select CUSM_CUST_ACCT_NO , CUSM_STAT_POST_ADD_NO , CHGDATE from nktxnbCUSM_TMP where CHGFLAG<>'D' c aq4js2update tsq$ set blocks=:3, maxblocks=:4, grantor#=:5, priv1=:6, priv2=:7, priv3=:8 where ts#=:1 and user#=:2 gkfjru8 b52m6delete from RecycleBin$ where bo=:1 vduutr8j bdv0rkSELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE kssq2j'%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPF' m bsa0wjselect file# from file$ where ts#=:1 tftg3uw btzq46update obj$ set obj#=:6, type#=:7, ctime=:8, mtime=:9, stime=:10, status=:11, dataobj#=:13, flags=:14, kta67doid$=:15, spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or z 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) bu8tnqselect count(*) into :b0 from ICCIFNSACT where BORM_MEMB_CUST_AC=:b1 r3xv25q bwt0pdelete from con$ where owner#=:1 and name=:2 mxhv7qk7 chjmy0insert into ICCICCS values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13) dxf9mbj cn1gtsBEGIN BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, av2d5jsys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN h 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; cp5duhselect CUSM_CUST_ACCT_NO from CUSM_TMP where CHGFLAG='D' csj72q0 d8z0u8insert into CUID select CUID_CUST_NO , CUID_ID_MAIN , CUID_ID_TYPE , CUID_ID_RECNO , hgj8xdCUID_ID_NUMBER , CHGDATE from CUID_TMP where CHGFLAG<>'D' y d92h3rjbegin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; p0y217 djs2w2DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN f17nw2statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; z f80h0xSELECT sys.wri$_adv_seq_msggroup.nextval FROM dual b1qvbsk g00cj2update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = 85jmgs(decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, w timestamp = :time where obj# = :objn gmn2winsert into OLDNEWACT values (:b0, :b1) 09rdxn14 Back to SQL Statistics Back to Top

Instance Activity Statistics

? ? ? Instance Activity Stats Instance Activity Stats - Absolute Values Instance Activity Stats - Thread Activity Back to Top

Instance Activity Stats

Statistic CPU used by this session CPU used when call started CR blocks created Cached Commit SCN referenced Commit SCN cached DB time DBWR checkpoint buffers written DBWR checkpoints DBWR fusion writes Total 23,388 21,816 2,794 237,936 3 583,424 402,781 9 255 per Second 4.95 4.61 0.59 50.33 0.00 123.41 85.20 0.00 0.05 per Trans 4.18 3.90 0.50 42.50 0.00 104.22 71.95 0.00 0.05 DBWR object drop buffers written DBWR thread checkpoint buffers written DBWR transaction table writes DBWR undo block writes DFO trees parallelized PX local messages recv'd PX local messages sent PX remote messages recv'd PX remote messages sent Parallel operations not downgraded RowCR - row contention RowCR attempts RowCR hits SMON posted for undo segment recovery SMON posted for undo segment shrink SQL*Net roundtrips to/from client active txn count during cleanout application wait time auto extends on undo tablespace background checkpoints completed background checkpoints started background timeouts branch node splits buffer is not pinned count buffer is pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas calls to kcmgcs change write time cleanout - number of ktugct calls cleanouts and rollbacks - consistent read gets cleanouts only - consistent read gets cluster key scan block gets cluster key scans cluster wait time commit batch/immediate performed commit batch/immediate requested commit cleanout failures: block lost commit cleanout failures: callback failure commit cleanout failures: cannot pin commit cleanouts commit cleanouts successfully completed commit immediate performed commit immediate requested commit txn count during cleanout concurrency wait time consistent changes consistent gets 0 221,341 130 219,272 16 40 40 80 80 16 9 14 5 0 9 1,544,063 276,652 1,620 0 7 9 21,703 337 1,377,184 20,996,139 149,122,035 1,696,712 433,435 142,482 4,707 282,045 55 2,406 21,886 10,540 2,855 294 294 2,227 750 4 427,610 424,629 294 294 111,557 515 1,716 5,037,471 0.00 46.82 0.03 46.38 0.00 0.01 0.01 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 326.62 58.52 0.34 0.00 0.00 0.00 4.59 0.07 291.32 4,441.37 31,544.22 358.91 91.69 30.14 1.00 59.66 0.01 0.51 4.63 2.23 0.60 0.06 0.06 0.47 0.16 0.00 90.45 89.82 0.06 0.06 23.60 0.11 0.36 1,065.59 0.00 39.54 0.02 39.17 0.00 0.01 0.01 0.01 0.01 0.00 0.00 0.00 0.00 0.00 0.00 275.82 49.42 0.29 0.00 0.00 0.00 3.88 0.06 246.01 3,750.65 26,638.45 303.09 77.43 25.45 0.84 50.38 0.01 0.43 3.91 1.88 0.51 0.05 0.05 0.40 0.13 0.00 76.39 75.85 0.05 0.05 19.93 0.09 0.31 899.87 7,381,397,183 1,561,408.36 1,318,577.56 consistent gets - examination consistent gets direct consistent gets from cache current blocks converted for CR cursor authentications data blocks consistent reads - undo records applied db block changes db block gets db block gets direct db block gets from cache deferred (CURRENT) block cleanout applications dirty buffers inspected drop segment calls in space pressure enqueue conversions enqueue releases enqueue requests enqueue timeouts enqueue waits exchange deadlocks execute count free buffer inspected free buffer requested gc CPU used by this session gc cr block build time gc cr block flush time gc cr block receive time gc cr block send time gc cr blocks received gc cr blocks served gc current block flush time gc current block pin time gc current block receive time gc current block send time gc current blocks received gc current blocks served gc local grants gc remote grants gcs messages sent ges messages sent global enqueue get time global enqueue gets async global enqueue gets sync global enqueue releases global undo segment hints helped global undo segment hints were stale heap block compress hot buffers moved to head of LRU immediate (CR) block cleanout applications immediate (CURRENT) block cleanout applications index crx upgrade (positioned) 2,902,016 0 5,037,471 0 434 1,519 8,594,158 11,611,321 1,167,830 10,443,491 20,786 25,007 0 6,734 595,149 595,158 9 7,901 1 1,675,112 536,832 746,999 9,099 13 143 474 36 4,142 10,675 23 34 1,212 52 15,502 17,534 405,329 318,630 1,129,094 90,695 1,707 12,731 190,492 190,328 0 0 108,758 18,652 2,462 325,184 4,663 613.87 0.00 1,065.59 0.00 0.09 0.32 1,817.95 2,456.18 247.03 2,209.14 4.40 5.29 0.00 1.42 125.89 125.90 0.00 1.67 0.00 354.34 113.56 158.01 1.92 0.00 0.03 0.10 0.01 0.88 2.26 0.00 0.01 0.26 0.01 3.28 3.71 85.74 67.40 238.84 19.18 0.36 2.69 40.30 40.26 0.00 0.00 23.01 3.95 0.52 68.79 0.99 518.40 0.00 899.87 0.00 0.08 0.27 1,535.22 2,074.19 208.62 1,865.58 3.71 4.47 0.00 1.20 106.31 106.32 0.00 1.41 0.00 299.23 95.90 133.44 1.63 0.00 0.03 0.08 0.01 0.74 1.91 0.00 0.01 0.22 0.01 2.77 3.13 72.41 56.92 201.70 16.20 0.30 2.27 34.03 34.00 0.00 0.00 19.43 3.33 0.44 58.09 0.83

index fast full scans (full) index fetch by key index scans kdiixs1 leaf node 90-10 splits leaf node splits lob reads lob writes lob writes unaligned local undo segment hints helped local undo segment hints were stale logons cumulative messages received messages sent no buffer to keep pinned count no work - consistent read gets opened cursors cumulative parse count (failures) parse count (hard) parse count (total) parse time cpu parse time elapsed physical read IO requests physical read bytes physical read total IO requests physical read total bytes physical read total multi block requests physical reads physical reads cache physical reads cache prefetch physical reads direct physical reads direct temporary tablespace physical reads prefetch warmup physical write IO requests physical write bytes physical write total IO requests physical write total bytes physical write total multi block requests physical writes physical writes direct physical writes direct (lob) physical writes direct temporary tablespace physical writes from cache physical writes non checkpoint pinned buffers inspected prefetch clients - default prefetch warmup blocks aged out before use prefetch warmup blocks flushed out before use prefetched blocks aged out before use process last non-idle time queries parallelized 13 852,181 339,583 34 106,552 11 83 83 0 0 61 20,040 19,880 0 1,513,070 183,375 1 143 182,780 27 338 82,815 2,643,378,176 98,871 2,905,491,456 24,089 322,678 213,728 191,830 108,950 108,812 0 223,456 133,835 116,135 1,714,120 1,276,780 0 108,812 437,340 1,673,703 10 0 0 0 0 4,730 16 0.00 180.26 71.83 0.01 22.54 0.00 0.02 0.02 0.00 0.00 0.01 4.24 4.21 0.00 320.06 38.79 0.00 0.03 38.66 0.01 0.07 17.52 559,161.45 20.91 614,607.04 5.10 68.26 45.21 40.58 23.05 23.02 0.00 47.27 28.31 24.57 362.59 270.08 0.00 23.02 92.51 354.04 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 152.23 60.66 0.01 19.03 0.00 0.01 0.01 0.00 0.00 0.01 3.58 3.55 0.00 270.29 32.76 0.00 0.03 32.65 0.00 0.06 14.79 472,200.46 17.66 519,023.13 4.30 57.64 38.18 34.27 19.46 19.44 0.00 39.92 23.91 20.75 306.20 228.08 0.00 19.44 78.12 298.98 0.00 0.00 0.00 0.00 0.00 0.84 0.00 14,042,071,040 2,970,360.02 2,508,408.55 23,114,268,672 4,889,428.30 4,129,022.63 recursive calls recursive cpu usage redo blocks written redo buffer allocation retries redo entries redo log space requests redo log space wait time redo ordering marks redo size redo subscn max counts redo synch time redo synch writes redo wastage redo write time redo writer latching time redo writes rollback changes - undo records applied rollbacks only - consistent read gets rows fetched via callback session connect time session cursor cache hits session logical reads session pga memory session pga memory max session uga memory session uga memory max shared hash latch upgrades - no wait shared hash latch upgrades - wait sorts (disk) sorts (memory) sorts (rows) space was found by tune down space was not found by tune down sql area evicted sql area purged steps of tune down ret. in space pressure summed dirty queue length switch current to new buffer table fetch by rowid table fetch continued row table scan blocks gotten table scan rows gotten table scans (long tables) table scans (short tables) total number of times SMON posted transaction lock background get time transaction lock background gets transaction lock foreground requests transaction lock foreground wait time transaction rollbacks 1,654,650 2,641 8,766,094 24 4,707,068 34 50 277,042 4,343,559,400 2,693 408 6,984 1,969,620 5,090 1 5,494 166,609 1,463 342,159 1,461 180,472 16,648,792 37,393,448 45,192,232 61,930,448 6,364 0 4 2,857 42,379,505 0 0 7 44 0 35,067 17 680,469 0 790,986 52,989,363 4 169,201 259 0 0 0 0 294 350.01 0.56 1,854.32 0.01 995.70 0.01 0.01 58.60 918,805.72 0.57 0.09 1.48 416.64 1.08 0.00 1.16 35.24 0.31 72.38 0.31 38.18 3,521.77 7,909.94 9,559.64 13,100.33 1.35 0.00 0.00 0.60 8,964.66 0.00 0.00 0.00 0.01 0.00 7.42 0.00 143.94 0.00 167.32 11,208.99 0.00 35.79 0.05 0.00 0.00 0.00 0.00 0.06 295.58 0.47 1,565.93 0.00 840.85 0.01 0.01 49.49 775,912.72 0.48 0.07 1.25 351.84 0.91 0.00 0.98 29.76 0.26 61.12 0.26 32.24 2,974.06 6,679.79 8,072.92 11,062.96 1.14 0.00 0.00 0.51 7,570.47 0.00 0.00 0.00 0.01 0.00 6.26 0.00 121.56 0.00 141.30 9,465.77 0.00 30.23 0.05 0.00 0.00 0.00 0.00 0.05 30,067,312,240 6,360,225.77 5,371,081.14 tune down retentions in space pressure undo change vector size user I/O wait time user calls user commits user rollbacks workarea executions - onepass workarea executions - optimal write clones created in background write clones created in foreground Back to Instance Activity Statistics Back to Top

0 1,451,085,596 11,992 1,544,383 812 4,786 1 1,616 0 11 0.00 306,952.35 2.54 326.69 0.17 1.01 0.00 0.34 0.00 0.00 0.00 259,215.00 2.14 275.88 0.15 0.85 0.00 0.29 0.00 0.00 Instance Activity Stats - Absolute Values

?

Statistics with absolute values (should not be diffed) Statistic session cursor cache count opened cursors current logons current Back to Instance Activity Statistics Back to Top

Begin Value End Value 3,024 37 24 3,592 39 26 Instance Activity Stats - Thread Activity

?

Statistics identified by '(derived)' come from sources other than SYSSTAT

Total per Hour 9 6.85 Statistic log switches (derived) Back to Instance Activity Statistics Back to Top

IO Stats

? ? Tablespace IO Stats File IO Stats Back to Top

Tablespace IO Stats

?

ordered by IOs (Reads + Writes) desc

Av Reads/s 14 0 3 Av Rd(ms) 3.76 12.00 8.74 Av Blks/Rd Av Writes/s 34 12 1 Buffer Waits 6 625 0 Av Buf Wt(ms) 0.00 0.02 0.00 Tablespace Reads ICCIDAT01 UNDOTBS1 TEMP 67,408 10 15,022 Writes 3.17 160,261 1.00 57,771 7.24 3,831 USERS SYSAUX SYSTEM UNDOTBS2 68 263 32 6 0 0 0 0 5.44 5.48 5.94 16.67 1.00 1.00 1.00 1.00 971 458 158 6 0 0 0 0 0 0 3 0 0.00 0.00 23.33 0.00 显示每个表空间的I/O统计。根据Oracle经验，Av Rd(ms) [Average Reads in milliseconds]不应该超过30，否则认为有I/O争用。

Back to IO Stats Back to Top

File IO Stats

?

ordered by Tablespace, File

Av Reads/s 1 2 1 2 1 1 2 2 1 0 0 0 0 0 0 0 0 0 0 0 1 1 0 2 0 0 0 Av Rd(ms) 4.30 4.12 2.59 2.93 2.61 4.03 4.12 3.83 4.79 5.31 4.45 4.23 5.13 4.91 5.29 7.23 6.26 8.44 5.48 5.94 5.67 4.42 2.50 12.43 12.00 16.67 5.44 Av Blks/Rd Av Writes/s 3 4 3 3 5 4 4 4 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12 0 0 Buffer Waits 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 0 3 0 0 0 0 625 0 0 Av Buf Wt(ms) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 23.33 0.02 0.00 0.00 Tablespace ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 ICCIDAT01 SYSAUX SYSTEM TEMP TEMP TEMP TEMP Filename /dev/rora_icci01 /dev/rora_icci02 /dev/rora_icci03 /dev/rora_icci04 /dev/rora_icci05 /dev/rora_icci06 /dev/rora_icci07 /dev/rora_icci08 /dev/rora_icci09 /dev/rora_icci10 /dev/rora_icci11 /dev/rora_icci12 /dev/rora_icci13 /dev/rora_icci14 /dev/rora_icci15 /dev/rora_icci16 /dev/rora_icci17 /dev/rora_icci18 /dev/rora_SYSAUX /dev/rora_SYSTEM /dev/rora_TEMP /dev/rora_TEMP2 /dev/rora_TEMP3 /dev/rora_TEMP5 Reads 5,919 7,692 6,563 8,076 6,555 6,943 7,929 7,719 6,794 211 1,168 478 355 411 172 119 227 77 263 32 3,653 2,569 1,022 7,778 10 6 68 Writes 3.73 15,161 3.18 16,555 3.80 15,746 3.11 16,164 3.31 21,958 3.41 20,574 2.87 18,263 2.99 17,361 3.29 18,425 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 6.61 6.70 16.86 6.46 6 6 6 6 6 6 6 6 6 458 158 827 556 557 1,891 UNDOTBS1 /dev/rora_UNDO0101 UNDOTBS2 /dev/rora_UNDO0201 USERS /dev/rora_USERS 1.00 57,771 1.00 1.00 6 971 Back to IO Stats Back to Top

Buffer Pool Statistics

? ?

P D Standard block size Pools D: default, K: keep, R: recycle Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Pool Hit% Buffer Gets Physical Reads 213,729 Physical Writes 437,340 Free Buff Wait 0 Writ Comp Wait 0 Buffer Busy Waits 634 Number of Buffers 401,071 99 15,480,754

Back to Top

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 Back to Top

Instance Recovery Stats

?

B: Begin snapshot, E: End snapshot

Estd MTTR (s) 0 0 11 98 Actual Redo Blks 2316 1828613 Target Redo Blks 5807 1883700 Log File Size Redo Blks 1883700 1883700 Log Ckpt Timeout Redo Blks 5807 5033355 Log Ckpt Interval Redo Blks B E Targt MTTR (s) Recovery Estd IOs 369 116200 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 D D D D D D D D 320 640 960 1,280 1,600 1,920 2,240 2,560 2,880 0.10 0.19 0.29 0.38 0.48 0.57 0.67 0.77 0.86 38,380 76,760 115,140 153,520 191,900 230,280 268,660 307,040 345,420 1.34 1.25 1.08 1.04 1.02 1.01 1.01 1.01 1.01 10,351,726 9,657,000 8,365,242 8,059,415 7,878,202 7,841,140 7,829,141 7,817,370 7,804,884

D D D D D D D D D D D D 3,200 3,344 3,520 3,840 4,160 4,480 4,800 5,120 5,440 5,760 6,080 6,400 0.96 1.00 1.05 1.15 1.24 1.34 1.44 1.53 1.63 1.72 1.82 1.91 383,800 401,071 422,180 460,560 498,940 537,320 575,700 614,080 652,460 690,840 729,220 767,600 1.00 1.00 0.99 0.99 0.99 0.99 0.99 0.98 0.98 0.98 0.98 0.98 7,784,014 7,748,403 7,702,243 7,680,429 7,663,046 7,653,232 7,645,544 7,630,008 7,616,886 7,614,591 7,613,191 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 Back to Advisory Statistics Back to Top

1,100 151 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

%Man W/A Mem 0.00 0.00 B E PGA Aggr Target(M) 1,024 1,024 Auto PGA Target(M) 862 860 PGA Mem Alloc(M) 150.36 154.14 W/A PGA Used(M) 0.00 0.00 %PGA %Auto W/A Mem W/A Mem 0.00 0.00 0.00 0.00 Global Mem Bound(K) 104,850 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 64K 128K 4K 128K 256K 1,385 28 5 1,385 28 5 0 0 0 0 0 0 256K 512K 1M 8M 128M 256M 512K 1024K 2M 16M 256M 512M 79 108 7 1 3 1 79 108 7 1 2 1 0 0 0 0 1 0 0 0 0 0 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

Size Factr 0.13 0.25 0.50 0.75 1.00 1.20 1.40 1.60 1.80 2.00 3.00 4.00 6.00 8.00 W/A MB Processed 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 4,652.12 Estd Extra W/A MB Read/ Written to Disk 2,895.99 2,857.13 2,857.13 2,857.13 717.82 717.82 717.82 717.82 717.82 717.82 717.82 717.82 717.82 717.82 Estd PGA Cache Hit % 62.00 62.00 62.00 62.00 87.00 87.00 87.00 87.00 87.00 87.00 87.00 87.00 87.00 87.00 Estd PGA Overalloc Count 0 0 0 0 0 0 0 0 0 0 0 0 0 0 PGA Target Est (MB) 128 256 512 768 1,024 1,229 1,434 1,638 1,843 2,048 3,072 4,096 6,144 8,192 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.

Est LC Size (M) 78 78 78 78 78 78 78 78 Est LC Mem Obj 7,626 7,626 7,626 7,626 7,626 7,626 7,626 7,626 Est LC Time Saved (s) 64,842 64,842 64,842 64,842 64,842 64,842 64,842 64,842 Est LC Time Saved Factr 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 Est LC Load Time (s) 31 31 31 31 31 31 31 31 Est LC Est LC Mem Load Time Obj Hits Factr 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 Shared Pool Size(M) 304 384 464 544 624 704 784 864 SP Size Factr 0.43 0.55 0.66 0.77 0.89 1.00 1.11 1.23 944 1,024 1,104 1,184 1,264 1,344 1,424 1.34 1.45 1.57 1.68 1.80 1.91 2.02 78 78 78 78 78 78 78 7,626 7,626 7,626 7,626 7,626 7,626 7,626 64,842 64,842 64,842 64,842 64,842 64,842 64,842 1.00 1.00 1.00 1.00 1.00 1.00 1.00 31 31 31 31 31 31 31 1.00 1.00 1.00 1.00 1.00 1.00 1.00 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 3,206,955 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 2,048 3,072 4,096 5,120 6,144 7,168 8,192 Back to Advisory Statistics Back to Top

0.25 0.50 0.75 1.00 1.25 1.50 1.75 2.00 9,060 7,612 7,563 7,451 7,423 7,397 7,385 7,385 9,742,760 7,948,245 7,886,258 7,748,338 7,713,470 7,680,927 7,666,980 7,666,980 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 Enqueue Activity Back to Top

Buffer Wait Statistics

?

ordered by wait time desc, waits desc

Waits Total Wait Time (s) Avg Time (ms) Class data block undo header file header block undo block 3 616 8 7 0 0 0 0 23 0 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

Succ Gets 14,075 964 24 4,223 10,548 1 121,761 103 9,933 12 18 16 3 3 11 14 2 2 6 Failed Gets 0 0 0 0 0 0 6 0 0 0 0 0 0 0 3 0 0 0 0 Wt Time (s) 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Av Wt Time(ms) 0.43 0.32 9.00 1.22 0.67 35.00 0.43 0.65 0.54 1.42 0.38 0.50 0.67 0.33 0.33 1.00 1.00 1.00 0.00 Enqueue Type (Request Reason) FB-Format Block US-Undo Segment WF-AWR Flush HW-Segment High Water Mark CF-Controlfile Transaction TX-Transaction (index contention) TM-DML PS-PX Process Reservation TT-Tablespace TD-KTF map table enqueue (KTF dump entries) TA-Instance Undo PI-Remote PX Process Spawn Status MW-MWIN Schedule DR-Distributed Recovery TS-Temporary Segment AF-Advisor Framework (task serialization) JS-Job Scheduler (job run lock - synchronize) UL-User-defined MD-Materialized View Log DDL Back to Wait Statistics Back to Top

Requests 14,075 964 24 4,223 10,548 1 121,768 103 9,933 12 18 16 3 3 14 14 2 2 6 Waits 7,033 556 14 37 58 1 70 46 39 12 13 8 3 3 3 1 1 1 2 Undo Statistics

? ? Undo Segment Summary Undo Segment Stats Back to Top

Undo Segment Summary

? ?

Min/Max TR (mins) - Min and Max Tuned Retention (minutes) STO - Snapshot Too Old count, OOS - Out of Space count

? ? ?

Undo TS# 1 Undo segment block stats:

uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed

Max Qry Len (s) 0 Num Undo Blocks (K) 219.12 Number of Transactions 113,405 Max Tx Concurcy Min/Max TR (mins) 6 130.95/239.25 STO/ OOS 0/0 uS/uR/uU/ eS/eR/eU 0/0/0/13/24256/0 Back to Undo Statistics Back to Top

Undo Segment Stats

?

End Time 25-Dec 15:18 25-Dec 15:08 25-Dec 14:58 25-Dec 14:48 25-Dec 14:38 25-Dec 14:28 25-Dec 14:18 25-Dec 14:08 Most recent 35 Undostat rows, ordered by Time desc Num Undo Blocks 182,021 57 68 194 570 36,047 70 91 Number of Transactions 74,309 170 31 4,256 12,299 21,328 907 105 Max Qry Len (s) 0 0 0 0 0 0 0 0 Max Tx Concy 5 3 2 4 5 6 3 3 Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU 0/0/0/13/24256/0 0/0/0/0/0/0 0/0/0/0/0/0 0/0/0/0/0/0 0/0/0/0/0/0 0/0/0/0/0/0 0/0/0/0/0/0 0/0/0/0/0/0 131 0/0 239 0/0 229 0/0 219 0/0 209 0/0 200 0/0 162 0/0 154 0/0 Back to Undo Statistics Back to Top

Latch Statistics

? ? ? ? ? Latch Activity Latch Sleep Breakdown Latch Miss Sources Parent Latch Statistics Child Latch Statistics Back to Top

Latch Activity

? ? ?

\requests

\ \ Latch Name Get Pct Get Avg Slps Wait NoWait Pct NoWait

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

Limit 450063 450063 Resource Name Current Utilization Maximum Utilization Initial Allocation gcs_resources gcs_shadows 349,392 400,300 446,903 450063 447,369 450063

Back to Top

init.ora Parameters

Parameter Name Begin value End value (if different) audit_file_dest background_dump_dest cluster_database cluster_database_instances compatible control_files core_dump_dest db_block_size db_domain db_name dispatchers instance_number job_queue_processes open_cursors pga_aggregate_target processes remote_listener remote_login_passwordfile sga_max_size sga_target sort_area_size spfile thread undo_management undo_retention undo_tablespace user_dump_dest /oracle/app/oracle/admin/ICCI/adump /oracle/app/oracle/admin/ICCI/bdump TRUE 2 10.2.0.3.0 /oracle/app/oracle/admin/ICCI/cdump 8192 ICCI (PROTOCOL=TCP) (SERVICE=ICCIXDB) 1 10 800 1073741824 500 LISTENERS_ICCI EXCLUSIVE 4294967296 4294967296 196608 /dev/rora_SPFILE 1 AUTO 900 UNDOTBS1 /oracle/app/oracle/admin/ICCI/udump /dev/rora_CTL01, /dev/rora_CTL02, /dev/rora_CTL03 db_file_multiblock_read_count 16

Back to Top

More RAC Statistics ? ? ? ?

Global Enqueue Statistics Global CR Served Stats Global CURRENT Served Stats Global Cache Transfer Stats Back to Top

Global Enqueue Statistics

Statistic acks for commit broadcast(actual) acks for commit broadcast(logical) broadcast msgs on commit(actual) Total 18,537 21,016 5,193 per Second per Trans 3.92 4.45 1.10 3.31 3.75 0.93 broadcast msgs on commit(logical) broadcast msgs on commit(wasted) dynamically allocated gcs resources dynamically allocated gcs shadows false posts waiting for scn acks flow control messages received flow control messages sent gcs assume cvt gcs assume no cvt gcs ast xid gcs blocked converts gcs blocked cr converts gcs compatible basts gcs compatible cr basts (global) gcs compatible cr basts (local) gcs cr basts to PIs gcs cr serve without current lock gcs dbwr flush pi msgs gcs dbwr write request msgs gcs error msgs gcs forward cr to pinged instance gcs immediate (compatible) converts gcs immediate (null) converts gcs immediate cr (compatible) converts gcs immediate cr (null) converts gcs indirect ast gcs lms flush pi msgs gcs lms write request msgs gcs msgs process time(ms) gcs msgs received gcs out-of-order msgs gcs pings refused gcs pkey conflicts retry gcs queued converts gcs recovery claim msgs gcs refuse xid gcs regular cr gcs retry convert request gcs side channel msgs actual gcs side channel msgs logical gcs stale cr gcs undo cr gcs write notification msgs gcs writes refused ges msgs process time(ms) ges msgs received global posts dropped global posts queue time global posts queued global posts requested 5,491 450 0 0 0 0 2 0 9,675 1 7,099 8,442 45 273 12,593 0 0 223 223 0 0 2,998 170,925 0 722,748 306,817 0 189 16,164 1,792,132 0 0 0 2 0 0 0 0 437 21,086 3,300 5 23 3 1,289 138,891 0 0 0 0 1.16 0.10 0.00 0.00 0.00 0.00 0.00 0.00 2.05 0.00 1.50 1.79 0.01 0.06 2.66 0.00 0.00 0.05 0.05 0.00 0.00 0.63 36.16 0.00 152.88 64.90 0.00 0.04 3.42 379.09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.09 4.46 0.70 0.00 0.00 0.00 0.27 29.38 0.00 0.00 0.00 0.00 0.98 0.08 0.00 0.00 0.00 0.00 0.00 0.00 1.73 0.00 1.27 1.51 0.01 0.05 2.25 0.00 0.00 0.04 0.04 0.00 0.00 0.54 30.53 0.00 129.11 54.81 0.00 0.03 2.89 320.14 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.08 3.77 0.59 0.00 0.00 0.00 0.23 24.81 0.00 0.00 0.00 0.00 global posts sent implicit batch messages received implicit batch messages sent lmd msg send time(ms) lms(s) msg send time(ms) messages flow controlled messages queue sent actual messages queue sent logical messages received actual messages received logical messages sent directly messages sent indirectly messages sent not implicit batched messages sent pbatched msgs causing lmd to send msgs msgs causing lms(s) to send msgs msgs received queue time (ms) msgs received queued msgs sent queue time (ms) msgs sent queue time on ksxp (ms) msgs sent queued msgs sent queued on ksxp process batch messages received process batch messages sent 0 81,181 19,561 0 0 15,306 108,411 222,518 474,202 1,931,144 25,742 137,725 88,859 1,050,224 61,682 85,978 911,013 1,931,121 5,651 66,767 215,124 243,729 120,003 181,019 0.00 17.17 4.14 0.00 0.00 3.24 22.93 47.07 100.31 408.50 5.45 29.13 18.80 222.16 13.05 18.19 192.71 408.50 1.20 14.12 45.51 51.56 25.38 38.29 0.00 14.50 3.49 0.00 0.00 2.73 19.37 39.75 84.71 344.97 4.60 24.60 15.87 187.61 11.02 15.36 162.74 344.97 1.01 11.93 38.43 43.54 21.44 32.34

Back to Top

Global CR Served Stats

Statistic CR Block Requests CURRENT Block Requests Data Block Requests Undo Block Requests TX Block Requests Current Results Private results Zero Results Disk Read Results Fail Results Fairness Down Converts Fairness Clears Free GC Elements Flushes Flushes Queued Flush Queue Full Flush Max Time (us) Light Works Errors Total 10,422 251 10,422 2 20 10,664 4 5 0 0 1,474 0 0 370 0 0 0 2 0

Back to Top

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 Flushes Writes 17,534 77 255 99.96 48.05 5.49 0.01 46.75 53.73 0.03 5.19 40.00 0.00 0.00 0.78 0.00 0.00 0.00

Back to Top

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

Inst No Block Class Blocks Received 3,945 191 11 2 data block 2 Others 2 undo header CR % Immed 87.20 100.00 100.00 % Busy 12.80 0.00 0.00 % Congst 0.00 0.00 0.00 Blocks Received 13,324 2,190 2 Current % Immed 99.71 96.48 100.00 % Busy 0.26 3.52 0.00 % Congst 0.04 0.00 0.00

Back to Top

End of Report

本文来源：https://www.bwwdw.com/article/lh9o.html