Mostrando entradas con la etiqueta SQL. Mostrar todas las entradas
Mostrando entradas con la etiqueta SQL. Mostrar todas las entradas

21 enero 2019

¿Cómo encontrar los metadatos de cualquier objeto en Oracle Database?


Normalmente nos vemos en la situación de necesitar definición de tablas, índices, vistas, etc. para replicar la estructura en otra base de datos. Oracle tiene un comando muy útil para encontrar la definición de metadatos de la estructura diversa. Este comando es muy útil para extraer los metadatos de cualquier objeto, como tabla, índice, vistas, vistas materializadas.


La salida de la consulta es de tipo de carácter large. Por favor, establezca la longitud length antes de comenzar la consulta.

Ejemplos variados:


Como es sabido, tenemos un comando para toda la creación de objetos en la base de datos Oracle. Este es un comando bastante poderoso y podemos usarlo para extraer la definición de tabla de todas las tablas en el esquema también.




30 septiembre 2018

Otra cosa interesante que hacer con la "tabla" DUAL

Esta entrada en el blog es pequeña a modo de receta, veremos una aplicación que se puede usar con dual, aparte de las consabidas y ya habituales. A veces es bastante útil tener una consulta que siempre regresa a un cierto número de filas, imagina que te piden un informe que devuelva una fila para cada día del mes. El uso de DUAL y CONNECT BY le permite devolver tantas filas como sea necesario.
Por ejemplo, esta consulta devuelve una fila para cada día del mes.

En la siguiente imagen podemos ver como construir esta sentencia y su resultado en SQL DEVELOPER:



24 noviembre 2017

Monitorización de índices en base de datos Oracle: El mal necesario


Para aquellos que no están familiarizados con él, monitorizar índices es la forma de la base de datos Oracle de rastrear si se está usando un índice, lo que le permite saber si es necesario o no. O sea, como vengo a decir normalmete si "el índice se ha ido a Parla"

Dos cosas para tener en cuenta:
  • No siempre marca un índice como se usa, incluso si se usa. Si no se utiliza en un plan de ejecución, pero se utiliza para aplicar una clave externa o una restricción única, no se marcará como se usa.
  • La vista utilizada para ver el uso del índice es específica del esquema. Es posible que esté supervisando índices, pero los índices no se mostrarán en v$object_usage a menos que inicie sesión como propietario del esquema. Es mejor ir directamente a la consulta subyacente para ver todos los índices supervisados (consultar más abajo).
Dado que la monitorización de un índice tiene un costo muy bajo, tiene sentido activarlo para todos los índices candidatos. Los índices de FK e índices únicos funcionan, incluso si no se utilizan en los planes de ejecución, por lo que no son candidatos para descartar. Aquí está la consulta para obtener todos los índices non-unique, non-FK, se asume que no hay PK concatenadas, (Por Tutatis!) si eso ocurre, la consulta se vuelve más complicada:

SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
  FROM all_ind_columns ic, all_indexes i
 WHERE i.uniqueness = 'NONUNIQUE' --no monitoriza índices únicos
   AND i.table_owner = 'NOMBRE_ESQUEMA'
   AND ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.position = 1
   AND NOT EXISTS (SELECT 'x' --No monitoriza índices en FK 
                     FROM all_cons_columns cc, all_constraints c
                    WHERE ic.table_name = cc.table_name
                      AND ic.column_name = cc.column_name
                      AND c.constraint_name = cc.constraint_name
                      AND c.constraint_type IN ('R'));

Aquí está la consulta para ver los objetos monitoreados si no se ha iniciado sesión como propietario del esquema:

select d.username, io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring 
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'SCHEMA_OWNER_HERE'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;



Y aquí hay un ejemplo de monitorización de índice en acción, incluido el uso del índice exclusivo que no se marca:

CREATE TABLE test_monitoring AS SELECT level id, dbms_random.value(1,1000) value FROM dual CONNECT BY LEVEL <= 5000;

