Oracle 11g high IO Wait

Question

Oracle Version: 11.1.0.7.0

We are having higher IO Wait in one of our Oracle RAC instance

One SQL is having high elapsedtime by execution - 1452.57s per execution. This started happening suddenly one day. Previously, it was taking max 3-4 min to query 20k(:v4 parameter) records

subscribeinfo records: 59 million (non - parallel)

chargerate records : 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)

According to AWR report Top 5 Timed Foreground Events

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 ]

I will not be able to post full AWR report, because it is restricted. So please ask details I'll post

Explain plan

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

We created an index for the fields monthbillid, nextchargetime and failtimestamp... Eventhough it improved a lot in cardinality by 1/6th, it increased the cost by 4-5 times. But oracle takes the new index by default

create index IDX_MONTHBILLQUERY on subscribeinfo(monthbillid,
nextchargetime, failtimestamp) local tablespace IMUSE_INDEX;

dbms_stats.gather_index_stats('IMUSE01', 'IDX_MONTHBILLQUERY');

We have hard parses = 0 in AWR reports. And also we changed the cursor_sharing = FORCE

Now IO is under control. Still feels, this not the root cause. And also, we made the instance dedicate for this query which happens more than 10 times an hour also, it takes approximately 100 seconds to retrieve 20k records.

Can anyone suggest whether it is a good decision if I go for optimizer mode as first_rows or use an hint first_rows(20000)?

As of now, we have disabled stats job; can we enable the same thing only for some tables or some indexes. Is this possible?

Answer 1

The problem solved..... The cursor_sharing is made to force... This reduced the IO very much. Now IO is normal in all instance. Then we created two indexes for the same query which recommended by sqltuning advisor and accepted the profile

2- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 80.44%)

• Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'my_sqltune_task1', task_owner => 'IMUSE01', replace => TRUE);

  Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time.

                     Original Plan  With SQL Profile  % Improved
                     -------------  ----------------  ----------   Completion Status:             PARTIAL          COMPLETE   Elapsed
  

  Time(ms): 31479 8049 74.43% CPU Time(ms): 5172 1656 67.98%
  User I/O Time(ms): 16367 3422 79.09%
  Buffer Gets: 265365 51818 80.47%
  Disk Reads: 3227 524 83.76%
  Direct Writes: 0 0 Rows Processed: 0 20000 Fetches:
  0 20000 Executions: 0
  1

3- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 81.1%)

• Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index IMUSE01.IDX$$_67E5B0001 on IMUSE01.SUBSCRIBEINFO("STATE","SUBFLAG","MONTHBILLID","RETRYENDDATETIME");

• Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index IMUSE01.IDX$$_67E5B0002 on IMUSE01.SUBSCRIBEINFO("STATE","MONTHBILLID","FAILTIMESTAMP");

  Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.

4- Restructure SQL finding (see plan 1 in explain plans section) ---------------------------------------------------------------- Predicate "O"."NEXTCHARGETIME"<>:B1 used at line ID 5 of the execution plan is an inequality condition on indexed column "NEXTCHARGETIME". This inequality condition prevents the optimizer from efficiently using indices on table "IMUSE01"."SUBSCRIBEINFO".

Recommendation -------------- - Rewrite the predicate into an equivalent form to take advantage of indices.

Rationale --------- The optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.

Answer 2

The problem is that the statement is causing more than 50000 disk reads. This is probably caused by using cursor_sharing. This parameter is generally used if the application is coded without using bind variables (very bad. Don't walk, run to fix that application). Probably you even set cursor_sharing to force, which can have undesirable effects like the one described and cursor peeking will also not work in most cases.

You might work around it by specifying hints to avoid the full table scans depending if you have indexes on the required tables. As you do not describe that, it is not possible to give you any concrete advice.