ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT While Compiling a Huge PLSQL package

Hello everyone,

Lately, I faced with an interesting ORA-04036 errors at one of my clients’ development database. My notification modules alerted me about ORA-04036 error and I was just started to investigate trace files (and saw a package name frequently) and one of the developer sent me an email about the error they got. They were trying to compile a package and they got this pga error. it was interesting because they didn’t even try to change the code they were just trying to compile the package but after a minute, Oracle raises ORA-04036.

So, the problem, obviously, related to that package and when I open the package source code, it was a little bit shock to me. The package contains 164 procedure/function and source code has more than 24.000 lines! This is the biggest package that I ever see (and I worked with really big/old/complex systems). with a quick look, unfortunately, many developers add many things to the package and not all of them are related. they used this package as a container for everything. Many procedures/functions are not even related by code or business logic.

Since this is a development database, server is really small (14GB memory, 2 cpu core) and weak. Compiling a source code that big causes too much use of PGA and finally raise the error. As you guess first request from dev team was increase the memory or remove the pga limit of course and my reactions was an absolute no to that as well 🙂 having that big package is bad for in every angel! I will explain the solution (workaround) at the end of my post but let’s talk about plsql objects and their sizes first.

Oracle says that (Oracle university contents) a plsql procedure or function works with performance at most 60-70 lines of codes. this might be a little bit utopic but true. Packages contain of procedures and functions and Packages should contain only related procedures/functions. Let’s say you have some conversion functions to standardize the converted strings and you shouldn’t put them into an invoicing related package. they should have their own package and can be used from in it. In that point of view, a package can contain 30-35 procedures/functions at most. Remember you should divide your code as much as you can, that way you will be able to manage your code more easily and your code will be far away from spaghetti coding. if we say a procedure or function should at most 100 lines of code, a package shouldn’t be more than 3000-3500 lines.

Packages are copied to users’ pga area before they run. You might be familiar to “ORA-04068: existing state of packages has been discarded” error. if your package has global variables and source code has been changed by another user, you will get this error because source code is copied to you pga area and since it is different now, all code must be reloaded. Even if you use just one procedure or function in that code, all the codes of package is loaded into the memory. That is really unnecessary. Keep your packages small.

How did we overcome the error? As I said before, this server is really small and we already using almost all the source on the server and as a workaround, I talked to developers and tell them to split up those code into another package. Of course, development team has some serious deadlines currently and it is not logical to ask them separate those code as they were supposed to be (create new packages grouped by business logic and call those new packages from application etc) but we talked about this job and it must be done in somewhere. They created necessary action records to do that as well. As the solution, I told them just create a new package, move biggest procedures/functions to new package and call this new package from the current package’s procedures and function.

PKG_CURRENT
SP_X — 1000 lines
SP_Y — 1000 lines
SP_Z — 1000 lines
SP_Q — 1000 lines

create new package and move some code into it:

PKG_NEW
SP_X — 1000 lines
SP_Y — 1000 lines

and call this new package from the old one so you don’t need to change your application code.

PKG_CURRENT
SP_X  => begin pkg_new.sp_x end; — 4 lines
SP_Y => begin pkg_new.sp_y end; — 4 lines
SP_Z — 1000 lines
SP_Q — 1000 lines

that way current package will be much more smaller and you don’t need to change a thing at the application. they take the action and it worked. of course this is not the correct way as I said, this package should be split into many packages based on business logic and dependencies. They will do that too.

while ending my post here I would like to remind those wise words: Keep it simple, keep it stupid.

wish you all healthy days.