Pinning Objects to the Pool

[dharma]

Hi,
We have a package which runs for a long time for the first time and is much faster for the rest . Now i'd like to pin the package , here's where i have my question, once i pin it if its not being used for a long time will it be renedered useless by the LRU, if that is so is it ok to have job that pins the package everyday?
how do i find if the package is in the pool or not?
I am new to this stuff, correct me if i have interpreted anything worng.
any suggestions/answers are appreciated,
Thx in advance,
Dharma.
(Developer-->DBA)

[jgmagnus]

** SHOWS what Packages are PINNED **

SELECT name, type, kept FROM v$db_object_cache WHERE kept = 'NO';


- Magnus

[jmodic]

Once you pin PL/SQL package into shared pool it will not be aged out untill you shut down the database or untill you flush the whole shared pool.

[chrisrlong]

From a different perspective:

Are you sure that this is going to help?

Usually, such speed improvements are cause by the *data* from the *queries* inside the procs being cached, as opposed to the proc itself.

Just something to check,

- Chris

[dharma]

Thanks for all the help to all.
but Q for Chris:
I couldnt get much out of the ans except for the query which u think might be slow.
Yes its a pretty big query, not yet optimised completely, but the data we are getting are for completely different sets of parameters - where does the cache creep in?-- couldnt comprehend..
could u please help in that a little more.
(apologies for any inconvenience)
Thanks a lot ,
-Dharma

[chrisrlong]

My point is that, in general, the retrieval of the stored procedure is not a lengthy process. The only reason that I know of for pinning a package is to avoid fragmentation if it is very large. Of course, my opinion is that when one has an extremely large package, it should be broken up anyway, but that is a different story .

I ask the forum to correct me if I'm wrong, but I simply can't imagine that pinning a query can save more than a very small number of physical reads and maybe a little CPU time.

What is far more important, in my opinion, is how well tuned the SQL within the procedure is. A large SQL statement can easily do tens of thousands of physical reads and hundreds of thousands of logical reads. These kinds of number fully dwarf the minor amount of work it took to read the package from disk.

Now, in your case, I was assuming that the first run of the package ran some SQL statements and that the data that these statements used was not yet cached, resulting in a large number of physical reads. Then, when run a second time, it all ran *much* faster because the large amount of *data* was cached, not because the little package was cached.

However, I am concerned about the additional info you provided. Check my math on this:

Original complaint:
- Ran package twice
- Second time ran much faster
- Why?

Latest info:
- The two runs used different parameters

Okay then, how can you expect to compare the times for the 2 runs if they used completely different parameters? Is the package running the exact same steps and all the queries using the exact same plans and returning the same amount of data? Or is it possible that the second set of parameters, if run first, would still have run faster? Of course, even if both queries are equivalent, but return different data from the main table, is it possible that the first run cached much of the data from the secondary tables? Is it possible that the first performed a table scan on the main table and cached the whole thing? (unlikely it would all still be in cache if large, but still possible)

I'm not trying to be diffficult, and you certainly don't need to give *me* all these answers. These are all things for you to consider. Again, all this because I find it hard to believe that simply pinning a package is going to make that much of a difference.

But that's just my opinion, I could be wrong,
(credit: D. Miller )

- Chris

[thillair]

Just see the object's which is currently pinned in memory by
running the queries

set pagesize 60;
column executions format 999,999,999;
column Mem_used format 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,21) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY executions desc;

then decide which objects to which to keep in memory.Keep frequently executed objects in memory.
syntax .
---------
execute dbms_shared_pool.keep('DBMS_APPLICATION_INFO');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('STANDARD');

Thanks
Ranga

[marist89]

Originally posted by chrisrlong
But that's just my opinion, I could be wrong,
(credit: D. Miller )

- Chris

The book must be getting close; he's crediting references...

[birdax1]

Have a look at this, from the Oracle WEB site. I found it most usefull.

Package Handling in Shared Pool


These recommendations will assist in addressing issues associated with
packages, procedures and functions in the shared pool.


A. Recommendations

1. Always pin the following packages in the shared pool:

STANDARD
DBMS_STANDARD
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_OUTPUT

