Oracle Version: 11.1.0.7.0
我们在一个Oracle RAC实例中有更高的IO等待
一个SQL的执行时间很长——每次执行1452.57秒。有一天，事情突然发生了。以前，查询20k(：v4参数)记录最多需要3-4分钟
subscribeinfo记录：5900万(非并行)
计费记录：2k-3k
SQL
select o.msisdn, o.spid, o.serviceid , o.ChargeReferenceID,
o.channelID, o.nextchargetime , o.failtimestamp, o.lastmonfeeday,
o.networkId, o.retryEndDateTime, o.trialType, o.subFlag, o.faultCode
from subscribeinfo o, chargerate r where (o.monthbillid = :v1) and
(((o.state = :"SYS_B_00") and (o.nextchargetime < :v2) and
((o.IsAutoExtend <> :"SYS_B_01") or ((o.IsAutoExtend = :"SYS_B_02")
and (o.extendflag <> :"SYS_B_03")))) or (o.subFlag = :"SYS_B_04" and
o.state = :"SYS_B_05" and o.retryenddatetime > :v2)) and
(o.ChargeClassForSub = r.chargeclassidx) and ((r.chargemode =
:"SYS_B_06" and r.activetype = :"SYS_B_07" and o.nextchargetime !=
:"SYS_B_08" ) or ( r.chargemode = :"SYS_B_09" and r.activetype <>
:"SYS_B_10") or (r.chargemode >= :"SYS_B_11" and r.chargemode <=
:"SYS_B_12" and r.basecharge >= :"SYS_B_13") or (r.chargemode =
:"SYS_B_14") or (r.chargemode = :"SYS_B_15") or (r.chargemode =
:"SYS_B_16") ) and (o.failtimestamp <= :v3) and (rownum <= :v4)
根据AWR报告，前5个定时前景事件
Direct path read [ Avg Wait Time: 22 s, %DB Time: 50.75% ]
DB file sequential read [ Avg Wait Time: 15 s, %DB Time: 38.00 ]
我将无法发布完整的AWR报告，因为它受到限制。所以请询问详细信息，我会发布
解释计划
ID Exec Ord Operation Go To More Peek Bind Capt Bind Cost2 Estim
Card LAST Starts LAST Output Rows LAST Over/Under
Estimate1 PStart PStop Work Area 0 7 SELECT STATEMENT
23335 1 2577 1 6 COUNT STOPKEY [+] [+]
[+] 23335 1 2577 2 5 . HASH JOIN [+] [+]
[+] 23335 20001 1 2577 8x over [+] 3 1 .. TABLE ACCESS FULL
CHARGERATE [+] [+] 68 3035 1 3036 1x 4 4 .. PARTITION LIST
SINGLE [+] 23266 25223 1 2577 10x over KEY KEY 5 3 ... TABLE
ACCESS BY LOCAL INDEX ROWID SUBSCRIBEINFO [+] [+] [+]
[+] 23266 25223 1 2577 10x over KEY KEY 6 2 .... INDEX RANGE SCAN
IDX_FAILTIMESTAMP_NEW [+] [+] [+] [+] 2435 1 2100765 KEY KEY
IOSTAT
Linux 2.6.16.46-0.12-smp (mdspdb01) 11/16/12
avg-cpu: %user %nice %system %iowait %steal %idle
8.41 0.00 9.38 13.25 0.00 67.67
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 5.71 39.53 121.79 665679995 2051190222
sdb 85.75 178.15 171.12 3000316741 2881953582
sdc 111.05 161.69 43.96 2723201251 740429949
我们为字段monthbillid、nextchargetime和failtimestamp创建了一个索引。。。尽管基数提高了1/6，但成本增加了4-5倍。但oracle默认采用新索引
create index IDX_MONTHBILLQUERY on subscribeinfo(monthbillid,
nextchargetime, failtimestamp) local tablespace IMUSE_INDEX;
dbms_stats.gather_index_stats('IMUSE01', 'IDX_MONTHBILLQUERY');
我们在AWR报告中有硬解析=0。我们还更改了cursor_sharing=FORCE
现在IO处于控制之下。还是觉得，这不是根本原因。此外，我们还让该实例专门用于该查询，该查询每小时发生10多次，检索2万条记录大约需要100秒。
有人能建议我将优化器模式作为first_rows还是使用提示first_rows(20000)是一个好决定吗？
截至目前，我们已禁用统计工作；我们可以只对某些表或某些索引启用相同的功能吗。这可能吗？
问题是该语句导致了超过50000次磁盘读取。这可能是由使用游标共享引起的。如果应用程序是在不使用绑定变量的情况下编码的(非常糟糕。不要走，运行以修复该应用程序)，通常会使用此参数。可能您甚至将cursor_sharing设置为force，这可能会产生与所描述的效果类似的不良效果，并且在大多数情况下，游标窥视也不起作用。
您可以通过指定提示来避免完整的表扫描，这取决于您是否在所需的表上有索引。由于你没有描述这一点，所以不可能给你任何具体的建议。
问题解决了。。。。。游标共享用于强制。。。这大大降低了IO。现在IO在所有情况下都是正常的。然后，我们为sqltuningadvisor推荐的同一查询创建了两个索引，并接受了配置文件
2-SQL配置文件查找(请参阅下面的解释计划部分)--------------------------------------------------------为此语句找到了可能更好的执行计划。
建议(预计效益：80.44%)
请考虑接受推荐的SQL配置文件。执行dbms_sqltune.accept_sql_profile(task_name=>'my_sqltune_task1'，task_owner=>'IMUSE01'，replace=>TRUE)；
验证结果------------------已测试SQL配置文件通过执行其计划和原始计划，并衡量其各自的执行统计信息。计划可能只是部分如果另一个可以在更短的时间内运行完成，则执行。
Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: PARTIAL COMPLETE Elapsed时间(毫秒)：31479 8049 74.43%CPU时间(毫秒)：5172 1656 67.98%
用户I/O时间(毫秒，16367 3422 79.09%
缓冲区获取：265365 51818 80.47%
磁盘读取：3227 524 83.76%
直接写入：0行已处理：0 20000获取：
0 20000执行：0
13-索引查找(请参阅下面的解释计划部分)--------------------------------------------------可以通过创建一个或多个
索引来改进此语句的执行计划。建议(预计效益：81.1%)
考虑运行Access Advisor以改进物理模式设计或者创建推荐的索引。在上创建索引IMUSE01.IDX$$_67E5B0001IMUSE01.SUBSCRIBEINFO("STATE"、"SUBFLAG"、"MONTHBILLID"、"RETRYENDDATETIME")；
考虑运行Access Advisor以改进物理架构设计或者创建推荐的索引。在上创建索引IMUSE01.IDX$$_67E5B0002IMUSE01.SUBSCRIBEINFO("STATE"、"MONTHBILLID"、"FAILTIMESTAMP")；
基本原理---------创建推荐的索引可显著改进执行计划本声明。但是，最好运行"Access Advisor"使用具有代表性的SQL工作负载，而不是使用单个语句。这将允许获得综合指数建议帐户索引维护开销和额外的空间消耗。
4-重构SQL查找(请参阅解释计划部分中的计划1)----------------------------------------------------------------谓词"O"。"NEXTCHARGETIME"<>：B1用于执行的第5行plan是索引列"NEXTCHARGETIME"上的不等式条件。这种不等式条件使优化器无法有效地使用表"IMUSE01"上的索引。"SUBSCRIBEINFO"。
建议---------------将谓词重写为等效形式以利用指数
基本原理---------如果谓词是不等式，则优化器无法使用索引条件，或者是否存在表达式或隐式数据类型转换在索引列上。