Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Scripts to deal with SQL Plan Baselines, SQL Profiles and SQL Patches

with 6 comments

To mitigate SQL performance issues, I do make use of SQL Plan Baselines, SQL Profiles and SQL Patches, on a daily basis. Our environments are single-instance 12.1.0.2 CDBs, with over 2,000 PDBs. Our goal is Execution Plan Stability and consistent performance, over CBO plan flexibility. The CBO does a good job, considering the complexity imposed by current applications design. Nevertheless, some SQL require some help in order to enhance their plan stability.

I have written and shared a set of scripts that simply make the use of a bunch of APIs a lot easier, with better documented actions, and fully consistent within the organization. I have shared with the community these scripts in the past, and I keep them updated as per needs change. All these “CS” scripts are available under the download section on the right column.

Current version of the CS scripts is more like a toolset. You treat them as a whole. All of them call some other script that exists within the cs_internal subdirectory, then I usually navigate to the parent sql directory, and connect into SQL*Plus from there. All these scripts can be easily cloned and/or customized to your specific needs. They are available as “free to use” and “as is”. There is no requirement to keep their heading intact, so you can reverse-engineer them and make them your own if you want. Just keep in mind that I maintain, enhance, and extend this CS toolset every single day; so what you get today is a subset of what you will get tomorrow. If you think an enhancement you need (or a fix) is beneficial to the larger community (and to you), please let me know.

SQL Plan Baselines scripts

With the set of SQL Plan Baselines scripts, you can: 1) create a baseline based on a cursor or a plan stored into AWR; 2) enable and disable baselines; 3) drop baselines; 4) store them into a local staging table; 5) restore them from their local staging table; 6) promote as “fixed” or demote from “fixed”; 7) “zap” them if you have installed “El Zapper” (iod_spm).

Note: “El Zapper” is a PL/SQL package that extends the functionality of SQL Plan Management by automagically creating SQL Plan Baselines based on proven performance of a SQL statement over time, while considering a large number of executions, and a variety of historical plans. Please do not confuse “El Zapper” with auto-evolve of SPM. They are based on two very distinct premises. “El Zapper” also monitors the performance of active SQL Plan Baselines, and during an observation window it may disable a SQL Plan Baseline, if such plan no longer performs as “promised” (according to some thresholds). Most applications do not need “El Zapper”, since the use of SQL Plan Management should be more of an exception than a rule.

SQL Profiles scripts

With the set of SQL Profiles scripts, you can: 1) create a profile based on the outline of a cursor, or from a plan stored into AWR; 2) enable and disable profiles; 3) drop profiles; 4) store them into a local staging table; 5) restore them from their local staging table; 6) transfer them from one location to another (very similar to coe_xfr_sql_profile.sql, but on a more modular way).

Note: Regarding the transfer of a SQL Profile, the concept is simple: 1) on source location generate two plain text scripts, one that contains the SQL text, and a second that includes the Execution Plan (outline); 2) execute these two scripts on a target location, in order to create a SQL Profile there. The beauty of this method is not only that you can easily move Execution Plans between locations, but that you can actually create a SQL Profile getting the SQL Text from SQL_ID “A”, and the Execution Plan from SQL_ID “B”, allowing you to do things like: removing CBO Hints, or using a plan from a similar SQL but not quite the same (e.g. I can tweak a stand-alone cloned version of a SQL statement, and once I get the plan that I need, I associate the SQL Text from the original SQL, with the desired Execution Plan out of the stand-alone customized version of the SQL, after that I create a SQL Plan Baseline and drop the staging SQL Profile).

SQL Patches scripts

With the set of SQL Patches scripts, you can: 1) create a SQL patch based on one or more CBO Hints you provide (e.g.: GATHER_PLAN_STATISTICS MONITOR FIRST_ROWS(1) OPT_PARAM(‘_fix_control’ ‘5922070:OFF’) NO_BIND_AWARE); 2) enable and disable SQL patches; 3) drop SQL patches; 4) store them into a local staging table; 5) restore them from their local staging table.

Note: I use SQL Patches a lot, specially to embed CBO Hints that generate some desirable diagnostics details (and not so much to change plans), such as the ones provided by GATHER_PLAN_STATISTICS and MONITOR. In some cases, after I use the pathfinder tool written by Mauro Pagano, I have to disable a CBO patch (funny thing: I use a SQL Patch to disable a CBO Patch!). I also use a SQL Patch if I need to enable Adaptive Cursor Sharing (ACS) for one SQL (we disabled ACS for one major application). Bear in mind that SQL Plan Baselines, SQL Profiles and SQL Patches happily co-exist, so you can use them together, but I do prefer to use SQL Plan Baselines alone, whenever possible.

Advertisements

Written by Carlos Sierra

December 15, 2018 at 5:57 am

6 Responses

Subscribe to comments with RSS.

  1. […] Update: Scripts to deal with SQL Plan Baselines, SQL Profiles and SQL Patches […]

  2. which option to use in which scenerio
    i think there are about 5 to 6 options available in oracle to fix plan

    lucky

    December 15, 2018 at 7:28 pm

    • Fixing a plan means: set SQL Plan Management to stop collecting new plans into plan history. Not sure what 5 or 6 options you are referring.

      Carlos Sierra

      December 16, 2018 at 7:09 am

  3. Amazing as always to make DBA life easier. Thanks Carlos.

    Saurabh

    December 16, 2018 at 2:40 pm

  4. Carlos, first of all, thank you very much for all your work regarding SQL profiles, something that I am using every day to tune performance.

    I have a couple of questions, if you have a chance to get back to me:

    1. I am using your coe_xfr_sql_profile.sql script for almost five years now, with force_match => TRUE. This is in our DW where we have CURSOR_SHARING=EXACT. Something strange is happening in our databases. In some of them, single line comments(‘–‘) are changing the force_matching_signatures. On other database single line comments leave the force_matching_signature the same even if literals change.
    All databases are 12.1.0.2. Are there some parameters that might cause that? Or some patches we can apply so that single line comments would no longer change the signature?

    2. I read Metalink note “How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (Doc ID 1253696.1)”, and at the end they make this statement:

    “SQL profiles created with FORCE_MATCH enabled does not recognize the addition of a comment to the SQL statement.
    If a comment is added to the SQL statement, then the SQL profile will not match to it.
    To overcome the problem, Patch 9488694 can be applied so that the comments are ignored and the SQL profiles will match the signature and get used.”

    Is it true that applying patch 9488694 comments will be completely ignored when calculating the force_matching_signature?

    Best Regards

    Klaus

    April 25, 2019 at 10:59 pm

    • I have heard about this issue of ‘–‘ some time back. Reference that you found seems a hit. I do not know the answer for sure, so I recommend you reach out to Support, or test it yourself.

      Carlos Sierra

      April 28, 2019 at 7:38 am


Leave a Reply to Saurabh Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: