DBMS_PROFILER用法(查找存储过程瓶颈)
存储过程性能瓶颈分析
-- SQL优化--鸡免问题 鸡免共24，腿共64,鸡多少只，免多少只
with a as (select rownum r from dual connect by rownum<24)
select c.r cock_num,r.r rabbit_num from a c,a r
where c.r+r.r=24 and c.r*2+r.r*4=64;
-- 问题：迪卡尔积，24*24次
with a as (select rownum r from dual conenct by rownum<24)
select c.r cock_num,(24-c.r) rabbit_num from a c
where c.r*2+(24-c.r)*4=64;
-- 优化，执行24次
存储过程性能瓶颈分析：
1，资源争于（锁,IO,CPU）
2，不合理的代码书写
3，不合理的SQL书写
4，未采用合理的技术来实现业务(全局临时表)
5，用到临时到或全局临时表的SQL性能差(统计信息更新不及时，未有合适索引)
方法：
存储过程是否记录了较为详细的各环节执行时间
有：根据记录，有重点的进行分析
无：转下一条
是否可以重新运行
可以：dbms_profiler
不能：调整存储过程，通过埋点方式，记录执行时长，便于分析
通过AWR分析与存储过程有关的Top SQL
直接阅读存储过程代码
小的：尚可
庞大：最不得以的方式，费时费力难出成绩
存储过程有关代码类别,
1，静态SQL
2，使用临时创建的临时表的静态SQL和动态SQL
3，动态SQL
4，条件控制/循环
解决瓶颈方法
1，创建索引
2，收集统计信息
3，适当hint
4，尽量少使用自定义函数
5，可以使用批量处理的场景，尽可能使用批量方式处理数据(forall,buld collect)
使用DBMS_PROFILER 2，自己埋点，记录代码片段执行时间
1，DBMS_PROFILER用法(查找存储过程瓶颈)
2，启动监控：dbms_profiler.start_profiler
3，运行存储过程，可以是多个
4，结束监控:dbms_profiler.stop_profiler
-- @?/rdbms/admin/proftab.sql
-- @?/rdbms/admin/profload.sql
-- exec dbms_profiler.start_profiler();
-- exec cog.hello() -- 执行的存储过程，PL/SQL代码块
-- exec dbms_profiler.stop_profiler();
-- 查询dbms_profiler存储中每一步的执行时间和次数
set line 190 pages 90
col text for a60
col runid for 999
select &&runid from dual;
select d.runid,d.line#,
s.text,
round(d.total_time/1e9,2) total_time,
d.total_occur,
round(d.min_time/1e9,2) min_time,
round(d.max_time/1e9,2) max_time
from plsql_profiler_data d,sys.all_source s,plsql_profiler_units u
where -- d.runid=3 and
-- u.unit_name='SP_TEST'and
u.runid=d.runid and d.unit_number=u.unit_number and d.total_occur<>0
and s.type(+)=u.unit_type and s.owner(+)=u.unit_owner and s.name(+)=u.unit_name and d.line#=NVL(s.line,d.line#)
order by d.runid,u.unit_number,d.line#;
=-- 查询视图获取存储过程中每1行的执行时间，执行次数
=-- 通过DBMS_PROFILER可以看到每一行有效代码运行的执行时间，
=-- 通过对total_time倒序排列，容易知晓哪些版本最耗时间，有的方失的筛查出具体有哪些片段有优化空间，再进行相应的优化。
=-- DBMS_PRIFILER相比于埋点，可以减少无谓代码，
=-- 总结：存储过程的性能瓶颈，大多和SQL有关，也有一部分和PL/SQL代码本身有关，与SQL无关
=-- 始终遵循 发现问题-定位问题-解决问题的路径来处理性能问题
=-- TT 3 7 2 – 三条腿的圆桌/腿脚数/租金/至少
=-- FT 4 15 2 – 四条腿的圆桌/腿脚数/租金/至少
=-- TC 3 12 2 – 三条退的椅子/腿脚数/租金/至少
=-- FC 4 8 2 – 四条腿的椅子/腿脚数/租金/至少
=–
=-- 要求1：总租金刚好1888
=-- 要求2：每样至少2个
=-- 要求3：桌椅总数188
=-- 要求4：腿脚总数范围588到699
set serveroutput on
drop table cog.t3 purge;
create table cog.t3 as select 1 TT,2 FT,3 TC,4 FC,5 JIAO,6 MON from dual;
drop table cog.t2 purge;
create table cog.t2 as select 999999999 id,to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') dt from dual;
truncate table cog.t2;
truncate table cog.t3;
create or replace procedure cog.hello as
-- declare
a number;
b number;
c number;
d number;
e number;
f number;
n number;
begin
n := 0 ;
a := 188 - 6 ;
-- dbms_output.put_line(' ');
insert into cog.t2 values(1,to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff'));
for TT in 2 .. a loop
b := 188 - 4 - TT;
for FT in 2 .. b loop
c := 188 - 2 - FT - TT;
for TC in 2 .. c loop
d := 188-TT-FT-TC;
-- e := (case when (TT + TC)*3 + (FT + d )* 4 between 588 and 699 then 1 else 0 end) ;
-- f := (case when (TT*7) + (FT*15)+ (TC*12) + (d*8) =1888 then 1 else 0 end) ;
e := ( (TT + TC)*3 ) + ( (FT + d )* 4 );
f := (TT*7) + (FT*15)+ (TC*12) + (d*8);
-- dbms_output.put_line('put line: '||TT||' '||FT||' '||TC||' '||d||' '||e||' '||f);
n := n+1 ;
if e between 588 and 699 and f=1888 then
insert into cog.t3 values(TT,FT,TC,d,e,f) ;
end if;
end loop;
end loop;
end loop;
insert into cog.t2 values(n,to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff'));
commit;
end;
/
select * from cog.t3 order by 1,2,3,4;
select * from cog.t2;
-- 1147种组合方式
-- 循环1004731次
-- 用时0.276097秒
TT FT TC FC JIAO MON
---------- ---------- ---------- ---------- ---------- ----------
111 69 3 5 638 1888
112 68 5 3 635 1888
114 70 2 2 636 1888
1147 rows selected.
SQL> select * from cog.t2;
ID DT
---------- -----------------------------
1 2020-06-23 23:11:13:356390
1004731 2020-06-23 23:11:13:632487
