Performance Impact on Package Size in Oracle 10g

Using Oracle 10g. The original Oracle designer for this project has advanced, and those of us who remain are a reasonable developer for Oracle, but we need help setting up and planning.

We have separate procedures in โ€œsimilarโ€ packages, some of which have grown in size to include many (50 ish) procedures with various complexities.

At this point, several small procedures (select the ID from the Contract, where Item = 'xyz') in these large packages will take much longer than expected from within these packages (from TOAD, SQL Developer, or from .NET Oracle Provider) than with compiling proc yourself or in a smaller package. (Tables are indexed)

Should there be costs to use such large packages even when calling relatively simple separate procedures, or is there some โ€œotherโ€ factor that we should look for?

(note: an upgrade to Oracle 11 is planned, but not "inevitable")

+6
source share
1 answer

When you first call any method in a package, the entire package must be read in memory. In general, this should be an advantage based on the assumption that if you call one method in a package, it is likely that many related methods will be called either by one procedure that you called, or by subsequent application calls. But this means that the first execution potentially slows down, loading a lot more code may be strictly necessary for a simple function. However, this punishment should disappear as soon as the package is loaded into memory. It does not seem that you are talking about a problem with making the first call to the procedure, although, as a rule, this rules out.

Is there any code in the initialization block of the package that will be executed before performing a small procedure that may distort the results?

How do you determine how long it takes to name these small procedures and what does โ€œmuch moreโ€ mean? Do you call them several times and measure a small number of elapsed milliseconds and see, say, a 30% increase in execution time? Or do you call them thousands of times and see a 1000% increase in runtime?

+7
source

Source: https://habr.com/ru/post/892764/


All Articles