DB Performance Issue - Latch Waits

[VageeshA]

Hello Everyone,

We are facing intermittent performance issue with one of our database. ProTop screen indicates "Latch Waits" to be large (around 4000). Reads on one table are also huge. Please advice.
Details:
Version 9.1E / -B 250000 / -spin 30000 / -aibufs & -bibufs 100 each / AIW & BIW running / APW 14

Tried increasing -spin but no improvement.

Thank You,
Vageesh

 

[cj_brandt]

Is that table in its own area? Are the indexes for the table in their own area?
I would look for other indicators for the performance problem rather than Latch Waits. What does the summary screen show during periods where the performance issue is occurring?

What is the reason for having 14 APW's ? If there wasn't a good one, I would consider starting with 6.

 

[TomBascom]

Unless I had consistent buffers flushed I'd only have 2 APWs. 14 is way out of line.

But it has nothing to do with the latch waits.

4,000 latchwaits per second is quite a few. However... are there any other indications of a problem?

Generally speaking if you have a table that has a "huge" number of reads that is probably the main driver of your latch wait issue. It is also likely that the read activity is due to bad coding or poor indexing on that table. Aside from fixing the code (or adding an appropriate index) as CJ indicates, one possible "band-aid" approach in version 9 is to isolate the problematic table into 2 dedicated storage areas -- 1 for the data and another for the indexes. This isn't a complete cure but it often helps improve the situation.

Upgrading to OE10 and converting to type 2 storage areas would also, of course, help a lot.

 

[VageeshA]

Hi,

Table & Index are in their own areas. Summary screen showed couple of Blocked sessions apart from this there were no abnormal displays.
We have 13 Physical Disks, this is the reason we have started 14 APW.

Thanks,
Vageesh

 

[cj_brandt]

Having 13 Physical Disks is not a good reason to have 14 APW's.

Try to provide more details about the performance issue and provide stats which show DB activity during the periods of slow performance.

 

[VageeshA]

Hi Tom,

Could you please advise the optimal value for -spin parameter in case the CPUs are Hyper threaded.

Thanks,
Vageesh

 

[TomBascom]

There is no single, simple answer to an optimal value for -spin. You have to experiment. Also, be aware that a settings which works well for one particular workload may not work well for another -- IOW the optimal setting for a single threaded table scan oriented process is not (usually) the same setting that provides best interactive concurrency.

High latch waits may, or may not be an actual problem. If the users are happy and there are no other symptoms then it is just telling you that you are working the CPUs very, very hard. That isn't necessarily a bad thing. (Although there are not all that many good reasons to be reading records at "huge" rates on a consistent basis -- I think I'd be worrying more about getting to the bottom of that issue first.)

 

[TomBascom]

Are your 13 disks in some sort of RAID configuration? Or are the JBOD? (Just a Bunch Of Disks).

In any case 14 APWs is not usually useful. I suspect that you have taken a (very) old recommendation from somewhere. It's not a big deal -- excess APWs just waste a little bit of CPU time and clutter up "ps" listings but gradually scale them back. If you start to see "buffers flushed" you've gone far enough. I'll be that if you eventually reduce it to 2 APWs you will be fine.