Stored Procedure with low performance

Navigation
2

I have a problem with Oracle, where I have two procedures that are executed one after the other. Where in the first procedure , I have a cursor that makes insert in a table. This insert has approximately 30 million records , which are inserted in one time fast and acceptable.

But when the next procedure is executed, the update is delayed. In one last execution it took about 38 hours. And it has only one cursor of update , smaller than the first one and executing the cursor separately, it runs in less than a minute.

A detail is that when the procedures are running, and I see that the second procedure is "locked", if I stop the execution, I give a analyze table , and I execute only the second procedure again, it is performed normally, taking about 1 or 2 hours to complete.

Does anyone have any idea how to help me?

    
asked by anonymous 21.10.2014 / 15:09

1 answer

1

The solution I found, along with a co-worker, worked the performance improvement, taking about 1 hour to process all the records. What before took about 28 hours.

The solution we tested and worked on was to change SORT_AREA_SIZE from the session. The default for Oracle is 65536 bytes, according to Oracle documentation

We change to a value 10 times more by going to 655360 bytes.

    
01.11.2014 / 21:20
how to display the first line of a text snippet, linking this to the page where it is? Print the values in a stack