why is sql monitor not working.....?



Today i've had to look at our nightmarish datawarehouse to try and tune some bad SQL.

Well bad does not really go far enough to be honest - it's a multi layered view with thousands of lines of SQL in total...

Here is the top bit of the execution plan - if this doesn't scare you then you've never had to tune something this big......



So just to be clear it thinks its getting 74P of data (yes petabytes) and the second number ringed is the number of lines in the execution plan (not the SQL - the execution plan) - yes you read it right there are 1053 lines.......

I'm not going to go into the details of how i tuned it (actually mainly because i didn't do that yet....)

What i did want to pick up on though is that this statement was not showing up in sql monitoring - even when i added an explicit monitor hint to it - why was that?

Well a quick trawl through metalink revealed the reason:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1613163.1

To save you going off to read all of that though basically the issue is that anything with more than 300 lines of plan will not be monitored - unless you change the threshold for that

So in my case if i do

alter system set "_sqlmon_max_planlines"=1500;

Then the monitoring kicks in - and gives me at least some more chance of tuning this thing....

As always be careful when setting underscore parameters, with this one the side effects are i guess obvious - it's going to need more resource to track these things so you may not want to increase the default.

The irony is of course that normally it's the biggest ones that you are most interested in the sql monitoring of.....


1 comments: