May 29, 2009

Pinning in M/R // Background Process

Pinning stored procedure/function to shared pool in 11G

execute dbms_shared_pool.keep(owner.trigger, 'R');

---------------------------------------------

Script displays instance background process information. The script works when the database is MOUNTed or OPENed.

select A.SID,A.SERIAL#,A.PROGRAM,P.PID,P.SPID,A.OSUSER, /* Who Started INSTANCE */A.TERMINAL,
A.MACHINE,A.LOGON_TIME,B.NAME,B.Description,P.PGA_USED_MEM,P.PGA_FREEABLE_MEM,P.PGA_MAX_MEM
from v$session A,v$process P,v$bgprocess B where A.PADDR=B.PADDR AND A.PADDR=P.ADDR and
A.type='BACKGROUND';

No comments: