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