-
Hello folks,
Not sure whether this is typical scenario but still :
You are a DBA. An huge commercial application is running against a reasonably big ORACLE database. Size close to a TB with 4 instances. On HP-UX ad XP arrays.
You DO NOT have access to the application. It is a third party application.
Issue is : things are running pretty slow.
Task is : Improve them.
The DBAs feel that the database is doing fine, the application is THE problem. The application guys feel exactly the other way.
Now, can somebody give me an idea about what can I do to prove that database is doing fine ? There are no bottlenecks ? There are no performance issues from database side ?
A list of such things is the thing I expect...
-
look into those docs on metalink:
1.
Note:106285.1
Subject: TROUBLESHOOTING GUIDE: Common Performance Tuning Issues
2.
UTLBSTAT / UTLESTAT (commonly known as bstat/estat reports)
In general, pay attention to wait event interface as it gives a lot about database issues regarding concurency and not only.
Other things are
-to identify sessions consuming most of CPU (get it from v$sesstat);
-identify work intensive SQL statements (get it from v$sql_area).
I would start tuning with abnormaly long wait events as well as identifying with intensive SQL. Intensivce SQL is both frequently executed and with many block gets per execution.
Yet another big issue is (fragmented) shared pool. Application misbehavour or shared pool size misconfiguration can cause dramatic effect.
db_buffers pool sizing(not too small at least) is very important too, as caching is what really helps in multyuser environment.
Of course, fresh start database or calculate delta values to have a picture of what is going on during specific time interval.
Remember, that some slowlyness can be cause by Oracle optimizer bugs or failure to prepare production database for Oracle's Cost Based Optimizer (like missing, or, on contrary, too many or too accurate statistics - issue known as overlearning).
-
In that case I would try to figure out at the network level how efficient are the packets are sent and how many hops they do using tracert.
-
You can use Quest Spotlight to verify "easily" if there aren't bottlenecks.
UTLBSTAT/..ESTAT gives you the same but a little "harder"
-
as for database, I forgot one more tool:
use SQL trace if you know what session is in problem to find exact statistics for every SQL statement and its wait statistics too.
Best of all, enable SQL trace via 1046 events, Level 12.
For more on SQL Trace read this article (find it on trivadis.com)
Subject Tracing Bind Variables and Waits
Author Christian Antognini (christian.antognini@trivadis.com)
Type of Info Technical Info, Oracle8
Source Practical experience
-
If you have Enterprize Manager or any other third party Tool to monitor Performance take performance overview and see where the problem and which component of the database is culprit. If nothing is shown over there, it will be the problem with application or SQL of application.
Do the same on the network and server and run "vmstat" or sar and see whats going on with the system activity. If there is no problem with OS or Databse or network, where else will be the problem ??? Definitely with the application.
Hope give some idea to go about...
