SQL性能信息

DBMS_HPROF包是Oracle 11g出现的工具,是DBMS_PROFILER和DBMS_TRACE的综合.这里简单演示如何使用DBMS_HPROF包来分析存储过程性能信息.首先我们需要执行一个脚本来生成保存相关信息的表.使用SYS用户执行$ORACLE_HOME/rdbms/admin/dbmshptab.sql然后创建directory,CREATE DIRECTORY LOG_FILE_DIR AS "/home/oracle";先用SCOTT用户创建一个用于测试的存储过程insert_emp.23:58:57 @ORCL> conn scott/tiger@orcl
Connected.
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 6 || "SELECT :EMPNO,:ENAME"
23:59:45 7 || " FROM DUAL";
23:59:45 8 dbms_output.put_line（stmt）;
23:59:45 9 EXECUTE IMMEDIATE stmt
23:59:45 10 USING pempno, pename;
23:59:45 11 END insert_emp;
23:59:46 12 /Procedure created.Elapsed: 00:00:00.44这里演示是在SYS用户下进行,如果要到指定用户执行,需要将DBMS_HPROF包的执行权限以及目录的读写权限赋予给该用户.收集刚建立的存储过程的性能指标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 8 scott.insert_emp（9990,"Cryking！"）;
00:10:42 9 -- Stop the profiling session
00:10:42 10 dbms_hprof.stop_profiling;
00:10:42 11 -- 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 14 END;
00:10:42 15 /
INSERT INTO emp_bak （EMPNO,ENAME） SELECT :EMPNO,:ENAME FROM DUAL
This Run: 1PL/SQL procedure successfully completed.Elapsed: 00:00:00.26执行完后,会在对应目录生成一个insert_emp的文件,直接查看文件的话,内容很凌乱也很简短,如下: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这里需要使用工具plshprof对其进行分析,如下:[oracle@oracleserver ~]$ plshprof insert_empPLSHPROF: 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然后我们再查看文件insert_emp.html的内容,如下:<HTML lang="en">
<HEAD>
<TITLE>PL/SQL Elapsed Time （microsecs） Analysis for "insert_emp"</TITLE>
</HEAD>
<BODY><H1>PL/SQL Elapsed Time （microsecs） Analysis</H1>
<H3>41519 microsecs （elapsed time） & 6 function calls</H3>
<H3>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:</H3>
<UL>
<LI> <A HREF = insert_emp_ts.html>
Function Elapsed Time （microsecs） Data sorted by Total Subtree Elapsed Time （microsecs）
</A> </LI>
<LI> <A HREF = insert_emp_tf.html>
Function Elapsed Time （microsecs） Data sorted by Total Function Elapsed Time （microsecs）
</A> </LI>
</UL>
<H3>In addition, the following reports are also available:</H3>
<UL>
<LI> <A HREF = insert_emp_fn.html>
Function Elapsed Time （microsecs） Data sorted by Function Name
</A> </LI>
<LI> <A HREF = insert_emp_td.html>
Function Elapsed Time （microsecs） Data sorted by Total Descendants Elapsed Time （microsecs）
</A> </LI>
<LI> <A HREF = insert_emp_tc.html>
Function Elapsed Time （microsecs） Data sorted by Total Function Call Count
</A> </LI>
<LI> <A HREF = insert_emp_ms.html>
Function Elapsed Time （microsecs） Data sorted by Mean Subtree Elapsed Time （microsecs）
</A> </LI>
<LI> <A HREF = insert_emp_mf.html>
Function Elapsed Time （microsecs） Data sorted by Mean Function Elapsed Time （microsecs）
</A> </LI>
<LI> <A HREF = insert_emp_md.html>
Function Elapsed Time （microsecs） Data sorted by Mean Descendants Elapsed Time （microsecs）
</A> </LI>
<LI> <A HREF = insert_emp_2f.html>
Module Elapsed Time （microsecs） Data sorted by Total Function Elapsed Time （microsecs）
.......通过浏览器查看,我们可以点击各个链接,查看,如点击Function Elapsed Time （microsecs） Data sorted by Total Subtree Elapsed Time （microsecs）后,结果如下图:从图可知,函数最耗时的语句为SCOTT.INSERT_EMO._dyn_sql_exec_line9（即为执行动态sql那一行）,耗时39858微秒,整个函数共耗时41519微秒.还可以点击Function Name列查看更详细的内容.PS:其实生成的HTML大部分信息都是来自dbmshp_function_info和dbmshp_parent_child_info表.更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12SUSE11 SP2下Oracle 11g安装指南PL/SQL中三种游标循环效率对比相关资讯 PL/SQL DBMS_HPROF

PL/SQL之存储过程和函数（今 14:09）
PL/SQL Developer连接本地Oracle （07月27日）
【PL/SQL系列】Oracle存储过程使用（04月23日）

PL/SQL Developer 使用技巧分享（09月16日）
PL/SQL实现Java中的split（）方法的（07月10日）
从一个案例看PL/SQL代码片的编译与（03月04日）

本文评论查看全部评论（0）