Extreme Performance with Very Big Data, Using Oracle Exadata X3-8

1. Extreme Performance with Very Big Data, Using Oracle Exadata X3-8

2. To understand over 400 million customers

4. Our scale as a group

5. Our vital statistics 26,000,000 Lines of code- at the heart of our data solutions 1,00,000,000rows of data processed every week 33data solutions containing 40 billionbaskets and 320 billionitems – We typically store 117 weeks history for each client 30,000 programs running automatically every week 50,000 files received and processed every week 11,000 client data transfers every week Global team of 130 people

6. ? Who am I Philip Moore • Exadata Data Warehouse Architect / Lead Oracle Developer — dunnhumby • 13+ years Oracle data warehousing • 3+ years with Oracle Exadata (v2, x2, x3) • Oracle Certified Professional • PL/SQL Developer • 10g DBA • Oracle SQL Certified Expert • Experience with: • SQL Server • Teradata • Netezza • Greenplum

7. ! We had a problem… Our data keeps growing (a good problem) Nearly ALL of it is “hot” (queried actively) Our analysts and products perform COUNT (DISTINCT x) in nearly every query — across billionsof rows! Traditional Kimball summary management techniques do NOT work with Distinct Counts Most queries touch a large percentage of fact rows — we don’t look for the needle, we look at, and analyze the hay in the haystack We require a global, multi-tenant solution Our legacy technology couldNOThandle our growing data volumes We needed a new, state-of-the-artdata warehouse platform!

8. Time for a Bake-Off! The dunnhumby Proof-of-Concept We Tested: • ETL Performance • Ad-hoc analyst queries • Canned product queries • Different levels of concurrency • Up-time statistics • Ease of administration • Much more… • Commissioned a project to find the very best datawarehousingplatform • Invited 5 different data warehousing hardware / software solution providers • Four “MPP” / “Shared-nothing” offerings — and Oracle Exadata • Spent over 3 weeks hands-on with each vendor — performing brutal stress tests.

9. ? Why Oracle Exadatawon… • It performed 8x better than the next-closest competitor for our workload! • Concurrency was key • Exadata was not the cheapest offering “per box” — but was most cost-efficient overall • The next-closest vendor needed 8x the floor space of one Exadata! • Oracle handled multi-tenancy quite well — due to instance caging

10. ? We chose Exadata – now what? We assembled teams of architects, DBA’s, and Application Developers to establish: We received one of the very first Oracle Exadata X3-8 full racks in October 2012. • Common Data Model — for all clients • ­ Logical (Kimball star-schema) • - Physical (Compression, Indexing, etc.) • Data architecture • Data replication/Disaster Recovery strategy (Active-Active) • Exadata configuration standards • Development Standards for SQL, PL/SQL, etc. • Standard ETL (really ELT) Components We had an aggressive go-live date for our largest U.S. client: Kroger — for March 1, 2013.

11. dunnhumbyCommon Data Model Logically —We use a traditional Kimball star schema Example client model: • Customers • 267+ Million rows • Products • 8+ Million rows • Terminals • 500+ rows Fact Table 90+ Billion rows • Dates • 4,700+ rows • Stores • 15,000+ rows

12. dunnhumbyCommon Data Model Physically —we use the following strategies For Dimensions Proper data types for our data • Oracle Advanced Compression (COMPRESS FOR OLTP) • Primary Keys are enforced — in RELY mode • Oracle DIMENSION objects • NUMBER for Numbers • DATE for Dates • VARCHAR2 for Characters “Soft” Referential Integrity for Foreign Key relationships For Facts • Exadata Hybrid Columnar Compression (HCC) — QUERY HIGH • Partition by RANGE on our date field, 1 week per partition NOT NULL and CHECK constraints to validate data and help CBO

13. Data replication / Disaster Recovery strategy (Active-Active) We use an Active-Active Disaster Recovery configuration –– using Oracle GoldenGate to replicate data from our Primary site to the D/R and Products site Primary Site D/R and Products Site Fact, Dimension data Product Configuration Data • Products –– The Shop • Pre-canned queries with SLA • ETL • Analyst ad-hoc queries (no-SLA)

14. Exadataconfiguration standards We chose a conservative (safe) configuration as follows: • High Capacity Disks (more space, but slower random I/O) • High Redundancy (triple mirroring) (allows for rolling patching, more uptime, and faster reads —but slower writes) • Isolation of Dev/Test Databases from Production databases by using another Exadata X3-8 machine for Dev/Test to elim-inate risk of Dev memory leaks or runaway SQLs impacting production (happier Oracle sales guy) It is important to configure your Exadata Database Machine for your workload, performance, up-time, and recovery requirements.

