Use the DBMS_HPROF package to collect PL/SQL Performance Information

The DBMS_HPROF package is an oracle11g tool. It is a combination of DBMS_PROFILER and DBMS_TRACE. Here is a simple demonstration of how to use the DBMS_HPROF package to analyze the stored procedure information.

The DBMS_HPROF package is a tool that appears in oracle 11 GB. It is a combination of DBMS_PROFILER and DBMS_TRACE. Here is a simple demonstration of how to use the DBMS_HPROF package to analyze the stored procedure information.

The DBMS_HPROF package is a tool for Oracle 11 GB and is a combination of DBMS_PROFILER and DBMS_TRACE.

Here is a simple demonstration of how to use the DBMS_HPROF package to analyze the stored procedure performance information.

First, we need to execute a script to generate a table to save the relevant information.

Run $ ORACLE_HOME/rdbms/admin/dbmshptab. SQL by using SYS.

Then create directory,

Create directory LOG_FILE_DIR AS '/home/oracle ';

Use SCOTT to create a test Stored Procedure insert_emp.

23:58:57 @ ORCL> conn scott/tiger @ orcl
Connected.
At 23:59:35 SCOTT @ orcl> create or replace procedure insert_emp (pempno number, pename varchar2) IS
23:59:45 2. stmt VARCHAR2 (2000 );
23:59:45 3 BEGIN
23:59:45 4 stmt: = 'insert INTO emp_bak'
23:59:45 5 | '(EMPNO, ENAME )'
23:59:45 | 'select: EMPNO,: ename'
23:59:45 7 | 'from dual ';
8: 23:59:45 dbms_output.put_line (stmt );
23:59:45 9 execute immediate stmt
23:59:45 10 USING pempno, pename;
23:59:45 END insert_emp;
23:59:46 12/

Procedure created.

Elapsed: 00:00:00. 44

The demo is performed under the SYS user. If you want to execute the command under the specified user, you need to grant the execution permission of the DBMS_HPROF package and the read/write permission of the directory to the user.

Collect the performance metrics of the created Stored Procedure

00:06:11 SYS @ orcl> DECLARE
00:10:42 2 v_runid dbmshp_runs.runid % TYPE;
00:10:42 3 v_plshprof_dir all_directories.directory_name % TYPE: = 'Log _ FILE_DIR ';
00:10:42 4 v_plshprof_file VARCHAR2 (30): = 'insert _ emp ';
00:10:42 5 BEGIN
00:10:42 6 -- Start the profiling session
00:10:42 7 dbms_hprof.start_profiling (v_plshprof_dir, v_plshprof_file );
00:10:42 scott. insert_emp (9990, 'cryking! ');
00:10:42 9 -- Stop the profiling session
00:10:42 10 dbms_hprof.stop_profiling;
00:10:42 -- Analyze the raw output and create the table data
00:10:42 12 v_runid: = dbms_hprof.analyze (v_plshprof_dir, v_plshprof_file );
00:10:42 13 DBMS_OUTPUT.PUT_LINE ('this Run: '| to_char (v_runid ));
00:10:42 END;
00:10:42/
Insert into emp_bak (EMPNO, ENAME) SELECT: EMPNO,: ENAME FROM DUAL
This Run: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 26

After execution, an insert_emp file will be generated in the corresponding directory. If you directly view the file, the content will be messy and brief, as shown below:

P #! PL/SQL Timer Started
P # c plsql. "SCOTT". "INSERT_EMP": 7. "INSERT_EMP" # 762ba075453b8b0d #1
P # X 1331
P # c plsql. "SYS". "DBMS_OUTPUT": 11. "PUT_LINE" #5892e4d73b579470 #109
P # X 4
P # c plsql. "SYS". "DBMS_OUTPUT": 11. "PUT" #5892e4d73b579470 #77
P # X 220
P # R
P # X 2
P # c plsql. "SYS". "DBMS_OUTPUT": 11. "NEW_LINE" # 980980e97e42f8ec #117
P # X 4
P # R
P # X 3
P # R
P # X 83
P # c SQL. "SCOTT". "INSERT_EMP": 7. "_ dyn_ SQL _exec_line9" #9
P # X 39858
P # R
P # X 14
P # R
P # c plsql. "SYS". "DBMS_HPROF": 11. "STOP_PROFILING" # 980980e97e42f8ec #59
P # R
P #! PL/SQL Timer Stopped

Here we need to use the tool plshprof for analysis, as shown below:

[Oracle @ oracleserver ~] $ Plshprof insert_emp

PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
[6 symbols processed]
[Report written to 'insert_emp.html ']

[Oracle @ oracleserver ~] $ Ls *. html
Insert_emp_2c.html insert_emp_fn.html insert_emp_mf.html insert_emp_nsf.html insert_emp_tc.html insert_emp_ts.html
Insert_emp_2f.html insert_emp.html insert_emp_ms.html insert_emp_nsp.html insert_emp_td.html
Insert_emp_2n.html insert_emp_md.html insert_emp_nsc.html insert_emp_pc.html insert_emp_tf.html

Then, we will upload the insert_emp.html file, as shown below:

PL/SQL Elapsed Time (microsecs) Analysis for 'insert _ emp'

PL/SQL Elapsed Time (microsecs) Analysis
41519 microsecs (elapsed time) & 6 function CILS
The PL/SQL Hierarchical Profiler produces a collection of reports that
Present information derived from the profiler's output log in a variety
Of formats. The following reports have been found to be the most generally
Useful as starting points for browsing:

• 
  Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs)
  


• 
  Function Elapsed Time (microsecs) Data sorted by Total Function Elapsed Time (microsecs)
  

In addition, the following reports are also available:

• 
  Function Elapsed Time (microsecs) Data sorted by Function Name
  


• 
  Function Elapsed Time (microsecs) Data sorted by Total Descendants Elapsed Time (microsecs)
  


• 
  Function Elapsed Time (microsecs) Data sorted by Total Function Call Count
  


• 
  Function Elapsed Time (microsecs) Data sorted by Mean Subtree Elapsed Time (microsecs)
  


• 
  Function Elapsed Time (microsecs) Data sorted by Mean Function Elapsed Time (microsecs)
  


• 
  Function Elapsed Time (microsecs) Data sorted by Mean Descendants Elapsed Time (microsecs)
  


• 
  Module Elapsed Time (microsecs) Data sorted by Total Function Elapsed Time (microsecs)
  .......

  You can click the links in the browser to view the results. For example, after you click Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs), the result is as follows:

  As shown in the figure, the most time-consuming statement of the function is SCOTT. INSERT_EMO. _ dyn_ SQL _exec_line9 (that is, the row that executes dynamic SQL), which takes 39858 microseconds and the total time of the function is 41519 microseconds.

  You can also click the Function Name column to view more details.

  PS: in fact, most of the generated HTML information is from the dbmshp_function_info and dbmshp_parent_child_info tables.

  ,