Parallel Query on Exadata

 Introduction  A Little History  Exadata  Oracle 11gR2  Practicalities  Summary 13/08/2012 Slide 2

 My real name is Douglas  But only my family call me that  My official intro is on the E4 website  There is even a picture of a Cuddly Toy  Please note that they are Cuddly not Plushy  I'm from Scotland which is part of Great Britain  Not Ireland  Definitely not English  Speaking of where I'm from … 13/08/2012 Slide 3

 Is a liar  Is offering you a useful simplification  Doesn't have much faith in your knowledge of British geography 13/08/2012 Slide 4

 From https://twitter.com/TheTumshie/status/227488415061508097/photo/1 13/08/2012 Slide 5

 Because Tanel asked  I've worked with Exadata V2 and X2 in high-throughput Production environments for the past two years  2 x Half-Rack V2  Full-Rack V2  Lots of Quarter-Rack X2-2s on the way  I have a long-standing interest in Parallel Execution  Not that the two went together particularly well at this client  It saved you all the bitter 'early V2 experiences' diatribe 13/08/2012 Slide 8

 This presentation will include opinions  I will not regurgitate large chunks of the documentation, Oracle White Papers or Expert Oracle Exadata  Client use of Parallelism on Exadata has been more limited than I expected 13/08/2012 Slide 9

 First time I heard of Oracle Parallel Query was in 1993  Boss returned quite giddy from IOUG Conference  Was a while longer before it was released in 7.1.6 and didn't hear much about it until 1996  Dev DBA decided it would be a really cool thing to try out  V7.3 time-frame  Was switched off not long afterwards  I've worked on many Data Warehouses so have been interested in Parallelism, Stats Collection etc for a long time 13/08/2012 Slide 11

 Tuning Parallel Execution  http://oracledoug.com/px.pdf  Parallel Adaptive Multi-User debate with Tom Kyte SELECT name, value FROM v$sysstat WHERE name LIKE 'Parallel%'; NAME VALUE -------------------------------------------------- ------ Parallel operations not downgraded 546353 Parallel operations downgraded to serial 432 Parallel operations downgraded 75 to 99 pct 790 Parallel operations downgraded 50 to 75 pct 1454 Parallel operations downgraded 25 to 50 pct 7654 Parallel operations downgraded 1 to 25 pct 11873 ▪ Downgraded to serial could be a particular problem! ▪ PX is great at delivering blistering but inconsistent performance 13/08/2012 Slide 12

 How Many Slaves?  http://oracledoug.com/px_slaves.pdf  Parallel Execution and the Magic of Two  Inspired by Cary Millsap's work on the Magic of Two for batch- type processes  Sharply diminishing returns with higher DOPs  ISP4400 quad-socket server with 4 x SCSI Ultra 160  E10k with 5 disk stripe-set on EMC array  Had never seen many benefits of DOPs over 2-4, confirmed by feedback on the paper  PX is great at uncovering storage bottlenecks 13/08/2012 Slide 13

