db file sequential read问题查找

AWR报告：

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
DB CPU		13.8K		83.6
db file sequential read	114,066	604.4	5	3.7	User I/O

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

User I/O Time (s)	Executions	UIO per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
140.87	31	4.54	14.84	282.70	52.01	49.83	fq0u1m9s43ppx	JDBC Thin Client	select * from ( select this_.R...
107.55	5	21.51	11.33	193.50	47.64	55.58	3mv4n3uxbpysj	JDBC Thin Client	select trade_no from webdata.t...

%Total:IO消耗的比较高的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 as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 518,951,502
• Captured SQL account for 69.1% of Total

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
43,960,324	26,947	1,631.36	8.47	592.46	99.8	0	2z8n38yn7ms0w	JDBC Thin Client	select theatresea0_.RECORDID a...
43,766,205	21,911	1,997.45	8.43	597.66	99.8	0	9avw3cauzpg89	JDBC Thin Client	select this_.RECORDID as RECOR...
29,284,185	607	48,244.13	5.64	275.19	97.3	0	ducq9dxv0knbu	JDBC Thin Client	select * from ( select this_.R...
20,190,246	16,025	1,259.92	3.89	384.72	100	0	bs4vy1pyx7b1k	JDBC Thin Client	select * from ( select row_.*,...
20,188,983	16,025	1,259.84	3.89	136.41	99.7	0	24xmjhryguucv	JDBC Thin Client	select count(*) as y0_ from WE...
15,273,497	12,133	1,258.84	2.94	97.30	95.2	0	cwqt5max2byfd	JDBC Thin Client	select this_.RECORDID as RECOR...
14,200,901	45,154	314.50	2.74	159.20	97.1	.1	8149h2gbb9zhh	JDBC Thin Client	select to_char(count(*)) as co...
14,001,355	109,758	127.57	2.70	405.67	99.8	0	022n8179y1xfk	JDBC Thin Client	select this_.RECORDID as y0_ f...
13,471,860	3,974	3,390.00	2.60	63.82	99.9	0	btgs5y7hr6s84	JDBC Thin Client	select * from ( select this_.R...
12,931,659	31	417,150.29	2.49	282.70	52	49.8	fq0u1m9s43ppx	JDBC Thin Client	select * from ( select this_.R...
11,472,200	595	19,281.01	2.21	123.49	99.5	0	94vtqyr5npkk1	JDBC Thin Client	select count(*) as y0_ from WE...
10,613,816	220	48,244.62	2.05	161.81	88.9	0	3nuz0p2t6kyhr	JDBC Thin Client	select * from ( select this_.R...
9,893,817	5	1,978,763.40	1.91	193.50	47.6	55.6	3mv4n3uxbpysj	JDBC Thin Client	select trade_no from webdata.t...
9,775,484	22,510	434.27	1.88	90.97	85.4	1.6	4vsbdpv6chjps	JDBC Thin Client	select openplayit0_.RECORDID a...
8,573,197	6	1,428,866.17	1.65	44.19	98.5	0	01zfpt15ma767	JDBC Thin Client	select openplayit0_.RECORDID a...

Gets per Exe的值比较大的，通常会涉及到全表扫描

查看执行计划：
select * from table(dbms_xplan.display_cursor('fq0u1m9s43ppx'));
select * from table(dbms_xplan.display_cursor('3mv4n3uxbpysj'));


获取SQL绑定的值
select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from v$sql_bind_capture t
  where sql_id = '2f7m2ac620crt';

真实执行SQL查看
set autotrace on;
select ...

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

转载于:http://blog.itpub.net/91975/viewspace-2085563/