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
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
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
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 ?
Pingback: December 26 Plan Change | Bobby Durrett's DBA Blog
Thanks
Bobby ,
Thanks for the script. May i know why there is discrepancies with respect to monitor query above and zip file ?
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.
Thanks
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.
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
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?
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
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.