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

Quadrant 1 Quadrant 2

Quadrant 3

Quadrant 4

Figure 1-2. Quadrant approach

Now that we have identified which component of the application needs immediate attention, the next step
would be, where do we start? How do we get to the numbers that will show us where the problem exists? There are
several methods to do this. One is a method that some of us would have used in the old days: embedding times calls
(timestamp) in various parts of the code and logging them when the code is executed to a log file. From the timestamp
outputs in the log files, it would provide analysis of the various areas of the application that are consuming the largest
execution times. Another method, if the application design was well thought out, would be to allow the database
administrator to capture performance metrics at the database level by including DBMS_APPLICATION_INFO definitions
(discussed earlier) of identifying modules and actions within the code; this could help easily identify which action in
the code is causing the application to slow down.
Obviously the most important piece is where the rubber meets the road. Hence, in the case of an application that
interacts with the database, the first step would be to look into the persistence layer. The database administrator could
do this by tracing the database calls.
The database administrator can create trace files at the session level using the DBMS_MONITOR.SESSION_TRACE_ENABLE
procedure. For example

SQL> exec dbms_monitor.session_trace_enable(session_id=>276,
serial_num =>1449,
waits=>TRUE,
binds=>TRUE);

The trace file will be located in the USER_DUMP_DEST directory. The physical location of the trace file can be
obtained by checking the value of the parameter (or by querying V$PARAMETER ):

SQL> SHOW PARAMETER USER_DUMP_DEST