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

Using 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

[SQL] -- demo environment sys @ USBO> select * from v $ version where rownum <2; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production a. Use the schema execution script proftab that requires plsql profiling. SQL (you can also use an account to create and then create a synonym and authorize it to the public) -- when executed for the first time, the corresponding table will be created to store the profiler information, that is, plsql_profiler_runs, plsql_profi Ler_data sys @ USBO> conn scott/tiger; Connected. scott @ USBO> @? /Rdbms/admin/proftab. SQL B. 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)

[SQL] a. Start profiler. Call start_profiler scott @ USBO> execute dbms_profiler.start_profiler ('liters'); B. execute the code you need to parse (package, process, anonymous block, etc) scott @ USBO> exec literals; c. Stop profiler. Call stop_profiler scott @ USBO> execute dbms_profiler.stop_profiler; d. View 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 limit ------------- ---------- -------- 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 row can be reduced, the overall performance will be significantly improved. 3. Use PROFILER to analyze PLSQL code (method 2)

[SQL] -- this method does not actually change much, only encapsulate the code to be parsed, enable profiler, and stop profiler into an SQL statement. The following creates an example to use the bound variable for profiling 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 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, the original execution time of the insert statement is reduced from 49.9s to 3.756 s. For this example, we only demonstrate how to locate the bottleneck code, bind variables to improve performance. For other cases, analyze the code used to modify the bottleneck. 4. Use the script in the example.

[sql] a、chk_profile.sql  --file_name: chk_profile.sql  set linesize 190  column text format a100 wrap  column total_time format 99999.9  column min_time format 99999.9  column max_time format 99999.9  select s.text ,         p.total_occur ,         p.total_time/1000000000 total_time,         p.min_time/1000000000 min_time,         p.max_time/1000000000 max_time  from plsql_profiler_data p, user_source s, plsql_profiler_runs r  where p.line# = s.line  and   p.runid = r.runid  and   r.run_comment = '&input_comment_name'  and   s.name =upper('&input_sp_name');      b、call_profiler  --file_name:call_profiler.sql  SET HEAD OFF  SET PAGES 0    SELECT DECODE (DBMS_PROFILER.start_profiler, '0', 'Profiler started', 'Profiler error') FROM DUAL;    -------you can put you plsql code in below block------------    begin      binds;  end;  /  ---------------------------------------------------------------    SELECT DECODE (DBMS_PROFILER.stop_profiler, '0', 'Profiler stopped', 'Profiler error') FROM DUAL;    SELECT DECODE (DBMS_PROFILER.flush_data, '0', 'Profiler flushed', 'Profiler error') FROM DUAL;    SELECT 'runid:' || plsql_profiler_runnumber.CURRVAL FROM DUAL;    SET HEAD ON  SET PAGES 200      c、evaluate_profiler_results.sql   --file_name:evaluate_profiler_results.sql   undef runid  undef owner  undef name  set verify off  col text format a60 wrap  SELECT s.line "Line"       , p.total_occur "Occur"       , p.total_time "sec"       , s.text "Text"  FROM   all_source s       , (SELECT u.unit_owner               , u.unit_name               , u.unit_type               , d.line#               , d.total_occur               , round(d.total_time / 1000000000,3) total_time          FROM   plsql_profiler_data d, plsql_profiler_units u          WHERE  u.runid = &&runid AND u.runid = d.runid AND u.unit_number = d.unit_number) p  WHERE      s.owner = p.unit_owner(+)         AND s.name = p.unit_name(+)         AND s.TYPE = p.unit_type(+)         AND s.line = p.line#(+)         AND s.name = UPPER ( '&&name' )         AND s.owner = UPPER ( '&&owner' )  ORDER BY s.line;    SELECT exec.cnt / total.cnt * 100 "Code% coverage"  FROM   (SELECT COUNT ( 1 ) cnt          FROM   plsql_profiler_data d, plsql_profiler_units u          WHERE      d.runid = &&runid                 AND u.runid = d.runid                 AND u.unit_number = d.unit_number                 AND u.unit_name = UPPER ( '&&name' )                 AND u.unit_owner = UPPER ( '&&owner' )) total       , (SELECT COUNT ( 1 ) cnt          FROM   plsql_profiler_data d, plsql_profiler_units u          WHERE      d.runid = &&runid                 AND u.runid = d.runid                 AND u.unit_number = d.unit_number                 AND u.unit_name = UPPER ( '&&name' )                 AND u.unit_owner = UPPER ( '&&owner' )                 AND d.total_occur > 0) exec;    undef runid  undef owner  undef name