You notice that a PL/SQL program is running slowly, and you need to identify what sections of the code are causing it to perform poorly.
Use the DBMS_PROFILER
routines to analyze the code and find potential bottlenecks. In the following example, the profiler is used to collect statistics on a run of a program, and then a query displays the statistics.
EXEC DBMS_PROFILER.START_PROFILER ('Test1', 'Testing One'),
EXEC sync_hr_data; -- the procedure identifed has having a bottleneck
EXEC DBMS_PROFILER.FLUSH_DATA;
EXEC DBMS_PROFILER.STOP_PROFILER;
Now that the profile data is collected, you can query the underlying tables to see the results of the analysis:
COL line# FORMAT 999
COL hundredth FORMAT a6
SELECT d.line#,
to_char (d.total_time/10000000, '999.00') hundredth,
s.text
FROM user_source s,
plsql_profiler_data d,
plsql_profiler_units u,
plsql_profiler_runs r
WHERE r.run_comment = 'Test1' -- run_comment matches the text in START_PROFILER
AND u.runid = r.runid
AND u.unit_owner = r.run_owner
AND d.runid = r.runid
AND d.unit_number = u.unit_number
AND s.name = u.unit_name
AND s.line = d.line#
ORDER BY d.line#;
Here are the results of the previous query:
1 .00 PROCEDURE sync_hr_data AS
3 .00 CURSOR driver is
4 11.58 SELECT *
5 .00 FROM employees@hr_data;
9 2.25 FOR recs IN driver LOOP
10 1.64 UPDATE employees
15 .01 END sync_hr_data;
Here is the complete source code for the sync_hr_data
procedure:
CREATE OR REPLACE PROCEDURE sync_hr_data AS
CURSOR driver IS
SELECT *
FROM employees@hr_data;
BEGIN
FOR recs IN driver LOOP
UPDATE employees
SET first_name = recs.first_name
WHERE employee_id = recs.employee_id;
END LOOP;
END sync_hr_data;
There are four steps necessary to collect statistics on a running procedure:
DBMS_PROFILER.START_PROFILER
routine to begin the process of collecting statistics. The two parameters allow you to give the run a name and a comment. Unique names are not required, but that will make it easier to query the results later.sync_hr_data
program.DBMS_PROFILER.FLUSH_DATA
to write the data collected to the profiler tables.DBMS_PROFILER.STOP_PROFILER
routine to, as the name implies, stop the collection of statistics.The query joins the profiler data with the source code lines to display executable lines and the execution time, in hundredths of a second. The raw data stores time in nanoseconds. The query results show three lines of code with actual execution time.
The SELECT
statement from the program unit in question, in which Oracle must establish a remote connection via a database link, consumes the majority of the execution time. The remainder of the time is consumed by the FOR
statement, which fetches each record from the remote database connection, and the UPDATE
statement, which writes the data to the local database.
Selecting records in the loop and then updating them causes the program to switch context between PL/SQL and the database engine. Each iteration of the LOOP
causes this switch to occur. In this example, there were 107 employee records updated. The next recipe shows you how to improve the performance of this procedure.
3.144.255.55