Yeah,%20Oracle%20can%20handle%20it

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 ATT
Corporation Jose de Lavalle Technical District
Manager ATT Corporation
3
Agenda

• Introduction
• Presentation focus
• Functional view of Siebel on Oracle
• Business background
• Technical view of Siebel on Oracle
• Design
• Performance
• Managed Operations
• QA

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
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
ATTs CRM Objectives

• To improve the interactions customers have with
  ATT Consumer and increasing operational
  efficiencies.
• Customer Objectives
• Improve overall customer experience by giving
  customers choices on how they interact with ATT
• 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 gt 30 full
• Tablespaces
• 4GB raw devices for datafiles
• Datafiles added serially to enable interleaving
  across metavolumes

19
Database Design

• Tablespaces

Name Size (GB) Type (Ext Management / Ext Allocation / Contents / Blocksize / Seg Space Mgt) Primary Use
SYSTEM 1 Local / System / Permanent / 8K / Manual Data Dictionary
TOOLS 5 Local / System / Permanent / 8K / Manual Statspack segments
TEMP 100 Local / Uniform / Temporary / 8K / Manual Temp segments
UNDOTBS1 36 Local / System / Undo / 8K / Auto Undo segments
USERS 1 Local / System / Permanent / 8K / Auto Misc schema segments
DATA_0 1300 Local / System / Permanent / 8K / Auto Siebel Base tables
DATA_1 1300 Local / Uniform / Permanent / 8K / Auto Siebel Base Tables
EIM_16K_AUTO 100 Local / System / Permanent / 16K / Auto Siebel EIM Tables
EIM_16K_UNIFORM 792 Local / Uniform / Permanent / 16K / Auto Siebel EIM Tables
SMTNC 400 Local / Uniform / Permanent / 8K / Auto DBA Maintenance
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_size3 GB
• Db_16k_cache_size6.5GB
• Shared_pool_size200 MB
• Log_buffer8 MB
• Pga_aggregate_target13GB
• Db_file_multiblock_Read_count128
• Log_parallelism8
• Optimizer_moderule
• Timed_statisticstrue
• Compatible9.2.0.0.0
• Processes4000
• Db_writer_processes8
• Undo_retention3600

22
Database Design

• Siebel 7.5 Restrictions
• Siebel does not support Oracles 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 SQLLoaded 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
• lt 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 minutes block 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

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
QA