CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle11g
Aditya
Aditya

Reputation: 23

Large number of Oracle Packages

I am generating code for Oracle Stored Procedure (SP) based on a dependency graph. In order to reduce recompilation unit size I have organised them in Oracle Packages. But this is resulting in large number of packages (250+). The number of procedures are 1000+.

My question: Will this large number of package create any performance issues with Oracle 11gR2+ ? Or will there be any deployment/management related issues ? Can somebody share their experience on working with large number of Oracle packages ?

Upvotes: 2

Views: 87

Answers (2)

sudhirkondle
sudhirkondle

Reputation: 125

The purpose of writing packages in oracle to implement the concept of modular methodology which explained as follows:

  1. Consolidate logical procedure and functional under one package
  2. There is way to define member variable in global and can be accessed with in package or outside packages
  3. The program units defined in package will be loaded at once in memory for processing and reduces context switching time

More details provided under link: https://oracle-concepts-learning.blogspot.com/

Upvotes: 0

ninesided
ninesided

Reputation: 23263

In one of the products that I've worked on, the schema had many thousands of stored procedures, functions and packages, totalling almost half a million lines of code. Oracle shouldn't have any issues with this at all. The biggest headache was maintenance and version control of the objects.

We stored each package header and body in separate files so that we could version them independently (the header typically changes much less frequently than the body), and we used an editor that supported ctags to make navigation within a package more manageable. When you have a hundred or more procedures and functions within a package, finding the right place to actually make changes takes as much time as actually doing the work! Another great tool was OpenGrok, which indexes the entire code base and makes searching for things super quick.

Deployment wise, we just used a simple script that wrapped SQL*Plus to load the files and log any issues with compilation or connectivity. There are more advanced tools that sit on top of your source control system and "manage" deployment and dependencies, but we never found that it was necessary.

Upvotes: 3

Related Questions