Real-World MAA Best Practices for Siebel Applications on Oracle Database Floyd Manzara

Mehdi Gerami

Technical Architect

Siebel Product Management

Kris Trzesicki

Richard Exley

Senior Database Specialist

Siebel MAA and Exadata

Disclaimer

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

2

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Program Agenda  Siebel Maximum Availability Architecture  Siebel and Active Data Guard  Siebel Testing with Oracle Database Replay  WestJet: Siebel on Exadata  Siebel Performance  Siebel Workload Tagging

3

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Maximum Availability Architecture

4

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Maximum Availability Architecture Primary Site

Secondary Site

Web Servers

Siebel Servers Siebel Gateway Server

Siebel Gateway Server

DB Servers Oracle RAC and ASM

Oracle RAC and ASM Oracle Data Guard

Siebel File System

5

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle Database

File System Replication

Oracle Standby Database

Siebel File System

Database Maximum Availability Architecture Best Practices

Real Application Clusters & Clusterware Fault Tolerant Server Scale-Out

Online Upgrade

Data Guard

Upgrade Hardware and Software Online

Active Failover Replica

Primary Site

Secondary Site

 Oracle RAC and Clusterware  Oracle Data Guard

Database Servers

Database Servers

Storage

Storage

 Oracle ASM  Oracle Flashback Database  Oracle Recovery Manager Automatic Storage Management Fault Tolerant Storage Scale-Out

6

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Flashback Correct Errors by Moving Back in Time

Recovery Manager & Oracle Secure Backup Low Cost High Performance Data Protection and Archival

Siebel High Availability Architecture HTTP Load Balancers Redundant Configuration

Best Practices  Load Balanced Web Servers  Clustered or Load Balanced

Web Servers Web Servers

Load Balanced

Siebel Servers  Clustered Gateway Server Siebel Servers

Clustered Siebel Servers Typically hosting Gateway Server, Siebel Remote, etc.

7

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Load Balanced Siebel Servers Typically hosting Object Managers, Workflow, etc.

Siebel MAA on Exalogic and Exadata Case Study Primary Site (Site 1)

Secondary Site (Site 2)

F5 LTM 4200v

vm36

vm52

F5 LTM 4200v

Web Servers

cn22

cn21

Virtualized Exalogic X3-2

Bare Metal Exalogic X3-2 Siebel Servers Siebel Gateway vm02 and vm05

Siebel Server vm47 and vm30

Gateway and Siebel Server cn23 and cn24

DB Servers

Exadata X3-2

Exadata X3-2

Oracle Data Guard Siebel File System (ZFS)

8

Exadata db01 and db02

Exadata db07 and db08

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle Database (Exadata)

File System Replication

Oracle Standby Database (Exadata)

Siebel File System (ZFS)

Unplanned Outage Testing Results Component

Outage Type

Outage (seconds)

Error Messages (% Users)

Siebel Web Server

Instance Failure

0

0.3%

0%

Node Failure

25 2 (partial)

0.3%

0%

Node Failure

0

50%

50%

Gateway Server

Instance Failure

0

0%

0%

30 2

Node Failure

0

0%

0%

100 2

Database Server

Instance Failure

10 (partial)

2% 1

0%

Node Failure

45 (complete)

2% 1

0%

Primary Site

Full Site Failure

77 (complete)

100%

100%

Siebel Server

New Login Failover Time (% users) (seconds)

1 = Mostly addressed by a Siebel product enhancement in Siebel 8.1.1.11 2 = Can be tuned to a shorter time by configuring more frequent monitoring and shorter timeouts

9

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

77

Siebel MAA Best Practices

 See “Siebel MAA on Exalogic and Exadata” New! – http://www.oracle.com/technetwork/database/availability/siebel-maa-

exalogic-exadata-2020333.pdf – Detailed steps to establish a Siebel MAA configuration on Exalogic and

Exadata – Detailed recovery steps for unplanned outages – Detailed outage test results – 30 best practices to achieve maximum Siebel availability, including several

new and improved recommendations 10

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel and Active Data Guard

11

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel and Active Data Guard Active Data Guard Features  Physical standby database open for transactionally consistent

