Toad World® Forums

How to create and migrate outlines to another database


#1

I have run into several situations where an optimized query in development will execute totally different in production. The data is more or less production data including amount of data with the same 11gR2 init params in each. Also with similar hardware. The problem is one is 11.2.0.2 and the other is 11.2.0.1. I was going to create an outline for the tuned query in development and migrate it to production so it executes the same in both. The help guide in the tool says I can do this, but instructions are not clear or conflicting to what actually happens. Also can and how do I migrate these outlines to my production database using the tool ??


#2

There are some reasons that a tuned SQL being deployed to a production database with different performance from your testing environment.

  1.   SQL has different plans in testing and production environment
    

Since the same SQL syntax apply to different size of databases may result into different plan by database SQL optimizer.
You can other better alternative SQL with hints instead of selecting the best performance SQL without hints.
Use our latest plan control SQL tuning technology in Quest SQL Optimizer version 8, you can deploy Plan Baselines to production database without the need to change you source code.

Outline is not encouraged by Oracle in their latest release, I recommend you use baseline instead of Outline for SQL tuning.

  1.   SQL has the same plan in testing and production environment
    

Although it is a rare case that a tuned SQL has the same plan in testing and production database with different performance, but for certain environments, that hardware configuration such memory size, cpu and IO speed or software configuration in init.ora that will cause the different performance of a plan. The most important factor is the data volume and data distribution, some plans will perform significantly in different speed while the data distribution is changed.

Thanks,
Richard


#3

You can SELECT other better alternative SQL with hints instead of selecting the best performance SQL without hints.


#4

I come from Oracle 9i moving to 11g. Is there a good tutorial on how I go about using plans with my sql. And will I be able to easily migrate my execution plans to production. We have essentially imported into our development database our latest production export. So sizes of tables are very much the same with only sensitive data replaced. The only real difference is that the development database is 11.2.0.2.0 and my production database is 11.2.0.1.4.


#5

**I copied some information from Oracle manual for your reference in the following, you can export and import stored outlines from 9i to 11g, and then you might want migrate the outlines into plan baselines for future enhancement of SQL speed.
--------------------------------------------------------------------**20.2.2.1 Upgrading with a Test System

A test database, separate from the production database, is useful for conducting experiments with optimizer behavior after an upgrade. You can migrate statistics from the production system to the test system using import/export. This technique alleviates the need to fill the tables in the test database with data.

You can move outlines between the systems by category. For example, after you create outlines in the problemcat category, export them by category using the query-based export option. This is a convenient and efficient way to export only selected outlines from one database to another without exporting all outlines in the source database. Use the Data Pump Export utility with the QUERY parameter as in the following example (note the use of the line continuation character):

% expdp outln DIRECTORY=outln_dir DUMPFILE=exp_file.dmp \

? TABLES=OL$,OL$HINTS,OL$NODES QUERY=‘WHERE CATEGORY=“problemcat”’

Password: password

**To migrate stored outlines to SQL plan baselines: **

  1. In SQL*Plus, call PL/SQL function MIGRATE_STORED_OUTLINE.
    The following sample PL/SQL block migrates all stored outlines to fixed baselines:

DECLARE

my_report CLOB;

BEGIN

my_report := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => ‘all’ );

END;

/


#6

If I am understanding this right, I tune my query in TEST create a plan baseline in TEST. Then I am suppose to use that baseline for PROD. How do I migrate my baseline from TEST to PROD ??


#7

Hi Rich,

You can deploy a baseline from test to production by using the Migrate feature of the Manage Plans module. The easiest way to access that module based on your workflow would be to press the Manage Plans link in the yellow bar displayed in the Deploy tab after you’ve deployed the plan in test. You can also get there by directly selecting the Manage Plans main menu option and then locating your SQL in the list of baselines.

Then select the Export, Import, Migrate tab and the the Migrate tab. Finally, enter the production information and migrate your baseline to the production environment.

