export import sql plan baselines

Migrating sql baselines from one database to another

                     Follow below steps for migrating sql plan baselines from one database to another.

1. Get the sql_plan and sql_handle[SOURCE]

2. create a staging table[source], 3. pack the sql_plan to the staging table[source], 4. take export of staging table[source], 5. copy the dump file and import in the target database.[target], 6. unpack the staging table:[target], 7. check whether sql plan has been loaded or not[target].

SIMILAR ARTICLE

Migrate sql profiles from one database to another:

Run sql tuning advisor for a sql_id

Change The Execution Plan Without Changing The Sql Query

MONITOR YOUR DB

About Admin

Related articles.

• Useful TFACTL commands
• How to install trace file analyzer( TFACTL)
• How to generate AWR report in RAC
• Useful ADRCI commands in oracle
• How to drop SQL baselines in oracle
• How to generate ADDM report
• SGA TARGET ADVISORY IN ORACLE
• Shared Pool Advisory in oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Sql plan base line export and import it in new database

Check the count of sql baselines to be exported:-

select count(*) from dba_sql_plan_baselines; 725

Create a user with which the base should be exported:-

create user sqbl identified by “#####”;

grant connect,resource,dba to sqbl;

alter user sqbl default tablespace users;

Connect to the database and create baseline table:-

sqlplus “/as sysdba”

conn sqlbl/#####;

BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE( table_name => ‘stage1’); END; /

var x number; begin 😡 := DBMS_SPM.PACK_STGTAB_BASELINE(‘STAGE1′,’sqbl’); end; /

Export the baseline table:-

expdp sqbl tables=sqbl.stage1 directory=DATA2 dumpfile=baseline_test.dmp logfile=baseline_test.log

SCP the dump from source to target

Target database:-

Create the same user in target database:-

create user sqbl identfied by “#####”; grant connect,resource,dba to sqbl;

alter USERS tablespace of whatever default tablespace for import user to at least 8G size.

Import the table in target database using the follwoing:-

impdp sqbl tables=sqbl.stage1 directory=DATA2 file=baseline_test.dmp logfile=baseline_test_imp.log

Unpack the baselines in the target database:-

var x number; begin 😡 := DBMS_SPM.UNPACK_STGTAB_BASELINE(‘STAGE1’, ‘sqbl’); end;

Start typing and press Enter to search

Move SQL Plan Baseline to other Instance

Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.

To import a set of SQL plan baselines from one system to another:

STEP 1) create a staging table

Use the CREATE_STGTAB_BASELINE procedure.

STEP 2) Pack the SQL plan baselines

Pack the sql plan baseline that you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function.

The following example packs enabled plan baselines created by user dba into staging table stage. You can select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or any other plan criteria. The table_name parameter is mandatory.

STEP 3) Export the staging table

Export the table (in our case stage table) into a flat file using the Oracle Data Pump Export utility.

Use export command exp

STEP 4) Transfer the flat file to the target system.

Using secure copy or other method copy the generated stage.dmp to target system

STEP 5) Import the staging table

Import the staging table from the flat file using the Oracle Data Pump Import utility.

STEP 6 ) Unpack the SQL plan baselines

Unpack the SQL plan baseline from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function.

The following example unpacks all fixed plan baselines stored in the staging table stage:

STEP 7) VERIFY

Verify if SQL plan baseline got imported successfully

• Recent Posts

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
• Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021

Related posts:

• Move SQL PROFILE to other database
• Create SQL Baseline for SQL_ID
• Flush Bad SQL Plan from Shared Pool
• 11i Split configuration- Move 11g Database to Linux 6 64Bit (PART 3 in “11i to R12.1.3 upgrade” series)
• Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
• Find SQL_ID of a SQL Statement in a PL/SQL Block
• Resizing the Instance – Oracle Compute Cloud
• Adding new user in Oracle Apps (EBS) from back end
• Create Database instance on Oracle Database Cloud
• Creating single compute instance – Oracle Cloud

Categories: SCRIPTS SQL Tuning TUNING Tuning Scripts

10 Comments

Mentioned wrong table name in step -1 It should be stage not stage1.

By d way, you explained so nicely with example.

Thanks. Corrected Typo.

Can I migrate a baseline from 11.2.0.4 to an Oracle 12 database?

thanks Brijesh

Good information but it would be handled if you included the SQL to get the SQL_HANDLE.

When I tried to unpack the plan I got the following errors. I’m not sure what to do. I’ve run the same sql script against both databases so they should both have it.

declare l_plans_unpacked PLS_INTEGER; * ERROR at line 1: ORA-38172: No SQL management object satisfies specified filters ORA-06512: at “SYS.DBMS_SMB”, line 1615 ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95 ORA-06512: at “SYS.DBMS_SMB”, line 1600 ORA-06512: at “SYS.DBMS_SPM”, line 1840 ORA-06512: at line 3

thank for your publication.

will it work? if I migrate sql profiles and base line plans from existing 11.2.0.4 to 19c. pls advise. thanks.

Name (optional)

Email (optional)

SQL Plan Baselines

May 18, 2022 • knowledge 000078955 no 0 0 0, article details, other details, system information, to be removed.

• Terms of Use
• Privacy Policy
• Do not sell my personal information
• Contact support

© 2023 Informatica. All Rights Reserved

• Database News

SQL Plan Baselines are a new feature in Oracle Database 11g that helps to prevent repeatedly used SQL statements from regressing because a newly generated execution plan is less effective than what was originally in the library cache. Read on to learn more…

What is one of the most challenging aspects of performance tuning in an Oracle database? There are probably a fair number of Oracle DBAs that will tell you dealing with the performance of SQL in their databases is one of the biggest challenges they encounter. SQL statement tuning is often painstaking and can be a very time consuming activity to say the least. Then, once properly tuned, making sure the most resource intensive statements stay that way is yet another hurdle that is faced by DBAs.

The cost based optimizer will generate new execution plans for statements that already have a plan cached in the library cache because of a variety of changes in the database. Sometimes, these new plans perform worse than the plan(s) cached in memory. The list of actions or changes in the database that cause execution plans to be regenerated include upgrades or patches to the optimizer, update optimizer statistics, DDL changes that affect the objects being accessed in the statement, system setting changes and the creation of SQL profiles. All of these are actions commonly performed in regular database maintenance operations.

Oracle has introduced several major features over several releases that are, or have been, designed to help the DBA manage and control the execution plans that the optimizer generates for repetitive statements run in our databases. These features include stored outlines, SQL Profiles and most recently with Oracle 11g, SQL Plan Baselines.

The drawback to stored outlines and SQL profiles is that they are reactive in nature and may require maintenance and updating. SQL Plan Baselines on the other hand are much more automated, and more proactive in their behavior. New plans can only be added to the SQL Plan Baseline if they will not result in a slower performance, and only plans that are actually in the SQL Plan Baseline are used by optimizer to generate the plans to process statements.

SQL Plan Baselines are a new feature in Oracle 11g that helps to prevent repeatedly used SQL statements from regressing because a newly generated execution plan is less effective than what was originally in the library cache. It should be noted however, that SQL Plan Baselines do not help if the action taken in the database is so significant that the execution plan absolutely must change, for example if a previously used index is dropped.

Defining SQL Plan Baselines

Defined, a SQL Plan Baseline is a set of one or more "accepted" plans that contain hints, the plan hash value and other plan related data. In addition to the actual baseline, Oracle also maintains a SQL Plan History. This history is a list of all execution plans generated for a statement, including those that have and have not been moved into the SQL Plan Baseline. Acceptable execution plans are moved from the SQL Plan History into the SQL Plan Baseline, which is referred to as evolving the plan. The baseline and history are maintained in the SQL Management Base (SMB), which is kept in tables in the SYSAUX tablespace. Included in the SMB is also any related SQL profiles for the statements.

The diagram that follows shows the relationship between the SMB, Plan History and Plan Baseline. A statement has had three different execution plans generated. Plan 1 and Plan 2 have performed well, and have been accepted into the baseline. Plan 3 was also generated, however it did not perform as well as the other two, and therefore, while it is part of the history, it is not a baseline plan.

