performance techniques for unicode conversion of … need to run crocllnk script from oss note #’s...

PerformanceTechniques for UnicodeConversion of SingleCode Page Systems

Richard Bernat, Chevron Corporation

Theodore Duong, Chevron Corporation

SAP TechEd ’05 / Session ID / SPC204

Learning Objectives

As a result of this workshop, you willbe able to:

Select an appropriate environment strategy for your projectUnderstand the Unicode conversion processUse SAP tools and techniques for high performanceconversion to reduce downtimeUse Oracle features to enable continuous processing due tospace consumption

Chevron CorporationSAP LandscapeConversion

PlanningEnvironment StrategyProcess

Tips and TricksQ & A

AgendaAgenda


One of the world’s largest global energy companiesActive in more than 180 countries

47,000 employees worldwide$150 billion in revenue for 2004

Chevron – About Our Company




AgendaAgenda


SAP Landscape

HPUX PA-RISC &IA64

Hitachi7 Production

environments500 GB - 4TB40+ EnvironmentsOracle 9.2.0.5+


H.R. Landscape Prior to The Conversion

HPUX 11.11 PA-RISC

SAP Enterprise 4.71

5 App. servers

2 ITS servers

500 GB DB

Timesheet access via ESS and Portal

24,000 users


H.R. Landscape After The Conversion

HPUX 11.23 IA64

SAP R/3 Enterprise 4.71 Unicode

3 Application servers

2 ITS servers

450 GB DB

Timesheet access via ESS and Portal

24,000 users




AgendaAgenda


Conversion Planning - Documentation

Unicode Conversion GuideSAP Installation GuideSystem Copy GuideMigration Monitor GuideRead OSS Notes


Conversion Planning - Hardware

Change platform to ItaniumNeed OS skills to patch and certify new platformMaking existing scripts work on new platformWhile HR is relatively small, need to make sure tohave enough disk space for exportGiven a 24 hour window for data movement downtime


Conversion Planning - Downtime

Understand what customers are willing to endure foroutage duration

Downtime started on Friday Evening

Go/No-Go decision on Sunday Morning




AgendaAgenda


Conversion Environment Strategy

All prior experience based on upgrade strategy

Identified that Unicode Conversion does NOT need touse the same strategy as an upgrade

Dual support stack not needed, although may bepreferred ( it’s up to you)

Key learning – development environment does notneed to be unicoded immediately

Fix ABAP code prior to converting an environment.


Conversion Environment Strategy Continued

Chose to apply ABAP unicode changes via transportbundles after conversion

Needed a separate environment to transport and testABAP Unicode ( can not use existing Staging / QA)

Sand box for initial learningEven though this was for technical learning, users wanted to seeit.


H.R. Itanium / Unicode Environment Strategy / Timeline




AgendaAgenda


Conversion Process

Have a rock solid detailed plan

Understand the environment architecture

Create sand box for learning

Apply OSS notes

Far fewer OSS notes to apply than SAP R/3 EnterpriseUpgrade


Conversion Process - Know Your Files

Import/Export package logsLOG

Migration Monitor Statusimport/export_state.properties

Communication Between Exportand Import Processes

SGN

DDL Statement Template forObject Creation

DDLORA.TPL

DB Size DefinitionDBSIZE.xml

PurposePurposeType of file ( Extension)Type of file ( Extension)

TaskTSK

CommandCMDTable of ContentTOCStructure/Table DefinitionSTR

Initial extent for tablesEXT


Conversion Process - Create DB/Central Inst.

Create a skeleton DB with locally managed tablespace(LMTS) and automatic segment space management(ASSM)

Improve performanceEasier to manage disk space

Run SAPINST to create central instanceSince the DB was created earlier (to fit our standards), chose MCOD tobypass DB creation

Extract R/3 Kernel, but not Oracle ClientClient already installed in our Shared Oracle_Home

May need to run CROCLLNK script from OSS Note #’s 521230, 539922


Conversion Process - Prepare Export/Import

Run INSTALL executable non-Unicode version forexport

After initial media extract, run sapinst version if youneed to restart

Run INSTALL executable UNICODE version for import

Get familiar with keydb.xml fileChange ORACLE_HOME referencesAcknowledge steps that have been performed

Change status from ERROR to OK ( ie: after Migration Monitor)

Terminate the export/import when R3load is running


Conversion Process

Setup Migration Monitor

Move your split table files into place.

Wait for scheduled downtime

Bring down SAP R/3

Take the last backup

Start Migration Monitor

Have plenty of snacks available




AgendaAgenda


Configure ‘Resumable Query’ for SAP Schema

Create a logon trigger to set resumable when SAP WP’sstart or connections are made to SAPR3 (R3load)CREATE OR REPLACE TRIGGER logon_set_resumable

AFTER LOGON

ON sapr3.schema

BEGIN

execute immediate 'alter session enable resumabletimeout &&TIMEOUT_VAL';

END;

The TIMEOUT_VAL is one of your choosing.We choose 7200 (seconds) in PRD and 1800 in all others.


Enable Autoextend on your tablespace datafiles

Set all your datafiles to autoextendSELECT 'ALTER DATABASE DATAFILE '||file_id||' AUTOEXTENDON NEXT ${EXTNDSIZE}M MAXSIZE ${MAXDFSIZE}M;'

FROM dba_data_files a, dba_tablespaces b

WHERE a.tablespace_name = b.tablespace_name

AND b.contents NOT IN ('UNDO','TEMPORARY')

AND a.tablespace_name NOT IN('PSAPTEMP','PSAPROLL','SYS_AUDIT');

This was used in conjunction with a script that periodically checksto add disk space from a pool and extends file systems.

Manage PSAPTEMP separately. With Resumable query, you havetime to analyze usage and extend if needed.


