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;

Rendimiento - Tiempos_db_file_sequential_read

Script para el análisis de tiempos en milisegundos del evento de espera db_file_sequential_read, indicador clave del rendimiento del subsistema de E/S, basado en las estadísticas de AWR

column c2 format 999.99
PROMPT Usage: tiempos_db_file_sequential_read.sql HORA
PROMPT Donde HORA= 08, 09, ...
select   TO_CHAR(a.end_interval_time,'DAY') DIA,
     a.end_interval_time "Fecha",
   sum(b.time_waited_micro-c.time_waited_micro)/sum(b.total_waits-c.total_waits)/1000 c2
from
   dba_hist_snapshot a,
   dba_hist_system_event b,
   dba_hist_system_event c
where
    a.snap_id=b.snap_id and
    a.snap_id=c.snap_id-1 and
    a.end_interval_time like '% &&1:%' and
   b.event_name = 'db file sequential read' and
   c.event_name = 'db file sequential read' and
   TO_CHAR(a.end_interval_time,'DAY') not LIKE 'SÁBADO%' AND
   TO_CHAR(a.end_interval_time,'DAY') not LIKE 'DOMINGO%'
group by
    a.end_interval_time,
       TO_CHAR(a.end_interval_time,'DAY')
order by
    a.end_interval_time;

Script - TopQ

Muestra las sentencias que existen en v$open_cursor, v$sqlarea, filtrando por aquél texto que se le pasa como parámetro, con información acerca de la máxima fecha de carga, máxima fecha de uso (last_active_time), usuario, modulos, servidores, etc

A usar preferiblemente tras sentencias TopD, TopE, TopB  ... para analizar de dónde vienen las sentencias encontradas como pesadas.

SET HEAD ON
SET LINESIZE 1000
SET PAGESIZE 2000
SELECT mAX(S.LAST_LOAD_TIME) MAX_LAST_LOAD_TIME,
    max(S.LAST_ACTIVE_TIME) max_LAST_ACTIVE_TIME,
    count(1) total,
       SUBSTR(V$SESSION.USERNAME,1,15) USUARIO,
       SUBSTR(V$SESSION.OSUSER,1,10) OSUSER,
       SUBSTR(V$SESSION.STATUS,1,10) ESTADO,
       SUBSTR(V$SESSION.SERVER,1,7) SERVER,
       SUBSTR(V$SESSION.module,1,20) modulo,
     SUBSTR(V$SESSION.MACHINE,1,30) MAQUINA,
     C.SQL_TEXT
FROM
     V$SESSION,
     V$PROCESS,
     v$sqlAREA S,
     V$OPEN_CURSOR C
WHERE
    (V$SESSION.PADDR = V$PROCESS.ADDR) AND
    (V$SESSION.USERNAME IS NOT NULL) AND
    (V$SESSION.SERVER LIKE 'DEDIC%') AND
    C.SID=V$SESSION.SID AND
    upper(C.SQL_TEXT) LIKE '%'||upper('&&1')||'%' AND
    C.SQL_ID=S.SQL_ID
group by
       SUBSTR(V$SESSION.USERNAME,1,15) ,
       SUBSTR(V$SESSION.OSUSER,1,10) ,
       SUBSTR(V$SESSION.STATUS,1,10) ,
       SUBSTR(V$SESSION.SERVER,1,7) ,
       SUBSTR(V$SESSION.module,1,20) ,
     SUBSTR(V$SESSION.MACHINE,1,30) ,
     C.SQL_TEXT
ORDER BY 4 asc,1
/