For our split table exports, we used custom coded WHERE clauses. (Basically adding additional columns to the R3ta default column to take advantage of existing indexes).
The results have been good so far. Full tablescans have been eliminated and export times have gone down, in some cases, tables export times have improved by 50%.
However, our biggest table, CE1OC01 (120 GB), continues to be a bottleneck. Initially, after using the new WHERE clause, it looked like performance gains were dramatic, with export times for the first 5 packages dropping from 25-30 hours down to 1 1/2 hours.
However, after 2 hours, the remaining CE1OC01 split packages have shown no improvement. This is very odd because we are trying to determine why part of the table exports very fast, but other parts are running very slow.
Before the custom WHERE clauses, the export server had run into issues with SORTHEAP being exhausted, so we thought that might be the culprit. But that does not seem to be an issue now, since the improved WHERE clauses have reduced or eliminated excessive sorting.
I checked the access path of all the CE1OC01 packages, through EXPLAIN, and they all access the same index to return results. The execution time in EXPLAIN returns similar times for each of the packages:
CE1OC01-11: select * from CE1OC01 WHERE MANDT='212'
AND ("BELNR" > '0124727994') AND ("BELNR" <= '0131810250')
CE1OC01-19: select * from CE1OC01 WHERE MANDT='212'
AND ("BELNR" > '0181387534') AND ("BELNR" <= '0188469413')
0 SELECT STATEMENT ( Estimated Costs = 8.448E+06 [timerons] )
|
--- 1 RETURN
|
--- 2 FETCH CE1OC01
|
------ 3 IXSCAN CE1OC01~4 #key columns: 2
query execution time [millisec] | 333
--------------------------------------------------------------------
uow elapsed time [microsec] | 429,907
total user CPU time [microsec] | 0
total system cpu time [microsec] | 0
Both queries utilize an index that has fields MANDT and BELNR. However, during R3load, CE1OC01-19 finishes in an hour and a half, whereas CE1OC01-11 can take 25-30 hours.
I am wondering if there is anything else to check on the DB2 access path side of things or if I need to start digging deeper into other aggregate load/infrastructure issues. Other tables don't seem to exhibit this behavior. There is some discrepancy between other tables' run times (for example, 2-4 hours), but those are not as dramatic as this particular table.
Another idea to test is to try and export only 5 parts of the table at a time, perhaps there is a throughput or logical limitation when all 20 of the exports are running at the same time. Or create a single column index on BELNR (default R3ta column) and see if that shows any improvement.
Anyone have any ideas on why some of the table moves fast but the rest of it moves slow?
We also notice that the "fast" parts of the table are at the very end of the table. We are wondering if perhaps the index is less fragmented in that range, a REORG or recreation of the index may do this table some good. We were hoping to squeeze as many improvements out of our export process as possible before running a full REORG on the database. This particular index (there are 5 indexes on this table) has a Cluster Ratio of 54%, so, perhaps for purposes of the export, it may make sense to REORG the table and cluster it around this particular index. By contrast, the primary key index has a Cluster Ratio of 86%.
Here is the output from our current run. The "slow" parts of the table have not completed, but they average a throughput of 0.18 MB/min, versus the "fast" parts, which average 5 MB/min, a pretty dramatic difference.
------------------------------------------------------------------------- package time start date end date size MB MB/min ------------------------------------------------------------------------- CE1OC01-16 10:20:37 2008-11-25 20:47 2008-11-26 07:08 417.62 0.67 CE1OC01-18 1:26:58 2008-11-25 20:47 2008-11-25 22:14 429.41 4.94 CE1OC01-17 1:26:04 2008-11-25 20:47 2008-11-25 22:13 416.38 4.84 CE1OC01-19 1:24:46 2008-11-25 20:47 2008-11-25 22:12 437.98 5.17 CE1OC01-20 1:20:51 2008-11-25 20:48 2008-11-25 22:09 435.87 5.39 CE1OC01-1 0:00:00 2008-11-25 20:48 0.00 CE1OC01-10 0:00:00 2008-11-25 20:48 152.25 CE1OC01-11 0:00:00 2008-11-25 20:48 143.55 CE1OC01-12 0:00:00 2008-11-25 20:48 145.11 CE1OC01-13 0:00:00 2008-11-25 20:48 146.92 CE1OC01-14 0:00:00 2008-11-25 20:48 140.00 CE1OC01-15 0:00:00 2008-11-25 20:48 145.52 CE1OC01-2 0:00:00 2008-11-25 20:48 184.33 CE1OC01-3 0:00:00 2008-11-25 20:48 183.34 CE1OC01-4 0:00:00 2008-11-25 20:48 158.62 CE1OC01-5 0:00:00 2008-11-25 20:48 157.09 CE1OC01-6 0:00:00 2008-11-25 20:48 150.41 CE1OC01-7 0:00:00 2008-11-25 20:48 175.29 CE1OC01-8 0:00:00 2008-11-25 20:48 150.55 CE1OC01-9 0:00:00 2008-11-25 20:48 154.84