I have an issue with the performance of a production database. I'm currently working on a development database as a DBA. Here is the scenario: as soon as you join a concern that is performing maintenance of a production database in a Unix environment (e.g., banking), you are asked to verify the performance of the database. Is it high-quality or not? Where will you start your first step and what are the necessary procedures you will take before starting your job? Will you work directly on a production database or take up a standby database? Do we have any standard procedure to follow for checking performance?
If the users are happy, then the database performance is fine. If the users are not happy, then you have to find out what operation is performing unacceptably, and the performance goal for that operation. Then, arrange for a test of the operation and trace the database session (using dbms_support in Oracle9i or dbms_monitor in 10g). Format the resulting trace file with tkprof and inspect the output; it will show you where the session is spending its time. Usually you will find that changes to SQL and PL/SQL code, or sometimes the addition of indexes, will be required to improve performance.
Repeat this procedure for each application whose performance is unacceptable.
My point here is that it is not possible to assess database performance in and of itself -- you must consider the response time of applications that use the database.