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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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.

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date