Optimizer Plan Stability in Oracle9i
What? A 9I article in a 10G blog? What happened? And its not on host monitoring like you promised? Blashpemy! Well, I thought I'd deviate from 10G for one blog to show you something neat that we have been using to tune vendor queries that are not coded for "optimal performance".
So here's what I'll do to make up for it. Because Stored Outline administration has changed somehwat much from 9I to 10G, I'll make sure I cover how Stored Outlines are created and administered in a 10G environment in a future blog.
Oracle8i provided administrators with the capability to "freeze" access paths and store them in the database. The access path to the data remains constant despite data changes, schema changes and upgrades of the database or application software. Administrators are able to determine which statements use the frozen access paths and which statements must have their access paths determined at execution time.So here's what I'll do to make up for it. Because Stored Outline administration has changed somehwat much from 9I to 10G, I'll make sure I cover how Stored Outlines are created and administered in a 10G environment in a future blog.
This feature provides stable application performance and is particularly suited to ISVs that distribute packaged applications. ISVs are now able to ensure that the same access paths are being used regardless of the environment their applications are installed upon.
Optimizer plan stability also benefits high-end OLTP sites by having SQL execute without having to invoke the cost-based optimizer at each SQL execution. This allows complex SQL to be executed without the additional overhead added by the optimizer when it performs the calculations necessary to determine the optimal access path to the data.
Oracle preserves the execution plans in objects called stored outlines. You can create a stored outline for one or more SQL statements. The optimizer generates equivalent execution plans from the outlines when you enable the use of stored outlines. You can also group outlines into categories and control which category of outlines Oracle uses. This feature allows administrators to choose from multiple outlines and multiple access paths. If the SQL text of the incoming statement matches the SQL text in an outline in that category, Oracle considers both texts identical and uses the outline. Oracle considers any differences a mismatch.
But it seems that the matching process isn't as strict as a hard parse vs soft parse match performed during statement execution. Oracle matches statements being executed to statements that are already parsed and stored in the shared pool. If Oracle finds an exact match, it will use the statement in the shared pool. When the statement is found in the shared pool, it is described as being a soft parse. If Oracle does not find any matching SQL in the shared pool, it will perform a hard parse which requires more steps to be performed than a soft parse. As with mostly everything elese, less steps = faster performance. 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 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. In addition, I 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 I did not use bind variables, Oracle considered this to be a mismatch and the stored outline was not used. 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 statements' 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. That means every statment. 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
But the biggest benefit that optimizer plan stability provides, in my opinion anyway, is its ability to allow us to tune vendor SQL without getting the vendor involved. Is that great or what?
Before optimizer plan stability became available, once the administrator identified the canned application's poorly performing SQL, the third-party vendor was contacted to change the SQL code. The vendor would eventually change the code and send a patch or new release back to the customer. The customer then implemented the changed code in test and finally in the production environment to effect the tuning change. Anyone who has experience with third-party application vendors knows that this is often a time consuming (if not impossible) process. Vendors certainly try to avoid tailoring code to a specific customer. If they do, it usually comes at a high monetary cost to the requestor.
Optimizer plan stability allows administrators to use 9I OEM's Stored Outline Editor and ALTER SESSION commands to influence the optimizer to use a more high performance access path which is then frozen in the stored outline.
You can also create two stored outlines and swap their access paths. One stored outline will contain hints to achieve the better access path while the other will match the SQL that will be running in the database to achieve the stored outline/running SQL match. You then use SQL UPDATE statements to swap the stored outlines so the running SQL uses the outline's access path that was created using the hints. Go to metalink.oracle.com and search using the keyword plan stability to find more detailed instructions.
Each time the poorly performing SQL statement executes, the rewritten statement stored in the outline is used in its place. No vendor assistance required! Working with third-party application vendors is usually painful to begin with. The less I need them to tune, in most cases, the better.
To use stored outlines when Oracle compiles a SQL statement, set the system parameter USE_STORED_OUTLINES to TRUE or to a category name. This parameter can be also be set 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. 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. Oracle also allows individual statements to be used as input to stored outlines by adding some additional syntax to the statement itself.
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. In order to achieve the 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.
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. 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 code we used to create the stored outline with the optimal access path. You will notice that because the statement has to run successfully to create the stored outline, we had to wrap a lot of additional code around the it to get it to execute in SQLPLUS. We were forced to create a mini stored procedure. 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.
File courtesy of Ace DBA Ron Berner.
Because the table's schema owner was not hardcoded in the SQL coming from the vendor, we logged on as the owner of the tables to create the stored outline. You are able to use 9I OEM's Outline Management tool to determine if the outline is being used. When you become proficient at SQL tuning, you can also use it to change the outline's access path. This screenshot of the Outline Management tool shows two stored outlines, their owners, and that they were both used by at least one SQL statement.
9i OEM's Outline Editor tool is activated by clicking on the EDIT OUTLINE button in the Outline Management tool. It is important to note that you use the Outline Editor to edit the Store Outline's access path and NOT the statement itself. If you look at this screen shot, 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 and toggle back and forth between index-access and full table scans.
If you are experienced at 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).
Next Up
Back to 10G EM host performance monitoring and tuning.
Thursday, April 28, 2005
|
Permalink |
Comments (0)
trackback URL:
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-04-27.7298144282/sbtrackback
