Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

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

Mustafa, 2021-09-28

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.

19c 21c compiling big functioncompiling big packagecompiling big plsql objectcompiling big procedurecompiling fuunctioncompiling packagecompiling procedureORA-04036PGA memory used by the instance exceeds

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes