WORKLOAD REPOSITORY PDB report (PDB snapshots)

AWR报告需要关心以下方面
1）数据库的参数，重点看：
Initialization Parameters
2）SQL执行时间和执行次数，请看
SQL Statistics
3）TOP等待事件，请看
Top 10 Foreground Events by Total Wait Time&Wait Classes by Total Wait Time
Top SQL with Top Events
4）看CPU是否为瓶颈，请看
WORKLOAD REPOSITORY report for中的session分析
SQL ordered by CPU Time
5）看IO是否为瓶颈，请监控服务器的iowait,如大于5%的，请分析SQL或者等待事件log file sync。
若IO为瓶颈,重点看
SQL ordered by User I/O Wait Time
Segment Statistics
6）数据库锁，请重点看：
Segments by Row Lock Waits
Segments by Buffer Busy Waits
Segments by Table Scans
Top SQL with Top Row SourcesAWR全称叫Automatic Workload Repository-自动负载信息库, AWR 是通过对比两次快照(snapshot)收集到的统计信息，来生成报表数据。
本节需要关注：
1）CPU负载
2）Sessions
其中具体解释如下：
1）CPU负载参考计算公式：DB CPU(s)/CPU Cores = (38.2/24)*100% =159%>= 80%  ##38.2和24取自AWR中真实的数据
Oracle指标说明：CPU负载大于70%，说明CPU比较繁忙；OLTP系统CPU负载使用率建议最大在60%~65%。OLAP/AW系统CPU负载使用率最大可以使用到100%；Oracle数据库模式是负载均衡或应用隔离时，两个实例的CPU负载的差异大于50%，需要关注。若主备模式或读写分离模式，差异大于50%，可能是正常的。
2）Cursors/Session，见当出现以下几种情况时，需要重点关注：Oracle数据库模式是负载均衡或应用隔离时，两个实例的session的差异值大于50%；session值是并发用户数的两倍。自动数据库性能监视器（ADDM）自动检查和报告数据库的性能问题，ADDM是构建在Oracle数据库内部的自我诊断软件，ADDM检查并分析自动工作量仓库（AWR）
捕获到的数据，确定Oracle数据库可能存在的性能问题，然后它定位性能问题的根本原因，为纠正这些性能问题提供建议，并量化预计的性能收益。 
本节需要关注：
    1）Finding Name里的事项，数据库需要重点关注的项；2）结合Main Report里的ADDM Reports一起分析。Load Profile显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。
如果DB Time(s) 远远大于DB CPU(s)，说明CPU很大部分时间在等待，需要关注等待事件
本节需要关注
    1）Redo size：每秒/每事务产生的redo大小（单位字节），可标志数据库任务的繁重程度；2）Logical read (blocks)：每秒/每事务逻辑读的块数，值过高，可能索引的效率不高；3）Physical read (blocks)/ Physical write (blocks)：每秒/每事务物理读/写的块数，值过高，有可能表少索引；4）Parses (SQL)/ Hard parses (SQL):解析次数/硬解析次数，参考下面解释，硬解析过多，需要考虑是否使用绑定变量；5）Sorts：每秒/每事务的排序次数：排序在PGA中进行，如果值很大，需要检查PGA的值是否合适；6）Executes：每秒/每事务SQL执行次数7）Transactions：每秒事务数，反映数据库任务繁重与否
Oracle的硬解析和软解析说明
当你发出一条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的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。Oracle指标说明：
    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很少 。Oracle等待事件，是Oracle性能诊断的专利。
    1.等待事件主要可以分为两类，即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。1) 空闲等待事件指ORACLE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。2) 非空闲等待事件专门针对ORACLE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。在Oracle 10g中的等待事件有872个，11g中等待事件1116个，19c中有1920个。 我们可以通过v$event_name 视图来查看等待事件的相关信息。这里我们关注Oracle的非空闲等待事件。2.常见的TOP 10等待事件和一般解决方法1）DB CPU，这个排在TOP1，是正常情况2）log file sync，会话发出的commit指令后，需要等待LGWR将这个事务产生的redo 成功写入到磁盘之后，才可以继续进行后续的操作，这个等待事件就叫作log file sync。高log file sync等待事件的3个主要原因: ①高提交频率②缓慢的I/O子系统③过大的日志缓冲区_LOG_IO_SIZE。一般是提交频率太高，比如建议单笔提交为批量提交。
        3）gc buffer busy acquire/gc buffer busy release，出现GC事件排在TOP1,一般来说数据库请求并发分布在两台数据库服务器节点上，竞争部分共享资源时，2个节点会互相争抢对方缓存的最新版本的数据块，导致节点间大量传输数据块，数据库阻塞在Global Cache Busy。这种情况下建议检查数据库的服务模式是否合理，比如是负载均衡模式改为主备模式试试。4）resmgr:cpu quantum，这个是Resource Manager特性导致的等待事件，当resource manager控制CPU调度时，需要控制对应进程暂时不使用CPU而进程到内部运行队列中，以保证该进程对应的consumer group (消费组)没有消耗比指定resource manager指令更多的CPU。这种情况下建议检查数据库的CPU是否够用，检查CPU_COUNT或者performance的值是否过小。5）buffer busy waits，从本质上讲，这个等待事件的产生仅说明了一个会话在等待一个Buffer（数据块）。它常见于数据库中存在热块的时候，当多个用户频繁地读取或者修改同样的数据块时，这个等待事件就会产生。建议检查数据库的热点Segments by Buffer Busy Waits再进一步分析哪块有问题。6）enq: TX - row lock contention: TX等待分为4类，分别是:1. enq:TX - row lock contention;2. enq:TX - index contention;3. enq:TX - ITL;4. enq:TX - contention,前三种的含义比较明显，第4种是表示其它类型的transaction contention,即除了前三种之外的都包含在其中。建议关注Segment Statistics中的Segments by Row Lock Waits和Segments by ITL Waits。7）db file sequential read，当Oracle 需要每次I/O只读取单个数据块这样的操作时，会产生这个等待事件。最常见的情况有索引的访问（除IFFS外的方式），回滚操作，以ROWID的方式访问表中的数据，重建控制文件，对文件头做DUMP等。这个等待事件是由于糟糕的SQL使用了太多索引，读取了更多的块造成的。这个等待事件通常指单块读操作（例如索引的读取）。该值过大说明表的连接顺序很糟糕，或者使用了选择率不高的索引。建议检查SQL的索引使用情况，还有关联表的连接顺序。8）log file switch（checkpoint incomplete），当一个在线日志切换到下一个在线日志时，必须保证要切换到的在线日志上的记录的信息（比如一些脏数据块产生的redo log）被写到磁盘上（checkpoint），这样做的原因是，如果一个在线日志文件的信息被覆盖，而依赖这些redo 信息做恢复的数据块尚未被写到磁盘上（checkpoint），此时系统down掉的话，Oracle将没有办法进行实例恢复。如果系统中出现大量的log file switch（checkpoint incomplete）等待事件，原因可能是日志文件太小或者日志组太少，所以解决的方法是，建议增加日志文件的大小或者增加日志组的数量。9）reliable message，也就是说当跨实例发送消息时，发送者期望收到订阅者的回复信息，如果得不到可信回复，就会一直处于等待。等待以3秒为周期进行反复尝试，直到收到所有订阅者的回复或者被唤醒。这个一般是和result cache有关，建议去查一下result_cache_max_size参数，设置为0，禁用Result cache，貌似是11g的bug，生产上是禁用这个参数的。10）direct path read，这个等待事件发生在会话将数据块直接读取到PGA当中而不是SGA中的情况，这些被读取的数据通常是这个会话私有的数据，所以不需要放到SGA作为共享数据，因为这样做没有意义。这些数据通常是来自于临时段上的数据，比如一个会话中SQL的排序数据，并行执行过程中间产生的数据，以及Hash Join，merge join产生的排序数据，因为这些数据只对当前的会话的SQL操作有意义，所以不需要放到SGA当中。当发生direct path read等待事件时，意味着磁盘上有大量的临时数据产生，比如排序，并行执行等操作。或者意味着PGA中空闲空间不足，建议增加PGA的值。Host CPU是数据库服务器的CPU资源配置及使用情况，若%User+%System超过80%，说明服务器CPU出现了瓶颈，请关注。Instance CPU是本实例占的数据库CPU的使用情况，若%Busy CPU超过80%，也请关注。IO Profile是数据库IO负载情况，只用关注第一行即行，比如每秒读写次数（Read+Write Per Second）,每秒读次数（Read per Second）,每秒写次数（Write Per Second）。Memory Statistics是数据库的内存负载情况，第一行是本数据库内存容量，后面是数据库SGA和PGA的使用情况，及两者占总内存的使用率。如占到总内存的80%以上，需要关注。看数据库的关键参数是否设置合理，（Database Resource Limits、Cache Sizes、Shared Pool Statistics）看这里的三项就行了这里可以看SGA,PGA及shared_pool_size等参数的情况。Main Report本节重点关注：1）Report Summary2）Wait Events Statistics3）SQL Statistics4）Segment Statistics5）Initialization Parameters6）Active Session History (ASH) Report7）ADDM Reports

DB Name	DB Id	Unique Name	Role	Edition	Release	RAC	CDB
H01U08PF	1924087814	H01U08PF	PRIMARY	EE	19.0.0.0.0	YES	YES

Instance	Inst Num	Startup Time	User Name	System Data Visible
H01U08PF1	1	03-Feb-23 14:08	APPHIGH1	YES

Container DB Id	Container Name	Open Time
1924087814	WCMMNG00	03-Feb-23 14:10

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
racuat2f2ch01u08	Linux x86 64-bit	64	32	2	1258.02

	Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	560	08-Mar-23 14:58:53	6	3.3	2
End Snap:	561	08-Mar-23 15:13:45	13	11.6	2
Elapsed:		14.87 (mins)
DB Time:		21.49 (mins)

Report Summary

Top ADDM Findings by Average Active Sessions

Finding Name	Avg active sessions of the task	Percent active sessions of finding	Task Name	Begin Snap Time	End Snap Time
Top SQL Statements	1.45	81.97	ADDM:1924087814_1_561	08-Mar-23 14:58	08-Mar-23 15:13
Session Connect and Disconnect	1.45	3.60	ADDM:1924087814_1_561	08-Mar-23 14:58	08-Mar-23 15:13

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	1.5	44.5	0.00	0.00
DB CPU(s):	1.4	44.2	0.00	0.00
Background CPU(s):	0.0	0.0	0.00	0.00
Redo size (bytes):	5,276.6	162,298.8
Logical read (blocks):	239,008.3	7,351,466.4
Block changes:	23.2	712.5
Physical read (blocks):	0.1	4.0
Physical write (blocks):	0.0	0.0
Read IO requests:	0.1	4.0
Write IO requests:	0.0	0.0
Read IO (MB):	0.0	0.0
Write IO (MB):	0.0	0.0
IM scan rows:	0.0	0.0
Session Logical Read IM:	0.0	0.0
Global Cache blocks received:	1.3	39.9
Global Cache blocks served:	0.0	0.1
User calls:	1,185.5	36,464.7
Parses (SQL):	26.0	798.4
Hard parses (SQL):	0.0	0.4
SQL Work Area (MB):	17.1	525.3
Logons:	3.1	94.2
User logons:	3.0	93.7
Executes (SQL):	604.9	18,605.2
Rollbacks:	0.0	0.0
Transactions:	0.0

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Avg Wait	% DB time	Wait Class
DB CPU		1283.1		99.5
resmgr:cpu quantum	311	4.3	13.71ms	.3	Scheduler
PGA memory operation	148,923	1.8	11.76us	.1	Other
SQL*Net message to client	1,048,433	1.1	1.09us	.1	Network
log file sync	207	.9	4.55ms	.1	Commit
DLM cross inst call completion	5,410	.9	162.37us	.1	Other
db file sequential read	122	.2	1.25ms	.0	User I/O
control file sequential read	1,296	.1	103.96us	.0	System I/O
latch free	103	.1	765.74us	.0	Other
gc cr block 2-way	576	.1	121.66us	.0	Cluster

Wait Classes by Total Wait Time

Wait Class	Waits	Total Wait Time (sec)	Avg Wait Time	% DB time	Avg Active Sessions
DB CPU		1,283		99.5	1.4
Scheduler	311	4	13.71ms	.3	0.0
Other	161,260	3	18.03us	.2	0.0
Network	1,056,719	1	1.10us	.1	0.0
Commit	207	1	4.55ms	.1	0.0
User I/O	1,217	0	213.11us	.0	0.0
Cluster	1,541	0	112.84us	.0	0.0
System I/O	1,296	0	103.96us	.0	0.0
Concurrency	185	0	207.40us	.0	0.0
Configuration	2	0	2.31ms	.0	0.0
Application	2	0	183.00us	.0	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	1.6	1.6	0.0
Database Requests:	0.1	0.1	0.0
Optimized Requests:	0.0	0.0	0.0
Redo Requests:
Total (MB):	0.0	0.0	0.0
Database (MB):	0.0	0.0	0.0
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):
Database (blocks):	0.1	0.1	0.0
Via Buffer Cache (blocks):	0.1	0.1	0.0
Direct (blocks):	0.0	0.0	0.0

Database Resource Limits

	Begin	End
CPUs:	4	4
SGA Target:	8,388,608,000	8,388,608,000
PGA Target:	2,097,152,000	2,097,152,000
Memory Target	0	0

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Wait Statistics
• Undo Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Initialization Parameters
• Active Session History (ASH) Report
• ADDM Reports

Back to Top

---

本节不用格外关注，分析等待事件Foreground Events的时候可以结合这个一起来分析。
回到顶部

Wait Events Statistics

• Time Model Statistics
• Foreground Wait Class
• Foreground Wait Events
• Service Statistics
• Service Wait Class Stats
• Top Process Types by Wait Class
• Top Process Types by CPU Used

Back to Top