read only access  Real-time apply of redo from primary resulting in minimal lag  Real-time block repair in the event of a block corruption on the primary  Incremental database backup support

12

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel and Active Data Guard Query Offload to Standby Read/Write Siebel Users

Query Offload from Primary

Read Only Siebel Users

Secondary Site (Read Only)

Primary Site (Read Write)

Web Servers

Read Only Siebel App Siebel Servers Siebel Gateway Server

Siebel Gateway Server

Real-time Redo Apply Database Servers Oracle RAC and ASM

Oracle RAC and ASM Oracle Data Guard Siebel File System

13

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle Database

File Synchronization

Oracle Active Data Guard Standby Database

Read Only Siebel File System Replica

Siebel and Active Data Guard Faster Queries for Remote Users Read/Write Siebel Users

Read/Write Siebel Users

Secondary Site (Read Only)

Primary Site (Read/Write)

Oracle Data Guard

Real-time Redo Apply Siebel Gateway Server

Siebel Gateway Server

San Francisco Oracle RAC and ASM

Beijing Oracle Database

14

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle RAC and ASM

Oracle Active Data Guard Standby Database

Read Only Siebel Users

Fast Queries for Remote Users

Siebel and Active Data Guard Opinion Poll  Would a read only Siebel application be useful for you? Read/Write Siebel Users

Read Only Siebel Users Secondary Site (Read Only)

Primary Site (Read Write)

To offload work from the primary?

Web Servers

Siebel Servers Siebel Gateway Server

Siebel Gateway Server

Database Servers Oracle RAC and ASM

Oracle RAC and ASM Oracle Data Guard Siebel File System

Oracle Database

File Synchronization

Oracle Active Data Guard Standby Database

Read Only Siebel File System Replica

Read/Write Siebel Users

Read Only Siebel Users

Read/Write Siebel Users Primary Site (Read/Write)

Secondary Site (Read Only)

Oracle Data Guard

Siebel Gateway Server

Siebel Gateway Server

Oracle RAC and ASM

Beijing

Oracle Active Data Guard Standby Database

? 15

To improve query performance for remote users?

Oracle RAC and ASM

San Francisco Oracle Database

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

What else?

Siebel and Active Data Guard The Challenge and a Potential Solution

Experimental and not currently supported!

 Challenge: The S_USER and S_SSA_ID tables are always updated

by the Siebel, even when the users are only performing queries  Solution: 1. Create a “dummy” read/write database for S_USER and S_SSA_ID 2. Create a separate “RO” table owner in the Siebel database: 

S_SSA_ID and S_USER are database links to “dummy”



All the other tables are synonyms of the real tables

3. When running against the Active Data Guard standby, Siebel

configured to connect to the “RO” table owner 16

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Testing with Oracle Database Replay

17

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Testing with Oracle Database Replay Oracle Database Replay Capture and Replay Workflow Production

Cross Platform

Real Production Workload

Test System Replay Clients

Cross Version

Oracle Database

Workload Capture

18

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Preprocessing

Workload Replay

Analysis & Reporting

Siebel Testing with Oracle Database Replay Results in Our Lab  Siebel 8.1.1.9  Oracle Database 11.2.0.3  33,000 concurrent simulated Siebel users  333 GB workload captured over a 4 hour period  350 million user calls, 19 million transactions  Successfully replayed on test system

19

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Testing with Oracle Database Replay Best Practices  See “Oracle Database Real Application Testing User's Guide” for more

details on Oracle Database Replay: – http://docs.oracle.com/cd/E11882_01/server.112/e41481/toc.htm

 See white paper “Siebel on Exadata” for best practice for running

Oracle Database Replay with a Siebel workload: – http://www.oracle.com/technetwork/database/features/availability/maa-wp-

siebel-exadata-177506.pdf

20

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

WestJet: Siebel on Exadata Floyd Manzara [email protected] Kris Trzesicki [email protected]

WestJet: Overview THE AIRLINE INDUSTRY IS TOUGH “If I’d been at Kitty Hawk in 1903 when Orville Wright took off, I would have been farsighted enough, and public-spirited enough – I owed this to future capitalists – to shoot him down.” - Warren Buffet

