topic Re: Pinning objects in the library cache in Operating System - HP-UX

Pinning objects in the library cache

Subodh deshpande_1 — Tue, 27 Mar 2007 06:00:02 GMT

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

Re: Pinning objects in the library cache

Peter Godron — Tue, 27 Mar 2007 06:21:43 GMT

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 !

Re: Pinning objects in the library cache

Hein van den Heuvel — Tue, 27 Mar 2007 06:23:47 GMT

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

Subodh deshpande_1 — Tue, 27 Mar 2007 06:29:03 GMT

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

Re: Pinning objects in the library cache

Peter Godron — Tue, 27 Mar 2007 06:34:24 GMT

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

Re: Pinning objects in the library cache

Arturo Galbiati — Wed, 28 Mar 2007 03:16:48 GMT

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