Here's an update of our journey to production Unicode conversions for R/3 and SCM.
In prior installments, I talked about planning and about our development conversions. This weekend we converted our quality systems, with the largest being an R/3 instance close to 6TB when we began. It is smaller now!
- 0.001 - [Destroyed by space pirates]
- 0.002 - Community Day and Onward
- 0.003 - Revenge of The Space Monster
- 0.004 - Children of Murphy
- 0.005 - Journey to the Edge of Quality
- 0.006 - The Calm Before the Storm of Quality
- 0.007 - Passage to Production
- 0.008 - Unicode The Final Descent
- 1.000 - The Final Chapter
First, my primary role is performance tuning, so here are charts of CPU use for the application servers we used in R/3 and SCM.
R/3 Unicode Conversion (quality) CPU Use
SCM Unicode Conversion (quality) CPU Use
At SAP Tech Ed '07, I showed a simplified version of charts below, and explained how we optimized our network connectivity to minimize contention between incoming and outgoing data on all servers. For R/3, we used 5 app servers, for SCM we used 4.
Network pipe diagrams
Our Oracle and Unicode consultants worked on balancing the export and import streams that R3Load used. We employed table splitting, the "load fast" procedure, and this time used ROWID (see below). The CPU charts show we were at peaks for about 12 hours, and the work dwindled after that. Additional load balancing, plus better I/O systems in production should put us back to around 12 hours for the copy.
As a result, the R/3 database move was executed in under 17 hours.
There was also downtime in the schedule to move storage back to the original I/O system. This ran for about 12 hours.
Packages
TST - just a few of the hundreds of packages!---------------------------------------------------------------------
| Packages | Runtime (2008-01-11 14:23 - 2008-01-12 07:30) |
|-------------------------------------------------------------------|
| CDCLS-3 | EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEI |
| COEP-20 | EEEEEEEEEEEEEEEEEEEIIIIIIIIIIIIII |
| CDCLS-2 | EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEI |
| COEP-17 | EEEEEEEEEEEEEEEEEIIIIIIIIIIIIIII |
| COEP-16 | EEEEEEEEEEEEEEEEEIIIIIIIIIIIIII |
| COEP-18 | EEEEEEEEEEEEEEEEEIIIIIIIIIIIIIII |
| CDCLS-4 | EEEEEEEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-12 | EEEEEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-6 | EEEEEEEEEEEEEEEEEEEEEEEEEEI |
| STXL | EEEEEEEEEEEEEEEEEEEEEEIIII|
| CDCLS-8 | EEEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-11 | EEEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-9 | EEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-10 | EEEEEEEEEEEEEEEEEEEEEEEEI |
| CDCLS-7 | EEEEEEEEEEEEEEEEEEEEEEEEI |
| RFBLG-10 | EEEEEEEEEEEEEEEEEEEEEEEI |
| RFBLG-2 |EEEEEEEEEEEEEEEEEEEEEEEI |
Brad plans to split CDCLS up into more packages for production, and we will debate holding some secondary indexes rebuilds until later.
SCT - One really big one, but the total time is much shorter than TST, so we don't need to tune this.
---------------------------------------------------------------------
| Packages | Runtime (2008-01-11 20:16 - 2008-01-12 02:19) |
|-------------------------------------------------------------------|
| SAPDODS |EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEIIIIIIIIIIIIIIIII|
| REPOSRC |EEEEEEEEEI |
| SAPAPPL0 |EEEIIII |
| SAPZOD02 |EEEEII |
| SAPAPPL2 |EIIIII |
| SAPDFACT |EEEII |
R/3 sizes before: Tables Indexes
Total number 37,475 44,571
Total size/kb 2,373,250,560 1,423,358,808
After: Tables IndexesTotal number 37,821 45,048Total size/kb 2,156,013,648 983,578,208
Analysis of top run times
| TABLE | DB_SIZE_GB | EXP_SIZE | EX_IM_HOURS | GB_PER_HOUR_DB | DB_SIZE_AFTER |
| CDCLS | 39 | 25 | 11.5 | 3.4 | 47 |
| COEP | 330 | 28 | 10.7 | 30.9 | 217 |
| RFBLG | 51 | 44 | 8.0 | 6.4 | 74 |
| STXL | 14 | 7 | 8.8 | 1.6 | 11 |
Run time for database statistics. This is the first time we have collected full statistics since 2001!
BR0801I BRCONNECT 7.00 (28)BR0805I Start of BRCONNECT processing: cdxaktei.sta 2008-01-13 02.01.32BR0484I BRCONNECT log file: /oracle/TST/sapcheck/cdxaktei.staBR0049W Last BRCONNECT run with function 'stats' was probably killed
BR0851I Number of tables with missing statistics: 34975BR0852I Number of tables to delete statistics: 0BR0854I Number of tables to collect statistics without checking: 36978[...]
BR0806I End of BRCONNECT processing: cdxaktei.sta 2008-01-13 06.26.46BR0280I BRCONNECT time stamp: 2008-01-13 06.26.46BR0803I BRCONNECT completed successfully with warnings
to check Oracle 10 parameters, since we migrated the database during this conversion. A very slick script is attached to that note to categorize your current settings.
Things we learned
The long running table export at the end will be split and started sooner for production. The total time would have been less except for some uncaught space adjustments.
Table splitting
Note 960280 - R3LOAD EXPORT TUNING FOR SPLIT TABLES WITH ORACLENote 1043380
- Note - Efficient Table Splitting for Oracle Databases This was a big time saver for the largest tables. There was a few lines of code that didn't work with Oracle 9 clients, so we commented these out. Support should have a fix shortly.
Next Steps
Next steps are already underway, as we have started the table scans in production, even though that conversion is 4 weeks away!