WestJet: Overview Canadian airlines operating today Adler Aviation Aeropro Air Canada Air Canada Jazz Air Canada Jetz Air Creebec Air Express Ontario Air Georgian Air Inuit Air Labrador Air Mikisew Air North Air Nunavut Air Saguenay Air Satellite Air Southwest Air Tindi Air Transat

Aklak Air Alberta Citylink Aldair Aviation Alkan Air Alta Flights Arctic Sunwest Charters Bar XH Air BCWest Air Bearskin Airlines Brock Air Services Buffalo Airways Calm Air Canadian Air Taxi Canadian Metro Airlines Canadian North CanJet

Cargojet Airways Central Mountain Air CHC Helicopter Cloud Air Corporate Express Cougar Helicopters Craig Air Enerjet Enterprise Airlines First Air Flair Airlines Green Air Harbour Air Harv’s Air Service Hawkair Helijet Innu Mikun Airlines Integra Air

KD Air North-Wright Airways Keewatin Air Orca Airways Kelowna Flightcraft Pacific Coastal Airlines Kenn Borek Air Pascan Aviation Keystone Air Service Perimeter Aviation Kivalliq Air Porter Airlines Kootnay Direct Airlines Prince Edward Air Pronto Airways Lakeland Aviation Provincial Airlines LR Helicopters Inc. Maritime Air Charter Regional 1 Salt Spring Air Morningstar Air Express SkyLink Aviation Nolinor Aviation SkyNorth Air Nor-Alta Aviation Skyservice North Cariboo Air Skyxpress Airline Northern Thunderbird Stanton Airways Air Summit Air Northwestern Air Sunwest Aviation

Sunwing Airlines Superior Airways Thunder Airlines Tofino Air Trans Capital Air Transwest Air Universal Helicopters Vancouver Island Air Voyageur Airways Wasaya Airways West Coast Air West Wind Aviation Westex Airlines WestJet White River Air Wilderness Air, ltd

WestJet: Overview Canadian airlines graveyard 30000 Island Air Air Alliance Air Atlantic Air Baffin Air BC Air Canada Tango Air Club International Air Dawson Air Nova Air Ontario All Canada Express Austin Airways Baxter Aviation Canada 3000 Canada West Air Canada West Airlines Canadian Airlines Canadian Airways Canadian Colonial Airways Canadian Pacific Air Lines

Canadian Regional Airlines City Express Colonial Airlines Eastern Provincial Airways Fortunair Globemaster Air Cargo Great Lakes Airlines Greyhound Air Harmony Airways Ilford-Riverton Airways Inter-Canadien Jetsgo Knighthawk Air Express Lamb Air Maestro Maritime Central Airways Millardair NAC Airways Nationair Newfoundland Airways

Norcanair Nordair NorOntair North Canada Air NWT Air Odyssey International Ontario Express Pacific Western Airlines Peace Air Pem Air Quebecair Express Queen Charlotte Airlines QuikAir Regal Air Roots Air Royal Aviation Sonicblue Airways Southern Frontier Airlines Time Air Torontair

Transair Trans-Canada Air Lines Trans-Provincial Airlines Triton Airlines Val Air Vision Airways Corporation Vistajet Wardair Western Canada Airways Westex Airlines Wilderness Air LTD. Winnport Worldways Canada World-Wide Airways Zip Zoom Airlines

WestJet: Overview US airlines graveyard – “A” only page… AAXICO Airlines (1946-1965, to Saturn Airways) Access Air (1998 - 2001) ADI Domestic Airlines Aeroamerica (1974 – 1982) Aero Coach (1983 – 1991) Aero International Airlines Aeromech Airlines (1951 - 1983, to Wright Airlines) AeroSun International AFS Airlines Air America (operated by the CIA in SouthEast Asia) Air America (1980s) Air Astro Air Atlanta (1981 - 88) Air Atlantic Airlines Air Bama Air Berlin, Inc. (1978 – 1990) Airborne Express (1946 - 2003, to DHL)

Air California, later AirCal (1967 - 87, to American) Air Carolina Air Central (Michigan) Air Central (Oklahoma) Air Chaparral (1980 - 82) Air Chico Air Colorado Air Cortez Air Florida (1972 - 84) Air Gemini Air General Air Great Lakes Air Hawaii (1960s) Air Hawaii (ceased Operations in 1986) Air Hyannix Air Idaho Air Illinois Air Iowa