(Insert Mandatory slide here with a pretty picture of a full-rack X2-2 and lots of very impressive throughput numbers) 13/08/2012 Slide 15

 The most common real world Parallel Query limitation in my experience of numerous sites was storage bandwidth  Exadata can certainly reduce that!  Not necessarily better than other modern storage solutions  Dedicated to Oracle and controlled by a dedicated team  Smart Scan reduces bandwidth pressure by reducing traffic  Balanced Configuration  Despite Oracle and the market deciding it's a mixed- workload/consolidation platform, it excels as a DW one 13/08/2012 Slide 16

 Exadata implies Oracle 11gR2  Let's all try to forget 11.2.0.1 as soon as possible  Assume 11.2.0.2  Lots of new Parallel features  Auto DOP  Statement Queuing  In-memory Parallel Query 13/08/2012 Slide 19

 Let Oracle decide what is the most-appropriate Degree of Parallelism (DOP) for queries  Set parallel_degree_policy to  LIMITED – Calculates DOP where objects have PARALLEL set to DEFAULT  AUTO – Calculates DOP for all objects, potentially  Default parallel_min_time_threshold is 10 seconds  Anything estimated to run for less time will not be considered  Auto-DOP will not work until you have run CALIBRATE_IO 13/08/2012 Slide 20

 Procedure in DBMS_RESOURCE_MANAGER  Support Note 727062.1  Oracle Best Practice advice in Support Note 1297112.1 is to set MAX_PMBPS to 200 on Exadata  Manual Update of resource_io_calibrate$  Restart each Instance 13/08/2012 Slide 21

 Parallel_degree_limit  Absolute DOP limit for an individual query  Default is CPU  parallel_threads_per_cpu * cpu_count * active_instance_count  Can set it to a fixed value 13/08/2012 Slide 22

 The problems with Parallel Adaptive Multi-User (PAMU)  Aggressive  DOP determined at run-time  Alternative approach  If slaves are available, then ▪ Use as many resources as possible ▪ Complete and let others in  If slaves are unavailable then queue until they are  Degraded DOP not decided at run-time 13/08/2012 Slide 23

 Set parallel_degree_policy to  AUTO  But that includes Auto-DOP and In-Memory PQ  _parallel_statement_queuing = TRUE  Alternatively – enable/disable at query-level using hints  /*+ NO_STMT_QUEUING */  /*+ STMT_QUEUING */  Waits on 'resmgr:pq queued' event (Scheduler) 13/08/2012 Slide 24

 The flip-side of the fact we can now do Direct Path Reads for queries executed Serially  Can now use reads into the Buffer Cache for Parallel Queries  Uses aggregated Buffer Cache across RAC cluster  But also works single-instance 13/08/2012 Slide 25

 Exadata nodes are not that powerful  Exadata storage and Flash Cache are not necessarily state-of-the art  Lots of quarter-racks out there  Companies are cheap ;-)  Kind of reasonable to partition workloads.  Technically, separating heavy write and read workloads probably makes sense, but … 13/08/2012 Slide 28

Node 2 Node 1 Read/Reporting Workload Write workload App A Node 4 Node 3 Read/Reporting Workload Read/Reporting Workload App B App A plus Standby 13/08/2012 Slide 29

 Remember those users who hate inconsistent response times?  Parallel Statement Queuing  Some visibility in OEM Performance Pages  Plenty of information in V$ views but not very useful to users!  Support Note 1359043.1 contains queries to help monitor Statement Queuing 13/08/2012 Slide 30

 Well, we're not doing Direct Path Reads any more  So we're not using Smart Scan any more  Tricky to get working properly  Needs to be pay-back  If you get a lot of pay-back, is Exadata really the way to go? Aren't these two different kinds of system? 13/08/2012 Slide 32

 Exadata is very likely to be better than previous solution  The bigger the rack, the better  Cross-instance Parallelism for truly impressive results  One method of driving towards Direct Path Reads to take advantage of Smart Scan 13/08/2012 Slide 34

 Auto-DOP  Statement Queuing  Balance cache benefits of In-Memory PQ against initial read overhead 13/08/2012 Slide 35

 "Oracle Database Parallel Execution Fundamentals"  Oracle Corp. White Paper – 11gR2 focus  Oracle Support Notes  1297112.1 – Best Practices for DW on X2-2  727062.1 – CALIBRATE_IO  1264548.1 – New 11gR2 Parallel Query Parameters  1269321.1 – Auto-DOP  1340180.1 – Recommended patches for PX Statement Queuing  1359043.1 – Configure Statement Queuing for mixed workloads  Chapter 6 of Expert Oracle Exadata 13/08/2012 Slide 36

 To various people at Oracle who keep working on improving this stuff  To Enkitec for taking such good care of the speakers  To you, for your time  P.S. If you really want me to explain what's going on with the toys ... 13/08/2012 Slide 37

Mail: dougburns@yahoo.com Twitter: @orcldoug Web: http://oracledoug.com

Parallel Query on Exadata

More Related Content

What's hot

Similar to Parallel Query on Exadata

More from Enkitec

Recently uploaded

Parallel Query on Exadata