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[
^]
Submit an article or tip