Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: Oracle Performance -- Possible Disk Bottleneck

From: EscVector <EscVector_at_gmail.com>
Date: Fri, 08 Jun 2007 15:24:36 -0000
Message-ID: <1181316276.780945.184140@p47g2000hsd.googlegroups.com>


I like all of Mr. Hooper's suggestions, he's one of the best and I only add to his caution by saying not to change anything unless you have a very high degree of surety that you are attacking the problem. Use Method-R diagnosis techniques. This is in Cary's book. Method-R summary: Don't guess.

My point as to the virus software and oracle is while perfmon may accurately depict system I/O, it will not point to the specific part of the disk that is hot. I've worked with some very good storage admins who have no issue identifying exactly which disk block is hot without having to look at perfmon. Again, while accurate, it appears not to be pointing to the exact issue on disk otherwise your diagnostic issue would be solved, i.e. oracle file #? is being access by os etc... What's in the file? Indexes? Match this to observed data, viola, surety = 98% that it is most likely single block read index scans causing the hot spot. The hard part is figuring out what to do to fix it. Knowing what it is is easy, fixing it is the hard part.

I strongly suggest stopping the virus software. Each machine is singular, meaning it is totally unique, so what it is doing on one machine may not be the same on the other. So if you have lots' of little disk reads, and the virus software is actively scanning the file each time, again, viola, the extra i/o is now accounted.

> > - OPTIMIZER_INDEX_CACHING - may be important if OPTIMIZER_MODE were
> > not RULE, as DB_CACHE_SIZE may not be large enough to cache 90% of the
> > index blocks.
> > - OPTIMIZER_MODE - RULE uses indexes, even if those are not the best
> > access method, why RULE?

OPTIMIZER_INDEX_CACHING and OPTIMISER_INDEX_COST_ADJ don't actually "cache" anything though. They only skew CBO costing associated with index reads. So unless you session set the optimizer _rule to CHOOSE or COST, these settings are useless.

As for RULE in 9i. Get rid if it (after extensive testing of course or unless you want to be the optimizer)...

I've used Winternals http://en.wikipedia.org/wiki/Sysinternals tools to diagnose these issues w/o the help of qualified storage admins. I keep posting this same comment, but winternals are so useful for windows debugging, it is worth being redundant.

Finally, I caution you regarding the statspack info. It is highly dependent on how you snap. I use it for long term planning only and use 10046 traces, tkprof or other profiler tools, trcsess(10g) exclusively to diagnose performance problems. Success rate is 100%. No guessing involved.

Problem diagnosis should take no longer than it takes to run the process being traced + time to profile. There should be zero guessing as to the cause. Again, fixing the problem is a whole other monster. Received on Fri Jun 08 2007 - 10:24:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US