KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 28th, 2008

 

All the database objects defined inside a package gets loaded in memory for first and only call to database from application, thus executing procedures in packages requires less communication time from database thus faster. The procedures inside a package using other private procedures does not require to communicate from database again, it is already in the memory.....thus executing procedures from packages is faster.

In case of stand alone procedures, each procedures or d/b objects requires one call to database to be loaded in memory and then executed from application, the number of calls to database makes the execution slower.

tripathi.atul

 Profile Answers by tripathi.atul 

• Sep 10th, 2008

 

Packaged procedure can be overloaded.
Packaged procedure implementation is hidden as only specification is required for refering packaged procedure.
Packaged procedure when compiled i.e. when package body is compiled all other objects referring packaged procedure does not get invalidated.
When a packaged procedure is called whole package gets loaded  into memory so any further reference to that package procedure can avoid parsing cost.

psingla

 Profile Answers by psingla Questions by psingla

• May 7th, 2010

 

It is true in most of the cases but not always.
The packages works excatly the way 'KD09714' has specified.
But think of the scenarios where memory is low to store enough packages (due to size of packages) in memory.
Thus leads to more page faults.

muraligrandhi

 Profile Answers by muraligrandhi 

• Jun 8th, 2010

 

It's true. Procedures in the packages are faster than the stand alone procedures, because all the procedures in the package are compiled once and stored in the SGA at shared pool area. If we call the packaged procedure oracle immediately check's in the shared pool area so performancewise packaged procedures are better.

Where as stand alone procedures all the times hit the database, so hitting ratio should increase and performance will decrease.

If we couldn't use some packages frequently oracle will invalidates that packages with the help of LRU(Least Recently Used), so to avoid that we should PIN the objects in shared pool area.

For PINNING we need to use DBMS_SHAREDPOOL.KEEP

Example:
execute dbms_sharedpool.keep('package_name');

monoranjang

 Profile Answers by monoranjang 

• Jan 24th, 2011

 

A Storeprocedure is a Set of transactional SQL statements which will do some operation. Storedprocedure is precomplied . When we use sp's in our application it will reduce network traffic because we need to just call the sp from our application by using the name of the sp and the parameters, and no need of writing queries in the code.

A package is a collection of storedprocedures. A package should have a declaration part and a body part. The declaration part is used to declare the stored procedures and the body part is used to implement the storedprocedures. Calling Procedures through Packages are more faster than calling normal procedures.