For far too long, database administrators and developers have focused on explain plans for tuning SQL statements that perform poorly. But with Oracle 11g and 12c, explain plans can quite possibly become a thing of the past. Furthermore, 12c can perform the entire tuning process automatically – with minimal time spent during the initial SQL development and maybe 1/10th the effort spent addressing any problematic SQL that somehow does find its way into production.
Does this all sound too good to be true? Then read on …
Let’s start by defining what an explain plan is – at least conceptually. The explain plan is the “approximate algorithm” that the Oracle database engine will attempt to “execute” in order to perform the requested database operation. Since SQL is a non-procedural, set oriented language – the explain plan is the only way to “guestimate” what the SQL code will actually do, because the code itself only expresses what is to be done – and not how. The explain plan exposes both the algorithm and indexes (if any) that the database engine would utilize during actual execution.
Over the years (and major database versions), Oracle has tried to make SQL optimization more stable and reliable than simple explain plans. An excerpt from the book Oracle 11g New Features cleanly delineates this progression nicely – which has been as follows:
- Explain plans for Oracle versions <= 7.X
- Plan stability for Oracle versions 8i and 9i
- SQL Profiles for Oracle version 10g
- SQL Plan Management for Oracle version 11g and 12c
- I have to wait in line for my turn to be helped
- The office may not have current information
- The person helping me might be misinformed
- I could say scenic route whereas the family simply wants to get there quickly
- Things can change between getting the TripTik and starting/ending my trip
- I must possess both an automobile and valid drivers license to make the trip
- I’m purposefully eliminating far better methods or mode choices (e.g. flying)
Believe it or not, the exact same cons can often exist for explain plans – as follows:
- During execution phase, the explain plan must be derived
- Step #1 requires some resources, so there could be a delay
- The statistics and/or histograms could be out of date (i.e. stale)
- SQL may so malformed that fast explain plan derivation could be suboptimal
- SQL code might contain hints that are suboptimal and yet take precedence
- The fundamental performance conditions can change (e.g. new indexes, removal of indexes, partitioning of object, partitioning of indexes, “init.ora” changes that might affect SQL execution, addition and/or subtraction of server resources, etc.)
- User must known how to obtain and read explain plans, and how to make SQL rewrites and/or database structure changes in order to improve the performance
- Oracle 11g’s Automatic SQL Plan Management can often yield great results for far less (if not minimal) effort
So let’s examine how the newer versions of Oracle have totally eliminated such concerns.
Oracle 10g adds the new SQL Tuning Advisor – a utility to “Analyze individual SQL statements, and recommend SQL profiles, statistics, indexes, and restructured SQL to improve overall SQL execution performance.” Using my vacation analogy, the SQL Advisor is more like going to the American Airlines web site and booking airfare for the same trip. I’ll let the airline, pilot, navigator, tower staff, and air traffic controllers worry about all the details – I just have to say get me from point A to B for whatever price and travel time I am willing to bear. Likewise, we can now manually inform Oracle to worry about the algorithmic details churning inside – then we’ll just let the database know how much effort (i.e. time) to expend in order to meet our general SQL performance needs.
Thus Oracle 10g requires the DBA to remain engaged to generate, review and implement the recommendations, as shown below in Figure 1 (which is a slide from the Oracle 11g new features training class). Returning to the vacation analogy via an airline, I still have to manually input the origin, destination, dates, seat class and number of stops that I’m willing to endure. I can then choose from among the flight recommendations, but until I press the button for “purchase the ticket” – nothing real has as of yet actually happened. It’s all just “what-if” type analysis whose impact won’t be felt unless formally chosen.
Figure 1: Manual SQL Tuning in Oracle 10g
So for 10G, we’d look at OEM’s Advisor Central SQL Tuning Advisors, and specifically the SQL Tuning Advisor looking for any statements with excessive database times or IO. The AWR repository will automatically contain all the SQL Tuning Candidates and they will be broken down along their relative percentage contribution to the cumulative total, as shown below in Figure 2.
Figure 2: Top SQL by IO Tuning Candidates
The SQL Tuning Advisor will offer suggestions to improve performance – as well as an estimate of the percentage improvement, as shown below in Figure 3. Note too that it suggests more than just alternate explain plans (which is essentially just an internal SQL rewrite), but it also offers other critical advice – including database structural changes.
Figure 3: Tuning Advisor Recommendations
Oracle 11g adds SQL Plan Management which can essentially automate the 10g scenario in whole, as shown below in Figure 4 (which is another slide from the Oracle 11g new features training class). Returning once again to my vacation analogy via a flight would be like setting some American Airlines web site booking preferences – and automatically having the airline purchase those tickets that best meet all my needs (and all without any involvement from me). Yes – I’m giving up some control, but in the real world we call such scenarios excellent customer service. So why should the database and/or DBA offer anything less? And for those DBA’s who don’t want to relinquish such control or worry about the relative overhead involved, the last step can be either manual or automatic.
Figure 4: Automatic SQL Tuning in Oracle 11g and 12c
Of course in Oracle 12c this only gets better. Instead of just preserving repeatedly executed SQL statements as candidates, it now records and tests all SQL statements. I for one cannot wait to see what 12c R2 offers to improve this.