An application can always be fine-tuned
for better performance with the use of better alternatives or with the new
features introduced with every release of Oracle.
Simply inspecting the code can bring out
the bottlenecks eating up your processing time. Using explain plan to
fine tune the SQL statements resolves issues most of the time. However,
sometimes it may not be that simple. It is baffling when all the SQL statements
are well tuned but the routine still takes noticeable time to execute.
DBMS_PROFILER Package
Oracle 8i provides a new tool called PL/SQL Profiler. This is a powerful tool
to analyze a Program unit execution and determine the runtime behavior. The
results generated can then be evaluated to find out the hot areas in the code.
This tool helps us identify performance bottlenecks, as well as where excess execution
time is being spent in the code. The time spent in executing an SQL statement
is also generated. This process is implemented with DBMS_PROFILER package.
The possible profiler statistics that are generated:
1. Total number of times each line was executed.
2. Time spent executing each line. This includes SQL statements.
3. Minimum and maximum duration spent on a specific line of code.
4. Code that is actually being executed for a given scenario.
DBMS_PROFILER.START_PROFILER
The DBMS_PROFILER.START_PROFILER tells Oracle to start the monitoring process.
An identifier needs to be provided with each run that is used later to retrieve
the statistics.
e.g.:
l_runstatus := dbms_profiler.start_profiler(‘am’ ||
to_char(sysdate));
DBMS_PROFILER.STOP_PROFILER
The DBMS_PROFILER.STOP_PROFILER tells Oracle to stop the monitoring.
e.g.:
l_runstatus := dbms_profiler.stop_profiler;
DBMS_PROFILER.FLUSH_DATA
The data collected for an execution is held in the memory. Calling the
DBMS_PROFILER.FLUSH_DATA routine tells Oracle to save this data in profiler
tables and clear the memory.
e.g.:
l_runstatus := dbms_profiler.flush_data;
The above functions return the following status’.
0 : Successful completion
1 : Incorrect parameters passed (error_parm).
2 : data flush operation failed (error_io).
-1 : mismatch between package and database implementation (error_version).
