Suggestions for reducing LIO on this query?
We're running a third party app (JD Edwards OneWorld XE) on 220.127.116.11 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
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.
Click Here to Expand Forum to Full Width