Before a plan in the SQL Plan Baseline can be used or selected by the optimizer, the SQL Baseline must be initialized with at least one accepted plan for the repeatable statements being run. The two activities that populate the SQL Plan Baselines are capturing and evolving. Capturing is the initial load of plans into the baseline, evolving is the evaluation of new plans in the SQL History to ensure they will not cause the statement to regress and then adding them to the SQL Baseline.

Capturing SQL Plan Baselines

During the capture phase, Oracle maintains a log of the SQL ID for statements executed against the database. If a statement is parsed or executed after it was initially logged, it is considered a repeatable statement. For each of these statements, the SQL History is created and new plans (if generated) are added to it. There are two ways to initiate the capture, automatically and manually.

To do an automatic load, change the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE rather than it’s default of FALSE. This is a dynamic parameter, so the capture process can be quickly started and then stopped if necessary. During automatic capture, the first plan generated for any statement is flagged as accepted and placed into both the SQL History and the SQL Baseline. Any subsequent execution plans are placed only in the history.

Manual loading of the initial SQL Baseline is accomplished by using the DBMS_SPM package supplied with Oracle. There are two functions that load SQL plans. These are LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM CURSOR_CACHE. Any plans that are manually loaded are placed into the SQL Plan Baseline.

DBMS_SPM also has a procedure that will migrate stored outlines that may already exist for any of the high-load statements into the SQL Plan Baseline. This function is called MIGRATE_STORED_OUTLINE and was introduced as a part of Oracle Database 11g R2. This means, the work done previously to create stored outlines can now be used to initially seed the SQL Plan Baselines, which is a nice feature.

For more details on running these procedures, refer to DBMS_SPM Documentation .

Evolving SQL Plan Baselines

If the optimizer generates a new plan for a repeated SQL statement, it is automatically added to the SQL Plan History. However, it is not automatically added to the baseline. In order for a new plan to be added to the SQL Plan Baseline, it must be "evolved" or verified first. Once again, there are several methods for evolving a plan from the history into the baseline.

The Automatic SQL Tuning job (also new in 11g) that is run as an automatic task during the maintenance window will automatically verify plans that have been added into the SQL Plan History. Verification simply means that the plans are checked to ensure that the new plan will not result in a performance degradation or regression. This is an out-of-the-box feature that focuses on high-load statements because these are the statements that the Automatic SQL Tuning job selects to analyze.

In addition, manually running the SQL Tuning Advisor may result in plans being added to the SQL Plan Baseline. If the SQL Tuning Advisor yields a recommendation to create and use a SQL Profile, if that profile is accepted, the corresponding plan is automatically added to the baseline.

The ALTER_SQL_PLAN_BASELINE function of DBMS_SPM can be used to change the status of plans in the SQL History to Accepted, which in turn moves them into the SQL Baseline and the EVOLVE_SQL_PLAN_BASELINE function of the DBMS_SPM package can be used to see which plans have been evolved.

Fixing SQL Plan Baselines

Another option that can be used with SQL Plan Baselines is to mark one (or more) specific plan as a fixed baseline. If a fixed plan exists in a baseline, the optimizer will give priority to that fixed plan, even if a better, lower cost plan is available.

This would enable a DBA to essentially guarantee (or at least make most likely) a very specific plan to be used by optimizer. However, there are some factors to be aware of, if this approach is used. First, the optimizer will not add new plans to the history if there is a fixed baseline and the EVOLVE_SQL_PLAN_BASELINE function will not evolve plans either. New plans in the SQL Plan Baseline would have to be added by manually loading them from the SQL Cache or a SQL Tuning set. Second, there is a possibility that a better execution plan will be ignored because it has not been marked as fixed.

A plan can be marked as fixed by using the ALTER_SQL_PLAN_BASELINE function of DBMS_SPM and changing the FIXED attribute to a value of YES.

DBA_SQL_PLAN_BASELINES Dictionary View

This is the main dictionary view used to see information about the SQL Plan Baselines. There are several columns that are of particular interest:

Selecting SQL Baseline Plans

Once SQL Plan Baselines are in place, additional steps are taken to evaluate whether or not an execution plan from the SQL Plan Baseline should be used for a repeatable statement run in the database. In order for Oracle to replace a generated SQL Plan with a plan from the SQL Plan Baseline, the OPTIMIZER_USE_SQL_PLAN_BASELINES must be set to TRUE (which is the default value).

For each statement that is run, the following steps take place.

Controlling SQL Baseline Space Usage

The SQL Management Base (SMB) area allocated in the SYSAUX tablespace is controlled by two settings, and an automatic purge activity takes place to remove any plans that have not been used within a designated period of time.

By default, the SMB is limited to no more than 10 percent of the SYSAUX tablespace. This can be adjusted by using the DBMS_SPM.CONFIGURE(‘SPACE_BUDGET_PERCENT’,nn); command. The value of nn can be from 1percent to 50percent. If the space is exceeded, warnings are generated to the alert log on a weekly basis until the space issue is resolved by adjusting the percentage, changing the size of SYSAUX, or plans are purged.

Plans are purged on a weekly basis. By default, any plan that has not been used in 53 weeks will be automatically purged. This is also an adjustable value and is changed with the DBMS_SPM.CONFIGURE(‘PLAN_RETENTION_WEEKS’,nnn); procedure. Valid values are from 5 to 523 weeks. The LAST_EXECUTED column of DBA_SQL_PLAN_BASELINES will indicate which plans might be up for purging in the near future.

In addition, plans can be manually removed from the SMB using the DBMS_SPM.DROP_SQL_PLAN_BASELINE function.

Wrapping Up

Additional Information on SQL Plan Baselines is available through the website listed above, including how to export/import plans from one database to another using DataPump, and more details on migrating stored outlines into SQL Plan Baselines.

Overall, SQL Plan Baselines may prove to be a very useful tool for DBAs by providing a benefit that will actually enhance the general stability of SQL performance by avoiding the use of new execution plans that will result in a slower, regressed query result. An additional benefit may be found in reducing the time and effort that DBAs have to spend doing SQL Statement tuning.

» See All Articles by Columnist Karen Reliford

Related Articles

Best certifications for database administrators, best courses for database administrators, type definition change in oracle 21c, get the free newsletter.

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

Sql sort by statement, working with null values in sql, working with sql and, or, and not operators, how to use databases with python.

Bhavesh Rathod is an Oracle Database Cloud Architect with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.

Samujjwal Roy is a Database Specialty Architect with the Professional Services team at Amazon Web Services. He has been with Amazon for 15+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.

• Getting Started

Blog Topics

• Amazon Aurora
• Amazon DocumentDB
• Amazon DynamoDB
• Amazon ElastiCache
• Amazon Keyspaces (for Apache Cassandra)
• Amazon Managed Blockchain
• Amazon MemoryDB for Redis
• Amazon Neptune
• Amazon Quantum Ledger Database (Amazon QLDB)
• Amazon Timestream
• AWS Database Migration Service
• AWS Schema Conversion Tool
•  Twitter
•  Facebook
•  LinkedIn
•  Twitch
•  Email Updates

• Blog Masonry
• Blog Masonry Full Width
• Checkout ? Pay
• Order Received
• My Bookings
• Full width page
• Home Elementor
• How does it work?
• Morbi varius augue
• Most popular posts
• Change Password
• Edit My Address
• Lost Password
• Page Markup And Formatting
• Password Recovery
• Privacy Policy
• Qualcomm jumps into wearables with Toq smartwatch
• Sample Page
• Site-Wide Activity
• Terms of service

Export Oracle SQL PLAN Baselines from one database to another

