Suggestions for reducing LIO on this query?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Suggestions for reducing LIO on this query?

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367

    Suggestions for reducing LIO on this query?

    Dear All

    We're running a third party app (JD Edwards OneWorld XE) on 8.1.7.4 64bit, HP-UX 11 64bit, and one of the batch jobs is taking far too long. I did a 10046 trace on the job and the output indicated that the bottleneck was the following query and it's executed thousands of times:
    ======================================================================
    SELECT sdkcoo, sddoco, sddcto, sdlnid, sdmcu, sdco, sdrkco, sdrorn, sdrcto, sdrlln, sdan8, sdpddj, sditm, sdlotn, sdlnty, sdnxtr, sdemcu, sduom, sduorg, sdsocn, sdqtyt, sduom4, sdso11, sdaid, sdlt, sdcrcd
    FROM proddta.f4211
    WHERE (sditm = :key1 AND sdmcu = :key2) AND (sdpddj BETWEEN :key3 AND :key4)
    ORDER BY sditm ASC, sdmcu ASC, sdpddj ASC;

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 1 5
    TABLE ACCESS BY INDEX ROWID PRODDTA.F4211 1 133 5
    INDEX RANGE SCAN PRODDTA.F4211_6 1 3
    ======================================================================

    The clustering factor for the index is a much higer value than the number of blocks that the table consists of, and so in our test environment I recreated the F4211 table inserting the data back into it sorted by the columns that the F4211_6 index is created on (SDITM, SDMCU, SDPDDJ). This proved to be very successfull given that the runtime went down from 5 hours to 2 hours and also that the job is run multiple times with different parameters so these savings would be good across the board. My thinking was that I'd create a index clustered table if we went live with this. Unfortunately, however, other equally time critical interactive jobs were degraded as the indexes these use are on the columns that reflect the 'natural' clustering of the data. Therefore I'm currently thinking of a plan B, and I don't like what I've come up with so far:

    1) Create an index on all 26 columns in the SELECT clause to eliminate the TABLE ACCESS BY INDEX ROWID (the table actually has 218 columns!).

    2) Use software like Quest's SharePlex to replicate the table as an index clustered table so all data is grouped by SDITM, SDMCU, and SDPDDJ and point the batch jobs at the replicated table. We don't currently own this but the jobs are important enough to warrant some expenditure...

    3) I've toyed with the idea of materialized views with an order by but don't like the overhead of refresh on commit and refresh on demand would seems unwieldy is this situation.

    If anyone could offer their views on the above then it would be greatly appreciated, as would any solutions that I haven't thought of.

    Thanks

    Austin

  2. #2
    Join Date
    Jul 2003
    Posts
    30
    Have you tried gathering statistics to see whether the optimizer does any better? or does JD Edwards use the Rule based optimizer?

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Nagi

    Thank you for your response. I'm confident that the problem does not relate to optimizer statistics. Monitoring is turned on for all tables (expect SYS and SYSTEM of course) and we gather stale every hour. Also, I think that the plan the optimiser is coming up with is the best it can. It uses the index which is built on all three columns that appear in the WHERE clause, and therefore must perform a range scan and a table access by rowid. This is why I am attempting to tune IO rather than the SQL (we can't retwrite the SQL of course, being third party app). The only alternative access path I can think of is a FTS and given the table has almost 5 million rows this is slower. Trace Analyzer suggests that the bottle neck is not reading the index itself:

    Details for wait event Times Count Max. Total Blocks
    'db file sequential read (single block scan)' Waited Zero Time Wait Waited Accessed
    ----------------------------------------------------------------- --------- --------- --------- --------- ---------
    proddta.f4211.................................................... 139990 72438 0.56 821.72 139990
    proddta.f4211_6.................................................. 2136 735 0.07 16.94 2136
    ----------------------------------------------------------------- --------- --------- --------- --------- ---------
    total............................................................ 142126 73173 0.56 838.66 142126

    but reading the table (hence the improved performance after the re-org of the table). That is why I am attempting to tune IO or eliminate the table access altogether.

    Thanks

    Austin

  4. #4
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91

    Re: Suggestions for reducing LIO on this query?

    Originally posted by hacketta1

    WHERE (sditm = :key1 AND sdmcu = :key2) AND (sdpddj BETWEEN :key3 AND :key4)
    ORDER BY sditm ASC, sdmcu ASC, sdpddj ASC;
    What is the selectivity of those 3 columns?

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Stancho

    In terms of the most unselective combination of values in those three columns the row selectivity is 0.04% and the block selectivity is 0.18%.

    Thanks

    Austin

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    As you say, there's a benefit to physical row ordering on the table -- that won't reduce the LIO's though, it'd just reduce the average waits on them. Rebuilding the table as a hash cluster would probably help.

    Have you tried just upping the parallelism on the table/index?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    slimdave

    Thanks for your reply.

    Autotrace did seem to suggest that I was reducing the number of LIOs e.g. consistent gets. The query was performing 18 consistent gets pre-reorg and 6 post. It has always been my understanding that ordering the data does reduce LIOs because the same blocks do not need to be read into the buffer cache over and over again when the table access by rowid occurrs. Am I missing something here?

    With regard to parallel query, given this is OLTP we don't use it. The query currently runs quickly (on average 0.17 seconds per exec) but re-ordering the table got this down to 0.02 seconds. I realise these difference sound totally academic, but the query is executed so many time during the batch job that this reduced the runtime by 3 hours! Given the current runtime do you think parallel query would help here? Can table access by rowid take advantage of parallelism?

    I've done a little reading on hash clusters and notice that in 8i at any rate range scans aren't supported. Would it be possible to for you to explain a bit more about this option?

    Any help would be appreciated.

    Thanks

    Austin

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try Index Organized Table. This will reduce LIO.

    Tamil

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    IOT consisting of about 30 columns? Probably not so good idea....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Sorry, wasn't reading the SQL closely enough -- i thought this was executed once and took a long time, not executed many times.

    I would suspect that your benefit in row ordering comes from reduced PIO's, not LIO's. i may be wrong, but i can't think how LIO's would be affected.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width