Alerting on plans that change for the worse

I’ve uploaded a monitoring script that I have worked on: zip.

The script alerts you when the optimizer runs a SQL statement with a potentially new and inefficient plan so you can intervene.  This script improves upon my earlier script which only alerts you to SQL statements running with new plans.  The new script compares the average elapsed time of the current plan with the average of the most often executed plan.  If the new plan averages more than ten times the most often executed plan then the script alerts you to a possible new slow plan.  The elapsed time for the current plan comes from V$SQL and the time for the most often executed plan comes from DBA_HIST_SQLSTAT.

Here is an example output from the first test case:

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
a1fw5xjcwkjqx      1357081020

There are two test cases included in the script.  The first test case has a query which uses an index and the plan changes when I drop the index.

If you look at the output for the first test case you see the good plan:

SQL_ID  a1fw5xjcwkjqx, child number 0
-------------------------------------
select /* 9 */ owner,table_name from test where owner='XYZ' and
table_name='XYZ'

Plan hash value: 268773832

---------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    58 (100)|
|*  1 |  INDEX RANGE SCAN| TESTI | 16222 |   221K|    58   (0)|
---------------------------------------------------------------

The good plan uses the index.  Here is the bad plan.  Note how the sql_id and plan_hash_value correspond to the output of the monitor script.

SQL_ID  a1fw5xjcwkjqx, child number 0
-------------------------------------
select /* 9 */ owner,table_name from test where owner='XYZ' and
table_name='XYZ'

Plan hash value: 1357081020

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 15708 (100)|
|*  1 |  TABLE ACCESS FULL| TEST |  8111 |   110K| 15708   (1)|
---------------------------------------------------------------

I have this running in a cron job on a development server and I plan to put the script into our production server next week and set it up to alert me with an email when the optimizer runs SQL statements with potentially  new and inefficient plans.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

13 Responses to Alerting on plans that change for the worse

  1. Ashwani says:

    Hi Bobby,

    could your share shell(sh/ksh) script which we need to schdule in cron ?, actually am looking script which will trigger email when plan change for any sql_id, if no change then no email

    • Bobby says:

      I cannot share the entire script for security reasons. I took out the environment variables and paths. But here is as much of my script as I can share:

      Bobby

      • Anonymous says:

        Thanks bobby for the brilliant scripts. Can we use the script directly?
        We are not allowed to create any table in prod but we can use centralised server to create table using data via dblink and feed it

        In the script do we need to add ? As you said its portion of scripts correct ?

  2. Pingback: December 26 Plan Change | Bobby Durrett's DBA Blog

  3. Anonymous says:

    Thanks

  4. Murugan says:

    Bobby ,
    Thanks for the script. May i know why there is discrepancies with respect to monitor query above and zip file ?

    • Bobby says:

      The zip file contains a sqlplus script that I wrote do monitor for SQL plan changes. The shell script listed above is that same script converted to run as a shell script on HP Unix. The logic is the same. I try to find queries that are running a plan that is taking 10 times longer than the best plan it ran in the past.

  5. Krish says:

    Thanks for the scripts. Why there is discrepancies with scripts?
    Zip file scripts only taking live sql however posted scripts filtering and taking only specific days of interest.

    • Bobby says:

      The shell script which I posted in a comment in 2021 is based on the real script I ran as of that date. The original zip was from 2014 which I had just written but did not have experience using it in production. Since I have been using this in production for several years I have added filters to make it more useful by eliminating false positives. If someone wants to use it, try it without the filters and then add your own if you see it alert on plan changes that do not matter. What is interesting is that plans change all the time. I had no idea the system was constantly changing in this way until I started running this monitoring script.

      Bobby

  6. Krish says:

    Thanks. Do you have any scripts after identifying plan flip it will invoke coe_xfr_sql_profile automatically and pin the best plan with out manual intervention?

    • Bobby says:

      No. I do not want to make a change in production without first evaluating the plan change. There are some queries that have to change plans based on the values passed to them in bind variables. I just use the script for alerting. I’m not comfortable just letting is put in the profile automatically.

      Thanks for the question. It is a good one and I have thought about it but so far have rejected automatically putting in the profile.

      Bobby

  7. Krish says:

    Hi Bobby,
    Thank you for the response. Appreciate it.

    We are trying to target few databases where literals are in play, unfortunately application code can’t be modified. Thus causes plan flip which can be fixed with force_match=true using CoE script. However quite often they are coming up with different queries with literals and some times profiles are getting obsolete due to changes in underlying object or statistics. To arrest plan flip we are trying to automate this CoE. So thought of checking with you. Ideas and inputs are greatly appreciated. Anyway once again thanks for your stunning blog post and supporting oracle community. Keep hustling and doing awesome job.

    You are one of my inspirations. Especially yours PT skills and python scripts are outstanding. Kudos to you.

Leave a Reply to Krish Cancel reply

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

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