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 ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
