Pinning objects in the library cache

[Subodh deshpande_1]

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

[Peter Godron]

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]

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

[Subodh deshpande_1]

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]

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]

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