Hello everyone, a few weeks ago, during a project, our client requested that during the database migration process we take the sql plan baseline of the current databases., i found the topic interesting to share with you, how to do this migration of sql plan baselines from one database to another., note: the database user must have “administer sql management object” to execute the steps below., 1 . create a staging table from the source database. a staging table cannot be created in sys schema.(ora-19381), i used the system schema., 2. pack baselines in the source database:, here we can do it in four ways., a) pack all baselines in the database., b) pack all baselines plans of a query, c) pack a specific baseline plan of a query, d) pack only accepted baseline plans of a query, 3. export the staging table migbaseline from the source database., 4. import the staging table migbaseline into the target database., 5. unpack the baselines in the target database., i hope this helps you, andre luiz dutra ontalba.

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

SQL Plan Management in Oracle Database 11g Release 1

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

How Does SQL Plan Management Work?

Automatic plan capture, manual plan loading, evolving sql plan baselines, step-by-step example, altering plan baselines, displaying sql plan baselines, sql management base, transferring sql plan baselines, dropping plans and baselines, enterprise manager.

Related articles.

• Adaptive SQL Plan Management (SPM) in Oracle Database 12c Release 1 (12.1)

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default. Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE , determines if the system should automatically capture SQL plan baselines. When set to TRUE , the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

I would advise doing considerable testing before using automatic plan capture in a production environment.

Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

The following code uses the LOAD_PLANS_FROM_SQLSET function to load all statements in an existing SQL tuning set into SQL baselines. A filter can be applied to limit the SQL statements loaded if necessary.

The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache. There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID , SQL_TEXT , PARSING_SCHEMA_NAME , MODULE and ACTION . The following example identifies the SQL statement using the SQL_ID .

The return value of the LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM_CURSOR_CACHE functions indicates the number of plan loaded by the function call.

Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

This section represents a step-by-step example of using SQL plan baselines. It uses manual loading, so switch off automatic plan capture.

Create and populate a test table.

Query the table using an unindexed column, which results in a full table scan.

Identify the SQL_ID of the SQL statement by querying the V$SQL view.

Use this SQL_ID to manually load the SQL plan baseline.

The DBA_SQL_PLAN_BASELINES view provides information about the SQL plan baselines. We can see there is a single plan associated with our baseline, which is both enabled and accepted.

Flush the shared pool to force another hard parse, create an index on the ID column, then repeat the query to see the affect on the execution plan.

Notice the query doesn't use the newly created index, even though we forced a hard parse. Looking at the DBA_SQL_PLAN_BASELINES view we can see why.

The SQL plan baseline now contains a second plan, but it has not yet been accepted.

Note: If you don't see the new row in the DBA_SQL_PLAN_BASELINES view go back and rerun the query from "spm_test_tab" until you do. It sometimes takes the server a few attempts before it notices the need for additional plans.

The following query uses the EVOLVE_SQL_PLAN_BASELINE function to evolve the SQL plan baseline and output the associated report.

The DBA_SQL_PLAN_BASELINES view shows the second plan as been accepted.

Repeating the earlier test shows the more efficient plan is now available for use.

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

• enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
• fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
• autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
• plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
• description : Used to amend the SQL plan description, up to a maximum of 30 character.

The following shows a specific plan being marked as fixed.

These attributes are present in the DBA_SQL_PLAN_BASELINES view.

In addition to querying the DBA_SQL_PLAN_BASELINES view, information about SQL plan baselines is available via the DBMS_XPLAN package. The DISPLAY_SQL_PLAN_BASELINE table function displays formatted information about a specific plan, or all plans in the SQL plan baseline in one of three formats (BASIC, TYPICAL or ALL). The following example displays the default format (TYPICAL) report for a specific plan.

The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs, plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE procedure of the DBMS_SPM package.

• space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
• plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.

The current settings are visible using the DBA_SQL_MANAGEMENT_CONFIG view.

The following example shows both values being reset.

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

The staging table is then transferred to the destination database using data pump or the original export/import utilities. Once in the destination database, the SQL plan baselines are imported into the dictionary using the UNPACK_STGTAB_BASELINE function. Once again, there are several parameters allowing you to limit amount and type of data you import. The following example imports all SQL plan baselines owned by the user "TEST".

The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.

SQL plan baseline management is integrated into Enterprise Manager. Click on the "Server" tab. Then click on the "SQL Plan Control" link in the "Query Optimizer" section. On the resulting page, click the "SQL Plan Baseline" tab. The "SQL Plan Baseline" screen, shown below, allows you to manage the SQL plan baselines.

For more information see:

• Using SQL Plan Management

Hope this helps. Regards Tim...

Back to the Top.

Created: 2008-03-26  Updated: 2019-07-31

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall Copyright & Disclaimer

• Install App

Oracle Database Discussions

Importing and exporting sql plan baselines , 11gr2.

I am trying to use imported  baseline I set for one query but it's not working.

What I did so far  :

1) alter system set optimizer_capture_sql_plan_baselines=true;

2) execute query  so  baseline would be created

3) create staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure

4) pack that  baseline using the DBMS_SPM.PACK_STGTAB_BASELINE procedure

5) export staging table using data pump

6) scp the dump file to the target server

1) alter system set optimizer_use_sql_plan_baselines = true;

2) import the staging table  using the data pump

3) succesfully unpacked baseline using the DBMS_SPM.UNPACK_STGTAB_BASELINE procedure

4) execute the same sql as the one on the db1 for which is created,packed and unpaced baseline

5) query is now using that baseline, which is fixed,accepted and enabled but with the execution plan the same as it was without the baseline

To sum it up,

this is the query ,

select  * from atable order by id desc ;

On db1 this query uses this plan ,

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------

Plan hash value: 1956699976

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT            |         |   112M|    20G|  2775K  (1)| 00:04:58 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ATABLE |   112M|    20G|  2775K  (1)| 00:04:58 |

|   2 |   INDEX FULL SCAN DESCENDING| A_PK  |   112M|       | 21099   (1)| 00:00:03 |

   - SQL plan baseline "SQL_PLAN_bb0pj1mwnykvh9ec174aa" used for this statement

And exactly the same query on db2 is still having 'old' plan , although OEM grid is showing it has that unpacked plan attached ,

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2902006383

--------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT   |         |   105M|    19G|       |  5473K  (1)| 00:09:46 |

|   1 |  SORT ORDER BY     |         |   105M|    19G|    28G|  5473K  (1)| 00:09:46 |

|   2 |   TABLE ACCESS FULL| ATABLE |   105M|    19G|       |   869K  (1)| 00:01:34 |

9 rows selected.

What I'm trying to do is force the index operation for that query on db2 , gathering fresh stats and hinting  didn't help , just like imported baseline.

Full table scan is always used.

DDL of both tables are the same, size of the table is maybe 0,1 % bigger @ db1 then  @ db2  , and exact matching signature is the same as the signature for that sql_id in the v$sql .

Any suggestions what can I check next, did I missed something in the process of baseline  packing/migrating/unpacking  ?

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor
• SQL Plan Management ,

Upgrade to Oracle Database 12c and Avoid Query Regression

Those of you that made it to the demo grounds at Oracle Open World this year (BTW - it’s still 2015 - just) will have had the chance to chat with the Oracle developers and throw lots of difficult questions at them! For everyone in the Optimizer development team it was a great opportunity to get lots of interaction and feedback, so thanks to all of you that took the time to come along and talk to us. We’re not all lucky enough to get a trip out to San Francisco, so I’ve been collating the main topics that came up to steer the subject matter of the next few blog posts. In this way I hope that we can all benefit from these interactions.

The bottom line is this: if you want to mitigate the risk of query regression when you upgrade to Oracle Database 12c, then use SQL Plan Management (SPM). This is the recommended approach for the vast majority of systems out there, where the most critical SQL statements are reusable or, in other words, they are executed more than once.

Here are a couple of common scenarios:

Ideally you will have captured SQL plan baselines before you’ve upgraded, because then you’ll have a set of “good” execution plans at-the-ready. It isn’t absolutely necessity to do this, though. As long as you can reproduce or find an example of a good plan, then this can be used to create a SQL plan baseline on-demand. For example, you may find a better plan:

