Yeah Oracle can handle it A technical perspective

Yeah, Oracle can handle it A technical perspective on one of the world's largest Siebel implementations on Oracle

Michael Nowak Senior Principal Service Delivery Engineer Oracle Corporation Bob Le. Fevre Database Administrator AT&T Corporation Jose de Lavalle Technical District Manager AT&T Corporation

Agenda Ÿ Introduction – Presentation focus Ÿ Functional view of Siebel on Oracle – Business background Ÿ Technical view of Siebel on Oracle – – – Design Performance Managed Operations Ÿ Q&A

Introduction Presentation Focus

Presentation Focus Ÿ Siebel from an Oracle technology point of view Ÿ Differentiating highlights

Functional view of Siebel on Oracle Business Background

There are Four Critical Components of CRM…. . . • Customer Data Customer Facing Business Processes Customer Interaction Channels • Customer Facing Business Processes • Back Office Business Processes • Enabling Customer Data Back Office Business Processes For CRM to be successful, they all must operate together to create a seamless experience for the customer. Enabling the right customer data is the key to success because it embodies the other components.

Customer Interaction Channels Customer Facing Business Processes (Front Office) Back Office Business Processes Here is how the targeted Business Processes stacked up relative to the CRM capabilities… Customer Data Order Management Analytics Billing Sales and Marketing Campaign Mgmt Customer Service Voice E-Mail Web Direct Indirect

AT&T’s CRM Objectives To improve the interactions customers have with AT&T Consumer and increasing operational efficiencies. Ÿ Customer Objectives – Improve overall customer experience by giving customers choices on how they interact with AT&T – Target and retain the most valuable customers Ÿ Operational Objectives – Increase the productivity of live agent interactions – Increase acquisition list yield and quality – Reduce gross to net sales loss

The Siebel CRM Solution • Siebel Communications on an Oracle Server platform was selected. • Siebel Communications is a comprehensive suite of e. Business applications designed specifically for the communications industry, based on the same architecture as all other Siebel e. Business applications.

Technical view of Siebel on Oracle Design

Software Design - Versions Ÿ AIX 5. 1 Ÿ Oracle 9. 2. 0. 3 (64 bit) Ÿ Netbackup 4. 5

Hardware Design - Servers Ÿ Database Servers – – IBM P 690 16 CPU x 32 GB Siebel Database Server IBM P 650 4 CPU x 4 GB for RMAN Backups Ÿ Servers driving the Siebel database workload – – – IBM P 670 16 CPU x 32 GB ETL Server (batch) SUN F 6800 12 CPU x 24 GB EAI Server (onlines) 5 x IBM X 440 8 CPU x 8 GB Object Application Servers (onlines)

Siebel Data Flow - Servers

Hardware Design - Storage Ÿ EMC SAN – 2 x ED 64 M connectrix switches (2 Gb) Ÿ Disk Storage – EMC 8830 Symmetrix Primary DB Storage Ÿ 24 GB EMC cache Ÿ 244 x 73 GB 10, 000 RPM drives Ÿ 64 x 181 GB 7200 RPM drives – EMC 8530 Symmetrix SRDF DB Copy for RMAN Backups Ÿ 16 GB EMC cache Ÿ 48 x 181 GB 7200 RPM drives Ÿ Tape Backup Storage – – Powderhorn Tape Silo STK 9840 B Tape Drives

Siebel Data Flow - Storage

I/O Subsystem Design - Database Server Ÿ I/O subsystem design – – – 4 Fibre Paths into the EMC SAN from DB server EMC Powerpath Hardware Striping and Mirroring Ÿ SAME Methodology (Stripe And Mirror Everything) Ÿ 960 K Stripe size – – Each physical disk split into 9 GB hypervolumes 16 Member (134 GB) metavolumes presented to host.

Database Design Ÿ Scripts generated from DBCA Ÿ Control Files – – 256 MB raw devices Multiplexed three ways Ÿ Online Redo Logs – – 2 GB raw devices Eight groups, two members each Ÿ Archive Redo Logs – – 134 GB file system Archive logs copied to tape daily and when filesystem > 30% full Ÿ Tablespaces – – 4 GB raw devices for datafiles Datafiles added serially to enable interleaving across metavolumes

