pin procedures/packages

[dorothy]

How do you determine which procedures and packages in the database are most often loaded?

I want to pin procedures and packages in my database so I would like to know determine which procedures and packages in the database are most often loaded

[sambavan]

Use the view v$db_object_cache to determine which are the packages that are being loaded more and then choose those packages for pinning.


Sam

[dorothy]

so from v$db_object_cache, how do I determine which procedure/packages are the best candidates to pin?

[ggnanaraj]

Use this script . . .

SELECT RPAD(owner,10), RPAD(name,20), namespace, RPAD(type,15), loads, executions, locks,
pins, kept FROM v$db_object_cache WHERE sharable_mem > 10000
AND (type='PACKAGE' OR type='PACKAGE BODY' OR type='FUNCTION'
OR type = 'PROCEDURE') ORDER BY loads;