Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 28, 2003

Oracle's DBMS_PROFILER: PL/SQL Performance Tuning

By Amar Kumar Padhi

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.

l_runstatus := dbms_profiler.start_profiler('am' || 

The DBMS_PROFILER.STOP_PROFILER tells Oracle to stop the monitoring.

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.

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).

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM