Plan stability: moving stored outlines to production
Download 36.56 Kb.
|
- Navigate this page:
- THE PROCEDURE
- Step 1: Manipulate the Session to get Desired Plan
- Step 2: Activate “Outline-Creating” Mode
- Step 4: Turn off the Outline-Creating Mode
- Step 6: Export the Stored Outline
- Step 7: Import the Outline into the Second Database
- Step 8: Confirm Proper Operation
|
The Art & Science of Oracle Performance Tuning By Chris Lawson
BACKGROUNDDBAs are sometimes tasked with preserving consistent database performance across multiple servers. This is the case, for instance, in a large shop where a test environment is used to check application performance before “going live.” In cases such as these, it is important to have stable execution plans, so that the DBAs can assure the users of good runtime performance. Stored Outlines are an excellent way to maintain consistent execution plans, especially when a SQL hint cannot be inserted into the program. Using Stored Outlines, a good execution plan can be “captured” so that any given SQL is run with the same execution plan, even when the environment changes. Most discussions of Plan Stability, however, assume that only one database is involved. While Stored Outlines can certainly be useful for one server, their biggest advantage is seen in more complicated setups involving numerous servers. In these large shops, Stored Outlines can be used to maintain identical execution plans even when the SQL is run on a different database. Although the actual process to transfer Stored Outlines to another database is not very complicated, some of the steps must be performed with great care, since even a slight mistake could cause the execution plan to astray. To add to the confusion, some of Oracle’s examples are not clear (or even contain misleading instructions). SCENARIOWe assume here that one or more SQL queries are not running properly in production. The DBA has decided to optimize the SQL in the test database first. Then, using a Stored Outline, the DBA will transfer the outline to the production database. In this way, the same execution plan will be achieved in production, without use of any SQL hints. (We will assume that changes to the application, such as adding a SQL hint, are not possible.) For purposes of this example, we use a simple query on a table called SALES. The execution plan of this query may or may not use an index, depending on which database the query is run. The DBA wishes to preserve plan stability and never use an index, no matter where the query is run. Here is the sample query and execution plan: Select Max (F_Sls_Amt) From Sales S Where Loc_Key = 5; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=2 Card=37 Bytes=222) 3 2 INDEX (RANGE SCAN) OF 'LOC' (NON-UNIQUE) (Cost=1 Card=37) The DBA would like the above query to never use an index, but perform a full table scan instead. THE PROCEDUREIn order to achieve a consistent execution plan, not using an index, we will follow these steps: Manipulate the session setup to obtain the desired execution plan Activate the “create-outline” mode Run the SQL in question Turn off the “create-outline” mode Confirm that the Stored Outline works properly Export the Outline tables Import the Outline tables into the second database Test the SQL on other database Let’s now carefully perform each step in the above process. Step 1: Manipulate the Session to get Desired PlanFirst of all, we need to somehow make the SQL run with the correct execution plan. One easy way to do this is to change some session-level parameter. Since we are trying to avoid an index read, we will adjust the cost of index reads via the parameter, Optimizer_Index_Cost_Adj to make each index read appear exorbitantly expensive. This will “fool” the optimizer so that is will perform a full table scan. Alter Session Set Optimizer_Index_Cost_Adj = 200; Step 2: Activate “Outline-Creating” ModeNote that an outline is assigned to a “Category.” In this example, we will use category “SQL1.” Alter Session Set Create_Stored_Outlines = Sql1;
Step 3: Run the SQL in questionWe create an Outline for the SQL statement(s) in question by simply running the applicable SQL queries. Note that it will run with the desired execution plan, due to the temporary change to the Optimizer_Index_Cost_Adj parameter. (If there are more than one SQL statements, we would run all of the problem statements.) Select Max (F_Sls_Amt) From Sales S Where Loc_Key = 5; Step 4: Turn off the Outline-Creating ModeAlter Session Set Create_Stored_Outlines = False; Session altered. Step 5: Test the Stored OutlineIn a fresh Sql*Plus session (e.g., without the tricky index parameter change), we must first inform Oracle that we want to use a Stored Outline. We supply the category that we used before: Alter Session Set Use_Stored_Outlines = Sql1; Session altered. Now, we simply run the SQL and confirm that the execution plan from the outline is used. Using Sql*Plus autotrace, we see from the output below that the desired full table scan is indeed used. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'SALES' (Cost=2 Card=37 Bytes=222) It is also possible to see exactly what is stored in the Outline tables. We can see the SQL for an outline by querying the OL$ view: Select Creator, Sql_Text From Outln.Ol$ Where Category = 'SQL1' CREATOR SQL_TEXT ------------------- ----------------------------------- CHRIS select max (f_sls_amt) from sales S where loc_key = 5
WHERE CATEGORY = 'SQL1' AND USER_TABLE_NAME = 'CHRIS.SALES' USER_TABLE_NAME HINT_TEXT ------------------------------- ---------------------- CHRIS.SALES NO_FACT(S) CHRIS.SALES FULL(S) The above query shows us that a Stored Outline is implemented via SQL hints! For our particular example, we see that the Stored Outline causes the Oracle optimizer to apply two hints: NO_FACT and FULL. Of course, this has the result of not using an index. Step 6: Export the Stored OutlineTo transfer this outline to another database, we export the outline tables. One slight twist: We use the export “query” clause to only export the desired outline category--in this case “SQL1.” exp outln/outln file=outline.dmp parfile=outline.par consistent=y parfile contents: tables= (ol$, ol$hints, ol$nodes) query='where category = ''SQL1''' << note that these are all single quotes About to export specified tables via Conventional Path ... . . exporting table OL$ 1 rows exported . . exporting table OL$HINTS 6 rows exported . . exporting table OL$NODES 1 rows exported Step 7: Import the Outline into the Second DatabaseNow, on the second database, we simply bring in the 3 tables we just exported: imp outln/outln file=outline.dmp ignore=y . importing OUTLN's objects into OUTLN . . importing table "OL$" 1 rows imported . . importing table "OL$HINTS" 6 rows imported . . importing table "OL$NODES" 1 rows imported Step 8: Confirm Proper OperationOn the second database, before actually running the SQL, let’s first confirm that the outline has been imported properly: Select Creator, Sql_Text From Outln.Ol$ Where Category = 'SQL1' CREATOR SQL_TEXT ------------------- ----------------------------------- CHRIS select max (f_sls_amt) from sales S where loc_key = 5 Since the Outline really does exist, let’s try our query. But first, we need to inform Oracle that we wish to use a Stored Outline in the category, SQL1: Alter Session Set Use_Stored_Outlines = Sql1; Session altered. Test the SQL with Sql*Plus Autotrace activated: Select Max (F_Sls_Amt) From Sales S Where Loc_Key = 5; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'SALES' (Cost=2 Card=37 Bytes=222) We see that the “right” execution plan is achieved from the SQL1 stored outline that was just imported. WRAP-UPCreating and moving Stored Outlines is pretty easy. The only tricky part is getting the SQL to temporarily run the “right” way, so that we can record the desired execution plan. In the example here, the trick we used was to change a session level parameter so that the optimizer would work a little differently (i.e., the way we wanted it to). Once we have figured out how to obtain the desired executed plan, it is very simple to create a Stored Outline for one or more SQL statements. Once the Outline is stored (and tested), the import/export utilities are used to move the desired Outlines to the production database. One important thing to remember is that Oracle is very picky about deciding when to use a Stored Outline. The SQL being run must be nearly identical to the SQL stored in the Outline. In fact, prior to Oracle 9i, any difference in the SQL meant that the Outline would not be invoked. With Oracle 9i, the process is only slightly more forgiving. With Oracle 9i, it is possible for an Outline to be used if there are some SQL differences in “white space” and case. Directory: white papers white papers -> MDaemon AntiVirus white papers -> Operating System Microsoft Windows 2000 tcp/ip implementation Details white papers -> September 2007 Copyright Microsoft Corporation 2007. All rights reserved white papers -> Toward a Science of Autonomy for Physical Systems: Paths Download 36.56 Kb. Share with your friends:
|