Table created.

CREATE UNIQUE INDEX test_monitoring_idx ON test_monitoring(id);

Index created.

ALTER INDEX test_monitoring_idx MONITORING USAGE;

Index altered.

Forzamos el índice para la aplicación de PK, este no marca el índice como usado:

INSERT INTO test_monitoring VALUES (100,0);
INSERT INTO test_monitoring VALUES (100,0)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFA.TEST_MONITORING_IDX) violated 

SELECT index_name, used FROM v$object_usage WHERE index_name = UPPER('test_monitoring_idx');

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            NO                                               

Pero ejecutamos una sentencia "Select" que usará el índice

SELECT * FROM test_monitoring WHERE id = 100;

        ID      VALUE                                                           
---------- ----------                                                           
       100   255.5571                                                           
Y ahora el índice aparece como usado:

SELECT index_name, used FROM v$object_usage WHERE index_name = 'TEST_MONITORING_IDX';

INDEX_NAME                     USE                                              
------------------------------ ---                                              

TEST_MONITORING_IDX            YES 

Tener índices sin usar en la base de datos puede quitarnos espacio necesario y reducir el rendimiento en las sentencias  de borrado o actualización, por eso es importante eliminar aquellos que no son utilizados.



03 octubre 2017

Optimización de los Parámetros de UNDO


A Partir de Oracle 9i, los segmentos de rollback pasan a llamarse Undo Logs. Tradicionalmente, la información de transacciones (UNDO) se almacenaba en Rollback segments hasta el COMMIT o ROLLBACK. Oracle9i permite la gestión automática de UNDO. El DBA para especificar cuánto tiempo debe mantenerse la información de UNDO, para prevenir los errores "snapshot too old" en consultas largas. Esto se hace mediante el parámetro UNDO_RETENTION. 
El valor por defecto es de 900 segundos (15 minutos), Y puedes cambiar este parámetro para garantizar que Oracle mantiene los registros de UNDO largos períodos de tiempo. En lugar de tener que definir y gestionar los segmentos de rollback, puedes simplemente definir un tablespace UNDO y dejar que se ORACLE se encargue de lo demás. En cuanto a la gestión automática de Undo es fácil. Todo lo que necesitas es crear un Tbs UNDO y poner el UNDO_MANAGEMENT = AUTO. Sin embargo vale la pena para ajustar los siguientes parámetros: 

  • El tamaño del Tbs UNDO. 
  • El Parámetro UNDO_RETENTION. 
  • Calcular el UNDO_RETENTION para el Undo Tablespace. 


Puedes optar por asignar un determinado tamaño del tablespace UNDO y, a continuación, establecer el UNDO_RETENTION a un valor óptimo de acuerdo con el tamaño de UNDO y la actividad de la base de datos. Si su espacio en el disco es limitado y no quiere asignar más espacio del necesario para el Tbs Undo, esta es la forma de proceder. 
 La siguiente consulta le ayudará a optimizar el 
UNDO_RETENTION (Optimo) = UNDO SIZE Actual / DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEQ 

Dado que estas consultas usan las estadísticas V$UNDOSTAT, ejecute las consultas sólo después de que la Base de datos lleve funcionando con UNDO para un importante y representativo periodo de tiempo.


Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.STATUS = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#; 

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat; 
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size'; 

Optimal Undo Retention

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.STATUS = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/ 

Calcular el UNDO necesario para la actividad de la BBDD

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.STATUS = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/

28 noviembre 2016

V$SESSION : Sácale partido a esta vista


¿Qué es V$SESSION?

