Deploying huge PLSQL package via JDBC is incredible slow

I have to deploy very large PLSQL packages via JDBC 开发者_JS百科and experience extremely long deployment duration. I know that it isn't a good idea to use packages with up to 25.000 lines of code, but I have no choice about that right now. Deployment of such a package takes about 2.5 hours via JDBC.

I read the package from filesystem with wrapping the FileReader in a BufferedReader. I parse it line by line and check for a delimiter and append each line to a StringBuilder until the statement is complete. Then I use the StringBuilders toString() and hand the resulting String over to my Statements execute().

Thank you for advice!

Oracle PL/SQL packages consist of:

• a package header - the specification similar to C .h files
• the package body - the equivalent of a .c file

Uploading a package header will invalidate all PL/SQL packages that utilize functions/procedures/records/constants/objects/types from that package - generally anything that references or uses something from the package.

Specify PACKAGE to recompile both the package specification and the package body if one exists, regardless of whether they are invalid. This is the default. The recompilation of the package specification and body lead to the invalidation and recompilation of dependent objects as described for SPECIFICATION and BODY.

The database also invalidates all objects that depend upon emp_mgmt. If you subsequently reference one of these objects without explicitly recompiling it first, then the database recompiles it implicitly at run time.

source: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/alter_package.htm

Uploading a package body has less impact on the state of the database. Packages dependent of the uploaded unit will not be invalidated.

Recompiling a package body does not invalidate objects that depend upon the package specification.

When you recompile a package body, the database first recompiles the objects on which the body depends, if any of those objects are invalid. If the database recompiles the body successfully, then the body becomes valid.

source: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/alter_package.htm

The reason for your extremely long compile times is probably caused by a cascade of recompilations performed by the database. This is typically a result of keeping the package header and the package body in a single file and then uploading both the header and the body of each package sequentially like in the following example:

• @pkg_a.sql - dependencies invalidated and recompilation attempt occurs
• @pkg_b.sql - same as above
• @pkg_c.sql - same as above

In this scenario, the database might attempt to recompile some packages several times without success because further dependencies were not yet uploaded. That's time wasted for dependency resolution and compilation.

This scenario can be greatly improved by splitting the packages into .pks (contains only the header) and .pkb (contains only the body). Uploading the header files first and then uploading the bodies.

• @pkg_a.pks - dependencies invalidated but not recompiled
• @pkg_b.pks - same as above
• @pkg_c.pks - same as above
• @pkg_a.pkb - pkg_a recompiled successfully because all headers are up to date
• @pkg_b.pkb - same as above
• @pkg_c.pkb - same as above

This is possible because it's only required that package headers from the dependencies are valid to compile a dependent package. In this scenario, recompilation of each package body occurs only once.

Splitting the packages into header and body files will also allow you to avoid uploading header files which did not change. This is quite often as most of the changes are made to the body (actual code) of a library. Skipping an unnecessary upload of a package header will result in less packages being invalidated and hence - less work to validate the whole database.

This approach should help you vastly reduce the time required to upload changes to the database.

I don't think your long package deployment times have to do with the Java, JDBC load process, but rather with the Oracle database package management.

When you execute a CREATE PACKAGE BODY FOO..., the RDBMS software first checks to make sure none of the other users in the system are using the package (via database locks). If they are, your process hangs until all the other users are finished using the package. Then it commits your package into the database. One of the ways to test this is to rename the package (in your original source file) and try and load it. If it doesn't take 2.5 hours this may be a contributing factor.

The other thing the RDMBS does when you run the statement is compile the package which involves verifying all the references in your code (e.g. tables, views, other packages) and generating a encoded version. For a large package the compile time may be significant. The way to test this is to run the statement ALTER PACKAGE FOO COMPILE; and time that.