Some of the information in this article is also covered in my 10g blog on DBAzine.com. But I have currently become such a strong proponent of Optimizer Plan Stability that I thought it was important to give a little more in-depth coverage to this feature.

I’ll start by providing you with a high-level overview of Optimizer Plan Stability. I’ll then continue the discussion by describing the issue that was facing us here at Giant Eagle and how we used this cool feature to solve our problems.

Introduction to Optimizer Plan Stability

Oracle8i provided administrators with the capability to “freeze” a SQL statement’s access path and store it in the database. The access path to the data then remains constant despite data changes, schema changes, and upgrades of the database and/or application software. This feature is called “Optimizer Plan Stability” because it guarantees that exactly the same optimization plan (access path) will be used each time the SQL statement is executed. Administrators are able to determine which statements use the frozen access paths and which statements must have their access paths determined at execution time.

Optimizer Plan Stability ensures predictable SQL performance and is particularly beneficial for third-party software vendors that distribute packaged applications. The vendors are able to guarantee that the same access paths are being used, regardless of the environment in which their applications are running. I have worked for a third-party software vendor in the past, so I can state that we spent a lot of time correcting customer mistakes that were negatively impacting the optimizer’s ability to choose the most optimal access path. Freezing the access paths allowed us to focus on the myriad of other issues we faced as a third-party application provider.

Optimizer Plan Stability also benefits high-end OLTP sites by enabling SQL to execute without having to invoke the cost-based optimizer at each SQL execution. This allows us to execute complex SQL without the additional overhead added by the optimizer when it performs the calculations necessary to determine the optimal access path to the data. I have tuned online applications that had so many concurrent users, it was important for us to get them in and out of the database as quickly as possible. Optimizer Plan Stability was one of the features we used to reduce transaction runtimes. We were forced to use Optimizer Plan Stability because the vendor code did not utilize bind variables and activating cursor sharing produced less-than-optimal access paths to the data. Please refer to text later in this article about cursor sharing (which usually works really well, by the way, and is the preferred method for reducing optimization times for applications that don’t use bind variables).

Before I continue, let me state that the decision to use Optimizer Plan Stability to freeze a statement’s access path should not be made lightly. The optimizer’s job is to calculate the most efficient access path to the data. Data is almost always fluid by nature (it changes as the result of normal, day-to-day database operations). To maintain adequate performance, a SQL statement’s access path must often change accordingly.

In addition, administrators often use hints in combination with Optimizer Plan Stability to influence the optimizer’s access-path selection (as we had to do). I’ll show you how to do this later in this article. By using hints, you are telling Oracle that your access paths are better than the ones the optimizer is choosing. It’s a safe assumption that, in most cases, most of us aren’t as smart as the optimizer. Let it make the choice, unless you are certain the optimizer is choosing the incorrect access path.

But what happens if the optimizer is making incorrect decisions? Before you begin adding hints to SQL or freezing access paths using Optimizer Plan Stability, consider taking the following steps first:

• Determine if it is actually an incorrect access path that is causing the performance problem. It may be some external influence affecting the SQL (hardware, workload, and so on).
• Identify and review the SQL taking the bad access path for proper SQL coding techniques.
• Verify that statistics have been generated on the tables and indexed columns. The Oracle-supplied utility DBMS_STATS is currently the preferred method of collecting statistics.
• Review parameters that affect SQL optimization (optimizer_mode, optimizer_index_cost_adj, optimizer_index_caching, optimizer_dynamic_sampling, optimizer_features_enable, optimizer_max_permutations).
• Investigate system statistics. Is it activated? Is it configured correctly if it is activated? Should it be activated?
• Does the application use bind variables? If so, investigate bind peeking quirks.
• Check for skewed data. Consider using histograms to compensate.
• Go to Metalink and review optimization bugs for your release. Oracle could have already identified your issue and fixed it.

OK, so you have performed all of the actions cited previously and you find that the optimizer is actually making an incorrect decision. Regardless of what some industry pundits may tell you, the optimizer is NOT infallible; it can make mistakes. Oracle created hints for a reason, and wouldn’t have made them public if it didn’t think we really needed them from time to time. If you are forced to add hints to the query to improve its performance, do so intelligently and judiciously.

But what happens if you can’t change the SQL being executed?

Tuning Third-Party Applications

Optimizer Plan Stability allows us to tune SQL that we can’t change. That includes tuning a third-party software vendor’s SQL without getting the vendor involved.

Before Optimizer Plan Stability, when an administrator identified the canned application’s poorly performing SQL, the third-party vendor was contacted to change their software. The vendor changed the code and delivered the updated software to the customer. After the customer tested the software to make sure the change didn’t affect the application’s functionality, the new code was implemented in the production environment to affect the tuning change. Anyone who has experience with third-party application vendors knows that this is often a time-consuming (if not impossible) process.

