Analizando sentencias SQL con base de datos Oracle

Para analizar las sentencias SQL con bases de datos Oracle contamos con las siguientes herramientas propias del Oracle, sin embargo, también es necesario conocer de mejores prácticas de programación y diseño para poder recomendar cambios para optimizar una sentencia SQL. 

EXPLAIN PLAN

AUTOTRACE

SQL Trace

TKPROF

Backups o Respaldos de base de datos

Es necesario que los backups o respaldos de las base de datos esten acordes con las necesidades de negocio de la empresa u organización a la que pertenecen.

Los backups en frio (off-line) de bases de datos Oracle se usan en empresas/negocios que hacen uso de la BD solo en horarios de oficina o solo en días laborables por ejemplo lo que permite aprovechar las horas de la madrugada o fines de semana para bajar los servicios de los sistemas que trabajan con la BD y la BD misma para poder tomar el backup en frio (off-line). A este tipo de backup tambien se le conoce como backup CONSISTENTE porque todos los archivos estan sincronizados (tiene la misma hora de actualización) y en caso de una restauración no requieren de archivos de cambios (archivos) para que puedan ser abierta la base de datos.


-- SQL para obtener un listado de archivos de la base de datos Oracle que necesita incluir en un backup en frio (off-line)
SELECT 'DATAFILE' AS TIPO, NAME
FROM V$DATAFILE
UNION ALL
SELECT 'TEMPFILE' AS TIPO, NAME
FROM V$TEMPFILE
UNION ALL
SELECT 'CONTROLFILE' AS TIPO, NAME
FROM V$CONTROLFILE
UNION ALL
SELECT 'ONLINE REDO LOG FILE' AS TIPO, MEMBER
FROM V$LOGFILE;


Los backups en caliente (on-line) de bases de datos Oracle se usan en empresas/negocios que no pueden detener sus base de datos, es decir, que operan las 24 horas de día sin interrupción. A este tipo de backup tambien se le conoce como backup INCONSISTENTE porque los archivos no estan sincronizados (no tiene la misma hora de actualización)  y en caso de una restauración requieren de archivos de cambios (archives) para que pueda ser abierta la base de datos.

-- SQL para obtener un listado de archivos de la base de datos Oracle que necesita incluir en un backup en caliente (on-line)
SELECT 'DATAFILE' AS TIPO, NAME
FROM V$DATAFILE;

-- Adicionalmente es necesario contar con un respaldo de los archivos de cambios (archives)

Objetos de base de datos

¿Cómo obtener informacion sobre los objetos de una base de datos Oracle?

-- Objetos de base de datos con el mismo nombre
SELECT OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
FROM DBA_OBJECTS
WHERE UPPER(OBJECT_NAME) = UPPER('NOMBRE DEL OBJETO');

-- Espacio en MB que ocupa un objeto segmento (tabla, indice ...)
SELECT OWNER,
SEGMENT_TYPE,
SEGMENT_NAME,
BYTES / (1024*1024) as Megabytes
FROM DBA_SEGMENTS
WHERE UPPER(SEGMENT_NAME) = UPPER('NOMBRE DEL OBJETO');

Documentación sobre Base de Datos Oracle 9i

Administración de Base de Datos Oracle, Conceptos y Referencia

Oracle9i New Features (Nuevas Funciones de Oracle9i): Este manual describe las nuevas funciones, nuevas opciones y mejoras de Oracle9i. Identifica lo que hay disponible con cada edición de Oracle9i (Standard Edition, Enterprise Edition y Personal Edition). Hace referencia a la documentación disponible para Oracle9i e identifica las funciones que han perdido valor o soporte.

Oracle9i Administrator's Guide (Guía del Administrador de Oracle9i): Esta guía está destinada a los usuarios que administran la operación de un sistema de base de datos Oracle. Denominados administradores de base de datos (DBA), son responsables de la creación de bases de datos Oracle que aseguran una operación correcta y controlan su uso.

Consumo de recursos de sentencias SQL en base de datos Oracle

¿Cómo saber el consumo de recursos de las sentencias SQL recientemente ejecutadas o que estan ejecutándose.

-- Sentencias SQL que están ejecutandose:
SELECT * FROM GV$SQLAREA
WHERE USERS_EXECUTING > 0;

-- Consumo de las sentencias SQL x Instancia y Modulo
SELECT INST_ID,
MODULE,
COUNT(1) as Cantidad_SQL,
SUM(DECODE(EXECUTIONS,1,1,0)) as Cantidad_SQL_1_EXECUTION,
SUM(EXECUTIONS) as Total_EXECUTIONS,
SUM(SHARABLE_MEM) as Total_SHARABLE_MEM,
SUM(BUFFER_GETS) as Total_BUFFER_GETS,
SUM(DISK_READS) as Total_DISK_READS,
SUM(CPU_TIME)/1000000 as Total_CPU_TIME_Seconds,
SUM(ELAPSED_TIME)/1000000 as Total_ELAPSED_TIME_Seconds
FROM GV$SQLAREA
GROUP BY INST_ID, MODULE;

Sesiones de base de datos Oracle

¿Cómo saber el estado de las sesiones de base de datos?
Para averiguar sobre el estado de las sesiones de base de datos hemos elaborado las siguientes sentencias SQL que les seran de gran ayuda:

-- Cantidad de sesiones por Instancia:
SELECT INST_ID, count(1) as Cant_Sesiones
FROM GV$SESSION
GROUP BY INST_D;

-- Cantidad de sesiones por Instancia y por Estado:
SELECT INST_ID, STATUS, count(1) as Cant_Sesiones
FROM GV$SESSION
GROUP BY INST_ID, STATUS;

-- Cantidad de sesiones por Instancia y por Modulo:
SELECT INST_ID, MODULE, count(1) as Cant_Sesiones
FROM GV$SESSION
GROUP BY INST_ID, MODULE

-- Cantidad de Sesiones por Instancia, Estado y por Modulo:
SELECT INST_ID, STATUS, MODULE, count(1) as Cant_Sesiones
FROM GV$SESSION
GROUP BY INST_ID, STATUS, MODULE;