我有一个存储过程，其中包含4000行需要微调性能，任何人都可以建议如何做到这一点，因为我是这个环境的新手。 [英] I have a stored procedure which contains 4000 lines need to fine tune the performance can anyone suggest how to do this as I'm new to this environment.

问题描述

我有一个存储过程，包含4000行需要微调性能，任何人都可以建议如何做这个，因为我是这个环境的新手。



我尝试了什么：



我已经尝试了每个声明，解释计划可以进行4000行程序。

I have a Stored Procedure which contains 4000 lines need to fine tune the performance can anyone suggest how to do this as i'm new to this environment.

What I have tried:

I have tried each statement with explain plan it is possible for 4000 lines procedure.

推荐答案

请先阅读我对这个问题的评论。



我建议从这里开始：改进Oracle Lite数据库的SQL查询性能 [ ^ ]

这个： Oracle SQL调优步骤 [ ^ ]也可能会有所帮助。





我的建议：获取摆脱旧SP并从零开始写入SP。

Please, read my comment to the question first.

I'd suggest to start here: Improving SQL Query Performance for the Oracle Lite Database[^]
This: Oracle SQL tuning steps[^] might be helpful too.


My advice: Get rid old SP and write that SP from zero.

对于初学者来说，拥有一个包含4000行的过程会告诉我们设计问题。如果没有看到实际的程序，就不可能说问题是数据库设计还是只有PL / SQL代码。正如已经建议的那样，如果您同时批判性地检查代码，则重写该过程最有可能产生最佳结果。



性能问题是什么，取决于代码可以有多个问题来源，都有不同的纠正需求。例如，考虑以下情况

- 单个复杂查询表现不佳可能需要查询优化，索引等等。

- 过度使用循环执行内部语句。单个语句可能表现得足够，但是当您重复执行语句时，使用的总时间是不可容忍的。

- 使用游标和程序方法而不是基于集合的操作。一个非常常见的问题是获取数据，执行一些计算/操作，然后使用游标和循环更新数据。当摆脱光标并使用基于集合的方法时，性能通常会更好。



那么从哪里开始？为了了解环境，我强烈建议您阅读调整PL / SQL应用程序的性能 [ ^ ]。它将为您提供有关Oracle环境中PL / SQL优化的良好基础知识。

阅读本章后，您可以开始查看代码，进行修改，将其拆分为较小的部分（如果适用）并使用dbms_profiler看看语句的行为方式。此外，如果您要使用企业管理器，您可以使用它来查找最昂贵的语句。但是，请记住，某些报告/视图/等可能需要其他许可证，如性能诊断包或类似功能。请参阅选项和包 [ ^ ]

For starters, having a procedure that contains 4000 lines tells about design problems. Without seeing the actual procedure, it's impossible to say if the problem lies with the database design or only with the PL/SQL code. As already suggested, rewriting the procedure most likely yields the best results if you go through the code critically at the same time.

What comes to the performance problems, depending on the code you could have multiple sources for the problems, all having different needs for correction. For example consider following cases
- A single complex query behaving badly may need query optimization, indexing, and so on.
- Excessive usage of loops where you execute statements inside. A single statement may behave sufficiently but when you repeatedly execute the statement the overall time used is intolerable.
- Usage of cursors and procedural approach instead of set based operations. One quite common problem is to fetch data, do some calculations/operations and then update the data using cursors and loops. When getting rid of cursor and using set based approach the performance is often dramatically better.

So where to start? To get the understanding about the environment I strongly recommend reading Tuning PL/SQL Applications for Performance[^]. It will give you a good basic knowledge about PL/SQL optimization in Oracle environment.
Once read the chapter, you can start looking at the code, making modifications, splitting it to smaller pieces if applicable and use dbms_profiler to see, how the statements behave. Also if you have Enterprise Manager to use, you can usi it to locate the most expensive statements. However, keep in mind that some of the reports/views/etc may require additional licenses such as performance diagnostics pack or similar. See Options and Packs[^]

这篇关于我有一个存储过程，其中包含4000行需要微调性能，任何人都可以建议如何做到这一点，因为我是这个环境的新手。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！