However, by using Optimizer Plan Stability, administrators are able to capture the statement’s access path and use OEM or SQL update statements to apply hints to influence the optimizer to create a more optimal execution plan.

Stored Outlines

Oracle preserves the execution plans in objects called “Stored Outlines.” You can create a Stored Outline for one or more SQL statements and group Stored Outlines into categories. Grouping Stored Outlines allows you to control which category of outlines Oracle uses. As a result, you can toggle back and forth between multiple outline categories and, therefore, multiple access paths for the same statement. If the SQL text of the incoming statement matches the SQL text in a Stored Outline in that category, Oracle considers both texts identical and uses the outline. Oracle considers any differences to be a mismatch.

But it seems that the matching process isn’t as strict as the hard-parse vs. soft-parse match that is performed during statement execution. Before a statement enters the parse phase, Oracle matches the statement being executed to statements that are already parsed and stored in the shared pool. When Oracle finds a matching statement in the shared pool, it will do a soft parse on the SQL statement. If Oracle does not find any matching SQL in the shared pool, it will perform a hard parse, which requires that more steps be performed than a soft parse. In this, as with almost everything else, fewer steps = faster performance. During a soft parse, you bypass the step of creating the access path, so one way to reduce the number of optimizations performed is to perform soft parses.

The key to obtaining a high number of soft parses is to match as many incoming statements to statements already stored in the shared pool as possible. This is trickier than it sounds and requires vigilant and rigorous SQL coding standards. When Oracle looks for a matching SQL statement in the shared pool, the statements must match exactly. That is, the statement must match in case, line breaks, and spacing. If the SQL doesn’t match exactly, Oracle executes a hard parse.

I was interested to learn whether or not the SQL matching process for Stored Outlines was just as strict as for soft parses. I experimented with capitalizing some of the characters in the Stored Outline SQL and executing the same statement in SQLPLUS in all lower case, and it still used the outline. Good news! I then placed hits of ENTER in the SQL I was executing in SQLPLUS and, once again, the outline was used. I finished my testing by using a combination of line breaks, extra spaces, lower- and upper-case, and the outline was still used. But if I rearranged any text in the statement, or if I did not use bind variables, Oracle considered this to be a mismatch, and it did not use the Stored Outline. If the SQL statement you want to use to create the Stored Outline never changes its search criteria, I would expect that you would be able to use the statement without using bind variables.

Oracle documentation states that administrators are able to activate cursor sharing if bind variables are not used to get the SQL statements running in the database to match the SQL in the Stored Outline. Setting the parameter CURSOR_SHARING to SIMILAR or EXACT tells Oracle to replace all hard-coded values in the SQL statement’s WHERE clause with bind variables. I have used this parameter to reduce hard parses in a large online transaction-processing database and it certainly works. Oracle replaced every statement’s hard-coded values with bind variables. I mean, every statement. I noticed that it was also transforming 9i OEM’s SQL used to retrieve database performance statistics.

So, statements that used to look like this in V$SQLTEXT:

SELECT FIRST_NAME, LAST_NAME, DEPT_ID FROM SCOTT.EMP 
WHERE LAST_NAME = 'SMITH'

look like this when you set the parameter CURSOR_SHARING = SIMILAR:

SELECT FIRST_NAME, LAST_NAME, DEPT_ID FROM SCOTT.EMP 
WHERE LAST_NAME = :V1

Only once did activating cursor sharing cause the optimizer to choose less-than-optimal access paths; we were then forced to use Stored Outlines to reduce the number of statements being optimized. As stated previously, using soft parses to reduce the number of SQL statements sent to the optimizer is much more efficient than freezing their access paths with Stored Outlines, but using Stored Outlines is certainly a viable backup plan. I recommend that you use soft parses first, then fall back to Stored Outlines if the primary solution fails.

Optimizer plan stability allows administrators to use 9i OEM’s Stored Outline Editor or SQL UPDATE statements to influence the optimizer to use a more high-performance access path, which is once again frozen in the Stored Outline.

If you want to learn how to use SQL UPDATE statements to change a statement’s frozen access path, go to METALINK.ORACLE.COM and search using the keywords “plan stability” to find detailed instructions in Document Number 144194.1 (it should be the first document that pops up in the search).

To create Stored Outlines, use the CREATE OR REPLACE OUTLINE statement to create an outline containing a single SQL statement. Administrators can freeze multiple SQL statements’ access paths in a Stored Outline by executing the following steps:

• Log in to SQLPLUS.
• Issue the ALTER SESSION SET CREATE_STORED_OUTLINES =TRUE statement to freeze access paths in the DEFAULT category or ALTER SESSION SET CREATE_STORED_OUTLINES = user defined category name statement to freeze access paths in a user-defined category name.
• Run one or more SQL statements that must have their access paths frozen.
• Execute ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE to deactivate the storage of access paths.

