Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01 |
|
Oracle8i provides a Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. You can use the collected profiler (performance) data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
The profiler API is implemented as a PL/SQL package, DBMS_PROFILER
, that provides services for collecting and persistently storing PL/SQL profiler data.
Note:
|
This chapter discusses the following topics:
Improving application performance is an iterative process. Each iteration involves the following steps:
The PL/SQL profiler supports this process using the concept of a "run". A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the ending of a run by calling the START_PROFILER
and STOP_PROFILER
functions.
A typical run involves:
As the application executes, profiler data is collected in memory data structures that last for the duration of the run. You can call the FLUSH_DATA
function at intermediate points during the run to get incremental data and to free memory for allocated profiler data structures.
Flushing the collected data involves storing collected data in database tables. The tables should already exist in the profiler user's schema. The PROFTAB
.SQL
script creates the tables and other data structures required for persistently storing the profiler data.
Note that running PROFTAB.SQL
drops the current tables. The PROFTAB.SQL
script is in the RDBMS/ADMIN
directory. Some PL/SQL operations, such as the first execution of a PL/SQL unit, may involve I/O to catalog tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called.
To avoid timing this overhead, "warm up" the database before collecting profile data. To do this, run the application once without gathering profiler data.
You can allow profiling across all users of a system, for example, to profile all users of a package, independent of who is using it. In such cases, the SYSADMIN should use a modified PROFLOAD.SQL
script which:
DBMS_PROFILER
must be installed as SYS
.
Use the PROFLOAD.SQL
script to load the PL/SQL Profiler packages.
With the Probe Profiler API, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
Note: It is possible to infer the code coverage figures for PL/SQL units for which data has been collected. |
The profiling information is stored in database tables. This enables querying on the data: you can build customizable reports (summary reports, hottest lines, code coverage data, and so on. And you can analyze the data.
The PROFTAB.SQL
script creates tables with the columns, datatypes, and definitions as shown in Table 46-1, Table 46-2, and Table 46-3.
With Oracle8, a sample textual report writer(profrep.sql) is provided with the PL/SQL demo scripts.
The profiler only gathers data for units for which a user has CREATE
privilege; you cannot use the package to profile units for which EXECUTE ONLY
access has been granted. In general, if a user can debug a unit, the same user can profile it. However, a unit can be profiled whether or not it has been compiled DEBUG.
Oracle advises that modules that are being profiled should be compiled DEBUG,
since this provides additional information about the unit in the database
Each routine in this package has two versions that allow you to determine how errors are reported.
In each case, the parameters of the function and procedure are identical. Only the method by which errors are reported differs. If there is an error, there is a correspondence between the error codes that the functions return, and the exceptions that the procedures raise.
To avoid redundancy, the following section only provides details about the functional form.
Exception | Description |
---|---|
version_mismatch |
Corresponds to error_version. |
profiler_error |
Corresponds to either "error_param" or "error_io". |
A 0
return value from any function denotes successful completion; a nonzero return value denotes an error condition. The possible errors are as follows:
error_param constant binary_integer := 1;
error_io constant binary_integer := 2;
DBMS_PROFILER
package is installed, and if the version of the profiler package cannot work with this database version. The only recovery is to install the correct version of the package.
error_version constant binary_integer := -1;
|
Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|