Transcript Presentation Title - IOUG - Independent Oracle Users Group

Exadata in EBS
Techniques for POV to Production and Beyond!
IOUG Annual Exadata Virtual Summit
Ananth Ram & Jigar Shah
07-Feb-2013
Ananth Ram
Senior Manager
Exadata & Engineered Systems
Accenture
[email protected]
http://www.linkedin.com/in/rananth
Jigar Shah
EBS - Exadata
Independent Consultant
[email protected]
http://www.linkedin.com/in/jigarshah5131
EBS Exadata Exalogic
High performance. Delivered.
Independent Consultant
2001 Market Street
Philadelphia, PA 19103
Email: [email protected]
[email protected]
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
1
• 200 modules , 25+ million lines of code.
• More than just OLTP
•
•
Mixed workload application.
Separate reporting or data warehouse database.
• Most systems averages 80% reads and 20% Write.
• Most of the Oracle databases running EBS
are big endian character set
• Multiple copies (5-10) of production are
maintained for support
• Large percentage of time is spent on
cloning and patching
2
EBS Workload Profile
3
Smart Scan
Large
Flash Memory
Storage
Indexes
EHCC
IORM
DBFS
Main EBS screens 2X-10X faster
Key batch programs 15X faster
Overall batch 6X-8X faster
Reports 20X times faster
Materialized view refresh 30X faster
ETL 8X faster
4
• Business requirements should drive EBS on Exadata.
•
•
•
•
Identify pain-points in modules and OLTP screens.
Identify pain-points in batch and month-end processes.
Quantify what 2X-10X performance means to the business.
Quantify what is near real-time reporting and DW means to the
business.
• Work with business on strategic opportunities due to 2X-10X speed.
5
•
•
•
•
6
Predictable Performance.
Isolated hardware for EBS applications.
Consolidation of multiple databases.
Support simplified.
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
7
•POV – On-premise
• Proof-of-value on-premise gives you the most flexibility.
• Maximum three months for testing.
•POV – At Oracle center
• Requires clearance from your company to ship EBS data to Oracle.
• Big endian to small endian conversion done by Oracle.
• Platform migration for application done by Oracle.
• Quick to test.
8
Install
Optimize
Exadata
To validate
expected performance
gains of
EBS in Exadata.Adapt key EBS
& Test
AS-IS procedures on
& Exadata.
Test
Programs
To Validate
Operations
•Install & setup Exadata
machine.
• Assess AS-IS results
and identify Exadata
optimization.
• Assess and identify top
3-10 EBS programs/oltp
for Adaptation.
• Validate Backup
& Recovery
•Convert EBS
Database to little endian
format.
• Pin objects in Flash
cache, Tune DB.
• Adapt identified
programs/oltp for
Exadata.
• Validate New backup
and recovery methods
• Run EBS Programs
and key OLTP. Measure
time.
• Run EBS Programs
and measure time.
• Run Modified Programs
and measure time.
• Setup and test the 12c
Cloud control and
monitoring.
TimeLine: Total of 10-12 Weeks including the machine configuration time.
9
Operations
Test
•
•
•
•
•
•
•
10
Run AS-IS. Expect to get 2X to 10X performance.
Identify two or three pain points as a sample.
Identify the percentage of smart scan usage.(5-20% for EBS)
Optimize using flash cache pinning.
Adapt few key programs to use smart scan.
Partition archived data using EHCC.
Expect 10X-50X performance improvements.
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
11
• Size production Exadata and non-production Exadata separately.
• Start from the database size and disk IOPS.
• Use Load average, cpu usage and memory usage by Oracle
processes.
• Use AWR Load profile or dba_hist_% tables for peak disk IOPS.
• Use AWR for DB CPU and OS CPU usage.
• EBS gets 50-70% flash cache hit due to large Flash.
•
12
Use a conservative number in the hardware calculation.
From AWR Load profile
• Legacy EBS Load profile :
•
•
•
•
EBS peak Physical IO ( read and write
together) = 4000 Oracle IOPS (8ms latency)
EBS peak Logical IO 487,750 LIO / second
on node 1. Similar profile for Node 2.
Number of logged in users 800 with Average
active session 30.
Database Size 2TB and expected to grow to
5TB in three years.
65539/(60.52*60) = 18.1 CPUs
13
Peak DISK IOPS
• What do we need – Full Rack, Half Rack or Quarter Rack ?
• Oracle provides a sizing sheet and suggest the size of Rack. Use the
below method to understand the additional buffer build in the sizing.
• Disks
• 4000 IOPS/node for 2 nodes = 8000 IOPS.
• Assuming 50% flash cache hit and 100 operations /disk
• 8000* 50%=4000 IOPS on disk = 4000/100 = 40 disks
• With 12 disks on each cell node, we need minimum 4 cells.**
• With 600GB High performance disks , total usable Half Rack is 22TB.
• So, in this case, we understand that we should start from Half Rack
14
• What do we need – Full Rack, Half Rack or Quarter Rack ?
•
•
•
15
•
•
CPU Calculation – Legacy based
• CPUs from AWR  18 CPUs from legacy.
• Adding 20% overhead = 22 CPUs/node
CPU Calculation – Exadata based
• Time to fetch a block = CPU Time/(Snap duration * 60)/(LIO * 1000000 (us)) *
• CPU average of 20 micro seconds per LIO,
• 600,000 LIO * 20 us/LIO = 12 seconds = 12 CPUs
• Adding 50% buffer
= 18 CPU/node.
Memory
• Logged in users * 20 MB per process + SGA + PGA + OS
• 400 * 20mb + 24GB + 10GB + 4 = 48GB /Node
* CPU time is not just LIO, it can be Block changes, cache fusion time, etc.,
* Block changes, cache fusion changes are not included here for simplicity.
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
16
Key EBS screens 2X-10X faster
Lookups 17X faster
Key batch programs 15X faster
Overall batch 6X-8X faster
Reports 20X times Faster
Materialized view refresh 30X faster
ETL 8X faster
17
•
•
•
•
18
EBS flash hit ratio is around 55-70%
Smart Scan rate is 10X-15X faster than normal scans in flash.
Flash is 20X faster than disks scans.
Top six programs average IO saving 95%.
• Flash Cache
•
•
•
•
Full Rack X3 has 22TB of Flash Cache.
IO Latency is less than half milli second.
IO is 4X to 20X faster than legacy SAN.
Use 10046 to identify hot objects for
critical programs and pin in flash cache
using CELL_FLASH_CACHE keep.
Trace
• 10046 trace
for Critical
programs
Parse
• Parse raw
trace files
Correlate
19
• Identify
hot
objects
• Using Smart Scans
•
•
•
•
•
Identify the repeated index scans with large number of executions.
If possible, modify the code to do bulk operations with smart scan.
Use “With” Clause to isolate FULL table, FULL INDEX scans and then join with
other tables.
Use Baselines with hints for smart scan access.
Use Profiles and Profile Injection to change explain plan.
• Storage Indexes
•
•
•
•
20
Unpredictable and may age out. Causes performance instability.
Provides limited visibility and control to the admins.
Instrumentation of the usage is only through v$mystat ,v$sesstat and v$sysstat.
Workaround was to run important SQLs often through scripts to prevent aging
out.
•
Complex queries get un-nested and transformed resulting in index scans
where smart scan can be faster.
• Enable 10053 trace and search on “Unparsed query” will give
transformed query.
•
•
•
21
Try without Query transformation hint and FULL for smart scans.
Using DBMS_XPLAN, get the outline data and use it to get the smart scan.
Example – UNNEST(SEL$1) in outline , NO_UNNEST(SEL$1) FULL(A1)
•
Use OPT_PARAM hint with
• _push_join_union_view = false ( for union)
• _push_join_union_view2 =false (for union all)
to stop Predicate Union Merging.
• Combine with FULL hint to use smart scans.
• Create a baseline with
Alter session set “_push_join_union_view”= false
Alter session set “_push_join_union_view”= false
•
Tanel Poder’s snapper.sql for Exadata specific information.
•
Scripts awrgrpt.sql and ashrpti.sql are useful to analyze Exadata.
21
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
21
Data pump
Transportable
Database
DataGuard
(Logical Standby)
22
XTTS &
RMAN Convert
DataGuard
(Physical Standby)
Migration
Methods
E-Business Rapid
Clone
Golden Gate/Oracle
Streams
CTAS/Parallel DML
•
Migration Methods – Little-Endian -> Little-Endian (Exadata)
•
Transportable Database (TDB)
• Query v$DB_TRANSPORTABLE_PLATFORM
for verification.
• Oracle Database Software version has to be
same.
• Only datafiles with undo data requires
conversion.**
•
•
•
Oracle EBS Rapid Clone
•Source Platform must be x86-64 and same OS
Data Guard Physical Standby
•Only supported on Linux and Solaris x86;
•Source and Target should be on Same OS
Oracle Data Guard Logical Standby, Golden Gate
•Not Supported for E-Business Suite *.
* Reference: MOS Note ID 1133355.1 , 734763.1 ,741818.1
** select distinct(file_name) from dba_data_files a,
dba_rollback_segs b where
a.tablespace_name=b.tablespace_name;
23
•
•
•
•
Big Endian -- Platforms are Solaris Sparc, HP-UX and AIX
Little Endian -- Platforms are Linux x-86, Solaris x-86
Exadata is Little Endian
Migration Methods -- Big-Endian -> Little-Endian (Exadata)
•
•
DataPump Most preferred method
• Longer downtime based on amount of data and Metadata
• Some tables in JTF and APPLSYS does not support Datapump. Use exp/imp instead
• Better performance and space management due to complete reorg during migration
Cross Platform Transportable Tablespace (XTTS)
• Requires Larger time investment, complex, additional testing for DB and application
• Need to use combination of Rman , TTS, NFS and DataPump or Exp/Imp
• Need huge staging space for Rman to convert datafiles
• Does not Transport Sys/System user objects or system tablespace and tablespaces
have to be self-contained
* Reference: MOS Note ID 1133355.1, 734763.1, 729309.1
24
DataPump
• MOS Note:741818.1 takes about 3 days of downtime.
• Majority of time spent on serial metadata transfer , so it is slow.
• Some Workflow and JTF related objects required exp/imp
• Export /Import of LOBs takes majority of time.
Migrating EBS to Exadata using MOS: 741818.1
Hours
0
10
20
30
40
50
60
70
Prepare EBS DB
EXPDP Source DB
XFER Dump to Exadata
IMPDP EBS DB to
Exadata
Validation and Compile
Post Migration
AutoConfig and Gather
Stats
Backup
25
80
• Combined migration approach reduces total time by 80%.
• DataPump
•
•
•
•
•
•
26
Build EBS DB ahead of time.
Freeze object and code changes.
Migrate metadata ahead of time. This is a serial operation.
Parallel DataPump for data migration during go-live.
Exadata throughput averages 3TB/hour.
Some workflow and JTF related objects require exp/imp.
CTAS/P-DML (insert /*+ APPEND */) over DB Link
•
•
•
•
•
•
27
Identify large tables and/or LOB
objects
Reduces overhead and IO by not
packing, copying and unpacking
Increasing parallel threads does not
speed up. Bottleneck is Query Coordinator
Create New Range partitions on
Exadata to speed up migration
Slice parallel workers across source
and target and not within
Reduced migration time for large LOB
objects from 36 hours to half-hour
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
28
• How ZFS is used in EBS ?
•
•
•
•
29
ZFS is used for filesystems in
EBS webtiers.
Used for Backup and
Recovery of database.
Used for Cloning.
Used for test DB cloning
with EHCC
• Setting up ZFS
•APPL_TOP,COMMON_TOP on ZFS and shared across all webtiers
•
•
Mounting COMMON_TOP on all Exadata compute nodes is required for CM.
Connect production compute nodes using IB and Development using 10Gb
• Backup using ZFS
•
•
•
EBS Backup rate about 8TB/Hour and restores 6TB/Hour on ZFS 7420 using 8
RMAN parallel Channels
RMAN low backup compression compresses backup 300-500GB for 2 TB
Turn on DNFS on all RDBMS instances on every node
•make –f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on
•/etc/oranfstab is required for x2-8 , Active/Active IPMP group is created
30
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS backup and recovery in Exadata
• EBS cloning in Exadata
• EBS real-time replication in Exadata
• Recap
31
EBS Cloning in Exadata is faster
Legacy EBS Rapid Clone
Hours
1
6
11
16
21
Pre-Clone Steps
BCV /SNAP Clone
Rapid Clone
Scrub Data and Post
Clone
Gather Stats
Backup
Exadata EBS Rapid Clone
Hours
0
Pre-Clone
Steps
2
4
6
8
10
12
14
RMAN
Duplicate
Rapid Clone
Scrub Data and
Post Clone
Gather Stats
Backup
32
16
18
26
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
33
•
Use of GoldenGate in EBS on Exadata
•
•
•
Near real-time replication to Data warehouse
Interfaces to external systems
NFS for Oracle is DBFS (Database File System) MOS 1054431.1
Exadata does not come pre-build with any file system
DBFS is distributed file system and NOT a clustered file system.
Provides Shared network file system across all Exadata compute nodes.
Staging of data for ELT , dumps for DataPump, software staging, tools, bulk data movement
• DBFS error tracing using dbfs_client (MOS : 1320683.1)
•
Golden Gate on Exadata MOS 1371489.1
•
•
•
•
34
DBFS directory is used to install software, staging extract trail files etc.
Accessible across all compute nodes if DBFS is created with RAC
Should be mounted without direct_IO options.
Highly available and can be used in Active-Passive mode
• Business case for EBS on Exadata
• Proof-of-value of EBS on Exadata
• Sizing Exadata for EBS
• Getting 10X performance
• Migration of EBS in Exadata
• EBS cloning in Exadata
• EBS backup and recovery in Exadata
• EBS real-time replication in Exadata
• Recap
35
• EBS performs well on Exadata due to the Mixed load.
• Expect 2X-10X performance increase.
• Better performance is achieved by adapting EBS modules to
Exadata features.
• Suggested migration method for EBS Big to Little Endian is
DataPump.
• Use ZFS as a backup, cloning and application filesystem.
• Separate Production Exadata with non-production.
• Backup speed of ZFS averages 8TB/hour.
• Clone EBS using RMAN duplicate & Rapid Clone
36
Ananth Ram
Senior Manager
Exadata & Engineered Systems
Accenture
[email protected]
http://www.linkedin.com/in/rananth
Jigar Shah
EBS - Exadata
Independent Consultant
[email protected]
http://www.linkedin.com/in/jigarshah5131