log file sync的再次思考

    昨天系统整体慢，最终定位到是数据库层面的问题。在TOP的等待事件中可以看到log file sync平均等待达到了157，根据之前的经验这个值超过20就有性能问题。

    如果按照以前的诊断思路，就是认定服务器的IO有问题，建议用IO快一点的存储。

    不过通过这次问题的解决，思路有些变化，发生log file sync等待事件过高说明服务器IO出现瓶颈，这是没有问题的。如果此服务器IO一直没有问题，就今天出了问题，可能是SQL导致，接着分析Segments by Physical Reads，可以看到是查询那些表的无力度高。再通过这些表找到对应的SQL优化。

    本次的优化就是优化SQL后，整个系统的物理读降下来了，log file sync也降到了5。

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
gg-sjk-01	AIX-Based Systems (64-bit)	128	32		240.00

	Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	5446	25-Nov-15 15:00:42	1084	138.8	2
End Snap:	5447	25-Nov-15 16:00:11	1086	131.4	2
Elapsed:		59.49 (mins)
DB Time:		6,185.41 (mins)

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
enq: TX - row lock contention	4,895	88K	17971	23.7	Application
direct path read	450,235	59.7K	133	16.1	User I/O
gc buffer busy acquire	996,236	37.3K	37	10.0	Cluster
rdbms ipc reply	82,075,609	26.3K	0	7.1	Other
db file sequential read	652,678	22.8K	35	6.1	User I/O
db file scattered read	828,115	20.9K	25	5.6	User I/O
DB CPU		20K		5.4
read by other session	946,402	18.5K	20	5.0	User I/O
log file sync	103,436	16.3K	157	4.4	Commit
gc cr block busy	73,695	13.1K	178	3.5	Cluster

Segments by Physical Reads

• Total Physical Reads: 167,906,170
• Captured Segments account for 98.3% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
L_SYS	L_SYS_DATA	RU_DONE_TASK_MISS		TABLE	71,893,921	42.82
L_SYS	L_SYS_DATA	USER_VISIT		TABLE	36,013,367	21.45
L_SYS	L_SYS_DATA	RU_DONE_TASK_MRTN		TABLE	25,627,235	15.26
L_Z	L_Z_DATA	PURCHASE_ITEM_MV		TABLE	16,697,719	9.94
L_Z	L_Z_DATA	OPERATION_HISTORY		TABLE	12,571,365	7.49