AWR之:SQL ordered by User I/O Wait Time

这几天在做AWR报告分析时，忽然发现在报告中SQL部分增加了几个新的内容，现在的列表包括：

• 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
• Complete List of SQL Text

这其中User I/O 和 Physical Reads(UnOptimized)部分是Oracle 11gR2中新增加的，而且幸运的是，当我们通过AEWRLOAD.sql将10g的AWR数据加载到11gR2的数据库中时，这部分信息同样能够被展现出来。

SQL ordered by User I/O Wait Time帮助我们找到那些消耗最多I/O等待时间的SQL查询，以下是一个示范输出：

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 78.2% of Total User I/O Wait Time (s): 167,727
• Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 167,727

User I/O Time (s)	Executions	UIO per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
22,780.47	12	1,898.37	13.58	25,162.65	2.24	90.53	3mdwt0vbm1h7v		SELECT SDSO15, SDFRGD, SDFUC, ...
15,310.87	8	1,913.86	9.13	16,537.85	2.67	92.58	83za26ynm7sn8		SELECT ILKCO, ILUKID, ILLOTN, ...
10,787.33	5	2,157.47	6.43	11,602.32	2.66	92.98	f5s0wxsx32jz3	ORACLE.EXE	SELECT "A1"."SDTRDJ", "A1"."SD...
10,515.02	11	955.91	6.27	10,686.33	2.01	98.40	b26uyypzm63ps		INSERT INTO PRODDTA.F554111L(T...
9,437.84	6	1,572.97	5.63	10,016.45	3.89	94.22	2a4x9zud41btv		SELECT ILKCO, ILUKID, ILLOTN, ...
8,720.13	4	2,180.03	5.20	9,291.57	3.29	93.85	0cry9wsbj3yjp	jdenet_k.exe	SELECT SDPRAN8 FROM PRODDTA.F4...
7,880.64	1	7,880.64	4.70	7,960.22	1.86	99.00	4gyhv1tu7cqd3		INSERT INTO PRODDTA.F554111X (...
5,790.45	1	5,790.45	3.45	5,832.77	2.04	99.27	1cqws9u14ayd5		SELECT SLDGL, SLLNTY, SLNXTR, ...
4,224.36	1	4,224.36	2.52	4,249.62	2.27	99.41	1smht09f1q2pt		SELECT SLDGL, SLLNTY, SLNXTR, ...
3,081.17	0		1.84	3,358.95	1.96	91.73	4nmku6h62j2p1		SELECT GLJBCD, GLJBST, GLUSER,...
2,588.41	3	862.80	1.54	2,614.65	1.39	99.00	1q3svk26r7ta4		SELECT SDSO15, SDFRGD, SDFUC, ...
2,124.71	1	2,124.71	1.27	2,776.07	2.54	76.54	ahcm9d23yvvpg		SELECT T0.SDDCTO, T0.SDUOPN, T...
2,107.62	2	1,053.81	1.26	2,110.95	3.82	99.84	f2xft4f1fbbvf		SELECT ILKCO, ILUKID, ILLOTN, ...
2,035.91	1	2,035.91	1.21	2,650.37	2.37	76.82	drhb7uvyqgcyx		SELECT ILKCO, ILUKID, ILLOTN, ...
2,014.26	2	1,007.13	1.20	2,033.23	1.54	99.07	a6zj1yjc05bg7	ORACLE.EXE	SELECT SUM("A1"."RPAG")/100 FR...
1,752.87	1	1,752.87	1.05	1,757.86	2.75	99.72	0cr0zm356mjzb	jdenet_k.exe	SELECT * FROM PRODDTA.F4111 WH...
1,749.27	73,862	0.02	1.04	1,772.38	3.96	98.70	8m6prp6zgr0dg	runbatch.exe	SELECT T0.PRMATC, T0.PRAN8, T0...
1,686.59	1	1,686.59	1.01	2,152.18	2.36	78.37	gpnxfz34ubzbk		SELECT ILKCO, ILUKID, ILLOTN, ...

这些细致入微的微小变更，都使得Oracle更接近用户。