Compare the analysis results of PL/SQL profiler

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:

Problems with PL/SQL functions in SQL

 

 

For more highlights, see the next page:

1 2 Next Page