剪不断 发表于 2014-3-20 10:19:42

AWR报告求分析

环境：windows server 2003 64bit
oracle版本：10.2.0.1 32bit
表现：程序刷新列表速度慢

yrg5101 发表于 2014-3-20 10:34:30

感觉库整体不忙，看到select condition from cdef$ where rowid=:1 的解析执行比为1:1 而且解析次数蛮多的，可以 看看什么原因，能不能减少软解析数。还有你说的刷新列表慢，能查到对应的语句吗？

剪不断 发表于 2014-3-20 10:37:18

yrg5101 发表于 2014-3-20 10:34
感觉库整体不忙，看到select condition from cdef$ where rowid=:1 的解析执行比为1:1 而且解析次数蛮多的 ...

可以查到语句，

xifenfei 发表于 2014-3-20 14:27:35

这个库的负载相对较低：

DB NameDB IdInstanceInst numReleaseRACHost
SPIRIT2938226026spirit110.2.0.1.0NOADMIN



Snap IdSnap TimeSessionsCursors/Session
Begin Snap:1766219-3ÔÂ -14 14:00:221036.3
End Snap:1766319-3ÔÂ -14 15:00:201186.2
Elapsed: 59.97 (mins)
DB Time: 14.70 (mins)



Cache Sizes

BeginEnd
Buffer Cache:1,048M1,072MStd Block Size:8K
Shared Pool Size:128M104MLog Buffer:6,968K

Load Profile

Per SecondPer Transaction
Redo size:1,710.527,231.84
Logical reads:2,903.8712,277.17
Block changes:4.3918.54
Physical reads:178.80755.96
Physical writes:0.632.68
User calls:20.5786.98
Parses:18.3477.55
Hard parses:0.482.04
Sorts:1.516.37
Logons:0.080.32
Executes:19.1480.93
Transactions:0.24



% Blocks changed per Read:0.15Recursive Call %:84.55
Rollback per transaction %:3.76Rows per Sort:1374.49


逻辑读，物理读相对来说，都比较小

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:99.96Redo NoWait %:100.00
Buffer Hit %:94.16In-memory Sort %:100.00
Library Hit %:97.06Soft Parse %:97.37
Execute to Parse %:4.17Latch Hit %:99.96
Parse CPU to Parse Elapsd %:94.77% Non-Parse CPU:97.01

Shared Pool Statistics

BeginEnd
Memory Usage %:86.0298.43
% SQL with executions>1:92.0988.47
% Memory for SQL w/exec>1:92.3193.29

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time47854.3
db file sequential read143,776225225.5User I/O
db file scattered read41,451164418.6User I/O
SQL*Net more data to client367,4374204.8Network
direct path read14,6621311.5User I/O
1. io相关等待占据了为数据库的主要等待，buffer cache命中率也不是很高(只有94%），而且内存本身分配也较小，建议考虑稍微增加一些sga，提高命令率，减少一些io等待时间，提高程序效率


Physical ReadsExecutionsReads per Exec%TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
62,507183,472.619.7253.0897.640xj7h1x9phsbjECG_Expert25.exeSELECT * FROM (SELECT a.*, ro...
46,085232,003.707.1694.49132.94gdtm1r1629fd9ECG_Expert25.exeSELECT * FROM (SELECT a.*, ro...
40,56194,506.786.3029.7660.16d93yxmpruwcjgECG_Expert25.exeSELECT * FROM (SELECT a.*, ro...
26,13646,534.004.063.3913.673arcmh417utwwECG_Expert25.exeSELECT LOG.*, LOG."ROWID" FR...
21,50154,300.203.343.9213.98842cs13wsm6ycExamStation.exeSELECT LOG.*, LOG."ROWID" FR...
13,41552,683.002.093.7012.230xbz627axvju4ExamStation0608.exeSELECT LOG.*, LOG."ROWID" FR...
11,071111,071.001.720.845.78bh73qvm1dtjv5EdanHolter.exeSELECT LOG.*, LOG."ROWID" FR...
10,11916632.441.574.0015.54cns19xuhp8g4ctj.exeSELECT "ABC"."V_PHYSICAL"."...

2. 考虑对逻辑读物理读大的sql进行优化，减小逻辑读物理读值，提高sql执行效率，这里看到很多sql执行基本上10秒以上，确实有点慢

剪不断 发表于 2014-3-20 15:55:40

xifenfei 发表于 2014-3-20 14:27
这个库的负载相对较低：




谢谢飞总指导！！
页: [1]
查看完整版本: AWR报告求分析