• By running the problem query in a pre-upgrade environment (remembering that you can export and import SQL plan baselines to copy them between databases).
• Inside an existing SQL Tuning Set (STS).
• By reproducing the good plan in the post-upgrade environment using (for example) “alter session set optimizer_features_enabled = 11…”, adjusting other Optimizer database parameters or by using hints. Yes, setting this parameter to an older version will give you the plan of the older version; that’s the whole purpose of it (and if it doesn’t work for you then it usually means that there’s a bug).

The next step is the particularly clever part, but I get the impression that a lot of Oracle experts don’t know that it’s even possible. When you’ve found a good plan and captured the details in a SQL plan baseline, you can use SPM to associate it with a regressed SQL statement without having to change the existing query or the existing application code. For details, take a look in the section, “Creating an accepted plan by modifying the SQL text” in an earlier Optimizer blog entry and the section entitled, "Using SPM to Correct Regressed SQL Statements" in the SQL Plan Management with Oracle Database 12c Release 2 white paper. In both cases, an improved SQL execution plan is found using a hint. This plan is associated with a regressed SQL statement so that, in future, the better plan is used.

Scenario 2 You should capture SQL Plan Baselines in your Oracle Database 11g environment and export them so that they can be imported into the upgraded database. If you are upgrading in-place, then existing SQL plan baselines will be available without the need to export and import them. If you neglected to capture baselines in the pre-upgrade environment, then you still have the option to capture 11.2 execution plans in an Oracle Database 12c environment by executing your queries in a session where the database parameter optimizer_features_enabled is set to “11.2.0.4” (or whatever version you like).

Once SQL plan baselines are established in the upgraded database, you will enjoy plan stability while you get to know your system in the critical early days after the upgrade. Once you are happy with your shiny new database, you can evolve SQL plan baselines either automatically or at your own pace. Then you will gain advantage of all the new Optimizer features available in Oracle Database 12c.

You do not need an additional licence to use SQL Plan Management in EE. A subset of functionality is also available in SE (without additional licence) for Oracle Database 18c onwards.

Whenever you plan to upgrade, check out Oracle’s Database Upgrade Blog . It’s full of really great information and it will hook you up with the latest advice at the finest level of detail. For example, here are some useful specifics on SPM to get you started.

Nigel Bayliss

Product manager.

Nigel is the product manager for the Oracle Optimizer. He's worked with Oracle technology since 1988 and joined Oracle in 1996. He's been involved in a wide variety of roles including development, benchmarking, consulting and pre-sales. 

Previous Post

Why are there many cursors for queries containing bind variables?

Efficient statistics maintenance for partitioned tables using incremental statistics – part 1.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2023 Oracle
• Privacy / Do Not Sell My Info

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Import and export data from SQL Server and Azure SQL Database

• 13 contributors

You can use a variety of methods to import data to, and export data from, SQL Server and Azure SQL Database. These methods include Transact-SQL statements, command-line tools, and wizards.

You can also import and export data in a variety of data formats. These formats include flat files, Excel, major relational databases, and various cloud services.

Methods for importing and exporting data

Use transact-sql statements.

You can import data with the BULK INSERT or the OPENROWSET(BULK...) commands. Typically you run these commands in SQL Server Management Studio (SSMS). For more info, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) .

Use BCP from the command prompt

You can import and export data with the BCP command-line utility. For more info, see Import and Export Bulk Data by Using the bcp Utility .

Use the Import Flat File Wizard

If you don't need all the configuration options available in the Import and Export Wizard and other tools, you can import a text file into SQL Server by using the Import Flat File Wizard in SQL Server Management Studio (SSMS). For more info, see the following articles:

• Import Flat File to SQL Wizard
• What's new in SQL Server Management Studio 17.3

Use the SQL Server Import and Export Wizard

You can import data to, or export data from, a variety of sources and destinations with the SQL Server Import and Export Wizard. To use the wizard, you must have SQL Server Integration Services (SSIS) or SQL Server Data Tools (SSDT) installed. For more info, see Import and Export Data with the SQL Server Import and Export Wizard .

Design your own import or export

If you want to design a custom data import, you can use one of the following features or services:

• SQL Server Integration Services. For more info, see SQL Server Integration Services .
• Azure Data Factory. For more info, see Introduction to Azure Data Factory .

Data formats for import and export

Supported formats.

You can import data from, and export data to, flat files or a variety of other file formats, relational databases, and cloud services. To learn more about these options for specific tools, see the following topics

• For the SQL Server Import and Export Wizard, see Connect to Data Sources with the SQL Server Import and Export Wizard .
• For SQL Server Integration Services, see Integration Services (SSIS) Connections .
• For Azure Data Factory, see Azure Data Factory Connectors .

Commonly used data formats

There are special considerations and examples available for some commonly-used data formats. To learn more about these data formats, see the following topics:

• For Excel, see Import from Excel .
• For JSON, see Import JSON Documents .
• For XML, see Import and Export XML Documents .
• For Azure Blob Storage, see Import and Export from Azure Blob Storage .

If you're not sure where to begin with your import or export task, consider the SQL Server Import and Export Wizard. For a quick introduction, see Get started with this simple example of the Import and Export Wizard .

Submit and view feedback for

Additional resources

• SQL Tuning Guide
• SQL Management Objects
• Managing SQL Plan Baselines

29 Managing SQL Plan Baselines

This chapter explains the concepts and tasks relating to SQL plan management using the DBMS_SPM package.

" Migrating Stored Outlines to SQL Plan Baselines "

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_SPM

29.1 About Managing SQL Plan Baselines

This topic describes the available interfaces and basic tasks for SQL plan management.

29.1.1 User Interfaces for SQL Plan Management

You can access the DBMS_SPM package through Cloud Control or through the command line.

29.1.1.1 Accessing the SQL Plan Baseline Page in Cloud Control

The SQL Plan Control page in Cloud Control is a GUI that shows information about SQL profiles, SQL patches, and SQL plan baselines.

To access the SQL Plan Baseline page:

Log in to Cloud Control with the appropriate credentials.

Under the Targets menu, select Databases .

In the list of database targets, select the target for the Oracle Database instance that you want to administer.

If prompted for database credentials, then enter the minimum credentials necessary for the tasks you intend to perform.

From the Performance menu, select SQL , then SQL Plan Control .

The SQL Plan Control page appears.

Click Files to view the SQL Plan Baseline subpage, shown in Figure 28-1 .

Figure 28-1 SQL Plan Baseline Subpage

You can perform most SQL plan management tasks in this page or in pages accessed through this page.

Cloud Control context-sensitive online help to learn about the options on the SQL Plan Baseline subpage

" Managing the SPM Evolve Advisor Task "

29.1.1.2 DBMS_SPM Package

On the command line, use the DBMS_SPM and DBMS_XPLAN PL/SQL packages to perform most SQL plan management tasks.

The following table describes the most relevant DBMS_SPM procedures and functions for creating, adding, dropping, and loading SQL plan baselines.

Table 28-1 DBMS_SPM Procedures and Functions

Also, you can use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE to show one or more execution plans for the SQL statement identified by SQL handle.

" About the DBMS_SPM Evolve Functions " describes the functions related to SQL plan evolution.

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM and DBMS_XPLAN packages

29.1.2 Basic Tasks in SQL Plan Management

This topic explains the basic tasks in using SQL plan management to prevent performance regressions and enable the optimizer to consider new execution plans.

The tasks are as follows:

Set initialization parameters to control whether the database captures and uses SQL plan baselines, and whether it evolves new plans.

See " Configuring SQL Plan Management " .

Display plans in a SQL plan baseline.

See " Displaying Plans in a SQL Plan Baseline " .

Manually load plans into SQL plan baselines.

Load plans from AWR, SQL tuning sets, the shared SQL area, a staging table, or stored outlines.

See " Loading SQL Plan Baselines " .

Manually evolve plans into SQL plan baselines.

Use PL/SQL to verify the performance of specified plans and add them to plan baselines.

See " Evolving SQL Plan Baselines Manually " .

Drop all or some plans in SQL plan baselines.

See " Dropping SQL Plan Baselines " .

Manage the SMB.

