Methodology - Expert Oracle RAC Performance Diagnostics and Tuning - page 6

Methodology
Problem-solving tasks of any nature need to be approached in a systematic and methodical manner. A detailed
procedure needs to be developed and followed from end to end. During every step of the process, data should be
collected and analyzed. Results from these steps should be considered as inputs into the next step, which in turn is
performed in a similar systematic approach. Hence, methodology is the procedure or process followed from start to
finish, from identification of the problem to problem solving and documentation.
During all this analysis, the cause or reasons for a behavior or problem should be based on quantitative analysis
and not on guesswork or trial and error.

USING DBMS_ appLICatION INFO

a feature that could help during all the phases of testing, troubleshooting, and debugging of the application is
the use of the DBMS_APPLICATION_INFO package in the application code. the DBMS_APPLICATION_INFO package
has procedures that will allow modularizing performance data collection based on specific modules or areas
within modules.

Incorporating the DBMS_APPLICATION_INFO package into the application code helps the administrators to easily
track the sections of the code (module/action) that are high resource consumers. When the user/application
session registers a database session, the information is recorded in V$SESSION and V$SQLAREA . this helps in easy
identification of the problem areas of the application.

the application should set the name of the module and name of the action automatically each time a user enters
that module. the name given to the module could be the name of the code segment in an oracle pre-compiler
application or service within the Java application. the action name should usually be the name or description of
the current transaction within a module.

Procedures

Procedure Description

SET_CLIENT_INFO Sets the CLIENT_INFO field of the session.

SET_MODULE Sets the name of the module that is currently running.

SET ACTION Sets the name of the current action within the current module.

Sets a row in the GV$SESSION_LONGOPS table.

SET_SESSION_LONGOPS

When the application connects to the database using a database service name (either using a type 4 client or a
type 2 client) then even a granular level of resource utilization for a given service, module, and/or action could be
collected. database service names are also recorded in GV$SESSION .

one of the great benefits of enabling the DBMS_APPLICATION_INFO package call in the application code is that the
database performance engineer can enable statistics collection or enable tracing when he/she feels it's needed
and at what level it's needed.