Home » Other » Training & Certification » Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities
Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638165] Thu, 04 June 2015 14:48 Go to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

This is directed specifically at Kevin. Someone asked me on the OTN certification forum for a SQL tuning book to help them prepare for certain aspects of the 1Z0-117 SQL Tuning certification exam. I suggested that he open a thread on OraFAQ and ask you if the bits he wanted more help with for the exam were covered in your book. He did not do this. He saw the reviews on your book and bought it (you're welcome). I told him that while I was confident that your book would give him more insight into SQL tuning, that I was not positive how much of what your book covers corresponds to exam topics.

That said, I really wanted to know what the topic correlations were between your book and the 117 exam, so I was looking forward to your reply. Unfortunately, the 'Look Inside' of your book on Amazon doesn't show the full Table of Contents, so I can't do what I normally would and map out the TOC to the test topics.

Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities, by Kevin Meade
http://www.amazon.com/gp/product/1501022695/
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638185 is a reply to message #638165] Fri, 05 June 2015 07:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, the book was not written as an Exam prep guide, nor was it intended to compete with such guides directly. Instead the book is for teaching what people need to know about SQL Tuning Science in order to tune 9 out of every 10 Problem Queries they will be faced with in the business DP world of Oracle. Anyone who reads my book will as a result, have the tools and knowledge to become an excellent SQL Tuner. I would expect this improves one's ability to pass related exams. But the process of learning SQL Tuning takes time and effort in actually doing tuning. So there is no immediate correlation between becoming a good SQL Tuner and passing any of the Oracle exams, because learning to be a SQL tuner is a long term goal, and passing a specific exam is a short term goal. I guess I would say this:

Quote:
If you want to learn how to tune SQL, get my book. If you are in a hurry to pass the 117 exam, buy training material about that exam. Of course, though it may cost you a few extra dollars, you can always do both. And do not forget that most companies have a reimbursement policy for books, so fill in an expense report and let your company pay you back. No need to spend your own money when someone else's will do.


My book is about acquiring and improving upon a lifelong career skill. Passing an exam is about putting a certification blurb on your pedigree. Though they are both valuable goals, they are clearly different goals with different time horizons.

Mathew, if you have not read the book yet, send me your mailing address in a PM and I will mail you out some copies. Attached please find chapter 1 and the scripts from the book, which you can share with others thinking about getting the book.

Kevin

[Updated on: Fri, 05 June 2015 07:07]

Report message to a moderator

Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638188 is a reply to message #638185] Fri, 05 June 2015 08:20 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

I 100% agree that there is a huge difference between learning to tune SQL and learning to pass the SQL Tuning exam.

I'm going to have to switch to another test for a good example, but here goes. My study guide on 1Z0-052 is all about passing that test. A lot of the facts that it covers are useful to Oracle database administrators, but the guide is not designed to teach someone to be a DBA. John Watson's 1Z0-052 book is a superset of my guide. It not only goes into the facts needed for the test, but also pulls many/most out into context an deals with how they relate to being an Oracle DBA. This makes the book definitely better for teaching someone how to be a DBA, but arguably less optimized for teaching them how to pass the test. The Oracle Database 11g DBA Handbook by Oracle Press is all about how to be an Oracle DBA and not directed toward the exam at all. However, much of what is covered in it will be applicable to the test and reading it would be valuable for people who want to be better DBAs.

Because I *know* my study guides don't cover everything people need in the real world to actually do the jobs in question, I always recommend people use other sources of study as well to broaden their knowledge base. My question was whether your book might be in that third class, where it covers some of the topics that are in 1Z0-117 despite not being designed for it at all. The chapter you included has the full table of contents, so I used that to do a quick analysis of the topics of the exam it appears your book will touch on. They include:

Describe what attributes of a SQL statement can make it perform poorly
Explain the tuning tasks
Describe the execution steps of a SQL statement
Explain the need for an optimizer
Explain the various phases of optimization
Control the behavior of the optimizer
Gather execution plans
Display execution plans, display xplan
Define a star schema, a star query plan without transformation and a star query plan after transformation
Interpret execution plans
Describe the SQL operations for tables and indexes
Describe the possible access paths for tables and indexes
Describe Clusters, In-List, Sorts, Filters and Set Operations
Use hints when appropriate
Specify hints for Optimizer mode, Query transformation, Access path, Join orders, Join methods and Views
Understand an explain plan of a parallel query
Understand an explain plan of parallel DML and DDL
Explain the available partitioning strategies
Explain partition pruning

That's a solid subset of the exam -- even if that wasn't your intent. Smile
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638189 is a reply to message #638188] Fri, 05 June 2015 08:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Mathew.

I think though that my books does not cover as much as you might want. For example, my book does expect that the reader has some knowledge of performance tuning topics. As an example, I do not believe there is anywhere in my book where I actually give a description of the PARTITION PRUNING is, and I make reference to it only a few times. This example shows why if a person's goal is to pass the exam, then they would be better served by finding material specifically aiming at passing the exam.

I would go take the exam and write one my self, but I see you (Mather Morris) and John Watson as the two guys filling the void for exam prep needs.

Kevin
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638190 is a reply to message #638188] Fri, 05 June 2015 08:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Another BIG difference to consider too is that the exam will likely be very centered around Oracle specific tools and architecture. For example, if you review the exam introduction from Oracle, the topics list has a topic of "describe the Oracle tools that can be used to tune SQL". So I am betting there are sections in the exam on SQL Tuning Advisor, OEM, RAT (Real Application Testing), SQL-T even, how to gather data in order to create an ORACLE SUPPORT TICKER, and so on. But I don't give two hoots about that stuff and this shows how the exam focus is different from actual SQL Tuning as a skill.

These specific products are all good products, and each covers a niche or offers functionality that is useful, for anyone willing to spend the time to learn the tools. But actual SQL tuning does not require any of them. Its like if Toad was to have a SQL Tuning exam, they would want you to know how to do SQL Tuning from their Toad tool which is going to be different from doing it via OEM. So Oracle will expect you to know their tools, and will have material on the exam that covers them. But these to me are a distraction from learning the core principles of SQL Tuning. These to me would be follow up material that once someone knows how to tune SQL, they could focus on which ever avenue was most valueable to them, given what their company wants them to learn. But I would never include material on any of them in a book on how to tune SQL.

Consider that with my book there are about 2 dozen scripts. OEM as a tool shows nice charts. But where does OEM get the info from to build those charts and what does the info really mean? My scripts show where and by book explains what the data means, and there is no need to know OEM to understand the importance of it.

So bottom line is what we have said already. If you want to learn how to tune SQL as a lifelong career skill, get my book. If you are heading to a testing center two weeks from today to take exam 117 and you want an edge on passing the exam, study with training materials based on the exam.

Kevin

[Updated on: Fri, 05 June 2015 08:57]

Report message to a moderator

Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638191 is a reply to message #638190] Fri, 05 June 2015 09:36 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Kevin:

I would never recommend your book as the primary source on studying for the exam (and neither would you -- as per the above). In this case the specific question from the person on OTN was "I need a good book on the oracle sql plans." He had already taken the exam (and failed it) once and wanted to get a book that went into greater depth on that subject. I had seen enough of your posts on OraFAQ that even without having read your book, I was pretty sure that it was going to go into great depth on reading and interpreting execution plans.

Understand that what you're saying is what I'm saying. When I suggest a book as a secondary source for a given certification exam -- I'm not telling someone "This book what you need to read in order to pass the exam". Instead I would provide it as a way to learn more about certain exam topics and connect them with real life practicality. In order to be able to make that recommendation, I wanted to know more about what SQL tuning topics your book covers.

I'll also note that I don't use the various tools (SQL Tuning Advisor, SQL Access Advisor, etc.) in real life either. My methods are much less refined than yours but they tend to work for me. They result in well-written SQL rather than what the two advisors above do -- which is essentially to make poorly written SQL run faster.
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #638192 is a reply to message #638191] Fri, 05 June 2015 13:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I get it, thanks Mathew. Send me your address, I'll send you some copies of the book.

Kevin.

Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #644961 is a reply to message #638192] Sun, 22 November 2015 06:51 Go to previous messageGo to next message
eramsaier
Messages: 1
Registered: November 2015
Junior Member
Kevin,
I'm in the process of reading your book and can't tell you how amazing it has been to find a book that finally lays out in practical terms what to do and how to do it. I'm now considering the oracle tuning certs, but a thought struck me. Considering the continual improvements in databases and technology (the 2% rule highlights this), will sql performance tuning simply go way down in importance? I separate out pl/sql from sql tuning. Some databases have built-in tuning advisors, suggesting what to index. Optimizers get better and better each release. Full table scans become more and more viable. And then there are columnar, in-memory databases w/ massive parallelism. Is performance tuning value dwindling away and does it make sense to go for that sort of certification?
Again, loving the book!
Eric
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #644966 is a reply to message #644961] Sun, 22 November 2015 12:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well Eric, if we look at this in terms of informal rules, noting that as hardware and software gets more sophisticated, it is able to do more for us, then we can also note that the more our hardware and software can do for us, the more we ask it to do for us. People are infinitely creative and thus able to get themselves into an infinite amount of trouble. So tuning as a skill will I think always have some value.

It may come to pass some day that databases are good enough to make themselves work without too much intervention. But I think there is at least one more generation between us and a reasonable facimile of that day, so I don't see tuning skills devalued too much in our working lifetimes. One of the things that makes tuning as a skill valuable is that not a lot of people do it well. In my company, there are many people with basic tuning skills, but none of them understood the concept of cardinality as a driver till they read the book. Fewer actually practice it even after reading my book. Like anything it takes effort to get good at and they do not want to spend the effort on it, expecially when there is someone who has already demonstrated they have the skill and so can do the work for them.

Looking back across the 30 years of my time with the Oracle database, and visualizing the state of tuning during those years, I can say that the status quo has been the same for all those years. A few people really know how to do it, most people struggle. And the "1% of the code does 90% of the work" rule taught to me by an Oracle Tuning Team speacialist in the begining of my career has always been true. Even with all the "self-tuning" the database does, this has remained true.

Still you are correct, there is a limit to which any single skill has value, and we need to morph our skills accordingly to what is going on in the world around us. For example, EXADATA adds new ideas to the tuning world of Oracle, as does the new IT mode of processing for DATA MINING and the emergence of the DATA SCIENTIST role and what it implies. So tuning will have to change to extend itself to cover these areas. But this maybe a good retort to your concern. As some avenues close, others open.

Lastly, sql tuning is only one skill of many you should have. I don't tune all day long you know. Not any more anyway. Having demonstrated superior results with superior skills, they now want me to be one of the THINKERS rather than one of the DOERS. I would suggest you should have two or three or more certifications if possible. In terms of hiring, many companies like to see the badges so their computer softeware looks for them in resumes and puts them at the top of the list. Having multiple certifications lets you talk about how your skills cover a wide area and how you blend them together to better effect. So if you feel confident you have studied enough to get the tuning certification, go for it, it will only help you, especially if you then follow through with others that map to the kinds of jobs you want in the future.

THE SQL TUNING SKILL

is a skill hard to learn even with good books and teachers, and so will always be rare.
like any skill, must morph to encompass new technologies that present new challenges.
is only one skill  of several you should have that together let you tell a story about yourself as you want to tell it.


Of course this is all just one man's opinion. Kevin
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #644967 is a reply to message #644961] Sun, 22 November 2015 14:40 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Quote:
...will sql performance tuning simply go way down in importance?
...
Is performance tuning value dwindling away and does it make sense to go for that sort of certification?


Long ago, when dinosaurs roamed the Earth, a spreadsheet program called Lotus123 became the top dog in the market. It did so largely because most PCs had 1 megabyte of RAM, but only 640K was conventionally accessible. The Lotus developers figured out a way to page in memory above 640K within the app and were able to do things as a result that the competing products could not. Fast forward to today and no one bothers trying to write applications that make the most efficient use of RAM or processor cycles or hard drive space. Developing applications faster has completely eclipsed developing efficient applications.

From this perspective, it is sadly true that the ability to write efficient SQL or manually tune inefficient SQL is almost certain to be valued less by employers as the ability of the database/hardware/etc are able to overcome the downsides of poorly written SQL. A 'smarter' CBO, and the SQL Tuning Advisor (and the SQL Access Advisor to a lesser degree) do not 'fix' SQL so much as they are able to make bad SQL run faster.

Does that mean that it no longer makes sense to write good SQL or tune bad SQL? At a former employer, one of my tasks was maintaining an application written before I was hired that ran hundreds of queries against insurance data. Whoever wrote the queries did a really bad job -- mainly because he tested against very small data sets. I was routinely able to rewrite the queries and make them execute in a tenth the time or less. This was on a 10g database, so the world will never know if the Advisors or CBO of 11g or 12c could have made a comparable improvement (but I seriously doubt it). Even if they could have, the same capabilities would presumably also have been able to speed up my improved queries. In my opinion, so long as Oracle is used by companies as their enterprise database, the ability to create efficient SQL -- either from scratch or by improving inefficient SQL -- will continue to have value.

Learn the skills covered in the topics of the Oracle SQL Tuning Expert exam so that employers 'see' that you are a SQL Tuning expert. However, learn how to tune SQL using methods like those covered in Kevin's book so that you really are one.
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #645005 is a reply to message #644967] Mon, 23 November 2015 09:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
Learn the skills covered in the topics of the Oracle SQL Tuning Expert exam so that employers 'see' that you are a SQL Tuning expert. However, learn how to tune SQL using methods like those covered in Kevin's book so that you really are one.


Most true. The exam, being an Oracle designed test, likely touches on many topics I do not cover in the book. Oracle may have considerable material on their tool sets for one thing. Consider this before taking the exam. This is why Matthew recommends spending some time with specialized study materials based on the exam, before taking an exam. Believe it or not, I have not taken the tuning exam, but Matthew has and passed it.

Kevin
Re: Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities [message #645077 is a reply to message #645005] Wed, 25 November 2015 13:15 Go to previous message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Quote:
This is why Matthew recommends...


Well I would yes -- if Eric will be taking the exam. The direction I was really going in, however, is that while the test does get into a lot of really good information about how the optimizer works, how to read execution plans, how to use SQL Traces and tkprof, and more -- it does not really attempt to get into HOW to take a piece of SQL that performs badly and transform it into a piece of SQL that does not perform badly. The only sections of the exam really about improving the performance of SQL are given over to the SQL Access Advisor and the SQL Tuning Advisor. Knowledge of how to use those tools is useful -- especially if you need to make a query perform better when you don't have the bandwidth to tune it manually. However, I wouldn't consider someone who can use those tools to improve performance (but no other method) an expert at SQL tuning.

As such -- passing the exam allows someone to put a line on their resume that indicates they are an expert at SQL Tuning... whether or not they really are. Reading (and understanding) Kevin's book is more likely to result in expertise at tuning SQL. However -- adding "I read Kevin Meade's book" to your resume will do nothing more than confuse a hiring manager. Smile
Previous Topic: 1Z0-068
Next Topic: The Best Oracle Practice Tests Available
Goto Forum:
  


Current Time: Thu Mar 28 06:23:06 CDT 2024