Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts. Oracle and third-party vendors offer a variety of products to help you do this; generally they focus on analyzing the SQL statements in your code, offering alternative implementations, and so on. These tools are very powerful, yet they can also be very frustrating to PL/SQL developers. They tend to offer an overwhelming amount of performance data without telling you what you really want to know: how fast did a particular program run and how much did the performance improve after making this change?
To answer these questions, Oracle offers a number of built-in utilities. Here are the most useful:
In case you do not have access to a tool that offers an interface to DBMS_PROFILER, here are some instructions and examples.
First of all, Oracle does not install DBMS_PROFILER for you automatically. To see if DBMS_PROFILER is installed and available, connect to your schema in SQL*Plus and issue this command:
SQL> DESC DBMS_PROFILER
If you then see the message:
you will have to install the program.
For early Oracle versions, such as Oracle7 and Oracle8 Database, you need to ask your DBA to run the following scripts under a SYSDBA account (the first creates the package specification, the second the package body):
For later versions, you need to run the $ORACLE_HOME/rdbms/admin/profload.sql file instead, also under a SYSDBA account.
You then need to run the $ORACLE_HOME/rdbms/admin/proftab.sql file in your own schema to create three tables populated by DBMS_PROFILER:
Finally, you will probably find it helpful to take advantage of some sample queries and reporting packages offered by Oracle in the following files:
Once all these objects are defined, you gather profiling information for your application by writing code like this:
Once you have finished running your application code, you can run queries against the data in the PLSQL_PROFILER_ tables. Here is an example of such a query that displays those lines of code that consumed at least 1% of the total time of the run:
/* File on web: slowest.sql */
As you can see, these queries are fairly complex (I modified one of the canned queries from Oracle to produce the above four-way join). That’s why it is far better to rely on a graphical interface in a PL/SQL development tool.
After you’ve analyzed your code and identified bottlenecks, the following sections can help you determine what kinds of changes to make to improve code performance.
blog comments powered by Disqus