Airlift International (1946 - 81) Air Kentucky Air LA Air-Lift Commuter Air Lincoln Air Link Airlines Air Link Airways Air Metro Air Miami Air Michigan Air Mid-America Air Midwest Air Missouri Air Molakai (1980) Air Molakai (1990) Air Molakai-Tropic Airlines Air Nebraska Air Nevada Air New England (1975 - 81)

WestJet: Overview By the numbers

1996

2013

5 destinations

87 destinations

1 country

18 countries

3 aircraft 220 WestJetters

100+ 737 aircraft 9,600 WestJetters 10 code-share agreements 33 interline partnerships 1,300 resumes each week 432 marriage proposals 17 million guests Our new regional airline: WestJet Encore

WestJet: Business Case Goals • By 2016, to grow Guest Self Service to 60% – Multi-channel shopping and reservation features – Enhanced ancillary sales

• Implement a database platform that will provide WestJet’s exceptional Guest Experience – – – – –

Operational efficiency Service availability Performance consistency Enhanced support Efficiently utilize Oracle RDBMS licensing

WestJet: Process – Look to Book Mobile Channels

Guest Applications westjet.com Rewards ESB – Web Services

Data Stores Internet Booking Hosted

Profile

Check-in

Kiosk Channels

WestJet Travel

IVR Channels

Employee Self Serve

Booking Notifications

Change / Cancel Rewards Redemption Flight Stats Identity Mobile Services Check-in GDS Functions

Siebel Loyalty

Identity/ Profile

Reservation Applications

Reservation

Browser Channels

GDS

ENS

Aviation MRO

Employee Apps

IVR

Email Delivery Vendor Files

WestNet

Other External Partners Vacations Air Car Core

Hotel

WestJet: Siebel Functionality • Loyalty – Frequent flyer system - WestJet Dollar & Vouchers – Compensation – Price guarantees, irregular operations

• • • • • •

Guest Profiles Guest Portal Member Promotions – Giveaways, etc. Member Statements Marketing campaigns – Bulk Email, Vacation Deals OBIEE – Analytical reporting for marketing and loyalty programs – Financial reconciliation

WestJet: Siebel • Statistics – – – –

1.2 million Loyalty member accounts 2.4 million Profiles 300 agents and 300 portal connections at any given time 100,000/day batch transactions from 3rd parties

• Versions – 8.1.1.5

WestJet: Consolidation • •

25+ servers  2 Exadata Consolidated 16 DBs

• •

Oracle Platinum Services Reduced data center admin needs Server Memory 12.37 10.09

57.30

10.09 5.47 3.95 4.06 3.73 3.57 1.94 2.02 2.23

2.48 2.77 2.56 2.45

3.05

WestJet: Siebel Performance • • Siebel on HP-UX and EVA storage • • •

Siebel on Exadata

• • • • •

Expert application and database tuning Customized database statistics Frequent I/O performance issues Long running backup Overlap in heavy batch processing

No tuning required post migration – 1 index added Default Oracle Maintenance statistics used No I/O performance issues Faster backup Batch jobs fit in the allocated windows

WestJet: Siebel Performance Response time: Backup time: Process

0.01 centi-seconds 0.5h 5h (HP)

HP Average Before Exadata

Average After Exadata

(Exadata + Data Domain) Improvement

Revenue Accounting – Corrections

13.5 min

5 sec

16,200%

Customer Profiles

33 minutes

13 minutes

253%

Revenue Accounting – Daily TX

174 minutes

9 minutes

1,900%

WestJet: Data Center Replication

Pre-Exadata (2012)

Picture From: MAA Best Practices Replication Technologies for High Availability and Disaster Recovery –A Technical Overview Joseph Meeks, HA\MAA Product Management, Oracle Lawrence To, MAA Development, Oracle

WestJet: Data Center Replication Western Data Centre

Eastern Data Centre

VM Farm for Siebel TAF Connection Pooling

VM Farm for Siebel

DC rented from 3rd party • Platinum support on hardware • • DC Managed and owned by WestJet • Platinum support on hardware

