Re: Performance degradation using packages?

Hugo,

I suggest you have a shared pool large enough to store your largest packages, and then pin them. By pinning I mean that large packages are kept in the library cache and are not aged out.

To accomplish this, the DBMS_SHARED_POOL package must be created by running dbmspool.sql in the $ORACLE_HOME/rdbms/admin directory. After this is accomplished you may pin an object by the following:

EXECUTE dbms_shared_pool.keep('package_name');

and unpin by the following:

EXECUTE dbms_shared_pool.unkeep('package_name');

Give that a try and see if your performance improves, if not I would look at tuning the code within the package.

Good Luck,
Mike Perrien
Staff Consultant, OGS Southeast

>>These are my opinions and do not reflect those of my employer<<

Hugo B. Baake <hbbaake_at_pi.net> wrote in article <01bc1535$3fb35aa0$0988dc91_at_hugoathome>...
> I would like to have some input on the following question:
>
> An Oracle 7.1 batchjob (pl/sql) loops through a table with some
> 250000 records. The job uses a number of custom packages to mimic
> an object oriented approach. The custom packages mainly fetch
> data from a number of tables to do some calculation.
> Will the 'packaged object oriented' approach introduce a severe
 performance
> degradation??
>
> Thanks for any input,
>
> Hugo
>
  Received on Tue Feb 11 1997 - 00:00:00 CST