db file sequential read
--系统：hp-unix B.11.31--数据库为rac
--数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--症状:
awr报告里db file sequential read很高：%DB time 32.21，有时候到50%
--我的思路：
awr内容：
index crx upgrade (positioned) 60 0.02 0.00
index crx upgrade (prefetch) 1 0.00 0.00
index fast full scans (full) 4 0.00 0.00
index fetch by key 79,554 22.01 0.05
index scans kdiixs1 109,869,941 30,395.22 68.18--索引全扫面很高
--Segments by Global Cache Buffer Busy及Segments by CR Blocks Received可知
IDX01_T_EXP_PACKAGE_TRACE及IDX01_T_EXP_TRUCK_TRACE的 capture值很高，
不能很确定原因所和无法下手处理？麻烦解析一下，谢谢~ 此节点的ADDM报告 其他两个节点的awr和addm
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 12.4% of Total User I/O Wait Time (s): 2,251
Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 2,251
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
89.58 413,968 0.00 3.98 275.31 62.99 32.54 c6pqryyuy5xzq JDBC Thin Client SELECT OP_TIME, WAYBILL_NO, TR...
41.34 27,964 0.00 1.84 107.92 56.58 38.30 5a0yy97mhdftq JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
34.00 16,652 0.00 1.51 76.10 51.86 44.68 62tf2r771bzbw JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
27.26 12,918 0.00 1.21 61.42 51.76 44.39 6kywcxujcb73y JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
21.11 7,492 0.00 0.94 42.00 46.35 50.25 51980j358mb03 JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
15.61 31,178 0.00 0.69 83.83 74.84 18.62 4t9pr7x31mb1k JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
9.47 2,519 0.00 0.42 17.39 42.03 54.43 9nfdg5c8529xq JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
9.44 40,033 0.00 0.42 85.96 83.34 10.98 dch6107gztrc4 JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
8.66 4,166 0.00 0.38 20.66 54.31 41.90 6zrp153qubq7x JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
3.86 28,763 0.00 0.17 56.23 85.58 6.86 1n11a3x3hmzhh JDBC Thin Client SELECT OP_TIME, OP_ORG_NAME, O...
UIO per Exec (s) 几乎都等于0
就Buffer Pool Advisory看 随着Size Factor 的增大 Est Phys Read Factor 较为线性地减小
Begin End
Host Mem (MB): 130,899.8 130,899.8
SGA use (MB): 47,149.7 47,149.7
PGA use (MB): 3,317.9 3,537.9
% Host Mem used for SGA+PGA: 38.55 38.72
1、 整体负载不高
2、 整体响应速度 并不慢
3、 db file sequential read的出现是合理地
4、 增大buffer cache是可以有效减少User Io wait time的，即db file sequential read
5、就host mem看，可以支持增大一部分buffer cache PGA是不是也要调大啊 谢谢刘大的解决思路
页:
[1]