Aprende el PL/SQL de Oracle 10g. Escrito en español. PDF actualizado disponible en http://solucionjava.com/manuales_gratis.phpDescripción completa
OracleFull description
oracle
Description complète
ORACLE
Descripción: Oracle Forms By Oracle
PLSQLDescripción completa
administración financiera contabilidad
Full description
technicalFull description
Notes i collected during training
Tấn công rootkit
Clasa a XII a
consultas de oracle 10g, un total de 20 consultasDescripción completa
Descripción completa
Brief discussion about Oracle ASMFull description
Administracion ParametrosDescripción completa
PlsqlFull description
ORACLE DBADescripción completa
1 Introducción Resultado de solicitar varias veces un curso de administración de Oracle. Mucha materia. Vamos a ver muchas cosas, pero no es exhaustivo.
Conceptos básicos generales, arquitectura y funcionamiento. Otros apartados más específicos, útiles para el trabajo del día a día de algunos de nosotros.
Dificultad para estructurar la información. Muchos conceptos se entremezclan. No se pueden explicar las estructuras de memoria sin hablar simultáneamente los procesos, ni de índices sin ver conceptos de optimización y estadísticas. Conocimiento asistentes muy diferente. Es posible que durante las explicaciones algunos conceptos se supongan conocidos. Ante cualquier cualquier duda, preguntar. Es preferible que los asistentes no tengan ordenadores. Podemos ver más materia.
2 ¿Qué es una base de datos? Hay distintos tipos de bases de datos. Oracle es una base de datos relacional. A grosso modo, es un conjunto de ficheros en los que se almacena información de forma estructurada en forma de tablas. Cada tabla contiene registros, formados a su vez por campos. Estos campos están identificados con su nombre, tipo y tamaño. Otros componentes que también forman parte de una base de datos son, entre otros, las vistas, las funciones y procedimientos, las secuencias y los disparadores (triggers) y los servicios necesarios para su funcionamiento. Términos con los que tenemos que estar familiarizados: tabla, registro, campo, vista, procedimiento, función, paquete, secuencia, disparador, esquema, sinónimo, índice. Vista: agrupación lógica de parte de una o más tablas.
5
Esquema: conjunto de todos los objetos que pertenecen a un usuario.
3 Instalación de Oracle Oracle está disponible en la página web, sin restricciones (primera (pri mera versión, sin parches) Hay que distinguir entre la aplicación cliente y el servidor. 3 de las múltiples diferencias entre la versión Standard y y Entreprise:
Particiones de tablas Índices bitmap Uso de paralelismo
Scripts para instalaciones locales. De esta forma no es necesario tener los servicios levantados (ver servicios): Start Oracle.bat
net start OracleOraDb11g_home1TNSListener net start OracleServiceORCL pause Stop Oracle.bat
net stop OracleServiceORCL net stop OracleOraDb11g_home1TNSListener pause
Puntos a tener en cuenta:
nombre (SID): identificador de la base de datos en un servidor Contraseñas de los usuarios sys y system
6
4 Arquitectura de la base de datos Tres componentes principales:
Ficheros: de parámetros, de datos, de control, temporales y de redo log.
Estructuras de memoria: SGA (System Global Area)
Procesos
Dos términos que causan confusión:
Database: una colección de ficheros
Instancia: conjunto de procesos y la SGA (System Global Area)
Una base de datos (conjunto de ficheros) puede ser montada y abierta por más de una instancia (conjunto de procesos y la SGA), aunque en la mayoría de los casos la relación es de 1 a 1. En un ordenador para pruebas podemos tener una instancia y varias bases de datos y montar y abrir una de las bases de datos. Para ello tendríamos distintos ficheros de configuración, cada uno de los cuales abriría una de las bases de datos. Una instancia solo puede estar conectada a una base de datos.
7
La SGA contiene estructuras de datos a las que acceden los procesos, como son la cache de datos, la cache de redo de los datos modificados, los planes de ejecución de SQL, etc. Si Oracle estuviese instalado en un servidor Unix podríamos ver los procesos de fondo: $ /bin/ps ‐aef | grep ora
En Windows, solo vemos el servicio Oracle.exe. Oracle.exe. Este servicio tiene varios hilos, h ilos, uno por proceso, aunque para poder verlos necesitamos alguna herramienta especial. Esquema de conexión a Oracle:
8
El modo de conexión más habitual es el de servidor dedicado. dedicado. En este modo, Oracle crea un proceso para cada sesión. Este proceso hace de interfaz entre el cliente y la base de datos. Recibe las sentencias que le l e enviamos, select , updates, updates, etc., efectúa las operaciones y nos devuelve las respuestas. El cliente "habla" directamente con el proceso dedicated server a a través de TCP/IP u otro mecanismo de comunicación. Existe otro modo de trabajo: el de servidor multi-hilo ( multi-hilo (multi-threaded multi-threaded server o o MTS ). ). Este modo se utiliza cuando el número de clientes conectados es muy grande. Con el modo de servidor dedicado, si queremos tener 10.000 conexiones abiertas, Oracle necesitaría crear 10.0000 procesos. Con el modo MTS es posible que con 100 o 200 procesos (servidores compartidos) podamos dar servicio a esos 10.000 clientes. Una diferencia importante entre este modo y el modo de servidor dedicado es que los clientes no se comunican directamente con los servidores compartidos. En su lugar, los clientes se comunican con un proceso o conjunto de procesos llamados dispatchers. dispatchers. Estos dispatchers ponen dispatchers ponen las peticiones de los clientes en una cola. El primer servidor compartido que queda libre toma la petición de la cola, la procesa y deja el resultado en una cola de respuestas. El dispatcher está está continuamente monitorizando la cola de respuestas para enviar los resultados a los clientes correspondientes.
9
Normalmente, los servidores compartidos (procesos) se crean al levantar la base de datos.
4.1 Conexiones Cuando nos conectamos a Oracle con un comando como el siguiente: C:\> sqlplus CURSO/curso@ORCL
la aplicación cliente leerá el fichero tnsnames.ora que tnsnames.ora que suele estar en el directorio [ORACLE_HOME]\network\admin [ORACLE_HOME]\network\admin para para averiguar la dirección del servidor y el modo de conexión. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) )
Con esta información, el cliente ya puede establecer una conexión con la dirección IP localhost por por el puerto 1521. 1521.
10
Si el servidor está bien configurado, debe haber un proceso llamado TNS Listener que que está a la escucha de nuevas peticiones de conexión. Si la conexión solicitada es de servidor dedicado, el listener crea crea un nuevo servidor dedicado (un proceso) y el cliente es redireccionado a este nuevo proceso. La conexión está establecida.
Si por el contrario, la conexión solicitada es de servidor compartido, el listener , que conoce los dispatchers que dispatchers que se están ejecutando en el servidor, elegirá uno y enviará al cliente los datos para que se pueda conectar a él. En ese momento, el cliente se desconecta del listener y y se conecta al dispatcher quedando quedando la conexión establecida.
11
Una instancia de Oracle puede usar ambos tipos de conexiones simultáneamente.
4.2 Ficheros Ficheros que conforman la base de datos: d atos:
Ficheros de datos: datos: contienen las tablas, índices y otros segmentos.
Redo log files: files: ficheros en los que se van guardando las transacciones.
Ficheros temporales: temporales: usados como almacenamiento temporal y espacio para realizar ordenaciones en disco.
Ficheros de control: control: indican la localización de los ficheros de datos.
El único fichero necesario para levantar una instancia es el fichero de parámetros.
12
4.2.1 Fichero de parámetros
Es un fichero de texto, conocido como fichero PFILE. Se suele llamar ora.ora. Así, si el nombre de nuestra base de datos es ORCL, el nombre del fichero de parámetros sería oraORCL.ora. Suele estar en el directorio %ORACLE_HOME%/database. En muchos casos se puede comprobar que el fichero de parámetros contiene sólo una línea que apunta a otro fichero: IFILE='C:\oracle\product\10.2.0\db_ IFILE='C:\oracle\product\10.2.0\db_1\database\oraORCL 1\database\oraORCL.ora' .ora' SPFILE='C:\oracle\product\10.2.0\db SPFILE='C:\oracle\product\10.2.0\db_1\database\spfile _1\database\spfileORCL.ora' ORCL.ora'
También podemos arrancar la base de datos manualmente especificando como parámetro del comando startup un fichero diferente: SQL> startup pfile='C:\oracle\pfile\init.ora'
El número de parámetros así como el nombre de estos varía entre las diferentes versiones de Oracle. En las versiones más recientes de Oracle, se utiliza una variante del fichero PFILE conocida como SPFILE. Se debe llamar SPFILE.ora.
13
A diferencia del anterior, este fichero está en formato binario (no se puede editar con un editor de texto). Podemos crear un fichero SPFILE a partir de un fichero PFILE con el siguiente comando: SQL> create spfile from pfile='C:\oracle\pfile\init.ora';
y viceversa: SQL> create pfile='oraORCL.ora' from spfile='C:\oracle\product\10.2.0\db_ spfile='C:\oracle\product\10.2.0\db_1\database\spfileOR 1\database\spfileORCL.ora'; CL.ora'; SQL> create pfile from spfile;
Si no se especifican las rutas de los ficheros, se utiliza la ruta por defecto. Si en el directorio %ORACLE_HOME%/database hay un fichero de parámetros del tipo PFILE y otro del tipo SPFILE, Oracle arrancará con el SPFILE. Una de las ventajas del nuevo formato es que los parámetros se pueden modificar mediante el comando alter system. alter system set sessions = 200 scope = [SPFILE|MEMORY|BOTH]
Si el scope (ámbito, alcance) es SPFILE, el nuevo parámetro se modifica en el fichero SPFILE y se activará al reiniciar la instancia. Si es MEMORY se activa inmediatamente, sin modificar el fichero SPFILE. Si la instancia se ha arrancado con un fichero PFILE solo se podrá usar la opción MEMORY que sería la opción por defecto. Si la instancia se ha arrancado con un fichero SPFILE el valor por defecto es BOTH. Otra ventaja de usar un fichero SPFILE es que Oracle puede almacenar en él parámetros de ajuste internos calculados automáticamente. Ejemplos: vamos a ver el contenido de nuestro SPFILE: Nota: hay que conectarse como sysdba para poder realizar esta operación.
14
C:\>sqlplus sys/sys@ORCL as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Jue Sep 18 10:27:32 2014 Copyright (c) 1982, 2010, Oracle.
All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 With the Partitioning option
‐
64bit Production
SQL> create pfile from spfile; Archivo creado. SQL> exit Desconectado de Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Production With the Partitioning option
‐
64bit
C:\>type C:\oracle\product\11.2.0\dbhome_1\d C:\oracle\product\11.2.0\dbhome_1\database\initORCL.o atabase\initORCL.ora ra orcl.__db_cache_size=771751936 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='c:\oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=570425344 orcl.__sga_target=1090519040 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=251658240 orcl.__streams_pool_size=16777216 *.audit_file_dest='c:\oracle\admin\ORCL\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='D:\oracle\ORCL\con *.control_files='D:\oracle\ORCL\control01.ctl','D:\or trol01.ctl','D:\oracle\ORCL\control02 acle\ORCL\control02.ctl' .ctl' *.db_block_size=8192
Vamos a modificar el valor del parámetro sessions: SQL> show parameter sessions NAME java_max_sessionspace_size java_soft_sessionspace_limit license_max_sessions license_sessions_warning sessions shared_server_sessions
TYPE integer integer integer integer integer integer
VALUE 0 0 0 0 248
SQL> alter system set sessions=200 scope=SPFILE; Sistema modificado.
Podemos comprobar que el parámetro se ha establecido en el fichero SPFILE aunque todavía no esté activo. Este parámetro en concreto no se puede modificar en memoria, por lo que si intentamos usar el scope MEMORY o BOTH nos aparecerá un mensaje de error. 4.2.1.1 Parámetro DB_NAME
Es el único parámetro obligatorio en el fichero de parámetros. Cuando se crea una base de datos, su nombre se almacena en los ficheros de datos, los ficheros de redo log y los ficheros de control. Si el nombre de la base de datos en el fichero de parámetros no coincide con el nombre de la base de datos, no se podrá arrancar. 4.2.1.2 Parámetro DB_BLOCK_SIZE
Es uno de los parámetros más importantes de la base de datos. Define la unidad mínima de lectura y escritura.
17
Se recomienda que sea múltiplo del tamaño de bloque del sistema operativo. El tamaño que utiliza Oracle cuando se instala una base de datos suele ser correcto para la mayoría de los casos. Para averiguar el tamaño de bloque del sistema operativo: D:\Usuarios\aruiz>fsutil fsinfo ntfsinfo c: La utilidad FSUTIL requiere privilegios administrativos.
Ejecutamos la consola de comandos como administrador: C:\Windows\system32>fsutil fsinfo ntfsinfo c: Número de serie de volumen NTFS: Versión: Número de sectores: Total de clústeres: Clústeres vacíos: Total de clústeres reservados: Bytes por sector: Bytes por sector físico:
Bytes por segmento de registro de archivo: 1024 Clústeres por segmento de registro de archivo: 0 Tamaño válido de datos MFT: 0x0000000013d00000 LCN de inicio de MFT: 0x00000000000b65b2 LCN de inicio de MFT2: 0x00000000003f47e0 Inicio de zona MFT: 0x0000000000ff67c0 Fin de zona MFT: 0x0000000001001f00 Id. de Administrador de recursos (RM): E6171C6C 6A48 11E2 AED1 AB806DF76A1E ‐
‐
‐
‐
El tamaño a usar depende del tipo de aplicación:
18
Pequeño: 2kb, 4kb, para sistemas OLTP (Online Transaction Processing) Grande: 8kb, 16kb, 32kb, para sistemas DSS (Decision Support Systems)
En un sistema OLTP se hacen muchas lecturas aleatorias (dispersas). Si las filas son pequeñas y el tamaño del bloque es grande, como en cada lectura se lee un bloque, estaríamos leyendo muchas filas que quedarían en la cache y que nadie necesitaría. Por el contrario, en un sistema que hace un uso intensivo de consultas, caso de un sistema DSS, si el tamaño de bloque es grande, se leerán más filas con una sola operación física de lectura, con lo que reducimos el número de lecturas necesarias, que es lo más costoso. En general, si los registros son grandes (con campos LOB) y las lecturas son secuenciales (full scan), es mejor usar un tamaño de bloque mayor. Si las filas son pequeñas y hay muchos accesos aleatorios, es mejor usar un bloque pequeño. Así, por ejemplo, si tenemos un bloque de 8 kb y queremos leer una sola fila que ocupa 50 bytes, estaríamos desperdiciando 8192 - 50 bytes de la cache. Con un bloque pequeño, se puede llegar a producir más frecuentemente el efecto de filas encadenadas (row chaining,) si una fila no cabe en un bloque. El espacio se aprovecha mejor con con bloques grandes, ya que la proporción entre el espacio ocupado ocupado por la cabecera del bloque y el espacio espacio para los datos es menor. 4.2.2 Ficheros de datos
Son los ficheros donde se almacena la información. La jerarquía de almacenamiento de Oracle es la siguiente:
Una base de datos está compuesta por uno o más tablespaces. Un tablespace está compuesto por uno o más ficheros. Las tablas, índices, vistas materializadas, etc. se conocen como segmentos.
19
Los segmentos están compuestos por una o más extensiones, que son áreas contiguas de almacenamiento en un fichero. Una extensión es un conjunto contiguo de bloques. Las extensiones que componen un segmento (una tabla p. ej.) no tienen por qué pertenecer a un mismo fichero, aunque si deben estar en el mismo tablespace. Un bloque es la unidad mínima de lectura y escritura.
Un segmento, para crecer, necesita añadir nuevas extensiones, que no tienen por qué ser contiguas a las existentes, pudiendo incluso pertenecer a otro fichero, siempre que sea del mismo tablespace. Si necesitamos más espacio en un tablespace podemos bien aumentar el tamaño de sus ficheros de datos, bien añadirle nuevos ficheros de datos. Todos los bloques de un tablespace tienen el mismo tamaño y están divididos en tres partes: cabecera, datos y espacio libre.
20
4.2.2.1 Caso Caso práctico práctico CREATE TABLESPACE INDX DATAFILE 'D:\ORACLE\ORADATA\SIPLAMA\INDX1.DBF' SIZE 10G AUTOEXTEND ON NEXT 2G MAXSIZE 32G; ALTER TABLESPACE INDX ADD DATAFILE 'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF' SIZE 4G; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF' RESIZE 10G; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF' AUTOEXTEND ON NEXT 2G MAXSIZE 32G;
4.2.3 Diccionario del sistema
Las tablas del diccionario del sistema siempre están en el tablespace SYSTEM.
21
Vamos a usar dos tablas del diccionario del sistema para ver información sobre los fich eros de datos: select df.tablespace_name, df.file_name, to_char(df.blocks,'999,999,999') as blocks, to_char(df.bytes / 1024 / 1024,'999,999,999') as Mb, to_char(fe.free_blocks,'999,999,999') as free_blocks, to_char(fe.free_bytes / 1024 / 1024, '999,999,999') as free_Mb from dba_data_files df, ( select tablespace_name, file_id, sum(blocks) as free_blocks, sum(bytes) as free_bytes from dba_free_space group by tablespace_name, file_id ) fe where df.file_id = fe.file_id(+) order by df.tablespace_name, df.file_name;
4.2.4 Ficheros temporales
Oracle utiliza los ficheros temporales para almacenar resultados intermedios y realizar operaciones de ordenación grandes que no caben en memoria. También se almacenan en estos ficheros temporales las tablas e índices temporales. Estos ficheros están en un tablespace especial del tipo temporal. 4.2.5 Ficheros de control
Es un fichero binario, sin el cual no es posible arrancar la BD. Por ello es conveniente mantener varias copias del mismo, preferiblemente en diferentes discos. Las distintas copias se encuentran situadas en las rutas especificadas en el fichero de parámetros. Podemos ver la lista fe ficheros de control a través de la vista del sistema v$controlfile.
22
Contienen información como el nombre y fecha de creación de la base de datos, nombre de los t ablespaces, nombre y localización de los ficheros de datos y de redo, número de secuencia del redo log en curso, información de checkpoint , información del archivado de los redo log, etc. Para añadir un fichero de control tenemos que parar la base de datos (shutdown), hacer una copia de uno de los ficheros de control a nivel de sistema operativo, añadir la ruta de la copia en el parámetro control_files del fichero de parámetros init.ora o spfile y volver a arrancar la base de datos (startup). Es posible crear una copia visible del fichero control file con el comando: SQL> alter database backup controlfile to trace;
La traza se crea en el directorio indicado por el parámetro user_dump_dest. SQL> select * from v$parameter where name = 'user_dump_dest';
También podemos especificar la ruta en la que crear el fichero: SQL> alter database backup controlfile to trace as 'D:\Usuarios\aruiz\ 'D:\Usuarios\aruiz\Documents\Temp\con Documents\Temp\controlfile.txt'; trolfile.txt';
o hacer una copia binaria del fichero en una ruta concreta: alter database backup controlfile to 'D:\Usuarios\aruiz\Documents\Temp\co 'D:\Usuarios\aruiz\Documents\Temp\controlfile.bak'; ntrolfile.bak';
La vista v$database nos proporciona información sobre algunos de los parámetros almacenados en el fichero de control. 4.2.6 Ficheros Redo Log
Comenzar explicando qué son las transacciones: Transacciones Los ficheros redo log son cruciales para el funcionamiento de Oracle. En ellos se guardan todas las transacciones que se realizan en la base de datos, con objeto de poder repetirlas más adelante si es necesario:
23
Si insertamos un registro en un tabla, el resultado de la inserción se escribe en el redo log. Si borramos un registro, el hecho de que lo hemos borrado se escribe en el redo log. Si borramos una tabla, el contenido de la tabla no se escribe en el redo log, pero sí la modificación que se realiza en el diccionario de datos.
En definitiva, en los ficheros redo log se almacena la información necesaria para volver a repetir las últimas operaciones realizadas. Los utiliza Oracle para poder recuperar los datos en el caso de que se produzca un error en el sistema (hardware o software). Como veremos más adelante, también son una de las razones fundamentales de la eficiencia (velocidad) de Oracle. En caso de un fallo de alimentación eléctrica, Oracle usará los redo log online para restaurar los datos al mismo estado en que estaban antes de irse la luz. Esta afirmación necesita una explicación. Si cada operación que realizamos se escribe en los ficheros de d atos no se debería perder información al irse la luz.
Si un disco se rompe, Oracle puede usar los redo log archivados junto con los redo log online para restaurar un backup del disco al estado antes del fallo. Si un usuario borra una tabla accidentalmente, es posible restaurar un backup anterior y actualizar los datos hasta el momento anterior al accidente. ¿qué son los redo log archivados? archivados? ¿en qué se diferencian diferencian de los ficheros de redo log online?
Toda base de datos Oracle debe tener al menos dos ficheros de redo log online . ¿por qué? Oracle escribe en los redo log de una forma circular. Comienza escribiendo en el primero. Cuando este se llena comienza a sobrescribir el segundo y así hasta el último, después del cual vuelve a comenzar por el primero. Al cambio de un fichero de log a otro (cuando se pasa de escribir en uno a escribir en el siguiente) se le llama log switch. Este cambio puede hacer que una base de datos que no esté bien configurada se quede "parada" temporalmente. ¿por qué?
24
Para entender cómo funcionan los ficheros de redo log online y alguna de las afirmaciones hechas anteriormente necesitamos comprender algunos conceptos importantes como saber qué es un checkpoint , que es el buffer o cache de datos y que hace el proceso Database Block Writer (DBWn). El buffer o cache de datos es un área de memoria, más concretamente de la SGA (System Global Area), en la que se almacenan temporalmente los bloques cuando se leen de los ficheros de datos. La idea es evitar tener que leerlos otra vez si los necesitamos más adelante. Las operaciones más costosas de una base de datos son las de lectura y escritura (I/O). Si conseguimos disminuir estas operaciones, la respuesta de la base de datos será más rápida. Cuando se modifica un registro, la modificación se hace sobre el bloque que contiene el registro en el buffer de datos que está en la memoria. El bloque del fichero de datos no se toca. La información necesaria para repetir la operación también se guarda en otra área de la SGA conocida como redo log buffer . Cuando realizamos un commit para para hacer los datos permanentes (finalizar la transacción), Oracle no escribe los bloques modificados de la SGA a disco inmediatamente. Tan solo escribe el contenido del redo log buffer al al fichero de redo log online activo. Mientras que el bloque modificado no se haya escrito en el disco, necesitamos el respaldo del fichero redo log online por si la base de datos falla. Si después de hacer commit la la luz se va, el buffer de datos se perderá y por lo tanto perderemos los bloques modificados. En este caso, el único registro que tenemos de los cambios realizados estará en el fichero de redo log online activo . Cuando levantemos otra vez la base de datos, Oracle ejecutará las transacciones de este fichero, volviendo a realizar la modificación en el bloque de la cache de datos . Es por este motivo que hasta que los bloques modificados no se escriben en los ficheros de datos no se puede reutilizar el fichero de redo log online .
25
Es aquí cuando el proceso DBWn entra en escena. Es un proceso background de de Oracle responsable de hacer sitio en el buffer de datos cuando se llena y de hacer los checkpoints. Un checkpoint consiste consiste en la escritura de los bloques modificados (dirty blocks) del buffer de datos a disco. Hay varias circunstancias que pueden provocar un checkpoint , siendo la más habitual el cambio de fichero de redo log activo. Cuando el fichero de log 1 se llena, se cambia al fichero de log 2 y Oracle lanza un checkpoint . En ese momento el proceso DBW comienza comienza a escribir a disco todos los bloques modificados protegidos por el fichero de log 1. Hasta que el proceso DBWn no termina, Oracle no puede reutilizar el fichero de log 1. Si se intenta utilizar antes de que el proceso DBWn haya terminado, se interrumpen todos los procesos hasta que el proceso DBWn finaliza el y se escribe en el fichero de trazas el indeseado mensaje " Checkpoint not complete " (el fichero de trazas contiene mensajes checkpoint y informativos que genera el servidor relacionados con acciones como el arranque y parada de la instancia y eventos excepcionales como el que acabamos de ver).
26
Siempre que veamos este mensaje, sabremos que estamos induciendo esperas innecesarias para el usuario final que pueden ser evitadas. Uno de los objetivos de un DBA es definir el número suficiente y el tamaño de los redo logs online necesario para evitar la reutilización de uno de ellos antes de que finalice el checkpoint que que generó cuando se llenó. Si queremos, podemos forzar un checkpoint manual manual con la siguiente sentencia: SQL> alter system checkpoint;
¿Por qué no definimos muchos ficheros ficheros de redo log o hacemos que sean sean muy grandes y así evitamos evitamos siempre el problema? problema?
Cada tipo de aplicación genera diferentes cantidades de redo log. Un sistema DSS (Decision Support System) genera significativamente menos redo log que un sistema OLTP (Transaction Processing). Una aplicación que gestiones muchas imágenes en campos BLOB (Binary Large Objects) genera
más redo log que un sistema de pedidos. Un sistema con 100 usuarios generará una décima parte del redo log que genere una aplicación con 1.000 usuarios. No existe un valor único apropiado para el número ni el tamaño de los ficheros de redo log. Existen multitud de factores que hay que tener en cuenta. Así, por ejemplo, para mantener una base de datos de consulta sincronizada con otra (Standby Database), como se sincronizan enviando los ficheros de redo log que se van llenando, interesa usar muchos fichero pequeños para que la sincronización sea siempre cercana. En una aplicación en la que hay muchos usuarios modificando la misma información, interesa tener ficheros de redo log grandes para que los bloques se modifiquen tantas veces como sea posible en memoria antes de enviarlos a disco. Esto, sin embargo, puede hacer que el tiempo de recuperación después de una caída sea mayor. Utilizar ficheros pequeños también puede disminuir la eficiencia de la base de datos al producirse un mayor número de checkpoints, pero por el contrario, el tiempo de recuperación en caso de fallo también será menor. Podemos ver la cantidad de redo generado (en bytes) (google: oracle statistics descriptions 11g) select n.name, s.value
27
from v$mystat s, v$statname n where s.statistic# = n.statistic# and n.name like 'redo size'; create table t as select object_id, object_name, created from all_objects where rownum <= 10;
4.2.6.1 Ficheros de redo log archivados
Una base de datos Oracle puede funcionar en los modos ARCHIVELOG y NOARCHIVELOG. Cuando la base de datos está en modo ARCHIVEOG, Oracle guarda una copia del fichero redo log online antes de reutilizarlo. La única forma de garantizar que nunca se perderá información es trabajando en modo ARCHIVELOG. En modo ARCHIVELOG, si se produce un fallo, podemos recuperar una copia de seguridad anterior y aplicar los ficheros redo log archivados y los ficheros redo log online y restaurar la base de datos a la misma situación en la que estaba antes de la caída (o cualquier otro momento que elijamos). Podemos averiguar el modo de funcionamiento de la base de datos con la siguiente s entencia: SQL> select log_mode from v$database;
Para activar el modo ARCHIVELOG hay que añadir los siguientes parámetros al fichero de parámetros: log_archive_start = true (el archivado de los ficheros de redo log es automático en lugar de manual) log_archive_dest_1 = C:\oracle\oradata\orcl\arch (hasta 10 rutas diferentes) log_archive_format = arch_%t_%s.arc (nombre con el que se copian los ficheros)
Para cambiar el modo de funcionamiento la base de datos debe estar montada pero no abierta:
28
SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;
4.2.6.2 Otra ventaja del del uso uso de de ficheros ficheros de redo log
Además de todo lo visto anteriormente, existe otro motivo por el que Oracle decidió usar los ficheros de redo log. Los ficheros de redo log se escriben de forma secuencial, y la escritura secuencial es mucho más rápida que la escritura aleatoria. Cuando hacemos un commit , sólo tenemos que esperar a que finalice la escritura secuencial del redo log buffer al al fichero de redo log online activo para seguir trabajando, operación que es mucho más rápida que la escritura aleatoria de los bloques modificados en los ficheros de datos. Otro motivo, como ya hemos visto, es el de reducir el número de escrituras a disco, lo que logramos dejando los bloques de datos modificados en memoria tanto tiempo como sea posible, sin temor a la perdida de información por un fallo de la instancia. 4.2.6.3 Caso Caso práctico práctico select * from v$logfile; select * from v$log; alter database drop logfile group 1; alter database add logfile group 1 'D:\ORACLE\ORADATA\SIPLAMA\REDO1.LOG' size 100M; alter database drop logfile group 2; alter database add logfile group 2 'D:\ORACLE\ORADATA\SIPLAMA\REDO2.LOG' size 100M; alter system switch logfile; alter database drop logfile group 3;
29
alter database add logfile group 3 'D:\ORACLE\ORADATA\SIPLAMA\REDO3.LOG' size 100M; alter database add logfile group 4 'D:\ORACLE\ORADATA\SIPLAMA\REDO4.LOG' size 100M; alter database add logfile group 5 'D:\ORACLE\ORADATA\SIPLAMA\REDO5.LOG' size 100M;
4.2.7 Undo, bloqueos y concurrencia
Como ya hemos visto, cuando Oracle modifica un registro, también añade los datos de la operación realizada a los ficheros de redo log. Esta información nos permite volver a repetir la operación si fuese necesario. Por otra parte, el registro modificado también se marca con el identificador de la transacción que lo ha modificado. Este identificador actúa como un bloqueo de escritura sobre el registro. Si otro usuario intenta modificar este registro, Oracle comprobara que ya está bloqueado por otra transacción activa y no lo permitirá. Esta característica ha permitido a Oracle implementar otra funcionalidad fundamental: el multi-versioning, que permite que Oracle nunca bloquee la información en modo de lectura. Para entender este mecanismo hemos de ver primero que son los segmentos de rollback o o segmentos de undo. Además de la información de redo, Oracle también guarda la información necesaria para poder deshacer la operación en caso de que la transacción no finalice correctamente. Esta información se s e conoce como de undo y se guarda en los segmentos de rollback o o undo (rollback or y restaurará la información. undo segments). En caso de que la transacción falle, Oracle leerá la imagen anterior de los segmentos de rollback y Supongamos ahora que hemos abierto un cursor (hecho un select) a las 10:00:00 contra una tabla con 100.000 registros. Empezamos a recorrerlo y a las 10:00:05, cuando vamos por el registro número 20.000, un usuario modifica el registro número 25.000. Si seguimos recorriendo el cursor y al llegar al registro número 25.000 el usuario todavía no ha finalizado la transacción, está claro que deberíamos ver la información que había antes de la modificación. Pero, ¿qué pasa si ya ha hecho commit y y la transacción ha finalizado?
30
Pues que también debería ver el registro como estaba a las 10:00:00 cuando comenzó la consulta (cuando se abrió el cursor). Oracle consigue este comportamiento comprobando que la hora a la que se realizó la modificación (realmente el SCN: System Change Number ) es posterior a la del comienzo de la consulta, y en el caso de este registro, devolviendo la información que está almacenada en el segmento de undo. Mediante este mecanismo, Oracle ha conseguido que las actualizaciones que estén realizando unos usuarios no afecten a las operaciones de lectura que estén usando otros. Cuando se hace un commit o o un rollback , la transacción, que se almacena en los segmentos de rollback se se marca como activa o finalizada.
En teoría, no sería demasiado complejo escribir nuestras aplicaciones para que lean y escriban los registros que necesitan directamente desde ficheros. La principal diferencia entre usar este mecanismo y una base de datos como Oracle es el concepto de transacción. Una base de datos nos garantiza la integridad de los datos. Si una operación requiere que se realicen modificaciones en dos ficheros (tablas), y el sistema se cae después
33
de realizar la primera actualización, los datos podrían quedar inconsistentes. Esto no ocurre usando una base de datos. La base de datos nos garantiza la atomicidad de la operación mediante el concepto de transacción. La operación se realiza completa o no se realiza, realiza, pero nunca se queda a medias. Otra propiedad importante de las transacciones es el aislamiento (isolation). Los cambios que esté realizando una transacción no finalizada no deben ser visibles para el resto de transacciones en curso. Existen dos comandos para controlar las transacciones: commit y rollback . Commit finaliza una transacción haciendo que los cambios sean permanentes.
también finaliza una transacción, pero deshaciendo todos los cambios que se hubiesen h ubiesen realizado desde la transacción anterior. Rollback también En muchas bases de datos como Informix , Sybase y SQLServer cada cada operación es una transacción en sí misma. Si queremos que varias sentencias formen parte de una misma transacción es necesario comenzarla explícitamente. Oracle adoptó el criterio opuesto. Todas las operaciones se realizan en el ámbito de una transacción y no se hacen efectivas hasta que esta no se cierra. Hace algunos años (y aún hoy con algunas bases de datos), si un usuario estaba modificando una tabla, otro usuario u suario no podía leerla hasta que finalizaba la operación. Los registros que se estaban modificando o incluso la tabla entera se bloqueaban durante la transacción. Además, estos bloqueos consumían muchos recursos. Esto motivó que muchos programadores intentaran que las transacciones fuesen lo más cortas posibles. Así, por ejemplo, si querían actualizar todos los registros de una tabla con un millón de registros, podían escribir un procedimiento para modificarlos de 1000 en mil. Este procedimiento, además de ser más complejo que una simple actualización, podía dar lugar a una inconsistencia en los datos. En Oracle, el tamaño de una transacción no es un problema. No se usa más memoria ni se consumen más recursos (por bloqueos) en una transacción grande que en una pequeña. El tamaño de las transacciones debe ser el que corresponda a cada operación. Si queremos actualizar un campo de una tabla con un millón de registros, lo correcto sería finalizar la transacción cuando se hubiesen modificado todos los registros. Enlace a Ficheros Redo Log
34
Realmente, las operaciones commit son son casi inmediatas, independientemente del tamaño de la transacción. Cuando se realiza el commit la la información de redo y la de undo ya se ha generado, y los bloques de datos se han modificado en la cache. Si la transacción es grande, dicha información se ha ido volcando a disco. La parte más costosa de una transacción es la escritura del redo log buffer a a los ficheros de redo log online por el proceso LGWR , aunque esto no suele ser un problema ya que este proceso suele estar enviando esta información continuamente:
Cada 3 segundos Si se ha llenado un tercio del buffer. Cuando se realiza un commit
¿puede un select generar generar redo?
Al hacer un commit , se hace una limpieza rápida (total o parcial) de las transacciones asociadas a los registros modificados (realmente bloques) que todavía están en la cache. No así de los registros que el proceso DBWR ha ha volcado a los ficheros de datos y ya no están en memoria (esta situación ocurre sobre todo en transacciones grandes). A este proceso se le llama commit cleanout . Posteriormente, otra sesión puede leer alguno de estos bloques que tiene transacciones asociadas. Oracle comprueba si las transacciones ya han finalizado y limpia el bloque. Como esta operación no se hace como consecuencia de un u n commit , se le llama delayed block cleanout . Como se trata de una operación que modifica los bloques, se genera redo. Por este motivo, es posible que una sentencia select pueda pueda generar redo, especialmente después de una actualización grande. Realizar el block cleanout es es conveniente para evitar que Oracle tenga que comprobar cada vez si el registro pertenece a una transacción activa. (*) En teoría, no se podrá limpiar la transacción tr ansacción asociada a un registro si hay un cursor abierto antes del comienzo de la transacción, aunque la transacción ya haya finalizado. El caso del rollback es es el opuesto del commit . El tiempo necesario para realizar un rollback depende depende directamente del volumen de datos modificados. Oracle tiene que leer la información que había antes del cambio de los segmentos de undo para revertir la operación. Conclusión: Oracle está optimizado para ser muy rápido haciendo commit a a costa de que los rollback sean sean más lentos y costosos.
35
Repaso de las acciones realizadas en un commit : Antes de que se haga commit , ya se han realizado las siguientes acciones:
La información de undo se ha generado en la SGA. Los bloques se han modificado en la SGA. SG A. Se ha generado la información de redo de las dos acciones anteriores en la SGA. (ver Nota *) Dependiendo del tamaño de la transacción y el tiempo que dure, parte de la información de los tres puntos anteriores puede que se haya volcado a disco. Se han activado los bloqueos correspondientes.
Después del commit :
Se genera el System Change Number (SCN) de la transacción. El proceso LGWR escribe el resto del redo log buffer a a disco y anota también el SCN. La transacción se marca como finalizada (committed). Se liberan los bloqueos. Se hace un barrido rápido de los bloques modificados que aún están en memoria para borrar la información sobre transacciones finalizadas que puedan contener (commit cleanout).
La acción más costosa es la escritura del resto del redo log buffer a a disco, y esta acción siempre será pequeña porque Oracle ha ido realizando la escritura de esta información de forma regular durante la transacción. Aunque el proceso LGWR normalmente se ejecuta de forma asíncrona, esta última escritura se realiza de forma síncrona. Hasta que no finalice, Oracle no puede seguir trabajando. Nota *: como hemos visto, en transacciones grandes puede ser que parte de la información ya se haya volcado a disco, aunque la transacción no haya aún finalizado, por lo que la información de undo también tiene que estar protegida por los ficheros de redo , para, en caso de caída, después de recuperar el último estado, poder realizar el rollback que que no se pudo hacer por la caída. Con la siguiente consulta podemos ver las transacciones activas y el número de bloques de undo que contienen:
36
select a.sid, a.username, b.used_ublk from v$session a, v$transaction b where a.saddr = b.ses_addr;
Podemos averiguar el tiempo aproximado que le queda a un rollback para para finalizar, viendo cuando se reduce el valor del campo intervalo de, digamos, 60 segundos.
USED _UBLK en
un
4.2.9 Flashback
Mediante esta funcionalidad podemos consultar y recuperar datos pasados. Utiliza la información de undo que generan las transacciones. create table t as select object_id, object_name, created from all_objects where owner = 'DIMA' and object_type = 'TABLE' order by object_name; select * from t where object_name = 'REPUESTOS_ET'; update t set object_name = 'FOO' where object_name = 'REPUESTOS_ET'; select * from t where object_name = 'FOO'; select t.* from t versions between scn minvalue and maxvalue where object_id = 64508; select ora_rowscn, scn_to_timestamp(ora_rowscn), t.* from t where object_id = 64508;
37
select * from t as of timestamp to_timestamp('2014 09 22 17:30:30', 'YYYY MM DD HH24:MI:SS') where object_id = 64508; ‐
select * from t as of timestamp (systimestamp where object_id = 64508;
‐
‐
‐
‐
interval '20' minute)
Incluso podemos buscar los registros eliminados o modificados recientemente: delete from t where object_name = 'FOO'; commit; select * from t as of timestamp (systimestamp minus select * from t;
‐
interval '10' minute)
El tiempo que se almacena depende del tamaño del tablespace de undo y del parámetro undo_retention (en segundos). El parámetro undo_retention debería ser superior al tiempo de ejecución de la consulta más larga. select * from v$parameter where name like '%undo_%'; alter system set undo_retention = 1800;
Para averiguar el tiempo que se están guardando los segmentos de undo : select begin_time, end_time, undoblks, txncount, maxquerylen, tuned_undoretention from v$undostat order by end_time desc;
Cada registro contiene las estadísticas en periodos de 10 minutos:
38
undoblks: número total de bloques de undo usados.
txncount: número de transacciones ejecutado en el periodo
maxquerylen: duración en segundos de la consulta más larga.
tuned_undoretention: tiempo de retención de los datos de los bloques de undo
También podemos investigar más a fondo es estado de los segmentos de undo: select status, round(sum_bytes / 1024 / 1024) as MB, round((sum_bytes / undo_size) * 100) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c, v$tablespace b, v$datafile a where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts# );
Con esta consulta podemos ver la cantidad de undo en cada uno de los tres estados posibles: ACTIVE
Espacio usado por las transacciones activas y que es necesario para poder realizar un rollback . El parámetro undo_retention no se utiliza para este tipo de undo. Si no hubiese suficiente espacio en el tablaspace de undo para
39
guardar la información generada por la transacción, se abortaría la transacción y se produciría el error ‘ORA 30036 unable to extend segment in Undo tablespace‘. ‐
Información generada por transacciones finalizadas y antigüedad inferior al parámetro undo_retention.. Es necesario para la UNEXPIRED consistencia de las lecturas y las funciones de flashback. Si no está toda la información necesaria para realizar una lectura consistente, aparecería el error ‘ORA 01555 snapshot too old‘. ‐
EXPIRED
Información generada por transacciones finalizadas y antigüedad superior al parámetro undo_retention. No es necesario para la consistencia de las lecturas.
4.2.10 Tamaño de la base de datos select (a.data_size + b.temp_size + c.redo_size) / 1024 / 1024 / 1024 "GB", free.bytes / 1024 / 1024 / 1024 "Free GB" from (select sum(bytes) data_size from dba_data_files) a, (select nvl(sum(bytes),0) temp_size from dba_temp_files) b, (select sum(bytes) redo_size from v$log) c, (select sum(bytes) bytes from dba_free_space) free;
4.3 Estructuras de memoria Las estructuras básicas de memoria de Oracle son la SGA (System Global Area) y la PGA (Program Global Area).
40
La SGA está compuesta por un grupo de estructuras de memoria compartida compartida (componentes) que contienen datos e información de control de la instancia. La comparten todos los procesos, tanto los servidores dedicados (creados con cada sesión) como los procesos que funcionan en background . Los componentes principales de la SGA son la cache de datos, los redo log buffers y el shared pool. La PGA es una zona de memoria propia y exclusiva de cada proceso. Se conoce como PGA al conjunto de todas las PGAs. En este área se guarda información como los valores de las variables enlazadas (bind variables), el estado de ejecución de la sentencia, cursores abiertos y también se reserva espacio para realizar ordenaciones (sort area) y "joins" (hash área y bitmap merge area). Si el espacio que se puede reservar (suponiendo que estamos usando la gestión automática de la memoria de la PGA) es demasiado pequeño en relación con el tamaño de la operación, el tiempo de respuesta puede dispararse ya que las operaciones han de realizarse sobre segmentos temporales en disco. 4.3.1 La cache de datos (database buffer buffer cache) cache)
Es uno de los componentes principales de la SGA. Contiene una copia en memoria de los últimos bloques que se han leído y de los bloques modificados que aún no se han escrito en los ficheros de datos.
41
Esta lista de bloques es dinámica. Cuando un proceso necesita un registro (realmente un bloque), primero lo busca en la cache de datos. Si lo encuentra, el bloque se coloca al comienzo de la lista, de forma que los bloques de la cache menos usados se van moviendo hacía el final. En realidad, actualmente se utiliza un mecanismo del tipo touch count , por el que cada vez que se accede a un bloque, se incrementa un contador en dicho bloque, y cuando hay que hacer hueco para los nuevos bloques, Oracle intenta mantener los bloques más usados, eliminado los que se hayan usado menos veces. ¿Qué ocurre con con la lectura de tablas grandes (full scan)? scan)? ¿podrían ocupar todo el buffer buffer y hacer que perdamos los los segmentos más usados? No. Oracle utiliza dos mecanismos para que esto no ocurra. En primer lugar, nunca utiliza más del 25% del buffer cuando hace un full scan. scan. Si la tabla es mayor que el 25% del tamaño del buffer, los bloques más recientes van borrando a los más antiguos de la misma tabla. En segundo lugar, si el tamaño de la tabla es mayor que el 10% del tamaño del buffer, el contador de uso de los bloques no se incrementará con las siguientes lecturas de la misma tabla, por lo que esos bloques serán los primeros en salir de la cache. Este comportamiento podemos modificarlo a nivel de tabla mediante el keep y keep y recycle pool, pool, como veremos más adelante. Con la siguiente consulta podemos ver cómo de efectiva está siendo la cache de datos: select name, to_char(value / 1024 / 1024, '999,999,999') as M_BLOCKS from v$sysstat where name in ( 'physical reads cache', 'consistent gets from cache', 'db block gets from cache' );
42
Buffer hit ratio = physical reads / (consistent gets from cache + db block gets from cache)
Physical reads: los bloques de datos que Oracle lee de disco. Muy costosos. Buffer gets y logical reads: número de veces que se solicita un bloque del buffer cache. Son prácticamente sinónimos. Si el bloque no está en el
buffer cache, la lectura lógica implica una lectura física (es uno de los campos de la vista v$sql_monitor que que veremos más adelante). Consistent gets: son las lecturas de bloques consistentes con un instante determinado (o SCN).
Son las lecturas normales. La mayoría de las veces, los bloques que están en la cache son consistentes y no hay que hacer ninguna operación adicional para recuperar la información. Cuando se crea un cursor (select) para acceder a una serie de registros, los valores de los registros deben ser consistentes con el momento de d e la creación del cursor, aunque otras sesiones modifiquen algunos de esos bloques antes de que hayan sido leídos. Si el bloque en la cache no es consistente con el momento deseado, Oracle debe reconstruir el bloque con la información de los segmentos de rollback del del tablespace de UNDO. Si no tuviésemos suficientes segmentos de rollback para para reconstruir el bloque, nos aparecería el error ORA 1555 "snapshot too old". ‐
El número total de bloques leídos es la suma de Consistent Gets y DB Block gets. DB block gets: cuando Oracle lee la versión del bloque que haya en la cache (la actual), sin importarle si se ha modificado o no. No está muy claro,
ni es muy importante. Para los valores anteriores nos sale un buffer hit ratio de 0,6%. Este ratio nos da una idea del porcentaje de veces que Oracle ha encontrado el bloque que necesitaba en la cache y por lo tanto no ha tenido que leerlo de los ficheros de datos. Cuanto menor sea este valor, más efectiva está siendo la cache. Estos mismos indicadores podemos verlos a nivel de sesión: select nvl(n.username, 'ORACLE PROC') || ' (' || n.sid || ')' usernamex, n.program,
43
from where and and
and order
n.machine, t.name, to_char(s.value, '999,999,999,999') as value, round((sysdate ‐ n.logon_time) * 24) as horas, to_char(s.value / (sysdate ‐ n.logon_time) / 24, '999,999,999,999') as value_per_hour v$sesstat s, v$statname t, v$session n s.statistic# = t.statistic# n.sid = s.sid t.name in ( 'physical reads cache', 'consistent gets from cache', 'db block gets from cache' ) s.value > 0 by s.value desc;
o a nivel de pool: select name, physical_reads, db_block_gets, consistent_gets, 1 ‐ (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio" from v$buffer_pool_statistics;
44
Existe una vista que nos proporciona una idea de cómo podría variar este ratio en función del tamaño de la cache. select size_for_estimate, to_char(buffers_for_estimate, '999,999,999') as buffers_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads, '999,999,999,999') as estd_physical_reads from v$db_cache_advice where name = 'DEFAULT' and block_size = (select value from v$parameter where name = 'db_block_size') and advice_status = 'ON';
45
Este resultado nos indica que aumentando el tamaño de la cache de los 3505 Mb actuales a 6048 Mb, la lectura física de bloques se reduciría un 50%. Tener una cache muy grande no siempre hace que Oracle vaya más rápido. Cierto tipo de operaciones que deben recorrer todos los bloques en memoria podrian verse afectadas negativamente si el número de bloques en la cache fuese muy grande.
46
Esta consulta nos muestra los objetos con más bloques en la cache: select from where and group having order
o.owner, o.object_name, o.object_type, count(*) as nb dba_objects o, v$bh b o.data_object_id = b.objd not o.owner like 'SYS%' by o.owner, o.object_name, o.object_type count(*) >= 100 by 4 desc;
47
Vista para averiguar los objetos que más tiempo han permanecido enla cache (hay que ejecutarla como sys): with b1 as ( select obj, max(tch) as tch from x$bh group by obj order by 2 desc ), b2 as (
48
select obj, tch from b1 where tch >= 10 ) select from where and order
o.owner, o.object_name, o.object_type, b2.tch dba_objects o, b2 o.data_object_id = b2.obj not o.owner like 'SYS%' by 4 desc;
Consulta para ver los objetos a los que se está accediendo más frecuentemente: select from where group order
object_owner, object_name, count(*) v$sql_plan not object_owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'APEX_030200', 'EXFSYS', 'MDSYS') by object_owner, object_name by count (*) desc;
49
Oracle usa por defecto con un buffer de datos: DEFAULT, pero nos permite trabajar con dos más, KEEP y RECYCLE. Cuando se accede a un segmento grande (full scan o index range scan), scan) , los bloques pertenecientes a segmentos importantes que se usan más a menudo quizás no salgan del buffer, pero aquellos bloques pertenecientes a segmentos i mportantes que se hayan usado menos frecuentemente es posible que sí salgan. Para evitarlo Oracle nos permite definir dos buffers de datos adicionales:
50
KEEP pool: para mantener los bloques de los segmentos que sabemos que se van a usar muy frecuentemente y queremos que estén (casi) siempre
disponibles. s abemos que no se van a usar frecuentemente, eliminen a otros objetos de RECYCLE pool: para evitar que lecturas de segmentos grandes que sabemos acceso más frecuente. El funcionamiento de estos dos buffers es el mismo que el del buffer por defecto, por lo que también salen los bloques menos usados para que entren otros más recientes. Es muy fácil que disminuyamos el rendimiento de la cache en general por un mal uso de estos buffers adicionales. Hay que tener en cuenta que el espacion que destinamos a estos buffers adicionales lo estamos quitando del buffer por defecto. Para usarlos, primero hemos de haberlos creado a nivel de instancia, mediante los parámetros de inicialización buffer_pool_keep y buffer_pool_recycle (se especifica el número de bloques). Luego tenemos que asociar los segmentos a estos buffers. CREATE TABLE EMP (...) STORAGE (BUFFER_POOL KEEP) CACHE; ALTER TABLE
… STORAGE (BUFFER_POOL KEEP | RECYCLE | DEFAULT); ALTER INDEX REBUILD STORAGE (BUFFER_POOL KEEP | RECYCLE | DEFAULT);
4.3.2 Shared Pool
Es otro de los componentes fundamentales de la SGA. Tres de los componentes fundamentales es este área de memoria son:
Library cache: almacena las sentencias SQL y código PL-SQL compilado.
Data dictionary: cache: almacena información del diccionario de datos.
Server result cache: almacena resultados de consultas (a partir de la versión 11g)
51
El tamaño de estas áreas se ajusta de forma automática en función de las necesidades. La compilación de las sentencias SQL es una tarea muy costosa (query plan), plan), por lo que es importante que las que se ejecutan con mayor frecuencia estén compiladas y disponibles en la library cache. cache. No encontrar una sentencia en la cache es bastante más costoso que no encontrar un bloque en la cache de datos. El funcionamiento de esta cache es muy parecido al de la cache de datos. Una buena manera de deteriorar el rendimiento de una base de datos consiste en usar sentencias SQL sin variables (bind variables). variables). Oracle entiende que cada sentencia es diferente, por lo que elimina sentencias importantes que no se han usado recientemente para almacenar otras que con toda probabilidad no se van a volver a usar. Este problema empeora aún más aumentado el tamaño de la library cache. cache. Respecto a la server result cache, cache , Oracle asigna un 0,25% del parámetro MEMORY_TARGET a este área, que va aumentando hasta llegar al valor máximo permitido, que está definido en el parámetro de inicialización result_cache_max_size. select * from v$parameter where name like 'result_cache%';
El valor por defecto del parámetro result_cache_mode es MANUAL, que indica que Oracle no almacena resultados a no ser que qu e se especifique con el hint /*+ RESULT_CACHE */ en la propia consulta. El otro valor posible es FORCE, que se puede activar a nivel de sesión. El parámetro result_cache_max_result indica el máximo porcentaje de la cache que se puede almacenar en una consulta. Por defecto es un 5%. Este mecanismo puede ser interesante en consultas que extraigan un volumen pequeño de información de la lectura de un gran número de registros: agrupaciones, medias, etc., por ejemplo, si vamos a usar varias veces el número de movimientos por movimientos por año, suponiendo que la tabla movimientos tiene movimientos tiene muchos registros.
52
4.3.3 Administración de la memoria
Oracle recomienda que usemos la gestión automática de la memoria (Automatic Memory Management) . Con este modo, disponible a partir de Oracle 11g, el administrador tan sólo establece el tamaño total de la memoria para la instancia y Oracle se encarga de repartir el espacio dinámicamente entre la SGA y la PGA. Supongamos que queremos usar 5Gb de memoria con posibilidad de aumentar hasta 8Gb en el futuro: alter system set memory_max_target=8G scope=spfile; alter system set memory_target=5G scope=spfile; alter system set pga_aggregate_target=0 scope=spfile; alter system set sga_target=0 scope=spfile; shutdown immediate; startup;
Al establecer los parámetros pga_aggregate_target y sga_target a a 0, dejamos el control total a Oracle. pga_aggregate_target y Con estos parámetros, los tamaños de la SGA y la PGA (suma de las PGAs de todos los procesos) se ajustan automáticamente. Si estableciésemos valores para sga_target o o pga_aggregate_target distintos de 0, los valores se usarían como valores mínimos para las respectivas áreas de pga_aggregate_target distintos memoria. El parámetro memory_target se se puede modificar dinámicamente, siempre que no supere el valor definido en el parámetro memory_max_target : alter system set memory_target=8G;
Tamaño de las áreas de memoria dinámicas: select * from v$memory_dynamic_components;
Últimas operaciones de redimensionamiento de memoria: select * from v$memory_resize_ops;
53
Información sobre la SGA y la PGA: select * from v$sgastat; select * from v$pgastat;
4.4 Procesos select pname, background from v$process where not pname is null;
4.4.1 Process Monitor Process (PMON)
Es un “monitorizador” de procesos. Si una sesión falla o se mata, este proceso es el encargado de iniciar un rollback de de las transacciones en curso y liberar los recursos que estaba usando: memoria, bloqueos, etc. También monitoriza al resto de procesos, para reiniciarlos en caso de que alguno falle, si es posible, o en caso contrario, parar la instancia. 4.4.2 System monitor (SMON)
Realiza la recuperación de la instancia cuando se vuelve a iniciar después de un fallo. También limpia los segmentos temporales que no están en uso y desfragmenta el espacio libre en los ficheros. 4.4.3 Database Writer Process (DBWn)
Es el proceso encargado de escribir los bloques modificados (dirty blocks) de la cache a disco. Escribe los bloques que no se han usado recientemente (algoritmo LRU Least Recently Used ) intentando garantizar que siempre queden bloques libres en la cache para poder cargar nuevos bloques. Aunque un proceso (DBW0) es suficiente en la mayoría de los casos, en sistemas con varios procesadores que modifiquen muchos datos puede ser conveniente tener varios procesos DBWn funcionando simultáneamente.
54
Aunque Oracle propone el número de procesos de escritura durante la instalación, podemos modificar este valor con el parámetro db_writer_processes. Hay dos condiciones que hacen que los procesos DBWn escriban a disco:
Que un proceso no pueda encontrar bloques libres después de buscar en un porcentaje del buffer de datos. Para avanzar el checkpoint . Todos los bloques modificados del buffer de datos deben estar respaldados por los ficheros de redo (redo log). Para poder reutilizar un fichero de redo, los procesos DBWn deben escribir los bloqu es modificados respaldados por dicho fichero.
4.4.4 Log Writer Process (LGWR)
Es el encargado de escribir las entradas del buffer de redo a los ficheros de redo en disco. Escribe todas las entradas nuevas desde que se realizó la última escritura. El buffer de redo es circular, es decir, las entradas que q ue se van escribiendo en los ficheros de redo Oracle las va reescribiendo. El proceso entra en funcionamiento en las siguientes circunstancias:
Cuando se realiza un commit Cada 3 segundos Si se ha llenado un tercio del buffer. Cuando el proceso DBWn intenta escribir un buffer modificado a disco y comprueba que su entrada de redo asociada aún no se ha escrito a disco. En este caso avisa al proceso LGWR para que lo haga, quedándose a la espera de que este finalice para continuar.
Cada vez que se hace commit de de una transacción, Oracle asigna un número secuencial a la transacción (SCN: system change number) . Este número se almacena con las entradas de redo log generadas por la transacción. Con objeto de maximizar la eficiencia del proceso, si durante una escritura generada por un commit se se producen varios commit más, más, estos se ponen en cola y cuando la primera escritura finaliza, todas las demás se hacen a la vez.
55
4.4.5 Checkpoint Checkpoint Process Process (CKPT)
Cuando los procesos DBWn han escrito los bloques modificados a disco, se produce un checkpoint. Este proceso, que realiza el checkpoint , escribe el SCN en la cabecera de los ficheros de datos y en los ficheros de control.
Un checkpoint finalizado finalizado garantiza que todos los datos son correctos hasta el último SCN escrito. Si hay que realizar una recuperación, tan solo hay que volver a aplicar los cambios almacenados en los redo log posteriores al último SCN almacenado (roll forward). Es posible que después de haya que realizar un rollback para para deshacer las transacciones activas no finalizadas. roll forward haya
56
Oracle sabe si ha habido una caída y se necesita recuperación si alguno de los SCNs de los ficheros de datos nno o coincide con el almacenado en el fichero de control. 4.4.6 Archiver Processes (ARCn)
Es el proceso encargado de copiar los redo log online a los destinos especificados cuando la base está en modo ARCHIVELOG. Se lanza después de cada log switch. Puede haber hasta 10 de estos procesos y el proceso LGWR se encarga de lanzar más instancias si el rendimiento de las que hay activas no es suficiente. En el fichero alert.log queda constancia de la creación de nuevos procesos ARCn. Se puede establecer el número máximo mediante el parámetro log_archive_max_processes. 4.4.7 Ficheros de trazas y fichero alert.log
Los procesos descritos anteriormente dejan constancia de los problemas que puedan puedan presentarse en su fichero de trazas. El nombre de estos ficheros contiene el nombre del proceso que los escribe. También existe un fichero de trazas genérico llamado alert.log en el que Oracle escribe cronológicamente mensajes de advertencias y errores:
Errores internos, corrupción de bloques, deadlocks. Tareas administrativas realizadas como la creación o eliminación de tablespaces, paradas y arranques de la instancia, finalización de sesiones, etc. Errores en el refresco de vistas materializadas y otros.
Los ficheros de trazas están en el directorio: select * from v$parameter where name = 'background_dump_dest';
5 Tablas 5.1 Index Organized Tables (IOTs) ej. lookup tables
57
CREATE TABLE DIMA.L_UNIDADES ( CD_USUARIO VARCHAR2(8 BYTE) NOT NULL, CD_UNIDAD VARCHAR2(8 BYTE) NOT NULL, CONSTRAINT L_UNIDADES_PK PRIMARY KEY (CD_USUARIO, CD_UNIDAD) ) ORGANIZATION INDEX TABLESPACE DIMA;
5.2 Temporary Tables Se usan para guardar resultados temporales. Los datos que se insertan en las tablas temporales t emporales solo son visibles para la sesión que los l os inserta, aunque se haga commit . Generan mucho menos redo que las tablas normales. create global temporary table temp_table ( ) on commit preserve rows;
Aunque la creemos con la cláusula on commit preserve rows, al cerrar la sesión los datos desaparecerán. create global temporary table temp_table ( ) on commit delete rows;
¿se genera redo al trabajar con tablas tablas temporales? SQL> create global temporary table gtt on commit preserve rows as select * from all_objects where 1=0;
58
Tabla creada. SQL> set autotrace on statistics SQL> insert into gtt select * from all_objects; 66107 filas creadas.
recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
SQL> insert /*+ append */ into gtt select * from all_objects; 66107 filas creadas.
recursive calls db block gets consistent gets physical reads
59
68 839 808 3 1042 0 66107
redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
SQL> set autotrace off
Al usar la cláusula /*+ append */ estamos usando lo que se conoce como direct-path insert . En este modo, los datos se escriben directamente en los ficheros de datos sin pasar por el buffer cache y por lo tanto sin generar redo. Tampoco se reutilizan los espacios libres en los bloques ni se tienen en cuenta las restricciones de integridad. Para poder usar este mecanismo en tablas no temporales, hay que crearlas con el atributo NOLOGGING.
5.3 Lobs Los campos LOBs (Large Objects) pueden tener un tamaño de hasta 4Gb. (similares a los campos MEMO de Access) Además del segmento que se crea al crear una tabla, Oracle crea dos segmentos adicionales, LOB SEGMENT y LOB INDEX, por cada campo LOB que contenga la tabla. El motivo es que si los objetos son muy grandes es más eficiente guardarlos en segmentos s egmentos independientes, en lugar de meterlos en la fila dentro del bloque. Además, estos objetos grandes se almacenan en trozos (chunks). El tamaño de estos chunks es configurable. De ahí la necesidad del segmento LOB INDEX. Como veremos ahora, estos segmentos no tienen por qué estar en el mismo tablespace que la tabla, lo cual puede simplificar las tareas administrativas (backup, gestión del espacio, etc.) Otro motivo fundamental es que los objetos LOB, LO B, por defecto, no se cachean en el buffer cache.
Hasta que no insertamos un registro no se crean los segmentos: insert into t1 (c1, c2) values (1, null);
La definición completa de la tabla podemos verla con la sentencia: select dbms_metadata.get_ddl('TABLE', 'T1') from dual;
Se puede modificar la propiedad de cache: ALTER TABLE tabname MODIFY LOB (lobname) (CACHE);
61
ALTER TABLE tabname MODIFY LOB (lobname) (CACHE READS); ALTER TABLE tabname MODIFY LOB (lobname) (NOCACHE); defecto ‐‐
Por defecto, si los objetos no ocupan más de 4Kb, Oracle los guarda junto con la fila en el mismo bloque, lo que hace que el funcionamiento sea más eficiente. Este comportamiento se puede modificar cambiando la cláusula ENABLE STORAGE IN ROW por DISABLE STORAGE IN ROW en el script de creación de la tabla, o posteriormente con la siguiente sentencia: ALTER TABLE test MOVE TABLESPACE tbs1 LOB (lob1, lob2) STORE AS ( TABLESPACE tbs2 DISABLE STORAGE IN ROW);
6 Índices Los índices contienen además de los campos que los definen, un campo adicional llamado ROWID. Este campo contiene la información del fichero de datos y el bloque dentro del fichero de datos que contiene el registro. Con esta información Oracle puede acceder rápidamente a un registro. Es la forma más rápida de acceder a un registro (aunque no necesariamente a varios). Se deberían diseñar al mismo tiempo que las tablas, no luego sobre la marcha. En general, no es buena idea añadir índices a una tabla que ya está en producción. Mi apreciación es que en muchos casos se utilizan demasiados índices y además, suelen estar mal diseñados. Ejemplo de lo que no se debería hacer (15 índices en una misma tabla): CREATE TABLE DIMA.COSES1 ( ID_COSES1 NUMBER,
62
ANO CO_TIP NOC NU_SERIE_ET CO_UNIDAD_ASIGNADA DS_UCO_ASIGNADA DS_BRIGADA DS_CABECERA DS_VOCES_COLECTIVAS DS_SERV DS_NOMBRE_FAMILIA_CUF DS_FAM_ACPLA DS_CONTROL_VIDA1 DS_CONTROL_VIDA2 DS_CONTROL_VIDA3 CA_CONTROL_VIDA1 CA_CONTROL_VIDA2 CA_CONTROL_VIDA3 COS CM COD COI YOLD COP COC ESCALON A02P A02C ET );
INDEX DIMA.COSES1_DS_BRIGADA_INDX ON DIMA.COSES1 (DS_BRIGADA); INDEX DIMA.COSES1_DS_CABECERA_INDX ON DIMA.COSES1 (DS_CABECERA); INDEX DIMA.COSES1_DS_DS_CNTR_VIDA1_INDX ON DIMA.COSES1 (DS_CONTROL_VIDA1); INDEX DIMA.COSES1_DS_DS_CNTR_VIDA2_INDX ON DIMA.COSES1 (DS_CONTROL_VIDA2); INDEX DIMA.COSES1_DS_DS_CNTR_VIDA3_INDX ON DIMA.COSES1 (DS_CONTROL_VIDA3); INDEX DIMA.COSES1_DS_FAM_ACPLA_INDX ON DIMA.COSES1 (DS_FAM_ACPLA); INDEX DIMA.COSES1_DS_NOMBRE_FAM_CUF_INDX ON DIMA.COSES1 (DS_NOMBRE_FAMILIA_CUF); INDEX DIMA.COSES1_DS_SERV_INDX ON DIMA.COSES1 (DS_SERV); INDEX DIMA.COSES1_DS_UCO_ASIGNADA_INDX ON DIMA.COSES1 (DS_UCO_ASIGNADA); INDEX DIMA.COSES1_DS_VOC_COLECTIV_INDX ON DIMA.COSES1 (DS_VOCES_COLECTIVAS); INDEX DIMA.COSES1_ESC_INDX1 ON DIMA.COSES1 (ESCALON); UNIQUE INDEX DIMA.COSES1_PK ON DIMA.COSES1 (ID_COSES1); UNIQUE INDEX DIMA.COSES1_U01 ON DIMA.COSES1 (ANO, CO_TIP, NOC, NU_SERIE_ET, ESCALON); INDEX DIMA.COSES1_YOLD_INDX ON DIMA.COSES1 (YOLD);
Consultas útil para la consulta de índices: select from where group order
table_owner = 'DIMA' by table_name, index_name, column_position;
Desventajas de definir muchos índices y/o índices con muchas columnas:
Las actualizaciones de las tablas son más costosas. Por cada registro que se inserte, modifique o borre, hay que actualizar todos los índices de la tabla. La elección del plan de ejecución será más compleja, aumentando la probabilidad de que el optimizador elija uno que no sea óptimo.
64
Si un índice contiene muchos campos, se necesitarán más lecturas para leerlo y ocupará más espacio en memoria, por lo que penalizará a todos los usuarios.
Cuando se cargan o se eliminan grandes cantidades de registros de una tabla puede ser bastante más eficiente deshabilitar algunos índices y recrearlos al finalizar la operación: ALTER INDEX UNUSABLE; ALTER INDEX REBUILD;
Tipos de índices:
B*Tree Index Organized Tables (IOT) Birmap Bitmap Join Function-based indexes
65
6.1 B*Tree
Los nodos finales (leaf nodes) que del registro están enlazados por una doble lista enlazada que permite el barrido nodes) que contienen la clave y el RowId del de los registros secuencialmente (index rage scan). scan). Una de las propiedades de este tipo de árboles es que todos los nodos finales están al mismo nivel, que se conoce como la profundidad (height) del índice. La mayoría de los índices tienen una profundidad de 2 o 3, aunque tengan millones de registros, por lo que solo se necesitan dos o tres lecturas para llegar al registro deseado. Otra propiedad es que los árboles se crean perfectamente balanceados, aunque tras múltiples inserciones e eliminaciones este balanceo se puede ir deteriorando.
66
valor se aproxima al número de bloques de la tabla, significa que los datos en la tabla están muy ordenados respecto al índice. Sin embargo, si el valor es cercano al número de registros, los datos están muy desordenados y su uso sería poco eficiente. Es uno de los factores que el optimizador tiene en cuenta a la hora de decidir el plan de ejecución para una consulta. Hay que tener en cuenta que solo un índice de data tabla puede tener un buen clustering_factor . Hay veces que Oracle puede darnos el resultado de una consulta accediendo únicamente al índice, como por ejemplo, si queremos conocer el número de registros de una tabla. Existe un modo rápido de lectura de índices llamado fast full scan, scan , con el que se leen los bloques del índice sin un orden en particular. El resultado de esta lectura no está ordenado.
6.2 Índices Bitmap No están disponibles en la versión Standard, solo en la Enterprise.
Son adecuados para columnas en las que hay pocos valores diferentes comparados con el número total de registros en la tabla. Ejemplo del tipo de consulta en la que son muy eficientes. select count(*) from T
68
where genero = 'H' and provincia in (1, 10, 30) and grupo_de_edad = '41 y superior';
Esta consulta sería muy poco eficiente si se utilizan índices B*Tree, B*Tree, sobre todo si no conocemos a priori cuáles son las condiciones que se aplicarán. Están orientados a sistemas DSS (Decision Support Systems) y Data Warehouses , no sistemas OLTP ( Online Online Transaction Processing) . Cuando se modifican los datos, Oracle necesita bloquear gran parte del índice para poder recrearlo, lo que podría degradar el funcionamiento de sistemas OLTP, en los que varios usuarios pueden pu eden estar modificando datos simultáneamente.
6.3 Índices Bitmap Join Permiten realizar una desnormalización, pero no en los datos sino en el índice. Veamos un ejemplo: La tabla DIMA.REPUESTOS_ET tiene el campo ID_TIPOS_DE_ARTICULOS. Este campo es una referencia a la tabla DIMA.TIPOS_DE_ARTICULOS:
Si queremos hacer una consulta por el código del tipo de artículo, no nos queda más remedio que hacer un un join join:: select count(*)
69
from REPUESTOS_ET R, TIPOS_DE_ARTICULOS T where R.ID_TIPOS_DE_ARTICULOS = T.ID_TIPOS_DE_ARTICULOS and T.CO_TIPOS_DE_ARTICULOS = 'G'; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
2 3 4
‐ ‐ ‐
access("R"."ID_TIPOS_DE_ARTICULOS"="T"."ID_TIPOS_DE_ARTI access("R"."ID_TIPOS_DE_ARTICULOS"="T "."ID_TIPOS_DE_ARTICULOS") CULOS") filter("T"."CO_TIPOS_DE_ARTICULOS"='G') filter("R"."ID_TIPOS_DE_ARTICULOS" IS NOT NULL)
Oracle nos permite crear el siguiente índice, con el que asociamos al índice de una tabla un campo de otra tabla: create on from where
bitmap index REPUESTOS_ET_TIPO_ART_IDX REPUESTOS_ET (T.CO_TIPOS_DE_ARTICULOS) REPUESTOS_ET R, TIPOS_DE_ARTICULOS T R.ID_TIPOS_DE_ARTICULOS = T.ID_TIPOS_DE_ARTICULOS;
Con este índice, podemos conseguir el resultado de la consulta accediendo tan solo al índice. La línea 2 es un “error” del optimizador, que añade una condición que en este caso es innecesaria y que obliga a Oracle a leer los registros que cumplen la condición principal.
| SELECT STATEMENT | | SORT AGGREGATE | | TABLE ACCESS BY INDEX ROWID | REPUESTOS_ET | BITMAP CONVERSION TO ROWIDS| | BITMAP INDEX SINGLE VALUE | REPUESTOS_ET_TIPO_ART_IDX
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
1
‐
filter(UPPER("DS_NOMBRE_CORTO")='GACA I/63')
drop index UNIDAD_NOMBRE_CORTO_IDX; create index UNIDAD_NOMBRE_CORTO_IDX on UNIDAD(Upper(DS_NOMBRE_CORTO)); ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
72
2
‐
access(UPPER("DS_NOMBRE_CORTO")='GACA I/63')
Importante: para poder usar funciones en este tipo de índices, es necesario marcarlas como DETERMINISTIC, para indicar a Oracle que para un
mismo parámetro la función siempre devolverá el mismo resultado. function mi_funcion(parametro in varchar2) return varchar2 deterministic as begin ... end;
Otro caso útil: supongamos que tenemos una tabla PEDIDOS con un campo ENVIADO que puede tomar los valores ‘S’ y ‘N’. Cuando tiene el valor ‘S’ debe rellenarse el campo NUM_ENVIO. En caso contrario permanece a nulo. En principio no podemos definir un índice único por los campos ENVIADO y NUM_ENVIO porque habría muchos registros con los valores (‘N’, NULL). Puesto que en los índices B*Tree no se almacenan los registros en los que todos los campos del índice sean nulos, podemos definirlo del siguiente modo: create unique index PEDIDOS_IDX on PEDIDOS (case when ENVIADO = 'N' then NULL end, NUM_ENVIO);
Nota: Oracle nunca podrá usar un índice en el que todos sus campos puedan ser nulos para realizar un count(*). La vista dba_ind_expressions nos permite consultar los índices basados en funciones.
7 Vistas y vistas materializadas Breve descripción. Cuando se utiliza una vista, Oracle sustituye la vista por su código.
Comprobamos que no podemos iniciar una sesión. GRANT CREATE SESSION TO CURSO;
Creamos la tabla para rellenar a partir de un fichero externo: CREATE TABLE CURSO.LDR_TEST ( CAMPO1 VARCHAR2(20) NOT NULL, CAMPO2 VARCHAR2(12), CAMPO3 INTEGER NOT NULL, CAMPO4 NUMBER, CAMPO5 DATE, CAMPO6 DATE, CAMPO7 VARCHAR2(2000) );
El usuario no tiene permisos para crear una tabla. GRANT CREATE TABLE TO CURSO;
El usuario todavía no puede insertar registros en la tabla: ORA 01950: no existen privilegios en tablespace 'USERS' ‐
Necesitamos un fichero de control de carga: test.ctl
LOAD DATA TRUNCATE INTO TABLE LDR_TEST WHEN CAMPO1 <> 'Prueba 4' FIELDS TERMINATED BY ';' TRAILING NULLCOLS ( CAMPO1, NO_IMPORTA FILLER, CAMPO2 "upper(:CAMPO2)", CAMPO3 INTEGER EXTERNAL, CAMPO4 DECIMAL EXTERNAL NULLIF CAMPO4=BLANKS, CAMPO5 DATE "dd/mm/yyyy", CAMPO6 "case when length(:CAMPO6) <= 10 then to_date(:CAMPO6,'dd/mm/yyyy') else to_date(:CAMPO6,'dd/mm/yyyy hh24:mi:ss') end", CAMPO7 optionally enclosed by '"' )
75
Para evitar tener que especificar en la línea de comandos todos los parámetros que necesita el comando SqlLdr , creamos un fichero de parámetros: test.par
Ya podemos ejecutar el comando: C:\> sqlldr parfile=test.par
El fichero test.dis contendrá los registros que no cumplen el formato especificado en el fichero de control. El número de registros rechazados se puede limitar con el parámetro DISCARDMAX. Por defecto tiene el valor ALL pero se podría poner a 1. El fichero test.bad contendrá contendrá los registros que no se han podido insertar porque se ha producido algún error. En el fichero test.log contendrá las estadísticas de la carga y el motivo de los errores si se han producido. En el fichero de control podríamos haber especificado APPEND en lugar de TRUNCATE.
9 Tablas externas Desde un usuario autorizado creamos un directorio de Oracle en el que estará el fichero que queremos leer como si fuese una tabla: create or replace directory EXT_TBL as 'C:\Users\Alex\Temp\SqlLoader';
76
Utilizaremos el mismo fichero test.csv usado en el capítulo sobre Sql Loader . El usuario CURSO no tiene acceso al directorio: select owner, directory_name, directory_path from all_directories;
Habilitamos al acceso del usuario CURSO al directorio EXT_TBL en modo lectura, y escritura porque Oracle generará ficheros de log. grant read, write on directory EXT_TBL to CURSO; CREATE TABLE TEST_EXT ( CAMPO1 VARCHAR2(20 BYTE), NO_VALE VARCHAR(10 BYTE), CAMPO2 VARCHAR2(12 BYTE), CAMPO3 VARCHAR2(16 BYTE), CAMPO4 VARCHAR2(16 BYTE), CAMPO5 VARCHAR2(10 BYTE), CAMPO6 VARCHAR2(19 BYTE), CAMPO7 VARCHAR2(2000 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY EXT_TBL ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE LOGFILE 'test.log' BADFILE 'test.bad' SKIP 1 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LDRTRIM
77
MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ) LOCATION ('test.csv') ) REJECT LIMIT UNLIMITED;
Ya podemos consultar la tabla: select * from test_ext;
Añadimos las conversiones necesarias para poder insertar los datos en la tabla LDR_TEST: select CAMPO1, upper(CAMPO3), to_number(CAMPO3), to_number(CAMPO4), to_date(CAMPO5, 'dd/mm/yyyy'), to_date(CAMPO6, 'dd/mm/yyyy hh24:mi:ss'), CAMPO7 from TEST_EXT;
Se produce un error porque no se puede convertir el número del último registro. Si intentamos hacer la inserción en la tabla se produce el mismo error: insert into LDR_TEST (CAMPO1, CAMPO2, CAMPO3, CAMPO4, CAMPO5, CAMPO6, CAMPO7) select CAMPO1, upper(CAMPO3), to_number(CAMPO3), to_number(CAMPO4), to_date(CAMPO5, 'dd/mm/yyyy'),
78
to_date(CAMPO6, 'dd/mm/yyyy hh24:mi:ss'), CAMPO7 from TEST_EXT;
Podemos solucionarlo asociando a la tabla en la que vamos a insertar los registros u na tabla de errores: exec dbms_errlog.create_error_log('LDR_TEST'); insert into LDR_TEST (CAMPO1, CAMPO2, CAMPO3, CAMPO4, CAMPO5, CAMPO6, CAMPO7) select CAMPO1, upper(CAMPO3), to_number(CAMPO3), to_number(CAMPO4), to_date(CAMPO5, 'dd/mm/yyyy'), to_date(CAMPO6, 'dd/mm/yyyy hh24:mi:ss'), CAMPO7 from TEST_EXT log errors reject limit unlimited;
Los errores quedan registrados en la tabla ERR$_LDR_TEST.
10 SQL Tuning 10.1 Introducción Para ejecutar una consulta, Oracle debe realizar una serie de pasos:
Parser: análisis sintáctico y semántico de la sentencia. Se utiliza el diccionario de datos para chequear la existencia de tablas y campos, el tipo de los campos, los permisos, etc. Reescritura y normalización de la sentencia (reformulación algebraica) (ver PLAN_HASH_VALUE de v$sqlarea)
79
Decidir el algoritmo o plan óptimo de ejecución para ejecutar la sentencia de la forma más eficiente: cómo acceder a los datos, cómo unir y filtrar los datos, en qué orden, etc.
Realmente, Oracle crea varios planes de ejecución y asigna a cada uno un coste. Ejecutará el que tenga el menor coste (Cost-Based Optimizer) El coste de cada plan de ejecución, en realidad de cada paso de cada plan de ejecución, se calcula en función de la cantidad de recursos necesarios: operaciones de lectura-escritura, cantidad de memoria necesaria y uso de CPU. Veamos un ejemplo simplificado: select from where and and
* EMPLEADOS E, DEPARTAMENTOS E E.DEPT_NO = D.DEPT_NO E.EMPLEO = 'SUPERVISOR' D.CIUDAD = 'MADRID';
Supongamos que la tabla EMPLEADOS tiene 2.000 filas, la tabla DEPARTAMENTOS 40 filas, existe un supervisor por departamento y hay 10 departamentos en Madrid. Supongamos también que solo podemos leer y escribir un registro cada vez (en la realidad la lectura y escritura se hace a nivel de bloque), que Oracle escribe cada paso intermedio a disco (no siempre es así en la realidad), y qu e las tablas no tienen índices definidos. Para este ejemplo tampoco vamos a tener en cuenta el coste asociado al volumen de memoria necesario ni al uso de CPU. Con estas premisas vamos a analizar el coste de tres posibles planes de ejecución: I.
Producto cartesiano 1. Leemos todos los registros: 2.000 + 40 = 2.040 lecturas 2. Generamos el producto cartesiano: 2.000 * 40 = 80.000 escrituras 3. Leemos el producto cartesiano y aplicamos las condiciones: 2.000 * 40 = 80.000 lecturas
Leemos todos los registros: 2.000 + 40 = 2.040 lecturas Unir las tablas aplicando la condición DEP_NO: 2.000 escrituras Leer el resultado del “join” anterior anterior filtrando por las condiciones: 2.000 lecturas Total: 2.040 + 2.000 + 2.000 = 6.040 operaciones de lectura/escritura.
Unión de las tablas previamente filtradas 1. 2. 3. 4. 5. 6. 7. 8.
Leer todos los registros de la tabla EMPLEADOS: 2.000 lecturas Escribir los empleados que son supervisores: 40 escrituras Leer todos los registros de la tabla DEPARTAMENTOS: 40 lecturas Escribir los departamentos de Madrid: 10 escrituras Leer los dos resultados intermedios anteriores: 40 + 10 = 50 lecturas Unimos los dos resultados anteriores por el campo DEP_NO: 10 escrituras Leer el resultado: 10 lecturas Total: 2.000 + 40 + 40 + 10 + 50 + 10 + 10 = 2.160 operaciones de lectura/escritura.
Oracle suele seguir una serie de reglas básicas para optimizar la ejecución de las consultas:
Eliminar todas las filas posibles en las primeras operaciones, para disminuir el volumen de filas con las que va a trabajar más adelante. Trabajar solo con las columnas que necesita. Realizar primero las uniones (joins) que generen los resultados con menor número de registros. Guardar el resultado de expresiones comunes para calcularlas una sola vez.
81
Para que el optimizador pueda elegir el plan más óptimo necesita hacer un uso intensiv o de las estadísticas (ver apartado de estadísticas). Tres de los factores que más influyen en los recursos (tiempo) que consume una consulta son:
Modo de optimización Modo de acceso a los datos (access path) Uniones (joins)
El parámetro optimizer_mode puede influir considerablemente en la elección del plan de ejecución. Puede tomar los valores ALL_ROWS, FIRST_ROWS_10, FIRST_ROWS_100 o FIRST_ROWS_1000. El valor por defecto es ALL_ROWS. select value from v$parameter where name = 'optimizer_mode';
Una de las decisiones más importantes del optimizador es elegir adecuadamente el modo de acceso a los datos (access path). Los más habituales son la lectura completa de la tabla (full table scan) y la lectura completa de un índice (index full scan). También es habitual el fast full index scan . Hemos que tener en cuenta que Oracle nunca lee un solo registro. Las lecturas se realizan a nivel de bloque. El tamaño de un bloque suele ser el mismo a nivel de la base de datos: select value from v$parameter where name = 'db_block_size'; select num_rows, avg_row_len, blocks from user_tables where table_name = 'REPUESTOS_ET';
El optimizador suele leer las tablas pequeñas usando un full_table_scan. Habitualmente es lo más rápido. Los full_table_scan de tablas grandes se hacen leyendo un conjunto de bloques de una vez. Este número de bloques se configura con el parámetro db_file_multiblock_read_count. select * from v$parameter where name like '%multiblock%';
82
10.2 Tipos de uniones (joins)
Cuando necesitamos acceder a campos que proceden de múltiples tablas, Oracle las une según los campos especificados. El modo de unirlas se selecciona en función de las estadísticas y de los índices que haya definidos en las tablas. En la mayoría de los casos, las uniones se hacen por la primary key . La primary key de de una tabla es un campo o combinación de campos que identifican unívocamente un registro. Los campos de la primary key no no pueden ser nulos. La primary key no no es obligatoria, aunque una tabla solo puede tener una primary key . Una primary key es una constraint (restricción) (restricción) que se impone con la ayuda de un índice. Si no se especifica el índice, Oracle lo crea automáticamente. CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n) ); alter table REPUESTOS_ET add (constraint REPUESTOS_ET_PK primary key (ID_ART) using index REPUESTOS_ET_PK);
Las uniones más comunes son: 10.2.1 Nested‐loop join
Supongamos que nos dan una lista de 20 nombres y un listín telefónico, y nos piden averiguar el número de teléfono de cada uno de los nombres. Para este tipo de operación utilizaríamos este tipo de unión. Cogeríamos el primer nombre de la li sta y lo buscaríamos en el listín, luego el segundo, y así sucesivamente hasta terminar. Este tipo de unión interesa cuando la tabla externa (outer o driving table) es pequeña y la tabla interna (inner table) tiene un índice único o clave primaria para acceder rápidamente al registro deseado, o al menos tiene un índice no único con un alto grado de selectividad.
83
Una de las ventajas principales de este tipo de uniones es que se pueden recuperar las primeras filas del resultado sin tener que esperar a completar el bucle externo completamente. Vamos a ver el explain plan de la siguiente consulta con Toad . select US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO from ( select CD_USUARIO, CO_UNIDAD from USUARIOS where CD_USUARIO like 'MA%' ) US, UNIDAD UN where UN.CO_UNIDAD = US.CO_UNIDAD;
Vamos a verlo ahora desde Sql-Plus: D:\>cd D:\Usuarios\aruiz\Documents\Temp D:\>sqlplus DIMA/xxx@ORCL SQL> set linesize 512 SQL> spool result.txt SQL> select * from FAMILIA_APOYO_ET where rownum < 5; SQL> spool off SQL> set autotrace on SQL> select * from FAMILIA_APOYO_ET where rownum < 5; SQL> set autotrace traceonly SQL> select * from FAMILIA_APOYO_ET where rownum < 5; SQL> spool result.txt append SQL> spool off SQL> exit
Y ahora con una herramienta tipo Sql Developer : explain plan for
84
select US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO from ( select CD_USUARIO, CO_UNIDAD from USUARIOS where CD_USUARIO like 'MA%' ) US, UNIDAD UN where UN.CO_UNIDAD = US.CO_UNIDAD; select * from table(dbms_xplan.display); ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
3 4
‐
‐
filter("CO_UNIDAD" IS NOT NULL AND "CD_USUARIO" LIKE 'MA%') access("UN"."CO_UNIDAD"="CO_UNIDAD")
Oracle ha tomado la tabla USUARIOS como tabla externa porque sabe (por las estadísticas) que ti ene menos registros (supone que 3).
85
En teoría, Oracle debería haber accedido a los registros que cumplen la condición where CD_USUARIO like 'MA%' mediante la primary key que que está definida sobre el campo CD_USUARIO (campo único, que identifica unívocamente a cada registro y que no puede ser nulo), sin embargo vemos que ha preferido hacer un full scan de la tabla USUARIOS. ¿por qué? Existen dos motivos: 1. La tabla es pequeña, y seguramente es menos costoso leer todos los registros de forma secuencial (posiblemente una sola lectura) que hacer tres lecturas individuales, una para cada uno de los registros -> 2. La primary key no no contiene el campo CO_UNIDAD que necesitamos para obtener el campo DS_NOMBRE_CORTO de la tabla UNIDAD, por lo que sería necesario hacer una lectura (por ROWID) para cada uno de los códigos de usuarios que cumplen la condición. De hecho, si cambiamos el orden de las tablas en la consulta, veremos que el plan de ejecución no cambia: select US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO from UNIDAD UN, ( select CD_USUARIO, CO_UNIDAD from USUARIOS where CD_USUARIO like 'MA%' ) US where UN.CO_UNIDAD = US.CO_UNIDAD;
El primer nested loop (línea 2 de explain plan) obtiene para cada usuario el RowId de su unidad asociada, mediante la primary key de de la tabla UNIDAD. El nested loop externo (línea 1 del explain plan) debe recorrer cada uno de los registros del join anterior para acceder mediante los RowIds de la tabla UNIDAD al registro completo y así poder leer el campo DS_NOMBRE_CORTO. Podemos comprobar cómo si eliminamos la condición para los usuarios, el explain plan cambia totalmente. select US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO
86
from UNIDAD UN, USUARIOS US where UN.CO_UNIDAD = US.CO_UNIDAD; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
¿Por qué supone el optimizador que la tabla USUARIOS tendrá del orden de 243 registros si la tabla tiene realmente 15.625 registros? select count(*) from USUARIOS;
Porque la unión solo se puede hacer para los usuarios que tienen una unidad asociada, es decir, con el campo CO_UNIDAD no nulo y Oracle tiene este factor en cuenta. select count(*) from USUARIOS where not CO_UNIDAD is NULL;
10.2.2 Sort ‐Merge joins
Se ordenan los registros de las dos tablas por los campos de unión y se unen (un puntero en cada lista, barridos simultáneos). Este tipo de unión es muy eficiente, pero las ordenaciones pueden consumir muchos recursos (I/O y espacio en disco). El rendimiento puede mejorarse si se accede a los datos, de al menos una de las tablas, por un índice definido sobre los campos de unión, pero hemos de tener en cuenta que acceder a muchos registros a través de un índice también puede ser muy costoso. Solo se pueden usar para condiciones de igualdad (equi-joins), no mayor que, menor que, distinto, etc.
87
Uno de los problemas principales que surgen al usar este est e tipo de uniones es el espacio necesario para realizar la ordenación. select /*+ USE_MERGE(US UN) */ US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO from UNIDAD UN, ( select CD_USUARIO, CO_UNIDAD from USUARIOS where CD_USUARIO like 'MA%' ) US where UN.CO_UNIDAD = US.CO_UNIDAD;
Como Oracle prefiere usar para esta consulta un nested loop join, forzamos un merge join mediante un hint : ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
3 4
‐
‐
filter("CO_UNIDAD" IS NOT NULL AND "CD_USUARIO" LIKE 'MA%') access("UN"."CO_UNIDAD"="CO_UNIDAD") filter("UN"."CO_UNIDAD"="CO_UNIDAD")
88
10.2.3 Hash join
Se crea una tabla de hash en memoria, a partir de la clave de unión de la menor de las tablas (o resultado intermedio). Luego se hace un barrido de la otra tabla (o resultado intermedio) y se va devolviendo el resultado de la unión. Es muy efectivo si la tabla de la que se hace el hash cabe en memoria. Este tipo de join es bastante efectivo cuando no existen índices que favorezcan el uso de una unión del tipo nested loop. También tiene la ventaja frente a los sort-merge de que solo hay que ordenar un conjunto de datos. Otra ventaja frente a la unión nested loop es que la comparación de cada uno de los registros contra una tabla hash en memoria es más rápida que la búsqueda de un registro en un índice de estructura b-tree. select US.CD_USUARIO, US.CO_UNIDAD, UN.DS_NOMBRE_CORTO from USUARIOS US, UNIDAD UN where UN.CO_UNIDAD = US.CO_UNIDAD; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
10.3 Estadísticas Encontrar el plan de ejecución óptimo puede ser complicado. En una consulta con cuatro tablas, estas se pueden ordenar de 24 formas diferentes. El método con el que se acceda a cada una de ellas dependerá de los índices que haya definidos, y además, hay que elegir el método óptimo de unión entre los diferentes conjuntos de datos. El optimizador siempre intentará unir las tablas de forma que se s e vaya eliminando el mayor número de registros posible. El optimizador depende enormemente de que las estadísticas sean correctas.
89
La recolección de estadísticas suele ser un proceso automático, aunque también se puede hacer de forma manual. Algunos ejemplos de las estadísticas que recopila Oracle:
De tabla: número de filas, número de bloques, número de registros por bloque, longitud media de los registros, registros , etc. select from where and
De columnas: número de valores diferentes en la columna, número de nulos, histogramas, etc. select from where and
El clustering_factor determina determina la eficiencia de un índice. Es el número de lecturas a disco que necesitaría hacer Oracle para leer todos los registros de una tabla, accediendo a través del índice (por ROWID) en el orden del índice. ín dice. Si los registros en la tabla están en un orden parecido al del índice, este valor será menor, y se aproximará al número de bloques de la tabla. Si los datos están dispersos respecto al orden del índice, el clustering factor se se podría acercar al número de registros de la tabla, y su uso no sería eficiente al realizar un index range scan . Si en una consulta se filtra por dos campos y para cada uno de ellos existe un índice, Oracle usará el índice más selectivo. 90
El uso de funciones como SUBSTR, INSTR, TO_DATE y TO_NUMBER deshabilita el uso de índices a no ser que se creen índices por funciones. Las estadísticas se recopilan con el paquete DBMS_STATS: Procedimiento
Obtiene
GATHER_INDEX_STATS
Estadísticas de índices
GATHER_TABLE_STATS
Estadísticas de tablas, columnas e índices.
GATHER_SCHEMA_STATS
Estadísticas de todos los objetos de un esquema.
GATHER_DATABASE_STATS
Estadísticas de todos los objetos de la base de datos.
Para que el coste calculado por el optimizador para cada plan de ejecución sea lo más real posible, y así elegir el que realmente sea más eficiente, Oracle necesita conocer los tiempos reales para cada una de las operaciones un una situación de carga real del sistema, teniendo en cuenta el número de sesiones, la memoria disponible para cada proceso, los tiempos reales de lectura y escrituras secuenciales y aleatorias, la velocidad de la CPU, etc.
91
Como Oracle no sabe cuándo es una situación de carga real, por defecto toma lo que se conoce como No Workload Statistics. Esta función realiza varias lecturas y escrituras durante unos minutos. Se realiza una vez, de forma automática, al crear la base de datos. execute dbms_stats.gather_system_stats();
Podemos calcular las estadísticas del sistema durante un periodo con las siguientes sentencias: dbms_stats.gather_system_stats('start'); dbms_stats.gather_system_stats('stop'); dbms_stats.gather_system_stats('interval', interval=>n);
donde n es el número de minutos durante los que se recopilan las estadísticas. El resultado de estas estadísticas se puede ver en la tabla sys.aux_stats$. Nota: Debido a un error en Oracle 11.2, el valor de los parámetros sreadtim (tiempo de lectura de un bloque en milisegundos) and mreadtim (tiempo de lectura de una lectura de bloques múltiple) se calcula mal, lo que induce a que a veces se elijan planes de ejecución que distan de ser los más óptimos. Este error se solucionó a partir de la versión 11.2.0.3.
10.3.1 Caso práctico create table prueba as select decode(mod(level, 2), 0, 1, decode(mod(level, 3), 0, 1, level)) as id from dual connect by level <= 100000; select * from prueba; create index prueba_idx_id on prueba(id);
Vamos a comprobar que solo se han calculado las estadísticas del índice (las del índice exactas). select table_name, num_rows, blocks, avg_row_len, last_analyzed from dba_tables
92
where owner = 'CURSO' and table_name = 'PRUEBA'; select from where and
Calculamos las estadísticas de la tabla: exec dbms_stats.gather_table_stats(ownname=> 'CURSO', tabname => 'PRUEBA');
Comprobamos que las del índice se han vuelto a calcular (esta vez no son exactas, Oracle ha hecho un muestreo). La densidad de un campo es la fracción de registros que se devolverían filtrando por ese campo. Así, por ejemplo, la densidad de un campo que solo puede tomar los valores ‘S’ o ‘N’, es 0,5. Normalmente equivale a 1 / num_distinct. Volvemos a calcularlas, esa vez exactas. Aunque no es necesario borrar las estadísticas anteriores, lo podemos hacer con la siguiente sentencia: exec
Por defecto, para el campo ID no se han calculado los histogramas de distribución.
93
Para poder observar las diferencias de comportamiento entre usar o no los histogramas, vamos a modificar el parámetro optimizer_dynamic_sampling: select * from v$parameter where name = 'optimizer_dynamic_sampling'; alter session set optimizer_dynamic_sampling = 0;
Vamos a ver la diferencia en los planes de ejecución de estas dos consultas: select * from prueba where id = 1; select * from prueba where id = 7; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
No existen diferencias. Vamos a calcular de nuevo las estadísticas, indicando ahora que se calculen los histogramas de los campos indexados: exec dbms_stats.gather_table_stats(ownname=> 'CURSO', tabname => 'PRUEBA', method_opt => 'for all indexed columns size 254');
Los planes de ejecución cambian considerablemente entre la primera y la segundo consulta. ¿qué ocurre si borramos las estadísticas y volvemos a poner el parámetro optimizer_dynamic_sampling en su valor original? exec
Podemos ver en el plan de ejecución que a falta de estadísticas Oracle ha usado el dynamic sampling: ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
1
‐
filter("ID"=1)
Note ‐‐‐‐‐
‐
dynamic sampling used for this statement (level=2)
10.4 Consejos generales Algunos consejos generales para hacer que las consultas sean más eficientes:
Intentar siempre que sea posible usar equi joins (inner join con =) frente a nonequi joins (inner join con operaciones del tipo <>, >, <, !=, BETWEEN). Reducir el número de registros con sentencias where lo antes posible, de forma que los conjuntos de registros a unir sean lo menores posible. Evitar usar subconsultas para calcular datos parciales. Es mejor usar la sentencia case. Evitar sobre todo llamadas a funciones Pl-Sql para obtener resultados que se podrían obtener mediante joins. De forma general, leer el mínimo número de registros posible. select (select count(*) from REPUESTOS_ET where IN_REPARABLE is NULL) SIN_DEFINIR,
95
(select count(*) from REPUESTOS_ET where not IN_REPARABLE is NULL) DEFINIDO from dual; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
select Sum(case when IN_REPARABLE is NULL then 1 else 0 end) as SIN_DEFINIR, Sum(case when IN_REPARABLE is NULL then 0 else 1 end) as DEFINIDO from REPUESTOS_ET; select Sum(Nvl2(IN_REPARABLE, 0, 1)) as SIN_DEFINIR, Sum(Nvl2(IN_REPARABLE, 1, 0)) as DEFINIDO from REPUESTOS_ET; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
10.5 Ordenación Situaciones en las que Oracle realiza ordenaciones:
Sentencias order by Sentencias group by Creación de un índice Al realizar un merge sort si no existen índices adecuados
Cada sesión dedicada tiene un área de memoria para realizar las ordenaciones. Si no hay suficiente espacio en memoria para realizar la ordenación, hay que utilizar espacio en disco. Las ordenaciones en disco son unas 14.000 veces más lentas que en memoria. Con la siguiente sentencia podemos ver las ordenaciones realizadas a nivel de sistema: select name, value from v$sysstat where name like 'sort%'; NAME
VALUE
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐
sorts (memory) sorts (disk) sorts (rows)
411308465 3388 6,9050E+10
select sysdate
‐
startup_time from v$instance;
70.000 millones de ordenaciones en 85 días:
≈
y con esta otra, a nivel de sesión: with R as ( select s.value as n_rows, s.sid from v$sesstat s, v$statname t
( select FX_MOVIMIENTO, NOC, CO_UNIDAD, CO_TIPO_MOVIMIENTO, row_number() over (order by FX_MOVIMIENTO desc) as RN from DIMA.MOVIMIENTO_ARTICULOS ) select /* q001 */ FX_MOVIMIENTO, NOC, CO_UNIDAD, CO_TIPO_MOVIMIENTO from S where RN = 5000000; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
1 2
‐
‐
filter("RN"=5000000) filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("FX_MOVIMIENTO") DESC )<=5000000)
Averiguamos el identificador de esta consulta: select sql_id, sql_text from v$sql where sql_text like '%q001%'; select operation, options, object_name name, trunc(bytes/1024/1024/1024) "input(GB)", trunc(last_memory_used/1024/1024) last_mem, trunc(estimated_optimal_size/1024/1024) opt_mem,
100
trunc(estimated_onepass_size/1024/1023) onepass_mem, decode(optimal_executions, null, null, optimal_executions||'/'||onepass_exe optimal_executions||'/'||onepass_executions||'/'||multi cutions||'/'||multipasses_executions) passes_executions) "O/1/M" from v$sql_plan p, v$sql_workarea w where p.address = w.address(+) and p.hash_value = w.hash_value(+) and p.id = w.operation_id(+) and p.sql_id='6xjv9qujyntmv'; order by p.id;
10.6 Análisis 10.6 Análisis y optimización de consultas 10.6.1 Función DBMS_XPLAN.DISPLAY_CURSOR Ya vimos en el apartado de los nested loop joins como joins como consultar el plan el plan de ejecución de ejecución de una consulta. La función display_cursor del del paquete dbms_xplan también dbms_xplan también nos permite ver el plan de ejecución de las consultas (cursores) que están en la memoria: dbms_xplan.display_cursor(
sql_id IN cursor_child_no IN format IN
VARCHAR2 NUMBER VARCHAR2
DEFAULT DEFAULT DEFAULT
NULL, 0, 'TYPICAL');
select sql_id, child_number, sql_text from v$sql where sql_text like '%q001%';
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
1 2
‐
‐
filter("RN"=5000000) filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("FX_MOVIMIENTO") DESC )<=5000000)
Esta función también nos permite ver las estadísticas del plan de ejecución, siempre que las activemos previamente. Podemos activarlas de dos formas: añadiendo el hint gather_plan_statistics gather_plan_statistics o estableciendo el parámetro statistics_level al valor ALL. select /*+ gather_plan_statistics q002 */ count(*) from DIMA.MOVIMIENTO_ARTICULOS M, DIMA.REPUESTOS_ET R where R.NOC = M.NOC and M.CO_TIPO_MOVIMIENTO = 'A' and M.CO_UNIDAD in ('213G9003', '50000005') and R.CO_FAMILIA_APOYO in ('003108', '310200'); select sql_id, child_number, sql_text from v$sql where sql_text like '%q002%'; select * from table(dbms_xplan.display_cursor('3junahtay3a82', 0, ' TYPICAL IOSTATS LAST ')); ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
DIMA.REPUESTOS_ET R R.NOC = M.NOC M.CO_TIPO_MOVIMIENTO = 'A' M.CO_UNIDAD in ('213G9003', '50000005') R.CO_FAMILIA_APOYO in ('003108', '310200');
select sql_id, child_number, sql_text from v$sql where sql_text like '%q004%'; select * from table(dbms_xplan.display_cursor('8w3m5452cx08y', 0, 'IOSTATS LAST'));
Nótese que ahora no hemos usado el hint gather_plan_statistics. ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Predicate Information (identified by operation id): ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
2 3 5 6
‐
‐
‐
‐
access("R"."NOC"="M"."NOC") filter(("R"."CO_FAMILIA_ filter(("R"."CO_FAMILIA_APOYO"='00310 APOYO"='003108' 8' OR "R"."CO_FAMILIA_APOYO"='310200')) filter("M"."CO_TIPO_MOVI filter("M"."CO_TIPO_MOVIMIENTO"='A') MIENTO"='A') access(("M"."CO_UNIDAD"= access(("M"."CO_UNIDAD"='213G9003' '213G9003' OR "M"."CO_UNIDAD"='50000005'))
Vamos ahora a forzar el uso del índice por el campo NOC de la tabla REPUESTOS_ET: select /*+ INDEX(R REPUESTOS_ET_NOC_U) q005 */ count(*)
104
En este nuevo plan de ejecución podemos comprobar que para cada uno de los 28.881 registros de MOVIMIENTO_ARTICULO que cumplen la condición, se accede a la tabla REPUESTOS_ET por su índice. Solo entonces podemos aplicar la condición por el campo CO_FAMILIA_APOYO.
10.6.2 Función DBMS_XPLAN.DISPLAY_AWR La versión de Oracle 10g incorporó el Automatic el Automatic Workload Workload Repository Repository (AWR) que (AWR) que recopila multitud de estadísticas, entre las que están las sentencias sql que sql que consumen más recursos. Para activarlo, el parámetro statistics_level tiene que estar a TYPICAL o ALL. El valor por defecto es TYPICAL por lo que por defecto está activado. AWR recopila la información en vistas que comienzan con DBA_HIST: select * from all_views where view_name like 'DBA_HIST%';
Por defecto, las estadísticas se recopilan en intervalos de una hora y se almacenan por un periodo de ocho días: select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1 desc;
La tabla DBA_HIST_WR_CONTROL contiene los intervalos y el periodo de retención que hay definidos: select * from dba_hist_wr_control;
106
Estos parámetros podemos modificarlos con el siguiente comando: exec dbms_workload_repository.modify_snaps dbms_workload_repository.modify_snapshot_settings(rete hot_settings(retention=>20160, ntion=>20160, interval=>15);
donde los parámetros retention e interval están expresados en minutos. También podemos tomar un "snapshot" manualmente en cualquier momento: exec dbms_workload_repository.create_snapshot;
o borrar un rango de "snapshots": exec dbms_workload_repository.drop_snapsho dbms_workload_repository.drop_snapshot_range(low_snap_ t_range(low_snap_id=>35, id=>35, high_snap_id=>37);
Lo más interesante de este mecanismo es que podemos consultar los planes de ejecución de consultas pasadas que ya no están en la SGA: select s.snap_id, s.disk_reads_delta reads_delta, s.executions_delta exec_delta, round(s.disk_reads_delta / decode(s.executions_delta, 0, 1, s.executions_delta)) rds_exec_ratio, s.sql_id, dbms_lob.substr(t.sql_text, 4000) from dba_hist_sqlstat s, dba_hist_sqltext t where t.sql_id = s.sql_id and s.disk_reads_delta > 1000 order by s.disk_reads_delta desc; select * from table(dbms_xplan.display_awr('fw4w9g3k4mbpa'));
10.6.3 Real‐time SQL monitoring
Es el mecanismo más efectivo para identificar sentencias problemáticas. Se ha introducido en la versión 11g. Oracle 11g monitoriza automáticamente todas las sentencias que se ejecuten en paralelo o cuya ejecución dure más de 5 segundos. Además, permite la monitorización de las sentencias en tiempo real, aunque aún se estén ejecutando.
107
La monitorización está activada si el parámetro statistics_level está a TYPICAL or ALL, y el parámetro control_management_pack_access tiene el valor DIAGNOSTIC+TUNING. select * from v$parameter where name in ('statistics_level', 'control_management_pack_access');
Por defecto está activo. La siguiente vista muestra un listado de las sentencias que se han monitorizado y aún están en la memoria: select sql_exec_start, status, username, round(elapsed_time / 1000000, 2) seconds, fetches, buffer_gets, disk_reads, direct_writes, sql_id, sql_text from v$sql_monitor order by 1 desc;
y el plan de ejecución, finalizado o no: select dbms_sqltune.report_sql_monitor( sql_id => 'g5hb0m0wq3s1v', type => 'TEXT', report_level => 'ALL') as report from dual;
Incluso podemos consultar las variables que se han usado: select /*+ monitor */ * from REPUESTOS_ET where CO_FAMILIA_APOYO = :cdFamilia;
‐‐
148000
select sql_id, replace(sql_text, chr(10), ' ') sql_text from v$sql_monitor order by sql_exec_start desc;
108
select xmltype(binds_xml) from v$sql_monitor where sql_id = 'fjcgkv750xnm3';
Vamos a lanzar una consulta larga desde otra sesión (usuario CURSO) para comprobar cómo podemos monitorizarla e incluso ver cuánto le queda para finalizar: select count(*) from DIMA.MOVIMIENTO_ARTICULOS M, DIMA.REPUESTOS_ET R where R.NOC = M.NOC and M.CO_TIPO_MOVIMIENTO = 'A' and M.CO_UNIDAD in ('213G9003', '50000005') and R.CO_FAMILIA_APOYO in ('003108', '310200');
Tenemos que dar permisos al usuario CURSO para que pueda leer estas dos tablas del usuario DIMA: grant select on DIMA.REPUESTOS_ET to CURSO; grant select on DIMA.MOVIMIENTO_ARTICULOS to CURSO; select sql_id, replace(sql_text, chr(10), ' ') sql_text from v$sql_monitor order by sql_exec_start desc; select dbms_sqltune.report_sql_monitor( sql_id => '9mk98zvcvdrp3', type => 'TEXT', report_level => 'ALL') as report from dual;
Es muy importante a la hora de analizar una consulta ser consciente de que existe la cache de datos, por lo que es posible que los datos ya estén en la cache y el tiempo de respuesta no sea real. Vamos a lanzar otra consulta larga desde el usuario CURSO:
109
select /*+ index(R REPUESTOS_ET_PK) */ count(distinct CO_FAMILIA_APOYO) from DIMA.REPUESTOS_ET R;
¿por qué es larga? La monitorizamos desde nuestra sesión y tarda más de un minuto. ¿qué ocurre si la volvemos a lanzar? Para evitar esto, podemos borrar la cache con la siguiente sentencia: alter system flush buffer_cache;
Ahora, si la volvemos a lanzar, tendrá que volver a leer los bloques de la tabla REPUESTOS_ET. La compilación de las sentencias también tarda algo de tiempo. Si también queremos tener en cuenta el tiempo de la compilación, podemos borrar la library cache con la sentencia: alter system flush shared_pool;
Ejecutar este comando de vez en cuando puede ser interesante para eliminar sentencias sql no reusables, como por ejemplo, las que usan parámetros fijos en lugar de variables (y de esas tenemos bastantes). Ver demostración de dbms_sqltune.report_sql_monitor con con el parámetro type=>ACTIVE (desde Oracle 11g Release 2). 10.6.4 Análisis de un “expediente X”
El campo CO_FAMILIA_APOYO de la tabla REPUESTOS_ET es una foreign key de de la tabla FAMILIA_APOYO_ET. ¿Cómo podemos averiguar cuántos de los registros de la tabla FAMILIA_APOYO_ET no se usan en la tabla REPUESTOS_ET? select /*+ monitor */ * from FAMILIA_APOYO_ET where CO_FAMILIA_APOYO not in (select CO_FAMILIA_APOYO from REPUESTOS_ET);
110
La consulta devuelve el resultado muy rápido. Seguramente la tabla REPUESTOS_ET está en la cache. alter system flush buffer_cache;
La volvemos a ejecutar y tarda lo mismo. Vamos a ver su plan de ejecución: select sql_id, replace(sql_text, chr(10), ' ') sql_text from v$sql_monitor order by sql_exec_start desc; select dbms_sqltune.report_sql_monitor( sql_id => 'b5z2d6cjxzw9c', type => 'TEXT', report_level => 'ALL') as report from dual;
¿solo ha leído 149 registros de REPUESTOS_ET con dos lecturas físicas cuando el plan de ejecución dice que se hace una lectura completa de REPUESTOS_ET? ¿cómo es posible? De hecho, el resultado es incorrecto… 10.6.5 Hints más comunes 10.6.5.1 FIRST_ROWS y y ALL_ROWS ALL_ROWS
Si no se indica nada, se utiliza el modo definido en el parámetro optimizer_mode: select * from v$parameter where name = 'optimizer_mode'; select from where and
* MOVIMIENTO_ARTICULOS M, REPUESTOS_ET R M.NOC = R.NOC M.CA_MOVIMIENTO > 100000
111
and R.IN_REPARABLE = 'S'; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
El parámetro de este hint puede puede tomar los valores 1, 10, 100 o 1000. Al usarlo, el plan de ejecución intenta devolver las primeras filas lo más rápido posible, aunque si se seleccionan más filas sería más lento.
112
10.6.5.2 INDEX y NO_INDEX y NO_INDEX select from where and and
/*+ FIRST_ROWS(10) NO_INDEX(M MOVIMIENTO_ARTICULOS_IDS_IDX5) */ * MOVIMIENTO_ARTICULOS M, REPUESTOS_ET R M.NOC = R.NOC M.CA_MOVIMIENTO > 100000 R.IN_REPARABLE = 'S';
select /*+ INDEX_FFS(M MOVIMIENTO_ARTICULOS_IDS_IDX5) */ CO_UNIDAD, ANO, FX_MOVIMIENTO from DIMA.MOVIMIENTO_ARTICULOS M where CO_UNIDAD = '213G9003' and ANO = 2012; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
El hint ORDERED hace que el optimizador intente hacer los joins en el mismo orden en el que las tablas aparecen en la cláusula FROM: select from where and and
/*+ ORDERED */ * DIMA.MOVIMIENTO_ARTICULOS M, DIMA.REPUESTOS_ET R M.NOC = R.NOC M.CA_MOVIMIENTO > 100000 R.IN_REPARABLE = 'S';
Si todas las filas que necesitamos se pueden extraer de varios índices, estos se pueden unir como si fuesen tablas más pequeñas y de ese modo no sería necesario acceder a la tabla: select /*+ INDEX_JOIN(M MOVIMIENTO_ARTICULOS_IDS_IDX5 MOVIMIENTO_ARTICULOS_IDS_IDX6) */ CO_UNIDAD, ANO, FX_MOVIMIENTO, NU_MOVIMIENTO from DIMA.MOVIMIENTO_ARTICULOS M where CO_UNIDAD = '213G9003' and ANO = 2012; ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
| SELECT STATEMENT | | VIEW | index$_join$_001 | HASH JOIN | | INDEX RANGE SCAN | MOVIMIENTO_ARTICULOS_IDS_IDX6 | INDEX FAST FULL SCAN| MOVIMIENTO_ARTICULOS_IDS_IDX2
11 El nuevo scheduler (jobs) Oracle 10g introdujo el paquete dbms_scheduler para para sustituir al conocido dbms_job. EL paquete dbms_scheduler es más más flexible y potente. Sintaxis para la creación básica de un job: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL,
118
end_date job_class enabled auto_drop comments
IN IN IN IN IN
TIMESTAMP WITH TIME ZONE DEFAULT NULL, VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', BOOLEAN DEFAULT FALSE, BOOLEAN DEFAULT TRUE, VARCHAR2 DEFAULT NULL);
Ejemplo: begin dbms_scheduler.create_job( job_name => 'prueba_01_job', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN NULL; END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0; bysecond=0;', enabled => TRUE, comments => 'Job definido completamente mediante el procedimiento CREATE JOB.'); end;
Aunque hemos creado el job, todavía no se ha ejecutado. Se ejecutará cuando se cumpla la condición especificada en el parámetro repeat_interval. Podemos comprobarlo con la siguiente consulta: select job_name, job_action, repeat_interval, enabled, last_start_date, last_run_duration, next_run_date, run_count, failure_count, state from all_scheduler_jobs where owner = 'CURSO'; select job_name, job_action, repeat_interval, enabled, last_start_date, last_run_duration, next_run_date, run_count, failure_count, state from user_scheduler_jobs;
119
Es habitual crear los jobs sin especificar que el parámetro enabled sea sea true (el valor por defecto es false) y activarlo de forma manual posteriormente: exec dbms_scheduler.disable('prueba_01_job'); exec dbms_scheduler.enable('prueba_01_job');
Podemos consultar el histórico de lanzamientos de un job mediante va vista all_scheduler_job_log: select * from all_scheduler_job_log where owner = 'CURSO'; select * from user_scheduler_job_log;
Es posible cambiar los atributos de un job mediante la función set_attribute: exec dbms_scheduler.set_attribute('prueba_01_job', 'repeat_interval', 'freq=minutely; interval=5; bysecond=0;');
Si se produce algún error, podemos consultarlo en el campo ADDITIONAL_INFO de la tabla all_scheduler_job_run_details. Vamos a generar un error: exec dbms_scheduler.set_attribute('prueba_01_job', 'job_action', 'begin foo; end;');
El campo status de la tabla user_scheduler_job_log aparecerá como FAILED. select log_date, job_name, additional_info from user_scheduler_job_run_details;
Un job finaliza bien cuando alcanza la fecha indicada en el parámetro end_date, bien cuando se ejecuta el número de veces especificado en el parámetro max_runs. Lo que ocurre cuando finaliza depende del valor del parámetro auto_drop. Si es true el job se borra. En caso contrario, su estado pasa a COMPLETED.
120
Otro atributo interesante es max_failures. Con él podemos limitar el número máximo de errores antes de que el job se deshabilite con el estado BROKEN. exec dbms_scheduler.set_attribute('prueba_01_job', 'max_failures', '3');
Un job en ejecución se puede parar con el procedimiento: DBMS_SCHEDULER.STOP_JOB ( job_name IN VARCHAR2 force IN BOOLEAN DEFAULT FALSE); exec dbms_scheduler.stop_job('prueba_01_job');
Si el job no para de forma normal, se puede forzar la parada con el parámetro force (en este caso no se pueden recopilar las estadísticas). Eliminación de un job: exec dbms_scheduler.drop_job('prueba_01_job');
En la versión 12 de Oracle se ha incluido el parámetro defer en en la función drop_job de forma que si se establece a true, el job se eliminará cuando termine o se produzca el primer error. Un job se puede lanzar manualmente sin que esto afecte a su programación: DBMS_SCHEDULER.RUN_JOB ( job_name use_current_session
IN VARCHAR2, IN BOOLEAN DEFAULT TRUE);
exec dbms_scheduler.run_job('prueba_01_job');
Cuando el parámetro use_current_session es true (es el valor por defecto):
121
El job se ejecuta con el usuario que lanza el procedimiento, de forma síncrona. Se puede ver el resultado de la ejecución en línea. Los campos RUN_COUNT, LAST_START_DATE, LAST_RUN_DURATION, y FAILURE_COUNT, no se actualizan. La tabla La tabla dba_scheduler_job_log sí se actualiza. Se puede lanzar el job aunque haya otra instancia ejecutándose en ese momento.
Cuando use_current_session es false:
Se ejecuta con la sesión del dueño del job, de forma asíncrona (igual que cuando se ejecuta de forma programada) Los errores hay que verlos en la tabla dba_scheduler_job_log. Se actualizan los campos RUN_COUNT, LAST_START_DATE, LAST_RUN_DURATION, y FAILURE_COUNT. La función falla si el job se está ejecutando.
Los lanzamientos manuales quedan registrados en el campo ADDITIONAL_INFO de la tabla dba_scheduler_job_log.
11.1 Programs El programador permite crear tareas o acciones sin que estén asociadas a una programación (o calendario). Posteriormente, se podrán crear jobs asociados a estas tareas (programs). DBMS_SCHEDULER.CREATE_PROGRAM ( program_name IN VARCHAR2, program_type IN VARCHAR2, program_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, enabled IN BOOLEAN DEFAULT FALSE, comments IN VARCHAR2 DEFAULT NULL);
El parámetro program_type puede ser del tipo PLSQL_BLOCK, EXECUTABLE o STORED_PROCEDURE. begin
122
dbms_scheduler.create_program ( program_name => 'test_plsql_block_prog', program_type => 'PLSQL_BLOCK', program_action => 'begin dbms_stats.gather_schema_stats(''SITRANS''); end;', enabled => true, comments => 'Programa para recopilar estadísticas del usuario SITRANS usando un bloque PL/SQL.'); end;
No se puede crear un program con number_of_arguments > 0 con el atributo enabled a a true. Una vez creado el job, tendremos que asignar los parámetros necesarios. begin dbms_scheduler.create_program ( program_name => 'test_stored_procedure_prog', program_type => 'STORED_PROCEDURE', program_action => 'dbms_stats.gather_schema_stats', number_of_arguments => 1, enabled => false, comments => 'Programa para recopilar estadísticas de un usuario usando un procedimiento almacenado.'); dbms_scheduler.define_program_argument ( program_name => 'test_stored_procedure_prog', argument_name => 'ownname', argument_position => 1, argument_type => 'VARCHAR2', default_value => 'SITRANS'); dbms_scheduler.enable (name => 'test_stored_procedure_prog'); end; select program_name, program_action, number_of_arguments, enabled from dba_scheduler_programs
123
where owner = 'DIMA';
Los programas se pueden borrar con el procedimiento drop_program: dbms_scheduler.drop_program(program_name => 'test_stored_procedure_prog');
11.2 Schedules Las programaciones también se pueden almacenar de forma independiente: begin DBMS_SCHEDULER.create_schedule ( schedule_name => 'test_hourly_schedule', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'Repite, cada hora, para siempre.'); end; select * from dba_scheduler_schedules;
11.3 Creación de jobs begin Job definido de forma completa mediante el procedimiento CREATE JOB. dbms_scheduler.create_job ( job_name => 'test_full_job', job_type => 'PLSQL_BLOCK', job_action => 'begin dbms_stats.gather_schema_stats(''SITRANS''); end;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => Job definido de forma completa mediante el procedimiento CREATE JOB.'); ‐‐
124
Job definido usando un ‘program’ y un ‘schedule’ existentes. dbms_scheduler.create_job ( job_name => 'test_prog_sched_job', program_name => 'test_plsql_block_prog', schedule_name => 'test_hourly_schedule', enabled => false, comments => Job definido usando un program y un schedule existentes.');
‐‐
Job definido mediante un ‘program’ existente y un ‘schedule’ en línea. dbms_scheduler.create_job ( job_name => 'test_prog_job_definition', program_name => 'test_plsql_block_prog', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => true, comments => Job definido mediante un program existente y un schedule en linea.');
‐‐
Job definido por un ‘schedule’ existente y un ‘program’ en línea. dbms_scheduler.create_job ( job_name => 'test_sched_job_definition', schedule_name => 'test_hourly_schedule', job_type => 'PLSQL_BLOCK', job_action => 'begin dbms_stats.gather_schema_stats(''SITRANS''); end;', enabled => true, comments => 'Job definido por un schedule existente y un program en línea.'); end; ‐‐
select * from dba_scheduler_jobs where owner = 'DIMA';
Los programs y schedules no se pueden eliminar si están siendo usados por algún job.
125
11.4 Ejecución de jobs externos begin dbms_scheduler.create_job(job_name => 'mkdir_job', job_type => 'EXECUTABLE', job_action => 'e:\temp\mkdir1.bat', number_of_arguments => 1, enabled => false ); dbms_scheduler.set_job_argument_val dbms_scheduler.set_job_argument_value('mkdir_job', ue('mkdir_job', 1, 'nuevo_directorio'); dbms_scheduler.enable('mkdir_job'); end;
Contenido del fichero mkdir1.bat : mkdir e:\%1
Este job crea el directorio nuevo_directorio en el directorio raíz de la unidad E del servidor. Importante: para que funcionen los jobs externos, es necesario que este levantado el servicio OracleJobScheduler .
Como no hemos establecido la propiedad auto_drop => false ni repeat_interval, el job se borrará una vez que se ejecute, aunque sí quedará registrado en la tabla de log.
frequency_clause = "FREQ" "=" frequency frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY"
Para probar algunos ejemplos creamos el procedimiento test_calendar_string: create or replace procedure test_calendar_string(p_calendar_string in VARCHAR2, p_iterations in INTEGER) is l_start_date TIMESTAMP := TO_TIMESTAMP('27 AGO 2014 08:04:32', 'DD MON YYYY HH24:MI:SS'); l_return_date_after TIMESTAMP := l_start_date; l_next_run_date TIMESTAMP; begin dbms_output.put_line('calendar_string: ' || p_calendar_string || ' iterations: ' || p_iterations); for i in 1 .. p_iterations loop dbms_scheduler.evaluate_calendar_string( calendar_string => p_calendar_string, start_date => l_start_date, return_date_after => l_return_date_after, next_run_date => l_next_run_date); ‐
‐
‐
‐
dbms_output.put_line('Next Run Date: ' || l_next_run_date); l_return_date_after := l_next_run_date; end loop; end;
begin test_calendar_string('freq=daily;', 4); end; calendar_string: freq=daily; iterations: 4 Next Run Date: 28/08/14 08:04:32,000000 Next Run Date: 29/08/14 08:04:32,000000 Next Run Date: 30/08/14 08:04:32,000000 Next Run Date: 31/08/14 08:04:32,000000
127
begin test_calendar_string('freq=daily; byhour=0; byminute=0; bysecond=0;', 3); end; calendar_string: freq=daily; byhour=0; byminute=0; bysecond=0; iterations: 3 Next Run Date: 28/08/14 00:00:00,000000 Next Run Date: 29/08/14 00:00:00,000000 Next Run Date: 30/08/14 00:00:00,000000
begin test_calendar_string('freq=minutely; bysecond=0;', 3); end; calendar_string: freq=minutely; bysecond=0; iterations: 3 Next Run Date: 27/08/14 08:05:00,000000 Next Run Date: 27/08/14 08:06:00,000000 Next Run Date: 27/08/14 08:07:00,000000
begin test_calendar_string('freq=minutely; interval=5; bysecond=0;', 3); end; calendar_string: freq=minutely; interval=5; bysecond=0; iterations: 3 Next Run Date: 27/08/14 08:09:00,000000 Next Run Date: 27/08/14 08:14:00,000000 Next Run Date: 27/08/14 08:19:00,000000
begin test_calendar_string('freq=weekly; byday=mon; byhour=9; byminute=0; bysecond=0;', 3); end; calendar_string: freq=weekly; byday=mon; byhour=9; byminute=0; bysecond=0; iterations: 3 Next Run Date: 01/09/14 09:00:00,000000 Next Run Date: 08/09/14 09:00:00,000000 Next Run Date: 15/09/14 09:00:00,000000
128
begin test_calendar_string('freq=weekly; byday=mon,wed,fri; byhour=6; byminute=0; bysecond=0;', 3); end; calendar_string: freq=weekly; byday=mon,wed,fri; byhour=6; byminute=0; bysecond=0; iterations: 3 Next Run Date: 29/08/14 06:00:00,000000 Next Run Date: 01/09/14 06:00:00,000000 Next Run Date: 03/09/14 06:00:00,000000
El primer lunes de enero, abril, julio y octubre: begin test_calendar_string('freq=monthly; bymonth=1,4,7,10; byday=1mon; byhour=6; byminute=0; bysecond=0;', 4); end; calendar_string: freq=monthly; bymonth=1,4,7,10; byday=1mon; byhour=6; byminute=0; bysecond=0; iterations: 4 Next Run Date: 06/10/14 06:00:00,000000 Next Run Date: 05/01/15 06:00:00,000000 Next Run Date: 06/04/15 06:00:00,000000 Next Run Date: 06/07/15 06:00:00,000000
Ejemplos para start_date y end_date:
Mañana a esta misma hora: 'sysdate + 1' Mañana a las 06:00: 'trunc(sysdate) + 1 + 6/24' Al comienzo de la próxima hora: 'trunc(sysdate, ''HH24'') + 1/24' Dentro de un minuto: 'sysdate + 1/24/60' El siguiente minuto exacto: 'trunc(sysdate, ''MI'') + 1/24/60' En cinco minutos: 'trunc(sysdate, ''MI'') + 5/24/60' El próximo miércoles a las 09:00: 'trunc(next_day(sysdate, ''miércoles'')) + 9/24'
129
El próximo lunes, miércoles o viernes a las 06:00: 'trunc(least(next_day(sysdate, ''lunes''), next_day(sysdate, ''miércoles''), next_day(sysdate, ''viernes''))) + (6/24)'
El primer lunes del próximo trimestre: 'next_day(add_months(trunc(sysdate, ''q''), 3), ''lunes'')'
12 Miscelánea 12.1 Tipos de comandos DDL – Data Definition Language – sentencias que modifican la estructura de los objetos:
CREATE – creación de objetos ALTER – modificación de objetos DROP – eliminación de objetos TRUNCATE – elimina todos los registros de una tabla y el espacio que ocupan COMMENT – añade un comentario a un objeto RENAME – renombrar un objeto
DML – Data Manipulation Language – sentencias de manipulación de datos:
SELECT – consulta de información INSERT – inserción UPDATE – actualización DELETE – elimina registros de una tabla, pero no el espacio que ocupan MERGE – combinación de los datos de dos tablas
12.2 Abrir 12.2 Abrir una base de datos C:\> set oracle_sid=ORCL C:\> sqlplus sys as sysdba
130
SQL*Plus: Release 10.1.0.2.0
‐
Production on Mon Feb 21 12:35:48
Enter password: xxxx Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened.
251658240 788368 145750128 104857600 262144
bytes bytes bytes bytes bytes
También se puede especificar la contraseña en el propio comando: C:\> sqlplus sys/passwd as sysdba
Este comando abre la base de datos en tres pasos: 1. Startup (nomount)
Lee los parámetros de inicialización (del spfile o pfile) y los utiliza para crear la SGA (reservar la memoria) y lanzar los "background processes". La instancia está levantada.
SQL> startup nomount;
131
2. Mount
Lee el fichero de control, que contiene información importante, como la localización de los ficheros de datos y otros recursos. Sin el fichero de control no es posible montar la base de datos. Por este motivo es importante tener más de una copia de este fichero y hacer copias de seguridad. SQL> startup mount
Si la base de datos ya está levantada en modo " nomount " se puede montar con el comando: SQL> alter database mount;
En este modo ya es posible acceder a las tablas fijas y vistas del diccionario de datos (tablas internas de configuración de Oracle), por ejemplo: SQL> select status from v$instance; STATUS ‐‐‐‐‐‐‐‐‐‐‐‐
MOUNTED SQL>
3. Open
Accede a los ficheros de datos y comprueba que son consistentes. SQL> startup
Si la base de datos ya está levantada en modo " mount " se puede abrir con el comando: SQL> alter database open;
132
Existe la posibilidad de abrir la base de datos en modo restringido, de forma que solo los administradores (DBAs) puedan acceder a ella. Técnicamente la base de datos está abierta: SQL> startup restrict
Es posible cambiar la instanca del modo normal al modo restringido y viceversa con los comandos: SQL> alter system enable restricted session; SQL> alter system disable restricted session;
aunque los usuarios que estuviesen conectados al restringir la instancia seguirían conectados. También es posible arrancar una base de datos con un fichero de parámetros distinto al habitual: SQL> startup pfile=C:\oracle\database\init2.ora
12.3 Parada de una base de datos La parada de Oracle se realiza mediante el comando shutdown después de haber establecido una conexión como sys as sysdba. Existen tres tipos de shutdown: 1. shutdown normal 2. shutdown immediate 3. shutdown abort
Shutdown normal SQL> shutdown
133
No se utiliza habitualmente ya que espera a que todos los usuarios se desconecten de forma ordenada, por lo que el proceso podría tardar horas. También podría haber procesos "zombies" (Oracle cree que hay alguien conectado pero realmente no lo hay) que habría que matar manualmente para que la base de datos se cerrase.
Espera a que los usuarios conectados actualmente finalicen todas las operaciones. Evita nuevas conexiones. Los usuarios que intentan conectarse reciben el mensaje "Shutdown in progress". Cierra y desmonta la base de datos. No se necesita recuperación al arrancar la base de datos. Se cierra en un modo consistente.
Shutdown immediate SQL> shutdown immediate
Funciona la mayoría de las veces. Espera a que las transacciones finalizadas (committed) se completen, es decir, las modificaciones realizadas en memoria se envían a los ficheros de datos. Evita nuevas transacciones y nuevas conexiones. Se finalizan las sesiones no activas y se hace un rollback de de aquellas transacciones que no estén validadas (uncommitted) Cierra y desmonta la base de datos. No se necesita recuperación al arrancar la base de datos. Se cierra en un modo consistente.
Shutdown transactional
Igual que el shutdown immediate pero esperando que la transacciones no validadas terminen (que se realize el commit de las transacciones comenzadas). No hace rollback . Shutdown abort SQL> shutdown
abort
134
Parada drástica, no espera a que los usuarios conectados finalicen sus transacciones. No se realiza rollback de de las transacciones pendientes. Se necesita recuperación al arrancar la base de datos.
12.4 Creación del usuario CURSO CREATE USER CURSO IDENTIFIED BY curso DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT DBA TO CURSO; GRANT UNLIMITED TABLESPACE TO CURSO; GRANT EXECUTE, READ, WRITE ON DIRECTORY TOAD_TRACEFILE_DIR TO CURSO WITH GRANT OPTION;
12.5 Validación de tablas e índices Aunque no es habitual, en algún caso una tabla o un índice pueden corromperse. Podemos comprobar si una tabla tiene algún problema con el siguiente comando: analyze table DIMA.REPUESTOS_ET validate structure;
Si añadimos la cláusula cascade, también se analizan sus índices asociados: analyze table DIMA.REPUESTOS_ET validate structure cascade;
Con la cláusula adicional fast, la comprobación es más rápida, pero solo se indica que hay un error, no cuál es el error. Así que es mejor realizar la comprobación con la cláusula fast, y solo en caso de error, la volvemos a ejecutar sin ella.
12.6 Filas migradas y encadenadas 12.6.1 Filas migradas
Son filas que, al aumentar de tamaño tras una sentencia update, no caben en el bloque en el que están y se mueven a otro bloque. El problema es que no se pueden mover sin más, debido a que los índices siguen apuntando al bloque inicial. Lo que hace Oracle es dejar en la posición original un enlace a la nueva posición del registro. El problema que tienen estos registros, es que cuando se intenta acceder a ellos y el registro no está, es necesario leer también el nuevo bloque, por lo que es necesario hacer una lectura adicional. 12.6.2 Filas encadenadas
Son filas mayores que el tamaño de un bloque, por lo que se almacenan en más de un bloque. Estas filas suelen aparecer en tablas con campos de tamaño variable: VARCHAR2, CHAR, CLOB, etc. 12.6.3 Búsqueda de filas migradas y encadenadas select 'analyze table DIMA.' || table_name || ' list chained rows;' from all_tables where owner = 'DIMA'; analyze analyze analyze analyze ... select from group order
table table table table
DIMA.ERMACS DIMA.ERMACO DIMA.ERMACF DIMA.COSNSA
list list list list
chained chained chained chained
rows; rows; rows; rows;
owner_name, table_name, count(*) chained_rows by owner_name, table_name by 3 desc;
create table TMP_REPUESTOS_ET as select * from DIMA.REPUESTOS_ET where rowid in ( select head_rowid from chained_rows where owner_name = 'DIMA' and table_name = 'REPUESTOS_ET' ); delete from DIMA.REPUESTOS_ET where rowid in ( select head_rowid from chained_rows where owner_name = 'DIMA' and table_name = 'REPUESTOS_ET' ); insert into DIMA.REPUESTOS_ET select * from TMP_REPUESTOS_ET;
138
drop table TMP_REPUESTOS_ET; delete from chained_rows where owner_name = 'DIMA' and table_name = 'REPUESTOS_ET';
A continuación volvemos a analizar la tabla. Si aparece alguna fila, debe ser encadenada. No siempre se pueden eliminar las filas encadenadas. Si el tamaño del bloque es in ferior al tamaño del registro, no se puede evitar.
12.7 Recycle bin A partir de la versión 10g, cuando se elimina una tabla, lo que qu e hace Oracle es renombrarla, y también renombra todos sus objetos relacionados: índices, triggers, segmentos de LOB, etc. Se activa con un parámetro de inicialización de la base de datos. Por defecto está activado: select * from v$parameter where name = 'recyclebin';
Puede activarse y desactivarse a nivel de sesión: alter session set recyclebin = off;
Ejemplos: create table tst (col varchar2(10), row_chng_dt date); insert into tst values ('Version1', sysdate); select * from tst;
139
drop table tst; select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin; select * from "BIN$aaEKgrOgSSqPsf+xD0hC9Q==$0"; flashback table tst to before drop;
Si se crea de nuevo una tabla que se había eliminado, y se vuelve a eliminar, en la papelera existirán dos versiones. Si existen varias versiones, especificar en el flashback el el nombre que tiene en la papelera: flashback table "BIN$aaEKgrOgSSqPsf+xD0hC9Q==$0" to before drop;
Se pueden eliminar objetos de la papelera con el comando purge table y purge index : purge table "BIN$aaEKgrOgSSqPsf+xD0hC9Q==$0";
Los objetos permanecen en el tablespace mientras que haya sitio disponible. Si no queda espacio (no se utiliza el autoextend para para almacenar elementos eliminados), se van eliminando los más antiguos. Para eliminar todos los elementos de la papelera (del usuario), usar el siguiente comando: purge recyclebin;
recyclebin es un sinónimo para user_recyclebin. La papelera global es dba_recyclebin. select * from dba_recyclebin; purge dba_recyclebin;
140
12.8 Sql avanzado 12.8.1 Contar días de la semana
¿Cuántos martes y jueves hay en un periodo determinado, entres las fechas :desde y :hasta? Vamos a crear la función get_weekday que nos devuelva el día de la semana de una fecha: Necesitamos permisos para poder crear una función: grant create any procedure to CURSO; create or replace function get_weekday(date in DATE) return INTEGER as begin return to_number(to_char(date, 'D')); end get_weekday; select get_weekday(sysdate) from dual; select :desde + 1 from dual; select :hasta
‐
:desde + 1 from dual;
select level from dual connect by level <= 5; select :desde + level
‐
1 from dual connect by level <= :hasta
select :desde + level 1, get_weekday(:desde + level from dual where get_weekday(:desde + level 1) in (2, 4) connect by level <= :hasta :desde + 1; ‐
‐
‐
:desde + 1;
1)
‐
‐
141
select count(*) from dual where get_weekday(:desde + level 1) in (2, 4) connect by level <= :hasta :desde + 1; ‐
‐
12.8.2 Eliminar registros duplicados 12.8.2.1 Método 1 – respecto a unas claves conocidas: delete from table_name a where rowid <> ( select min(rowid) from table_name b where a.key_values = b.key_values );
12.8.2.2 Método 2 – eliminar eliminar duplicados duplicados idénticos: SQL> create table table_name2 as select distinct * from table_name1; SQL> drop table table_name1; SQL> rename table_name2 to table_name1;
Este método es muy rápido, pero hay que recrear los índices, constraints (restricciones), triggers (disparadores), etc. 12.8.2.3 Método 3 – Usando Usando funciones funciones analíticas: delete table_name where rowid in ( select lead(rowid) over (partition by key_values order by null) from table_name );
Las funciones analíticas permiten realizar consultas contra más de una fila, para cada fila, sin tener que hacer un join de la tabla consigo misma.
142
La operación opuesta a lead es es lag.
12.9 Permisos y seguridad (algunas consulta útiles) Usuarios con contraseñas por defecto. Deberían estar bloqueados: select u.username, u.account_status from dba_users_with_defpwd d, dba_users u where d.username = u.username;
Usuarios con permisos “sensibles”: select from where and and
* dba_sys_privs p, dba_users u p.grantee = u.username p.privilege in ('SELECT ANY TABLE', 'SELECT ANY DICTIONARY') not u.account_status like '%LOCKED%';
select from where order
* dba_sys_privs privilege like '%ANY%' by grantee;
Generación de la lista de usuarios como una cadena separados por comas: select listagg('''' || username || '''', ',') within group (order by username) from all_users;
Permisos asignados a un usuario: select * from dba_tab_privs where grantee = 'SITRANS';
143
Roles asignados a un usuario: select grantee, granted_role from dba_role_privs;
Todo lo que tiene asignado un usuario: select from where union select from where union select from where
privilege dba_sys_privs grantee = 'DBSNMP' all granted_role privilege dba_role_privs grantee = 'DBSNMP' all privilege||' on '||owner||'.'||table_name privilege dba_tab_privs grantee = 'DBSNMP';
Lista de los permisos que un usuario tiene sobre los objetos de otro esquema, esqu ema, en este caso del usuario NOGAL sobre los objetos del esquema DIMA: select * from dba_role_privs where grantee = 'NOGAL'; with S as ( select table_name, privilege, 'S' as directo from dba_tab_privs where grantee = 'NOGAL' and owner = 'DIMA' union select table_name, privilege, 'N' as directo from dba_tab_privs where grantee = 'NOGAL_ROLE'
144
and owner = 'DIMA' ) select a.object_type, a.object_name, S.privilege, S.directo from all_objects a, S where a.owner = 'DIMA' and a.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TABLE', 'VIEW') and a.object_name = S.table_name(+) order by a.object_type, a.object_name, S.privilege, S.directo;
