Gnarly tuning problem (was: Re: OraPerf.com down?)

From: stephen booth <stephenbooth.uk_at_gmail.com>
Date: Thu, 13 Jan 2005 11:16:18 +0000
Message-ID: <687bf9c4050113031675cedd57@mail.gmail.com>

On Thu, 13 Jan 2005 13:36:11 +0300, Jaffar_DBA <sjaffarhussain_at_gmail.com> wrote:
> Of course, me too facing the same problem. I was trying to access
> oraperf.com, but no luck.

Thanks. Oh well, back to trying to analyse STATSPACK reports by hand. I've got this really gnarly problem with a database that I've been landed with, it seems to be an excellent example of how *not* to set up and Oracle database. It's not a case of working out what to fix, rather what to fix first.

I'm getting big waits on virtual_circuit_status, log_file_sync and db_file_scattered_read, also it looks like virtually every piece of non-recursive SQL is being hard parsed everytime it's executed but invalidations are zero or so close to zero as to make no difference.

The SQL all uses bind variable so that avenue is already closed to me.

I figure the virtual-circuit_status wait is down to the fact that it's configured for shared server but all logins are dedicated server. Probably a red herring but I'd like to eliminate it anyway.

Right now I'm leaning towards:

* Increase the size of the shared pool
* Increase the size of the buffer cache
* Get rid of the dispatchers and shared server processes 
* Move the redologs onto a different disk (currently everything is on one disk).
* Increase sort_area_size 
*  Reduce the Java pool (it's 120Mb and empty, the app doesn't use

Java in the database)
* Hunt down the consultant who set up this database and express my views

Unfortunately tuning is something I haven't done much of in the past and the Oracle University tuning course I went on last year is proving itself to be as useful as a chocolate teapot.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 13 2005 - 05:11:31 CST