Use DBMS_PROFILER to locate PL/SQL bottleneck code

References involved in this description
Bind variables and their advantages and disadvantages
Oracle hard parsing and soft Parsing
Oracle variable binding
What does SQL Tuning Advisor (STA) do?
Use SQL tuning advisor (STA) to automatically optimize SQL statements

1. Configure PROFILER and demo Environment

-- Demo environment sys @ USBO> select * from v $ version where rownum <2; BANNER executes Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Productiona and uses 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 is created to store profiler information, that is, plsql_profiler_runs, plsql_profiler_datasys @ USBO> conn scott/tiger; Connected. scott @ USBO> @? /Rdbms/admin/proftab. sqlb. Run the script profload. SQL scott @ USBO> conn/as sysdbaConnected. sys @ USBO> @? /Rdbms/admin/profload. sqlc. If necessary, create plan_table and execute the script utlxplan. sqlsys @ USBO> @? /Rdbms/admin/utlxplan. sqlsys @ 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.

A. Start profiler and call start_profilerscott @ USBO> execute dbms_profiler.start_profiler ('liters'); B. execute the code to be parsed (package, process, anonymous block, etc) scott @ USBO> exec literals; c. Stop profiler. Call stop_profilerscott @ USBO> execute statement; d. view the profiler report scott @ USBO> @ chk_profileEnter value for input_comment_name: literalsEnter value for input_sp_name: literalsTEXT 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 greatly improved.

-- 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_profilerProfiler startedPL/SQL procedure successfully completed. profiler stoppedProfiler flushedrunid: 4 -- Author: Leshami -- Blog: Author> @ evaluate_profiler_resultsEnter value for runid: 4 Enter value for name: bindsEnter 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 -- as shown in the preceding report, when bind a variable is used, the original execution time of the insert statement is reduced from 49.9s to 3.756 s. This example only demonstrates how to locate the bottleneck Code, instead, bind variables to improve performance. For other situations, we should analyze how to modify the bottleneck code.

a、chk_profile.sql--file_name: chk_profile.sqlset linesize 190column text format a100 wrapcolumn total_time format 99999.9column min_time format 99999.9column max_time format 99999.9select s.text ,       p.total_occur ,       p.total_time/1000000000 total_time,       p.min_time/1000000000 min_time,       p.max_time/1000000000 max_timefrom plsql_profiler_data p, user_source s, plsql_profiler_runs rwhere p.line# = s.lineand   p.runid = r.runidand   r.run_comment = '&input_comment_name'and   s.name =upper('&input_sp_name');b、call_profiler--file_name:call_profiler.sqlSET HEAD OFFSET PAGES 0SELECT 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 ONSET PAGES 200c、evaluate_profiler_results.sql --file_name:evaluate_profiler_results.sql undef runidundef ownerundef nameset verify offcol text format a60 wrapSELECT 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) pWHERE      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 runidundef ownerundef name