epic clarity running on exadata
DESCRIPTION
TRANSCRIPT
Industry specific cover image
|
Optimize the Performance of Your Epic Clarity Data Warehouse
Webcast 2/14/2013
| Epic |
© 2013 Oracle Corporation
Anita Salinas Healthcare Bus. Dev. Oracle
Patrick O’Connor Healthcare Sales Consultant Oracle
Bob Bryla Snr. DB Architect & Systems Engineer Epic
Tim Fox Chief Technologist Enkitec
2 © 2013 Oracle Corporation
• Introductions • Why optimize? • Exadata: extreme performance for OLTP and DW • Customer results: Enkitec
– Benchmark 1 results – Benchmark 2 results – Short demo
• Epic/Clarity target platforms explained: Epic • Summary and next steps • Q&A
Agenda
3 © 2013 Oracle Corporation
Exadata Delivers Higher Value To Epic Clarity Users Benefits Realized In Multiple Areas
Epic Clarity on
Oracle Exadata
Business Value • Improve quality of patient care • Receive timely critical reports • Execute reports more frequently as needed • Strategic partnership IT<->Business
IT Value • Higher operational excellence, raise IT bar • Improve service - enhance SLA metrics • Seamless DW w/OLTP environment • Higher performance, scalability, throughput • Standardized complete management tools
IT Cost Advantage • Reduce core IT costs • Significant cost benefits • Lowest industry TCO
What if you could get MORE information
SOONER and USE LESS
hardware to do it?
4 © 2013 Oracle Corporation
Business Users Will Realize Significant Benefits Oracle Customers Confirm Benefits
EpicCare Outpatient
Tapestry
OpTime Surgery
ADT Prelude
EpixRx Medication
EpicCare Inpatient
Resolute Hospital Billing
Profess. Billing
Epic Clarity Reports: 5-100x Performance Improvements
Sample Set of Reports • Organ donor list, heart and lung transplant reports • Specific inpatient diagnosis/flowsheet data related to transplants • Currently admitted inpatient data for specific counties • Medication, MAR, dispensed charge data • Orders and treatment plan data • Orders, results, diagnosis for ambulatory visits for specific depts • Outpatient appointment data for a specific county • OR logs excluding specific CPT codes including charge data • OR logs for specific CPT codes including charge data • ED data from the prior day based on trauma diagnosis • ED Order data • ED patient flowsheet data and events
5 © 2013 Oracle Corporation
Timely Transplant Reports • Improved patient care due to timely
information, data confidence • Enhanced productivity for all
coordinators, supporting personnel • Improved IT productivity, eliminating
unnecessary running of reports
Customers Confirm Higher Business Value Enhanced Patient Care With Confidence
Other Reports
Finance
Education
Research
IT Ops
Admin
Clinical
~200 Daily Reports, >300 Locations Will Benefit
• Improved patient care • Significant productivity boost to clinical,
research, administrative users • Improved operational effectiveness and
reduced cost to keep the lights on
Finance Provide financial reports to analysts sooner for regular reporting periods
New Research Reports • Meet new requirements due to
faster report execution
6 © 2012 Oracle Corporation
Oracle Exadata Extreme OLTP/DW Performance
7 © 2013 Oracle Corporation
Exadata Unified Workload Transformation
Single Machine for… • OLTP
• Data Warehousing • ETL • Query parallelism
OLTP with Analytics and Parallelism of Warehousing
Warehousing with Interactivity, Availability, and Security of OLTP
8 © 2013 Oracle Corporation
Exadata Innovations
• Intelligent Storage – Scale-out InfiniBand storage – Smart Scan query offload
+ + +
• Hybrid Columnar Compression – 10x compression for warehouses – 15x compression for archives
• Smart PCI Flash Cache – Accelerates random I/O up to 30x – Triples data scan rate
Data remains
compressed for scans
and in Flash
Benefits Cascade to Copies
compress
primary DB
standby test dev backup
uncompressed
9 © 2013 Oracle Corporation
• Significantly reduce query times by orders of magnitude
• Use fewer indexes to significantly improve daily load times
– Less space utilization
– Reduced maintenance of index builds/rebuilds
• Lower costs by consolidating all workloads on one platform
– Use Exadata for simultaneous Warehouse and OLTP
• Accelerate response times by up to 100x (or better)
Oracle Exadata: Extreme Performance and Scale Advantages
10 © 2013 Oracle Corporation
Compression Ratio of Real-World Data
0 10 20 30
Telecom HTelecom TTelecom A
Financial HFinancial UFinancial BFinancial P
Healthcare BHealthcare C
Query Compression Ratio(Average= 13x) • Compression ratio varies by
customer and table
• Trials were run on largest table at 10 ultra large companies • Average revenue > $60 BB
• 13x – Avg query compression ratio
• On top of Oracle’s already highly efficient format
11 © 2013 Oracle Corporation
Secure Database Machine
• Moves decryption from software to hardware • Over 5x faster
• Near zero overhead for fully encrypted database
• Queries decrypt data at hundreds of Gigabytes/second
12 © 2012 Oracle Corporation
Epic Clarity on Exadata Benchmark 1 Details
13 © 2013 Oracle Corporation
• Data model has many very wide tables but rarely are all columns in a single report
• Data model loaded on daily / usually requires significant DB server resources
• Thousands of reports are run against Clarity on a daily basis
• Up to 120 reports may execute concurrently
• Clarity customers look for database configurations which improve throughput. Often, the result is non-default Oracle configurations
• Customer-written report queries are often more complex than Epic-released reports, and are challenging to tune with traditional methods
Observations - Epic Clarity on Exadata
14 © 2013 Oracle Corporation
• 1.5T Clarity database imported to Exadata X2-2 Quarter Rack (excluding audit tables) • One BizObj server (VM) used to generate reporting load for 40 concurrent report jobs
• Evaluated automated reporting batches for execution time, load characteristics • Customer supplied specific, long-running queries tested individually on Exadata • Where applicable, Exadata features induced to explore performance • Exadata’s Hybrid Columnar Compression (HCC) not used to compress tables during
the POC, but compression tests were run on large tables • Tests on CLARITY_TDL_TRAN table show the following results
• Query High HCC Compression ratio – 8x to 10x • Can reduce a 30GB table to 3GB
• Query Performance of HCC Compressed data often execute faster
Epic Clarity on Exadata POC - Approach
15 © 2013 Oracle Corporation
• Customer supplied queries were executed under the following conditions: • Database configured per Customer (matches current production)
• Reduced buffer cache to 2GB / multi-block read count = 128 / all non-PK indexes made invisible
• Configuration changes were made to show that Exadata performs better, for most DW workloads, with a smaller memory footprint
• The following page displays the results of the individual query testing done for a Clarity customer on Enkitec’s Exadata X2-2 quarter rack
Query Execution
16 © 2013 Oracle Corporation
Results – Query Execution Average Performance Improvement – 91x
17 © 2012 Oracle Corporation
Epic Clarity on Exadata Benchmark 2 Details
18 © 2013 Oracle Corporation
• Customer provided 2T production Clarity database export, 20 specific queries • Supplied queries were run unmodified under three configurations:
*8 GB SGA (equivalent to current production) *15 GB SGA *40 GB SGA
• PARALLEL_MAX_SERVERS =24 • Used standard formula maximum parallel Servers = 2 * Core Count
• Each query executed 2x to ensure at least some relevant data in buffer cache • Hybrid Columnar Compression (HCC) was not used • No tables were pinned in Exadata Smart Flash Cache • The entire POC was run on a single node Exadata Quarter Rack • Parallel slaves were confined to one node of the RAC • All serial processes were run on a single node of the RAC
Epic Clarity on Exadata POC – Approach
19 © 2013 Oracle Corporation
Results – Query Execution
Currnent System 8G SGA
Exadata 8G SGA
Exadata 15G SGA
Exadata 40G SGA
Parallel Degree Exadata Improvement Factor (based on 8G SGA)
Query 1 46:13.00 00:00.02 00:00.02 00:00.02 24 138,650 Query 2 58:55.00 00:00.05 00:01.66 00:01.94 24 70,700 Query 3 32:24.00 11:47.44 10:29.40 08:20.10 24 3 Query 4 06:57.00 00:15.81 00:15.45 00:15.80 24 26 Query 5 8:45:12.00 13:17.68 10:36.32 11:05.40 24 40 Query 6 14:04.00 00:25.14 00:11.60 00:11.83 24 34 Query 7 04:47.00 00:16.46 00:16.80 00:18.97 24 17 Query 8 08:33.00 00:36.71 00:35.31 00:35.22 12 14 Query 9 6:38:10.00 02:50.14 02:49.07 02:48.65 Serial 140 Query 10 19:59.00 10:43.30 06:48.19 03:33.01 12 2
Improvement factors are based on the current system compared to Exadata with an 8G SGA
hr:min:sec:10th sec
20 © 2013 Oracle Corporation
Results – Query Execution Continued
Currnent System 8G SGA
Exadata 8G SGA
Exadata 15G SGA
Exadata 40G SGA
Parallel Degree Exadata Improvement Factor (based on 8G SGA)
Query 11 28:07 00:13.18 00:13.66 00:14.24 24 128 Query 12 40:07 01:58.41 01:52.75 01:55.74 24 20 Query 13 36:08 00:12.15 00:13.82 00:11.96 24 178 Query 14 1:10:27 09:29.83 03:25.33 00:13.52 Serial 7 Query 15 04:45 00:13.68 00:13.80 00:13.37 24 21 Query 16 02:57 01:33.33 00:00.46 00:02.14 24 2 Query 17 1:27:26 08:05.57 00:13.49 00:13.32 Serial 11 Query 18 42:32 02:24.66 01:21.20 00:58.96 24 18 Query 19 18:23 00:05.03 00:14.04 00:13.76 24 219 Query 20 3:13:31 00:18.39 00:15.75 00:16.67 24 631
21 © 2013 Oracle Corporation
To test Hybrid Columnar Compression on Clarity data, the Compression Advisor (DBMS_COMPRESSION) was used to simulate compression of the CLARITY_TDL_TRAN table
Results – HCC Compression Test
HCC Compression Level Compression Ratio
Query Low 3 to 1
Query High 6 to 1
Archive Low 8 to 1
Archive High 10 to 1
22 © 2013 Oracle Corporation
Demo and Conclusions
23 © 2013 Oracle Corporation
• Epic Clarity workload hits the sweet spot for Exadata – Large data volume, long running queries
• It is impossible to match Exadata’s IO capability for large table scans with any other Oracle-capable platform
• Additional benefits are available – Hybrid Columnar Compression, Exadata Flash, and Parallelism
• With minimal effort, Customer can identify the business benefit of extreme performance gains shown during this POC
• Exadata supports improved performance with smaller memory – More databases can be run on same hardware vs. custom built systems
Conclusions
1
2
3
4
5
24 © 2013 Oracle Corporation
• Target platform definition
• Supported platforms
• Customer demand
• Industry trends
• Exadata in-house at Epic
Epic Clarity Target Platforms Epic
25 © 2012 Oracle Corporation
Summary and Next Steps
26 © 2013 Oracle Corporation
Summary What can YOU do generating MORE reports FASTER on LESS hardware?
• Extreme Epic Clarity performance on Exadata – Up to 100x faster
• Do more (reports) with less (hardware) in less (time) – 512 reports in 12 hours vs. 1604 reports in 4 hours – 3x # of reports completed in ¼ the time
– Lower costs, consolidate workloads on same hardware
• Improve care quality – More timely = better intelligence
– Actionable data at your fingertips sooner and/or more often
27 © 2013 Oracle Corporation
Next Steps
Join us at HIMSS13! • Oracle and Enkitec Breakfast Briefing
Wed, March 6, 2013 7:30-9:00am Register here
• Continue the Conversation Reception Wed, March 6, 2013 4.30-7.30pm Invite forthcoming
Investigate further – Exadata website – Schedule a private consultation
Consultation • Assess performance of Epic Clarity DW
• Review reports and queries to identify opportunities that improve reporting
• Compare system to benchmark results
• Written performance recommendations Contact [email protected]
28 © 2012 Oracle Corporation
Q & A
29 © 2013 Oracle Corporation
For More Information
• Visit: Oracle Healthcare Website • Read: Oracle Healthcare Solutions • Join: Oracle Healthcare on Facebook • Follow: Oracle Healthcare on Twitter • Call: Oracle Healthcare Representative
30 © 2013 Oracle Corporation
31 © 2013 Oracle Corporation
Appendix – Query Execution
Current Customer System
Exadata per Customer
16GB Buffer
Exadata per Enkitec
4GB Buffer
Exadata per Enkitec
2GB Buffer
Exadata No Indexes
2GB Buffer
8488_sec_aun8fmpug9jk4 03:46:17.33 00:55:41.50 00:50:10.97 01:08:15.80 00:22:08.46 8207_sec_1vauja2xan534 06:14:16.34 01:23:19.67 01:06:36.11 01:19:04.93 00:52:50.74 6881_sec_232b9Czqbnn9 06:06:15.45 01:22:36.91 01:05:02.66 01:15:24.70 00:52:53.65 6833_sec_18mgrhn25hvk8 02:53:32.03 00:49:11.32 00:30:56.22 00:35:38.72 00:18:15.83 6827_sec_facj6p8f68drf 01:40:23.40 00:28:55.56 00:25:10.01 00:26:51.30 00:11:50.95 6820_sec_azgu4cxwvub3n 00:27:34.90 00:10:30.08 00:01:52.60 00:02:05.00 00:00:38.90 5890_sec_57rgm8v0jzpc1 00:31:11.20 00:04:25.41 00:13:44.35 00:12:29.06 00:03:00.75 5695_sec_5a02q7wg0k05x 00:50:19.90 00:25:42.47 00:23:57.29 00:23:35.28 00:00:46.47 5546_sec_at3uwh0bmvygv 00:49:20.10 00:06:56.32 00:07:38.94 00:07:03.25 00:07:13.97
All queries improved in performance on Exadata with no tuning. No parallelism was used. All queries were run on one node of the two node RAC.
32 © 2013 Oracle Corporation
Appendix – Query Execution
Current Customer System
Exadata per Customer
16GB Buffer
Exadata per Enkitec
4GB Buffer
Exadata per Enkitec
2GB Buffer
Exadata No Indexes
5282_sec_13w3x29huvpzs 00:38:40.30 00:04:55.45 00:05:05.03 00:05:05.76 00:12:46.55 4742_sec_g6hmtqdhggcs7 00:31:12.80 00:04:55.45 00:00:01.28 00:00:00.95 00:00:02.20 4736_sec_1jkjps3basyz7 00:16:34.40 Killed Killed Killed 00:17:52.99 4728_sec_9fy866srqj1hz 00:28:07.20 00:33:24.63 00:23:31.59 00:24:51.67 00:09:54.17 4716_sec_1wuj2pzmdf0wk 00:47:45.80 00:11:31.62 00:06:15.23 00:04:59.86 00:10:29.17 4120_sec_3vu8b5sfmr8r6 14:35:44.65 TEMP TEMP TEMP TEMP 3534_sec_fv2hr8d15q4tr 00:09:22.60 00:11:08.64 00:09:12.01 00:09:36.35 00:00:33.87 3383_sec_dvztmf02uqcya 00:12:54.30 00:00:09 00:00:11.52 00:00:04.15 00:00:04.57 3184_sec_gg5jrs56h19t2 00:36:13.60 00:00:00.52 00:00:03.63 00:00:03.52 00:00:07.08 3182_sec_gazv5xbhh0w5s 00:08:08.50 00:00:52.88 00:02:38.70 00:02:18.52 00:01:33.66
All queries improved in performance on Exadata with no tuning with the exception of two queries, both of which experienced plan digression due to database version change.
33 © 2013 Oracle Corporation
Appendix – Additional Tuning
• Query 4736 ran for 16 minutes at Customer. Due to execution plan changes from 10g to 11g, the query never finished on Exadata.
• After removing all non-PK indexes, Query 4736 finished in 17 minutes on Exadata (1 minute longer than on Customer production).
• The largest table in the query was still using a PK index. After removing this index (via hint) the query ran in 3 minutes 42 seconds on Exadata (5x faster).