13-2. Identifying Bottlenecks

Problem

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.

Solution

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;

How It Works

There are four steps necessary to collect statistics on a running procedure:

  1. Call the 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.
  2. Execute the program you suspect has bottleneck issues; in this example, we run the sync_hr_data program.
  3. Execute DBMS_PROFILER.FLUSH_DATA to write the data collected to the profiler tables.
  4. Call the 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.255.55