Subject: TROUBLESHOOTING GUIDE: Common Performance Tuning Issues
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).
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 (email@example.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.