Database Design Ÿ Tablespaces Name Size Type (Ext Management / Ext Allocation (GB) / Contents / Blocksize / Seg Space Mgt) Primary Use SYSTEM 1 Local / System / Permanent / 8 K / Manual Data Dictionary TOOLS 5 Local / System / Permanent / 8 K / Manual Statspack segments TEMP 100 Local / Uniform / Temporary / 8 K / Manual Temp segments UNDOTBS 1 36 Local / System / Undo / 8 K / Auto Undo segments USERS 1 Local / System / Permanent / 8 K / Auto Misc schema segments DATA_0 1300 Local / System / Permanent / 8 K / Auto Siebel Base tables DATA_1 1300 Local / Uniform / Permanent / 8 K / Auto Siebel Base Tables EIM_16 K_AUTO 100 Local / System / Permanent / 16 K / Auto Siebel EIM Tables EIM_16 K_UNIFORM 792 Local / Uniform / Permanent / 16 K / Auto Siebel EIM Tables SMTNC 400 Local / Uniform / Permanent / 8 K / Auto DBA Maintenance

Database Design Ÿ 9 i. R 1 Features Used – – – – – Online index builds Index Monitoring Automatic Segment Space Management Multiple Block Sizes Automatic Undo Management Metadata API Default Temporary Tablespace PGA Aggregate Target Block level media recovery Ÿ 9 i. R 2 Features Used – – Parallel DML on non partitioned tables Segment Level statistics Locally Managed System Tablespace Multiple redo allocation latches

Database Design Ÿ “Interesting” init. ora parameters – – – – Db_cache_size=3 GB Db_16 k_cache_size=6. 5 GB Shared_pool_size=200 MB Log_buffer=8 MB Pga_aggregate_target=13 GB Db_file_multiblock_Read_count=128 Log_parallelism=8 Optimizer_mode=rule Timed_statistics=true Compatible=9. 2. 0. 0. 0 Processes=4000 Db_writer_processes=8 Undo_retention=3600

Database Design Ÿ Siebel 7. 5 Restrictions – – – Siebel does not support Oracle’s cost based optimizer (CBO) in Siebel release 7. 5. CBO is required for the following features: Ÿ Partitioning Ÿ Hints (except RULE hint) Ÿ Index organized tables Ÿ Parallel query Ÿ Materialized views Ÿ Function based indexes Ÿ Reverse key indexes These features were not used for any of the “canned” Siebel application Some of these features were used for customized processing

Technical view of Siebel on Oracle Performance

Performance - Siebel Perspective Ÿ Process Overview – Batch – – Siebel tables are categorized into EIM and base tables Siebel EIM tables are SQL*Loaded with ETL Files Siebel EIM stored procedures convert the EIM table data into base table data by generating relationships and populating the base tables as necessary. 3 Million records/hour goal (exceeded in all phases)

Performance - Siebel Perspective Ÿ Changes implemented – – Batch Ÿ EIM data broken into smaller “clone” tables Ÿ Sort EIM data to improve cache hits for range scans Ÿ Increase parallel processes (Concurrent Siebel jobs flows) Ÿ Adjust batch sizes (commit frequency) Ÿ Adjust batch ranges to avoid cache buffer chain contention Ÿ Disable relationship building options from within Siebel Tools. Apply relationships outside of Siebel EIM using Oracle Parallel DML for non partitioned tables with custom SQL code. Online Ÿ Customized screens

Performance - Oracle Perspective Ÿ Performance Tuning Process 1. 2. 3. 4. 5. 6. 7. Ensure statspack is running at 30 minute intervals with level 7 snapshots Run Siebel process (conversion/batch, or online) If necessary, use dbms_support package to enable event 10046 in one of the shadow processes Use tkprof to summarize trace file Analyze statspack report and summarized trace files Implement changes If performance requirements met, stop. If not, go back to (1)