TAF Connection Pooling

Physical Data Guard

Consolidated Exadata

3200 km - Asynchronous Consolidated Fast Switchover or Failover Exadata Simplicity and Performance Protection from Lost Writes and Corruption

WestJet: IBE - Hybrid Columnar Compression RESVCOMPADDITINFO 1 2

Partition 1 No compression Partition 2 Compress for query Partition 3 Compress for archival

3 4 …

March 2013

April 2013

+ 55 GB

X 50 X 170

March March 2014 2014 + 1.1 TB

WestJet: IBE - Hybrid Columnar Compression Hint 1: Find right data and partition by frequency of data updates

•

Technology is unique to Exadata

•

No additional license cost

•

Partitioned tables will guarantee performance

•

Delay additional storage purchases

Archive Query

Hint 2: Use DBMS_REDEFINITION for partitioning

54 GB 34 GB

0.32 GB 0.68 GB

Disk space saved : 87 GB •

Decreased network requirements for replication

WestJet: Future • • • •

Siebel 8.2.2.3 – 3rd Qtr. 2013 Siebel HCC (1-2 Million/Week) – 4th Qtr. 2013 Memory Optimization – September 2013 Purchasing two additional ¼ Rack X3-2 – 1st Qtr. 2014 – Additional Siebel Functionality  Travel Agent Commission – 2013  Call Center CTI integration and Case Management - 2014 – Revenue Management - 2013 – BI Analytics Initiative – 2014 – Other Tier 1 database consolidations … - 2013/2014

• Active Data Guard / Golden Gate - 2014

WestJet: Benefit Summary • Exadata: A platform that enables WestJet’s exceptional Guest Experience – High Availability and Performance – Exceptional Oracle support

• Consistent patching practice and schedule • Cost avoidance of additional hardware and software • Time to Market benefits – Initial build and configuration – Performance Load testing

• Smaller Data Center footprint

WestJet: Resources

WestJet Oracle Exadata Technical Case Study http://www.oracle.com/technetwork/serverstorage/engineered-systems/exadata/index.html

WestJet Video http://medianetwork.oracle.com/media/ Search: WestJet

Siebel Performance

41

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel on Exalogic - Exadata Benchmark

 Siebel benchmarks on Exalogic X3 – Exadata X3: – Call Center & Order Management Results are available and presented in

later slides  Exalogic & Exadata provide an excellent scalable platform for all Siebel

tiers  Note: – Performed on Siebel Release 8.1.1.10 using Oracle 11.2.0.3 – Siebel preferred version is 8.1.1.5 onward (8.1.1.4 is also certified).

42

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Performance Testing Architecture IB Network IP/IB

OATS-OLT

43

Web Server + Siebel Gateway

Exalogic X3 (Siebel App Server)

Exadata X3 (Siebel DB)

Dual CPU 8-Core 96 - 288 GB RAM (16-48 cores - 1-3 nodes)

Dual CPU 8-Core 256 - 768 GB RAM (16-48 cores - 1-3 nodes)

Dual CPU 8-Core 256 GB RAM (16-core – 1 Node)

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Test Workloads – Call Center  Call Center Use Case –

Siebel Finance used as Reference Application

–

Benchmark simulates multi-user Call Center Agents

 OATS-OLT Scripts –

–

–

44

Sales: 

Create Contact and Opportunity



Lookup Account, Associate Contact & Opportunity



Add products and create Quote and Order

Create Service Request : 

Lookup Contact



Create Service Request and add Entitlement



Lookup Account, associate contact & opportunity



Add products and create Quote and Order

Update Service Request : 

Lookup Service Request



Add Solution for the Service Request

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Exa* 13k & 39k Users Call Center Benchmark2 Number of Call Center Users

 Each Exalogic node can

host 13,000 Call Center users  Exalogic and Exadata demonstrate linear CPU and performance scalability 1

The total number of records created are ballpark figures. During the benchmark there are significant number of queries and updates that are not reflected in the table on the left.

2

Siebel App Servers Avg. CPU* Siebel App Mem (MB) DB Server CPU Web Servers Avg. CPU Avg RT (sec):

13,000 Users 39,000 Users 72.56% 105,636 9.51% 19.02% 0.049

