Problem solve Get help with specific problems with your technologies, process and projects.

Getting information about execution times

I am tuning a process that runs many stored functions in various packages. Apart from using the Oracle perfstat and other tuning-oriented products, how is possible to gather information about execution times with a precision better than seconds? Is it possible to build something like a profiler?

You can use the Oracle supplied DBMS_PROFILER package to accomplish exactly what you want. The DBMS_PROFILER package provides a source code profiler for PL/SQL applications to help find code bottlenecks. It gathers statistics into database tables which you can then query to get all kinds of useful information.

There are several scripts that must be executed in order to create the DBMS_PROFILER package and then others to create mechanisms which you can use to summarize and report on the profiled data. The SYS user must first execute the profload.sql script (located in ORACLE_HOME\rdbms\admin) which will create the package. Next, for each schema that will use the profiler, the script named proftab.sql should be ran which creates the profiling tables (plsql_profiler_date, plsql_profiler_units and plsql_profiler_runs) which will hold the profiled data. Next for each schema, run the script profrep.sql, to create the views and packages which will assist in reporting on the profiled data (located in ORACLE_HOME\plsql\demo). Finally, the script profsum.sql can be ran after a profiler run to create summary reports of the collected data.

Once you've got all set up, you can start the profiler easily by simply using the following procedure:

SQL> EXEC dbms_profiler.start_profiler('My first profiler run');
Note that the string string is some unique word or phrase to identify your profiler run.

Once you've started the profiler, simply execute the procedure or function that you wish to profile. Either execute it directly or create a small anonymous PL/SQL block to execute it. For example:

   my_proc_to_test ;
When your procedure completes, stop the profiling session as follows:
SQL> EXEC dbms_profiler.stop_profiler;
The statistics collected will be in the tables listed above. Now, if you run the profsum.sql script, it will produce a bunch of detail and summary reports about your profiling session. Warning!! This script can take a considerable amount of time to run as the queries it uses can be dealing with a lot of data. Personally, I use an adjusted version of the profsum.sql script which runs quickly and is formatted a bit more cleanly. Click on the following link to see the updated version of profsum.sql that I use: profsum.sql

I could go on and on, but I'll leave you with all this to get started and suggest that you refer to the Oracle Supplied Packages reference for more info on DBMS_PROFILER. Hope it helps!

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.