db file sequential read

zm_tree

--系统：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值很高，

不能很确定原因所和无法下手处理？麻烦解析一下，谢谢~

zm_tree

此节点的ADDM报告

zm_tree

其他两个节点的awr和addm

Maclean Liu(刘相兵

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

iloracle

PGA是不是也要调大啊

zm_tree

谢谢刘大的解决思路