Compare the analysis results of PL/SQLprofiler

Using PLSQLPROFILER to analyze PLSQL code is the most effective way to quickly locate PLSQL code segments. In the previous article, we used PLSQLPROFILER to locate the PLSQL bottleneck generation.

Using PL/SQL profiler to analyze PL/SQL code is the most effective way to quickly locate PL/SQL code segments. In the previous article, PL/SQL profiler was used to locate the PL/SQL bottleneck generation.

Using PL/SQL profiler to analyze PL/SQL code is the most effective way to quickly locate PL/SQL code segments. In the previous article, using PL/SQL profiler to locate the PL/SQL bottleneck Code describes how to install PROFILER and provides analysis examples. This article compares and analyzes the performance of Master Tom's code and attaches its code.

Use PL/SQL profiler to locate PL/SQL bottleneck code

1. Stored Procedure for analysis

-- Environment
Sys @ USBO> select * from v $ version where rownum <2;

BANNER
Bytes ------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

-- The original process used for profiling. The following is the process of using the literal quantity. Note that the Code includes enabling and stopping profiler.
Scott @ USBO> create or replace procedure binds
2 is
3 v_num number;
4 begin
5 dbms_profiler.start_profiler ('binds ');
6 for I in 1 .. 100000 loop
7 v_num: = dbms_random.random;
8 insert into t1 values (v_num, v_num );
9 end loop;
10 dbms_profiler.stop_profiler;
11 end;
12/

Procedure created.

-- The original process used for analysis. The following describes the process of using the binding quantity. Note that the Code includes enabling and stopping profiler.
Scott @ USBO> create or replace procedure literals
2 is
3 v_num number;
4 begin
5 dbms_profiler.start_profiler ('literals ');
6 for I in 1 .. 100000 loop
7 v_num: = dbms_random.random;
8 execute immediate
9 'insert into t1 values ('| v_num |', '| v_num | ')';
10 end loop;
11 dbms_profiler.stop_profiler;
12 end;
13/

Procedure created.

-- Author: Leshami
-- Blog:

-- Clear historical data in the profiling table (executed before each profiling and comparison)
Scott @ USBO> delete from plsql_profiler_data;

Scott @ USBO> delete from plsql_profiler_units;

Scott @ USBO> delete from plsql_profiler_runs;

-- Execute two different processes respectively
Scott @ USBO> exec literals;

Scott @ USBO> exec binds;

2. Extract analysis and comparison results

Scott @ USBO> @ profsum

2 rows updated.

PL/SQL procedure successfully completed.

=
=
================================
Total time

GRAND_TOTAL
-----------
58.93

=
=
================================
Total time spent on each run

RUNID RUN_COMMENT SECS
---------------------------
7 literals 53.19
Eight binds 5.75

=
=
================================
Percentage of time in each module, for each run separately

RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECS PERCEN
----------------------------------------------------------
7 literals scott literals 53.19 100.0
8 binds scott binds 5.75 100.0

=
=
================================
Percentage of time in each module, summarized into SS runs

UNIT_OWNER UNIT_NAME SECS PERCENTAG
-------------------------------------------
Scott literals 53.19 90.25
Scott binds 5.75 9.75

=
=
================================
Lines taking more than 1% of the total time, each run separate

Runid hsecs pct owner UNIT_NAME LINE # TEXT
---------------------------------------------------------------------------
7 5221.18 88.6 scott literals 8 execute immediate
8 502.97 8.5 scott binds 8 insert into t1 values
(V_num, v_num );

7 73.04 1.2 scott literals 7 v_num: =
Dbms_random.random;

=
=
================================
Most popular lines (more than 1%), summarize every SS all runs