AWR报告求分析

剪不断

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

yrg5101

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

剪不断

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

可以查到语句，

xifenfei

这个库的负载相对较低：

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
SPIRIT	2938226026	spirit	1	10.2.0.1.0	NO	ADMIN

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	17662	19-3ÔÂ -14 14:00:22	103	6.3
End Snap:	17663	19-3ÔÂ -14 15:00:20	118	6.2
Elapsed:		59.97 (mins)
DB Time:		14.70 (mins)

Cache Sizes

	Begin	End
Buffer Cache:	1,048M	1,072M	Std Block Size:	8K
Shared Pool Size:	128M	104M	Log Buffer:	6,968K

Load Profile

	Per Second	Per Transaction
Redo size:	1,710.52	7,231.84
Logical reads:	2,903.87	12,277.17
Block changes:	4.39	18.54
Physical reads:	178.80	755.96
Physical writes:	0.63	2.68
User calls:	20.57	86.98
Parses:	18.34	77.55
Hard parses:	0.48	2.04
Sorts:	1.51	6.37
Logons:	0.08	0.32
Executes:	19.14	80.93
Transactions:	0.24

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

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

Instance Efficiency Percentages (Target 100%)

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

Shared Pool Statistics

	Begin	End
Memory Usage %:	86.02	98.43
% SQL with executions>1:	92.09	88.47
% Memory for SQL w/exec>1:	92.31	93.29

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		478		54.3
db file sequential read	143,776	225	2	25.5	User I/O
db file scattered read	41,451	164	4	18.6	User I/O
SQL*Net more data to client	367,437	42	0	4.8	Network
direct path read	14,662	13	1	1.5	User I/O

1. io相关等待占据了为数据库的主要等待，buffer cache命中率也不是很高(只有94%），而且内存本身分配也较小，建议考虑稍微增加一些sga，提高命令率，减少一些io等待时间，提高程序效率

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
62,507	18	3,472.61	9.72	53.08	97.64	0xj7h1x9phsbj	ECG_Expert25.exe	SELECT * FROM (SELECT a.*, ro...
46,085	23	2,003.70	7.16	94.49	132.94	gdtm1r1629fd9	ECG_Expert25.exe	SELECT * FROM (SELECT a.*, ro...
40,561	9	4,506.78	6.30	29.76	60.16	d93yxmpruwcjg	ECG_Expert25.exe	SELECT * FROM (SELECT a.*, ro...
26,136	4	6,534.00	4.06	3.39	13.67	3arcmh417utww	ECG_Expert25.exe	SELECT LOG.*, LOG."ROWID" FR...
21,501	5	4,300.20	3.34	3.92	13.98	842cs13wsm6yc	ExamStation.exe	SELECT LOG.*, LOG."ROWID" FR...
13,415	5	2,683.00	2.09	3.70	12.23	0xbz627axvju4	ExamStation0608.exe	SELECT LOG.*, LOG."ROWID" FR...
11,071	1	11,071.00	1.72	0.84	5.78	bh73qvm1dtjv5	EdanHolter.exe	SELECT LOG.*, LOG."ROWID" FR...
10,119	16	632.44	1.57	4.00	15.54	cns19xuhp8g4c	tj.exe	SELECT "ABC"."V_PHYSICAL"."...

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

剪不断

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

谢谢飞总指导！！