Alter disk space limits and change the length of the plan retention policy.

See " Managing the SQL Management Base " .

Migrate stored outlines to SQL plan baselines.

See " Migrating Stored Outlines to SQL Plan Baselines " .

29.2 Configuring the Capture and Use of SQL Plan Baselines

You control SQL plan management with the initialization parameters OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and OPTIMIZER_USE_SQL_PLAN_BASELINES .

The default values are as follows:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false

For any repeatable SQL statement that does not already exist in the plan history, the database does not automatically create an initial SQL plan baseline for the statement.

If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true , then you can use the DBMS_SPM.CONFIGURE procedure to configure filters that determine which statements are eligible for plan capture. By default, no filters are configured, which means that all repeatable statements are eligible for plan capture.

OPTIMIZER_USE_SQL_PLAN_BASELINES=true

For any SQL statement that has an existing SQL plan baseline, the database automatically adds new plans to the SQL plan baseline as unaccepted plans.

The settings of the preceding parameters are independent of each other. For example, if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is true , then the database creates initial plan baselines for new statements even if OPTIMIZER_USE_SQL_PLAN_BASELINES is false .

If the default behavior is what you intend, then skip this section.

The following sections explain how to change the default parameter settings from the command line. If you use Cloud Control, then set these parameters in the SQL Plan Baseline subpage.

" Figure 28-1 "

" Automatic Initial Plan Capture "

" Plan Selection "

29.2.1 Enabling Automatic Initial Plan Capture for SQL Plan Management

Setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to true is all that is necessary for the database to automatically create an initial SQL plan baseline for any eligible SQL statement not already in the plan history.

By default, the database considers all repeatable SQL statements as eligible for capture, with the following exceptions:

CREATE TABLE when the AS SELECT clause is not specified

INSERT INTO ... VALUES

By default, when automatic baseline capture is enabled, the database creates a SQL plan baseline for every eligible repeatable statement, including all recursive SQL and monitoring SQL. Thus, automatic capture may result in the creation of an extremely large number of plan baselines. To limit the statements that are eligible for plan baselines, configure filters using the DBMS_SPM.CONFIGURE procedure.

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter does not control the automatic addition of newly discovered plans to a previously created SQL plan baseline.

To enable automatic initial plan capture for SQL plan management:

In SQL*Plus, log in to the database with the necessary privileges.

Show the current settings for SQL plan management.

For example, connect SQL*Plus to the database with administrator privileges and execute the following command (sample output included):

The following sample output shows that automatic initial plan capture is disabled:

If the parameters are set as you intend, then skip the remaining steps.

To enable the automatic recognition of repeatable SQL statements and the generation of SQL plan baselines for these statements, enter the following statement:

Oracle Database Reference to learn more about OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

29.2.2 Configuring Filters for Automatic Plan Capture

If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true , then you can use the DBMS_SPM.CONFIGURE procedure to create an automatic capture filter for repeatable statements.

An automatic filter enables you to capture only statements that you want, and exclude noncritical statements. This technique saves space in the SYSAUX tablespace.

The following table describes the relevant parameters of the DBMS_SPM.CONFIGURE procedure.

Table 28-2 DBMS_SPM.CONFIGURE Parameters

You can configure multiple parameters of different types. Also, you can specify multiple values for the same parameter in separate statements, which the database combines. The settings are additive: one parameter setting does not override a previous setting. For example, the following filter captures SQL in the parsing schema SYS or SYSTEM :

However, you cannot configure multiple values for the same parameter in the same procedure. For example, you cannot specify multiple SQL text strings for AUTO_CAPTURE_SQL_TEXT .

The DBA_SQL_MANAGEMENT_CONFIG view shows the current parameter values.

This tutorial assumes the following:

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter is set to true .

You want to include only statements parsed in the sh schema to be eligible for baselines.

You want to exclude statements that contain the text TEST_ONLY .

To filter out all statements except those parsed in the sh schema:

Connect SQL*Plus to the database with the appropriate privileges.

To remove any existing filters for parsing schema and SQL text, execute the following PL/SQL programs:

Include only statements parsed in the sh schema for consideration for automatic capture:

Exclude any statement that contains the text TEST_ONLY from consideration for automatic capture:

Optionally, to confirm the filters, query DBA_SQL_MANAGEMENT_CONFIG .

For example, use the following query (sample output included):

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM.CONFIGURE procedure

Oracle Database Reference to learn more about the DBA_SQL_MANAGEMENT_CONFIG view

29.2.3 Disabling All SQL Plan Baselines

When you set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to false , the database does not use any plan baselines in the database.

Typically, you might want to disable one or two plan baselines, but not all of them. A possible use case might be testing the benefits of SQL plan management.

To disable all SQL plan baselines in the database:

Connect SQL*Plus to the database with the appropriate privileges, and then show the current settings for SQL plan management.

To ignore all existing plan baselines enter the following statement:

Oracle Database Reference to learn about the SQL plan baseline initialization parameters

29.3 Configuring SQL Plan Management

You can configure the capture and use of SQL plan baselines, and the SPM Evolve Advisor task.

29.3.1 Managing the SPM Evolve Advisor Task

SPM Evolve Advisor is a SQL advisor that evolves plans that have recently been added to the SQL plan baseline. The advisor simplifies plan evolution by eliminating the requirement to do it manually.

29.3.1.1 About the SPM Evolve Advisor Task

By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window

The SPM Evolve Advisor task perform the following operations:

Locates unaccepted plans

Ranks all unaccepted plans

Performs test executions of as many plans as possible during the maintenance window

Selects the lowest-cost plan to compare against each unaccepted plan

Accepts automatically any unaccepted plan that performs sufficiently better, using a cost-based algorithm, than the existing accepted plan

The SPM Evolve Advisor task can accept more than one plan for a single statement.

SPM Evolve Advisor locates unaccepted plans that the optimizer added to the SMB during earlier hard parses. In some cases, more optimal plans may reside in other locations. By using the DBMS_SPM.SET_EVOLVE_TASK_PARAMETER procedure, you can configure the automatic task to search the shared SQL area, AWR repository, or SQL tuning sets for plans that are not yet in the SMB plan history. If the advisor finds eligible plans in alternative locations, then it includes them along with the other unaccepted plans. By default, the automatic task does not search alternative locations.

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM.SET_EVOLVE_TASK_PARAMETER procedure

29.3.1.2 Enabling and Disabling the Automatic SPM Evolve Advisor Task

No separate scheduler client exists for the Automatic SPM Evolve Advisor task.

One client controls both Automatic SQL Tuning Advisor and Automatic SPM Evolve Advisor. Thus, the same task enables or disables both. You can also disable it using DBMS_SPM.SET_EVOLVE_TASK_PARAMETER .

To disable the Automatic SPM Evolve Advisor task:

Log in to the database with the appropriate privileges.

Set the ALTERNATE_PLAN_BASELINE parameter to null:

Set the ALTERNATE_PLAN_SOURCE parameter to an empty string:

" Enabling and Disabling the Automatic SQL Tuning Task " to learn how to enable and disable Automatic SPM Evolve Advisor

29.3.1.3 Configuring the Automatic SPM Evolve Advisor Task

Configure automatic plan evolution by using the DBMS_SPM package.

Overview of the Automatic SPM Evolve Advisor Task

Specify the automatic task parameters using the SET_EVOLVE_TASK_PARAMETER procedure. The following table describes some procedure parameters.

Table 28-3 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER Parameters

See Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services

Assumptions

The tutorial in this section assumes the following:

You can log in to the database as SYS . Because the SYS_AUTO_SPM_EVOLVE_TASK task is owned by SYS , only SYS can set task parameters.

You want the database to accept plans automatically.

You want the task to time out after 1200 seconds per execution.

You want the evolve task to look for up to a maximum of 500 plans in the shared SQL area and AWR repository

To set automatic evolution task parameters:

Start SQL*Plus, and then log in to the database as SYS .

Query the current parameter settings for SYS_AUTO_SPM_EVOLVE_TASK .

