Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: joel garry <>
Date: Fri, 08 Jun 2007 12:40:59 -0700
Message-ID: <>

On Jun 7, 4:39 pm, wrote:
> I'm digging into the performance article tonight. Thanks so much for
> the feedback.
> Here is the statspack. I hate posting something so large, so I hope
> it's ok. If someone is kind enough to take a peak, then it is
> appreciated.

> ------------------ ---------------- ----------------
> ----------------
> SQL memory manager 599 0
> 1
> cache buffers chai 5900251 332
> 1

This might be an indication of a "hot block." That means, your application is trying to update a particular block (which may contain several rows), over and over. This could lead to the symptoms you are seeing, as the same block is being asked for from the hardware, so it is giving it from its own cache. Oracle uses latches to protect its buffers, and so you are seeing this wait gotten a lot. You need to fix the application that does this! Search Metalink for cache buffers chain for more info, and google for it.

> cache buffers lru 6124904 3900 .
> 999

Oracle uses a least recently used algorithm to age stuff out of the cache. I speculate the waits from the chains make this worse. This might be helped by making the SGA bigger. Or might not, as more buffers means more latches to search. This is the sort of thing where people who say "bigger SGA is going to be better" are often stuffing their mouths with their feet. But I think some increase in yours may help other issues. The problem with tuning this stuff is there are so many variables, and much of what the performance is is based on what _everyone else_ is doing. That's why a methodology like Milsap's works so magically well, it quickly focuses on a few worst problems, and sometimes just one thing is most of the problem. If you can change an app that has everyone wait while everyone hits one hot block, you don't have to worry about how many spindles you have. There's also a view called v$bh, which sometimes can give a clue which objects are important to your app. Google for scripts to look at it (especially pay attention if you run across any comments by Jonathan Lewis).

> checkpoint queue l 5125 0
> 1
> hash table column 753064 5
> 1
> job workq parent l 4 1 .
> 8
> library cache 47360 249 .
> 995

This can bite you. Somewhere in the concepts or performance manuals it notes the rules for using CBO v. RBO. It can be surprising about the fact that CBO will be run if there are any statistics on any of the tables - show parameter OPTIMIZER_MODE to be sure it is RULE (you will probably want to change this to use CBO see ). So, if your app has specifically been written to use RBO you MUST NOT have any statistics on anything. If you have a situation where some of the tables or indexes have statistics and some don't (an easy mistake to make), you will be pushing the CBO down the garden path, it will make it's own assumptions about objects that don't have statistics, which may be very wrong.

> pga_aggregate_target
> 50000000
> processes
> 200
> query_rewrite_enabled
> query_rewrite_integrity
Are you using function-based indexes?

Still need to be reassured you only have one db.


-- is bogus.
I will think pure thoughts!
Received on Fri Jun 08 2007 - 14:40:59 CDT

Original text of this message