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.
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.
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));
The DBMS_PROFILER.STOP_PROFILER tells Oracle to stop the monitoring.
e.g.: l_runstatus := dbms_profiler.stop_profiler;
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).