1 / 40

Yeah, Oracle can handle it

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 LeFevre Database Administrator AT&T Corporation Jose de Lavalle Technical District Manager

anisa
Download Presentation

Yeah, Oracle can handle it

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Michael NowakSenior Principal Service Delivery Engineer Oracle Corporation Bob LeFevre Database Administrator AT&T Corporation Jose de Lavalle Technical District Manager AT&T Corporation

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

  4. IntroductionPresentation Focus

  5. Presentation Focus • Siebel from an Oracle technology point of view • Differentiating highlights

  6. Functional view of Siebel on Oracle Business Background

  7. Customer Interaction Channel Customer Data Back Office Business Processes Customer Facing Business Processes There are Four CriticalComponents of CRM…... • Customer Interaction Channels • Customer Facing Business Processes • Back Office Business Processes • Enabling Customer Data 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.

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

  9. 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

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

  11. Technical view of Siebel on OracleDesign

  12. Software Design - Versions • AIX 5.1 • Oracle 9.2.0.3 (64bit) • Netbackup 4.5

  13. Hardware Design - Servers • Database Servers • IBM P690 16 CPU x 32GB Siebel Database Server • IBM P650 4 CPU x 4GB for RMAN Backups • Servers driving the Siebel database workload • IBM P670 16 CPU x 32GB ETL Server (batch) • SUN F6800 12 CPU x 24GB EAI Server (onlines) • 5 x IBM X440 8 CPU x 8GB Object Application Servers (onlines)

  14. Siebel Data Flow - Servers

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

  16. Siebel Data Flow - Storage

  17. 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) • 960K Stripe size • Each physical disk split into 9GB hypervolumes • 16 Member (134GB) metavolumes presented to host.

  18. Database Design • Scripts generated from DBCA • Control Files • 256 MB raw devices • Multiplexed three ways • Online Redo Logs • 2GB 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 • 4GB raw devices for datafiles • Datafiles added serially to enable interleaving across metavolumes

  19. Database Design • Tablespaces

  20. Database Design • 9iR1 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 • 9iR2 Features Used • Parallel DML on non-partitioned tables • Segment Level statistics • Locally Managed System Tablespace • Multiple redo allocation latches

  21. Database Design • “Interesting” init.ora parameters • Db_cache_size=3 GB • Db_16k_cache_size=6.5GB • Shared_pool_size=200 MB • Log_buffer=8 MB • Pga_aggregate_target=13GB • 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

  22. 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

  23. Technical view of Siebel on OraclePerformance

  24. 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)

  25. 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

  26. Performance - Oracle Perspective • Performance Tuning Process • 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)

  27. 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

  28. 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

  29. Performance – Highlights • Batch throughput • Conversion • 3-12 million EIM input records per hour • 240,000 logical I/Os per second • 100MB physical I/O per second (25MB redo, 75MB 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-10ms single block read time ms single block read time

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

  31. Performance – Batch Details Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 26,508,885.24 3,031,161.51 Logical reads: 239,192.91 27,350.54 Block changes: 156,733.80 17,921.74 Physical reads: 1,066.29 121.93 Physical writes: 3,067.71 350.78 User calls: 53.02 6.06 Parses: 24.27 2.78 Hard parses: 15.80 1.81 Sorts: 4.55 0.52 Logons: 0.02 0.00 Executes: 28.76 3.29 Transactions: 8.75 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

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

  33. 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

  34. Technical View of Siebel on OracleManaged Operations

  35. Backup – Software Components • Backup Components • EMC SRDF • Veritas Netbackup • Veritas Netbackup Oracle Agent • RMAN

  36. 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 • 3TB Database backed up in 6 hours using 9 tape drives

  37. 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

  38. Online Operations • Online index rebuilds

  39. Essential Oracle Tools • OEM • Console • Session statistics, Explain Plans, Long Operations • SQL Analyze • TopSessions • Capacity Planner • Statspack • Sbttest • Opatch

  40. Q&A

More Related