| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Gnarly tuning problem (was: Re: OraPerf.com down?)
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
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 onedisk).
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 13 2005 - 11:05:11 CST
 |
 |