Oracle SQL Developer offers the ability to create reports with graphs in it. A number of reports are included with the tool, unfortunately a report which shows information based on DBMS_PROFILER built in package isn’t there. DBMS_PROFILER allows developers to profile the run-time behaviour of PL/SQL code, making it easier to identify performance issues. Because I couldn’t find an existing report to show this information, I created a couple of reports to show this information.
Using the Reports
After starting up SQL Developer, navigate to the Reports tab. Right click on "User Defined Reports" and choose "Import". Locate the Report file, which are included at the end of this blog, and that’s it. An extra folder is created, named "Profiler Reports" with two reports in it. After using DBMS_PROFILER, choose the Report titled "Run Overview".
To see more detailed information right click the run of choice and choose "Detailed Profile". (I haven’t yet figured out how to remove the other "Detailed Profile" or "Profiler Report" labels – these were other trial reports which no longer exist.) This will open up a second report with more detailed information.
The Graph shows you the execution time per line of PL/SQL code. The Line numbers are shown on the horizontal axis of the Graph. When you hover your mouse pointer over the graph, you will see a context popup with the line number and the source code text. Behind the Graph is another Tab, labelled "Source Code", which shows the source of the tested stored procedure.
Testing
To show the use of these reports, I used a simple Stored Procedure to demonstrate the efficiency of PLS_INTEGER when used as a loop-counter. There are three procedures which have a similar body:
begin for i in 1..100 loop num := num + 1; end loop; end;
The only thing that is different in these procedures is the datatype of the NUM variable.
Procedure | Datatype of NUM variable |
PNUM_UNRESTRICTED | NUMBER |
PNUM_RESTRICTED | NUMBER (3,0) |
PLS | PLS_INTEGER |
All of these procedures are included in the profiler_test package at the bottom of this post. To test these procedures using DBMS_PROFILER, you can use an anonymous block:
SQL> begin 2 dbms_profiler.start_profiler ('Private Counter Performance'); 3 profiler_test.pnum_unrestricted; 4 profiler_test.pnum_restricted; 5 profiler_test.pls; 6 dbms_profiler.stop_profiler (); 7 end; 8 / PL/SQL procedure successfully completed.
Based on this simple test, you can easily tell that the PLS_INTEGER is the preferred method as a local counter instead of a NUMBER. Hopefully you will have as much fun with these reports as I have. Any suggestions for improvement, let me know… or even better make the changes, drop me a line and I will put the reports up here for everybody to use (with full credits of course).
相关推荐
DBMS_PROFILER使用指南 介绍详细,从安装到使用都一目明了
支持ArcGIS10.2版本的PostgreSQL_DBMS_for_windows_922,ESRI官方原版资源。
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_...
Oracle DOM编程 文档,有要的没 Start from toc.htm DBMS_XMLDOM DBMS_XMLPARSER DBMS_XMLQUERY
oracle dbms_lob
oracle中DBMS_SQL的使用,详细讲解oracle DBMS_SQL的使用办法
dbms_obfuscation_toolkit加密解密数据
sqlserver2017 developer版本;SQL Server是由Microsoft开发和推广的关系数据库管理系统(DBMS),它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的,并于1988年推出了第一个OS/2版本。Microsoft SQL ...
亲测有效 通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考...脚本里有说明 先运行upg_tzv_check.sql再运行upg_tzv_apply.sql
ORA-04063: package body SYS.DBMS_REGISTRY_SYS has errors
ORACLE数据库使用dbms_stats包手动收集关于表的、索引的统计信息。
一个自己开发的dbms系统,实现自己的sql语句编译解析
oracle dbms_sql的使用方法,非常使用
详细介绍DBMS_JOB包的各函数操作,以及各函数对job的使用作用等。
Oracle 18c bug 执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY报错_ITPUB博客.mhtml
dbms_comp_advisor.getratio 预估压缩比例的存储过程脚本 11gR2以前使用,11gR2后可以使用系统自带的存储。
DBMS_JOB的详细定义,有实例,有详细的解释
java写的数据库 SQL解析器 分客户端和后端底层实现
符合新时代要求的OTT平台拟议DBMS_Proposed DBMS for OTT platforms in line with new age requirements.pdf
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。