|Oracle8i Supplied Packages Reference
Oracle8i provides a Probe Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Code coverage data can be used by application developers 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.
Improving application performance is an iterative process. Each iteration involves the following:
To support this process, the PL/SQL profiler supports the notion of a run. A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the end of the run by calling the
A typical session involves:
As the application executes, profiler data gets collected in memory data structures which last for the duration of the session. You can call the
FLUSH_DATA function at intermediate points during the session to get incremental data and to free memory for allocated profiler data structures.
Flushing the collected data involves storing collected data to database tables. The tables should already exist in the profiler user's schema. The
SQL script is provided for creating the tables and other data structures required for persistently storing the profiler data.
Alternately, the tables can be created in one centrally administered schema, and profiler users can get access to the tables by creating public synonyms and granting
SELECT privileges on the tables and sequence.
Some PL/SQL operations, such as the very 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, you should warm up the database before collecting profile data. Warming up involves running the application once without gathering profiler data.
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 that 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.
The profiling information is stored in database tables. This enables the ad-hoc querying on the data: It lets you build customizable reports (summary reports, hottest lines, code coverage data, etc.) and analysis capabilities.
With Oracle8i, a sample textual report writer is provided with the PL/SQL demo scripts.
DBMS_PROFILER must be installed as
SQL script to load the PL/SQL Profiler packages.
0 return value from any function denotes successful completion; a non-zero return value denotes an error condition. The possible error returns are listed below:
DBMS_PROFILERpackage is installed, and this version of the profiler package cannot work with this database version. The only possible recovery is to install the correct version of the package.
Starts profiler data collection in session.
Stops profiler data collection in session.
Flushes profiler data collected in session.
Gets the version of this API.
Verifies that this version of the
This function starts profiler data collection in session.
Each profiler run can be associated with a comment. For example, the comment could provide the name and version of the benchmark test that was used to collect data.
This function stops profiler data collection in session.
This function has the side effect of flushing data collected so far in the session, and it signals the end of a run.
This function flushes profiler data collected in session. The data is flushed to database tables, which are expected to pre-exist.
This procedure gets the version of this API.
Major version of
Minor version of
This function verifies that this version of the
DBMS_PROFILER package can work with the implementation in the database.