For example, connect SQL*Plus to the database with administrator privileges and execute the following query:

Sample output appears as follows:

Set parameters using PL/SQL code of the following form:

For example, the following PL/SQL block configures the SYS_AUTO_SPM_EVOLVE_TASK task to automatically accept plans, seek up a maximum of 500 plans in the shared SQL area and AWR repository, and time out after 20 minutes:

Optionally, confirm your changes by querying the current parameter settings for SYS_AUTO_SPM_EVOLVE_TASK .

For example, execute the following query:

Oracle Database PL/SQL Packages and Types Reference for complete reference information for DBMS_SPM.SET_EVOLVE_TASK_PARAMETER

Oracle Database Reference to learn more about the DBA_ADVISOR_PARAMETERS view

29.3.2 Automatic SQL Plan Management

A SQL plan baseline prevents performance regressions caused by suboptimal plans.

The High Frequency SPM Evolve Advisor Task can be configured to identify execution plan changes and compare the performance of the new plan with plans previously captured in the automatic SQL tuning set.

If a SQL statement does not have a SQL plan baseline, then the High Frequency SPM Evolve Advisor Task can sometimes resolve such performance regressions automatically. The advisor compares all available plans and chooses the best-performing plan as the baseline.

There are two modes of plan performance verification. In background-verification mode, the high frequency SPM evolve task does the verification. In real-time mode, plan performance verification is done by the foreground process immediately.

The SPM configuration parameter AUTO_SPM_EVOLVE_TASK controls Automatic SQL Plan Management. The setting ON is for background verification (using the task) and AUTO is for Real-Time SQL Plan Management.

The following figure shows the workflow for the High Frequency SPM Evolve Advisor Task in background verification mode:

Figure 28-2 High Frequency SPM Evolve Advisor Task

Whenever it runs, the High Frequency SPM Evolve Advisor Task performs the following tasks:

Searches AWR and ASTS for resource-intensive SQL statements.

Looks for alternative plans in the automatic SQL tuning set.

If multiple plans are identified, they are added to the SQL plan history.

Test executes and measures the performance of the plans alternatives.

The database test executes the statement and records the performance statistics.

Performs either of the following actions, depending on whether the alternative plan performs better than the current plan:

If performance is better, then High Frequency SPM Evolve Advisor accepts the plan. The alternative plan is now in the baseline.

If performance is worse, then the plan remains in the statement history, but not the baseline.

• The Automatic SQL Tuning Set , the system-maintained record of SQL execution plans and SQL statement performance metrics seen by the database. The High Frequency SPM Evolve Advisor Task compares the performance of a new plan with plans previously captured in the automatic SQL tuning set.

Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services

29.3.2.1 Configuring Automatic SQL Plan Management

The High-Frequency SPM Evolve Advisor task complements the standard Automatic SPM Evolve Advisor task and is a component of automatic SQL Plan Management.

This task runs periodically throughout the day.

Both the standard Automatic SPM Evolve Advisor task and high-frequency task have the same name: SYS_AUTO_SPM_EVOLVE_TASK . In DBA_ADVISOR_EXECUTIONS , the two tasks are distinguished by execution name. The name of the standard task execution has the form EXEC_ number , whereas the name of the high-frequency execution has the form SYS_SPM_ timestamp .

Either real-time SPM or SPM (with background-verification) is enabled by DBMS_SPM.CONFIGURE to set AUTO SPM_EVOLVE_TASK to ON (background) or AUTO (real-time).

DBMS_SPM.CONFIGURE enables the high-frequency task, but has no dependency on the SPM Evolve Advisor. The standard task and high-frequency task are independent and are scheduled through two different frameworks.

• unresolvable-reference.html#GUID-646613B4-F2E5-4BCD-BCD3-8982EC5D3FA2

See DBMS_SPM.CONFIGURE in the Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM.CONFIGURE procedure.

29.3.2.2 Enabling Automatic SQL Plan Management: Tutorial

To enable and disable automatic SQL plan management (automatic SPM), use the DBMS_SPM.CONFIGURE procedure.

You can set auto_spm_evolve_task to any of the following values:

ON — Enables automatic SPM and the High-Frequency SPM Evolve Advisor Task with background-verified Automatic SPM.

OFF — Disables automatic SPM and the High-Frequency SPM Evolve Advisor task. This is the default.

AUTO — Enables Automatic APM in real-time mode (running in the foreground).

Note that the task interval and run-time are fixed and cannot be adjusted by the user.

To enable the high-frequency SPM Evolve Advisor task:

Query the current setting for DBMS_SPM.CONFIGURE (sample output included):

Enable the task.

Execute the following PL/SQL code:

To confirm that the task is enabled, query the current setting for AUTO_SPM_EVOLVE_TASK (sample output included):

Optionally, wait a few hours, and then query the status of the task executions:

In the preceding output, EXEC_6 is the execution name of the standard SPM Automatic Advisor task. The other executions are of the high-frequency task.

29.3.2.3 Monitoring and Reporting in Automatic SQL Plan Management

SQL plan baselines are shown in the view DBA_SQL_PLAN_BASELINES . The DBA_SQL_PLAN_BASELINES.ORIGIN column in this view displays FOREGROUND-CAPTURE .

29.4 Displaying Plans in a SQL Plan Baseline

To view the plans stored in the SQL plan baseline for a specific statement, use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function.

This function uses plan information stored in the plan history to display the plans. The following table describes the relevant parameters for DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE .

Table 28-4 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE Parameters

This section explains how to show plans in a baseline from the command line. If you use Cloud Control, then display plan baselines from the SQL Plan Baseline subpage shown in Figure 28-1 .

To display plans in a SQL plan baselines:

Connect SQL*Plus to the database with the appropriate privileges, and then obtain the SQL ID of the query whose plan you want to display.

For example, assume that a SQL plan baseline exists for a SELECT statement with the SQL ID 31d96zzzpcys9 .

Query the plan by SQL ID.

The following query displays execution plans for the statement with the SQL ID 31d96zzzpcys9 :

The sample query results are as follows:

The results show that the plan for SQL ID 31d96zzzpcys is named SQL_PLAN_52gvzja8jfysuc0e983c6 and was captured automatically.

" SQL Management Base "

Oracle Database PL/SQL Packages and Types Reference to learn about additional parameters used by the DISPLAY_SQL_PLAN_BASELINE function

29.5 Loading SQL Plan Baselines

Using DBMS_SPM , you can initiate the bulk load of a set of existing plans into a SQL plan baseline.

29.5.1 About Loading SQL Plan Baselines

The DBMS_SPM package enables you to load plans from multiple sources.

The goal of this task is to load plans from the following sources:

Load plans from Automatic Workload Repository (AWR) snapshots. You must specify the beginning and ending of the snapshot range. Optionally, you can apply a filter to load only plan that meet specified criteria. By default, the optimizer uses the loaded plans the next time that the database executes the SQL statements.

Shared SQL area

Load plans for statements directly from the shared SQL area, which is in the shared pool of the SGA. By applying a filter on the module name, the schema, or the SQL ID you identify the SQL statement or set of SQL statements to capture. The optimizer uses the plans the next time that the database executes the SQL statements.

Loading plans directly from the shared SQL area is useful when application SQL has been hand-tuned using hints. Because you probably cannot change the SQL to include the hint, populating the SQL plan baseline ensures that the application SQL uses optimal plans.

SQL tuning set (STS)

Capture the plans for a SQL workload into an STS, and then load the plans into the SQL plan baselines. The optimizer uses the plans the next time that the database executes the SQL statements. Bulk loading execution plans from an STS is an effective way to prevent plan regressions after a database upgrade.

Staging table

Use the DBMS_SPM package to define a staging table, DBMS_SPM.PACK_STGTAB_BASELINE to copy the baselines into a staging table, and Oracle Data Pump to transfer the table to another database. On the destination database, use DBMS_SPM.UNPACK_STGTAB_BASELINE to unpack the plans from the staging table and put the baselines into the SMB.

