Friday, 20 February 2015

Parallel Processing: the Damien Hirst of SQL Tuning Art World

After spending a decade of my life among people whom I eventually came to regard generally as imposters (i.e. journalists), I learned something important about myself: I'm a purist committed to "principles" and I hate bullshitters!

But even if you ignore the pointless self-glorification of the first paragraph, you must have understood from the title that I'm not a fan of Damien Hirst!

Taking The "SQL Tuning Expert" certification exam (1Z0-117) recently reminded me that perhaps Damien Hirst is not the only entity which -in my opinion- is placed in a context that it doesn't belong to; or AT LEAST maybe we shouldn't regard people like him as such "integral" parts of the art world.

The following is a review of subjects covered in the exam, and what was overemphasised.

Here It Goes!

Disclaimer: If you've found your way to this post hoping that you might find some exam dumps or samples, you can press "Back" on your browser RIGHT NOW!

My only aim is to share what I remember (I will update if/when I remember more) from the test about what needs to be learned so that an applicant can pass the exam to achieve "SQL Tuning Expert" certification, mainly because not even a sample test was provided by Oracle.

I divide this post to three parts:

  1. Main Subjects
  2. References and Resources
  3. A Demand for Keeping Things In Perspective

1. Main Subjects 

You can find the exam topics here, but I did not find it representative of the main emphasis points of the exam; so here's what the actual main (not all of them, and not in the order of emphasis in the exam!) topics:
  1. Reading Execution Plans
  2. Query Transformation
  3. Partition Pruning
  4. Parallel Processing
  5. DBMS_MONITOR/trcsess/tkprof 
  6. SQLTuningSets, SQL Tuning Advisor, and SQL Access Advisor
  7. SQL Profiles and SQL Plan Management

1.1. Reading Execution Plans


The plans that you come across in the exam are not complicated (especially the stats related issues, such as cardinality mismatches and manifestations of stale stats are generally obvious), and you must have seen them all, if you have gone through the Oracle documentation mentioned below under "References and Resources."

  • You should be loving this stuff! If you don't like cracking the execution plans code, why on earth are you taking this exam anyway?!
  • You should be familiar with different interpretation methods of execution plans in terms of finding out the chronology of the events and processes involved.
  • More or less every SQL Tuning case that is mentioned in the official exam topic is referenced in the exam through an execution plan.


 1.2. Query Transformation


  • When do either of View Merging, Subquery Unnesting and Query Rewrite happen?
  • When either of those can't happen?
  • What are the signs of each taking place, in an execution plan?
  • When would you see a "Projection View" during View Merging?
  • How do you distinguish a Bitmap Join an Star Transformation?


1.3. Partition Pruning


  • What is the sign of Partition Pruning NOT taking place on a partitioned table, in execution plan?
  • Generally how can predicates affect partition pruning in different partition methods? for example what can cause a Full Table Scan of a partitioned table? What kind of predicates will facilitate partition pruning in Range, List, Hash, or System partitioned tables?
  • Full/Partial partition-wise joins: When will they happen in parallel?
  • You need to keep an eye on the "IN-OUT" column of the execution plans on parallel partition-wise joins and "S -> P" operations: not all the tables are read in parallel!
  • What is System Partitioning?


1.4. Parallel Processing


You need to know about the following concepts:

  • How do different values of PARALLEL_DEGREE_POLICY(AUTO|MANUAL|LIMITED) affect the behaviour of the database?
  • How the DOP for a SQL statement is calculated under either of the modes mentioned above?
  • Under which circumstances the Auto DOP is used?
  • How is DOP calculated in each mode?
  • In each mode of PARALLEL_DEGREE_POLICY  when a statement is run in parallel; i.e. using which syntax (i.e. hint) or using which parallel settings for the segments involved?
  • Under which conditions parallel SQL statements are queued?
  • How/when does the value of PARALLEL_MIN_TIME_THRESHOLD affects the execution of a SQL statement?
  • Under which circumstances CTAS and INSERT ... SELECT can run in parallel? and when does the "SELECT" part of both operations does [not] run in parallel?
  • What could cause either an ORA-7454 or ORA-12827 during parallel execution?
  • This is a tricky one: When does a direct-path insert into an IOT NOT take place in parallel even if IOT is created in parallel mode?
  • How can Resource Management be deployed to provide services with different parallel processing characteristics (e.g. statement queuing)?
  • Under which mode of PARALLEL_DEGREE_POLICY, and how does lack of I/O Calibration affect parallel processing?
  • When should PARALLEL(MANUAL) hint be used?
  • When can In-Memory parallel execution be employed? Where is the data kept in such environment? (PGA vs. Buffer Cache)
  • Under which circumstances DML statements can't be executed in parallel?
  • How does parallel execution in RAC takes place?
  • What's the effect of either of the following parameters?


