Home » SQL & PL/SQL » SQL & PL/SQL » Packages
Packages [message #666787] Sat, 25 November 2017 20:44 Go to next message
rkapex
Messages: 10
Registered: November 2017
Junior Member
I have a package with 50 procedure and 30 functions etc. In short I have a huge package. So I don't want to load the whole package in memory. I want to one function or procedure of package laodaed in the memory. How can I do that ?

[Updated on: Sat, 25 November 2017 20:47]

Report message to a moderator

Re: Packages [message #666788 is a reply to message #666787] Sat, 25 November 2017 21:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rkapex wrote on Sat, 25 November 2017 18:44
I have a package with 50 procedure and 30 functions etc. In short I have a huge package. So I don't want to load the whole package in memory. I want to one function or procedure of package laodaed in the memory. How can I do that ?
How much RAM does the DB Server have?

What is the size of the whole package as measured by total number of characters in the source code?

Please quantify the measured savings by doing so.

Remove the function/procedure from the package & make it standalone.

It appears to me that you suffer badly from Compulsive Tuning Disorder.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Packages [message #666790 is a reply to message #666788] Sat, 25 November 2017 22:35 Go to previous messageGo to next message
rkapex
Messages: 10
Registered: November 2017
Junior Member
This question was asked in an interview.So I just want to know how to do that ?
Re: Packages [message #666795 is a reply to message #666787] Sun, 26 November 2017 01:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Invoking one function or procedure in a package forces Oracle to load the entire package into the library cache. How do you think you might re-structure the code to avoid this?
Re: Packages [message #666798 is a reply to message #666790] Sun, 26 November 2017 06:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
rkapex wrote on Sat, 25 November 2017 23:35
This question was asked in an interview.So I just want to know how to do that ?
Most likely interviewer had SERIALLY_REUSABLE Packages in mind.

SY.
Re: Packages [message #666818 is a reply to message #666798] Mon, 27 November 2017 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Though I'd say that the real correct answer is that if loading a single large package into memory is causing issues then you've got much bigger underlying problems that need fixing first.
Re: Packages [message #666831 is a reply to message #666787] Mon, 27 November 2017 08:04 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
rkapex wrote on Sat, 25 November 2017 21:44
I have a package with 50 procedure and 30 functions etc. In short I have a huge package. So I don't want to load the whole package in memory. I want to one function or procedure of package loaded in the memory. How can I do that ?
That's not exactly a huge package. Who told you that a package that size would be a problem? You look at some of the standard oracle packages in SYS and they are as big or bigger. That being said when you reference a package and it is not already in memory it loads it. While the database will keep internal variables separate per session the database doesn't load each session that uses a package a fresh copy of the code.

If you want only the called procedure/function loaded you have to make them into standalone code. While totally do able you lose the many advantages you get with a package.

[Updated on: Mon, 27 November 2017 08:05]

Report message to a moderator

Previous Topic: How to Avoid ORA-01438: value larger than specified precision allowed for this column
Next Topic: trigger impacting performance
Goto Forum:
  


Current Time: Thu Mar 28 21:27:18 CDT 2024