本节是数据库的时间模型，可以看一下，如果DB CPU排在第一位的话，那一般sql耗时最高。
回到顶部

Time Model Statistics

• DB Time represents total time in user calls
• DB CPU represents CPU time of foreground processes
• Total CPU Time represents foreground and background processes
• Statistics including the word "background" measure background process time, therefore do not contribute to the DB time statistic
• Ordered by % of DB time in descending order, followed by Statistic Name

Statistic Name	Time (s)	% of DB Time	% of Total CPU Time
DB CPU	1,283.08	99.52	100.00
sql execute elapsed time	1,191.61	92.42
connection management call elapsed time	46.38	3.60
PL/SQL execution elapsed time	2.34	0.18
parse time elapsed	1.69	0.13
hard parse elapsed time	0.29	0.02
hard parse (sharing criteria) elapsed time	0.01	0.00
repeated bind elapsed time	0.00	0.00
DB time	1,289.29
total CPU time	1,283.08

---

Back to Wait Events Statistics
Back to Top

此八节不用格外关注，分析等待事件Foreground Events的时候可以结合这些一起来分析。
（Foreground Wait Class、Foreground Wait Events、Background Wait Events、Wait Event Histogram、Wait Event Histogram (up to 64 us)、
Wait Event Histogram (up to 32 ms)）、Wait Event Histogram (up to 2 sec)、Wait Event Histogram (up to 2 min)、Wait Event Histogram (up to 1 hr)
回到顶部

Foreground Wait Class

• s - second, ms - millisecond, us - microsecond, ns - nanosecond
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 100.3% of Total DB time 1,289.29 (s)
• Total FG Wait Time: 9.79 (s) DB CPU time: 1,283.08 (s)

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait	%DB time
DB CPU			1,283		99.52
Scheduler	311	0	4	13.71ms	0.33
Other	158,100	2	3	17.92us	0.22
Network	1,051,291	0	1	1.11us	0.09
Commit	207	0	1	4.55ms	0.07
User I/O	1,196	0	0	214.81us	0.02
Cluster	1,284	0	0	122.91us	0.01
System I/O	1,296	0	0	103.96us	0.01
Concurrency	167	75	0	216.43us	0.00
Configuration	2	0	0	2.31ms	0.00
Application	2	0	0	183.00us	0.00

---

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond, us - microsecond, ns - nanosecond
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait	Waits /txn	% DB time
resmgr:cpu quantum	311		4	13.71ms	10.72	0.33
PGA memory operation	148,923		2	11.76us	5,135.28	0.14
SQL*Net message to client	1,048,433		1	1.09us	36,152.86	0.09
log file sync	207		1	4.55ms	7.14	0.07
DLM cross inst call completion	5,410	50	1	162.37us	186.55	0.07
db file sequential read	122		0	1.25ms	4.21	0.01
control file sequential read	1,296		0	103.96us	44.69	0.01
latch free	103		0	765.74us	3.55	0.01
gc cr block 2-way	576		0	121.66us	19.86	0.01
Sync ASM rebalance	5		0	11.63ms	0.17	0.00
Disk file operations I/O	713		0	78.19us	24.59	0.00
ASM file metadata operation	1		0	42.52ms	0.03	0.00
gc current block busy	342		0	119.44us	11.79	0.00
Disk file Mirror Read	307		0	122.48us	10.59	0.00
library cache lock	122	99	0	233.43us	4.21	0.00
IPC group service call	2,858		0	7.29us	98.55	0.00
gc cr block busy	34		0	559.32us	1.17	0.00
gc current block 2-way	152		0	90.34us	5.24	0.00
direct path read	18		0	579.06us	0.62	0.00
gc current grant busy	101		0	64.88us	3.48	0.00
PX Deq: reap credit	305	100	0	16.46us	10.52	0.00
PX Deq: Join ACK	12		0	403.50us	0.41	0.00
ges enter server mode	506		0	8.70us	17.45	0.00
log file switch completion	1		0	4.38ms	0.03	0.00
enq: TX - index contention	30		0	131.73us	1.03	0.00
gc current grant 2-way	56		0	63.77us	1.93	0.00
gc cr multi block mixed	14		0	189.43us	0.48	0.00
enq: WF - contention	18		0	121.17us	0.62	0.00
library cache load lock	2		0	734.00us	0.07	0.00
row cache lock	5	100	0	270.60us	0.17	0.00
ges resource directory to be unfrozen	2,714		0	498.16ns	93.59	0.00
enq: WT - contention	9		0	148.56us	0.31	0.00
enq: PS - contention	9		0	133.89us	0.31	0.00
SQL*Net message from client	1,047,688		4,734	4.52ms	36,127.17
PX Deq: Execution Msg	63		1	8.13ms	2.17
PX Deq: Execute Reply	35		0	4.49ms	1.21
PX Deq: Parse Reply	12		0	1.65ms	0.41
PX Deq Credit: send blkd	135		0	10.91us	4.66

---

Back to Wait Events Statistics
Back to Top

若本AWR报告是CDB级别的话，通过这两节可以看到哪个PDB比较消耗资源，并且可以看到是哪种类型的比较消耗资源，比如是读还是写，还是网络方面。
回到顶部

Service Statistics

• ordered by DB Time

Service Name	DB Time (s)	DB CPU (s)	Physical Reads (K)	Logical Reads (K)
WCMMNG00_S1	789	788	0	212,863
wcmmng00	501	496	0	330
WCMMNG00_S2	0	0	0	0

---

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 seconds

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
WCMMNG00_S1	10	0	5	0	0	0	1037854	1
wcmmng00	1164	0	161	0	0	0	14284	0

---

Back to Wait Events Statistics
Back to Top

Top Process Types by Wait Class,Top Process Types by CPU Used 此2节可以看到Oracle自身的哪个进程比较耗时，可以参考看一下。
回到顶部

Top Process Types by Wait Class

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Top Process Types by CPU Used

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

SQL Statistics按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，是给我们的一个调优指南。例如在一个系统中，
CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IO最多的SQL语句。
在AWR报告中，如果SQL显示不全，可使用报告中的SQL Id通过下面语句从数据库中查到：
eg: select SQL_TEXT from v$sqltext where SQL_ID='44g3f0b82000k' order by piece asc;
一般关注以下维度：
1）执行时间：Elapsed Time;
2）执行次数：Executions
3）CPU维度：CPU Time
4）IO维度：User I/O Wait Time和Physical Reads(UnOptimized)
回到顶部

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• 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

Back to Top