La vista V$SESSION es la base de toda la información relacionada con el estado actual del sistema:
  • ¿Cuántas sesiones hay actualmente corriendo en la base de datos?
  • ¿Qué consulta se está ejecutando?
  • ¿Cuánto tardan en ejecutarse estas consultas?
 V$SESSION es el primer lugar cuando DBA comienza a buscar información relacionada con el rendimiento  e información de la ejecución de las consultas, un DBA puede llegar a consultar un centenar de veces al día esta vista, así que arrojemos un poco de luz sobre esta vista.



A efectos prácticos este artículo está basado en una base de datos Oracle 11g, usaremos:
  • V$session: para una estancia sencilla.
  • GV$session: para una instalación en cluster (RAC).

Listado de las sesiones ejecutándose


Lo primero que un DBA busca en nuestra vista favorita es la lista de sesiones de usuario de bases de datos. Hay dos tipos de sesiones de base de datos, principalmente background y sesiones de usuario. La sesión background se utiliza para la funcionalidad básica de la base de datos como dbwr, arch, smon, pmon etc. La sesión del usuario es la sesión que realiza las operaciones del usuario.

Cada vez que se conecta a los datos se crea una sesión en la base de datos para realizar sus operaciones. Un DBA puede ver fácilmente esto consultando la vista de sistema V$SESSION.

Actualmente sólo hay una sesión de usuario en ejecución. 
SQL> select count(*),type from v$session group by type;
  COUNT(*) TYPE
---------- ----------
         1 USER
        49 BACKGROUND
En el caso del entorno RAC, DBA debe utilizar GV$SESSION en lugar de V$SESSION.
   
SQL> select count(*),type,INST_ID from gv$session group by type,inst_id;
 COUNT(*) TYPE              INST_ID
----------  ---------- ----------
        21  USER            1
        48  BACKGROUND       1
        49  BACKGROUND       2
        17  USER         2

Supongamos que tenemos dos nodos RAC, la instancia 1 tiene 21 usuarios y 48 sesiones en background, mientras que la instancia 2 tiene 17 usuarios y 49 sesiones en background. Esta información es útil cuando se produce el siguiente error "ORA-00020: maximum number of processes (%s) exceeded" error. Puede utilizar la consulta siguiente para identificar qué usuario está creando un número alto de sesiones.
SQL> select SID,USERNAME,COMMAND,PROCESS,TERMINAL,PROGRAM from gv$session where type='USER';
       SID USERNAME                          COMMAND PROCESS                  TERMINAL                       PROGRAM
---------- ------------------------------ ---------- ------------------------ ------------------------------ --------
        16 SYS                                    47 17978                                                   oraagent.bin@dbarm2 (TNS V1-V3)
        19 DBSNMP                                  0 1234                     unknown                        JDBC Thin Client
        25 SYSMAN                                  0 1234                     unknown                        OMS
        26 DBSNMP                                  0 1234                     unknown                        JDBC Thin Client
  • SID es la ID de la sesión, USERNAME es el nombre del usuario de la base de datos.
  • Process es el número de proceso. 
  • Terminal es el nombre del sistema que esta ejecutando la consulta. 
  • Program muestra el nombre del programa que esta usando la consulta.

Terminal y Program son los campos más importantes para encontrar las sesiones culpables de los errores ORA-00020.

Es una lista del uso de la vista V$SESSION, he enumerado algunos de ellos aquí. Por favor, comparte en los comentarios si sabéis más.

Encontrar sesiones bloqueantes

Una queja común al administrador de la base de datos por el usuario es "mi conexión de la base de datos es muy lenta". En este caso DBA debe comprobar dos cosas O toda la base de datos se está ejecutando lento o Sólo una sesión de usuario es lenta. Para comprobar el estado completo de la base de datos, DBA puede utilizar Top Command, Watcher de OS e Informe AWR.
Si está seguro de que sólo una sesión única se está ejecutando lento entonces V$session o gv$session es el lugar perfecto para empezar. He visto muchos casos en los que una sesión está bloqueando otra sesión debido a transacciones no confirmadas.

