Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Pinning objects in the library cache

Pinning objects in the library cache

Hi Gurus,
Can anybody tell me if we can pin SELECT statements to the library cache using dbms_shared_pool or they should be converted to PL/SQL blocks to pin them?

Please help me in this regard.

Regards
Subodh
5 REPLIES
Peter Godron
Honored Contributor

Re: Pinning objects in the library cache

Hi,
I believe the syntax is:
execute dbms_shared_pool.keep('package');
Keep an eye on your hit ratio!

Interesting doc:
www.apress.com/ApressCorporate/supplement/1/92/1590590228-994.pdf

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

You have only awarded points to 1 of 9 answers !
Hein van den Heuvel
Honored Contributor

Re: Pinning objects in the library cache

This question is 100% about Oracle (allthough you failed to indicate that) and has nothing to do with HP-UX or other attributes of the box it run on.
I recommend you seek help through Oracle support, or an Oracle specific forum. If you do, be sure to specify the exact version.

Regards,
Hein van den Heuvel

Re: Pinning objects in the library cache

Thanx a lot peter, however it is for a package, i wanna know how to pin a SELECT statement
example
SELECT USERNAME FROM DBA_USERS;
somethin of that sort.

Regards
Subodh
Peter Godron
Honored Contributor

Re: Pinning objects in the library cache

Hi,
I don't thing you can pin an individual select statement. The closest is probably the cursor pin.

See:
http://www.unix.org.ua/orelly/oracle/bipack/ch12_02.htm Section 12.2.4.2
Arturo Galbiati
Esteemed Contributor

Re: Pinning objects in the library cache

Hi Subodh,
usualyy are pinned the package and function most used and that required large portion of memory. Good practice is to pin them when the datasbe ha sbeen reboot to avoid to invalidate object making this online.
Usually a set of Oracle package are pinned to increase perfomance:
--- SYS USER
exec dbms_shared_pool.keep('SYS.STANDARD');
exec dbms_shared_pool.keep('SYS.DBMS_SYS_SQL');
exec dbms_shared_pool.keep('SYS.DBMS_LOCK');
exec dbms_shared_pool.keep('SYS.DBMS_SQL');
exec dbms_shared_pool.keep('SYS.DBMS_APPLICATION_INFO');
exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
exec dbms_shared_pool.keep('SYS.DBMS_PIPE');
exec dbms_shared_pool.keep('SYS.DBMS_ALERT');
exec dbms_shared_pool.keep('SYS.DBMS_STANDARD');
exec dbms_shared_pool.keep('SYS.DBMS_UTILITY');
exec dbms_shared_pool.keep('SYS.DBMS_SQL');


plus your package/function/cursors.

to identify most used/loaded objects:

clear column
col Stmt for a50
select
sql_text "Stmt",
count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
from
v$sql
group by
sql_text
having
sum(sharable_mem) > 5000000
/

HTH,
Art