PL/SQL与SQL分析利器-Profiler

白鳝的洞穴 2020-06-22

2705

在日常的运维与优化工作中，经常需要分析某条语句或者某个存储过程执行是不是正常的，存储过程中哪些SQL语句或者循环开销较大。这个时候，一个ORACLE自带的工具-profiler就十分有用了。profiler是一个Oracle自带的存储过程性能分析工具，从很早的版本就存在了，不过可能很多DBA都不知道这个工具的存在，因为这个工具在Oracle官方的手册里并没有提及。

老白在十多年前写的《oracle 优化日记》里提到了这个工具，并提供了一个利用这个工具解决《最长的一天》的案例。在那个故事里，用户的系统莫名其妙的很慢，数据库的负载并不高。最后老白怀疑到是不是应用有问题。而应用是一个套装软件，开发商在英国，所有的PL/SQL存储过程都是加密的，现场没法分析代码。于是老白就使用PROFILER把耗时最多的代码行找到，通过和英国那边的开发团队沟通，开发团队很快定位了问题，从而帮助客户解决了问题。

Profiler的使用十分简单，在$ORACLE_HOME/rdbms/admin下面有几个Profiler相关的脚本：

要安装Profiler，首先在你的用户SCHEMA执行proftab创建相关的表，然后使用SYSDBA执行profload创建相关的存储过程。安装就结束了。十分简单。

如果你要跟踪分析某个存储过程，也十分简单。首先编写一个PL/SQL BLOCK，把你的存储过程跟踪调试一下：

declare

err number;

begin

err:=DBMS_PROFILER.START_PROFILER ('testprofiler'); --启动 profiler测试

test1_proc(10);

err:=DBMS_PROFILER.STOP_PROFILER ;

end;

然后查看这次测试的runid是什么

column RUN_COMMENT format a40 truncate;

select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;

runid =6的就是我们刚才的那次测试，下面我们可以分析这次测试的情况：

set line 132

column RUN_COMMENT format a40 truncate;

select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;

column unit_name format a15 truncate;

column occured format 999999 ;

column line# format 99999 ;

column tot_time format 999999.999999 ;

col text format a60 truncate;

select p.unit_name, p.occured, p.tot_time, p.line# line,

substr(s.text, 1,75) text

from

(select u.unit_name, d.TOTAL_OCCUR occured,

(d.TOTAL_TIME/1000000000) tot_time, d.line#

from plsql_profiler_units u, plsql_profiler_data d

where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number

and d.TOTAL_OCCUR >0

and u.runid= &RUN_ID) p,

user_source s

where p.unit_name = s.name(+) and p.line# = s.line (+)

order by p.unit_name, p.line#;

这里我们就很清晰的看到了每一行PL/SQL代码的执行时间了。

Profiler工具不仅仅可以用于跟踪PL/SQL，还可以用于测试一些SQL语句比较细微的差别，老白就经常用PL/SQL来分析不同的SQL执行的差异。比如前阵子有人在讨论UPDATE第一个字段和第十个字段的性能是不是有差异，我们就很方便的通过PROFILER精准的测试到。首先我们创建一张有15个字段的表：

create table test_col as select

object_id c1,

object_id c2,

object_id c3,

object_id c4,

object_id c5,

object_id c6,

object_id c7,

object_id c8,

object_id c9,

object_id c10,

object_id c11,

object_id c12,

object_id c13,

object_id c14,

object_id c15

from dba_objects ;

create index idx_col2 on test_col(c2);

然后创建一个存储过程：

create or replace procedure upd_col(lpc integer) is

begin

for i in 1.. lpc loop

update test_col set c1=123 where c2=10613;

update test_col set c15=123 where c2=1-613;

end loop;

commit;

end;

进行PROFILER测试

最后我们来看测试结果：

可以看出，还是有细微的差别的。有些企业规定常用的字段必须放在表的前面，从这个角度上还是有一定的意义的。不过这些差异十分细微，不过如果这些字段的访问十分频繁，积累下来的效果还是挺明显的。

如果把PROFILER用好了，那么我们是可以做很多工作的。上面就是一个简单的例子。

技术

最后修改时间：2020-06-22 09:16:58

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PL/SQL与SQL分析利器-Profiler

评论