Home » Other » Training & Certification » Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities
|
|
| 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   |
 |
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 |
 |
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. 
|
|
|
|
|
|
| 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 |
|
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 #644966 is a reply to message #644961] |
Sun, 22 November 2015 12:13 |
|
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 |
|
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jan 14 07:46:12 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
