Slow SQL From Monitoring Software

Queries from monitoring software like BMC are not supposed to be among Top SQL, but that was happening in many databases at one site. This may happen if the dictionary statistics are not updated, leading Oracle to choose the wrong plan. Document with ID 1328789.1 on MOS shows an example of this.

So the SQL that came from BMC sometimes took up to a minute and was executed every 5 minutes, easily making it to the Top SQL list in Enterprise Manager / Cloud Control. This is the SQL:

select count(1)
from v$lock
where ctime > 120 and type in ('TM', 'TX', 'UL') and lmode = 0;

The sql_id was 9m8xksjywfy1x (I am curious if people actually google for sql_ids.) Anyway, checking the execution of this in SQL Monitor showed that the rows returned from some operations were much higher than expected by the Cost Based Optimizer (CBO). This reminded me of stale dictionary statistics and the search on MOS led me to the document above. The fix is easy, just execute as SYS:

exec dbms_stats.GATHER_FIXED_OBJECTS_STATS

It can take more than a minute for this to complete, even for a small database.

In one database, the cursor with the wrong plan wouldn’t go away, without figuring out why I followed the instructions in this blog post to remove it.

Find the ADDRESS and HASH_VALUE of the cursor:

select ADDRESS, HASH_VALUE 
from V$SQLAREA 
where SQL_ID like '9m8xksjywfy1x';

ADDRESS          HASH_VALUE
---------------- ----------
00000002D314E160 2110224445

Then execute:

exec  DBMS_SHARED_POOL.PURGE ('00000002D314E160,2110224445','C')

The first argument is ‘ADDRESS,HASH_VALUE’, the second is C for cursor object.

I had to run this on both instances in the RAC. You can add PLAN_HASH_VALUE to the query above in order to see if the plan differs across instances; next time Oracle executes this query a new plan will hopefully be generated, resulting in a new PLAN_HASH_VALUE.

I personally prefer to use Enterprise Manager / Cloud Control from Oracle to monitor databases, since I reckon they know better how to monitor their own stuff. Critical alarms can be forwarded to a site-wide monitoring system like BMC, or whatever you are struggling with.