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

For SQL optimization, you can use SQL tuning advisor to achieve automatic SQL optimization and adjustment. For PL/SQL code, there are both SQL code and PL/SQL code. It is far from enough to use only 10046 events, because the SQL time may be very short, the entire package or process is executed for a long time, and other packages, processes, and functions are nested in the package or process. It seems that you have a headache. Although no tool can be used directly to optimize PL/SQL code, PL/SQL PROFILER can be used to locate the performance bottleneck in your code block, which has achieved twice the result with half the effort. This article first describes how to install PROFILER. Next we will show how to define and compare the bottleneck block when using the literal volume in the PL/SQL block with the variable binding. The last part will list some related scripts.

1. Configure PROFILER and demo Environment

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

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

A. Run the script proftab. SQL using the schema that requires plsql profiling (you can also create a synonym using an account and grant it to public)
-- If it is executed for the first time, a corresponding table will be created to store profiler information, that is, plsql_profiler_runs, plsql_profiler_units, and plsql_profiler_data.
Sys @ USBO> conn scott/tiger;
Connected.
Scott @ USBO> @? /Rdbms/admin/proftab. SQL

B. Use the sysdba account to install DBMS_PROFILER and run the script profload. SQL.
Scott @ USBO> conn/as sysdba
Connected.
Sys @ USBO> @? /Rdbms/admin/profload. SQL

C. If necessary, create plan_table and execute the script utlxplan. SQL.
Sys @ USBO> @? /Rdbms/admin/utlxplan. SQL

Sys @ USBO> grant all on sys. plan_table TO public;

Sys @ USBO> create public synonym plan_table FOR sys. plan_table;

Sys @ USBO> conn scott/tiger;
Connected.

-- Create a demo table
Scott @ USBO> create table t1 (id number, val number );

-- Create a literal-based process
Scott @ USBO> create or replace procedure literals
2 is
3 v_num number;
4 begin
5 for I in 1 .. 100000 loop
6 v_num: = dbms_random.random;
7 execute immediate
8 'insert into t1 values ('| v_num |', '| v_num | ')';
9 end loop;
10 end;
11/

Procedure created.

2. Use PROFILER to analyze PLSQL code (method 1)

A. Start profiler and call start_profiler
Scott @ USBO> execute dbms_profiler.start_profiler ('literals ');

B. Execute the code you need to parse (packages, processes, anonymous blocks, etc)
Scott @ USBO> exec literals;

C. Stop profiler. The call process is stop_profiler.
Scott @ USBO> execute dbms_profiler.stop_profiler;

D. view the profiler report.
Scott @ USBO> @ chk_profile
Enter value for input_comment_name: literals
Enter value for input_sp_name: literals

TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
--------------------------------------------------------------------------------------------
Procedure literals 1. 0. 0. 0
Procedure literals 3. 0. 0. 0
Procedure literals 0. 0. 0. 0
For I in 1 .. 100000 loop 100001. 2. 0. 0
V_num: = dbms_random.random; 100000. 8. 0. 0
Execute immediate 100000 49.9. 0. 0
End; 1. 0. 0. 0
Procedure literals 2. 0. 0. 0

-- The above results show that execute immediate consumes 49s, that is, if the Code time of this line can be reduced, the overall performance will be greatly improved.

3. Use PROFILER to analyze PLSQL code (method 2)

-- This method does not actually change much, but encapsulates the code to be parsed, profiler enabled, and profiler stopped into an SQL statement.
-- The following is an example of variable binding.
Scott @ USBO> create or replace procedure binds
2 is
3 v_num number;
4 begin
5 for I in 1 .. 100000 loop
6 v_num: = dbms_random.random;
7 insert into t1 values (v_num, v_num );
8 end loop;
9 end;
10/

Procedure created.

-- Call call_profiler. SQL directly (this Code encapsulates start profiler and stop profiler)
Scott @ USBO> @ call_profiler
Profiler started

PL/SQL procedure successfully completed.

Profiler stopped

Profiler flushed

Runid: 4

-- Author: Leshami
-- Blog: http://blog.csdn.net/leshami

-- View the profiler report
Scott @ USBO> @ evaluate_profiler_results
Enter value for runid: 4
Enter value for name: binds
Enter value for owner: scott

Line Occur sec Text
------------------------------------------------------------------------------------------
1 0 0 procedure binds
2 is
3 v_num number;
4 begin
5 100001. 182 for I in 1 .. 100000 loop
6 100000. 498 v_num: = dbms_random.random;
7 100000 3.756 insert into t1 values (v_num, v_num );
8 end loop;
9 1 0 end;

9 rows selected.

Code % coverage
--------------
80

-- From the above report, we can see that after using the Bind Variable instead, the original execution time of the insert statement dropped from 49.9s to 3.756 s.
-- This example only demonstrates how to locate the bottleneck code and bind variables to improve performance. For other cases, we should analyze how to modify the bottleneck code.

Recommended reading:

Use PL/SQL to execute java storage to obtain the MAC address

Problems with PL/SQL functions in SQL

 

For more highlights, see the next page:

1 2 Next Page