Rendimiento - Gestion Shared Pool

Apuntes para gestión de la Shared Pool

-- SHARED_POOL OBJECTS:

SELECT substr(owner,1,10)||'.'||substr(name,1,35) "Object Name",
' Type: '||substr(type,1,12) TYPE,
' size: '||sharable_mem SHARE_MEM,
' execs: '||executions EXECS,
' loads: '||loads LOADS,
' Kept: '||kept KEPT
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
AND executions > 0
ORDER BY executions desc,
loads desc,
 sharable_mem desc
/

-- PINNING CURSORS (CUANDO LLEVA LA INSTANCIA CORRIENDO UN TIEMPO, NOS SIRVE PARA MARCAR COMO KEEP LOS CURSORES MÁS UTILIZADOS)

CREATE OR REPLACE PROCEDURE pincurs AS
addr_plus_hash varchar2(100);
cursor c1 is select rawtohex(address) addr,hash_value
from v$sqlarea
where executions > 1000
and sharable_mem > 4000;
BEGIN
for C in C1 loop
addr_plus_hash := c.addr||','||c.hash_value;
sys.DBMS_SHARED_POOL.KEEP(addr_plus_hash,'C');
end loop;
END pincurs;

-- TRIGGER INICIAL DE LA BBDD PARA CARGAR LOS PAQUETES MÁS IMPORTANTES:

create or replace trigger
   pin_packs
       after startup on database
       begin
       execute dbms_shared_pool.keep('DBMS_ALERT');                                
       execute dbms_shared_pool.keep('DBMS_DDL');
       execute dbms_shared_pool.keep('DBMS_DESCRIBE');
       execute dbms_shared_pool.keep('DBMS_LOCK');                        
       execute dbms_shared_pool.keep('DBMS_OUTPUT');
       execute dbms_shared_pool.keep('DBMS_PIPE');
       execute dbms_shared_pool.keep('DBMS_SESSION');                         
       execute dbms_shared_pool.keep('DBMS_SHARED_POOL');
       execute dbms_shared_pool.keep('DBMS_STANDARD');
       execute dbms_shared_pool.keep('DBMS_UTILITY');                                 
       execute dbms_shared_pool.keep('STANDARD');
       end;