自己的总结，哪里不足之处希望各位大侠指出，共同学习。同时也希望里面的某些内容给各位puber者带来些帮助，谢谢！ 关注点一：Elasped Time: It represents the snapshot window or the time between the two snapshots.DB TIME: Represents the activity on the database. If DB TIME is Greater than Elapsed Time then it means that database has high workload.关注点二：load profile也是一个重要的关注点Redo size – Indicates the amount of DMLactivity happening in the database. User calls - Indicates how many user calls have occurred during the snapshot period. This value can give you some indication if usage has increased. Parses and hard parses - Provides an indication of the efficiency of SQL re-usage.?Sorts - Number of sorts occurring in the database. 关注点三：sql的命中率也就是Instance Efficiency部分 这个要是结合后面的Instance Activity Statistics 会比较好Buffer NowaitBufferHit Library HitRedo NoWait In-memory Sort Soft Parse Latch Hit Non-Parse CPU 其他的都好理解，下面的这个说明一下% SQL with executions>1 value should also be high, a low value could mean thatThe database is not using shared SQL statements which in turn could mean thatBind variables are not being used.关注点四：等待事件是另外一个关注点If there are events like TX – row lock contention, Latch Free then thatmeans there is contention in your database. If there is a high log file syncevent then check why is it happening. A possible solution for this problem isto increase the size of the redo log buffer and to move your Logfiles to a storagesystem where Disk I/O capacity is high.Also, The db file sequential read(which means index reads) should be higher ascompared to db file scattered read(which means full table scans).关注点五：在rac环境中有一个关注点Global Cache的相关的一些情况From the perspective of performance tuning the amount of block transferacross the interconnect should be low in comparison to localized access.This can be seen in the "Global Cache Efficiency Percentages" section. In the "Global Cache and Enqueue Services" the upper limit forAvg global cache cr block receive time should be 4 andAvg global cache current block receive time should be 8.关注点六（信息）：这个一点不像其他那么重要，但也是有助于我们分析.Time Model Statistics 这个要和Wait Class、Wait Events结合来看 Time related statistics presents the various operations which are consuming most of the database time.消耗数据库大部分时间的操作If Hard parses or parsing time is very high then further investigationshould be done to resolve the problem.parse time elapsed 181,627.22 60.01 hard parse elapsed time 152,972.22 50.54 sql execute elapsed time这里查看不是解析有问题。
Avg
%TimeTotal WaitwaitWaitsEvent
Waits-outs Time (s)(ms)/txn---------------------------- -------------- ------ ----------- ------- ---------control file parallel write 1,220 .0 1815 1.6control file sequential read
6,508 .0 6 18.7CGS wait for IPC msg
422,253100.0 1 0566.0change tracking file synchro
60 .0 1130.1db file parallel write
291 .0 0 10.4db file sequential read
90 .0 0 40.1reliable message
136 .0 0 10.2log file parallel write
106 .0 0 20.1lms flush message acks
1 .0 0600.0gc current block 2-way
200 .0 0 00.3change tracking file synchro
59 .0 0 10.1In this example our control file parallel write waits (which occurs during writes to the control file) are taking up 18 seconds total, with an average wait of 15 milliseconds per wait. Additionally we can see that we have 1.6 waits per transaction (or 15ms * 1.6 per transaction = 24ms).关注点七（信息）：操作系统的信息Operating System Statistics NUM_CPUSfor example, we have 4 CPU's on the box.关注点八：Buffer Pool Advisory这个是不要和sga的分配有关系，这个都是oracle内部给出的建议。这部分我们主要关注两个参数Size Factor和Est Phys Read Factor这两个参数在同一列都为1时，分配SGA还是比较合理的。Size Factor为1时对应的Size for Est (M)为当前所分配的sga的大小关注点九：latch相关的问题可以关注如下信息Latch Activity From this report you can determine if Oracle is suffering from latching problems, and if so, which latches are causing the greates amount of contention on the system.这里主要关注两个参数Pct Get Miss和Wait Time (s)进一步了解latch的信息可以通过下面的两部分来去了解Latch Sleep Breakdown和Latch Miss Sources关注点十：如果有和SGA相关的性能问题，可用相信的来查看sga具体分配到哪里了。SGA breakdown difference
|