SQL> select sid, username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION WAIT_CLASS
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ----------------- ---------------- ---------------
        47 SCOTT               6 ACTIVE   sqlplus@database.example.com (TNS V1-V3)         2rbwgj3zdsnus                 1               34 Application

En el caso anterior, el usuario scott no recibía ninguna respuesta de su consulta. Comprobé utilizando la consulta anterior en la que "BLOCKING_SESSION" muestra el detalle de la sesión que está bloqueando la sesión de usuario SCOTT
Los campos: 
  • SID, 
  • USERNAME,
  • COMMAND, 
  • STATUS, 
  • PROGRAM y 
  • SQL_ID 
son detalles del usuario SCOTT, BLOCKING_INSTANCE significa en qué instancia se está ejecutando la sesión de bloqueo, BLOCKING_SESSION es el ID de sesión de la sesión de bloqueo.
Ahora el siguiente plan de acción para DBA es comprobar lo que está causando que la sesión 34 bloquee la sesión de scott. El DBA puede utilizar la consulta siguiente para averiguar los detalles de la sesión 34.
SQL> select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where sid=34;
SID USERNAME      COMMAND STATUS   PROGRAM  SQL_ID BLOCKING_INSTANCE             BLOCKING_SESSION     WAIT_CLASS
--- ---------- ---------- -------- ------------------------------------------------ ------------- ------------
34 SCOTT               3 INACTIVE sqlplus@database.example.com (TNS V1-V3)         17d40vwcct4g6       Idle
Sin embargo, podría haber muchas razones para este tipo de problema. Uno de ellos lo he discutido aquí.

21 noviembre 2016

Tareas de un DBA (primera parte)

Esta es una compilación de tareas que un administrador de base de datos ha de ejecutar  de forma diaria, semanal, mensual y otras sin un periodo definido.

Actividad diaria 

En esta entrada nos centraremos en las tareas diarias.

Comprueba si la instancia Oracle está funcionando o no.

1.     Sistema operativo Windows: mirar el programa services.msc
2.     Sistema operativo Unix: ps -ef | grep pmon
3.     SQL: SQL> select status from v$instance;

Comprueba si los listeners Oracle están funcionando o no

Para que desde fuera del servidor donde está instalada la base de datos Oracle se pueda acceder a la misma el servicio denominado listener ha de estar activado, o como se suele decir, el listener de Oracle ha de estar escuchando.
Puede pasar que la base de datos esté correctamente levantada y no se pueda conectar desde otros servidores, que también están correctamente configurados (TNSNAMES correcto, etc.). En estos casos puede ser que el listener tenga algún problema, o simplemente que no haya sido iniciado. En ese caso tan sólo habría que arrancar el listener.
Consultar el estado del mismo, arrancarlo o pararlo es muy sencillo. Sólo hay que abrir una sesión de línea de comandos (consola, terminal, etc. ) con el usuario con el que se ha instalado la base de datos, y ejecutar el comando lsnrctl con los siguientes parámetros para cada caso:
·         Comprobar su estado: > lsnrctl status
·         Parar el listener:          > lsnrctl stop
·         Levantar el listener:     > lsnrctl start

Comprueba si hay sesiones que bloquean otras sesiones 

En Oracle hay una vista v$lock que nos indica los objetos que se encuentran en bloqueo, el identificador de usuario y sesión y el tipo de bloqueo.
Una “join” con la tabla dba_objects nos proporciona además el nombre y tipo de los objetos bloqueados:
Existen principalmente dos tipos de bloqueo:
·         Bloqueos de tablas (TM) 
·         Bloqueos a nivel de fila (TX)
Los bloqueos a nivel de tabla son creados cuando se ejecuta una sentencia DML del tipo: update, insert, delete, select ..for update sobre la tabla entera. 
Los bloqueos a nivel de fila se crean cuando se ejecutan sentencias DML contra un conjunto de registros específicos.
Una consulta sobre esta vista nos permite rápidamente saber que procesos están bloqueados y si además hacemos un join con v$open_cursor podemos ver que consulta es la que se encuentra parada a la espera de que se produzca el desbloqueo para poder ejecutarse. En la consulta siguiente podemos ver las sentencias paradas y el id de proceso que las está bloqueando.