73.03% 108,821 26.60% 17.14% 0.046

*13k Users benchmark 1 Siebel App Server 33k Users benchmark 3 Siebel App Server

Entity1 Contacts Opportunities Orders Activities Service Requests Resolution Items

Creation/hour Creation/day - 8 hours 113,124 904,992 113,244 905,952 113,016 904,128 742,170 5,937,360 148,436 1,187,488 120,120 960,960

Actual results may vary, based on a broad range of implementation-specific factors, such as transaction mix, hardware platform, network parameters, and database size. Oracle does not warrant or guarantee that customers will obtain the same or similar results, even if they use the same or similar equipment and/or software applications. Oracle does not warrant, endorse, or guarantee any performance of any products, any results desired or achieved, or any statements made within this document.

45

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Test Workloads – Order Management  Order Management Use Case –

Siebel Communications used as Reference Application

–

Benchmark simulates multi-user Call Center Agents

 OATS-OLT Scripts –

Create Order:  Create Account and Address  Lookup Catalog and Price List  Create Order and Asset

–

Modify Order:  Lookup Account and Asset  Lookup Catalog and Price List  Modify Order and Asset

46

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Exa* 11k Users Order Management Benchmark2 Exalogic - Exadata provides an excellent scalable platform for all Siebel tiers  One Exalogic node can host more than

7,000 Order Management users  Siebel DB Server on Exadata

demonstrates 2 times better in CPU usage compared to standard HW  The Response Time (RT) is 2 times

better compared to standard HW

No. of Users Siebel App Server Avg CPU Siebel App Mem (MB) DB Server CPU Web Server Avg CPU Avg RT (sec): Entity 1 ORDERS ORDER ITEMS ASSETS

7000 Exalogic/Exadata 68.27% 94,179 7.04% 6.21% 0.137

Creation/hour Creation/day (8 hours) 49,000 285,000 255,000

392,000 2,282,000 2,040,000

1

The total number of records created are ballpark figures in 10k users benchmark. During the benchmark there are significant number of queries and updates that are not reflected in the table above.

2

Actual results may vary, based on a broad range of implementation-specific factors, such as transaction mix, hardware platform, network parameters, and database size. Oracle does not warrant or guarantee that customers will obtain the same or similar results, even if they use the same or similar equipment and/or software applications. Oracle does not warrant, endorse, or guarantee any performance of any products, any results desired or achieved, or any statements made within this document.

47

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proven Best Practices for Scalable Deployment  Minimize scripting & customization – Stay close to the Out-Of-The-Box where feasible – Use Siebel features instead  Workflow can be used to encapsulate complicated business logic – Available from anywhere in the Siebel application – Available from outside application

 Integration – Siebel is primarily a CRM system, not an integration hub – Best performance is always achieved by performing transformation and routing in

the middleware – Asynchronous messaging yields significant better performance

48

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proven Best Practices for Scalable Deployment Siebel Infrastructure  Siebel Gateway fix is a must for large scale deployments – Major improvement for Siebel Server start up and recycling – Please see the Alert (MOS - Doc Id 1450272.1) – Example: The first large customers using this fix see Siebel Enterprise Startup

from ~2 hrs 40 mins down to ~ 15 mins  Siebel Connection Pooling reduces number of database connections

There are 2 important QF (See MOS) – For majority of cases 1:2-5 ratio (1 connection for 2-5 users)  The 1:1 ratio keeps the connections which reduces significantly the cost of

reconnection – For eAI, eProdCfg, and WFP 1:1-3

49

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Best Practices for Scalable Deployment Database and Query Tuning  Eliminate or reduce the use of expensive queries: – Case-Insensitive queries – Sorting important data sets – Search specs generating large result sets

 Tune the database – Please see Doc ID 781927.1 on MOS

 Review and tune slow running queries – Siebel SQL Tagging in development – Workload Tagging in performance testing and in production

50

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Best Practices for Scalable Deployment Database and Query Tuning  Be prepared to handle ad-hoc queries in production  Some long running queries are part of user transactions and must

not be killed – Otherwise, users will restart

 Once a query is killed, it could restart depending on the OM

settings  Providing well-tuned PDQs could address certain of the above challenges

