Re: Oracle Performance -- Possible Disk Bottleneck

On Jun 7, 4:39 pm, beth.sto..._at_gmail.com 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.
>

...
>
> LATCH_NAME NOWAIT_GETS NOWAIT_MISSES
> NOWAIT_HIT_RATIO
> ------------------ ---------------- ----------------
> ----------------
> 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

...
> RULE
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 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#38183 ). 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
> TRUE
> query_rewrite_integrity
> ENFORCED
Are you using function-based indexes?

Still need to be reassured you only have one db.

jg

--
@home.com is bogus.
I will think pure thoughts!  http://www.pickleloaf.com/cars/gm/chevy.htm