Oracle如何根据存储过程定位慢SQL?

需求说明,数据库执行的SQL出现性能问题，如何是单条SQL比较好找到问题SQL，但是如过是存储过程呢？

存储过程中可能会衍生出不同的动态SQL，那么具体调用存储过程中，存储过程执行慢在什么SQL的位置呢？

方法一 Trace 10046

SQL>execute sys.dbms_system.set_ev(7,36,10046,12,'');
exec sql
SQL>execute sys.dbms_system.set_ev(7,36,10046,0,'');
tkprof ora_2229_10046.trc 888.trc 
可以通过disk 排序之类的方式，定位TOP SQL
特点：
1.需要再次执行一次存储过程；
2.定位top sql并不高效,需要对disk 操作系统文件进行一定的grep 过滤 order by 人为查询

方法二、dbms包分析

执行dbms包进行分析,sys.DBMS_PROFILER.start_profiler

这里有两种途径，1.使用plsql图形化进行调试，2.手工通过sql调用执行

使用plsql对存储过程进行调试
1、在“Procedures”下拉列表中找到已经编写好的存储过程，点击右键，找到“测试”，如图所示：
2、PL\SQL会打开调试界面，图中位置1的按钮就是开始调试的按钮，在调试之前要填写输入参数的值，位置2就是填写参数的地方，如果有多个参数，会有多行参数框，按参数名填写相应的参数即可，
如果没有参数，可以不填。
3、填写完参数，单击开始调试按钮后，调试的界面会发生一些变化。图中位置1的变化，说明存过已经处于执行状态，别人不能再编译或者执行。位置2的按钮就是执行按钮，
单击这个按钮存过会执行完成或者遇到bug跳出，否则是不会停下来的，调试时不会用这个按钮的。位置3的按钮才是关键——单步执行，就是让代码一行一行的执行，位置4的按钮是跳出单步执行，
等待下一个指令。

特点：
1.需要再次执行一次存储过程；
2.定位存储过程的慢SQL比较方便，因为有一个整体的性能消耗的展示

方法三、Ash视图查询

原理就是V$ACTIVE_SESSION_HISTORY
ash有top_level_sql_id(就是存储过程的sql_id)，根据执行时间定位哪个sql_id执行时间长，每个sql都有sql_exec_start

1.跟客户沟通得到执行存储过程慢的时间范围；
2.通过时间，根据sql_id or top_level_sql_id进行group by count 得到top sql
3.根据步骤2得到的sql_id,查询sql_text,与客户反馈确认执行存储过程的慢sql，对应sql_id
4.根据sql_id or top_level_sql_id 等于执行存储过程的慢sql,找到存储过程里面执行的内部sql_id
5.将内部sql_id 进行循环或者每个进行检查sql执行时间，得到top sql

特点：
1.无需客户再次执行；
2.得到汇总的结果比例比较麻烦，需要写脚本完成循环过程，否则分析时间很长。