1.5. DBMS_MONITOR/trcsess/tkprof


  • Which kinds of trace (i.e. which DBMS_MONITOR procs) could produce multiple trace files?
  • You need to be familiar with the sequence of actions from generating trace to using trcsess and eventually tkprof.
  • Which program consolidates trace from different trace files? trcsess? or tkprof?
  • How does tkprof treat recursive calls?
  • tkprof parameters; for example what options are available for sorting the output items?
  • How many trace files are produced by for each


 1.6. SQL Tuning Sets, SQL Tuning Adviso, SQL Access Advisor 


  • What can be kept in an STS? The syntax of different STS management operations do not matter, as much as a full grasp on the process of STS management matters.
  • What are the data sources for Automatic and Manual invocations of SQL Tuning Advisor?
  • What are the sources for SQL Access advisor?
  • What is the functional relationship between STS, Tuning Advisor, and Access Advisor? Which one is used by which?
  • What are the output of Tuning Advisor and Access Advisor? for example which one issues recommendation regarding deploying materialized views?
  • What is the role of Automatic Tuning Optimizer?


1.7. SQL Profiles and SQL Plan Management


  •  Which initialization parameters would facilitate the discovery/storage and using of the plans by optimizer?
  • What is the role of Automatic Tuning Optimizer in an ecosystem which also includes SQL Tuning Advisor, SQL Profiles, and SPM mechanism? How are the dots connected to each other? You need to know the data flow between these elements.
  • What are the different steps in the life cycle of a plan? How does a "Fixed" plan differ from an "Accepted" one?
  • What happens when a plan changes? What does "Evolution" entail?
  • How do you manage different plans for different purposes? e.g. Batch processing vs.other times


2. References and Resources


I suggest the following resources would be adequate for passing the certification exam:

  • Database SQL Tuning Guide in here
  • Database VLDB and Partitioning Guide in here
  • Troubleshooting Oracle Performance, 2nd Ed (2014), by Christian Antognini via Apress

 3. A Demand for Keeping Things In Perspective


Although the subject of Parallel Processing is mentioned in less than 20% of subjects outlined in the official exam topics, it actually was referenced in about half of the 75 questions in one way or other! So if you're not proficient in concepts of parallel processing, there's a big chance that you will not pass this exam.

Implications of such huge emphasis on the subject are somewhat confusing; especially considering the fact that "throwing resources at a problem" is not exactly an example of "Tuning."

Parallel Processing is as much of a "SQL Tuning" technique that Damien Hirst is a representative of Art, compared to artists such as Picasso, Dali, or even Anthony Gormley.

If -like me- you have a background which makes you prone to being open to conspiracy theories, you could even call it "a sign of Oracle's hidden agenda to drive the demand for more beefy ('Engineered') servers!"

But less naive exam takers could simply refer to few references to the concept of Resource Management in the exam, and simply call it "disproportionate" to the number of questions about Parallel Processing.

I believe the subject of Parallel Processing could be addressed in an exam such as Performance Tuning (1Z0-054 or 1Z0-064), AND alongside concepts like instance caging.

At least I tend to think that "SQL Tuning" is more of an art form, and Parallel Processing is the sledge hammer that Rodin never used and never would have used for creating "The Thinker."

Update: I'd like to thank Horia Berca for guiding me to slides from Tom Kyte's Optimizer Masterclass. The Optimizer Masterclass is not the preparation/training course for 1Z0-117, but It made me happy to see how the amount of emphasis that one of Oracle's gurus has put on the subject of Parallel Processing seems to bolster my position regarding the subject, and what I've expressed in this post.