optimization of unicode conversions for oracle systems

Optimization ofUnicodeConversions forOracle Systems

SAP AG 2006, SAP TechEd ’06 / SPC200 / 2

Speakers

Jan KlokkersORACLE Corporation

Stephan SchmidSAP Consulting Deutschland


AGENDA

Motivation and Goals

Setup - Recommendations

Tuning and Optimizations

Project Experiences


Why Unicode, why Optimization?

Motivation for UnicodeFuture SAP Versions will be Unicode onlySome existing SAP products such as XI require Unicode todayUnicode defines the character set for …

– … efficient text processing in any language– … maintaining text data integrity

Unicode systems …– … need only one locale (Unicode locale = platform-independent)– … integrate seamless in existing system landscape (SAP and

non-SAP systems)… Users can …

– … enter and display any character from any script no matter whichlogon language they use

– … print text data in multiple languages

Motivation for OptimizationConversion of an existing SAP system to Unicode requires a completeexport and import of the database through SAP R3load utility.Customers can only afford limited downtime of their production systemfor the Unicode conversion.


Unicode Conversion Optimizations - Goals

Improvement of technical conversion duration, throughCorrect system configuration (OS-Parameters, I/O,..)Correct and optimal database configuration (parameters, layout)Using all existing system resources (CPU, Memory, IO) more effectivethrough advanced database features

Optimization recommendations available for 6.40 and 7.00 based SAPsystems with Oracle 9.2 and Oracle 10.2

Improvements for split export phase and R3load import phase

PreparationUnicode Conversion Finalization

SUMG

ConversionCompletion

in theUnicodesystem

SPUMG

ConversionPreparation

innon-Unicode

system

R3loadSAPInstMigMonDistMon

DatabaseExport,

Conversion& Import

Unicode system isup and running

Verification ofData Consistency

IntegrationTesting focusedon languagehandling

Set up theConversionProject

CheckPrerequisites

Plan databasedowntimeduringconversion

EnableCustomerDevelopments

PreparationUnicode Conversion Finalization

SUMG

ConversionCompletion

in theUnicodesystem

SPUMG

ConversionPreparation

innon-Unicode

system

R3loadSAPInstMigMonDistMon

DatabaseExport,

Conversion& Import

Unicode system isup and running

Verification ofData Consistency

IntegrationTesting focusedon languagehandling

Set up theConversionProject

CheckPrerequisites

Plan databasedowntimeduringconversion

EnableCustomerDevelopments


AGENDA



Tuning and Optimization

Project Experiences


Conversion Setup I

Use at least two servers for parallel export/import:Source database serverTarget database serverAvoid R3load Processes on the source database Server

Bay NetworksBay Networks

Source DBNUC

Target DBUC

Export installdirectory

(local)

Network exchangedirectory

Exportdirectorie(s) (local)

Import installdirectory

(local)

MIGMON

R3LOAD

MIGMON

R3LOAD


Conversion Setup II - advanced

Use existing Application Servers:Several Export/import servers (each with export/import R3loadprocesses)Each application servers controlled by the Distribution Monitor

Bay Networks

Bay Networks

Source DBNUC

Target DBUC


(local)




(local)

MIGMON

R3LOAD

MIGMON

R3LOAD


(local)


(local)






(local)


(local)

MIGMONMIGMON

R3LOADR3LOAD

MIGMONMIGMON

R3LOADR3LOAD

Ba y Netwo rks


(local)




(local)

MIGMON

R3LOAD

MIGMON

R3LOAD


(local)


(local)






(local)


(local)

MIGMONMIGMON

R3LOADR3LOAD

MIGMONMIGMON

R3LOADR3LOAD

Ba y Netwo rks


Environment Setup - recommendations

Configure enough network bandwidthGigabit Ethernet between machinesSeveral Ethernet adapters for each serverUse Jumbo Frames instead of Ethernet FramesOSS Note 936441

Increase OS resources forMaximum number of processesAsync io processes/settingsOSS note 834343 and 999524

