Debugging Oracle Database(10g) and Application performance

Most often we find ourself in a position where we need to improve our application performance over a period of time when the Application has scaled due to large volume of data.

In a typical three tier Architecture(Application server,Webserver,Database server) or any Multi tier Architecture the poor performance may be due to any of the Components.

Each component needs to be debugged for performance issues.

The primary ways of identifying performance issues are

A) Instrumenting the Application:-  Instrumentation for example is logging information about important transactions of the Application.
Recording information such as time taken to perform a transaction and response transaction over a period of time,wait time for resources for a particular transaction etc.
It should be done during design of the application itself for later debugging.This Appproach saves a lot of time and resources which is required to identify performance bottle necks when the application has gone to production.

The important consideration to be made before instrumentation is

1)There should be always be an option to disable the logging of information since it would be additional overhead during normal operation of the Application

 2)There should be different levels of logging information.For example Warning level,debug level,information level.

3)It should be done for all the components of the Architecture

Although all the Components of the Application has to be instrumented we will cover only Oracle Instrumentation to be relevant with the topic.

Oracle DB:-Oracle Instrumentation which covers mainly the following categories

1. Debugging:-The characteristics of it are

• Should be able to enable debugging for one or more “modules” in the code.
• Should be able to activate debugging remotely for one or more “users”.
• Debugging should be written to one or more “persistent” sources and preferably to a console for initial debugging and there needs to be enough debugging code to be useful.

2. Logging:-Although logging is dependent on the nature of the application here are some common requirements for application logging:

•  Output to a persistent source
•  Provide logging levels (i.e. informational or error)
•  The ability to record errors and anomalies
•  The ability to record runtime information

3. Runtime registration and Metric collection

The source code ,examples and other information of the oracle instrumentation are available at debugf,log4plsql,dbms_application_info,log4plsql example and user guide,Debug.f usage and other information

B) Profiling the Application:- the Application-It is nothing but gathering information about various component of the Application through external profilers which means not part of the Application environment and also integrated profilers part of the development environment.They defer from Instrumentation in the sense they are independent of the Application and are tools of the underlying technology.

Although all the Components of the Application has to be profiled we will cover only the database part

 
1)TKRPOF and TRCSESS:- These profilers are used for interpreting SQL TRACE which is used to provide information mainly about SQL statements and their processing .The details about how to enable and disable SQL trace,how to interpret trace files(TKPROF) and how to extract specific information from trace files (Trcsess)are available in SQL TRACE Details,Interpreting TKPROF,Oracle Documentation

 2)PLSQL Profiling:-To profile PL/SQL code, the database engine provides an API through the package dbms_profiler. With that package, the profiler integrated in the PL/SQL engine can be enabled and disabled at the session level. For more information kindly check DBMS_PROFILER