How to Transfer Stored Outlines from One Database to Another (9i and above)

(Doc ID 728647.1)

Last updated on MAY 23, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.4 [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Goal

This document shows you how to export Stored Outlines from one database and import them on another in Oracle9i and above.

For Oracle8i please use:

<Note:102311.1> How to Transfer Stored Outlines from One Database to Another (8i)

A typical usage scenario is when queries are getting a sub-optimal execution plan in a production database PROD, but are performing well in a similarly configured test database QA. Here it is possible to force each query on PROD to use the same execution plan as on QA via a Stored Outline. The steps are:

  1. create Stored Outlines for the queries on QA
  2. export the Stored Outlines using the EXP utility
  3. transfer the dumpfile to the production system
  4. import the Stored Outlines on PROD using the IMP utility
  5. enable them for use thus allowing the queries on PROD to use the same plan as on QA

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms