| 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: Database sloowww...trying to figure out why...
Chris,
An Apps Database is a complex beast, and does not respond to the normal tuning that a generic database would merit. [We have some Apps DBAs here in this list, and I am sure that they will agree]. Just wanted to state this beforehand before this becomes a free-for-all!
IMHO, there are two ways of looking at an Apps Database response problem - Functionally, and Technically. In the technical sense, you would look for and solve problems in the environment. This would include I/O response, CPU-load, Memory usage, DB parameter misuse or misconfiguration (*), missing or incorrect Statistics, etc. The (*) indicates that there are a number of init.ora parameters that should be set as per the recommendation without question, as deviation would mean that Oracle will not support you if you change them. Since you mention the DB tier is at 9203, I would look at whether you can move up to 9204 [03 has a nasty bug that sets all Wait events to 'Null Event']... As well, do you collect System Stats?
Functionally, you would look at what is slow, i.e. is it the Forms sessions, or the Concurrent programs, or interface programs, or Self-service Web apps, etc. What patch levels are you at, and how far behind are you, etc.? What modules are in use - do you use Multi-Org, MRC, etc. Are you managing the Purge routines properly? Tracing may be enabled in a variety of locations, depending on whether you are tracing Concurrent programs or Forms... [Btw, do you have an "Oracle Apps DBA" in place to understand these components?]
I believe that you would need to clearly define "what is slow" before even attempting to resolve it... Believe you me, I have been there, done that [in spite of Oracle "Experts" coming in and looking at BCHR and SP ratios finally suggesting large increases in Buffer Cache and Shared Pool successively!]
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' <http://www.needhim.org'/> for Grace and Mercy that is freely available!
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Chris Stephens
Sent: Tuesday, March 30, 2004 1:24 PM
To: oracle-l_at_freelists.org
Subject: Database sloowww...trying to figure out why...
Well, what can I say...more performance problems in our apps environment. The outsourcing company is suggesting things like increasing buffer cache (seriously!).
This time I've successfully campaigned to alteast get perfstat installed on the prod db. Here is what I've found so far.
After taking several snapshots it appears we have a cpu issue (sort of...it's weird because the slowaris box doesn't look stressed for cpa AT ALL!). As much as 80% of the response time is due to cpu usage. ...of that cpu usage the majority of it is 'other cpu'. Absolutely everything I try to do in there is slow (mostly look ups on the data dictionary to try and figure out what's wrong). Without the ability to trace user sessions, I thought I would trace my own. Executing statspack.snap takes as much as 25 seconds!!! So I enable 10046 trace and formatted the trace file through tkprof. The database is 9203. The biggest waits in the trace file are due to the execute phase. Here is an example:
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Tue Mar 30 2004 - 16:43:15 CST
-----------------------------------------------------------------
 |
 |