15. Exadataconfiguration - parameters It is important to follow Oracle Exadata Best Practices— to run a standard, Oracle recommended configuration. Except: Parallel_degree_policy We use “AUTO” DoP mode — capped at parallel degree 24 We set our Database initialization parameters to values recommended for warehouse workloads — as per “My Oracle Support” note: Oracle Sun Database Machine Setup/Configuration Best Practices (Doc ID 1274318.1) Avoid use of undocumented parameters

16. Multi-tenancy – configuration We run a multi-tenant environment — running databases for multiple clients’ data on the same Exadata X3-8 Database machine. We use instance caging — setting “CPU_COUNT” to ratios of the physical CPU core (and hyperthreading) count. We use a conservative core count for our ratios — we use 100 cores per compute node. If a client merits 60% of resources — we give them a CPU_COUNT of 60 on each node. X3-8 Compute node: 80 physical cores × 1.25 hyperthreading factor = 100 cores

17. Parallelism Parallel is key to performance in a large datawarehousing environment for haystack-type workloads. Traditional Oracle DSS solutions like star transformation don’t perform well with our workload, because they are designed to “find the needle” quickly. They also result in NESTED LOOP query plans that typically run serially. We use parallelism to “divide-and-conquer” the work of scanning, joining, sorting, and summarizing data in our queries. With 90+ billion rows in one fact table alone — we HAVE to use parallelism – one CPU can only handle so much data per second. 90,000,000,000 rows × 1 microsecond = over 1 day

18. Parallelism – Continued SQL Monitor is your new best friend. It clearly identifies the parallelism mode of each operation within a query execution plan — as well as the efficiency of parallel operations. It is accessible through Oracle Enterprise Manager in real-time. It is also available mid or post SQL execution via the DBMS_SQLTUNE.REPORT_SQL_MONITOR function.

19. Parallelism — tips Use Auto-DoP mode — yes this is “controversial” — but it usually does a good job — IF: You’ve gathered stats on your tables You’ve run the I/O Calibration Stats routine It is OK to have a mix of manual and Auto-DoP SQLs. Use UNIFORM extents with ASSM, locally-managed tablespaces Use large cache sizes on sequences — and be sure to use the NOORDER clause (Exadata is a RAC database!) When in doubt — eliminate the need for parallel execution servers to coordinate their work with each other and fight for shared resources.

20. Parallelism - configuration Remember — that CPU_COUNT will artificially limit your CPU consumption by parallel execution slaves. Waits for event: “cpu quantum” high in your top wait events list indicate that your PARALLEL_MAX_SERVERS count is too high. Example (from our friend: SQL Monitor): Use simple ratios to CPU_COUNT for your parallel parameters Use PARALLEL_THREADS_PER_CPU of: 1 to keep the math simple. On the X3-8 - we recommend: 16:1 for PARALLEL_MAX_SERVERS 8:1 for PARALLEL_SERVERS_TARGET (start queuing at 8:1) 3.2:1 for PARALLEL_MIN_SERVERS 24 for PARALLEL_DEGREE_LIMIT

21. Smart Scans — the Exadata secret sauce Combined with parallel execution — smart scans in Exadata can give you amazing performance. Cell Offloading offers: Encourage more smart scans by: • Storage Filtering • - Includes Bloom Filters — allowing you to find the fact hay matching dimension filters extremely fast — with no indexes! • Column Projection • Storage Indexes • Having a smaller SGA (buffer cache) • Reduce indexes usage • Make some Constraint enforcement B-Tree indexes INVISIBLE • Run DBMS_STATS.GATHER_SYSTEM_STATS with “EXADATA” gathering mode

22. Parallel Execution —with Storage Cell Offloading / Bloom FilteringPURE MAGIC Filtered AND joined 4.58 million fact rows per second! How do I know if I am getting a bloom filter on my fact join? Look at the “Plan” Tab – using tabular view:

23. Development Standards for SQL, PL/SQL, etc. To maximize performance and supportability — we implemented standards for development. In general we use the “Tom Kyte” methodology for moving data around. Use Bulk SQL operations wherever possible — this is where the Exadata shines Avoid doing things in our model and/or code which would prevent smart scans by the storage cells. Avoid ROWDEPENDENCIES! Only resort to PL/SQL loops (a.k.a. “slow-by-slow”) when SQL is not an option Reduce some pre-Exadata Oracle practices — such as breaking processing up into smaller chunks

24. Standard ETL (really ELT) Our requirement was to run a twin-track warehouse — keeping our legacy system alive — while simultaneously loading the data into Oracle Exadata. This allowed us to move adopters over from the legacy system more slowly. We did NOT force our analysts to use Exadata — but they quickly “self-migrated” due to 30-50x faster ad-hoc query performance! As such — we had to develop an ETL (ELT) solution that quickly extracted data from our legacy solution — and loaded it into our Exadata machines. 30-50x faster