Favorite monitoring queries – who_is_sorting.sql

who_is_sorting.sqlcolumn tablespace format a10;column osuser format a10;column machine format a10;column bytes format 999,999,999,990break on tablespace skip 1compute sum of bytes on tablespaceset linesize 180

SELECT b.tablespace, b.segfile#, b.segblk#,b.blocks, b.blocks*8192 as bytes, a.sid, a.serial#,a.process, a.machine, a.username, a.osuser,a.status

FROM v$session a,v$sort_usage bWHERE a.saddr = b.session_addrORDER BY b.tablespace, b.segfile#, b.segblk#,b.blocks;


Favorite monitoring queries – sqlactive.sql

sqlactive.sqlset pagesize 60set linesize 130set long 1000column sql_text format A50 word_wrapped

select b.hash_value, a.status, b.executions NumExec,b.buffer_gets BuffGets,(b.buffer_gets/b.executions) GetsPer, a.sid,rpad(a.username,8) usernm, b.sql_text

from v$session a, v$sqlarea bwhere a.sql_address = b.addressand a.sql_hash_value = b.hash_valueand a.username <> 'SYS'and b.users_executing > 0and b.executions > 0and a.status != 'INACTIVE'order by buffer_gets;


Tips and Tricks - Continued

Export/import_monitor_cmd.properties (MigrationMonitor)

Use mounted file system - nfs vs. ftpSaves on space and no need to enable ftp (security risk)

Use explicit export order to force large packages earlier and getthem importing (and indexing) (OrderBy=<filename>)ExportNumJobs=16 ( on an 8-CPU system)

Restarting export/import processKnow your tools R3load and status(import/export_state.properties)Know how to reset an export/import package (LOG, TSK,SAPxxx.00x files)


Tips & Tricks - Continued

Splitting large tables into multiple packagesVery worthwhile, need to know your tables & indexes to determinewhere/how to split (‘where clause’ generation).Query to define boundaries for split table “where clause”select min(DOCNUM), max(DOCNUM), pcntile from (selectDOCNUM, ntile(10) over (order by DOCNUM) pcntile fromsapr3.EDIDS where mandt='002')group by pcntile;

“Where clause” bypasses export sort!!!“Where clause” for export/import MUST be identical ( including whitespace)

Custom indexes need to be rebuilt (if not in SAP DD)

Great opportunity to upgrade & reduce hardwareThe move to IA64 and faster CPU’s yielded a reduction in app serversfrom 5 to 3.


Summary

Unicode is not to be feared… don’t believe all the myth/horror!

Outage can be reduced using parallel/concurrent processingtechniques

Oracle “resumable query” is a key to a successful migration

It’s vital to have in-depth knowledge of your files and status forrestarting specific packages/phases

Practice… practice… practice makes perfect!Technical downtime for data movement (R3load/MigMon)450GB instanceExport time: 4h:40m, Import time: 7h:45m

Last hour of import was just one packageConcurrent Export and Import using MigMon


Further Information

Public Web:www.sap.comwww.sdn.sap.com

Americas’ SAP Users’ Group (ASUG)www.asug.com

Related Workshops/Lectures at SAP TechEd ’05SPC204 Performance Techniques for Unicode Conversion of Single Code PageSystems

Tue, 4:00 p.m. – 5:00 p.m., 253B, Thu, 5:15 p.m. – 6:15 p.m., 258BSPC202, Conversion of SAP Systems to Unicode

Wed, 1:45 p.m. – 3:45 p.m., 258C, Fri, 8:00 a.m. – 10:00 a.m., 258ASPC250 Making Programs Unicode Enabled

Wed, 10:30 a.m. – 12:30 p.m., 103, Thu, 1:45 p.m. – 3:45 p.m., 103SPC251 Unicode Interfaces – Data Exchange Between Unicode and non-UnicodeSystems

Wed, 4:15 p.m. – 6:15 p.m., 103, Thu, 4:15 p.m. – 6:15 p.m., 103

http://www.sap.com/

http://www.sdn.sap.com/

http://www.asug.com/

http://www.sapteched.com/usa/edu_sessions/session.htm?sid=792





Q&A

Questions?


Please complete your session evaluation.

Be courteous — deposit your trash,and do not take the handouts for the following session.

Be kind to others.

Feedback

Thank You !


No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The informationcontained herein may be changed without prior notice.Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iSeries, pSeries, xSeries, zSeries, z/OS, AFP,Intelligent Miner, WebSphere, Netfinity, Tivoli, and Informix are trademarks or registered trademarks of IBM Corporation in the United States and/or othercountries.Oracle is a registered trademark of Oracle Corporation.UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.Java is a registered trademark of Sun Microsystems, Inc.JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.MaxDB is a trademark of MySQL AB, Sweden.SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver and other SAP products and services mentioned herein as well as their respective logos aretrademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentionedare the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specificationsmay vary.

The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purposewithout the express prior written permission of SAP AG.This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains only intendedstrategies, developments, and functionalities of the SAP® product and is not intended to be binding upon SAP to any particular course of business, productstrategy, and/or development. Please note that this document is subject to change and may be changed by SAP at any time without notice.SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics,links, or other items contained within this material. This document is provided without a warranty of any kind, either express or implied, including but not limitedto the implied warranties of merchantability, fitness for a particular purpose, or non-infringement.SAP shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the useof these materials. This limitation shall not apply in cases of intent or gross negligence.The statutory liability for personal injury and defective products is not affected. SAP has no control over the information that you may access through the useof hot links contained in these materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-partyWeb pages.

Copyright 2005 SAP AG. All Rights Reserved

performance techniques for unicode conversion of … need to run crocllnk script from oss note #’s...

Documents