Performance: Create many procedures or a package?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Tue, 28 Apr 2009 10:21 am

Hi guys:D

I'm redesigning a system and want to write procedures and functions in the Bank to avoid duplication of code inside of my forms.

My question is, what works best in a matter of performance: Write multiple Store Procedures and acessalas as needed or gather them in a Package?

Location: Curitiba

Poston Tue, 28 Apr 2009 11:42 am

This matter is very personal, I believe the performance doesn't change since well written (both).

I believe you do everything within Package (where they are the same process) makes the environment more friendly and organized.
Location: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Poston Wed, 29 Apr 2009 9:03 am

To with Rodrigo também brow.

Usually create Packages for modules.

The sales of the Materials, Financial Group by affinity.
Location: Araraquara - SP


OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional

Poston Wed, 29 Apr 2009 9:26 am

For sure.
The creation of packages gives a number of benefits:
  • you can choose which procedures are PUBLIC or not. (stating whether or not the package spec)
  • You can define multiple subscriptions for the same procedure. (For example, procedure gera_log (input varchar2) and gera_log (entry date). The package will know which procedure call based on the type of parameter passed.
  • Can become a FIXED package in memory. (Pin Package). Oracle suggests always leave in memory the following packages: STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE, Dbms_output.put_line
    Code: Select all
    execute dbms_shared_pool.keep (' MINHA_PACKAGE');
  • as stated above, packages are better to control, because not a lot of General dependencies, are \"scattered\" in the Bank to move and change one important feature. Imagine you have a system with 30 30 procedures and functions.
  • You can create variables that are visible during the whole session.
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Wed, 29 Apr 2009 9:42 am

taking advantage of dr_gori's response?

I have a package which is used at least once per minute.
access to it is done several times by telemarketing

You aconcelhariam I put it in memory, giving this command ai dbms_shared_pool. keep??

here no DBA, would need to do some configuration very Specifies to use this feature?

After q always have any maintenance in the same she would already automatically in memory??

Thank you
Location: Batatais - SP

Poston Wed, 29 Apr 2009 10:12 am

If it is used a lot, it's worth it. But use it wisely, because it's not worth putting EVERYTHING, because you will be wasting resources (memory). Put only what is essential and what is widely used. Then it's worth it.

When you initialize the Bank has to run the command to let her in memory. In the old days, I saw a script that \"Booting\" the most commonly used packages. They had in all packages a procedure that didn't do anything. (only one NULL). Dai when the Bank startava, they run these procedures, only for the purpose of putting them in memory.

Here's a link with various tips about:
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

  • See also
    Last Post

Return to SQL

Who is online

Users browsing this forum: No registered users and 10 guests