Ensure stable storage for dump-DirectoryUse local file systems for export dump!Do not use NFS file systemsDo not use Network SharesOSS note 769476


AGENDA




Project Experiences

Database TuningOptimization Package SplittingOptimization Table SplittingOptimization Export of splitted TablesOptimization Import


Database Tuning – Major Steps I/O

RedoLogsDisable Database Archiving – Put Database in No Archive ModeDisable Redo Log Mirroring – Only one redo log member per groupUse raw devices for optimal IO

RAW DevicesUse raw devices for optimal IO and disk throughput for the followingdatabase areas:

– Redo-Logs, these areas can easily be changed afterwards to usefilesystem files again

– Datafiles for PSAPUNDO and PSAPTEMP, if possible all datafiles

Very important for HP-UX platforms as HP JFS does not supportasync io – Doubles R3load Import PerformanceVery important for AIX and JFS filesystem. Not needed for JFS2filesystem – Doubles R3load Import Performance


Database Tuning – Major Steps Parameters

Database ParametersIncrease parallel_max_servers parameter

– number of r3load jobs * number of CPUs/Threads * 2 (may require OSkernel changes)

– processes = processes + parallel_max_servers

PGA_AGGREGATE_TARGET– 70% of memory to improve sorts during index creation while importing– ~40% of memory for export– _pga_max_size 2147483648

db_cache_size– 20% for import– 50% for export– Use most of the memory for PGA

filesystemio_options– filesystemio_options = setall– disk_asynch_io = true– OSS note 834343 and 999524

Use most of the memory for PGA not for db_block_buffers !

Database Tuning

Optimization Package SplittingOptimization Table SplittingOptimization Export of splitted TablesOptimization Import


Package Splitting - Example

Default: One (R3load) package per data classThe number and size of tables per package varies a lot.

Splitting Options:Create separate packages for the largest <n> tables.Create separate packages for all tables larger than <x>.Split all packages larger than <x> into packages of size ~ <x>.Create packages for all tables from one input list (EXT files are optional).

split this package

Database TuningOptimization Package Splitting

Optimization Table SplittingOptimization Export of splitted TablesOptimization Import


Export Tuning – Major Steps - Overview

Use Package Splitting to separate tables

Use Table Splitting for large tablesDo not use R3ta for calculation of ranges, because optimized for allplatformsUse NEW PL/SQL based utility “splitter” for Oracle database – OSS Note1043380

Advantages of PL/SQL based utilityAt least 10 times faster than R3ta

– R3ta Splitting CDCLS with 90GB ~ 16h– PL/SQL utility CDCLS with 90GB ~45min

Optimized for BW and Partitioned Tables, not covered from R3ta so farRuns inside database kernel, no client/server data transfer necessary


PL/SQL based utility splitter - Usage

Installation:1. Download script from OSS note 10433802. Rename “splitter.txt” to “splitter.sql”3. Load splitter script once into database with sqlplus with sapr3 or sapsid

user:SQL> start splitter

Use splitter script by running sqlplus with sapr3 or sapsid user.Examples:

– SQL> exec splitter (‘RFBLG’, ‘BELNR’, 10);– SQL> exec splitter (‘/BIC/XXX’, ‘DUMMY’, 10);

Calculated ranges are stored in database table “~SPLITTER_RANGES”and flat file <table_name>.WHR in /tmp directory

Examples:– Database: select ranges from “~SPLITTER_RANGES ”

where table_name = ‘tab: RFBLG’ and num_packages = 10;– OS: /tmp/RFBLG.WHR

Database TuningOptimization Package SplittingOptimization Table Splitting

Optimization Export of splitted TablesOptimization Import


Export – Optimal Index Usage

Problem:Optimal Index Access is not always chosen for the execution of the whereclause of the table split ranges resulting in long and unequal distribution of runtimes for different packages for the same splitted table

Cause:Stale and old Index statisticsReexecution of new accurate statistics takes too long on large indexes

Non optimal solution so farIf possible, Create additional index on split column