Performance - Oracle Perspective Ÿ Changes implemented – – Batch Ÿ Index additions/changes/removal Ÿ Parameter changes Multiple Buffer Caches Log_parallelism increased Log_buffer increased Db_file_multiblock_read_count increased Online Ÿ Index additions/changes/removal Ÿ Creation of stored outlines Ÿ Inittrans increased

Performance - System Perspective Ÿ Monitor system resources – – – Iostat Vmstat Truss Sar STP Ÿ Example Changes – – – EMC cache increased AIX swap space increased AIX TCP/IP system parameters increased

Performance – Highlights Ÿ Batch throughput – Conversion Ÿ 3 -12 million EIM input records per hour Ÿ 240, 000 logical I/Os per second Ÿ 100 MB physical I/O per second (25 MB redo, 75 MB datafile) – Customized Siebel Relationship builds via 8 way Parallel DML for non partitioned tables Ÿ Elapsed time of 20 minutes per clone table Ÿ Online response time – Performance Testing Ÿ < 3 second average end-to-end response time for online screens Ÿ 80 ms average instance response time per execute Ÿ 5 -10 ms single block read time

Performance – Highlights Ÿ Maintenance throughput – 16 way Parallel Create Table as Select (PCTAS) of 4 GB test table with archiving enabled Ÿ 150 MB physical I/O per second (50 MB redo, 100 MB datafile) Ÿ Elapsed time of 1 minute – 16 way Parallel Create Table as Select (PCTAS) of 100 GB S_ASSET table with archiving disabled Ÿ 205 MB physical I/O per second (all datafile IO) Ÿ Elapsed time of 18 minutes – Parallel Online Index Build of 5 GB unique index on S_ORG_EXT with concurrent batch conversion being processed Ÿ Elapsed time of 44 minutes block read time

Performance – Batch Details Load Profile ~~~~~~ Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Logons: Executes: Transactions: Per Second -------26, 508, 885. 24 239, 192. 91 156, 733. 80 1, 066. 29 3, 067. 71 53. 02 24. 27 15. 80 4. 55 0. 02 28. 76 8. 75 Per Transaction -------3, 031, 161. 51 27, 350. 54 17, 921. 74 121. 93 350. 78 6. 06 2. 78 1. 81 0. 52 0. 00 3. 29 Top 5 Timed Events ~~~~~~~~~ % Total Event Waits Time (s) Ela Time ---------------------- -------db file sequential read 57, 912 979 54. 69 CPU time 341 19. 03 log file sync 631 324 18. 09 db file parallel write 885 121 6. 77 log file switch completion 68 8. 47

Performance – Online Details Instance response time per execute in seconds (80 ms average)

Performance – Scalability Challenges Ÿ All Oracle response time and throughput requirements were met Ÿ Stress tests did show that if we needed to meet even more stringent requirements, the following scalability challenges would have to be addressed: – – – EMC cache size increased Number of EMC fibre adapter cards increased Number of physical disks increased

Technical View of Siebel on Oracle Managed Operations

Backup – Software Components Ÿ Backup Components – – EMC SRDF Veritas Netbackup Oracle Agent RMAN

Backup Process and Performance Ÿ Backup Process – – RMAN backup of Archivelogs from the primary server SRDF copy of DB created daily and mounted on the backup server RMAN backup of the SRDF copy from the backup server The goal of backing up from the SRDF copy is to offload CPU cycles and I/O bandwidth from the primary cabinet/server Ÿ Backup Performance – 3 TB Database backed up in 6 hours using 9 tape drives

Recovery - Process and Testing Ÿ Recovery Process – RMAN Restore/Recovery commands issued from Primary DB server (Alternate Node restore) Ÿ Testing Scenarios – – Block Recovery Tablespace / Datafile Recovery Point In Time Database Recovery Recover to a Different Incarnation

Online Operations Ÿ Online index rebuilds

Essential Oracle Tools Ÿ OEM – Console Ÿ Session statistics, Explain Plans, Long Operations – – – SQL Analyze Top. Sessions Capacity Planner Ÿ Statspack Ÿ Sbttest Ÿ Opatch