A use case is the introduction of new SQL statements into the database from a new application module. A vendor can ship application software with SQL plan baselines for the new SQL. In this way, the new SQL uses plans that are known to give optimal performance under a standard test configuration. Alternatively, if you develop or test an application in-house, export the correct plans from the test database and import them into the production database.

Stored outline

Migrate stored outlines to SQL plan baselines. After the migration, you maintain the same plan stability that you had using stored outlines while being able to use the more advanced features provided by SQL Plan Management, such as plan evolution. See .

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM.PACK_STGTAB_BASELINE Function

29.5.2 Loading Plans from AWR

This topic explains how to load plans from AWR using PL/SQL.

Load plans with the LOAD_PLANS_FROM_AWR function of the DBMS_SPM package. The following table describes some function parameters.

Table 28-5 LOAD_PLANS_FROM_AWR Parameters

This section explains how to load plans using the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 28-1 ) and click Load to load plan baselines from AWR.

You want to load plans for the following query into the SMB:

You want the loaded plans to be nonfixed.

The user sh has privileges to query DBA_HIST_SNAPSHOT and DBA_SQL_PLAN_BASELINES , execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT , and execute DBMS_SPM.LOAD_PLANS_FROM_AWR .

To load plans from the shared SQL area:

Log in to the database with the appropriate privileges, and then query the most recent 3 AWR snapshots.

For example, query DBA_HIST_SNAPSHOT as follows:

Query sh.sales , using the LOAD_AWR tag to identify the SQL statement.

For example, use the following query:

Take a new AWR snapshot.

For example, use the following program:

Query the most recent 3 AWR snapshots to confirm that a new snapshot was taken.

Load the plans for the most recent 2 snapshots from AWR.

For example, execute the LOAD_PLANS_FROM_AWR function in SQL*Plus to load the plans from snapshot 212 to 213 :

In the preceding example, the variable v_plan_cnt contains the number of plans that were loaded.

Query the data dictionary to ensure that the plans were loaded into the baseline for the LOAD_AWR statement.

The following statement queries DBA_SQL_PLAN_BASELINES (sample output included):

The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is MANUAL-LOAD-FROM-AWR , which means that the statement was loaded manually from AWR rather than automatically captured.

" Fixed Plans "

Oracle Database PL/SQL Packages and Types Reference to learn how to use the DBMS_SPM.LOAD_PLANS_FROM_AWR function

Oracle Database Reference to learn more about the DBA_SQL_PLAN_BASELINES view

29.5.3 Loading Plans from the Shared SQL Area

This topic explains how to load plans from the shared SQL area, also called the cursor cache, using PL/SQL.

Load plans with the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. The following table describes some function parameters.

Table 28-6 LOAD_PLANS_FROM_CURSOR_CACHE Parameters

This section explains how to load plans using the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 28-1 ) and click Load to load plan baselines from the shared SQL area.

You have executed the following query:

Connect SQL*Plus to the database with the appropriate privileges, and then determine the SQL IDs of the relevant statements in the shared SQL area.

For example, query V$SQL for the SQL ID of the sh.sales query (sample output included):

The preceding output shows that the SQL ID of the statement is 27m0sdw9snw59 .

Load the plans for the specified statements into the SQL plan baseline.

For example, execute the LOAD_PLANS_FROM_CURSOR_CACHE function in SQL*Plus to load the plan for the statement with the SQL ID 27m0sdw9snw59 :

Query the data dictionary to ensure that the plans were loaded into the baseline for the statement.

The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is MANUAL-LOAD-FROM-CC , which means that the statement was loaded manually from the shared SQL area rather than automatically captured.

Oracle Database PL/SQL Packages and Types Reference to learn how to use the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function

29.5.4 Loading Plans from a SQL Tuning Set

A SQL tuning set (STS) is a database object that includes one or more SQL statements, execution statistics, and execution context. This topic explains how to load plans from an STS.

Load plans with the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function or using Cloud Control. The following table describes some function parameters.

Table 28-7 LOAD_PLANS_FROM_SQLSET Parameters

This section explains how to load plans from the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 28-1 ) and click Load to load plan baselines from SQL tuning sets.

You have loaded the plan from the shared SQL area into the SQL tuning set named SPM_STS , which is owned by user SPM .

After the operation, you want to drop the STS using DBMS_SQLTUNE.DROP_SQLSET rather than the equivalent D BMS_SQLSET.DROP_SQLSET .

To load plans from a SQL tuning set:

Connect SQL*Plus to the database with the appropriate privileges, and then verify which plans are in the SQL tuning set.

For example, query DBA_SQLSET_STATEMENTS for the STS name (sample output included):

The output shows that the plan for the select /*LOAD_STS*/ statement is in the STS.

Load the plan from the STS into the SQL plan baseline.

For example, in SQL*Plus execute the function as follows:

The basic_filter parameter specifies a WHERE clause that loads only the plans for the queries of interest. The variable v_plan_cnt stores the number of plans loaded from the STS.

Query the data dictionary to ensure that the plan was loaded into the baseline for the statement.

The following statement queries the DBA_SQL_PLAN_BASELINES view (sample output included).

The output shows that the plan is accepted, which means that it is in the plan baseline. Also, the origin is MANUAL-LOAD-FROM-STS , which means that the plan was loaded manually from a SQL tuning set rather than automatically captured.

Optionally, drop the STS.

For example, execute DBMS_SQLTUNE.DROP_SQLSET to drop the SPM_STS tuning set as follows:

" Command-Line Interface to SQL Tuning Sets "

Oracle Database Reference to learn about the DBA_SQL_PLAN_BASELINES view

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function

29.5.5 Loading Plans from a Staging Table

You may want to transfer optimal plans from a source database to a different destination database.

For example, you may have investigated a set of plans on a test database and confirmed that they have performed well. You may then want to load these plans into a production database.

A staging table is a table that, for the duration of its existence, stores plans so that the plans do not disappear from the table while you are unpacking them. Use the DBMS_SPM.CREATE_STGTAB_BASELINE procedure to create a staging table. To pack (insert row into) and unpack (extract rows from) the staging table, use the PACK_STGTAB_BASELINE and UNPACK_STGTAB_BASELINE functions of the DBMS_SPM package. Oracle Data Pump Import and Export enable you to copy the staging table to a different database.

Figure 28-3 Loading Plans from a Staging Table

Export plans with the PACK_STGTAB_BASELINE function of the DBMS_SPM package, and then import them with UNPACK_STGTAB_BASELINE . The following table describes some function parameters.

Table 28-8 PACK_STGTAB_BASELINE and UNPACK_STGTAB_BASELINE Parameters

You want to create a staging table named stage1 in the source database.

You want to load all plans owned by user spm into the staging table.

You want to transfer the staging table to a destination database.

You want to load the plans in stage1 as fixed plans.

To transfer a set of SQL plan baselines from one database to another:

Connect SQL*Plus to the source database with the appropriate privileges, and then create a staging table using the CREATE_STGTAB_BASELINE procedure.

The following example creates a staging table named stage1 :

On the source database, pack the SQL plan baselines you want to export from the SQL management base into the staging table.

The following example packs enabled plan baselines created by user spm into staging table stage1 . Select SQL plan baselines using the plan name ( plan_name ), SQL handle ( sql_handle ), or any other plan criteria. The table_name parameter is mandatory.

Export the staging table stage1 into a dump file using Oracle Data Pump Export.

Transfer the dump file to the host of the destination database.

On the destination database, import the staging table stage1 from the dump file using the Oracle Data Pump Import utility.

On the destination database, unpack the SQL plan baselines from the staging table into the SQL management base.

The following example unpacks all fixed plan baselines stored in the staging table stage1 :

Oracle Database PL/SQL Packages and Types Reference for more information about using the DBMS_SPM package

Oracle Database Utilities for detailed information about using the Data Pump Export and Import utilities

29.6 Evolving SQL Plan Baselines Manually

You can use PL/SQL or Cloud Control to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline.

29.6.1 About the DBMS_SPM Evolve Functions