NEW Optimal solution with PL/SQL Package “sap_r3load.sql” – see OSSnote 960280 for more details

Force optimal index access by using optimizer hintsForce full table scans

Guarentees almost same run time for all packages of one splitted table


Export – Optimal Index Usage - Examples

Export times without using hints Export times using hints(full table scans)


PL/SQL “sap_r3load.sql” - Usage

Important: DO NOT USE THIS PACKAGE DURING NORMALOPERATION !ONLY TO BE USED DURING EXPORT PHASE !

Installation:Download PL/SQL Package “sap_r3load.txt” from OSS note 960280Rename “sap_r3load.txt” to “sap_r3load.sql”Load script once into database with sqlplus with sapr3 or sapsiduser:

– SQL> start sap_r3load

Use sap_r3load script by executing sqlplus as sapr3 or sapsiduser:

Examples:– SQL> exec sap_r3load.set_index_scan (‘PK’, ‘RFBLG’, ‘BELNR’);– SQL> exec sap_r3load.clear_all_scans;

It is very important to run “clear_all_scans” if you run it on yourproduction system before you resume normal operation !

Database TuningOptimization Package SplittingOptimization Table SplittingOptimization Export of splitted Tables

Optimization Import


Import Optimization

R3load Import:Implementation of OCI direct path interface in R3load for“-loadprocedure fast” option of R3loadOSS Note 864861Consider redo log recommendations already given

R3load import (data load phase) performance improvesby a factor 3-5

Optimized DDLORA.TPL Templates:Implementation of nologging, compute statistics and Parallel CreateIndex in advanced R3load templates (DDLORA.TPL)Index Builds are dramatically faster – at least factor 5 !Accurate CBO statistics for indexes – no need to re-generate indexstatistics with BR*Connect


DDLORA_LRG.TPL - Examples

<…..>cretab: CREATE TABLE &tab_name&

( /{ &fld_name& &fld_desc& /-, /} )TABLESPACE &tablespace&&lob_cache&NOLOGGING

<…..>

crepky: CREATE UNIQUE INDEX &pri_key&ON &tab_name&( &key_fld& /-, )TABLESPACE &tablespace&STORAGE (INITIAL &init&

NEXT &next&MINEXTENTS &minext&MAXEXTENTS &maxext&PCTINCREASE &pctinc& )

PARALLEL NOLOGGING COMPUTE STATISTICS ;ALTER INDEX &pri_key& NOPARALLEL LOGGING ;ALTER TABLE &tab_name& LOGGING

<……>

creind: CREATE &unique& INDEX &ind_name&ON &tab_name&( /{ &fld_name& /-, /} )TABLESPACE &tablespace&STORAGE (INITIAL &init&

NEXT &next&MINEXTENTS &minext&MAXEXTENTS &maxext&PCTINCREASE &pctinc& )

PARALLEL NOLOGGING COMPUTE STATISTICS ;ALTER INDEX &ind_name& NOPARALLEL LOGGING


Unicode Migration Optimizations

Important SAP notes:

864861: Oracle Direct Path Load support in R3LOAD 6.40 and above

952514: Pilot Projects for table splitting

954268: Optimization of export: Unsorted unload

960280: R3LOAD export tuning for split tables with Oracle

936441: Oracle settings for R3load based system copy

1043380: Efficient Table Splitting for Oracle Databases


AGENDA




Project Experiences

Project Scope and InformationDowntime Setup and InformationTechnical Statistics


Project - Key Facts

From R/3 4.6c IS-OIL MDMP to SAP ERP 2005 of a ~ 8 TB Global Instance

Two step approach:first Upgrade (21st of October 2006)then Unicode conversion (27th of January 2007)

The approach Combined Upgrade and Unicode (CU&UC) was not chosenbecause the downtime effort would be more than 3 daysCU&UC was in pilot phase, while the project decision was done

To reduce the Downtime of the Unicode Conversion IMIG was chosen90% of the system was converted while system uptime10% of the system was converted while system downtimeOSS 693168 for new MDS-Service

