Transcript A technical perspective on one of the world`s largest Siebel

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
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
Customer
Data
Customer
Facing
Business
Processes
Customer
Interaction
Channel
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 eBusiness applications designed
specifically for the communications industry,
based on the same architecture as all other
Siebel eBusiness applications.
Technical view of Siebel on Oracle
Design
Software Design - Versions
 AIX 5.1
 Oracle 9.2.0.3 (64bit)
 Netbackup 4.5
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)
Siebel Data Flow - Servers
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
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)
 960K Stripe size
–
–
Each physical disk split into 9GB hypervolumes
16 Member (134GB) metavolumes presented to host.
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
Database Design
 Tablespaces
Name
Size
Type (Ext Management / Ext Allocation
(GB)
/ 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
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
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
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
 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
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
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
(80ms 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
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
–
3TB 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
TopSessions
Capacity Planner
 Statspack
 Sbttest
 Opatch