Home » RDBMS Server » Performance Tuning » Database slowness and high load due to IO Latencies? (Oracle 11.2.0.4 on Linux)
Database slowness and high load due to IO Latencies? [message #645624] Thu, 10 December 2015 02:49 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi All,

I have a situation in my production database where we had quite a high load (DB time was 79 times the elapsed time). On reviewing the AWR, I saw very high latency on the IO Stats (Av. Rd (ms)) metrics. I think the average read times should be in the range of 10 ms whereas we had the Av. Read (ms) like 30ms/40ms and even 100+ ms. As seen in the awr snapshot below(db name etc. changed to keep it generic).

Now intestingly during some other time of the day the same metrics are pretty 'normal' the same metircs are less than 12 ms during the next day (Tuesday) whereas they were very high on Monday.

Is it right from the this AWR data to conclude that, the slowness of the database (it was reported by alerts that monitor the database) and the load on it was due to the IO latencies? If so storage team has to investigate , is it right?

Note: A part of the AWR report showing these metrics is attached.

Thanks,
Nirav
  • Attachment: awr_parts.gif
    (Size: 174.71KB, Downloaded 1839 times)

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645627 is a reply to message #645624] Thu, 10 December 2015 03:28 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member

Quote:
Is it right from the this AWR data to conclude that, the slowness of the database (it was reported by alerts that monitor the database) and the load on it was due to the IO latencies? If so storage team has to investigate , is it right?
I would hesitate to draw that conclusion from the information you have posted. You could be having CPU issues. Can you post the complete report?

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645629 is a reply to message #645627] Thu, 10 December 2015 03:35 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi John,

You can take it as a 100% confirm fact that CPU is not an issue at all. We have complete proof of it beyond doubt. I hesitate to post the entire AWR as someone may question me etc. - but it is 100% sure that CPU is not an issue -CPU is allways within 30%.

Thanks,
Nirav

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645630 is a reply to message #645629] Thu, 10 December 2015 03:39 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Well, Nirav, you appear to have decided on your answer, which involves passing responsibility to another team (very convenient). You are supremely confident in your ability to diagnose problems; you are not open to any other suggestions; you do not need advice from anyone. One wonders what the purpose of your post was.

Report message to a moderator

icon7.gif  Re: Database slowness and high load due to IO Latencies? [message #645633 is a reply to message #645630] Thu, 10 December 2015 03:44 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
I love the candidness of your response Smile. I will at least now question my self. I am certainly not looking for a 'Yes' to my opinion though I was confiedent, but now I will try to rectify myself.

The only issue in posting Production AWR was that I can be questioned. Can you tell me if there are a few parts of AWR that I should post that would help the analysis?
Thanks,
OrauserN

[Updated on: Thu, 10 December 2015 03:44]

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645654 is a reply to message #645633] Thu, 10 December 2015 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post the top two dozen or so lines from OS utility "top"

In the real world you can be 100% sure & WRONG at the same time.

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645657 is a reply to message #645654] Thu, 10 December 2015 08:58 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
BlackSwan wrote on Thu, 10 December 2015 14:28
In the real world you can be 100% sure & WRONG at the same time.

Smile This.

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645680 is a reply to message #645657] Thu, 10 December 2015 14:29 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Quote:

post the top two dozen or so lines from OS utility "top"

Thanks BlackSwan. Unlucking I don't have the privileges for the running top command. I am a Dev. DBA who only has the AWR.

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645681 is a reply to message #645680] Thu, 10 December 2015 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You have a mystery & we have no clues.
You claim the DB is/was "slow" but post no quantifiable proof to support this report.
is/was every SQL slow; even ad hoc SQL that is not part of the application?
First you need to identify SQL that perform poorly & then proceed with normal SQL tuning approach.

When your only tool is a hammer, every problem is viewed as if it is a nail.
A hammer is a nice tool, but it can be sub-optimal for certain tasks; like dividing a board into 2 pieces.
To be effective you need more tools than only AWR.

Report message to a moderator

Re: Database slowness and high load due to IO Latencies? [message #645798 is a reply to message #645657] Tue, 15 December 2015 04:12 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
pablolee wrote on Thu, 10 December 2015 14:58
BlackSwan wrote on Thu, 10 December 2015 14:28
In the real world you can be 100% sure & WRONG at the same time.

Smile This.



He is wrong, imo.

The problem there is that there are far, far, far too many sessions in that database. roughly 50-100 times too many.

The CPUs will be dying in a fire, likely on system time as the OS thrashes to try and manage the crazy amount of connections.

Looks like a classic case of poor connection management.

I know, I'm breaking the cardinal rule of "incomplete data leads to incomplete analysis" however I've seen this enough to tell that the hurricane cause by those sessions renders everything else invalid until it is corrected.

Here is a handy video for when the lead dev tells you that what I've posted is all wrong:

https://apexapps.oracle.com/pls/apex/f?p=44785:24:112977436179760:PRODUCT:::P24_CONTENT_ID,P24_PREV_PAGE,P24_PROD_SECTION_GRP_ID:9268 ,141,1745


ed: And yes, for more things that are wrong (looks like more than a few) you will need to post the whole AWR.

[Updated on: Tue, 15 December 2015 04:24]

Report message to a moderator

Previous Topic: PLAN_TABLE_OUTPUT
Next Topic: use_concat hint
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 13:41:18 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.