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;
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.
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:
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?
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?