Replacing packages on the fly

[pacsinte]

Hi,

I have a performance problem. A java client is used to insert several hundred thousands of entities into an Oracle9i database. An entity is composed of data from several tables. During the insert checks are made. Because of the checks the inserts are very slow after 100k. I have found where the problem is and I modified the package but I don't know how to update the package "on-the-fly". I cannot interrupt the java client as it will have to start the process from the beginning. My present solution is to suspend the Oracle process, update the package and resume the process. On the previous versions of Oracle oradebug could be used to suspend and resume a certain process. In 9i if I try to set the orapid and suspend I get my session suspended and not the Oracle process which I need to be suspended. Do you have any idea? What I am looking for is to be able to update (replace) a package body while a connected user is using it.

Thank you very much,
Bobby

 

[sem]

In general performing such operations with EJB's is not encouraged, because EJB's were designed to be used in pure OLTP applications, while your task is apparently a batch one. Thus I'd recommend you to use either pure JDBC (or other more flexible JDO that supports bulk operations) or use another tool (e.g. sql*loader) designed especially for bulk inserts. And after all, is your database itself ready to accept such transactions? I mean the size of rollback segments at least.
As for changing a package on the fly, I don't know a way, but I'm also sure that there's no need in this. In most cases updating/inserting 100K rows takes not more than several minutes (much higher times in general warn about low resources, inappropriate design or wrong execution plans used due to the lack of statistics), so I see no reason to worry about the part of work already done

Regards, Dima

http://seminihin.narod.ru