Buffer busy wait alarm ... false result?

[damir.vadas_250]

Hi

Mine config:
Spotlight version is 10.2.1.1504 x64
Oracle client (used for Spotlight) was 11.2.0.3 x64
Client OS is Windows 7 x64 SP1
Monitored database was Oracle 11.2.0.3 x64 on AIX

Once while presenting Spotlight capabilities during one of mine Toad hotest features, I have situation that Spotlight shows pretty long time buffer busy wait alram.

The bb wait situation lasts for 3-5 minutes and then I decide to took a picture, which is here as live example.

Once again, before this picture was taken BB wait shows red alarm (99%-100% values), and here, when the picture was taken was blue level.

Then I took ASH from the same period to show to users that Spotlight can replace ASH reports. But ... but in generated html report (from the 3 minutes before and after monitored Spotlight pic) there was no sign of mentioned buffer busy wait event at all. I cannot place html report here ( do not know how and do not want to compromise client data!) but please let me get someone mail to send ... not a problem.

I was then asked why is Spotlight showing this (quite often by their words wait event) and this state/problem is not reflected in ASH (which I belieive more, sorry to say that).

So please understand that this may be obvious to you people but not to me and not to mine client, who is waiting for an straight answer-what is underneath of that Spotlight message and how to inter-prate in the future.

THX in advance

Damir

[damir.vadas_250]

Bumps … anyone to get ASH file and tell me where the problem resides?

Cheers,

Damir

[david.robson_843]

Hi Damir,

Spotlight calculates this based off data in v$system_event - basically working out the percentage of buffer busy waits against total waits. It’s unclear exactly what the value is at the time - if you hover over the control it will tell you but this is not the same as the buffer cache hit ratio value which you can see is 98%. The red alarm starts occurring when the buffer busy waits are 11% or more of total waits.

ASH works a bit differently looking at individual sessions - so depending on the sampling interval this may not pick up the buffer busy waits. You can send the report you are referring to me at david.robson@quest.com and I can have a look at it and give you some more details if you like.

David

[damir.vadas_250]

mail with attached ASH html sent.

[damir.vadas_250]

Hi David!

Did you get my mail with ASH?

Hope you’ll respond when you get time.

Brg

Damir

[damir.vadas_250]

Bumps???

[david.robson_843]

Hi Damir,

I am sorry this has been left this long without getting back to you.

I have had a look at the ASH report and the wait even that is causing Spotlight to show the buffer busy wait alarm is “read by other session”. According to Oracle the wait event is defined as:

This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the “buffer busy waits” wait-event. However, in Oracle 10.1 and higher, the wait time is now broken out into the “read by other session” wait event. (See note 732891.1)

So basically the ASH report is telling you over that five minute interval 6% of time was spent waiting on that wait. Spotlight was then showing you a red alarm which would be triggered at 11%. Due to the differences in averaging this is most likely showing you the same thing. The value was 6% over 5 minutes, but it quite possibly spikes up to 15% or so for a short period, then might drop down to 3% and so on. Spotlight calculates the alarm based on the last 30 seconds so it will usually show alarms much sooner than the ASH report.

We also include the other buffer busy wait events such as “buffer busy waits”, so unless you add together all the different wait events from the ASH report (not just the top 5) they won’t match exactly. Regardless I think both products are pointing to a problem with buffer busy waits on this particular database which you could try and resolve.

David

[damir.vadas_250]

Hi David,

Thank you for your answer.
This practically means that when 30 seconds ASH is taken on the same period, then it should show the exact values … will try once in the future.

>We also include the other buffer busy wait events such as “buffer busy waits” so unless you add together all the different wait events from the ASH report (not just the top 5) they won’t match exactlyI do not understand now relation of that sentence to this problem and just previously said for 30 seconds. Please explain what do you mean (i.e. that this wait event on picture is some kind of cumulative wait …or I what).

>Regardless I think both products are pointing to a problem with buffer busy waits on this particular database which you could try and resolve.
I would not say that this problem, in this volume does not exists 10x a day in almost any database. Please try to understand that I promote this tool as ultimate performance tool and have to has answer on question from clients like that…and me, do not have logical answers. And than this fact as long with fact this is not Oracle tool (free Grid), gives a wrong expression to end customer, who wants some deeper and wider answers for tool which is not free (in a case of paid EE license).

P.S.
Never found a straight answer why should users really use Spotlight instead of free Grid (OEM). Hope you would realize that such a questions maybe need deeper explanations but customers are customers and always suspect in a case of external any tools, outside Oracle.

Brg
Damir