AWR报表解读-02

五、首要的SQL语句

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 124,799,294
• Captured SQL account for 30.2% of Total

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
22,491,837	195	115,342.75	18.02	41.73	98338.50	dc9bdxa7xpv8s	PL/SQL Developer	declare t_owner varchar2(30...
7,133,380	268	26,617.09	5.72	34.94	47123.31	c1jrhw45gfvkj	JDBC Thin Client	SELECT NULL AS table_cat, ...
4,041,821	16	252,613.81	3.24	6.83	21081.17	44mwxbkuxcrsa	PL/SQL Developer	SELECT /*+rule*/ S.TABLE_OWNER...
3,732,650	2,306	1,618.67	2.99	154.53	3474181.21	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
2,586,532	199,022	13.00	2.07	511.23	9197480.15	6s6scbnr6f83t	JDBC Thin Client	select roles0_.USERID as USERI...
2,054,318	12,673	162.10	1.65	50.45	356134.77	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
1,877,833	2,383	788.01	1.50	39.01	305891.77	7gtztzv329wg0		select c.name, u.name from co...
1,786,656	8,814	202.71	1.43	19.19	1296212.94	az33m61ym46y4		SELECT NULL AS table_cat, ...
1,725,058	162,928	10.59	1.38	33.67	6468004.54	0h6b2sajwb74n		select privilege#, level from ...
1,171,434	0		0.94	6.56	10.44	6mcpb06rctk0x	DBMS_SCHEDULER	call dbms_space.auto_space_adv...

如果磁盘读操作不是很高，但是缓冲区读操作很高而执行次数较低，说明查询可能使用了糟糕的索引，或者以错误的顺序执行了连接。

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
9,197,480	511	199,022	46.21	72828.36	6s6scbnr6f83t	JDBC Thin Client	select roles0_.USERID as USERI...
6,468,005	34	162,928	39.70	51215.57	0h6b2sajwb74n		select privilege#, level from ...
3,474,181	155	2,306	1506.58	27509.59	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
2,847,440	15	50,626	56.24	22546.87	0ws7ahf1d78qa		select SYS_CONTEXT('USERENV', ...
1,944,786	13	53,891	36.09	15399.39	0k8522rmdzg4k		select privilege# from sysauth...
1,623,748	4	13,926	116.60	12857.32	du7nyv586fquh	PL/SQL Developer	declare runtime_info sys.dbm...
1,359,553	51	13,319	102.08	10765.34	g64qbsqnjv0yv	JDBC Thin Client	select bizproject0_.ID as ID69...
1,296,213	19	8,814	147.06	10263.80	az33m61ym46y4		SELECT NULL AS table_cat, ...
1,244,139	5	50,623	24.58	9851.46	459f3z9u4fb3u		select value$ from props$ wher...
1,104,041	13	19,736	55.94	8742.13	db78fxqxwxt7r		select /*+ rule */ bucket, en...
1,051,807	4	50,391	20.87	8328.52	5ur69atw3vfhj		select decode(failover_method,...
1,027,609	42	199,259	5.16	8136.91	crf03umxqfz8n	JDBC Thin Client	select branchs0_.USERID as USE...
807,017	32	27,515	29.33	6390.20	7ng34ruy5awxq		select i.obj#, i.ts#, i.file#,...
614,561	2	45,806	13.42	4866.28	96g93hntrzjtr		select /*+ rule */ bucket_cnt,...
539,461	3	2,236	241.26	4271.61	2p5skbyu7hzmg	exp.exe	SELECT SOURCE FROM SYS....
491,025	4	116,866	4.20	3888.08	53saa2zkr6wc3		select intcol#, nvl(pos#, 0), ...
450,808	2	11,520	39.13	3569.63	803b7z0t84sq7		select job, nvl2(last_date, ...
443,960	14	197,043	2.25	3515.41	167x1z6377n6a	JDBC Thin Client	select ledgers0_.USERID as USE...

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
511	9,197,480	199,022	0.00	72828.36	6s6scbnr6f83t	JDBC Thin Client	select roles0_.USERID as USERI...
155	3,474,181	2,306	0.07	27509.59	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
131	8,525	1,128	0.12	67.50	a5n6w6u98cf1v	exp.exe	BEGIN :RET := SYS.DBMS_...
129	155	1,058	0.12	1.23	41ssv70uw2mkr	exp.exe	SELECT 1 FROM role_role_privs ...
51	1,359,553	13,319	0.00	10765.34	g64qbsqnjv0yv	JDBC Thin Client	select bizproject0_.ID as ID69...
50	356,135	12,673	0.00	2819.98	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
42	98,339	195	0.21	778.67	dc9bdxa7xpv8s	PL/SQL Developer	declare t_owner varchar2(30...
42	1,027,609	199,259	0.00	8136.91	crf03umxqfz8n	JDBC Thin Client	select branchs0_.USERID as USE...
39	305,892	2,383	0.02	2422.14	7gtztzv329wg0		select c.name, u.name from co...
36	321,015	5,315	0.01	2541.89	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
35	47,123	268	0.13	373.14	c1jrhw45gfvkj	JDBC Thin Client	SELECT NULL AS table_cat, ...
34	6,468,005	162,928	0.00	51215.57	0h6b2sajwb74n		select privilege#, level from ...
32	807,017	27,515	0.00	6390.20	7ng34ruy5awxq		select i.obj#, i.ts#, i.file#,...
19	1,296,213	8,814	0.00	10263.80	az33m61ym46y4		SELECT NULL AS table_cat, ...
19	127,601	2,938	0.01	1010.38	5ddayam7mxm74	JDBC Thin Client	select distinct sysmenupri0_.M...
17	12,287	27	0.63	97.29	bunssq950snhf		insert into wrh$_sga_target_ad...
15	2,847,440	50,626	0.00	22546.87	0ws7ahf1d78qa		select SYS_CONTEXT('USERENV', ...
14	443,960	197,043	0.00	3515.41	167x1z6377n6a	JDBC Thin Client	select ledgers0_.USERID as USE...

SQL ordered by Reads

• Total Disk Reads: 345,050
• Captured SQL account for 9.8% of Total

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
12,783	2,306	5.54	3.70	154.53	3474181.21	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
10,064	10	1,006.40	2.92	0.88	69.13	dmnntnpc9mnnq		SELECT SEV.ROWID FROM MGMT_SEV...
10,048	9	1,116.44	2.91	0.86	68.62	crqc0d6r83p1r		BEGIN EM_SEVERITY.SEVERITY_PUR...
6,556	2	3,278.00	1.90	0.70	551.77	cm60fmwcnkfuj		select oshistorys0_.ID as ID0_...
3,899	19,736	0.20	1.13	12.98	1104041.42	db78fxqxwxt7r		select /*+ rule */ bucket, en...
1,743	18,151	0.10	0.51	5.04	253895.15	cvn54b7yz0s8u		select /*+ index(idl_ub1$ i_id...
1,463	2,236	0.65	0.42	2.87	539460.67	2p5skbyu7hzmg	exp.exe	SELECT SOURCE FROM SYS....
1,272	5,315	0.24	0.37	36.25	321014.56	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
869	679	1.28	0.25	0.96	1858.11	0ybwd63u2any5		insert into sys.wri$_optstat_h...
685	3	228.33	0.20	2.34	9.61	8cz8whpyr6k4x	DBMS_SCHEDULER	call PACK_RISK_DYNAMICALLY.SP_...

SQL ordered by Executions

• Total Executions: 4,063,357
• Captured SQL account for 61.3% of Total

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
199,259	610,728	3.06	0.00	5.16	crf03umxqfz8n	JDBC Thin Client	select branchs0_.USERID as USE...
199,022	5,086,739	25.56	0.00	46.21	6s6scbnr6f83t	JDBC Thin Client	select roles0_.USERID as USERI...
197,043	318,443	1.62	0.00	2.25	167x1z6377n6a	JDBC Thin Client	select ledgers0_.USERID as USE...
188,176	188,175	1.00	0.00	2.02	1r3tpapvur4aq	JDBC Thin Client	select bizstate0_.ID as ID77_0...
162,928	1,394,780	8.56	0.00	39.70	0h6b2sajwb74n		select privilege#, level from ...
146,635	146,627	1.00	0.00	2.34	fwuaq5spq8d4g	JDBC Thin Client	select sysdepartm0_.ID as ID10...
122,289	122,289	1.00	0.00	2.96	3az8w1f6563w6	JDBC Thin Client	select sysuser0_.ID as ID33_0_...
116,866	125,148	1.07	0.00	4.20	53saa2zkr6wc3		select intcol#, nvl(pos#, 0), ...
53,891	163,854	3.04	0.00	36.09	0k8522rmdzg4k		select privilege# from sysauth...
50,626	50,626	1.00	0.00	56.24	0ws7ahf1d78qa		select SYS_CONTEXT('USERENV', ...
50,623	50,623	1.00	0.00	24.58	459f3z9u4fb3u		select value$ from props$ wher...
50,391	50,391	1.00	0.00	20.87	5ur69atw3vfhj		select decode(failover_method,...
45,806	41,916	0.92	0.00	13.42	96g93hntrzjtr		select /*+ rule */ bucket_cnt,...

SQL ordered by Parse Calls

• Total Parse Calls: 1,426,390
• Captured SQL account for 59.8% of Total

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
162,903	162,928	11.42	0h6b2sajwb74n		select privilege#, level from ...
53,867	53,891	3.78	0k8522rmdzg4k		select privilege# from sysauth...
50,625	50,626	3.55	0ws7ahf1d78qa		select SYS_CONTEXT('USERENV', ...
50,623	50,623	3.55	459f3z9u4fb3u		select value$ from props$ wher...
50,368	50,391	3.53	5ur69atw3vfhj		select decode(failover_method,...
18,529	18,542	1.30	39m4sx9k63ba2		select /*+ index(idl_ub2$ i_id...
18,527	18,542	1.30	c6awqs517jpj0		select /*+ index(idl_char$ i_i...
18,134	18,151	1.27	cvn54b7yz0s8u		select /*+ index(idl_ub1$ i_id...
18,120	18,137	1.27	ga9j9xk5cy9s0		select /*+ index(idl_sb4$ i_id...
14,237	29,980	1.00	83taa7kaw59c1		select name, intcol#, segcol#,...

 

在10g中，v$sql显示使用相同语句的多个用户的SQL，V$SQL_PLAN_STATISTICS_ALL显示连接计划和统计数据，

V$SQL_PLAN_STATISTICS显示

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23754390/viewspace-689776/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23754390/viewspace-689776/