Re: Gnarly tuning problem (was: Re: OraPerf.com down?)
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Jan 2005 08:52:40 -0800
Stephen,
If you aren't using MTS or java, then there's no reason to have MTS running
or a large java_pool_size. You haven't said what "big waits" are, so we
can't say whether those waits need to be addressed, but for now we'll assume
they should be. Increasing your shared pool and buffer cache may not help.
Are you getting a lot of reloads of SQL? If not, I don't think a larger
shared pool will help.
Are your scattered reads because of a small buffer cache, or do you need
indexes or query tuning? On your statspack report, look at what queries are
doing the most physical reads and see what can be done there. Many is the
database where such a problem can be solved with an index or two.
Why do you want to increase sort area size? Are you seeing lots of sorting
to disk? Are you seeing direct read/write waits?
Having everything on one disk is a bad idea for sooo many reasons. Moving
the redo logs will probably reduce your length of scattered read waits as
well as helping log file sync waits. Log file sync waits are often a sign
of committing too often, but if everything is on one disk, moving them
should help a lot.
You haven't said what Oracle version you're on. Depending on your version,
all or most of the changes you propose can be done without downtime. You
can turn off MTS, change the sort_area_size (if needed), and move redo logs
with the database running. If you're on 9i and sga_max_size is set
sufficiently, you can change the shared_pool and db_cache_size with the
database running. I do think you need to bounce to change java_pool_size.
But I'd make sure you need a larger shared pool or buffer cache before you
change them. And you can certainly shoot a consultant while the database is
up; it's done all the time!
--Terry
----- Original Message -----
From: "stephen booth" <stephenbooth.uk@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, January 13, 2005 3:16 AM
Subject: Gnarly tuning problem (was: Re: OraPerf.com down?)
On Thu, 13 Jan 2005 13:36:11 +0300, Jaffar_DBA <sjaffarhussain@xxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