To use Stored Outlines when Oracle compiles a SQL statement, set the system parameter USE_STORED_OUTLINES to TRUE or to a user-defined category name. You can also set this parameter at the session level. If you set USE_STORED_OUTLINES to TRUE, Oracle uses outlines in the DEFAULT category; if you specify a category name with the USE_STORED_OUTLINES parameter, Oracle uses outlines in that category until you re-set the USE_STORED_OUTLINES parameter to another category name or until you suspend outline use by setting USE_STORED_OUTLINES to FALSE. And if you specify a category name, and Oracle does not find an outline in that category that matches the SQL statement, Oracle searches for an outline in the DEFAULT category.

Remember that you are trying to get the SQL being executed to use a more efficient access path. In our case, the vendor was optimizing the entire database for high-volume transactions that required sub-second response times. To achieve quick response times, the vendor set OPTIMIZER_MODE to FIRST_ROWS, OPTIMIZER_INDEX_CACHING to 90 and OPTIMIZER_INDEX_COST_ADJ to 20 — all excellent settings if you want to influence the optimizer to use NESTED LOOP joins and index access paths to improve online transaction performance.

However, our problem was that the application also required the execution of large batch jobs that joined large tables and scanned a large percentage of the tables being accessed. Needless to say, the parameter changes made by the vendor to achieve sub-second OLTP response time didn’t allow the optimizer to create the most optimal access paths for heavy-batch processing. We realized that the only way we could get the vendor’s batch jobs to run in the desired timeframes was to influence some of the access paths to use more HASH joins and table scans (which are typically more suited to processing large volumes of data).

We achieved this by using the ALTER SESSION commands to customize our session parameter settings in SQLPLUS. We used the ALTER SESSION command to set OPTIMIZER_MODE to ALL_ROWS and set OPTIMIZER_INDEX_ CACHING and OPTIMIZER_INDEX_COST_ADJ to their default values.

Here’s the Create Stored Outline code we used to create the Stored Outline with the optimal access path. You will notice that, because the statement must run successfully to create the Stored Outline, we had to wrap a lot of additional code around the SQL to get it to execute in SQLPLUS. That’s because the SQL used input bind variables, which you have to supply to get it to run. In addition, SQLPLUS will choke if you select values from columns in PL/SQL and don’t put them in predefined output variables. That is why all of those output variables are identified. Because the table’s schema owner was not hard coded in the SQL coming from the vendor, we logged on as the owner of the tables to create the Stored Outline. You can use 9i’s 9i OEM’s Outline Management tool to determine whether or not the Stored Outline is being used. The following screenshot of the Outline Manager shows two Stored Outlines, their owners, and that they were both used by at least one SQL statement.

Figure 1: Outline Manager.

Stored Outlines allowed us to change several key SQL statements that were jeopardizing the application’s usefulness to our business users. The vendor is currently changing their batch programs to embed the ALTER SESSION commands required to influence the optimizer to create more optimal access paths. Why didn’t we catch this in test? It looks like we may have hit a previously unknown Oracle bug on which Oracle support is currently working.

You are also able to use 9i OEM’s Outline Management tool to create Stored Outlines. It’s really quite simple to use OEM to create the outline: You select CREATE from the Outline Management tool menu on the left and paste the SQL statement into the box that appears. That’s it! When you become proficient at SQL tuning, you can then use 9i OEM’s Outline Editor tool to change the outline’s stored access path.

You can access 9i OEM’s Outline Editor tool by clicking on the EDIT OUTLINE button in the Outline Management tool. It is important to note that you use the Outline Editor tool to change the statement’s access path, but not the statement itself. If you look at this image of the Outline Editor tool, you’ll see that I have activated the Join Method Properties Panel, which allows me to change the statement’s join method. You can also use the editor to determine which table is accessed, first in the join; then you can toggle back and forth between index-access and full-table scans.

Figure 2: Outline Editor.

If you are experienced in tuning SQL and know the access path you want, you can dump the SQL into the Outline Management tool, create the Stored Outline, and then activate the Outline Editor tool to manipulate the access path to achieve optimal performance. Is this a great tool, or what? Oracle also provides PL/SQL programs to perform the same activities, but I’m now hooked on Oracle’s SGT (Sissy GUI Tools).

So, why did we have to use SQLPLUS to create our Stored Outlines and not 9i OEM’s Outline Editor tool? Remember that we were using ALTER SESSION commands in SQLPLUS to change the optimization mode to CHOOSE and reset some index optimization settings to their default values; otherwise, we would have used the Sissy GUI Tool to create and change the statements’ access paths.

Wrap-up

Freezing and changing a statement’s access path should only be done when you have exhausted all other options. But the benefits that this feature provides should not be understated. It helped us in our third-party SQL performance emergency; now, we have another tool in our toolbox to use. The more tools we have at our disposal, the more quickly we can solve problems for our customers.

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.