Optimizing Table Scans in Today’s Cloud Environments

2020-02-27 53浏览

1.
2.Optimizing Table Scans in Today’s Cloud Environments Andrew Holdsworth Vice President Real-World Performance Server Technologies October, 2017 John Clarke Software Development Director Real-World Performance Server Technologies October, 2017 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted
3.Safe Harbor Statement 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. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 3
5.Real-World Performance 2017 Who We Are • Part of the Database Development Organization • Global Team located in USA, Europe, Asia • 350+ combined years of Oracle database experience • Innovate to achieve exceptional Database Performance • Ourmethods:• Use the product as it was designed to be used • Numerical and logical debugging techniques • Educate others about the best performance methods and techniques • Avoid and eliminate “tuning” by hacking/guessing/luck Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
6.We’ve Been Here Before • How many rows do you need to find? a) One b) A few c) A lot d) I don’t know • Do you scan or use an index? • If you don’t know, what access method is the least risky? Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 6
7.The Question We’re Asking SELECT d_year, d_sellingseason, c_region, SUM(lo_extendedprice),SUM(lo_supplycost) FROM lineorder JOIN customer ON lo_custkey = c_custkey JOIN date_dim ON lo_orderdate = d_datekey JOIN part ON lo_partkey = p_partkey JOIN supplier ON lo_suppkey = s_suppkey WHERE d_month IN (’June’,’July’,’August’) AND p_mfgr IN (’MFGR#1’,’MFGR#2’) AND s_nation = ’China’ GROUP BY d_year, d_sellingseason, c_region ORDER BY d_year, d_sellingseason, c_region “Show me the price and cost by year, selling season, and customer region for all goods sold in June, July, and August for parts manufactured by MFGR#1 and MFGR#2 in China” Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 7
8.Where We’re Asking the Question Oracle Cloud Infrastructure • Oracle Database Cloud Service – Bare Metal • Oracle Exadata Cloud Service - Bare Metal Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 8
9.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do to Speed Up Next Operation in Plan Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 9
10.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do to Speed Up Next Operation in Plan Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 10
11.Why Table Scans? Some History and Math • Our query joins 4 dimension tables to a 1-billion row fact table • Would you expect this query to be I/O bound? • If we useindexes:– First join to fact table retrieves 42 million rows, or 4.2% – After completing additional joins, we end up doing ~ 320 million random reads • 320m random reads @5ms/read =~ 1600 seconds – Is 5ms for a random I/O an “old tech number”? – If the “new tech number number” is 1ms, we’re looking at ~5 minutes for I/O Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 11
12.Why Table Scans? Index Access • We spend 43 seconds on I/O, not 26 minutes or 5 minutes • Our average random read is taking a fraction of a millisecond • We’re CPU-bound, not I/O bound Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 12
13.Why Table Scans? Full Table Scans • What about a full table scan? • We have about 14 million blocks • The “old tech number” for multi-block reads is about 6 or 7ms per MBR • A multi-block read count of 128 = 109k multi-block reads • 109k multi-block reads at 6.5ms per MBR means we’d spend under a second doing I/O • Let’s see … Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 13
14.Why Table Scans? Full Table Scans • We 375ms doing I/O with an average I/O size of 25MB • “Effective” MBRC much higher than 128 • We’re still CPU-bound Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 14
15.Why Table Scans? The New Math & What We’ve Learned • In Oracle’s Cloud, random and sequential reads are much faster than the old numbers people think about • For both index and table scan access, the queries are CPU-bound, not I/O bound • In this case, scans were 6x faster. Was this because of scans, joins, aggregation, or something else? • Time to dig a bit deeper! Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 15
16.Why Table Scans? Leverage Matrix Method % of Time in Data Acquisition Scans with Hash 86% Joins Index access and NL 99% Join % of Time in Joins 12.24% .08% % of Time in Sort/Aggregate 1.60% .17% Elapsed seconds 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 16
18.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do to Speed Up Next Operation in Plan Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 18
19.Making Scans Smaller Partitioning and Compression • Partitioning is a means to prune data and reduce I/O & CPU • Compression is a means to reduce size of data on disk and reduce I/O • Oracle Cloud Infrastructure supports Hybrid Columnar Compression • Let’s try it out Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 19
20.Making Scans Smaller Partitioning Still CPU-bound, but uses a lot less CPU than scans without partitioning Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 20
21.Making Scans Smaller Partitioning Table Scans without partitioning Table Scans with partitioning We partitioned on date join key and our predicates filtered ¾ of the data Although CPU-bound, I/O bytes is a proxy for CPU consumption Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 21
22.Making Scans Smaller Partitioning Warnings • A common problem we see is over-partitioning – Proxy for indexes? – Used to avoid contention? • Too many partitions can cause manyproblems:– Excessive time during parse & execute – High metadata cost – DDL more expensive due to data dictionary overheads – Exacerbated with RAC – Problems could reveal themselves in non-obvious ways Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 22
23.Making Scans Smaller HCC Compression with Scans and Partitioning, Oracle Database Cloud Service Query is CPU-bound again but only ran marginally faster Without HCC Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 23
24.Making Scans Smaller HCC Compression with Scans and Partitioning, Oracle Database Cloud Service We scanned 3.5x less data but only improved query performance by 35% Without HCC Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 24
25.Making Scans Smaller HCC Compression with Scans and Partitioning, Oracle Database Cloud Service • We weren’t I/O bound to begin with, we were CPU-bound • CPU & time to parse HCC blocks less than time to parse uncompressed blocks, but … • We need CPU to decompress compressed data • Querying smaller datasets doesn’t yield linear performance gains • Conventional mindset vs. modern capabilities Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 25
26.Making Scans Smaller Leverage Matrix Method % of Time in % of Time in % of Time in Data Acquisition Joins Sort/Aggregate Partitioning with Compression (OCI DBCS) 81% 14.5% 5.8% Partitioning on (OCI DBCS) 79% 17% 4% Scans with Hash Joins (OCI DBCS) Index access and NL Join (OCI DBCS) 86% 12.24% 1.60% 99% .08% .17% Elapsed Seconds 36 55 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 26
28.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do to Speed Up Next Operations` in Plan Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 28
29.Where’s Our Leverage? Some Profiling Data Index Scans Table Scans Most of our time is in parsing rows/blocks Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 29
30.Where’s Our Leverage? Profiling Data with HCC and Partitioning on Oracle Database Cloud Service Time spent parsing columns/rows in HCC format Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 30
31.Where’s Our Leverage? Block Parsing • Most of our time is being spent on data acquisition • Data acquisition is CPU-bound on Oracle DB Cloud Service • Profiling shows it’s largely related to parsing blocks • What if we could recruit more resources for block parsing, parse blocks in parallel, offload this work to different machines, and decrease wall clock time? • Do we have any technology that does this? Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 31
32.Where’s Our Leverage? Exadata Cloud Service • Query still CPU-bound but we see 7.5% (2 seconds) on I/O • Remember we saw no I/O on DBCS • I/O in this case means anything in the I/O path, including CPU on storage cells. 4.35% of our time is on “smart scan” Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 32
33.Where’s Our Leverage? Exadata Cloud Service to Offload Block Parsing • 4.35% of 13 seconds =~ .5 seconds, multiply by 2 slaves =~ 1 sec on cells • On Exadata we use up to 10 parallel requests per slave – 2 slaves = up 20 parallel requests per cell – 7 cells =~ 140 parallel requests in total, which is 70x more than DWCS • In BMC we spend =~ 70 CPU seconds on scan • Offloading allows us to parse blocks in parallel, reduce elapsed time, and reduce compute node CPU time • Bonus question – if we do 10 requests per slave with 1MB I/O size, what is should our minimum partition size be? Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 33
34.Where’s Our Leverage? Exadata Cloud Service to Offload Block Parsing Did you notice we’re still spending 24.5 CPU seconds not doing the scans? Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 34
35.Where’s Our Leverage? Exadata Cloud Service to Offload Block Parsing Compute node Profiling Time on compute node no longer dominated by parsing blocks Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 35
36.Where’s Our Leverage? Leverage Matrix Method % of Time in % of Time in % of Time in Data Acquisition Joins Sort/Aggregate Partitioning with Compression (OCI ExaCS) 48% 51% 1% Partitioning with Compression (OCI DBCS) 81% 14.5% 5.8% Partitioning on BMC (OCI DBCS) 79% 17% 4% Scans with Hash Joins (OCI DBCS) Index access and NL Join (OCI DBCS) 86% 12.24% 1.60% 99% .08% .17% Elapsed Seconds 13 36 55 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 36
37.Where’s Our Leverage? More You Can Do on Exadata • Exadata provides some other interesting alternatives to explore • Zone Maps with Attribute Clustering provide and additional means to prune I/O and reduce CPU • In addition to partitioning on our date dimension’s join key, let’s implement Attribute Clustering with a Zone Map on our Supplier dimension’s join key Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 37
38.Where’s Our Leverage? Exadata Cloud Service with Clustering and Zone Maps • Elapsed time reduced from 13 seconds to 5 seconds • Compute node CPU reduced from 24.5 to 6.7 seconds • Like partitioning, Zone Maps with Clustering means fewer calls to Exadata, with each call being more “row-rich” Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 38
39.Where’s Our Leverage? Leverage Matrix Method % of Time in Data % of Time in % of Time in Acquisition Joins Sort/Aggregate Partitioning with HCC & Zone Maps (OCI 57% ExaCS) Partitioning with HCC (OCI ExaCS) 48% 29% 14% 51% 1% Partitioning with Compression (OCI DBCS) Partitioning (OCI DBCS) Scans with Hash Joins (OCI DBCS) Index access and NL Join (OCI DBCS) 81% 14.5% 5.8% 79% 17% 4% 86% 12.24% 1.60% 99% .08% .17% Elapsed Seconds 5 13 36 55 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 39
41.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do to Speed Up Next Operation in Plan Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 41
42.Rearranging the Data In-Memory Columnar • We’ve demonstrated that parsing blocks consumes CPU and contributes to query elapsed time during scans • Offloading to Exadata provides us more CPUs to parse blocks • How would In-Memory Columnar representation impact our results? • Let’s test with Database In-Memory Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 42
43.Rearranging the Data In-Memory Columnar Scan with Partitioning and In-Memory (DB Cloud Service) Scan with Partitioning (DB Cloud Service, row format) Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 43
44.Rearranging the Data In-Memory Columnar Scan with Partitioning and DBIM Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 44
45.Rearranging the Data In-Memory Columnar Leverage Matrix Method % of Time in Data % of Time in % of Time in Acquisition Joins Sort/Aggregate DBIM with Partitioning (OCI DBCS) Partitioning with HCC & Zone Maps (OCI ExaCS) Partitioning with HCC (OCI ExaCS) Partitioning with Compression (OCI DBCS) Partitioning (OCI DBCS) Scans with Hash Joins (OCI DBCS) Index access and NL Join (OCI DBCS) 18% 57% 48% 81% 79% 86% 99% 64% 29% 51% 14.5% 17% 12.24% .08% 18% 14% 1% 5.8% 4% 1.60% .17% Elapsed Seconds 13 5 13 36 55 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 45
47.Agenda 1 Why Table Scans? 2 Making Scans Smaller 3 Where’s Our Leverage? 4 Rearranging the Data 5 Things We Can Do for Joins and Aggregation Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 47
48.Things We Can Do for Joins and Aggregation Bloom Filters • Bloom Filters provide means to efficiently filter data, reducing the volume of data for hash joins and distribution in subsequent plan steps • Bloom Filter evaluation can be pushed down to Exadata so we can leverage storage cell CPUs • Bloom Filter evaluation also pushed down to In-Memory column store and able to use different & more efficient algorithms • Let’s test Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 48
49.Things We Can Do for Joins ands Aggregation Bloom Filters Scan with Bloom Filters, Partitioning and DBIM Scan with Partitioning and DBIM Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 49
50.Things We Can Do for Joins and Aggregation Numbers So Far with Bloom Filters on DBCS and ExaCS Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 50
51.Things We Can Do for Joins and Aggregation Numbers So Far with Bloom Filters on DBCS and ExaCS Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 51
52.Things We Can Do for Joins and Aggregation In-Memory Aggregation • Push down aggregation to scan • In-Memory Aggregation performs aggregation during scan • Let’s enable it and test Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 52
55.Things We Can Do for Joins and Aggregation Leverage Chart Method % of Time in Data Acquisition % of Time in Joins % of Time in Sort/Aggregate IMC with IMA and Partitioning (OCI DBCS) IMC & Partitioning with BFs and Zone Maps (OCI ExaCS) Partitioning with BFs and Zone Maps (OCI ExaCS) Partitioning with BFs (OCI ExaCS) IMC with BFs and Partitioning (OCI DBCS) IMC with Partitioning (OCI DBCS) Partitioning with HCC & Zone Maps (OCI ExaCS) Partitioning with HCC (OCI ExaCS) Partitioning with Compression (OCI DBCS) Partitioning (OCI DBCS) Scans with Hash Joins ((OCI DBCS) Index access and NL Join (OCI DBCS) 100% 28% 33% 30% 30% 18% 57% 48% 81% 79% 86% 99% 0% 58% 50% 50% 40% 64% 29% 51% 14.5% 17% 12.24% .08% 0% 14% 17% 20% 10% 18% 14% 1% 5.8% 4% 1.60% .17% Elapsed Seconds 1 3 4 6 5 13 5 13 36 55 187 1,195 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 55
56.Bonus Sorting on Database Cloud Service, In-Memory, In-Memory Aggregation • We can’t use Zone Maps with Attribute Clustering on non-Exadata, but here’s something (relatively) free in the Cloud • Let’s manually sort the data to leverage In-Memory Min-Max pruning Look for ”IM scan CUs pruned” or “IM scan rows optimized” stats Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 56
57.Features Availability Feature Oracle Cloud Infrastructure Database Cloud Service Partitioning Hybrid Columnar Compression Zone Maps and Attribute Clustering In-Memory and In-Memory Aggregation Bloom Filters Oracle Cloud Infrastructure Exadata Cloud Service Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 57
58.Summary • These days, scan performance usually isn’t about reducing I/O, it’s about reducing CPU • There are a number of ways to do this • We reduced CPU from 1,176 seconds to 1.7 seconds for the same query • We reduced elapsed time from 1,195 seconds to 1 second for the same query • Look for leverage! • Don’t settle for “good enough” Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 58
60.When 11am 12pm 1pm 2pm RWP Sessions @ OOW17 Oct 4th Rm 3012 ID Topic CON6560 Optimizing Table Scans in Today's Cloud Platforms CON6561 Migrating On-Premises Applications to theCloud:Examining the Connection Strategy CON6629 Real-World Challenges with Cloud Migrations and Proof-ofConcept Projects CON6660 Applying Oracle Database 12c and Real-World Performance Techniques to SAP Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 60
61.Safe Harbor Statement The preceding 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. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Confidential – Oracle Internal/Restricted/Highly Restricted 61
63.