基于Oracle PLSQL的存储过程性能优化方法案例

【前言】：最近忙着一个项目，Oracle有个JOB基于定时任务处理，经过我多次冥思苦索优化后，该存储过程最终依然需要花费1小时6分。这个JOB处理是为了生成年度、季度、月度三张统计分析报表以供用户查询使用，但客户知情后，指出必须实现时时查询汇总。由于鄙人技术能力有限，领导安排协调oracle高手来上海协助解决该问题，经过两天的学习，从中也跟这位师父学到不少东西，因此，今天特此总结，以供分享学习。


【题外话】：在入职本公司之前，基于Oracle PLSQL编程处于初级水平(DML)，当我参与现在的全面预算系统，经过将近两年的项目研发和实施工作，在这么长时间中不断摸索，平常经常跟Oracle打交道，从最初的解读他人的成千上万行的代码，到现在已经可以自己解决各种业务问题，这种成长可谓可喜可贺。但是，基于PLSQL编程，我只能简单的调试，排查bug，但是对于其中出来花费时间消耗没有太多的能力解决性能优化。今天特此，通过案例并加图以解说如何通过PLSQL Developer进行优化。


1、比如有这样一个存储过程那么如何通过调试，知道每个代码块花费的时间呢，根据消耗时间以此进行优化。

2、进入调试模式



3、点击上图中的那个图标，当前用户会创建三张表



4、当点击上述图标后，输入存储过程的参数后，然后点击RUN，如下图左上角那个齿轮东西






5、然后在切换到Profiler选项卡，如下图，会看到该存储过程执行消耗详细




 
6、对于上述说明

Unit:指调用的存储过程
Line:指Text中文本在存储过程代码块中的行号
Total time:指Text行执行消耗的毫秒
Occrurrences:指当前行代码执行的次数
Text:指具体的代码块行


7、上述Occurrences指执行一次、现在想把该存储过程执行1000次，通过for循环






8、执行1000次，共花费0.234秒 执行消耗详细如下图：

 

 
9、这时根据上图、发现occurrences是1000，total time也明显上升。


10、根据此以上步骤，通过调试就可以分析某个存储过程中是因为哪个代码块消耗时间太长而进行性能优化。


另外：在数据库查询统计中，我之前也知道表添加索引可以提高查询效率，担任鄙人不知在哪儿看到，说添加索引会导致该表增删改操作效率变慢，因此在从事行业生涯中，从未添加任何索引，因此在本次学习性能优化中，导师支出对于表如果不超过百万数据，是没多大影响的。因此在优化工作中，通过添加索引，Unique Index更搞笑，一大堆存储过程处理明显提高。 因此，在以后开发中，逐步学会合理添加索引，以此解决一些常规时间消耗太长的情况。