Why Explain Plans May No Longer Matter

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:

 

 

So we must accept and embrace this performance optimization technique progression.

 

Before I explain what these new SQL optimization techniques are, let me explain how and why the old method (explain plans) can fail us. For that, I’ll use a simple analogy.

 

When I prepare for a vacation where I’m driving to my final destination, I first go to the nearest AAA office and get a “TripTik Travel Planner” – which is simply a map of the AAA recommended directions based upon whether I prefer the least distance, best time, or the most scenic route. So the map is the universe of alternatives (i.e. possible explain plans) and my choice (e.g. best time) is the deciding factor on the route they suggest (i.e. much like an optimizer hint). I know this seems overly simplified, but bear with me.

 

So what are the cons of this approach? Here’s a list of some obvious issues:

 

  • I have to drive to the AA office (gas & time)
  • 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.

 

You can try IDERA DBArtisan to see how it handles explain plans free for 14 days.