This topic describes the most relevant DBMS_SPM functions for managing plan evolution. Execute evolution tasks manually or schedule them to run automatically.

Table 28-9 DBMS_SPM Functions and Procedures for Managing Plan Evolution Tasks

Oracle recommends that you configure SPM Evolve Advisor to run automatically. You can also evolve SQL plan baselines manually. The following graphic shows the basic workflow for managing SQL plan management tasks.

Figure 28-4 Evolving SQL Plan Baselines

Typically, you manage SQL plan evolution tasks in the following sequence:

Create an evolve task

Optionally, set evolve task parameters

Execute the evolve task

Implement the recommendations in the task

Report on the task outcome

" Configuring the Automatic SPM Evolve Advisor Task " to learn about SET_EVOLVE_TASK_PARAMETER

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SPM package

29.6.2 Managing an Evolve Task

This topic describes a typical use case in which you create and execute a task, and then implement its recommendations.

The following table describes some parameters of the CREATE_EVOLVE_TASK function.

Table 28-10 DBMS_SPM.CREATE_EVOLVE_TASK Parameters

This section explains how to evolve plan baselines from the command line. In Cloud Control, from the SQL Plan Baseline subpage, select a plan, and then click Evolve .

You do not have the automatic evolve task enabled.

You want to create a SQL plan baseline for the following query:

You want to create two indexes to improve the query performance, and then evolve the plan that uses these indexes if it performs better than the plan currently in the plan baseline.

To evolve a specified plan:

Perform the initial setup as follows:

Connect SQL*Plus to the database with administrator privileges, and then prepare for the tutorial by flushing the shared pool and the buffer cache:

Enable the automatic capture of SQL plan baselines.

For example, enter the following statement:

Connect to the database as user sh , and then set SQL*Plus display parameters:

Execute the SELECT statements so that SQL plan management captures them:

Execute the SELECT /* q1_group_by */ statement for the first time.

Because the database only captures plans for repeatable statements, the plan baseline for this statement is empty.

Query the data dictionary to confirm that no plans exist in the plan baseline.

For example, execute the following query (sample output included):

SQL plan management only captures repeatable statements, so this result is expected.

Execute the SELECT /* q1_group_by */ statement for the second time.

Query the data dictionary to ensure that the plans were loaded into the plan baseline for the statement.

The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is AUTO-CAPTURE , which means that the statement was automatically captured and not manually loaded.

Explain the plan for the statement and verify that the optimizer is using this plan.

For example, explain the plan as follows, and then display it:

Sample output appears below:

The note indicates that the optimizer is using the plan shown with the plan name listed in the previous step.

Create two indexes to improve the performance of the SELECT /* q1_group_by */ statement.

For example, use the following statements:

Execute the select /* q1_group_by */ statement again.

Because automatic capture is enabled, the plan baseline is populated with the new plan for this statement.

Query the data dictionary to ensure that the plan was loaded into the SQL plan baseline for the statement.

The following statement queries DBA_SQL_PLAN_BASELINES (sample output included).

The output shows that the new plan is unaccepted, which means that it is in the statement history but not the SQL plan baseline.

Explain the plan for the statement and verify that the optimizer is using the original unindexed plan.

The note indicates that the optimizer is using the plan shown with the plan name listed in Step 3.

Connect as an administrator, and then create an evolve task that considers all SQL statements with unaccepted plans.

For example, execute the DBMS_SPM.CREATE_EVOLVE_TASK function and then obtain the name of the task:

The following sample output shows the name of the task:

Now that the task has been created and has a unique name, execute the task.

Execute the task.

For example, execute the DBMS_SPM.EXECUTE_EVOLVE_TASK function (sample output included):

View the report.

For example, execute the DBMS_SPM.REPORT_EVOLVE_TASK function (sample output included):

This report indicates that the new execution plan, which uses the two new indexes, performs better than the original plan.

Implement the recommendations of the evolve task.

For example, execute the IMPLEMENT_EVOLVE_TASK function:

Query the data dictionary to ensure that the new plan is accepted.

The query provides the following sample output:

The output shows that the new plan is accepted.

Clean up after the example.

For example, enter the following statements:

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM evolve functions

29.7 Adding SQL Plans to a Baseline

You can use DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE to add SQL plans to a baseline.

This function fetches all existing plans for a given SQL ID. It chooses the best performing plans and accepts them. The sources may include the Cursor Cache, Automatic Workload Repository, and Auto SQL Tuning Set ( SYS_AUTO_STS ).

These are the steps that DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE executes:

• Loads plans from all sources into SQL Plan Management SQL plan history in a non-accepted state.
• Uses the SQL Plan Management Evolve Advisor internally to identify the best-performing execution plans. The best-performing plans are accepted.

When the function has completed, it will have accepted the best plans among the plans available from the Cursor Cache, AWR, and the Automatic SQL Tuning Set.

Table 28-11 ADD_VERIFIED_SQL_PLAN_BASELINE Function Parameters

29.8 Dropping SQL Plan Baselines

You can remove some or all plans from a SQL plan baseline. This technique is sometimes useful when testing SQL plan management.

Drop plans with the DBMS_SPM.DROP_SQL_PLAN_BASELINE function. This function returns the number of dropped plans. The following table describes input parameters.

Table 28-12 DROP_SQL_PLAN_BASELINE Parameters

This section explains how to drop baselines from the command line. In Cloud Control, from the SQL Plan Baseline subpage, select a plan, and then click Drop .

This tutorial assumes that you want to drop all plans for the following SQL statement, effectively dropping the SQL plan baseline:

To drop a SQL plan baseline:

Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary for the plan baseline.

Drop the SQL plan baseline for the statement.

The following example drops the plan baseline with the SQL handle SQL_b6b0d1c71cd1807b , and returns the number of dropped plans. Specify plan baselines using the plan name ( plan_name ), SQL handle ( sql_handle ), or any other plan criteria. The table_name parameter is mandatory.

Confirm that the plans were dropped.

Oracle Database PL/SQL Packages and Types Reference to learn about the DROP_SQL_PLAN_BASELINE function

29.9 Managing the SQL Management Base

The SQL management base is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

29.9.1 About Managing the SMB

Use the DBMS_SPM.CONFIGURE procedure to set configuration options for the SMB and the maintenance of SQL plan baselines.

The DBA_SQL_MANAGEMENT_CONFIG view shows the current configuration settings for the SMB. The following table describes the parameters in the PARAMETER_NAME column.

Table 28-13 Parameters in DBA_SQL_MANAGEMENT_CONFIG.PARAMETER_NAME

" Eligibility for Automatic Initial Plan Capture "

Oracle Database Reference to learn more about DBA_SQL_MANAGEMENT_CONFIG

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_SPM.CONFIGURE

29.9.2 Changing the Disk Space Limit for the SMB

A weekly background process measures the total space occupied by the SMB.

When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until either the SMB space limit is increased, the size of the SYSAUX tablespace is increased, or the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles). This task explains how to change the limit with the DBMS_SPM.CONFIGURE procedure.

The current SMB space limit is the default of 10%.

You want to change the percentage limit to 30%

To change the percentage limit of the SMB:

Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary to see the current space budget percent.

Change the percentage setting.

For example, execute the following command to change the setting to 30%:

Query the data dictionary to confirm the change.

For example, execute the following join (sample output included):

29.9.3 Changing the Plan Retention Policy in the SMB

A weekly scheduled purging task manages disk space used by SQL plan management.

The task runs as an automated task in the maintenance window. The database purges plans that have not been used for longer than the plan retention period, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The default retention period is 53 weeks. The period can range between 5 and 523 weeks.

This task explains how to change the plan retention period with the DBMS_SPM.CONFIGURE procedure. In Cloud Control, set the plan retention policy in the SQL Plan Baseline subpage (shown in Figure 28-1 ).

To change the plan retention period for the SMB:

Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary to see the current plan retention period.

Change the retention period.

For example, execute the CONFIGURE procedure to change the period to 105 weeks:

Oracle Database PL/SQL Packages and Types Reference to learn more about the CONFIGURE procedure