Project runtime11 month in total14 weeks on ECC 6.00 MDMP


Upgrade and Unicode Steps

DB ServerOracle 9.2.0.6Old Hardware

DB ServerOld Hardware

DB ServerOracle 10.2.0.2New Hardware

R/3 4.6C MDMP

IMIGUpgrade inplace

24 hOutage

24 hOutage

ERP 2005 MDMP ERP 2005 Unicode


Key Facts Unicode Conversion

Relevant Size of source system 7668 GB :4.335 GB used for tables3.253 GB used for indices8.600 GB Disk space allocated

Database infos37% of data is stored in the 10 largest tables72% of data is stored in the 50 largest tables270 tables are larger than 1 GBThe size of the 200 IMIG tables was 3.838 GB of 4.335 GB (~89%)

Total free/Gb ; 939,42

Tables/Gb ; 4335,74

Indices/Gb ; 3253,98

“online”

“downtime”

Project scope and Informationz

Downtime Setup and InformationTechnical Statistics


Downtime Setup

Two Servers used for export/importMigration Monitor for export and import on target serverAll R3load Processes running on target server64 R3load for export / 32 R3loads for import

Bay NetworksBay Networks

Source DB Target DB

IBM P590

32 physical CPUs

256 GB of Memory(only 128 used)

IBM SAN

Database on RAW

IBM P570

16 physical CPUs

128 GB of Memory

IBM SAN

Database on RAW Export installdirectory

(local)




(local)

MIGMON

R3LOAD

MIGMON

R3LOAD


(local)


(local)






(local)


(local)

MIGMONMIGMON

R3LOADR3LOAD

MIGMONMIGMON

R3LOADR3LOAD


Overview planned Downtime

System shutdown2h hours

Stop user sessionsEm

pty BW

Queues

Lock all usersStop batch processes and interfaces

Last Data Transfer

Clean source system

Additional IMIG

-Preparations

export/import to new

Systemin parallel w

ith MigM

onif required D

istMon

Move discs to new

serverC

hanging host name and IP adresses

Start online-backup

Systencopy post activities (SG

EN,

Statistics etc.)U

nicode Post StepsStart SU

MG