Esta consulta permite ver los objetos que están esperando a que termine un bloqueo y la sentencia que quieren ejecutar. el id de proceso nos da la pista de quien esta bloqueando:
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): '||U.NAME||'.'||O.NAME||
' - Mode held: '||
decode(L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_LOCKER.LMODE))||
' / Mode requested: '||
decode(L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
sys.OBJ$ O,
sys.USER$ U
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TM')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L_WAITER.ID1 = O.OBJ#
and U.USER# = O.OWNER#
union
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK L1_WAITER,
V$OPEN_CURSOR O
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TX')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L1_WAITER.LADDR = L_WAITER.ADDR
and L1_WAITER.KADDR = L_WAITER.KADDR
and L1_WAITER.SADDR = O.SADDR
and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE

Comprueba el “alert log” si hay errores

El registro de alertas es un registro cronológico de mensajes y errores, e incluye los siguientes elementos:

  • Todos los errores internos (ORA-600),
  • Errores de corrupción de bloques (ORA-1578)
  • Errores de bloqueo (ORA-60)
  • Operaciones administrativas, como sentencias CREATE, ALTER y DROP y instrucciones STARTUP, SHUTDOWN y ARCHIVELOG.
  • Mensajes y errores relacionados con las funciones de los procesos de servidor compartido y despachador.
  • Errores que ocurren durante la actualización automática de una vista materializada.
  • Los valores de todos los parámetros de inicialización que tenían valores no predeterminados al iniciarse la base de datos y la instancia.

Los archivos de seguimiento se escriben en nombre de los procesos del servidor siempre que se produzcan errores críticos. Además, al establecer el parámetro de inicialización SQL_TRACE = TRUE, el recurso de rastreo SQL genera estadísticas de rendimiento para el procesamiento de todas las sentencias SQL de una instancia y las escribe en el repositorio de diagnóstico automático. 
Opcionalmente, puede solicitar que se generen archivos de seguimiento para los procesos del servidor. Independientemente del valor actual del parámetro de inicialización SQL_TRACE, cada sesión puede habilitar o deshabilitar el registro de trazas en nombre del proceso del servidor asociado mediante la instrucción SQL ALTER SESSION SET SQL_TRACE

Este ejemplo habilita el recurso de rastreo de SQL para una sesión específica:
ALTER SESSION SET SQL_TRACE TRUE;
Puede ver y cambiar las configuraciones de umbral para las métricas de alertas del servidor mediante los procedimientos SET_THRESHOLD y GET_THRESHOLD del paquete de PL / SQL, DBMS_SERVER_ALERTS.
SELECT
    metrics_name,
    warning_value,
    critical_value,
    consecutive_occurrences
FROM
    dba_thresholds
WHERE
    metrics_name LIKE '%CPU Time%';

Vista
Descripción
DBA_THRESHOLDS
Enumera los valores de umbral definidos para la instancia
DBA_OUTSTANDING_ALERTS
Describe las alertas pendientes de la base de datos
DBA_ALERT_HISTORY
Enumera un historial de alertas que se han borrado
V$ALERT_TYPES
Proporciona información como grupo y tipo para cada alerta
V$METRICNAME
Contiene los nombres, identificadores y otra información sobre las métricas del sistema
V$METRIC
Contiene valores de métrica a nivel de sistema
V$METRIC_HISTORY
Contiene un historial de valores de métrica a nivel de sistema

Utilice los paquetes DBMS_SESSION o DBMS_MONITOR si desea controlar el seguimiento de SQL para una sesión.

El paquete DBMS_MONITOR le permite utilizar PL / SQL para controlar el rastreo adicional y la recopilación de estadísticas.


Proceso PLSQL
Descripción
CLIENT_ID_STAT_DISABLE
Inhabilita la recopilación estadística habilitada previamente para un identificador de cliente determinado
CLIENT_ID_STAT_ENABLE
Permite la recopilación estadística de un determinado identificador de cliente
CLIENT_ID_TRACE_DISABLE
Deshabilita la traza activada previamente para un identificador de cliente determinado globalmente para la base de datos
CLIENT_ID_TRACE_ENABLE
Habilita la traza de un identificador de cliente determinado globalmente para la base de datos
DATABASE_TRACE_DISABLE
Deshabilita la traza de SQL para toda la base de datos o una instancia específica
DATABASE_TRACE_ENABLE
Habilita la traza de SQL para toda la base de datos o una instancia específica
SERV_MOD_ACT_STAT_DISABLE
Inhabilita la recopilación estadística activada para una combinación dada de Nombre del servicio, MÓDULO y ACCIÓN
SERV_MOD_ACT_STAT_ENABLE
Permite la recopilación de estadísticas para una combinación dada de Nombre del servicio, MÓDULO y ACCIÓN
SERV_MOD_ACT_TRACE_DISABLE
Deshabilita la traza para TODAS las instancias habilitadas para una combinación o una combinación dada de Nombre de servicio, MÓDULO y ACCIÓN nombre globalmente
SERV_MOD_ACT_TRACE_ENABLE
Habilita el rastreo de SQL para una combinación dada de Nombre de servicio, MÓDULO y ACCIÓN globalmente a menos que se especifique un nombre_instancia
SESSION_TRACE_DISABLE
Deshabilita la traza previamente habilitada para un identificador de sesión de base de datos (SID) en la instancia local
SESSION_TRACE_ENABLE
Habilita la traza de un identificador de sesión de base de datos (SID) en la instancia local

Comprueba si hay dbms jobs ejecutándose y comprueba los estados del mismo.

ORACLE ofrece una cola para planificar operaciones rutinarias en una base de datos. La funcionalidad de los Jobs de Oracle es parecida al cron de UNIX en el cual se puede planificar una tarea, a una determinada hora y con una periodicidad concreta pero en base de datos.
El paquete encargado de hacer esta planificación es DBMS_JOB.

Para que cualquier Job de Oracle se pueda ejecutar tenemos que tener en cuenta el parámetro job_queue_processes no esté a cero, ya que es el que nos indica el número de colas que gestionarán nuestros jobs. Este parámetro debe de ser mayor del número de jobs que se desee ejecutar de forma simultánea (el máximo es 1000 para Oracle).
Las vistas que podemos usar para manejar los jobs son:
  • DBA_JOBS: Muestra la información de todos los jobs de la base de datos.
  • ALL_JOBS: Muestra la misma información que dba_jobs pero sólo los jobs a los cuales puede acceder el usuario actual con el que se está realizando la consulta.

Para consultar todos los jobs:
select * from all_scheduler_jobs;
Consultar log de ejecución de jobs:
select * from all_scheduler_job_log order by log_date desc;
Consultar los jobs que están en ejecución:
select * from all_scheduler_running_jobs;
Detener un job:
exec dbms_scheduler.stop_jop('USER.JOB');
Se puede forzar la parada de un job ejecutando la siguiente query como usuario system:
exec dbms_scheduler.stop_job('USER.JOB', true);
Deshabilitar un Job:
exec dbms_scheduler.disable('USER.JOB');
Se puede forzar la deshabilitación de un job ejecutando la siguiente query como system:
exec dbms_scheduler.disable('USER.JOB', true);
Habilitar un Job:
exec dbms_scheduler.enable('USER.JOB');
Arrancar un Job:
exec dbms_scheduler.run_job('USER.JOB');

15 noviembre 2016

PL / SQL: Evitar algoritmos innecesariamente complejos



Si tienes un equipo de  programadores nuevos en PL / SQL (y SQL), no es raro encontrar que complican demasiado las cosas, escribiendo más código de lo necesario y poniendo demasiada lógica en PL / SQL. Ese problema puede ser agravado por accidentalmente obtener la "respuesta correcta" basada en pruebas inadecuadas y datos de prueba.

Supongamos que tengo una tabla y un conjunto de datos como sigue:



# 1. Éxito accidental con datos de prueba incorrectos


CREATE TABLE undead_type
(
   undead_id     INTEGER PRIMARY KEY,
   undead_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (1, 'Skeleton');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (2, 'Zombie');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (3, 'Ghoul');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (4, 'Wight');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (5, 'Wraith');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (6, 'Mohrg');
  INSERT INTO undead_type (undead_id, undead_name)
        VALUES (7, 'Ghast');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (8, 'Mummy');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (9, 'Lich');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (10, 'Demilich');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (11, 'Vampire Spawn');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (12, 'Vampire');
   INSERT INTO undead_type (undead_id, undead_name)
        VALUES (13, 'Allip');
   COMMIT;
END;
/
Y tengo que escribir un programa que produce la siguiente salida: 
Undead en Orden Alfabético: 
  • Allip 
  • Demilich 
  • Ghast
  • ...
DECLARE
   l_count   INTEGER;
   l_name    plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT COUNT (*) INTO l_count FROM plch_animals;

   FOR indx IN 1 .. l_count
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

# 2. Demasiado SQL, datos insuficientes

DECLARE
   TYPE undead_ids_t IS TABLE OF undead_type.undead_id%TYPE;

   l_undead_ids   undead_ids_t;
   l_name         undead_type.undead_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('undead in Alphabetical Order');

   SELECT undead_id
     BULK COLLECT INTO l_undead_ids
     FROM undead_type;

   FOR indx IN 1 .. l_undead_ids.COUNT
   LOOP
      SELECT undead_name
        INTO l_name
        FROM undead_type
       WHERE undead_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

# 3. Vamos a pasarlo bien con las conexiones PL/SQL con colecciones

DECLARE
   TYPE undead_ids_t IS TABLE OF undead_type.undead_id%TYPE
      INDEX BY undead_type.undead_name%TYPE;

   l_undead_ids   undead_ids_t;
   l_index        undead_type.undead_name%TYPE;
   l_name         undead_type.undead_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('undeads in Alphabetical Order');

   FOR rec IN (  SELECT *
                   FROM undead_type
               ORDER BY undead_name DESC)
   LOOP
      l_undead_ids (rec.undead_name) := rec.undead_id;
   END LOOP;

   l_index := l_undead_ids.FIRST;

   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.put_line (l_index);

      l_index := l_undead_ids.NEXT (l_index);
   END LOOP;
END;
/

#4. SQL Simple

SELECT undead_name FROM undead_type
 ORDER BY undead_name;

No es mucho más simple que eso. Y si lo necesita dentro de PL / SQL

 #5. PL/SQL Simple

 BEGIN
   DBMS_OUTPUT.put_line ('undead in Alphabetical Order');

   FOR rec IN (SELECT undead_name FROM undead_type
                ORDER BY undead_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.undead_name);
   END LOOP;
END;


Lecciones aprendidas 


  •  Deje a SQL hacer el levantamiento de pesas, tanto como sea posible (no es que haya algo muy "pesado" para levantar en este ejercicio!) 
  • No sobre-complicar las cosas. Asegúrese de que sus datos de prueba tengan suficiente volumen y variedad para ejercer realmente su algoritmo. 
  • Si te encuentras pensando "¿tiene que ser tan complicado?", Casi con toda seguridad la respuesta es un resonante "No!" Y debe dar un paso atrás, desafiar sus suposiciones, y ver cómo se puede simplificar su código