•Elapsed Time（S）:SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，
而是监控范围内SQL执行次数的总和时间。单位时间为秒。ElapsedTime= CPUTime+Wait Time.
•Executions: SQL语句在监控范围内的执行次数总计，一般每次交易SQL执行次数应不超过5-8次。
•Elapsed Time per Exec (s)：执行一次SQL的平均时间，单位时间为秒，时间过长时可能是出现了慢SQL。 
•%Total: 为SQL的Elapsed Time时间占数据库总时间的百分比。
•%CPU: 为SQL的Elapsed Time时间占数据库CPU总时间的百分比。
•%IO: 为SQL的Elapsed Time时间占数据库IO总时间的百分比。 
•SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。 
•SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是
有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
•PDB Name: 该SQL所在的PDB的名称。
•SQL Text: 简单的sql提示，详细的需要点击SQL ID。
本节需要关注：
1）执行时间Elapsed Time超过10ms的SQL，建议分析sql看否有优化空间;
2）执行次数Elapsed Time per Exec大于TPS的5倍,建议咨询开发从业务层面来分析是否合理
回到顶部

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
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 92.0% of Total DB Time (s): 1,289
• Captured PL/SQL account for 0.6% of Total DB Time (s): 1,289

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
261.67	950	0.28	20.30	98.90	0.00	dprdzbty156ux	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
237.23	253,185	0.00	18.40	95.43	0.00	5b64sq2g9zj1a	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
227.78	201	1.13	17.67	99.23	0.00	b2cxpv2ah9j3f	JDBC Thin Client	WCMMNG00	SELECT * FROM (SELECT user_nam...
133.61	999	0.13	10.36	98.96	0.00	3y2d7rgcy1959	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
128.78	252,577	0.00	9.99	93.50	0.00	gjq63nfm9bd6u	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
46.09	201	0.23	3.57	99.25	0.00	6fyytvcmzzaxq	JDBC Thin Client	WCMMNG00	select ( (select value from v$...
23.34	201	0.12	1.81	98.94	0.00	6tvb8fxmgrkst	JDBC Thin Client	WCMMNG00	SELECT value as table_scans FR...
23.22	201	0.12	1.80	99.01	0.00	33hkvdfmk3uh9	JDBC Thin Client	WCMMNG00	SELECT value as parse_count FR...
23.17	201	0.12	1.80	98.98	0.00	f424nh2d912f9	JDBC Thin Client	WCMMNG00	SELECT value as sorts FROM v$s...
22.99	201	0.11	1.78	99.01	0.00	b8unvt854zu6k	JDBC Thin Client	WCMMNG00	SELECT value as physical_reads...
22.97	201	0.11	1.78	99.03	0.00	10g91b26jxck2	JDBC Thin Client	WCMMNG00	SELECT name, value FROM v$syss...

---

Back to SQL Statistics
Back to Top

本节SQL按照CPU维度进行排序，如果数据库CPU有瓶颈的，建议关注这节。
回到顶部

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - CPU Time as a percentage of Total DB CPU
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 90.2% of Total CPU Time (s): 1,283
• Captured PL/SQL account for 0.6% of Total CPU Time (s): 1,283

CPU Time (s)	Executions	CPU per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
258.80	950	0.27	20.17	261.67	98.90	0.00	dprdzbty156ux	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
226.38	253,185	0.00	17.64	237.23	95.43	0.00	5b64sq2g9zj1a	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
226.02	201	1.12	17.62	227.78	99.23	0.00	b2cxpv2ah9j3f	JDBC Thin Client	WCMMNG00	SELECT * FROM (SELECT user_nam...
132.22	999	0.13	10.30	133.61	98.96	0.00	3y2d7rgcy1959	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
120.41	252,577	0.00	9.38	128.78	93.50	0.00	gjq63nfm9bd6u	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
45.74	201	0.23	3.56	46.09	99.25	0.00	6fyytvcmzzaxq	JDBC Thin Client	WCMMNG00	select ( (select value from v$...
23.09	201	0.11	1.80	23.34	98.94	0.00	6tvb8fxmgrkst	JDBC Thin Client	WCMMNG00	SELECT value as table_scans FR...
22.99	201	0.11	1.79	23.22	99.01	0.00	33hkvdfmk3uh9	JDBC Thin Client	WCMMNG00	SELECT value as parse_count FR...
22.93	201	0.11	1.79	23.17	98.98	0.00	f424nh2d912f9	JDBC Thin Client	WCMMNG00	SELECT value as sorts FROM v$s...
22.77	201	0.11	1.77	22.99	99.01	0.00	b8unvt854zu6k	JDBC Thin Client	WCMMNG00	SELECT value as physical_reads...
22.75	201	0.11	1.77	22.97	99.03	0.00	10g91b26jxck2	JDBC Thin Client	WCMMNG00	SELECT name, value FROM v$syss...

---

Back to SQL Statistics
Back to Top

本节SQL按照I/O维度进行排序，如果数据库I/O有瓶颈的，建议关注这节。
回到顶部

SQL ordered by User I/O Wait Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - User I/O Time as a percentage of Total User I/O Wait time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 26.6% of Total User I/O Wait Time (s): 0
• Captured PL/SQL account for 42.0% of Total User I/O Wait Time (s): 0

User I/O Time (s)	Executions	UIO per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
0.11	1	0.11	42.03	7.87	96.52	1.39	a0t0a12gyqm20	JDBC Thin Client	WCMMNG00	call DBMS_WORKLOAD_REPOSITORY....
0.03	2,713	0.00	11.92	0.59	94.01	5.25	865qwpcdyggkk		WCMMNG00	select spare6 from user$ where...
0.03	2	0.01	10.60	1.49	94.97	1.85	9rrvf7y1ftb98	JDBC Thin Client	WCMMNG00	select OUTPUT from table(dbms_...
0.01	103	0.00	2.95	0.02	68.42	31.99	f7xnvp44nukp7		WCMMNG00	insert into wrm$_snapshot_deta...
0.00	2,708	0.00	0.73	3.16	96.63	0.06	9zg9qd9bm4spu	JDBC Thin Client	WCMMNG00	update user$ set spare6=DECODE...
0.00	253,185	0.00	0.15	237.23	95.43	0.00	5b64sq2g9zj1a	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
0.00	950	0.00	0.15	261.67	98.90	0.00	dprdzbty156ux	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
0.00	999	0.00	0.13	133.61	98.96	0.00	3y2d7rgcy1959	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
0.00	114	0.00	0.00	0.01	98.68	0.07	3dbzmtf9ahvzt		WCMMNG00	merge /* KSXM:OPTIM_DML_INF */...
0.00	4	0.00	0.00	0.00	100.00	0.64	9tgj4g8y4rwy8		WCMMNG00	select type#, blocks, extents,...

---

Back to SQL Statistics
Back to Top

本节SQL按照Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。
顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。
因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。
在这里的Buffer Gets是一个累积值，所以这个值大并不一定意味着这条语句的性能存在问题。
通常我们可以通过对比该条语句的Buffer Gets和physical reads值，如果这两个比较接近，肯定这条语句是存在
问题的，可以通过执行计划来分析，为什么physical reads的值如此之高。另外，也可以关注gets per exec的值，
这个值如果太大，表明这条语句可能使用了一个比较差的索引或者使用了不当的表连接。另外说明一点：大量的逻辑读
往往伴随着较高的CPU消耗。所以很多时候我们看到的系统CPU将近100％的时候，很多时候就是SQL语句造成的，这时候
我们可以分析一下这里逻辑读大的SQL。select * from	(select substr(sql_text,1,40) sql,buffer_gets,
executions, buffer_gets/executions "Gets/Exec",hash_value,address from v$sqlarea where buffer_gets > 0 and executions>0 order by buffer_gets desc)
where rownum <= 10 ;
回到顶部

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 as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 213,192,525
• Captured SQL account for 99.9% of Total

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
160,800,421	253,185	635.11	75.42	237.23	95.4	0	5b64sq2g9zj1a	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
21,845,724	950	22,995.50	10.25	261.67	98.9	0	dprdzbty156ux	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
15,147,386	999	15,162.55	7.11	133.61	99	0	3y2d7rgcy1959	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
14,810,936	252,577	58.64	6.95	128.78	93.5	0	gjq63nfm9bd6u	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
168,764	2,722	62.00	0.08	3.77	68.1	0	5dqz0hqtp9fru		WCMMNG00	select /*+ connect_by_filterin...
102,448	2	51,224.00	0.05	1.49	95	1.8	9rrvf7y1ftb98	JDBC Thin Client	WCMMNG00	select OUTPUT from table(dbms_...
48,340	990	48.83	0.02	0.34	21.7	0	1ss9v49tfudvy	JDBC Thin Client	WCMMNG00	SELECT bus_type, apply_req_id,...
19,352	1	19,352.00	0.01	7.87	96.5	1.4	a0t0a12gyqm20	JDBC Thin Client	WCMMNG00	call DBMS_WORKLOAD_REPOSITORY....
13,610	2,722	5.00	0.01	0.63	96.4	0	0k8522rmdzg4k		WCMMNG00	select privilege# from sysauth...
10,890	2,708	4.02	0.01	3.16	96.6	.1	9zg9qd9bm4spu	JDBC Thin Client	WCMMNG00	update user$ set spare6=DECODE...

---

Back to SQL Statistics
Back to Top

本节SQL按照Reads维度进行排序，如果数据库I/O有瓶颈的，建议关注这节。
和上一节的SQL ordered by Gets一起结合分析，详细分析见上节。
回到顶部

SQL ordered by Reads

• %Total - Physical Reads as a percentage of Total Disk Reads
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Disk Reads: 116
• Captured SQL account for 25.0% of Total

Physical Reads	Executions	Reads per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
79	1	79.00	68.10	7.87	96.52	1.39	a0t0a12gyqm20	JDBC Thin Client	WCMMNG00	call DBMS_WORKLOAD_REPOSITORY....
18	2	9.00	15.52	1.49	94.97	1.85	9rrvf7y1ftb98	JDBC Thin Client	WCMMNG00	select OUTPUT from table(dbms_...
11	103	0.11	9.48	0.02	68.42	31.99	f7xnvp44nukp7		WCMMNG00	insert into wrm$_snapshot_deta...
0	201	0.00	0.00	0.27	55.51	0.00	01davw5xnh2vg	JDBC Thin Client	WCMMNG00	SELECT * FROM (SELECT event, t...
0	201	0.00	0.00	0.01	85.42	0.00	0b639nx4zdzxr		WCMMNG00	select domain# from sys.im_dom...
0	2,722	0.00	0.00	0.63	96.43	0.00	0k8522rmdzg4k		WCMMNG00	select privilege# from sysauth...
0	5	0.00	0.00	0.00	41.43	0.00	0kkhhb2w93cx0		WCMMNG00	update seg$ set type#=:4, bloc...
0	201	0.00	0.00	22.97	99.03	0.00	10g91b26jxck2	JDBC Thin Client	WCMMNG00	SELECT name, value FROM v$syss...
0	25	0.00	0.00	3.97	99.33	0.00	1238ttd8y24zw	JDBC Thin Client	WCMMNG00	select a.username, a.lockwait,...
0	990	0.00	0.00	0.34	21.65	0.00	1ss9v49tfudvy	JDBC Thin Client	WCMMNG00	SELECT bus_type, apply_req_id,...

---

Back to SQL Statistics
Back to Top

本节SQL按照物理读维度进行排序。这显示引起大部分对这个系统进行读取活动的SQL，即物理I/O。
当我们的系统如果存在I/O瓶颈时，需要关注这里I/O操作比较多的语句。select * from (select substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions, "Reads/Exec",hash_value,address
from v$sqlarea where disk_reads > 0 and executions >0 order by disk_reads desc) where rownum <= 10;
回到顶部

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqs - (Optimized Read Reqs - Cell Flash Cache Read Hits for Controlfile)
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 116
• Captured SQL account for 44.8% of Total
• Total UnOptimized Read Requests: 116
• Captured SQL account for 44.8% of Total
• Total Optimized Read Requests: 1
• Captured SQL account for 0.0% of Total

UnOptimized Read Reqs	Physical Read Reqs	Executions	UnOptimized Reqs per Exec	%Opt	%Total	SQL Id	SQL Module	PDB Name	SQL Text
1,215	1,215	1	1,215.00	0.00	1047.41	a0t0a12gyqm20	JDBC Thin Client	WCMMNG00	call DBMS_WORKLOAD_REPOSITORY....
52	52	2	26.00	0.00	44.83	9rrvf7y1ftb98	JDBC Thin Client	WCMMNG00	select OUTPUT from table(dbms_...
0	0	201	0.00		0.00	01davw5xnh2vg	JDBC Thin Client	WCMMNG00	SELECT * FROM (SELECT event, t...
0	0	201	0.00		0.00	0b639nx4zdzxr		WCMMNG00	select domain# from sys.im_dom...
0	0	2,722	0.00		0.00	0k8522rmdzg4k		WCMMNG00	select privilege# from sysauth...
0	0	5	0.00		0.00	0kkhhb2w93cx0		WCMMNG00	update seg$ set type#=:4, bloc...
0	0	201	0.00		0.00	10g91b26jxck2	JDBC Thin Client	WCMMNG00	SELECT name, value FROM v$syss...
0	0	25	0.00		0.00	1238ttd8y24zw	JDBC Thin Client	WCMMNG00	select a.username, a.lockwait,...
0	0	990	0.00		0.00	1ss9v49tfudvy	JDBC Thin Client	WCMMNG00	SELECT bus_type, apply_req_id,...
0	0	3	0.00		0.00	2ajc7pwz9jsx3		WCMMNG00	select max(scn) from smon_scn_...

---

Back to SQL Statistics
Back to Top

本节SQL按照执行次数维度进行排序，可以和上一节的SQL ordered by Elapsed Time一起结合分析，
详细分析见上节SQL ordered by Elapsed Time。建议执行次数Elapsed Time per Exec不大于TPS的5倍,
如是建议咨询开发从业务层面来分析是否合理，为了隔离某些频繁执行的查询，以观察是否有某些更改逻辑的方法以避免
必须如此频繁的执行这些查询，这可能是很有用的。或许一个查询正在一个循环的内部执行，而且它可能在循环的外部执行一次，
可以设计简单的算法更改以减少必须执行这个查询的次数。即使它运行的飞快，任何被执行几百万次的操作都将会耗尽大量的时间。
select * from (select substr(sql_text,1,40) sql, executions, rows_processed, rows_processed/executions
"Rows/Exec",hash_value,address from v$sqlarea where executions > 0 order by executions desc) where rownum<=10
回到顶部

SQL ordered by Executions

• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Executions: 539,551
• Captured SQL account for 98.0% of Total

Executions	Rows Processed	Rows per Exec	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
253,185	28,230	0.11	237.23	95.4	0	5b64sq2g9zj1a	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
252,577	27,840	0.11	128.78	93.5	0	gjq63nfm9bd6u	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
2,722	2,722	1.00	0.63	96.4	0	0k8522rmdzg4k		WCMMNG00	select privilege# from sysauth...
2,722	114,324	42.00	3.77	68.1	0	5dqz0hqtp9fru		WCMMNG00	select /*+ connect_by_filterin...
2,722	6	0.00	0.28	62.7	0	d9q2q82g1tuzx		WCMMNG00	select decode(upper(failover_m...
2,722	2,722	1.00	0.81	97.7	0	f0h5rpzmhju11		WCMMNG00	select SYS_CONTEXT('USERENV', ...
2,713	2,713	1.00	0.59	94	5.2	865qwpcdyggkk		WCMMNG00	select spare6 from user$ where...
2,708	2,708	1.00	3.16	96.6	.1	9zg9qd9bm4spu	JDBC Thin Client	WCMMNG00	update user$ set spare6=DECODE...
999	29,970	30.00	133.61	99	0	3y2d7rgcy1959	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
990	29,700	30.00	0.34	21.7	0	1ss9v49tfudvy	JDBC Thin Client	WCMMNG00	SELECT bus_type, apply_req_id,...

---

Back to SQL Statistics
Back to Top

本节SQL按照分析调用维度进行排序。在这一部分，主要显示PARSE与EXECUTIONS的对比情况。
如果PARSE/EXECUTIONS>1，往往说明这个语句可能存在问题：没有使用绑定变量，共享池设置太小，
cursor_sharing被设置为exact，没有设置session_cached_cursors等等问题。
select * from (select substr(sql_text,1,40) sql, parse_calls, 
executions, hash_value,address from v$sqlarea where parse_calls > 0
order by parse_calls desc) where rownum <= 10
回到顶部

SQL ordered by Parse Calls

• Total Parse Calls: 23,154
• Captured SQL account for 86.9% of Total

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	PDB Name	SQL Text
2,722	2,722	11.76	0k8522rmdzg4k		WCMMNG00	select privilege# from sysauth...
2,722	2,722	11.76	5dqz0hqtp9fru		WCMMNG00	select /*+ connect_by_filterin...
2,722	2,722	11.76	d9q2q82g1tuzx		WCMMNG00	select decode(upper(failover_m...
2,722	2,722	11.76	f0h5rpzmhju11		WCMMNG00	select SYS_CONTEXT('USERENV', ...
2,713	2,713	11.72	865qwpcdyggkk		WCMMNG00	select spare6 from user$ where...
2,708	2,708	11.70	9zg9qd9bm4spu	JDBC Thin Client	WCMMNG00	update user$ set spare6=DECODE...
561	561	2.42	2p9fv35c7zxtg		WCMMNG00	select /* KSXM:LOAD_DML_INF */...
201	201	0.87	01davw5xnh2vg	JDBC Thin Client	WCMMNG00	SELECT * FROM (SELECT event, t...
201	201	0.87	0b639nx4zdzxr		WCMMNG00	select domain# from sys.im_dom...
201	201	0.87	10g91b26jxck2	JDBC Thin Client	WCMMNG00	SELECT name, value FROM v$syss...

---

Back to SQL Statistics
Back to Top

本节SQL按照共享内面维度进行排序。在这一部分，主要是针对shared memory占用的情况进行排序。
select * from
(select substr(sql_text,1,40) sql, sharable_mem,
executions, hash_value,address
from v$sqlarea where sharable_mem > 1048576
回到顶部

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b)	Executions	% Total	SQL Id	SQL Module	PDB Name	SQL Text
18,529,183	1	0.09	586577qpbkgnk		WCMMNG00	select 1 from DBA_SCHEDULER_JO...
10,137,516	0	0.05	3kqrku32p6sfn		WCMMNG00	MERGE /*+ OPT_PARAM('_parallel...
6,101,088	561	0.03	2p9fv35c7zxtg		WCMMNG00	select /* KSXM:LOAD_DML_INF */...
5,706,708	114	0.03	3dbzmtf9ahvzt		WCMMNG00	merge /* KSXM:OPTIM_DML_INF */...
3,363,635	5	0.02	0kkhhb2w93cx0		WCMMNG00	update seg$ set type#=:4, bloc...
2,721,979	4	0.01	9tgj4g8y4rwy8		WCMMNG00	select type#, blocks, extents,...
2,206,979	3	0.01	2ajc7pwz9jsx3		WCMMNG00	select max(scn) from smon_scn_...
1,936,880	18	0.01	6n2qqv1brfhpp		WCMMNG00	select ind.type#, ind.property...
1,786,349	1	0.01	fy8bgmw8b2swb		WCMMNG00	select a.dbid, a.status_flag f...
1,166,704	3	0.01	76cckj4yysvua		WCMMNG00	select time_mp, scn, num_mappi...

---

Back to SQL Statistics
Back to Top

本节SQL按照多版本维度进行排序。在这一部分，主要是针对SQL语句的多版本进行排序。相同的SQL文本，但是不同属性，
比如对象owner不同，会话优化模式不同、类型不同、长度不同和绑定变量不同等等的语句，他们是不能共享的，
所以在缓存中会存在多个不同的版本。这当然就造成了资源上的更多的消耗
回到顶部

SQL ordered by Version Count

• Only Statements with Version Count greater than 20 are displayed

Version Count	Executions	SQL Id	SQL Module	PDB Name	SQL Text
49	5	0kkhhb2w93cx0		WCMMNG00	update seg$ set type#=:4, bloc...
45	4	9tgj4g8y4rwy8		WCMMNG00	select type#, blocks, extents,...
34	3	2ajc7pwz9jsx3		WCMMNG00	select max(scn) from smon_scn_...
34	1	fy8bgmw8b2swb		WCMMNG00	select a.dbid, a.status_flag f...
31	18	6n2qqv1brfhpp		WCMMNG00	select ind.type#, ind.property...

---

Back to SQL Statistics
Back to Top

本节SQL按照集群等待时间维度进行排序，如果数据库等待事件TOP5有关集群的等待事件的，建议关注这节。
回到顶部

SQL ordered by Cluster Wait Time

• %Total - Cluster Time as a percentage of Total Cluster Wait Time
• %Clu - Cluster Time as a percentage of Elapsed Time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Only SQL with Cluster Wait Time > .005 seconds is reported
• Total Cluster Wait Time (s): 0
• Captured SQL account for 65.3% of Total

Cluster Wait Time (s)	Executions	%Total	Elapsed Time(s)	%Clu	%CPU	%IO	SQL Id	SQL Module	PDB Name	SQL Text
0.05	2	29.21	1.49	3.42	94.97	1.85	9rrvf7y1ftb98	JDBC Thin Client	WCMMNG00	select OUTPUT from table(dbms_...
0.04	1	24.77	7.87	0.55	96.52	1.39	a0t0a12gyqm20	JDBC Thin Client	WCMMNG00	call DBMS_WORKLOAD_REPOSITORY....
0.03	2,713	15.23	0.59	4.50	94.01	5.25	865qwpcdyggkk		WCMMNG00	select spare6 from user$ where...
0.02	2,708	11.05	3.16	0.61	96.63	0.06	9zg9qd9bm4spu	JDBC Thin Client	WCMMNG00	update user$ set spare6=DECODE...
0.01	950	3.40	261.67	0.00	98.90	0.00	dprdzbty156ux	JDBC Thin Client	WCMMNG00	SELECT info.CARD_NUMBER, info....
0.01	103	2.89	0.02	21.03	68.42	31.99	f7xnvp44nukp7		WCMMNG00	insert into wrm$_snapshot_deta...

---

Back to SQL Statistics
Back to Top

本节是根据SQL_ID可以从前面的链接到这里查看完整的SQL，分析SQL需要用到完整的SQL信息。
回到顶部

Complete List of SQL Text

SQL Id	SQL Text
01davw5xnh2vg	SELECT * FROM (SELECT event, total_waits, time_waited, average_wait, wait_class FROM v$system_event WHERE wait_class <> 'Idle' ORDER BY time_waited DESC ) WHERE rownum < 6
0b639nx4zdzxr	select domain# from sys.im_domain$ where objn = :1 and col# = :2
0k8522rmdzg4k	select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
0kkhhb2w93cx0	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, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
10g91b26jxck2	SELECT name, value FROM v$sysstat WHERE name='execute count'
1238ttd8y24zw	select a.username, a.lockwait, a.status, a.machine, a.program from v$session a, v$locked_object b where a.SQL_EXEC_START > SYSDATE-1 and a.sid = b.session_id
1ss9v49tfudvy	SELECT bus_type, apply_req_id, fill_type, card_number, client_id, card_internal_nbr, verify_date, verify_teller1_type, verify_teller1, verify_teller1_nam, verfiy_teller1_tel, verify_teller2_type, verify_teller2, verify_teller2_nam, verify_teller2_tel, verify_flag, tel_check, dlg_type, dlg_rst, pic_id, spec_code, tech_business_id, tech_trace_id, tech_db_name, tech_mnt_time, tech_version, tech_resv FROM WCM_INFO_VERIFY_T WHERE (CARD_NUMBER IN (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 ))
2ajc7pwz9jsx3	select max(scn) from smon_scn_time
2p9fv35c7zxtg	select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m) */ nvl(m.inserts, 0) ins, nvl(m.updates, 0) upd, nvl(m.deletes, 0) del, nvl(m.drop_segments, 0) dropseg, nvl(m.flags, 0) flags, nvl(rowcnt, 0) rowcnt, o.pobjn pobjn from (select :objn objn, 0 pobjn, rowcnt from tab$ t where t.obj# = :objn union all select :objn objn, tp.bo# pobjn, rowcnt from tabpart$ tp where tp.obj# = :objn union all select :objn objn, tcp.bo# pobjn, rowcnt from tabcompart$ tcp where tcp.obj# = :objn union all select :objn objn, tsp.pobj# pobjn, rowcnt from tabsubpart$ tsp where tsp.obj# = :objn) o, sys.mon_mods_all$ m where o.objn = m.obj#(+) and rownum < 2
33hkvdfmk3uh9	SELECT value as parse_count FROM v$sysstat WHERE name = 'parse count (hard)'
3dbzmtf9ahvzt	merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m using dual on (m.obj# = :objn and (:pobjn = 0 or m.obj# = :pobjn or m.obj# in (select obj# from tabpart$ where bo# = :pobjn and obj# = :objn union all select obj# from tabcompart$ where bo# = :pobjn and obj# = :objn union all select tsp.obj# from tabsubpart$ tsp, tabcompart$ tcp where tsp.pobj# = tcp.obj# and (tcp.bo# = :pobjn or tcp.obj# = :pobjn) and tsp.obj# = :objn))) when matched then update set inserts = decode(:reset, 1, 0, inserts) + :ins, updates = decode(:reset, 1, 0, updates) + :upd, deletes = decode(:reset, 1, 0, deletes) + :del, flags = decode(:reset, 1, 0, flags - bitand(flags, :flag)) + :flag, drop_segments = decode(:reset, 1, 0, drop_segments) + :dropseg, timestamp = :time when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (:objn, :ins, :upd, :del, :time, :flag, :dropseg) where :ins != 0 or :upd != 0 or :del != 0 or :flag != 0 or :dropseg !=0
3kqrku32p6sfn	MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME, ROUND(MAX(S.DELTA_READ_IO_BYTES/S.DELTA_TIME), 3) SCANRATE FROM GV$ACTIVE_SESSION_HISTORY S, GV$SQL_PLAN P, OBJ$ O, USER$ U WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQL_ID AND S.SQL_PLAN_HASH_VALUE = P.PLAN_HASH_VALUE AND S.SQL_PLAN_LINE_ID = P.ID AND P.OBJECT_NAME = O.NAME AND (:B4 IS NULL OR O.OBJ# = :B4 ) AND O.OWNER# = U.USER# AND P.OBJECT_OWNER = U.NAME AND O.TYPE# = 2 AND S.CON_ID = SYS_CONTEXT('userenv', 'con_id') AND P.CON_ID = SYS_CONTEXT('userenv', 'con_id') AND S.SQL_PLAN_OPERATION = 'TABLE ACCESS' AND P.OPERATION = 'TABLE ACCESS' AND S.DELTA_TIME > 0 AND S.DELTA_READ_IO_BYTES > 0 AND FROM_TZ(S.SAMPLE_TIME, :B3 ) > :B2 AND (:B1 = 'ON' OR (:B1 = 'HADOOP_ONLY' AND EXISTS ( SELECT NULL FROM SYS.EXTERNAL_TAB$ ET WHERE ET.OBJ# = O.OBJ# AND TYPE$ IN ('ORACLE_HIVE', 'ORACLE_HDFS', 'ORACLE_BIGDATA')))) GROUP BY O.OBJ#) C WHERE NOT EXISTS (SELECT NULL FROM OPTSTAT_USER_PREFS$ PR WHERE PNAME = 'SCAN_RATE' AND C.OBJ# = PR.OBJ# AND (PR.SPARE1 IS NULL OR C.SCANRATE <= TO_NUMBER(PR.VALCHAR))) ) SRC ON (D.OBJ#=SRC.OBJ# AND D.PNAME = 'SCAN_RATE') WHEN MATCHED THEN UPDATE SET D.VALCHAR= TO_CHAR(SRC.SCANRATE), D.CHGTIME=SRC.CHGTIME, D.SPARE1=1 WHEN NOT MATCHED THEN INSERT (OBJ#, PNAME, VALCHAR, CHGTIME, SPARE1) VALUES(SRC.OBJ#, 'SCAN_RATE', TO_CHAR(SRC.SCANRATE), SRC.CHGTIME, 1)
3y2d7rgcy1959	SELECT info.CARD_NUMBER, info.CARD_NAME, info.CARD_CODE, info.OPEN_BRANCH, info.OPEN_BANK, 'P' as OPR_RESULT, info.ACTIVE_FLAG, info.OPEN_DATE, info.EFFECT_DATE, info.EXPIRE_DATE, info.CLIENT_ID, info.CARD_INTERNAL_NBR, info.OPEN_OPERATE_TELLER, info.OPEN_AUTH_TELLER FROM WCM_MNG_MAININFO_T info WHERE ( (info.OPEN_DATE = :1 and info.OPEN_BRANCH = :2 and info.CARD_NUMBER > :3 ) or (info.OPEN_DATE = :4 and info.OPEN_BRANCH > :5 ) or info.OPEN_DATE > :6 ) and info.OPEN_BANK = :7 and info.OPEN_DATE >= :8 and info.OPEN_DATE <= :9 and info.CARD_CODE in ( :10 ) and info.ACTIVE_FLAG='Y' ORDER BY info.OPEN_BANK, info.OPEN_DATE, info.OPEN_BRANCH, info.CARD_NUMBER FETCH FIRST :11 ROWS ONLY
586577qpbkgnk	select 1 from DBA_SCHEDULER_JOBS where JOB_NAME like 'KWQICPOSTMSGDEL_1_%' and JOB_ACTION = 'DBMS_AQADM_SYS.REMOVE_ORPHMSGS'
5b64sq2g9zj1a	SELECT info.CARD_NUMBER, info.CARD_NAME, info.CARD_CODE, info.OPEN_BRANCH, info.OPEN_BANK, audi.OPR_RESULT, info.ACTIVE_FLAG, info.OPEN_DATE, info.EFFECT_DATE, info.EXPIRE_DATE, info.CLIENT_ID, info.CARD_INTERNAL_NBR, info.OPEN_OPERATE_TELLER, info.OPEN_AUTH_TELLER FROM WCM_MNG_MAININFO_T info LEFT JOIN WCM_APPLY_VERIFY_AUDIT_T audi on audi.APPLY_REQ_ID=info.CARD_NUMBER and audi.AUDI_CODE='OPENCARDAUDIT' and audi.AUDI_DESC='开户审核标记' WHERE info.OPEN_BANK = :1 and info.OPEN_DATE >= :2 and info.OPEN_DATE <= :3 and info.CARD_CODE in ( :4 ) and info.ACTIVE_FLAG='N' ORDER BY info.OPEN_BANK, info.OPEN_DATE, info.OPEN_BRANCH, info.CARD_NUMBER FETCH FIRST :5 ROWS ONLY
5dqz0hqtp9fru	select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
6fyytvcmzzaxq	select ( (select value from v$sysstat where name='user commits' )+ (select value from v$sysstat where name='user rollbacks' )) as value from dual
6n2qqv1brfhpp	select ind.type#, ind.property, base_obj.obj#, base_owner.name, base_obj.name from sys.ind$ ind, sys.user$ base_owner, sys.obj$ base_obj where ind.obj# = :oid and ind.dataobj# = :doid and ind.bo# = base_obj.obj# and base_obj.owner# = base_owner.user#
6tvb8fxmgrkst	SELECT value as table_scans FROM v$sysstat WHERE name = 'table scans (long tables)'
76cckj4yysvua	select time_mp, scn, num_mappings, tim_scn_map from smon_scn_time where scn = (select max(scn) from smon_scn_time where scn <= :1)
865qwpcdyggkk	select spare6 from user$ where user#=:1
9rrvf7y1ftb98	select OUTPUT from table(dbms_workload_repository.awr_report_html(:1 , :2 , :3 , :4 , 0))
9tgj4g8y4rwy8	select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
9zg9qd9bm4spu	update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
a0t0a12gyqm20	call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
b2cxpv2ah9j3f	SELECT * FROM (SELECT user_name, sql_id, count(1) as num from v$open_cursor where user_name is NOT NULL group by user_name, sql_id ORDER BY num DESC) WHERE rownum<=3
b8unvt854zu6k	SELECT value as physical_reads FROM v$sysstat WHERE name = 'physical reads'
d9q2q82g1tuzx	select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
dprdzbty156ux	SELECT info.CARD_NUMBER, info.CARD_NAME, info.CARD_CODE, info.OPEN_BRANCH, info.OPEN_BANK, audi.OPR_RESULT, info.ACTIVE_FLAG, info.OPEN_DATE, info.EFFECT_DATE, info.EXPIRE_DATE, info.CLIENT_ID, info.CARD_INTERNAL_NBR, info.OPEN_OPERATE_TELLER, info.OPEN_AUTH_TELLER FROM WCM_MNG_MAININFO_T info LEFT JOIN WCM_APPLY_VERIFY_AUDIT_T audi on audi.APPLY_REQ_ID=info.CARD_NUMBER and audi.AUDI_CODE='OPENCARDAUDIT' and audi.AUDI_DESC='开户审核标记' WHERE ( (info.OPEN_DATE = :1 and info.OPEN_BRANCH = :2 and info.CARD_NUMBER > :3 ) or (info.OPEN_DATE = :4 and info.OPEN_BRANCH > :5 ) or info.OPEN_DATE > :6 ) and info.OPEN_BANK = :7 and info.OPEN_DATE >= :8 and info.OPEN_DATE <= :9 and info.CARD_CODE in ( :10 ) and info.ACTIVE_FLAG='N' ORDER BY info.OPEN_BANK, info.OPEN_DATE, info.OPEN_BRANCH, info.CARD_NUMBER FETCH FIRST :11 ROWS ONLY
f0h5rpzmhju11	select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
f424nh2d912f9	SELECT value as sorts FROM v$sysstat WHERE name = 'sorts (disk)'
f7xnvp44nukp7	insert into wrm$_snapshot_details (snap_id, dbid, instance_number, table_id, begin_time, end_time, step_id) values (:snap_id, :dbid, :instance_number, :bind1, :bind2, :bind3, :step_id)
fy8bgmw8b2swb	select a.dbid, a.status_flag from wrm$_wr_control a, (select distinct dbid from wrm$_database_instance) b where a.dbid = b.dbid
gjq63nfm9bd6u	SELECT info.CARD_NUMBER, info.CARD_NAME, info.CARD_CODE, info.OPEN_BRANCH, info.OPEN_BANK, 'P' as OPR_RESULT, info.ACTIVE_FLAG, info.OPEN_DATE, info.EFFECT_DATE, info.EXPIRE_DATE, info.CLIENT_ID, info.CARD_INTERNAL_NBR, info.OPEN_OPERATE_TELLER, info.OPEN_AUTH_TELLER FROM WCM_MNG_MAININFO_T info WHERE info.OPEN_BANK = :1 and info.OPEN_DATE >= :2 and info.OPEN_DATE <= :3 and info.CARD_CODE in ( :4 ) and info.ACTIVE_FLAG='Y' ORDER BY info.OPEN_BANK, info.OPEN_DATE, info.OPEN_BRANCH, info.CARD_NUMBER FETCH FIRST :5 ROWS ONLY

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Key Instance Activity Stats
• Instance Activity Stats
• Instance Activity Stats - Absolute Values

Back to Top

Key Instance Activity Stats

• Ordered by statistic name

Statistic	Total	per Second	per Trans
db block changes	20,663	23.17	712.52
execute count	539,551	604.89	18,605.21
gc cr block receive time	3	0.00	0.10
gc cr blocks received	646	0.72	22.28
gc current block receive time	3	0.00	0.10
gc current blocks received	512	0.57	17.66
logons cumulative	2,733	3.06	94.24
opened cursors cumulative	23,562	26.42	812.48
parse count (total)	23,154	25.96	798.41
parse time elapsed	149	0.17	5.14
physical reads	116	0.13	4.00
physical writes	0	0.00	0.00
redo size	4,706,664	5,276.60	162,298.76
session cursor cache hits	642	0.72	22.14
session logical reads	213,192,525	239,008.29	7,351,466.38
user calls	1,057,477	1,185.53	36,464.72
user commits	28	0.03	0.97
user rollbacks	1	0.00	0.03
workarea executions - multipass	0	0.00	0.00
workarea executions - onepass	0	0.00	0.00
workarea executions - optimal	34,393	38.56	1,185.97

---

Back to Instance Activity Statistics
Back to Top

本节是实例活动统计。有三个方面需要关注
1）由consistent gets，db block gets和physical reads这三个值，也可以计算得到buffer hit ratio，计算的公式如下：
buffer hit ratio = 100*(1-physical reads /(consistent gets+ db block gets))；
2）dirty buffers inspected，脏数据从LRU列表中老化，如果dirty buffers inspected这个值大于0，就需要考虑增加DBWRs；
3）free buffer inspected这个值包含dirty，pinned，busy的buffer区域，
如果free buffer inspected - dirty buffers inspected - buffer is pinned count的值还是比较大，表明不能被重用的内存块比较多，
这将导致latch争用，需要增大buffer cache；
4）sorts(disk)磁盘排序一般不能超过5％。如果超过5％，需要设置参数PGA_AGGREGATE_TARGET或者 SORT_AREA_SIZE，注意，
这里SORT_AREA_SIZE是分配给每个用户的，PGA_AGGREGATE_TARGET则是针对所有的session的一个总数设置；
5）table fetch by rowid通过索引或者where rowid=语句来取得的行数，这个值越大越好；
6）table fetch continued row这是发生行迁移的行。当行迁移的情况比较严重时，需要对这部分进行优化。
检查行迁移的方法：
a)	运行$ORACLE_HOME/rdbms/admin/utlchain.sql
b)	analyze table table_name list chained rows into CHAINED_ROWS
c)	select * from CHAINED_ROWS where table_name='table_name';
清除的方法：
方法1：create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows);Delete from table_name where rowed in (select head_rowid from chained_rows);Insert into table_name select * from table_name_tmp;
方法2：create table table_name_tmp select * from table_name ;
truncate table table_name
insert into table_name select * from table_name_tmp
方法3：用exp工具导出表，然后删除这个表，最后用imp工具导入这表
方法4：alter table table_name move tablespace tablespace_name，然后再重新表的索引
上面的4种方法可以用以消除已经存在的行迁移现象，但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致，所以需要调整PCT_FREE参数的值
7）table scans (long tables)就是表的大小超过buffer buffer* _SMALL_TABLE_THRESHOLD的表。如果一个数据库的大表扫描过多，
那么db file scattered read等待事件可能同样非常显著。如果table scans (long tables)的per Trans值大于0，你可能需要增加适当的索引来优化你的SQL语句；
8）table scans (short tables)是指表的长度低于buffer chache 2％（2％是有隐含参数_SMALL_TABLE_THRESHOLD定义的，
这个参数在oracle不同的版本中，有不同的含义。在9i和10g中，该参数值定义为2%，在8i中，该参数值为20个blocks，在v7中，该参数为5个blocks）的表。
这些表将优先使用全表扫描。一般不使用索引。_SMALL_TABLE_THRESHOLD值的计算方法如下（9i,8K）： (db_cache_size/8192)*2％。
注意：_SMALL_TABLE_THRESHOLD参数修改是相当危险的操作。
回到顶部

Instance Activity Stats

• Ordered by statistic name

Statistic	Total	per Second	per Trans
ASSM cbk:blocks examined	388	0.43	13.38
ASSM cbk:blocks marked full	95	0.11	3.28
ASSM gsp:L1 bitmaps examined	316	0.35	10.90
ASSM gsp:L2 bitmap full	5	0.01	0.17
ASSM gsp:L2 bitmaps examined	110	0.12	3.79
ASSM gsp:Optimized reject DB	0	0.00	0.00
ASSM gsp:Optimized reject l1	0	0.00	0.00
ASSM gsp:Search all	5	0.01	0.17
ASSM gsp:Search hint	5	0.01	0.17
ASSM gsp:Search steal	4	0.00	0.14
ASSM gsp:bump HWM	4	0.00	0.14
ASSM gsp:get free block	293	0.33	10.10
ASSM gsp:get free data block	0	0.00	0.00
ASSM gsp:good hint	193	0.22	6.66
ASSM gsp:reject L1	0	0.00	0.00
ASSM gsp:reject db	0	0.00	0.00
ASSM rsv:fill reserve	0	0.00	0.00
ASSM wasted db state change	1	0.00	0.03
Batched IO (bound) vector count	3	0.00	0.10
Batched IO block miss count	24	0.03	0.83
Batched IO double miss count	1	0.00	0.03
Batched IO same unit count	13	0.01	0.45
Batched IO single block count	3	0.00	0.10
Batched IO vector block count	8	0.01	0.28
Batched IO vector read count	1	0.00	0.03
Block Cleanout Optim referenced	26	0.03	0.90
CCursor + sql area evicted	4	0.00	0.14
CPU used by this session	119,255	133.70	4,112.24
CPU used when call started	119,258	133.70	4,112.34
CR blocks created	6	0.01	0.21
Cached Commit SCN referenced	2,502,498	2,805.53	86,293.03
Commit SCN cached	14	0.02	0.48
DFO trees parallelized	4	0.00	0.14
Effective IO time	12,182	13.66	420.07
HSC Heap Segment Block Changes	3,327	3.73	114.72
Heap Segment Array Inserts	147	0.16	5.07
Heap Segment Array Updates	2,702	3.03	93.17
KTFB alloc req	6	0.01	0.21
KTFB alloc space (block)	4,325,376	4,849.14	149,150.90
KTFB alloc time (ms)	901	1.01	31.07
KTFB apply req	2	0.00	0.07
KTFB apply time (ms)	14	0.02	0.48
KTFB commit req	0	0.00	0.00
KTFB commit time (ms)	0	0.00	0.00
KTFB free req	0	0.00	0.00
KTFB free space (block)	0	0.00	0.00
KTFB free time (ms)	0	0.00	0.00
LOB table id lookup cache misses	0	0.00	0.00
Number of read IOs issued	0	0.00	0.00
PX local messages recv'd	89	0.10	3.07
PX local messages sent	90	0.10	3.10
PX remote messages recv'd	8	0.01	0.28
PX remote messages sent	143	0.16	4.93
Parallel operations downgraded to serial	0	0.00	0.00
Parallel operations not downgraded	4	0.00	0.14
Requests to/from client	1,049,244	1,176.30	36,180.83
RowCR attempts	0	0.00	0.00
RowCR hits	0	0.00	0.00
SQL*Net roundtrips to/from client	1,049,315	1,176.38	36,183.28
active txn count during cleanout	189	0.21	6.52
blocks cleaned out using minact	148	0.17	5.10
branch node splits	0	0.00	0.00
buffer is not pinned count	121,357,980	136,053.38	4,184,757.93
buffer is pinned count	715,212,470	801,818.49	24,662,498.97
bytes received via SQL*Net from client	30,399,639	34,080.77	1,048,263.41
bytes sent via SQL*Net to client	70,402,214	78,927.31	2,427,662.55
calls to get snapshot scn: kcmgss	551,276	618.03	19,009.52
calls to kcmgas	6,206	6.96	214.00
calls to kcmgcs	22,936	25.71	790.90
cell physical IO interconnect bytes	22,355,968	25,063.08	770,895.45
change write time	25	0.03	0.86
cleanout - number of ktugct calls	329	0.37	11.34
cleanouts and rollbacks - consistent read gets	0	0.00	0.00
cleanouts only - consistent read gets	169	0.19	5.83
cluster key scan block gets	5,496	6.16	189.52
cluster key scans	5,496	6.16	189.52
commit batch/immediate performed	0	0.00	0.00
commit batch/immediate requested	0	0.00	0.00
commit cleanout failures: block lost	6	0.01	0.21
commit cleanout failures: buffer being written	0	0.00	0.00
commit cleanout failures: callback failure	16	0.02	0.55
commit cleanout failures: cannot pin	0	0.00	0.00
commit cleanouts	3,276	3.67	112.97
commit cleanouts successfully completed	3,254	3.65	112.21
commit immediate performed	0	0.00	0.00
commit immediate requested	0	0.00	0.00
commit txn count during cleanout	292	0.33	10.07
consistent changes	169	0.19	5.83
consistent gets	213,171,694	238,984.93	7,350,748.07
consistent gets direct	0	0.00	0.00
consistent gets examination	154,465,195	173,169.59	5,326,386.03
consistent gets examination (fastpath)	154,464,845	173,169.20	5,326,373.97
consistent gets from cache	213,171,694	238,984.93	7,350,748.07
consistent gets pin	58,706,499	65,815.35	2,024,362.03
consistent gets pin (fastpath)	58,705,762	65,814.52	2,024,336.62
cursor authentications	0	0.00	0.00
cursor reload failures	0	0.00	0.00
data blocks consistent reads - undo records applied	12	0.01	0.41
db block changes	20,663	23.17	712.52
db block gets	20,831	23.35	718.31
db block gets direct	0	0.00	0.00
db block gets from cache	20,831	23.35	718.31
db block gets from cache (fastpath)	14,116	15.83	486.76
deferred (CURRENT) block cleanout applications	2,917	3.27	100.59
dirty buffers inspected	0	0.00	0.00
enqueue conversions	29	0.03	1.00
enqueue releases	11,735	13.16	404.66
enqueue requests	11,742	13.16	404.90
enqueue timeouts	0	0.00	0.00
enqueue waits	76	0.09	2.62
exchange deadlocks	0	0.00	0.00
execute count	539,551	604.89	18,605.21
failed probes on index block reclamation	0	0.00	0.00
file io service time	14	0.02	0.48
free buffer inspected	3,309	3.71	114.10
free buffer requested	4,077	4.57	140.59
gc cleanout applied	0	0.00	0.00
gc cleanout saved	6	0.01	0.21
gc cr block receive time	3	0.00	0.10
gc cr blocks received	646	0.72	22.28
gc cr multiblock grant time	0	0.00	0.00
gc cr multiblock grants received	0	0.00	0.00
gc current block receive time	3	0.00	0.10
gc current blocks received	512	0.57	17.66
gc force cr read cr	3	0.00	0.10
gc force cr read current	0	0.00	0.00
gc local grants	140	0.16	4.83
gc merge pi fg	145	0.16	5.00
gc reader bypass grants	7	0.01	0.24
gc reader bypass waits	1	0.00	0.03
gc remote grants	283	0.32	9.76
gc status messages received	151	0.17	5.21
gcs affinity lock failures	0	0.00	0.00
gcs affinity lock grants	7	0.01	0.24
gcs data block access records	1,002	1.12	34.55
gcs messages sent	1,429	1.60	49.28
gcs stats management (SCM) process posts	4	0.00	0.14
ges messages sent	2,947	3.30	101.62
global enqueue gets sync	160,478	179.91	5,533.72
global enqueue releases	160,414	179.84	5,531.52
heap block compress	8	0.01	0.28
hot buffers moved to head of LRU	63,449	71.13	2,187.90
immediate (CR) block cleanout applications	169	0.19	5.83
immediate (CURRENT) block cleanout applications	212	0.24	7.31
index crx upgrade (positioned)	0	0.00	0.00
index crx upgrade (prefetch)	0	0.00	0.00
index fast full scans (full)	16	0.02	0.55
index fetch by key	56,949,946	63,846.09	1,963,791.24
index range scans	598,262	670.71	20,629.72
index reclamation/extension switch	0	0.00	0.00
index split cancel wait clean	7	0.01	0.24
index split cancel wait noclean	30	0.03	1.03
leaf node 90-10 splits	0	0.00	0.00
leaf node splits	55	0.06	1.90
lob reads	4,816	5.40	166.07
lob writes	76	0.09	2.62
lob writes unaligned	76	0.09	2.62
logical read bytes from cache	1,746,473,164,800	1,957,955,897.16	60,223,212,579.31
logons cumulative	2,733	3.06	94.24
max cf enq hold time	0	0.00	0.00
messages sent	2,761	3.10	95.21
min active SCN optimization applied on CR	0	0.00	0.00
no work - consistent read gets	58,682,929	65,788.92	2,023,549.28
non-idle wait count	1,227,850	1,376.53	42,339.66
opened cursors cumulative	23,562	26.42	812.48
parse count (describe)	0	0.00	0.00
parse count (failures)	0	0.00	0.00
parse count (hard)	11	0.01	0.38
parse count (total)	23,154	25.96	798.41
parse time cpu	125	0.14	4.31
parse time elapsed	149	0.17	5.14
physical read IO requests	116	0.13	4.00
physical read bytes	950,272	1,065.34	32,768.00
physical read total IO requests	1,433	1.61	49.41
physical read total bytes	22,355,968	25,063.08	770,895.45
physical read total multi block requests	0	0.00	0.00
physical reads	116	0.13	4.00
physical reads cache	98	0.11	3.38
physical reads cache prefetch	0	0.00	0.00
physical reads direct	18	0.02	0.62
physical reads direct (lob)	18	0.02	0.62
physical reads direct temporary tablespace	0	0.00	0.00
physical reads prefetch warmup	0	0.00	0.00
physical write IO requests	0	0.00	0.00
physical write bytes	0	0.00	0.00
physical write total IO requests	0	0.00	0.00
physical write total bytes	0	0.00	0.00
physical write total multi block requests	0	0.00	0.00
physical writes	0	0.00	0.00
physical writes direct	0	0.00	0.00
physical writes direct (lob)	0	0.00	0.00
physical writes direct temporary tablespace	0	0.00	0.00
physical writes non checkpoint	0	0.00	0.00
pinned buffers inspected	21	0.02	0.72
pinned cursors current	1	0.00	0.03
prefetched blocks aged out before use	0	0.00	0.00
process last non-idle time	893	1.00	30.79
queries parallelized	3	0.00	0.10
recursive calls	757,489	849.21	26,120.31
recursive cpu usage	1,337	1.50	46.10
redo buffer allocation retries	1	0.00	0.03
redo entries	10,638	11.93	366.83
redo log space requests	1	0.00	0.03
redo ordering marks	0	0.00	0.00
redo size	4,706,664	5,276.60	162,298.76
redo size for direct writes	0	0.00	0.00
redo subscn max counts	217	0.24	7.48
redo synch poll writes	0	0.00	0.00
redo synch polls	0	0.00	0.00
redo synch time	72	0.08	2.48
redo synch time (usec)	945,014	1,059.45	32,586.69
redo synch time overhead (usec)	3,458	3.88	119.24
redo synch time overhead count ( 2ms)	207	0.23	7.14
redo synch time overhead count ( 8ms)	0	0.00	0.00
redo synch writes	2,705	3.03	93.28
redo write broadcast ack count	0	0.00	0.00
redo write broadcast ack time	0	0.00	0.00
redo write info find	207	0.23	7.14
rollback changes - undo records applied	0	0.00	0.00
rollbacks only - consistent read gets	6	0.01	0.21
root node splits	0	0.00	0.00
rows fetched via callback	39,707,276	44,515.48	1,369,216.41
saved cleanout failures: callback failure	0	0.00	0.00
saved cleanouts	0	0.00	0.00
saved cleanouts successfully completed	0	0.00	0.00
securefile allocation bytes	0	0.00	0.00
securefile allocation chunks	0	0.00	0.00
securefile bytes non-transformed	0	0.00	0.00
securefile direct read bytes	147,456	165.31	5,084.69
securefile direct read ops	18	0.02	0.62
securefile direct write bytes	0	0.00	0.00
securefile direct write ops	0	0.00	0.00
securefile number of non-transformed flushes	0	0.00	0.00
segment chunks allocation from dispenser	0	0.00	0.00
segment dispenser allocations	0	0.00	0.00
session connect time	0	0.00	0.00
session cursor cache hits	642	0.72	22.14
session logical reads	213,192,525	239,008.29	7,351,466.38
shared hash latch upgrades - no wait	2,321	2.60	80.03
shared hash latch upgrades - wait	0	0.00	0.00
shared io pool buffer get success	18	0.02	0.62
sorts (disk)	0	0.00	0.00
sorts (memory)	781,461	876.09	26,946.93
sorts (rows)	348,679,723	390,901.81	12,023,438.72
sql area evicted	4	0.00	0.14
sql area purged	4	0.00	0.14
switch current caused by our pin	2,599	2.91	89.62
switch current to new buffer	2,599	2.91	89.62
table fetch by rowid	389,572,881	436,746.77	13,433,547.62
table fetch continued row	0	0.00	0.00
table scan blocks gotten	15,057,924	16,881.31	519,238.76
table scan disk non-IMC rows gotten	219,570,816	246,158.93	7,571,407.45
table scan rows gotten	219,570,816	246,158.93	7,571,407.45
table scans (direct read)	0	0.00	0.00
table scans (long tables)	0	0.00	0.00
table scans (short tables)	3,885	4.36	133.97
temp space allocated (bytes)	0	0.00	0.00
total cf enq hold time	0	0.00	0.00
total number of cf enq holders	0	0.00	0.00
transaction rollbacks	0	0.00	0.00
undo change vector size	1,507,852	1,690.44	51,994.90
user calls	1,057,477	1,185.53	36,464.72
user commits	28	0.03	0.97
user logons cumulative	2,716	3.04	93.66
user logouts cumulative	2,709	3.04	93.41
user rollbacks	1	0.00	0.03
workarea executions - multipass	0	0.00	0.00
workarea executions - onepass	0	0.00	0.00
workarea executions - optimal	34,393	38.56	1,185.97
write clones created in foreground	0	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
logons current	6	13
opened cursors current	20	151
session cursor cache count	3,653,540	3,673,610
session pga memory	42,746,832	73,630,648
session pga memory max	657,163,888	866,567,768
session uga memory	117,584,104,912	118,228,340,880
session uga memory max	1,225,451,381,776	1,231,951,850,592

---

Back to Instance Activity Statistics
Back to Top

通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，
他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。在这里主要关注Av Rd(ms)列 (reads per millisecond)的值，
一般来说，大部分的磁盘系统的这个值都能调整到14ms以下，oracle认为该值超过20ms都是不必要的。如果该值超过1000ms，
基本可以肯定存在I/O的性能瓶颈。如果在这一列上出现######，可能是系统存在严重的I/O问题，也可能是格式的显示问题。
当出现上面的问题，可以考虑以下的方法：
1）优化操作该表空间或者文件的相关的语句。
2）如果该表空间包含了索引，可以考虑压缩索引，是索引的分布空间减小，从而减小I/O。
3）将该表空间分散在多个逻辑卷中，平衡I/O的负载。
4）可以通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT来调整读取的并行度，这将提高全表扫描的效率。但是也会带来一个问题，
就是oracle会因此更多的使用全表扫描而放弃某些索引的使用。为解决这个问题，需要设置另外一个参数OPTIMIZER_INDEX_COST_ADJ=30（一般建议设置10－50）。
关于OPTIMIZER_INDEX_COST_ADJ＝n：该参数是一个百分比值，缺省值为100，可以理解为FULL SCAN COST/INDEX SCAN COST。
当n%* INDEX SCAN COST
回到顶部

IO Stats

• IOStat by Filetype summary
• Tablespace IO Stats
• File IO Stats

Back to Top

IOStat by Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Small Read and Large Read are average service times
• Ordered by (Data Read + Write) desc

Filetype Name	Reads: Data	Reqs per sec	Data per sec	Writes: Data	Reqs per sec	Data per sec	Small Read	Large Read
Data File	1M	0.45	.001M	7M	0.72	.008M	518.80us
TOTAL:	1M	0.45	.001M	7M	0.72	.008M	518.80us

---

Back to IO Stats
Back to Top

显示每个表空间的I/O统计。根据Oracle经验，Av Rd(ms) [Average Reads in milliseconds]不应该超过30，否则认为有I/O争用。
回到顶部

Tablespace IO Stats

No data exists for this section of the report.

Back to IO Stats
Back to Top

本节不用关注。
回到顶部

File IO Stats

No data exists for this section of the report.

Back to IO Stats
Back to Top

本节不用关注。
回到顶部

Buffer Wait Statistics

No data exists for this section of the report.

Back to Top

Undo从9i开始，回滚段一般都是自动管理的，一般情况下，不需要太重点关注。
这里，主要关注pct waits，如果出现比较多的pct waits，那就需要增加回滚段的数量或者增大回滚段的空间。
另外，观察一下各个回滚段使用的情况，比较理想的是各个回滚段上Avg Active比较均衡。在oracle 9i之前，
回滚段是手工管理的，可以通过指定optimal值来设定一个回滚段收缩的值，如果不设定，默认也应当为
initial+(minextents-1)*next extents ，这个指定的结果，就是限制了回滚段不能无限制的增长，
当超过optimal的设定值后，在适当的时候，oracle会shrinks到optimal大小。但是9i之后，
undo一般都设置为auto模式，在这种模式下，无法指定optimal值，好像也没有默认值，所以无法shrinks，
回滚段就会无限制的增长，一直到表空间利用率达到为100％，如果表空间设置为自动扩展的方式，这种情况下，
就更糟糕，undo将无限制的增长。shrinks的值为0表明从来就没收缩过。
回到顶部

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 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
2	0.44	4,084	49	3	15/15	0/0	0/0/0/0/0/694

---

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
08-Mar 15:10	42	1,836	49	1	15	0/0	0/0/0/0/0/264
08-Mar 15:00	400	2,248	0	3	15	0/0	0/0/0/0/0/430

---

Back to Undo Statistics
Back to Top

本节Segment章节是如果数据库I/O有瓶颈，建议关注这些segment章节，从不同维度来分析的，
比如逻辑读，物理读，没有优化的读，有优化的读，直接物理读，物理写，直接写，表扫，数据块，ITL等待。
建议关注以下章节：
1）逻辑读和物理读;
2）逻辑写和物理写;
3）缓冲忙等待
4）表扫
5）行锁等待
需要考虑以下三方面的使用情况：
1）表
2）索引
3）分区
回到顶部

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Physical Read Requests
• Segments by UnOptimized Reads
• Segments by Optimized Reads
• Segments by Direct Physical Reads
• Segments by Physical Writes
• Segments by Physical Write Requests
• Segments by Direct Physical Writes
• Segments by Table Scans
• Segments by DB Blocks Changes
• 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 Global Cache Remote Grants

Back to Top

Segments by Logical Reads

• Total Logical Reads: 213,192,525
• Captured Segments account for 100.0% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Logical Reads	%Total	PDB Name
WCMMNG00	TBS_CUR_IDX	WCM_APPLY_VERIFY_AUDIT_IDX_1		INDEX	24904	24904	113,587,424	53.28	WCMMNG00
WCMMNG00	TBS_CUR_DAT	WCM_APPLY_VERIFY_AUDIT_T		TABLE	24771	24771	54,731,840	25.67	WCMMNG00
WCMMNG00	TBS_CUR_DAT	WCM_MNG_MAININFO_T		TABLE	24688	24688	41,063,696	19.26	WCMMNG00
APPHIGH1	SYSTEM	WCM_MNG_MAININFO_T_CARD_CODE_IDX		INDEX	75583	75583	3,172,224	1.49	WCMMNG00
SYS	SYSTEM	SYSAUTH$		TABLE	145	145	130,656	0.06	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Physical Reads

• Total Physical Reads: 116
• Captured Segments account for 60.3% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Physical Reads	%Total	PDB Name
SYS	SYSAUX	SYS_LOB0000011151C00038$$		LOB	11152	11152	18	15.52	WCMMNG00
SYS	SYSAUX	WRM$_SNAPSHOT_DETAILS_INDEX		INDEX	11586	11586	8	6.90	WCMMNG00
SYS	SYSAUX	WRH$_ENQUEUE_STAT_PK		INDEX	11186	11186	7	6.03	WCMMNG00
SYS	SYSAUX	WRH$_MUTEX_SLEEP_PK		INDEX	11218	11218	6	5.17	WCMMNG00
SYS	SYSAUX	WRH$_BG_EVENT_SUMMARY_PK		INDEX	11176	11176	4	3.45	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Physical Read Requests

• Total Physical Read Requests: 116
• Captured Segments account for 61.2% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Phys Read Requests	%Total	PDB Name
SYS	SYSAUX	SYS_LOB0000011151C00038$$		LOB	11152	11152	18	15.52	WCMMNG00
SYS	SYSAUX	WRM$_SNAPSHOT_DETAILS_INDEX		INDEX	11586	11586	9	7.76	WCMMNG00
SYS	SYSAUX	WRH$_ENQUEUE_STAT_PK		INDEX	11186	11186	7	6.03	WCMMNG00
SYS	SYSAUX	WRH$_MUTEX_SLEEP_PK		INDEX	11218	11218	6	5.17	WCMMNG00
SYS	SYSAUX	WRH$_BG_EVENT_SUMMARY_PK		INDEX	11176	11176	4	3.45	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by UnOptimized Reads

• Total UnOptimized Read Requests: 116
• Captured Segments account for 61.2% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	UnOptimized Reads	%Total	PDB Name
SYS	SYSAUX	SYS_LOB0000011151C00038$$		LOB	11152	11152	18	15.52	WCMMNG00
SYS	SYSAUX	WRM$_SNAPSHOT_DETAILS_INDEX		INDEX	11586	11586	9	7.76	WCMMNG00
SYS	SYSAUX	WRH$_ENQUEUE_STAT_PK		INDEX	11186	11186	7	6.03	WCMMNG00
SYS	SYSAUX	WRH$_MUTEX_SLEEP_PK		INDEX	11218	11218	6	5.17	WCMMNG00
SYS	SYSAUX	WRH$_BG_EVENT_SUMMARY_PK		INDEX	11176	11176	4	3.45	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Optimized Reads

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Direct Physical Reads

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Physical Writes

• Total Physical Writes: 1
• Captured Segments account for 4.6E+04% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Physical Writes	%Total	PDB Name
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	68	6800.00	WCMMNG00
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	WRH$_SQLSTAT_1924087814_MXSN	INDEX PARTITION	75239	75239	42	4200.00	WCMMNG00
SYS	SYSAUX	WRH$_PARAMETER	WRH$_PARAMETER_1924087814_MXSN	TABLE PARTITION	65644	75276	34	3400.00	WCMMNG00
SYS	SYSAUX	WRH$_LATCH	WRH$_LATCH_1924087814_MXSN	TABLE PARTITION	65612	75252	26	2600.00	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	20	2000.00	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Physical Write Requests

• Total Physical Write Requests: 1
• Captured Segments account for 3.1E+04% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Phys Write Requests	%Total	PDB Name
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	34	3400.00	WCMMNG00
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	WRH$_SQLSTAT_1924087814_MXSN	INDEX PARTITION	75239	75239	26	2600.00	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	17	1700.00	WCMMNG00
SYS	SYSAUX	WRH$_SEG_STAT_OBJ		TABLE	11343	11343	13	1300.00	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT	WRH$_SYSSTAT_1924087814_MXSN	TABLE PARTITION	65640	75272	12	1200.00	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Direct Physical Writes

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Table Scans

• Total Table Scans: 16
• Captured Segments account for 37.5% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Table Scans	%Total	PDB Name
SYS	SYSAUX	WRH$_EVENT_NAME_PK		INDEX	11172	11172	6	37.50	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by DB Blocks Changes

• % of Capture shows % of DB Block Changes for each top segment compared
• with total DB Block Changes for all segments captured by the Snapshot
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	DB Block Changes	% of Capture	PDB Name
SYS	SYSTEM	USER$		TABLE	22	10	43,344	89.88	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	1,904	3.95	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	752	1.56	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_MISSES_SUMMARY_PK	WRH$_LATCH_MISSES_SUMMARY_1924087814_MXSN	INDEX PARTITION	75257	75257	240	0.50	WCMMNG00
SYS	SYSAUX	WRH$_PARAMETER_PK	WRH$_PARAMETER_1924087814_MXSN	INDEX PARTITION	75277	75277	224	0.46	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

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

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by CR Blocks Received

• Total CR Blocks Received: 646
• Captured Segments account for 87.2% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	CR Blocks Received	%Total	PDB Name
SYS	SYSTEM	USER$		TABLE	22	10	215	33.28	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	42	6.50	WCMMNG00
SYS	SYSAUX	WRH$_SEG_STAT_OBJ		TABLE	11343	11343	33	5.11	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT	WRH$_SYSSTAT_1924087814_MXSN	TABLE PARTITION	65640	75272	32	4.95	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	26	4.02	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Current Blocks Received

• Total Current Blocks Received: 512
• Captured Segments account for 82.0% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	Current Blocks Received	%Total	PDB Name
SYS	SYSTEM	USER$		TABLE	22	10	107	20.90	WCMMNG00
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	WRH$_SQLSTAT_1924087814_MXSN	INDEX PARTITION	75239	75239	40	7.81	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	39	7.62	WCMMNG00
SYS	SYSAUX	WRH$_SYSSTAT	WRH$_SYSSTAT_1924087814_MXSN	TABLE PARTITION	65640	75272	21	4.10	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	19	3.71	WCMMNG00

---

Back to Segment Statistics
Back to Top

Segments by Global Cache Remote Grants

• Total Global Cache Remote Grants: 283
• Captured Segments account for 100.4% of Total
• When ** MISSING ** occurs, some of the object attributes may not be available

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Obj#	Dataobj#	GC Remote Grants	%Total	PDB Name
SYS	SYSAUX	WRH$_SYSSTAT_PK	WRH$_SYSSTAT_1924087814_MXSN	INDEX PARTITION	75273	75273	59	20.85	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_MISSES_SUMMARY_PK	WRH$_LATCH_MISSES_SUMMARY_1924087814_MXSN	INDEX PARTITION	75257	75257	34	12.01	WCMMNG00
SYS	SYSAUX	WRH$_PARAMETER	WRH$_PARAMETER_1924087814_MXSN	TABLE PARTITION	65644	75276	32	11.31	WCMMNG00
SYS	SYSAUX	WRH$_LATCH_PK	WRH$_LATCH_1924087814_MXSN	INDEX PARTITION	75253	75253	27	9.54	WCMMNG00
SYS	SYSAUX	WRH$_CON_SYSSTAT	WRH$_CON_SYSSTAT_1924087814_MXSN	TABLE PARTITION	65744	75376	19	6.71	WCMMNG00

---

Back to Segment Statistics
Back to Top

本节不用关注。
回到顶部

Dictionary Cache Statistics

• Dictionary Cache Stats
• Dictionary Cache Stats (RAC)

Back to Top

本节不用关注。
回到顶部

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	680	0.00	0		15	48
dc_files	34	0.00	0		0	144
dc_global_oids	140	0.00	0		0	601
dc_histogram_data	4,718	0.17	0		0	282,286
dc_histogram_defs	1,391	5.68	0		0	235,813
dc_objects	53,501	0.01	0		18	75,018
dc_profiles	10,804	0.00	0		0	26
dc_rollback_segments	49,067	0.00	0		0	2,918
dc_segments	160	7.50	0		37	41,207
dc_sequences	8	25.00	0		8	127
dc_tablespaces	15,018	0.00	0		0	245
dc_users	280,455	0.00	0		0	2,896
outstanding_alerts	12	91.67	0		0	95
sch_lj_oids	24	0.00	0		0	516

---

Back to Dictionary Cache Statistics
Back to Top

本节不用关注。
回到顶部

Dictionary Cache Stats (RAC)

Cache	GES Requests	GES Conflicts	GES Releases
dc_awr_control	680	0	0
dc_files	34	0	0
dc_global_oids	140	0	0
dc_histogram_defs	1,391	0	0
dc_objects	10,317	0	0
dc_profiles	10,804	0	0
dc_rollback_segments	49,067	0	0
dc_segments	160	0	0
dc_sequences	8	0	0
dc_tablespaces	15,031	0	0
dc_users	161,132	0	0
outstanding_alerts	12	0	0
sch_lj_oids	24	0	0

---

Back to Dictionary Cache Statistics
Back to Top

本节不用关注。
回到顶部

Library Cache Statistics

• Library Cache Activity
• Library Cache Activity (RAC)

Back to Top

本节为词典缓存统计
Get Requests：get表示一种类型的锁，语法分析锁
pin requests：pin也表示一种类型的锁，是在执行发生的加锁
reloads：reloads列显示一条已执行过的语句因Library Cache使该语句的已语法分析版本
过期或作废而需要被重新语法分析的次数pct miss应该不高于1％。
Reloads /pin requests <1％，否则应该考虑增大SHARED_POOL_SIZE
该部分信息通过v$librarycache视图统计得到：
select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER', 'INDEX');
回到顶部

Library Cache Activity

• "Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
ACCOUNT_STATUS	21,600	0.00	0		0	0
BODY	262	0.00	1,436	0.00	0	0
DBLINK	31,370	0.00	0		0	0
EDITION	7,864	0.00	7,864	0.00	0	0
INDEX	152	0.00	152	0.66	1	0
SCHEMA	8,112	0.10	0		0	0
SQL AREA	57,089	0.09	816,348	0.04	172	131
SQL AREA BUILD	213	36.15	0		0	0
SQL AREA STATS	172	22.09	172	22.09	0	0
TABLE/PROCEDURE	14,860	0.46	17,835	1.06	22	0
TRIGGER	52	0.00	52	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
ACCOUNT_STATUS	21,600	0	0	0	0
BODY	0	1,432	1,432	0	0
DBLINK	31,370	0	0	0	0
EDITION	7,863	7,863	7,863	0	0
INDEX	152	152	152	0	0
SCHEMA	7,861	0	0	0	0
TABLE/PROCEDURE	8,891	16,174	16,174	0	0
TRIGGER	0	52	52	0	0

---

Back to Library Cache Statistics
Back to Top

本节需要重点关注。
回到顶部

Initialization Parameters

• Parameters modified by this container
• Parameters modified by other containers
• Multi-Valued Parameters modified by this container
• Multi-Valued Parameters modified by other containers

Back to Top

本节需要重点关注。CDB级别的参数，需要和《招商银行开放平台Oracle数据库参数配置指导书19cV1.6.doc》
中的第7节数据库参数对照着看，看是否符合标准。
回到顶部

Parameters modified by this container

• This section shows all the modified initialization parameters that were in effect during the entire snapshot interval
• End Value is displayed only if the parameter value was modified within the snapshot interval

Parameter Name	Begin value	End value (if different)	PDB Name
_rollback_segment_count	0		WCMMNG00
cpu_count	4		WCMMNG00
db_cache_size	4194304000		WCMMNG00
db_securefile	PREFERRED		WCMMNG00
job_queue_processes	20		WCMMNG00
parallel_max_servers	1280		WCMMNG00
pga_aggregate_limit	8388608000		WCMMNG00
pga_aggregate_target	2097152000		WCMMNG00
resource_manager_plan			WCMMNG00
result_cache_max_size	0		WCMMNG00
sessions	750		WCMMNG00
sga_target	8388608000		WCMMNG00
shared_pool_size	2097152000		WCMMNG00
undo_retention	900		WCMMNG00
undo_tablespace	UNDOTBS1		WCMMNG00

---

Back to Initialization Parameters
Back to Top

本节需要重点关注。CDB级别的参数，需要和《招商银行开放平台Oracle数据库参数配置指导书19cV1.6.doc》
中的第7节数据库参数对照着看，看是否符合标准。
回到顶部

Parameters modified by other containers

• This section shows all the modified initialization parameters that were in effect during the snapshot interval
• End Value is displayed only if the parameter value was changed within the snapshot interval

Parameter Name	Begin value	End value (if different)	PDB Name
_PX_use_large_pool	TRUE
_adg_parselock_timeout	3000
_autotask_max_window	11520
_b_tree_bitmap_plans	FALSE
_cursor_obsolete_threshold	1024
_enable_shared_pool_durations	FALSE
_fast_index_maintenance	FALSE
_gc_policy_minimum	15000
_max_spacebg_slaves	10
_optimizer_batch_table_access_by_rowid	FALSE
_optimizer_gather_feedback	FALSE
_optimizer_gather_stats_on_load	FALSE
_optimizer_use_feedback	FALSE
_redo_transport_async_mode	1
_report_capture_cycle_time	0
_reuse_dropped_pdbid_time	1800
_sys_logon_delay	0
_trace_files_public	TRUE
_use_single_log_writer	TRUE
archive_lag_target	900
audit_file_dest	/u01/app/oracle/admin/H01U08PF/adump
audit_trail	NONE
awr_snapshot_time_offset	1000000
backup_tape_io_slaves	TRUE
cluster_database	TRUE
compatible	19.0.0
control_file_record_keep_time	7
control_files	+DATADG/H01U08PF/CONTROLFILE/current.257.1122562155, +FRADG/H01U08PF/CONTROLFILE/current.256.1122562157
db_block_size	8192
db_create_file_dest	+DATADG
db_files	6000
db_flashback_retention_target	1440
db_name	H01U08PF
db_recovery_file_dest	+FRADG
db_recovery_file_dest_size	96636764160
deferred_segment_creation	FALSE
diagnostic_dest	/diaglog
dispatchers	(PROTOCOL=TCP) (SERVICE=H01U08PFXDB)
enable_ddl_logging	TRUE
enable_pluggable_database	TRUE
fast_start_mttr_target	300
instance_number	1
listener_networks	((NAME=network1)(LOCAL_LISTENER=local_net1) (REMOTE_LISTENER=remote_net1))
local_listener	local_net1
lock_sga	TRUE
log_archive_dest_1	LOCATION=USE_DB_RECOVERY_FILE_DEST
nls_language	AMERICAN
nls_territory	AMERICA
open_cursors	1000
optimizer_capture_sql_plan_baselines	FALSE
parallel_force_local	TRUE
parallel_min_servers	0
pre_page_sga	FALSE
processes	10000
remote_listener
remote_login_passwordfile	EXCLUSIVE
session_cached_cursors	300
sga_max_size	294205259776
sql92_security	FALSE
standby_file_management	AUTO
target_pdbs	60
thread	1
timed_statistics	TRUE
undo_management	AUTO

---

Back to Initialization Parameters
Back to Top

本节不用关注。
回到顶部

Multi-Valued Parameters modified by this container

No data exists for this section of the report.

Back to Initialization Parameters
Back to Top

本节不用关注。
回到顶部

Multi-Valued Parameters modified by other containers

• This section only displays parameters that have more than one value
• '(NULL)' indicates a missing parameter value
• A blank in the 'End Snapshot' indicates the same value as the 'Begin Snapshot'

Parameter Name	Begin value	End value (if different)	PDB Name
control_files	+DATADG/H01U08PF/CONTROLFILE/current.257.1122562155
control_files	+FRADG/H01U08PF/CONTROLFILE/current.256.1122562157

---

Back to Initialization Parameters
Back to Top

Active Session History (ASH) Report

• Top SQL with Top Events
• Top SQL with Top Row Sources
• Top Sessions
• Top Blocking Sessions
• Top PL/SQL Procedures
• Top Events
• Top Event P1/P2/P3 Values
• Top DB Objects
• Activity Over Time

Back to Top

本节需要关注，分析SQL的时候可以结合一起分析
回到顶部

Top SQL with Top Events

• Top SQL statements by DB Time along with the top events by DB Time for those SQLs.
• % Activity is the percentage of DB Time due to the SQL.
• % Event is the percentage of DB Time due to the event that the SQL is waiting on.
• % Row Source is the percentage of DB Time due to the row source for the SQL waiting on the event.
• Executions is the number of executions of the SQL that were sampled in ASH.

SQL ID	Plan Hash	Executions	% Activity	Event	% Event	Top Row Source	% Row Source	SQL Text	Container Name
5b64sq2g9zj1a	436876415	34	27.87	CPU + Wait for CPU	27.87	INDEX - UNIQUE SCAN	9.84	SELECT info.CARD_NUMBER, info....	WCMMNG00
dprdzbty156ux	4131394011	29	23.77	CPU + Wait for CPU	23.77	HASH JOIN - OUTER	10.66	SELECT info.CARD_NUMBER, info....	WCMMNG00
b2cxpv2ah9j3f	1023752713	22	18.03	CPU + Wait for CPU	18.03	FIXED TABLE - FULL	17.21	SELECT * FROM (SELECT user_nam...	WCMMNG00
3y2d7rgcy1959	3238011603	10	8.20	CPU + Wait for CPU	8.20	TABLE ACCESS - BY INDEX ROWID	4.92	SELECT info.CARD_NUMBER, info....	WCMMNG00
6fyytvcmzzaxq	3847658529	5	4.10	CPU + Wait for CPU	4.10	FIXED TABLE - FULL	3.28	select ( (select value from v$...	WCMMNG00

---

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析SQL的时候可以结合一起分析
回到顶部

Top SQL with Top Row Sources

• Top SQL statements by DB Time along with the top row sources by DB Time for those SQLs.
• % Activity is the percentage of DB Time due to the SQL.
• % Row Source is the percentage of DB Time spent on the row source by that SQL.
• % Event is the percentage of DB Time spent on the event by the SQL executing the row source.
• Executions is the number of executions of the SQL that were sampled in ASH.

SQL ID	Plan Hash	Executions	% Activity	Row Source	% Row Source	Top Event	% Event	SQL Text	Container Name
5b64sq2g9zj1a	436876415	34	27.87	INDEX - UNIQUE SCAN	9.84	CPU + Wait for CPU	9.84	SELECT info.CARD_NUMBER, info....	WCMMNG00
5b64sq2g9zj1a	436876415	34	27.86885245901639344262295081967213114754	WINDOW - SORT PUSHED RANK	8.20	CPU + Wait for CPU	8.20		WCMMNG00
5b64sq2g9zj1a	436876415	34	27.86885245901639344262295081967213114754	TABLE ACCESS - BY INDEX ROWID	4.10	CPU + Wait for CPU	4.10		WCMMNG00
dprdzbty156ux	4131394011	29	23.77	HASH JOIN - OUTER	10.66	CPU + Wait for CPU	10.66	SELECT info.CARD_NUMBER, info....	WCMMNG00
dprdzbty156ux	4131394011	29	23.7704918032786885245901639344262295082	TABLE ACCESS - BY INDEX ROWID	5.74	CPU + Wait for CPU	5.74		WCMMNG00
dprdzbty156ux	4131394011	29	23.7704918032786885245901639344262295082	TABLE ACCESS - FULL	3.28	CPU + Wait for CPU	3.28		WCMMNG00
b2cxpv2ah9j3f	1023752713	22	18.03	FIXED TABLE - FULL	17.21	CPU + Wait for CPU	17.21	SELECT * FROM (SELECT user_nam...	WCMMNG00
3y2d7rgcy1959	3238011603	10	8.20	TABLE ACCESS - BY INDEX ROWID	4.92	CPU + Wait for CPU	4.92	SELECT info.CARD_NUMBER, info....	WCMMNG00
3y2d7rgcy1959	3238011603	10	8.19672131147540983606557377049180327869	WINDOW - SORT PUSHED RANK	1.64	CPU + Wait for CPU	1.64		WCMMNG00
3y2d7rgcy1959	3238011603	10	8.19672131147540983606557377049180327869	INDEX - RANGE SCAN	1.64	CPU + Wait for CPU	1.64		WCMMNG00
6fyytvcmzzaxq	3847658529	5	4.10	FIXED TABLE - FULL	3.28	CPU + Wait for CPU	3.28	select ( (select value from v$...	WCMMNG00

---

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top Sessions

• '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
• 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event

Sid, Serial#	% Activity	Event	% Event	User	Program	# Samples Active	XIDs
2606,62859	13.93	CPU + Wait for CPU	13.93	APPHIGH1	JDBC Thin Client	17/89 [ 19%]	0
3999,39101	11.48	CPU + Wait for CPU	11.48	APPHIGH1	JDBC Thin Client	14/89 [ 16%]	0
6602,37610	9.02	CPU + Wait for CPU	9.02	APPHIGH1	JDBC Thin Client	11/89 [ 12%]	0
12695,11558	9.02	CPU + Wait for CPU	9.02	APPHIGH1	JDBC Thin Client	11/89 [ 12%]	0
1894,12863	8.20	CPU + Wait for CPU	8.20	APPHIGH1	JDBC Thin Client	10/89 [ 11%]	0

---

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top Blocking Sessions

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top PL/SQL Procedures

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top Events

• Top Events by DB Time
• % Activity is the percentage of DB Time due to the event

Event	Event Class	Session Type	% Activity	Avg Active Sessions
CPU + Wait for CPU	CPU	FOREGROUND	100.00	1.37

---

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top Event P1/P2/P3 Values

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Top DB Objects

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

本节需要关注，分析等待事件的时候可以结合一起分析
回到顶部

Activity Over Time

• Analysis period is divided into smaller time slots as indicated in the 'Slot Time (Duration)'.
• Top 3 events are reported in each of those slots
• 'Slot Count' shows the number of ASH samples in that slot
• 'Event Count' shows the number of ASH samples waiting for that event in that slot
• '% Event' is 'Event Count' over all ASH samples in the analysis period

Slot Time (Duration)	Slot Count	Event	Event Count	% Event
14:58:53 (1.1 min)	6	CPU + Wait for CPU	6	4.92
15:00:00 (5.0 min)	14	CPU + Wait for CPU	14	11.48
15:05:00 (5.0 min)	78	CPU + Wait for CPU	78	63.93
15:10:00 (3.8 min)	24	CPU + Wait for CPU	24	19.67

---

Back to Active Session History (ASH) Report
Back to Top

本节需要重点关注，可以对数据库整体分析。
回到顶部

ADDM Task ADDM:1924087814_1_561

          ADDM Report for Task 'ADDM:1924087814_1_561'          -------------------------------------------- Analysis Period---------------AWR snapshot range from 560 to 561.Time period starts at 08-MAR-23 02.58.53 PMTime period ends at 08-MAR-23 03.13.45 PM Analysis Target---------------Database 'H01U08PF' with DB ID 1924087814.Database version 19.0.0.0.0.ADDM performed an analysis of instance H01U08PF1, numbered 1 and hosted at racuat2f2ch01u08.ADDM detected that the system is a PDB. Activity During the Analysis Period-----------------------------------Total database time was 1289 seconds.The average number of active sessions was 1.45. Summary of Findings-------------------   Description                     Active Sessions      Recommendations                                   Percent of Activity      ------------------------------  -------------------  ---------------1  Top SQL Statements              1.18 | 81.97         52  Session Connect and Disconnect  .05 | 3.6            1  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~            Findings and Recommendations          ---------------------------- Finding 1: Top SQL StatementsImpact is 1.18 active sessions, 81.97% of total activity.---------------------------------------------------------SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement.    Recommendation 1: SQL Tuning   Estimated benefit is .4 active sessions, 27.87% of total activity.   ------------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "5b64sq2g9zj1a".      Related Object         SQL statement with SQL_ID 5b64sq2g9zj1a.         SELECT info.CARD_NUMBER,info.CARD_NAME,info.CARD_CODE,info.OPEN_BRANC         H,info.OPEN_BANK,audi.OPR_RESULT,         info.ACTIVE_FLAG,info.OPEN_DATE,info.EFFECT_DATE,info.EXPIRE_DATE,inf         o.CLIENT_ID,info.CARD_INTERNAL_NBR,         info.OPEN_OPERATE_TELLER,info.OPEN_AUTH_TELLER         FROM WCM_MNG_MAININFO_T info         LEFT JOIN WCM_APPLY_VERIFY_AUDIT_T audi on          audi.APPLY_REQ_ID=info.CARD_NUMBER and audi.AUDI_CODE='OPENCARDAUDIT'          and audi.AUDI_DESC='开户审核标记'         WHERE  info.OPEN_BANK = :1          and info.OPEN_DATE >= :2          and info.OPEN_DATE <= :3          and info.CARD_CODE in         (         :4          )         and info.ACTIVE_FLAG='N'          ORDER BY info.OPEN_BANK,info.OPEN_DATE,info.OPEN_BRANCH,info.CARD_NUM         BER         FETCH FIRST :5  ROWS ONLY   Rationale      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.       This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL       execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java       execution.   Rationale      SQL statement with SQL_ID "5b64sq2g9zj1a" was executed 253185 times and       had an average elapsed time of 0.00093 seconds.    Recommendation 2: SQL Tuning   Estimated benefit is .34 active sessions, 23.77% of total activity.   -------------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "dprdzbty156ux".      Related Object         SQL statement with SQL_ID dprdzbty156ux.         SELECT info.CARD_NUMBER,info.CARD_NAME,info.CARD_CODE,info.OPEN_BRANC         H,info.OPEN_BANK,audi.OPR_RESULT,         info.ACTIVE_FLAG,info.OPEN_DATE,info.EFFECT_DATE,info.EXPIRE_DATE,inf         o.CLIENT_ID,info.CARD_INTERNAL_NBR,         info.OPEN_OPERATE_TELLER,info.OPEN_AUTH_TELLER         FROM WCM_MNG_MAININFO_T info         LEFT JOIN WCM_APPLY_VERIFY_AUDIT_T audi on          audi.APPLY_REQ_ID=info.CARD_NUMBER and audi.AUDI_CODE='OPENCARDAUDIT'          and audi.AUDI_DESC='开户审核标记'         WHERE  (         (info.OPEN_DATE = :1  and info.OPEN_BRANCH = :2  and info.CARD_NUMBER          > :3 )         or (info.OPEN_DATE = :4  and info.OPEN_BRANCH > :5 )         or info.OPEN_DATE > :6          )         and info.OPEN_BANK = :7          and info.OPEN_DATE >= :8          and info.OPEN_DATE <= :9          and info.CARD_CODE in         (         :10          )         and info.ACTIVE_FLAG='N'          ORDER BY info.OPEN_BANK,info.OPEN_DATE,info.OPEN_BRANCH,info.CARD_NUM         BER         FETCH FIRST :11  ROWS ONLY   Rationale      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.       This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL       execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java       execution.   Rationale      SQL statement with SQL_ID "dprdzbty156ux" was executed 950 times and had       an average elapsed time of 0.27 seconds.    Recommendation 3: SQL Tuning   Estimated benefit is .26 active sessions, 18.03% of total activity.   -------------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "b2cxpv2ah9j3f".      Related Object         SQL statement with SQL_ID b2cxpv2ah9j3f.         SELECT * FROM (SELECT user_name,sql_id,count(1) as num from          v$open_cursor where user_name is NOT NULL group by user_name,sql_id          ORDER BY num DESC) WHERE rownum<=3   Rationale      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.       This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL       execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java       execution.   Rationale      SQL statement with SQL_ID "b2cxpv2ah9j3f" was executed 201 times and had       an average elapsed time of 1.1 seconds.    Recommendation 4: SQL Tuning   Estimated benefit is .12 active sessions, 8.2% of total activity.   -----------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "3y2d7rgcy1959".      Related Object         SQL statement with SQL_ID 3y2d7rgcy1959.         SELECT info.CARD_NUMBER,info.CARD_NAME,info.CARD_CODE,info.OPEN_BRANC         H,info.OPEN_BANK,'P' as OPR_RESULT,         info.ACTIVE_FLAG,info.OPEN_DATE,info.EFFECT_DATE,info.EXPIRE_DATE,inf         o.CLIENT_ID,info.CARD_INTERNAL_NBR,         info.OPEN_OPERATE_TELLER,info.OPEN_AUTH_TELLER         FROM WCM_MNG_MAININFO_T info         WHERE  (         (info.OPEN_DATE = :1  and info.OPEN_BRANCH = :2  and info.CARD_NUMBER          > :3 )         or (info.OPEN_DATE = :4  and info.OPEN_BRANCH > :5 )         or info.OPEN_DATE > :6          )         and info.OPEN_BANK = :7          and info.OPEN_DATE >= :8          and info.OPEN_DATE <= :9          and info.CARD_CODE in         (         :10          )         and info.ACTIVE_FLAG='Y'          ORDER BY info.OPEN_BANK,info.OPEN_DATE,info.OPEN_BRANCH,info.CARD_NUM         BER         FETCH FIRST :11  ROWS ONLY   Rationale      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.       This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL       execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java       execution.   Rationale      SQL statement with SQL_ID "3y2d7rgcy1959" was executed 999 times and had       an average elapsed time of 0.13 seconds.    Recommendation 5: SQL Tuning   Estimated benefit is .06 active sessions, 4.1% of total activity.   -----------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "6fyytvcmzzaxq".      Related Object         SQL statement with SQL_ID 6fyytvcmzzaxq.         select (             (select value from v$sysstat where name='user commits' )+         (select value from v$sysstat where name='user rollbacks' )) as value         from dual   Rationale      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.       This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL       execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java       execution.   Rationale      SQL statement with SQL_ID "6fyytvcmzzaxq" was executed 201 times and had       an average elapsed time of 0.22 seconds.  Finding 2: Session Connect and DisconnectImpact is .05 active sessions, 3.6% of total activity.------------------------------------------------------Session connect and disconnect calls were consuming significant database time.    Recommendation 1: Application Analysis   Estimated benefit is .05 active sessions, 3.6% of total activity.   -----------------------------------------------------------------   Action      Investigate application logic for possible reduction of connect and       disconnect calls. For example, you might use a connection pool scheme in       the middle tier.   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~           Additional Information          ---------------------- Miscellaneous Information-------------------------Wait class "Application" was not consuming significant database time.Wait class "Cluster" was not consuming significant database time.Wait class "Commit" was not consuming significant database time.Wait class "Concurrency" was not consuming significant database time.Wait class "Configuration" was not consuming significant database time.CPU was not a bottleneck for the DB on this instance.Wait class "Network" was not consuming significant database time.Wait class "User I/O" was not consuming significant database time.The network latency of the cluster interconnect was within acceptable limits of 1 milliseconds.Hard parsing of SQL statements was not consuming significant database time. 

Back to Top

End of Report