51

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel Workload Tagging

52

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Business Benefits  Siebel Workload Tagging adds significant monitoring capability to track

SQL statements being issued by any Siebel OM-based component  Provides significant visibility to DBAs and Siebel Administrators into causes of poor performance through: – Oracle Enterprise Manager – Best way – V$SESSION and certain other V$ DB Views (using direct SQL queries against

Oracle DB) – Cumbersome and time-consuming

 User tracking for auditing purposes  User Name is maintained across all tiers even when using: – LDAP – Connection Pooling

53

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Workload Tagging for Production  When workload tagging is enabled, SQL statements generated by any

Object Manager-based component are tagged with workload tagging attributes.  The DML SQL statements options are: – SELECT – INSERT, UPDATE, DELETE – SELECT, INSERT, UPDATE, DELETE

 The values for these attributes are: ■ CLIENT_IDENTIFIER: Component Name, Server Name, Task ID, User ID, SARM ID ■ ACTION: Business Component Name [primary BC if more than 1] ■ MODULE: View Name NOTE: The length of the attribute values above is limited to 64 bytes for CLIENT_IDENTIFIER, 32 bytes for ACTION, and 48 bytes for MODULE. Truncation occurs when the length exceeds these limitations.

54

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Workload Tagging for Production  Available in Siebel 8.1.1.9 & 8.2.2.3 onward  Currently available for Oracle Only  Lightweight: – Our performance testing shows 2-3% of CPU overhead on the DB Server – The overhead on the Siebel Servers was found to be insignificant (< 1%)

 Can be set dynamically at run-time

55

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Enabling Workload Tagging  Description for OCI SQL

Tagging Levels  0 Workload tagging is disabled.  1 Workload tagging is disabled. This is the default setting.  2 Workload tagging is enabled only for SELECT statements.  3 Workload tagging is enabled only for INSERT, UPDATE, and DELETE statements.  4 Workload tagging is enabled for SELECT, INSERT, UPDATE, and DELETE statements.

56

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Workload Tagging for Production  Oracle Enterprise Manager (OEM) is The best way to use Siebel

Workload Tagging: – On the Home page Scroll down and click on "Top Activity“ – Check the "Top Session" and click on "Session ID" corresponding to Siebel

application running tasks – It opens "Activity" page for this "Session ID". – Click on "General" tab and OCI attributes and values will be displayed.

Remark: Alternatively, you can use SQL Plus or similar tools to query the Oracle database V$SESSION and certain other V$ DB views to look up the CLIENT_IDENTIFIER, ACTION, and MODULE attributes by using SQL_ID of the SQL statement

57

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Finding the Source of BAD SQL in Siebel

58

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Finding the Source of BAD SQL in Siebel

59

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Finding the Source of BAD SQL in Siebel

60

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel SQL Tagging vs Workload Tagging Siebel SQL Tagging is exclusively for development phase  Piggyback extra column in queries that includes Siebel OM context SELECT T6.CONFLICT_ID, T6.LAST_UPD, …, :CONTEXT

FROM … WHERE …

 Turned on & off via a Siebel parameter  Siebel OM Context Passed Component Name, Server Name, Task Id, User Id, SARM Id, Business Component Name, Applet, View Name

 Example: The following is written into Object Manager log file SCCObjMgr_enu,SDC78450svod,11534352, SADMIN, 0000022b4aaee054:0,Visible Contact List View

61

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Siebel SQL Tagging vs Workload Tagging Siebel SQL Tagging is exclusively for development phase  Exclusively for development phase – To troubleshoot customizations generating poor SQL statements – Could be combined with SQL log level to obtain the fetch time – Useless in production – Millions/Billions of log file entries  Significant overhead and log file size

 Advantages – Cross DB Platform – Available since Siebel 8.1.1.2

 Limitations – Bulks up every query result – Not always on – Not integrated into Oracle tools – Stored Outlines and SPM Plans will be affected by activating this features 62

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Summary of Oracle Database Best Practices  Deploy the Siebel Maximum Availability Architecture  Use Oracle Database Replay for Siebel Testing  Apply our Performance Tuning Best Practices  Use Siebel Workload Tagging

63

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Graphic Section Divider

64

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.