I’ve attached some images showing the steps to get to the migrate page.

We are also considering streamlining this process in a future release of the product.

Best Regards,
Greg


#8

Hi Rich,

You can deploy a baseline from test to production by using the Migrate feature of the Manage Plans module. The easiest way to access that module based on your workflow would be to press the Manage Plans link in the yellow bar displayed in the Deploy tab after you’ve deployed the plan in test. You can also get there by directly selecting the Manage Plans main menu option and then locating your SQL in the list of baselines.

Then select the Export, Import, Migrate tab and the the Migrate tab. Finally, enter the production information and migrate your baseline to the production environment.

I’ve attached some images showing the steps to get to the migrate page.

We are also considering streamlining this process in a future release of the product.

Best Regards,
Greg


#9

Hi Rich,

You can deploy a baseline from test to production by using the Migrate feature of the Manage Plans module. The easiest way to access that module based on your workflow would be to press the Manage Plans link in the yellow bar displayed in the Deploy tab after you’ve deployed the plan in test. You can also get there by directly selecting the Manage Plans main menu option and then locating your SQL in the list of baselines.

Then select the Export, Import, Migrate tab and the the Migrate tab. Finally, enter the production information and migrate your baseline to the production environment.

I’ve attached some images showing the steps to get to the migrate page.

We are also considering streamlining this process in a future release of the product.

Best Regards,
Greg


#10

Hi Rich,

You can deploy a baseline from test to production by using the Migrate feature of the Manage Plans module. The easiest way to access that module based on your workflow would be to press the Manage Plans link in the yellow bar displayed in the Deploy tab after you’ve deployed the plan in test. You can also get there by directly selecting the Manage Plans main menu option and then locating your SQL in the list of baselines.

Then select the Export, Import, Migrate tab and the the Migrate tab. Finally, enter the production information and migrate your baseline to the production environment.

I’ve attached some images showing the steps to get to the migrate page.

We are also considering streamlining this process in a future release of the product.

Best Regards,
Greg


#11

This is very helpful information. I still have a problem in that I can’t find the ‘generate plan baseline’ for a sql query in the optimizer tool. I can get to the PLAN MANAGEMENT, but it’s blank because I have yet to create one. Do I need to configure the tool ?? Or am I missing something. I do want to point out that my SQL Optimizer was in the DBA Suite package and is not a standalone version - if that makes a difference.


#12

Hi Rich,

You can deploy a SQL baseline plan by using the Plan Control function in the Optimize SQL module. Select the Optimize SQL main tab and then press the Plan Control button to open a Plan Control session. You can also select New Plan Control Session from the New Session drop down arrow. Once you have identified an improved plan you wish to deploy, select that alternative SQL plan in the alternatives list, right-click, and select Deploy Plan. Verify the settings presented and then press the Deploy button. I’ve attached a few screen shots to help clarify.

Regards,
Greg
Plan Control 2.png


#13

Hi Rich,

You can deploy a SQL baseline plan by using the Plan Control function in the Optimize SQL module. Select the Optimize SQL main tab and then press the Plan Control button to open a Plan Control session. You can also select New Plan Control Session from the New Session drop down arrow. Once you have identified an improved plan you wish to deploy, select that alternative SQL plan in the alternatives list, right-click, and select Deploy Plan. Verify the settings presented and then press the Deploy button. I’ve attached a few screen shots to help clarify.

Regards,
Greg


#14

Hi Rich,

You can deploy a SQL baseline plan by using the Plan Control function in the Optimize SQL module. Select the Optimize SQL main tab and then press the Plan Control button to open a Plan Control session. You can also select New Plan Control Session from the New Session drop down arrow. Once you have identified an improved plan you wish to deploy, select that alternative SQL plan in the alternatives list, right-click, and select Deploy Plan. Verify the settings presented and then press the Deploy button. I’ve attached a few screen shots to help clarify.

Regards,
Greg