Use PL/SQL Developer to analyze PL/SQL code

 

Click Tools ---> Preferences ---> Profiler. The configuration description is as follows (the current demo version is 9.0.0.1601)
Available Columns -- Available Columns (can be moved to the right)
Selected Columns -- Selected Columns
Time units -- unit of Time (seconds, milliseconds, microseconds)
Show 0 occurrences -- whether to display the statement that is executed 0 times
Graphical time display -- display the color depth percentage of processing time in a graphic.

 

 

The following is the result after analysis. Note that the Text column on the right contains other package processes or functions called during this process.

Is the result of sorting by Total time. We can see that the most time-consuming part of the current package is concentrated in the SQL part, so we can concentrate on tuning these SQL statements.

 

A. Profiler panel Toolbar
On the Profiler panel tab, click from left to right,
-- Configure profiler
-- Refresh
-- Delete the current result
-- Run Start Time of the current Code
-- Unit indicates all involved modules called by the code. The default value is the total execution time of all units. When switching to the Unit module, the total execution time of the Unit module is

Unit -- unit name, that is, the stored procedure of execution, including the calling process and anonymous block.
Line -- code line number
Total time -- the execution time of the line of code (the color length represents the percentage of the execution time of the line of code and the maximum code execution time)
Occurrences-number of executions of this row
Text-the corresponding line of code cannot be displayed in the encrypted line
Average time-Average running time (these three times are not displayed by default, if you configure the block settings in profiler)
Maximum time -- maximum running time
Minimum time -- minimum running time

The Text in the Profiler result only displays the first part of the line of the entire code. You can right-click the remaining part in the corresponding line and select [Go to unit line] to directly jump to the corresponding source code location.

You can sort different fields in the Profiler result by clicking the right square of the corresponding column name. To sort the Total time column, click the right square of the Total time column to change to the lower triangle.

 

Use DBMS_PROFILER to locate PL/SQL bottleneck code

Compare the analysis results of PL/SQL profiler