25. ETL approach (ELT) We wrote scripts to extract data from our legacy solution into csv files which were written directly to our ZFS storage appliance. The ZFS storage is NFS (via 10gb/s Ethernet) mounted on our legacy blades — while it is simultaneously mounted (via Infiniband QDR) on the Exadata Database Machine. Once the CSV was extracted, we create external tables pointing to them on the ZFS. We then enable parallel DML, and use “INSERT /*+ append */” to load the data using direct path (and thus parallelism).

26. ETL approach (ELT) — continued To ensure optimal performance and parallelism of your table loads, ensure: Business transformations were done using VIEWS Leverages the power of Exadata for transformations In-Exadata transformations will almost always be orders of magnitude faster than any ETL server solution Wrote a simple, lightweight loading engine in PL/SQL which optimized the source views and target tables for parallel execution / smart scans Enable parallel DMwithin your session For manualDoP mode — ensure PARALLEL attribute is set on source/target tables and upon any indexes upon those tables There are NO enabled FK constraints pointing to or from the target table (PRIMARY KEY, UNIQUE, NOT NULL, and CHECK constraints are fine to be enabled during parallel loads) There are NO enabled triggers on the target table Disable indexes on the target table prior to loading, then rebuild them afterward

27. Virtual Private Database (VPD) We use VPD to have a single source of Customer data. We can hide Personally Identifiable Information (PII) columns from folks without the proper roles to see that data. This allows our analysts and products to query the tables for non-PII columns — and thereby having consistent results to those with PII privileges. We make use of Oracle Virtual Private Database (VPD) — also known as “Row/Column Level Security” (RLS).

28. We like Options (and our sales-guy likes us ) We purchased several Database options: Advanced Compression(COMPRESS FOR OLTP) — allows us to have excellent performance of DML on our dimension tables — and maintain comp-ression. Now - also includes “Total Recall” — allowing us to maintain history of tables. Advanced Security Option — allows us to use Transparent Tablespace Encryption — this secures our data at rest. Decryption is offloaded to the storage cells and is done via Xeon processor hardware instructions for maximum performance. Advanced Tuning and Diagnostics Packs — a must have for performance troubleshooting. Advanced Analytics — this is what we do! Standard Options necessary for Exadata are: Real Application Clusters (RAC) Exadata Storage Software

29. Potential future Options: We may purchase additional options to help us deliver an even better platform: Real Application Testing (RAT) — would allow us to record and replay production workloads in our Dev/Test cluster to see impacts of patches, upgrades, parameter changes, etc. Spatial — allows us to calculate distances between customers and their nearest store — from SQL! OLAP — allows multi-dimensional analysis of data from OLAP enabled tools and now transparently from SQL with Cube Materialized Views with Query Rewrite

30. Exadata and ZFS – better together We purchased ZFS Storage Appliances — and connected them via Infiniband to our Exadata Database Machines. This allows for extremely fast access to CSV files via EXTERNAL TABLES It also allows us to back up the database at over 15 Terabytes per hour —this allows them to finish at night — eliminating the impact that backups would have on our end users and ETL during the day. Be sure to enable block change tracking! — The fastest way to do things is STILL not to do them — even in Exadata… allowing us to load hundreds of millions of rows per minute.

31. Some more tips SQL Tuning is still VERY important — tune the question first — then the SQL. Write the simplest query that will answer the question. Avoid hints unless absolutely necessary — let the CBO do its job. Some hints discourage smart scans and use of new optimization features. Indexes are still necessary — don’t always rely on smart scan for large dimension tables. Storage indexes are no persistent after an outage — and you are limited to 8 per table — in other words — they are NOT predictable. B-Tree indexes are far more predictable. Still use indexes for PK/UNIQUE key enforcement — and always use them for OLTP workloads. The fastest way to do something is STILL not to do it — even in Exadata

32. What we are excited about for the future Oracle Database 12c — we are looking forward to : • Multitenant Architecture — Pluggable Databases • Adaptive Query Optimization • Online Statistics Gathering for Bulk Loads • Dynamic Statistics • Enhanced Parallel Statement Queuing • Enhancements to System Statistics • Out-of-Place Materialized View Refresh • Synchronous Materialized View Refresh • Session-Private Statistics for Global Temporary Tables • Automatic Data Optimization (ADO) — ILM • Improved Automatic Degree of Parallelism • Improve Histograms • Row pattern matching (MATCH_RECOGNIZE) • Partition Maintenance Operations on Multiple Partitions • Concurrent Execution of UNION and UNION ALL Branches • Data Redaction

33. What we are excited about for the future — continued Oracle Database 12c — we are looking forward to : • PL/SQL Enhancements • UDF Pragma • Security Enhancements • DEFAULT ON NULL columns • Invisible Columns • IDENTITY Columns – potential sequence replacements • DEFAULT Sequence.NEXTVAL for columns • Much More! Other future enhancements (post-12cR1): HyperLogLog – additive Distinct Counts – could allow a summary management strategy for Distinct Counts for the first time!

34. Thank You!