PL/SQL Profiling

September 21, 2005 Database, Java, Oracle, PL/SQL, Tools 3 Comments

How do you trace performance issues and bottlenecks in your PL/SQL applications?

Since 8i the Oracle database has been equiped with the profiling tool dbms_profiler. It is quite simple to use: start the profiler, execute the code, stop the profiler. The profiler analyzes the execution of PL/SQL statements and stores the results for later usage. As expected, Oracle does not provide tools to analyze the data. You can do that by querying the three profiling tables yourself and have a hard time interpreting the data. Fortunately other tools like TOAD (and also PL/SQL Developer) can support you with this analysis, e.g. by presenting the data in a nice down-drillable graphic and by displaying the code along with the data. This article gives an excellent introduction.

Figure 1: The TOAD profiler analysis. The top part presents the relative time per line. The bottom part presents the absolute time alongside the code. A pie chart is also available

Example of how TOAD presents the profiler data.

You can also profile your code from outside TOAD, e.g. with SQL+ or JDeveloper. But when you want to review the results in TOAD you’ll need to add some data to the profiling tables that TOAD requires (like totals and source code). The same article provides all the necessary information (along with pl/sql code) on how to do that.

Running from JDeveloper is also quite easy:
_ Navigate in the DB Connections Navigator to the procedure (or function) you’d like to profile.
_ Run this procedure (with a right click).
_ The ‘run PL/SQL’ window will open: Add profiling code in JDeveloper
_ Edit the anonymous block to your liking (add the start and stop profiler methods and the toad specific SQL code).
_ Then really run your code.
_ View the analysis in TOAD (You may have to reopen the profiler analysis window).

Before using, a few preparations are needed:
Install the dbms_profiler package (9i and before; 10g has it installed by default) with public access.
Install the profiler tables (plsql_profiler_runs, plsql_profiler_data, plsql_profiler_units) and provide public access. This can be done easily with the TOAD server-side objects wizard.

Related Posts

About The Author

Aino Andriessen

Aino Andriessen is principal consultant and expertise lead 'Continuous Delivery'. His focus is on Oracle Fusion Middleware ADF and SOA development, Continuous Delivery, architecture, improving the software development proces and quality management. He is a frequent presenter at Oracle Open World, ODTUG Kaleidoscope, UKOUG Technology Conference and OUGN Vårseminar. He writes articles and publishes at the AMIS technology blog (http://technology.amis.nl/blog/).

3 Comments

  1. RockinRyan September 6, 2006

    I too have been fighting to enable the TOAD profiler button. There is a TOAD_PROFILER package that seems responsible for enabling the functionality. The source for the TOAD_PROFILER package can be obtained directly from TOAD. On my version I went to Tools -> Server Side Objects Wizard. I chose the last option on the form that opened (Create setup scripts without a database connection) because I do not have sys access of my own use. Anyway that utility will generate a DDL script (TOADProfiler.sql) that will include the needed grants and procedures to presumably enable the profiler button. I’m posting this to possibly help others who are not able to profile through TOAD but do already have the DBMS_PROFILER package available. If I find that I’ve mistated anything I’ll re-post…

  2. Johan van den Heuvel December 6, 2005

    You have first to install the DBMS_PROFILER package in the database

    SYS.DBMS_PROFILER
    /opt/oracle/product/920/rdbms/admin/profload.sql (unix)
    D:\oracle\ora92\rdbms\admin\profload.sql (windows)

    installation of DBMS_PROFILER
    login directly on the server with telnet of ssh as Oracle dba, then with sqlplus “/ as sysdba”
    SQL> @$ORACLE_HOME/rdbms/admin/profload.sql

    SQL> CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
    SQL> GRANT connect TO profiler;

    SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
    SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
    SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
    SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

    SQL> CONNECT profiler/profiler

    SQL> @$ORACLE_HOME/rdbms/admin/proftab.sql
    SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
    SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
    SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
    SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

    then you can use the package

  3. Andreas Markussen October 4, 2005

    I have been working with the DBMS_PROFILER, and I can run it without problems,
    but TOAD does not enable the PL/SQL Profiling option. (It is grayed out)

    Do you have any idear on how to show the report from the DBMS_PROFILER without TOAD or
    if you know what could be the problem with TOAD. (8.0) On Oracle 8.1.7.

    Br,

    Andreas