and any other SYS package that are used (DBMS_LOCK, DBMS_ALERT, etc). In
addition, large user written packages and any frequently used packages must
be pinned.

In a development environment, the following packages should also be pinned in
the shared pool:

PIDL
DIANA
DIUTIL

In summary PIN as much as possible, but most importantly, those owned by
SYS and large user written packages. Currently only packages can be pinned in
the shared pool, therefore it is recommended that procedures and functions are
created as packages.

2. Package size

With 7.0.13, there was a limit of 64k for the size of a package that could be
successfully compiled and executed. This was removed with 7.0.15.

From 7.0.15, the compiled code for a package was split into more than one
piece, each piece being only about 12k in size. So the 64k restriction was
lifted. But packages larger than 100k can still have problems compiling. It is
therefore recommended that the code size of a package should not be greater
than 100k. In addition, any large package must be pinned in the shared pool to
guarantee that there is sufficient memory to execute the package on subsequent
attempts. It must remembered that pinning packages takes away memory available
in the shared pool from other resources. Therefore if a large number of
packages are being pinned, the shared pool may need to be increased in size.

To determine the package code size use the column CODE_SIZE in the view
DBA_OBJECT_SIZE. The column PARSED_SIZE.DBA_OBJECT_SIZE is the size of the
parsed code for the package.

B. How to pin packages in the shared pool

The procedure dbms_shared_pool.keep should be used to pin a package. The
package dbms_shared_pool is created by running the dbmspool.sql script as
user sys. Please note that issueing 'keep' does not load a package immediately
into the shared pool. The package must be executed or recompiled to be loaded.
It is recommended that packages are pinned in the shared pool soon after
instance startup, since at this point the sga has not been fragmented.

When pinning packages, other than those owned by sys, execute privilege on the
package must be granted to public before the call to keep will work. Once this
has been done the access may then be revoked.

The following sql will look for all packages currently in the sga and generate
a keep script. It could equally drive off DBA_SOURCE or DBA_OBJECT_SIZES.

SET PAGESIZE 999
SET HEAD OFF
SET FEEDBACK OFF

SPOOL keep_exec.sql

SELECT 'execute dbms_shared_pool.keep('''||name||''');' sql_stmt
FROM v$db_object_cache
WHERE type='PACKAGE'
/
SPOOL OFF

An example output is:

execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('STANDARD');
execute dbms_shared_pool.keep('DBMS_UTILITY');

execute dbms_shared_pool.keep('XK_KERNEL_PK');
execute dbms_shared_pool.keep('XK_WINDOW_QUERY');
execute dbms_shared_pool.keep('XK_WINDOW_QUERY_REPORT');
execute dbms_shared_pool.keep('XK_WINDOW_QUERY_REPORT_2');

C. How to unpin packages from the shared pool

The procedure dbms_shared_pool.unkeep should be used to unpin a package.
Issueing the command 'alter system flush shared_pool' does not flush 'keep'ed
packages. They are only aged out of the shared pool by using the procedure
'unkeep' and restarting database.

D. How to find out which packages are pinned in the shared pool:

SELECT name, type, kept
FROM v$db_object_cache
WHERE kept = 'YES';

The column 'KEPT' has the values 'YES' or 'NO'.

or use dbms_shared_pool.sizes to list objects in the shared pool. The second
column 'KEPT' shows whether the package is pinned.

E. Database Triggers

It is recommended to keep database triggers as small as possible. They should
act as stubs and call packages where necessary. Packages can, optionally, be
pinned, and do not have to be recompiled when loaded into the shared pool.

Please note that the keep and unkeep procedures may not be supported in future
releases of the rdbms if automatic mechanisms are implemented to make this
unnecessary.

Known bugs:

196270 ATTEMPT TO RUN A STORED PROCEDURE GIVES ORA 4031
192829 ENH: DO NOT SWAP DB TRIGGERS OUT OF SHARED POOL
187424 NEED UTILITIES TO HELP TUNE SIZING OF SHARED POOL
231459 NOT POSSIBLE TO COMPILE PACKAGES WITH CODE SIZE > 100K