Plsql Developer using Profiler to analyze stored procedure performance

The Oracle stored procedure needs to be written recently, as required by the company. I am a novice, after the completion of the writing of the stored procedure, it is necessary to analyze the execution time of each statement in the stored procedure in order to

Optimize the entire stored procedure.

Due to the use of Plsql Developer client tools, and most of the internet is introduced by the manual method of applying the profiler, using relatively chores, roughly through the command line to SYS user

Create a Dbms_profiler package first, and also create a user to hold the trace information, and a synonym for the Prof table and sequence, and finally create Prof tables and sequences with the profiler user and empower them.

There is no introduction to this approach. (A separate introduction to this method will be written if necessary)

The following is a detailed introduction to the Plsql DEVELOPER Application Profiler:

1. Right-click on the stored procedure and test

2. After clicking Test, a test window will pop up, for example, click "Create Overview Chart Report"

3. Click "Execute" or F8 to execute the stored procedure.

4, after the execution, click on the "Test window" on the overview map will appear as follows:

The detailed meanings of each column are as follows:
Unit-the cell name, which is the procedure that executes the stored procedure, including its invocation
Line--Code row number
Total time-the line execution times (color length represents the execution time of our code and the percentage graph of the longest code execution time)
Occurrences--Number of executions on this line
Text--The corresponding line of code, for the encrypted code, will not be displayed
Average time-Average Run time
Maximum time-Maximum uptime
Minimum time-Minimum runtime (the above three time is not displayed by default, can be selected through the Configuration dialog box, participate in 4.4)

The source code shown in the list displays only one row, and if you want to locate it, you can open the right button in the corresponding row and select [Go to Unit line], which will jump directly to the corresponding source code location.

The profiler Panel's toolbar description:
A. Display Configuration dialog box
B, Refresh
C. Delete the data for the current run number
D, Run displays all the profiler lists for the current system, and defaults to the current trace
E, Unit displays the cell list information for this trace (execution time), which defaults to the execution time of all units

5.4. Profiler Configuration dialog box

This allows for a good analysis of stored procedures to optimize them.