( will run the next2 days

Functional system test

Stop user sessionsEm

pty BW

Queues

Lock all usersStop batch processes and interfaces

Stop user sessionsEmpty BW QueuesLock all usersStop batch processes and

interfaces

Last Data TransferClean source systemAdditional IMIG-Preparations

export/import to new Systemin parallel with MigMonif required DistMon

Move discs to new serverChanging host name and IP

adressesStart online-backup

Systen copy post activities (SGEN,Statistics etc.)

Unicode Post StepsStart SUMG (will run the next2 days)

Functional system testStart stopped systems & InterfacesUnlock usersRelease system

22.00 24.00 02.00 05.00 08.00 10.00 13.00 14.00


Downtime planned vs. realized

Realized Business Downtime

Optimistic planned Downtime

The 6 hours delay was caused byLonger running import (creating empty tables)Additional hardware maintenance (changing memory on central interface server )

Problems due to not deleted Matchcodes

Interface Problems to HR Web Application

The GoLive was still 3 hours earlier then announced!

SystemShutdown2,5 hours

IMIGCompletion1,5 hours

Exp/ImportRest Tables

4 hours

HardwareSwitch

2,5 hours

Tech. PostActivities2,5 hours

BusinessTesting7 hours

SystemStartup1 hour

22:00 00:30 02:00 06:00 08:30 11:00 18:00 19:00

SystemShutdown

2 hours

IMIGCompletion

2 hours

Exp/ImportRest Tables

3 hours

HardwareSwitch2 hours

Tech. PostActivities2 hours

BusinessTesting3 hours

SystemStartup1 hour

22:00 00:00 02:00 05:00 07:00 09:00 12:00 13:00

Project scope and InformationzDowntime Setup and Information

Technical Statistics


CPU-Usage

CPU-Load target system

0,00

5,00

10,00

15,00

20,00

25,00

30,00

35,00

0:00

:00

0:07

:00

0:14

:00

0:21

:00

0:28

:00

0:35

:00

0:42

:00

0:49

:00

0:56

:00

1:03

:00

1:10

:00

1:17

:00

1:24

:00

1:31

:00

1:38

:00

1:45

:00

1:52

:00

1:59

:00

2:06

:00

2:13

:00

2:20

:00

2:27

:00

2:34

:00

2:41

:00

2:48

:00

2:55

:00

3:02

:00

3:09

:00

3:16

:00

3:23

:00

3:30

:00

3:37

:00

3:44

:00

3:51

:00

3:58

:00

4:05

:00

4:12

:00

4:19

:00

4:26

:00

Time

n C

PUs

start exportstart import

stop éxport

stop import


I/O -Throughput

An average throughput (measured at the import) for 300Gb/h are possible

Total I/O's Total I/O's Total TotalHH:MM per second (avg) per second (peak) MB Reads MB Writes----- ---------------- ----------------- --------- ---------00:00 7 15 0 16501:00 150 434 284 692402:00 10715 12160 236237 57188303:00 2356 5574 14725 15121004:00 577 721 6895 538705:00 198 255 8 219706:00 37 67 675 18608:00 516 516 7305 33509:00 2101 4549 410978 1345610:00 4348 4579 969657 1268511:00 2767 4065 537468 2652012:00 4703 5348 870815 3630713:00 4018 4429 853290 881714:00 4298 4480 959275 1331915:00 2394 4416 515757 647016:00 313 667 34171 629617:00 385 581 16417 1219718:00 210 288 24046 655119:00 454 719 19675 986320:00 1272 2421 81445 1936821:00 1082 1439 46712 2006322:00 641 1110 26305 10581


Reorganization – example MSEG

• The Table it self grows 8 GB(+13%)

• The indices shrunk in sum 16GB (-38 %)

• MSEG consumes 3 GB lessspace (-5 %)

•Tablespace Reorganisation

•Index names with newconvention

•Database in new Schema

Non-UC-SystemOwner Object Type Tablespace Mbytes

SAPR3 MSEG TABLE PSAPBTABD 105.032,0SAPR3 MSEG~N INDEX PSAPBTABI 6.620,0SAPR3 MSEG~ZB INDEX PSAPBTABI 6.815,1SAPR3 MSEG______0 INDEX PSAPBTABI 6.280,0SAPR3 MSEG______S INDEX PSAPBTABI 6.560,0SAPR3 MSEG______ZA INDEX PSAPBTABI 4.480,0SAPR3 MSEG______R INDEX PSAPBTABI 6.620,0SAPR3 MSEG~OIB INDEX PSAPBTABI 6.940,0

Total 149.347,2

UC-SystemOwner Object Type Tablespace Mbytes Delta

SAPPRD MSEG TABLE PSAPPRD 118.718,4 13,0%SAPPRD MSEG~0 INDEX PSAPPRD 4.290,8 -35,2%SAPPRD MSEG~N INDEX PSAPPRD 4.088,6 -40,0%SAPPRD MSEG~OIB INDEX PSAPPRD 7.132,9 13,6%SAPPRD MSEG~R INDEX PSAPPRD 3.028,3 -53,8%SAPPRD MSEG~S INDEX PSAPPRD 2.695,4 -39,8%SAPPRD MSEG~ZA INDEX PSAPPRD 2.369,9 -64,2%SAPPRD MSEG~ZB INDEX PSAPPRD 4.468,6 -35,6%

Total 146.792,8 -1,7%


Table comparision NUC-UC

TableGbytes

Source_IMIGGbytesTarget Growth

ZZGLA 290,63 300,82 3,5%GLPCA 220,98 230,21 4,2%BSIS 177,42 165,26 -6,9%RFBLG 155,52 244,77 57,4%COEP 134,28 146,64 9,2%SOFFCONT1 132,20 1,94 -98,5%JVSO1 126,67 132,60 4,7%ACCTIT 123,69 133,76 8,1%ZZJVA 121,03 129,57 7,1%TST03 102,81 1,50 -98,5%MSEG 102,57 115,94 13,0%DBTABLOG 93,04 96,17 3,4%CDCLS 89,89 126,24 40,4%VBRP 83,41 94,57 13,4%ZZTW_COMM_LOG 80,42 35,98 -55,3%BSAS 75,42 90,58 20,1%SOC3 70,84 71,87 1,4%CE1COP1 68,37 73,72 7,8%BSAD 66,91 75,88 13,4%VBAP 64,56 70,94 9,9%LIPS 61,22 77,67 26,9%KOCLU 57,50 103,79 80,5%BKPF 48,68 60,26 23,8%CE4COP1_ACCT 45,68 51,88 13,6%VBFA 44,77 49,39 10,3%VBPA 36,30 37,80 4,1%BSE_CLR 35,67 37,62 5,5%MSEGO2 29,07 32,94 13,3%BSAK 28,96 33,52 15,8%


Smaller Indices – Top 25

IndexSize SourceSystem GB

Size TargetSystem GB Delta

ZZGLA_____1 94,04 52,37 -44,3%ZZGLA_____2 93,46 42,80 -54,2%ZZGLA_____3 82,60 43,88 -46,9%COEP______1 77,74 33,72 -56,6%ZZGLA_____0 67,97 32,38 -52,4%ZZGLA_____ZA 56,54 27,07 -52,1%BSIS______0 53,61 33,70 -37,1%BSIS______ZA 40,14 23,09 -42,5%COEP______0 37,24 12,34 -66,9%BSAS______0 37,15 21,67 -41,7%GLPCA_____2 33,29 19,47 -41,5%ACCTCR____0 32,13 20,36 -36,6%GLPCA_____3 32,00 19,95 -37,6%BSIS~Z02 31,13 19,86 -36,2%GLPCA_____0 28,34 14,75 -48,0%GLPCA_____1 27,49 19,95 -27,4%GLPCA~ZD 27,26 19,76 -27,5%JVSO1_____3 27,21 16,75 -38,4%BSE_CLR~0 25,69 11,99 -53,3%ZZJVA_____2 25,69 11,12 -56,7%MSEGO2____0 25,53 18,65 -26,9%GLPCA_____7 25,20 14,73 -41,6%GLIDXA____0 25,18 12,62 -49,9%JVSO1_____4 25,12 13,40 -46,7%ZZJVA~1 24,84 13,44 -45,9%CDHDR~0 24,58 14,15 -42,4%CDCLS_____0__X 23,43 14,97 -36,1%JVSO1_____REP 23,06 12,27 -46,8%VBFA______0 22,21 11,71 -47,3%GLPCA~ZB 22,21 10,96 -50,6%ZZJVA_____3 21,58 11,40 -47,2%ZZJVA~Z01 21,58 11,41 -47,1%


Downtime Setup

The database size of the new UC - SystemHas a allocated disk size of 7060 GBshrunk about 1700 GBis ~ 22 % smaller than the source DB

The system growth after Go-LiveAfter 2 weeks + 280 GbAfter 7 weeks + 570 Gb

Source System 7,5 TB used from 8,6 TB Target System 5,9 TB used from 6,9 TB

Total free/Gb ; 819,75

Tables/Gb ; 4225,45

Indices/Gb ; 1797,83Total free/Gb ; 819,75

Tables/Gb ; 4225,45

Indices/Gb ; 1797,83


AGENDA




Project Experiences


Summary

Optimization of a Unicode conversion is more then optimization ofexport/import.

post activities should be traineddon’t underestimate the system isolation processoptimal order of steps, what can be done in system uptime?…

Several successful tests are necessary to be sure that predicteddowntime is achievable.

The most critical system resource is I/O not CPU!

In general the size of the Oracle database shrinks through aUnicode Conversion

For large and complex systems involve expert consulting e.g. SAPConsulting

optimization of unicode conversions for oracle systems

Documents