使用DBMS_HPROF进行PL/SQL代码探查
来源：互联网 发布：windows界面设计工具 编辑：程序博客网 时间：2024/05/16 00:30
--使用DBMS_HPROF进行PL/SQL代码探查
通过包dbms_hprof进行管理的调用级别探查器（也称为分层探查器，hirearchical profiler）。
优势：
1.开启后对开销影响非常小。
2.提供调用级别的信息。
3.有self time和total time的概念。
4.并不需要附加的权限。
通过包dbms_hprof进行管理的调用级别探查器（也称为分层探查器，hirearchical profiler）。
优势：
1.开启后对开销影响非常小。
2.提供调用级别的信息。
3.有self time和total time的概念。
4.并不需要附加的权限。
5.支持native-compiled PL/SQL。
--实验SYS@PROD1> CREATE OR REPLACE PROCEDURE perfect_triangles(p_max IN INTEGER) IS 2 hyp NUMBER; 3 ihyp INTEGER; 4 TYPE side_r IS RECORD(short INTEGER, long INTEGER); 5 TYPE sides_t IS TABLE OF side_r INDEX BY BINARY_INTEGER; 6 unique_sides sides_t; 7 dup_sides sides_t; 8 n integer :=0; 9 m integer :=0; 10 11 PROCEDURE store_dup_sides(p_long IN INTEGER, p_short IN INTEGER) IS 12 mult INTEGER := 2; 13 long_mult INTEGER := p_long*2; 14 short_mult INTEGER := p_short*2; 15 BEGIN 16 WHILE long_mult < p_max OR short_mult < p_max 17 LOOP 18 n := n+1; 19 dup_sides(n).long := long_mult; 20 dup_sides(n).short := short_mult; 21 mult := mult+1; 22 long_mult := p_long*mult; 23 short_mult := p_short*mult; 24 END LOOP; 25 END store_dup_sides; 26 27 FUNCTION sides_are_unique(p_long IN INTEGER, p_short IN INTEGER) RETURN BOOLEAN IS 28 BEGIN 29 FOR j IN 1..n 30 LOOP 31 IF p_long = dup_sides(j).long 32 AND 33 p_short = dup_sides(j).short 34 THEN 35 RETURN FALSE; 36 END IF; 37 END LOOP; 38 RETURN TRUE; 39 END sides_are_unique; 40 41 BEGIN 42 FOR long IN 1..p_max 43 LOOP 44 FOR short IN 1..long 45 LOOP 46 hyp := sqrt(long*long + short*short); 47 ihyp := floor(hyp); 48 IF hyp-ihyp < 0.01 49 THEN 50 IF ihyp*ihyp = long*long + short*short 51 THEN 52 IF sides_are_unique(long, short) 53 THEN 54 m := m+1; 55 unique_sides(m).long := long; 56 unique_sides(m).short := short; 57 store_dup_sides(long, short); 58 END IF; 59 END IF; 60 END IF; 61 END LOOP; 62 END LOOP; 63 FOR j IN 1..m 64 LOOP 65 dbms_output.put_line('.' || 66 lpad(unique_sides(j).long, 4,' ')|| 67 lpad(unique_sides(j).short,4,' ')); 68 END LOOP; 69 END perfect_triangles; 70 /SYS@PROD1> SYS@PROD1> CREATE OR REPLACE PACKAGE perfect_triangles_pck IS 2 PROCEDURE run(p_max IN INTEGER); 3 END perfect_triangles_pck; 4 /SYS@PROD1> SYS@PROD1> CREATE OR REPLACE PACKAGE BODY perfect_triangles_pck IS 2 PROCEDURE run(p_max IN INTEGER) IS 3 BEGIN 4 perfect_triangles(p_max); 5 END run; 6 END perfect_triangles_pck; 7 /SYS@PROD1> SYS@PROD1> CREATE OR REPLACE TYPE perfect_triangles_typ AS OBJECT ( 2 dummy NUMBER, 3 STATIC PROCEDURE run(p_max IN INTEGER) 4 ); 5 /SYS@PROD1> SYS@PROD1> CREATE OR REPLACE TYPE BODY perfect_triangles_typ IS 2 STATIC PROCEDURE run(p_max IN INTEGER) IS 3 BEGIN 4 perfect_triangles(p_max); 5 END run; 6 END; 7 /SYS@PROD1> SYS@PROD1> SYS@PROD1> SYS@PROD1> CREATE DIRECTORY plshprof_dir AS '&directory_path';Enter value for directory_path: /home/oracle/ext/SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Install the profiler tablesSYS@PROD1> REMSYS@PROD1> SYS@PROD1> @?/rdbms/admin/dbmshptab.sqlSYS@PROD1> RemSYS@PROD1> Rem $Header: dbmshptab.sql 30-jul-2007.13:07:41 sylin Exp $SYS@PROD1> RemSYS@PROD1> Rem dbmshptab.sqlSYS@PROD1> RemSYS@PROD1> Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.SYS@PROD1> RemSYS@PROD1> Rem NAMESYS@PROD1> Rem dbmshptab.sql - dbms hierarchical profiler table creationSYS@PROD1> RemSYS@PROD1> Rem DESCRIPTIONSYS@PROD1> Rem Create tables for the dbms hierarchical profilerSYS@PROD1> RemSYS@PROD1> Rem NOTESSYS@PROD1> Rem The following tables are required to collect data:SYS@PROD1> Rem dbmshp_runsSYS@PROD1> Reminformation on hierarchical profiler runsSYS@PROD1> RemSYS@PROD1> Rem dbmshp_function_info -> Rem information on each function profiledSYS@PROD1> RemSYS@PROD1> Rem dbmshp_parent_child_info -> Rem parent-child level profiler informationSYS@PROD1> RemSYS@PROD1> Rem The dbmshp_runnumber sequence is used for generating uniqueSYS@PROD1> Rem run numbers.SYS@PROD1> RemSYS@PROD1> Rem The tables and sequence can be created in the schema for each userSYS@PROD1> Rem who wants to gather profiler data. Alternately these tables can beSYS@PROD1> Rem created in a central schema. In the latter case the user creatingSYS@PROD1> Rem these objects is responsible for granting appropriate privilegesSYS@PROD1> Rem (insert,update on the tables and select on the sequence) to allSYS@PROD1> Rem users who want to store data in the tables. Appropriate synonymsSYS@PROD1> Rem must also be created so the tables are visible from other userSYS@PROD1> Rem schemas.SYS@PROD1> RemSYS@PROD1> Rem THIS SCRIPT DELETES ALL EXISTING DATA!SYS@PROD1> RemSYS@PROD1> Rem MODIFIED (MM/DD/YY)SYS@PROD1> Rem sylin 07/30/07 - Modify foreign key constraints with on deleteSYS@PROD1> Rem cascade clauseSYS@PROD1> Rem kmuthukk 06/13/06 - fix commentsSYS@PROD1> Rem sylin 03/15/05 - CreatedSYS@PROD1> RemSYS@PROD1> SYS@PROD1> drop table dbmshp_runs cascade constraints;drop table dbmshp_runs cascade constraints *ERROR at line 1:ORA-00942: table or view does not existSYS@PROD1> drop table dbmshp_function_info cascade constraints;drop table dbmshp_function_info cascade constraints *ERROR at line 1:ORA-00942: table or view does not existSYS@PROD1> drop table dbmshp_parent_child_info cascade constraints;drop table dbmshp_parent_child_info cascade constraints *ERROR at line 1:ORA-00942: table or view does not existSYS@PROD1> SYS@PROD1> drop sequence dbmshp_runnumber;drop sequence dbmshp_runnumber *ERROR at line 1:ORA-02289: sequence does not existSYS@PROD1> SYS@PROD1> create table dbmshp_runs 2 ( 3 runid number primary key,-- unique run identifier, 4 run_timestamp timestamp, 5 total_elapsed_time integer, 6 run_comment varchar2(2047) -- user provided comment for this run 7 );SYS@PROD1> SYS@PROD1> comment on table dbmshp_runs is 2 'Run-specific information for the hierarchical profiler';SYS@PROD1> SYS@PROD1> create table dbmshp_function_info 2 ( 3 runid number references dbmshp_runs on delete cascade, 4 symbolid number, -- unique internally generated 5 -- symbol id for a run 6 owner varchar2(32), -- user who started run 7 module varchar2(32), -- module name 8 type varchar2(32), -- module type 9 function varchar2(4000), -- function name 10 line# number, -- line number where function 11 -- defined in the module. 12 hash raw(32) DEFAULT NULL, -- hash code of the method. 13 -- name space/language info (such as PL/SQL, SQL) 14 namespace varchar2(32) DEFAULT NULL, 15 -- total elapsed time in this symbol (including descendats) 16 subtree_elapsed_time integer DEFAULT NULL, 17 -- self elapsed time in this symbol (not including descendants) 18 function_elapsed_time integer DEFAULT NULL, 19 -- number of total calls to this symbol 20 calls integer DEFAULT NULL, 21 -- 22 primary key (runid, symbolid) 23 );SYS@PROD1> SYS@PROD1> comment on table dbmshp_function_info is 2 'Information about each function in a run';SYS@PROD1> SYS@PROD1> create table dbmshp_parent_child_info 2 ( 3 runid number, -- unique (generated) run identifier 4 parentsymid number, -- unique parent symbol id for a run 5 childsymid number, -- unique child symbol id for a run 6 -- total elapsed time in this symbol (including descendats) 7 subtree_elapsed_time integer DEFAULT NULL, 8 -- self elapsed time in this symbol (not including descendants) 9 function_elapsed_time integer DEFAULT NULL, 10 -- number of calls from the parent 11 calls integer DEFAULT NULL, 12 -- 13 foreign key (runid, childsymid) 14 references dbmshp_function_info(runid, symbolid) on delete cascade, 15 foreign key (runid, parentsymid) 16 references dbmshp_function_info(runid, symbolid) on delete cascade 17 );SYS@PROD1> SYS@PROD1> comment on table dbmshp_parent_child_info is 2 'Parent-child information from a profiler runs';SYS@PROD1> SYS@PROD1> create sequence dbmshp_runnumber start with 1 nocache;SYS@PROD1> SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Enable the profilerSYS@PROD1> REMSYS@PROD1> SYS@PROD1> BEGIN 2 dbms_hprof.start_profiling(location => 'PLSHPROF_DIR', 3 filename => 'dbms_hprof.trc'); 4 END; 5 /SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Execute the procedure perfect_trianglesSYS@PROD1> REMSYS@PROD1> SYS@PROD1> SET TIMING ONSYS@PROD1> DECLARE 2 l_count INTEGER; 3 BEGIN 4 perfect_triangles(1000); 5 SELECT count(*) INTO l_count 6 FROM all_objects; 7 END; 8 /Elapsed: 00:00:01.62SYS@PROD1> SET TIMING OFFSYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Stop the profilerSYS@PROD1> REMSYS@PROD1> SYS@PROD1> BEGIN 2 dbms_hprof.stop_profiling; 3 END; 4 /SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Load profiling data into output tablesSYS@PROD1> REMSYS@PROD1> SYS@PROD1> SELECT dbms_hprof.analyze(location => 'PLSHPROF_DIR', 2 filename => 'dbms_hprof.trc') AS runid 3 FROM dual; RUNID---------- 1SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM Display the information gathered by the profilerSYS@PROD1> REMSYS@PROD1> SYS@PROD1> REM NamespacesSYS@PROD1> 将探查数据按照命名空间进行分组，PLSQL在此占用的响应时间比例为36.1。SYS@PROD1> SELECT sum(function_elapsed_time)/1000 AS total_ms, 2 100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent, 3 sum(calls) AS calls, 4 100*ratio_to_report(sum(calls)) over () AS calls_percent, 5 namespace AS namespace_name 6 FROM dbmshp_function_info 7 WHERE runid = &runid 8 GROUP BY namespace 9 ORDER BY total_ms DESC;TOTAL [ms] TOT% CALLS CAL% NAMESPACE_NAME---------- ------ ---------- ------ --------------- 992 63.9 1084.9 SQL 561 36.1 2,085 95.1 PLSQLSYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REM ModulesSYS@PROD1> 按模块级别分组SYS@PROD1> SELECT sum(function_elapsed_time)/1000 AS total_ms, 2 100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent, 3 sum(calls) AS calls, 4 100*ratio_to_report(sum(calls)) over () AS calls_percent, 5 namespace, 6 nvl(nullif(owner || '.' || module, '.'), function) AS module_name, 7 type 8 FROM dbmshp_function_info 9 WHERE runid = &runid 10 GROUP BY namespace, nvl(nullif(owner || '.' || module, '.'), function), type 11 ORDER BY total_ms DESC;TOTAL [ms] TOT% CALLS CAL% NAMESPACE MODULE_NAME---------- ------ ---------- ------ --------- ------------------------------TYPE-------------------------------- 654 42.1 10.0 SQL __static_sql_exec_line5 557 35.9 1,214 55.4 PLSQL SYS.PERFECT_TRIANGLESPROCEDURE 339 21.8 1074.9 SQL SYS.XML_SCHEMA_NAME_PRESENTPACKAGE BODY 1 0.1 542.5 PLSQL SYS.XML_SCHEMA_NAME_PRESENTPACKAGE BODY 1 0.1 730 33.3 PLSQL SYS.DBMS_OUTPUTPACKAGE BODY 1 0.1 150.7 PLSQL __anonymous_block 0 0.0 562.6 PLSQL __plsql_vm@1 0 0.0 150.7 PLSQL __plsql_vm 0 0.0 10.0 PLSQL SYS.DBMS_HPROFPACKAGE BODYSYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REM Call hierarchySYS@PROD1> SYS@PROD1> SELECT lpad(' ', (level-1) * 2) || nullif(c.owner || '.', '.') || 2 CASE WHEN c.module = c.function THEN c.function ELSE nullif(c.module || '.', '.') || c.function END AS function_name, 3 pc.subtree_elapsed_time/1000 AS total_ms, 4 pc.function_elapsed_time/1000 AS function_ms, 5 pc.calls AS calls 6 FROM dbmshp_parent_child_info pc, 7 dbmshp_function_info p, 8 dbmshp_function_info c 9 START WITH pc.runid = &runid 10 AND p.runid = pc.runid 11 AND c.runid = pc.runid 12 AND pc.childsymid = c.symbolid 13 AND pc.parentsymid = p.symbolid 14 AND p.symbolid = 1 15 CONNECT BY pc.runid = prior pc.runid 16 AND p.runid = pc.runid 17 AND c.runid = pc.runid 18 AND pc.childsymid = c.symbolid 19 AND pc.parentsymid = p.symbolid 20 AND prior pc.childsymid = pc.parentsymid 21 ORDER SIBLINGS BY total_ms DESC;FUNCTION NAME TOTAL [ms] FUNCTION [ms]---------------------------------------------------------------------- ---------- ------------- CALLS----------__static_sql_exec_line5 994 654 1 __plsql_vm@1 340 056 SYS.XML_SCHEMA_NAME_PRESENT.IS_SCHEMA_PRESENT 340 154 SYS.XML_SCHEMA_NAME_PRESENT.__dyn_sql_exec_line17 310 31054 SYS.XML_SCHEMA_NAME_PRESENT.__dyn_sql_exec_line34 28 2853SYS.PERFECT_TRIANGLES 558 459 1 SYS.PERFECT_TRIANGLES.PERFECT_TRIANGLES.SIDES_ARE_UNIQUE 93 93 1,034 SYS.PERFECT_TRIANGLES.PERFECT_TRIANGLES.STORE_DUP_SIDES5 5 179 SYS.DBMS_OUTPUT.PUT_LINE1 0 179 SYS.DBMS_OUTPUT.PUT 0 0 179 SYS.DBMS_OUTPUT.NEW_LINE0 0 179SYS.DBMS_OUTPUT.GET_LINES1 113 SYS.DBMS_OUTPUT.GET_LINE0 0 180SYS.DBMS_HPROF.STOP_PROFILING0 0 1SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REM FunctionsSYS@PROD1> SYS@PROD1> SELECT c.subtree_elapsed_time/1000 AS total_ms, 2 c.subtree_elapsed_time*100/t.total AS total_percent, 3 c.function_elapsed_time/1000 AS function_ms, 4 c.function_elapsed_time*100/t.total AS function_percent, 5 (c.subtree_elapsed_time-c.function_elapsed_time)/1000 AS descendants_ms, 6 (c.subtree_elapsed_time-c.function_elapsed_time)*100/t.total AS descendants_percent, 7 c.calls AS calls, 8 c.calls*100/t.tcalls AS calls_percent, 9 nullif(c.owner || '.', '.') || 10 CASE WHEN c.module = c.function THEN c.function ELSE nullif(c.module || '.', '.') || c.function END || 11 CASE WHEN c.line# = 0 THEN '' ELSE ' (line '||c.line#||')' END AS function_name 12 FROM dbmshp_function_info c, 13 (SELECT max(subtree_elapsed_time) AS total, 14 sum(calls) AS tcalls 15 FROM dbmshp_function_info 16 WHERE runid = &runid) t 17 WHERE c.runid = &runid 18 ORDER BY total_ms DESC;TOTAL [ms] TOT% FUNCTION [ms] FCT% DESCENDANTS [ms] DESC% CALLS CAL%---------- ------ ------------- ------ ---------------- ------ ---------- ------FUNCTION NAME---------------------------------------------------------------------- 1,553 100.0 0 0.0 1,553 100.0 15 0.7__plsql_vm 1,553 100.0 1 0.1 1,552 99.9 15 0.7__anonymous_block 994 64.0 654 42.1 340 21.91 0.0__static_sql_exec_line5 (line 5) 558 35.9 459 29.6 99 6.31 0.0SYS.PERFECT_TRIANGLES (line 1) 340 21.9 0 0.0 340 21.9 56 2.6__plsql_vm@1 340 21.9 1 0.1 339 21.8 54 2.5SYS.XML_SCHEMA_NAME_PRESENT.IS_SCHEMA_PRESENT (line 3) 310 20.0 310 20.0 0 0.0 54 2.5SYS.XML_SCHEMA_NAME_PRESENT.__dyn_sql_exec_line17 (line 17)93 6.0 93 6.0 0 0.0 1,034 47.2SYS.PERFECT_TRIANGLES.PERFECT_TRIANGLES.SIDES_ARE_UNIQUE (line 27)28 1.8 28 1.8 0 0.0 53 2.4SYS.XML_SCHEMA_NAME_PRESENT.__dyn_sql_exec_line34 (line 34) 5 0.3 5 0.3 0 0.0 179 8.2SYS.PERFECT_TRIANGLES.PERFECT_TRIANGLES.STORE_DUP_SIDES (line 11) 1 0.0 1 0.0 0 0.0 13 0.6SYS.DBMS_OUTPUT.GET_LINES (line 180) 1 0.0 0 0.0 0 0.0 179 8.2SYS.DBMS_OUTPUT.PUT_LINE (line 109) 0 0.0 0 0.0 0 0.0 179 8.2SYS.DBMS_OUTPUT.PUT (line 77) 0 0.0 0 0.0 0 0.0 180 8.2SYS.DBMS_OUTPUT.GET_LINE (line 129) 0 0.0 0 0.0 0 0.0 179 8.2SYS.DBMS_OUTPUT.NEW_LINE (line 117) 0 0.0 0 0.0 0 0.01 0.0SYS.DBMS_HPROF.STOP_PROFILING (line 59)SYS@PROD1> SYS@PROD1> PAUSESYS@PROD1> SYS@PROD1> REMSYS@PROD1> REM CleanupSYS@PROD1> REMSYS@PROD1> SYS@PROD1> DROP PROCEDURE perfect_triangles;SYS@PROD1> SYS@PROD1> DROP DIRECTORY plshprof_dir;SYS@PROD1> SYS@PROD1> UNDEFINE runid--还可以使用命令行工具PLSHPROF来处理生成的跟踪文件，产生HTML报告可视化。[oracle@ocm1 ext]$ plshprof -output hprof dbms_hprof.trc PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production[16 symbols processed][Report written to 'hprof.html'][oracle@ocm1 ext]$ lsdbms_hprof.trc hprof_2f.html hprof_mf.html hprof_pc.html import.logex.dmp hprof_2n.html hprof_ms.html hprof_tc.htmlexport.log hprof_fn.html hprof_nsc.html hprof_td.htmlex_tbs.dmp hprof.html hprof_nsf.html hprof_tf.htmlhprof_2c.html hprof_md.html hprof_nsp.html hprof_ts.html 0 0
- 使用DBMS_HPROF进行PL/SQL代码探查
- 使用DBMS_HPROF包收集PL/SQL性能信息
- 使用PL/Scope分析PL/SQL代码
- 使用PL/SQL Developer剖析PL/SQL代码
- 使用PL/SQL Developer剖析PL/SQL代码
- 如何使用SQL 事件探查器？
- SQL Server事件探查器使用技巧
- SQL SERVER:事件探查器的使用
- 使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
- 使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
- 使用PL/SQL执行计划进行sql调优
- pl/sql代码块
- PL/SQL代码举例
- SQL事件探查器使用介绍(1587字)
- SQL 事件探查器的提示和技巧的使用
- SQL Server 2000 “事件探查器”的简单使用
- 使用 SQL Server 事件探查器的方案
- SQL事件探查器，你使用了吗？
- 函数
- Linux发展史简述
- 使用二分法查找指定数据
- 使用IDEA导入工程时无反映的问题处理
- redis和memcache比较优缺点
- 使用DBMS_HPROF进行PL/SQL代码探查
- 简学Python第三章__函数式编程、递归、内置函数
- 今天是2017.2.27，计划开始了
- C++第1次实验-两点距离
- css 清除浮动的几种方法
- Win10开机“提示语音”以及”随机播放音乐”
- Android单元测试框架源码分析（三）构建自己的单元测试框架
- 第一次c++实验：y的分段函数
- Scrapy 1.3.2
