curso administración oracle

7/25/2019 Curso Administracin Oracle

1/145


2/145


3/145


4/145


5/145

5

1 Introduccin

Resultado de solicitar varias veces un curso de administracin de Oracle.

Mucha materia. Vamos a ver muchas cosas, pero no es exhaustivo.

Conceptos bsicos generales, arquitectura y funcionamiento.

Otros apartados ms especficos, tiles para el trabajo del da a da de algunos de nosotros.

Dificultad para estructurar la informacin. Muchos conceptos se entremezclan. No se pueden explicar las estructuras de memoria sin hablarsimultneamente los procesos, ni de ndices sin ver conceptos de optimizacin y estadsticas.

Conocimiento asistentes muy diferente. Es posible que durante las explicaciones algunos conceptos se supongan conocidos. Ante cualquier duda,

preguntar.

Es preferible que los asistentes no tengan ordenadores. Podemos ver ms materia.

2 Quesunabasededatos?

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 informacin de forma estructurada en forma de tablas. Cada tabla contiene

registros, formados a su vez por campos. Estos campos estn identificados con su nombre, tipo y tamao.

Otros componentes que tambin 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.

Trminos con los que tenemos que estar familiarizados: tabla, registro, campo, vista, procedimiento, funcin, paquete, secuencia, disparador,

esquema, sinnimo, ndice.

Vista: agrupacin lgica de parte de una o ms tablas.


6/145

6

Esquema: conjunto de todos los objetos que pertenecen a un usuario.

3

InstalacindeOracle

Oracle est disponible en la pgina web, sin restricciones (primera versin, sin parches)

Hay que distinguir entre la aplicacin cliente y el servidor.

3 de las mltiples diferencias entre la versin Standardy 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):

StartOracle.bat

netstartOracleOraDb11g_home1TNSListener

netstartOracleServiceORCL

pause

StopOracle.bat

netstopOracleServiceORCL

netstopOracleOraDb11g_home1TNSListener

pause

Puntos a tener en cuenta:

nombre (SID): identificador de la base de datos en un servidor

Contraseas de los usuarios sysy system


7/145

7

4 Arquitecturadelabasededatos

Tres componentes principales:

Ficheros: de parmetros, de datos, de control, temporales y de redo log.

Estructuras de memoria: SGA (System Global Area)

Procesos

Dos trminos que causan confusin:

Database: una coleccin 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 ms de una instancia (conjunto de procesos y la SGA), aunque en la

mayora de los casos la relacin 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

tendramos distintos ficheros de configuracin, cada uno de los cuales abrira una de las bases de datos. Una instancia solo puede estar conectada

a una base de datos.


8/145

8

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 ejecucin de SQL, etc.

Si Oracle estuviese instalado en un servidor Unix podramos ver los procesos de fondo:

$/bin/ps

aef

|grep

ora

En Windows, solo vemos el servicio Oracle.exe. Este servicio tiene varios hilos, uno por proceso, aunque para poder verlos necesitamos alguna

herramienta especial.

Esquema de conexin a Oracle:


9/145

9

El modo de conexin ms habitual es el de servidor dedicado. En este modo, Oracle crea un proceso para cada sesin. Este proceso hace de

interfaz entre el cliente y la base de datos. Recibe las sentencias que le enviamos, select, updates, etc., efecta las operaciones y nos devuelve las

respuestas. El cliente "habla" directamente con el proceso dedicated servera travs de TCP/IP u otro mecanismo de comunicacin.

Existe otro modo de trabajo: el de servidor multi-hilo(multi-threaded servero MTS). Este modo se utiliza cuando el nmero de clientes

conectados es muy grande. Con el modo de servidor dedicado, si queremos tener 10.000 conexiones abiertas, Oracle necesitara 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. Estos dispatchersponen las

peticiones de los clientes en una cola. El primer servidor compartido que queda libre toma la peticin de la cola, la procesa y deja el resultado en

una cola de respuestas. El dispatcherest continuamente monitorizando la cola de respuestas para enviar los resultados a los clientes

correspondientes.


10/145

10

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:\>sqlplusCURSO/curso@ORCL

la aplicacin cliente leer el fichero tnsnames.oraque suele estar en el directorio [ORACLE_HOME]\network\adminpara averiguar la direccin del

servidor y el modo de conexin.

ORCL=

(DESCRIPTION=

(ADDRESS=(PROTOCOL

=TCP)(HOST

=localhost)(PORT

=1521))

(CONNECT_DATA=

(SERVER=DEDICATED)

(SERVICE_NAME=ORCL)

)

)

Con esta informacin, el cliente ya puede establecer una conexin con la direccin IP localhostpor el puerto 1521.


11/145

11

Si el servidor est bien configurado, debe haber un proceso llamado TNS Listenerque est a la escucha de nuevas peticiones de conexin.

Si la conexin solicitada es de servidor dedicado, el listenercrea un nuevo servidor dedicado (un proceso) y el cliente es redireccionado a estenuevo proceso. La conexin est establecida.

Si por el contrario, la conexin solicitada es de servidor compartido, el listener, que conoce los dispatchersque se estn 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 listenery se conecta al

dispatcherquedando la conexin establecida.


12/145

12

Una instancia de Oracle puede usar ambos tipos de conexiones simultneamente.

4.2 Ficheros

Ficheros que conforman la base de datos:

Ficheros de datos: contienen las tablas, ndices y otros segmentos.

Redo log files: ficheros en los que se van guardando las transacciones. Ficheros temporales: usados como almacenamiento temporal y espacio para realizar ordenaciones en disco.

Ficheros de control: indican la localizacin de los ficheros de datos.

El nico fichero necesario para levantar una instancia es el fichero de parmetros.


13/145

13

4.2.1 Ficherodeparmetros

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 parmetros sera

oraORCL.ora.

Suele estar en el directorio %ORACLE_HOME%/database.

En muchos casos se puede comprobar que el fichero de parmetros contiene slo una lnea que apunta a otro fichero:

IFILE='C:\oracle\product\10.2.0\db_1\database\oraORCL.ora'

SPFILE='C:\oracle\product\10.2.0\db_1\database\spfileORCL.ora'

Tambin podemos arrancar la base de datos manualmente especificando como parmetro del comando startupun fichero diferente:

SQL>startuppfile='C:\oracle\pfile\init.ora'

Ejemplo de fichero de parmetros

db_name="ORCL"

db_block_size=4096

control_files=("C:\oradata\control01.ctl","C:\oradata\control02.ctl")

nls_territory=spain

nls_language=spanish

El nmero de parmetros as como el nombre de estos vara entre las diferentes versiones de Oracle.

En las versiones ms recientes de Oracle, se utiliza una variante del fichero PFILEconocida como SPFILE.

Se debe llamar SPFILE.ora.


14/145

14

A diferencia del anterior, este fichero est en formato binario (no se puede editar con un editor de texto).

Podemos crear un fichero SPFILEa partir de un fichero PFILEcon el siguiente comando:

SQL>createspfilefrompfile='C:\oracle\pfile\init.ora';

y viceversa:

SQL>createpfile='oraORCL.ora'fromspfile='C:\oracle\product\10.2.0\db_1\database\spfileORCL.ora';

SQL>createpfilefromspfile;

Si no se especifican las rutas de los ficheros, se utiliza la ruta por defecto.

Si en el directorio %ORACLE_HOME%/databasehay un fichero de parmetros del tipo PFILE y otro del tipo SPFILE, Oracle arrancar con el SPFILE.

Una de las ventajas del nuevo formato es que los parmetros se pueden modificar mediante el comando alter system.

altersystemsetsessions=200scope=[SPFILE|MEMORY|BOTH]

Si el scope (mbito, alcance)es SPFILE, el nuevo parmetro se modifica en el fichero SPFILEy se activar al reiniciar la instancia.

Si es MEMORYse activa inmediatamente, sin modificar el fichero SPFILE.

Si la instancia se ha arrancado con un fichero PFILEsolo se podr usar la opcin MEMORYque sera la opcin por defecto.

Si la instancia se ha arrancado con un fichero SPFILEel valor por defecto es BOTH.

Otra ventaja de usar un fichero SPFILEes que Oracle puede almacenar en l parmetros de ajuste internos calculados automticamente.

Ejemplos: vamos a ver el contenido de nuestro SPFILE:

Nota: hay que conectarse como sysdbapara poder realizar esta operacin.


15/145

15

C:\>sqlplussys/sys@ORCLassysdba

SQL*Plus:

Release

11.2.0.1.0

Production

on

Jue

Sep

18

10:27:32

2014

Copyright(c)1982,2010,Oracle. Allrightsreserved.

Conectadoa:

OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0 64bitProduction

WiththePartitioningoption

SQL>create

pfile

from

spfile;

Archivocreado.

SQL>exit

DesconectadodeOracleDatabase11gEnterpriseEditionRelease11.2.0.1.0 64bit

ProductionWiththePartitioningoption

C:\>type

C:\oracle\product\11.2.0\dbhome_1\database\initORCL.ora

orcl.__db_cache_size=771751936

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__oracle_base='c:\oracle'#ORACLE_BASEsetfromenvironment

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\control01.ctl','D:\oracle\ORCL\control02.ctl'

*.db_block_size=8192


16/145

16

*.db_domain=''

*.db_name='ORCL'

*.diagnostic_dest='c:\oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=ORCLXDB)'

*.job_queue_processes=20

*.memory_target=1660944384

*.nls_language='SPANISH'

*.nls_territory='SPAIN'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'*.sec_case_sensitive_logon=FALSE

*.statistics_level='TYPICAL'

*.undo_tablespace='UNDOTBS1'

C:\>

Vamos a modificar el valor del parmetro sessions:

SQL>showparametersessions

NAME TYPE VALUE

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

sessions integer 248

shared_server_sessions integer

SQL>altersystemsetsessions=200scope=SPFILE;

Sistemamodificado.


17/145

17

SQL>showparametersessions

NAME

TYPE

VALUE

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

sessions integer 248

shared_server_sessions integer

SQL>createpfilefromspfile;

Archivocreado.

Podemos comprobar que el parmetro se ha establecido en el fichero SPFILEaunque todava no est activo.

Este parmetro en concreto no se puede modificar en memoria, por lo que si intentamos usar el scopeMEMORYo BOTHnos aparecer un mensaje

de error.

4.2.1.1 ParmetroDB_NAME

Es el nico parmetro obligatorio en el fichero de parmetros.

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 parmetros no coincide con el nombre de la base de datos, no se podr arrancar.

4.2.1.2

Parmetro

DB_BLOCK_SIZE

Es uno de los parmetros ms importantes de la base de datos.

Define la unidad mnima de lectura y escritura.


18/145

18

Se recomienda que sea mltiplo del tamao de bloque del sistema operativo.

El tamao que utiliza Oracle cuando se instala una base de datos suele ser correcto para la mayora de los casos.

Para averiguar el tamao de bloque del sistema operativo:

D:\Usuarios\aruiz>fsutilfsinfontfsinfoc:

LautilidadFSUTILrequiereprivilegiosadministrativos.

Ejecutamos la consola de comandos como administrador:

C:\Windows\system32>fsutilfsinfontfsinfoc:

NmerodeseriedevolumenNTFS: 0x32423d91423d5b35

Versin: 3.1

Nmerodesectores: 0x000000000b1f3658

Totaldeclsteres: 0x000000000163e6cb

Clsteresvacos: 0x00000000005b5114

Totaldeclsteresreservados: 0x0000000000000830

Bytesporsector: 512

Bytesporsectorfsico: 4096

Bytesporclster: 4096

Bytesporsegmentoderegistrodearchivo: 1024

Clsteresporsegmentoderegistrodearchivo:0

TamaovlidodedatosMFT: 0x0000000013d00000

LCNdeiniciodeMFT: 0x00000000000b65b2

LCNdeiniciodeMFT2: 0x00000000003f47e0

IniciodezonaMFT: 0x0000000000ff67c0

FindezonaMFT: 0x0000000001001f00

Id.deAdministradorderecursos(RM):E6171C6C6A4811E2AED1AB806DF76A1E

El tamao a usar depende del tipo de aplicacin:


19/145

19

Pequeo: 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 pequeas y el tamao del bloque es grande, como en cada

lectura se lee un bloque, estaramos leyendo muchas filas que quedaran en la cache y que nadie necesitara.

Por el contrario, en un sistema que hace un uso intensivo de consultas, caso de un sistema DSS, si el tamao de bloque es grande, se leern ms

filas con una sola operacin fsica de lectura, con lo que reducimos el nmero de lecturas necesarias, que es lo ms costoso.

En general, si los registros son grandes (con campos LOB) y las lecturas son secuenciales (full scan), es mejor usar un tamao de bloque mayor.

Si las filas son pequeas y hay muchos accesos aleatorios, es mejor usar un bloque pequeo.

As, por ejemplo, si tenemos un bloque de 8 kb y queremos leer una sola fila que ocupa 50 bytes, estaramos desperdiciando 8192 - 50 bytes de la

cache.

Con un bloque pequeo, se puede llegar a producir ms frecuentemente el efecto de filas encadenadas (row chaining,)si una fila no cabe en un

bloque.

El espacio se aprovecha mejor con bloques grandes, ya que la proporcin entre el espacio ocupado por la cabecera del bloque y el espacio para

los datos es menor.

4.2.2 Ficherosdedatos

Son los ficheros donde se almacena la informacin.

La jerarqua de almacenamiento de Oracle es la siguiente:

Una base de datos est compuesta por uno o ms tablespaces.

Un tablespaceest compuesto por uno o ms ficheros.

Las tablas, ndices, vistas materializadas, etc. se conocen como segmentos.


20/145

20

Los segmentosestn compuestos por una o ms extensiones, que son reas contiguas de almacenamiento en un fichero.

Una extensin es un conjunto contiguo de bloques.

Las extensionesque componen un segmento(una tabla p. ej.) no tienen por qu pertenecer a un mismo fichero, aunque si deben estaren el mismo tablespace.

Un bloquees la unidad mnima de lectura y escritura.

Un segmento, para crecer, necesita aadir 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 ms espacio en un tablespacepodemos bien aumentar el tamao de sus ficheros de datos, bien aadirle nuevos ficheros de datos.

Todos los bloquesde un tablespacetienen el mismo tamao y estn divididos en tres partes: cabecera, datos y espacio libre.


21/145

21

4.2.2.1 Casoprctico

CREATETABLESPACEINDX

DATAFILE'D:\ORACLE\ORADATA\SIPLAMA\INDX1.DBF'SIZE10GAUTOEXTENDONNEXT2GMAXSIZE32G;

ALTERTABLESPACEINDX

ADDDATAFILE'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF'SIZE4G;

ALTERDATABASEDATAFILE'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF'RESIZE10G;

ALTERDATABASEDATAFILE'D:\ORACLE\ORADATA\SIPLAMA\INDX2.DBF'AUTOEXTENDONNEXT2GMAXSIZE32G;

4.2.3 Diccionariodelsistema

Las tablas del diccionario del sistema siempre estn en el tablespaceSYSTEM.


22/145

22

Vamos a usar dos tablas del diccionario del sistema para ver informacin sobre los ficheros de datos:

select

df.tablespace_name,

df.file_name,

to_char(df.blocks,'999,999,999')asblocks,

to_char(df.bytes/1024/1024,'999,999,999')asMb,

to_char(fe.free_blocks,'999,999,999')asfree_blocks,

to_char(fe.free_bytes/1024/1024,'999,999,999')asfree_Mb

fromdba_data_filesdf,

(

selecttablespace_name,file_id,sum(blocks)asfree_blocks,sum(bytes)asfree_bytes

fromdba_free_spacegroupbytablespace_name,file_id

)fe

wheredf.file_id=fe.file_id(+)

orderbydf.tablespace_name,df.file_name;

4.2.4 Ficherostemporales

Oracle utiliza los ficheros temporales para almacenar resultados intermedios y realizar operaciones de ordenacin grandes que no caben enmemoria. Tambin se almacenan en estos ficheros temporales las tablas e ndices temporales.

Estos ficheros estn en un tablespaceespecial del tipo temporal.

4.2.5 Ficherosdecontrol

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 parmetros.

Podemos ver la lista fe ficheros de control a travs de la vista del sistema v$controlfile.


23/145

23

Contienen informacin como el nombre y fecha de creacin de la base de datos, nombre de los tablespaces, nombre y localizacin de los ficheros

de datos y de redo, nmero de secuencia del redo log en curso, informacin de checkpoint, informacin del archivado de los redo log, etc.

Para aadir 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, aadir la ruta de la copia en el parmetro control_filesdel fichero de parmetros init.orao spfiley 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 parmetro user_dump_dest.

SQL>select*fromv$parameterwherename='user_dump_dest';

Tambin podemos especificar la ruta en la que crear el fichero:

SQL>

alter

database

backup

controlfile

to

trace

as

'D:\Usuarios\aruiz\Documents\Temp\controlfile.txt';

o hacer una copia binaria del fichero en una ruta concreta:

alterdatabasebackupcontrolfileto'D:\Usuarios\aruiz\Documents\Temp\controlfile.bak';

La vista v$databasenos proporciona informacin sobre algunos de los parmetros almacenados en el fichero de control.

4.2.6

FicherosRedoLogComenzar explicando qu son las transacciones: Transacciones

Los ficheros redo logson 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 ms adelante si es necesario:


24/145

24

Si insertamos un registro en un tabla, el resultado de la insercin 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 modificacin que se realiza en el diccionario de datos.

En definitiva, en los ficheros redo logse almacena la informacin 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 ms adelante, tambin son una de las razones fundamentales de la eficiencia(velocidad) de Oracle.

En caso de un fallo de alimentacin elctrica, Oracle usar los redo log onlinepara restaurar los datos al mismo estado en que estaban antes deirse la luz.

Esta afirmacin necesita una explicacin. Si cada operacin que realizamos se escribe en los ficheros de datos no se debera perder informacin al irse

la luz.

Si un disco se rompe, Oracle puede usar los redo log archivadosjunto con los redo log onlinepara restaurar un backupdel disco al estado antes del

fallo.

Si un usuario borra una tabla accidentalmente, es posible restaurar un backupanterior y actualizar los datos hasta el momento anterior al

accidente.

qu son los redo log archivados? en qu se 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, despus 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?


25/145

25

Para entender cmo funcionan los ficheros de redo log onliney alguna de las afirmaciones hechas anteriormente necesitamos comprender algunos

conceptos importantes como saber qu es un checkpoint, que es el buffer o cache de datosy que hace el proceso Database Block Writer

(DBWn).

El buffer o cache de datoses un rea de memoria, ms 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 ms adelante. Las operaciones

ms 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 ms rpida. Cuando se modifica un registro, la modificacin se hace sobre el bloque que contiene el registro en el buffer de datosque

est en la memoria. El bloque del fichero de datos no se toca. La informacin necesaria para repetir la operacin tambin se guarda en otra rea de

la SGAconocida como redo log buffer. Cuando realizamos un commitpara hacer los datos permanentes (finalizar la transaccin), Oracle no

escribe los bloques modificados de la SGAa disco inmediatamente. Tan solo escribe el contenido del redo log bufferal 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 onlinepor si la base de

datos falla. Si despus de hacer commitla luz se va, el buffer de datosse 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 modificacin 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.


26/145

26

Es aqu cuando el proceso DBWnentra en escena. Es un proceso backgroundde Oracle responsable de hacer sitio en el buffer de datoscuando se

llena y de hacer los checkpoints. Un checkpointconsiste en la escritura de los bloques modificados (dirty blocks)del buffer de datosa disco. Hay

varias circunstancias que pueden provocar un checkpoint, siendo la ms 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 DBWcomienza a escribir a disco todos los

bloques modificados protegidos por el fichero de log 1. Hasta que el proceso DBWnno termina, Oracle no puede reutilizar el fichero de log 1. Sise intenta utilizar antes de que el proceso DBWnhaya terminado, se interrumpen todos los procesos hasta que el proceso DBWnfinaliza el

checkpointy se escribe en el fichero de trazas el indeseado mensaje "Checkpoint not complete" (el fichero de trazas contiene mensajes

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).


27/145

27

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 nmero suficiente y el tamao de los redo logs online necesario para evitar la reutilizacin de uno de ellos

antes de que finalice el checkpointque gener cuando se llen.

Si queremos, podemos forzar un checkpointmanual con la siguiente sentencia:

SQL>altersystemcheckpoint;

Por qu no definimos muchos ficheros de redo log o hacemos que sean muy grandes y as evitamos siempre el problema?

Cada tipo de aplicacin genera diferentes cantidades de redo log. Un sistema DSS(Decision Support System)genera significativamente menos redo

logque un sistema OLTP(Transaction Processing). Una aplicacin que gestiones muchas imgenes en campos BLOB(Binary Large Objects)genera

ms redo logque un sistema de pedidos. Un sistema con 100 usuarios generar una dcima parte del redo logque genere una aplicacin con 1.000

usuarios.

No existe un valor nico apropiado para el nmero ni el tamao 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 logque se van llenando, interesa usar muchos fichero pequeos para que la sincronizacin sea siempre cercana.

En una aplicacin en la que hay muchos usuarios modificando la misma informacin, interesa tener ficheros de redo loggrandes 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

recuperacin despus de una cada sea mayor.

Utilizar ficheros pequeos tambin puede disminuir la eficiencia de la base de datos al producirse un mayor nmero de checkpoints, pero por el

contrario, el tiempo de recuperacin en caso de fallo tambin ser menor.

Podemos ver la cantidad de redo generado (en bytes) (google: oracle statistics descriptions 11g)

selectn.name,s.value


28/145

28

fromv$mystats,v$statnamen

wheres.statistic#=n.statistic#

andn.namelike'redosize';

createtabletas

selectobject_id,object_name,created

fromall_objects

whererownumselectlog_modefromv$database;

Para activar el modoARCHIVELOGhay que aadir los siguientes parmetros al fichero de parmetros:

log_archive_start=true(elarchivadodelosficherosderedologesautomticoenlugardemanual)

log_archive_dest_1=C:\oracle\oradata\orcl\arch(hasta10rutasdiferentes)

log_archive_format=arch_%t_%s.arc(nombreconelquesecopianlosficheros)

Para cambiar el modo de funcionamiento la base de datos debe estar montada pero no abierta:


29/145

29

SQL>startupmount;

SQL>alterdatabasearchivelog;

SQL>alterdatabaseopen;

4.2.6.2 Otraventajadelusodeficherosderedolog

Adems de todo lo visto anteriormente, existe otro motivo por el que Oracle decidi usar los ficheros de redo log.

Los ficheros de redo logse escriben de forma secuencial, y la escritura secuencial es mucho ms rpida que la escritura aleatoria.

Cuando hacemos un commit, slo tenemos que esperar a que finalice la escritura secuencial del redo log bufferal fichero de redo log online

activopara seguir trabajando, operacin que es mucho ms rpida que la escritura aleatoria de los bloques modificados en los ficheros de datos.

Otro motivo, como ya hemos visto, es el de reducir el nmero 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 informacin por un fallo de la instancia.

4.2.6.3 Casoprctico

select*fromv$logfile;

select*fromv$log;

alterdatabasedroplogfilegroup1;

alterdatabaseaddlogfilegroup1'D:\ORACLE\ORADATA\SIPLAMA\REDO1.LOG'size100M;

alter

database

drop

logfile

group

2;


altersystemswitchlogfile;

alterdatabasedroplogfilegroup3;


30/145

30




4.2.7 Undo,bloqueosyconcurrencia

Como ya hemos visto, cuando Oracle modifica un registro, tambin aade los datos de la operacin realizada a los ficheros de redo log. Esta

informacin nos permite volver a repetir la operacin si fuese necesario.

Por otra parte, el registro modificado tambin se marca con el identificador de la transaccin que lo ha modificado.

Este identificador acta como un bloqueo de escritura sobre el registro. Si otro usuario intenta modificar este registro, Oracle comprobara que ya

est bloqueado por otra transaccin activa y no lo permitir.

Esta caracterstica ha permitido a Oracle implementar otra funcionalidad fundamental: el multi-versioning, que permite que Oracle nunca bloquee

la informacin en modo de lectura. Para entender este mecanismo hemos de ver primero que son los segmentos de rollbacko segmentos de

undo.

Adems de la informacin de redo, Oracle tambin guarda la informacin necesaria para poder deshacer la operacin en caso de que la

transaccin no finalice correctamente. Esta informacin se conoce como de undoy se guarda en los segmentos de rollbacko undo(rollback or

undo segments). En caso de que la transaccin falle, Oracle leer la imagen anterior de los segmentos de rollbacky restaurar la informacin.

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 nmero 20.000, un usuario modifica el registro nmero 25.000. Si seguimos recorriendo el cursor y al

llegar al registro nmero 25.000 el usuario todava no ha finalizado la transaccin, est claro que deberamos ver la informacin que haba antes de

la modificacin. Pero, qu pasa si ya ha hecho commity la transaccin ha finalizado?


31/145

31

Pues que tambin debera 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 modificacin (realmente el SCN: System Change Number) es posterior a la del

comienzo de la consulta, y en el caso de este registro, devolviendo la informacin que est almacenada en el segmento de undo.

Mediante este mecanismo, Oracle ha conseguido que las actualizaciones que estn realizando unos usuarios no afecten a las operaciones de

lectura que estn usando otros.

Cuando se hace un commito un rollback, la transaccin, que se almacena en los segmentos de rollbackse marca como activa o finalizada.


32/145

32


33/145

33

Finalizar explicacin transacciones: Transacciones

4.2.8 Transacciones

En teora, no sera 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 transaccin. Una base de datos nos

garantiza la integridad de los datos. Si una operacin requiere que se realicen modificaciones en dos ficheros (tablas), y el sistema se cae despus


34/145

34

de realizar la primera actualizacin, los datos podran quedar inconsistentes. Esto no ocurre usando una base de datos. La base de datos nos

garantiza la atomicidad de la operacin mediante el concepto de transaccin. La operacin se realiza completa o no se realiza, pero nunca se

queda a medias.

Otra propiedad importante de las transacciones es el aislamiento(isolation). Los cambios que est realizando una transaccin no finalizada no

deben ser visibles para el resto de transacciones en curso.

Existen dos comandos para controlar las transacciones: commity rollback.

Commitfinaliza una transaccin haciendo que los cambios sean permanentes.

Rollbacktambin finaliza una transaccin, pero deshaciendo todos los cambios que se hubiesen realizado desde la transaccin anterior.

En muchas bases de datos como Informix, Sybasey SQLServercada operacin es una transaccin en s misma. Si queremos que varias sentencias

formen parte de una misma transaccin es necesario comenzarla explcitamente. Oracle adopt el criterio opuesto. Todas las operaciones se

realizan en el mbito de una transaccin y no se hacen efectivas hasta que esta no se cierra.

Hace algunos aos (y an hoy con algunas bases de datos), si un usuario estaba modificando una tabla, otro usuario no poda leerla hasta quefinalizaba la operacin. Los registros que se estaban modificando o incluso la tabla entera se bloqueaban durante la transaccin. Adems, estos

bloqueos consuman muchos recursos. Esto motiv que muchos programadores intentaran que las transacciones fuesen lo ms cortas posibles.

As, por ejemplo, si queran actualizar todos los registros de una tabla con un milln de registros, podan escribir un procedimiento para

modificarlos de 1000 en mil. Este procedimiento, adems de ser ms complejo que una simple actualizacin, poda dar lugar a una inconsistencia

en los datos.

En Oracle, el tamao de una transaccin no es un problema. No se usa ms memoria ni se consumen ms recursos (por bloqueos) en una

transaccin grande que en una pequea. El tamao de las transacciones debe ser el que corresponda a cada operacin. Si queremos actualizar un

campo de una tabla con un milln de registros, lo correcto sera finalizar la transaccin cuando se hubiesen modificado todos los registros.

Enlace a Ficheros Redo Log


35/145

35

Realmente, las operaciones commitson casi inmediatas, independientemente del tamao de la transaccin. Cuando se realiza el commitla

informacin de redoy la de undoya se ha generado, y los bloques de datos se han modificado en la cache. Si la transaccin es grande, dicha

informacin se ha ido volcando a disco.

La parte ms costosa de una transaccin es la escritura del redo log buffera los ficheros de redo log onlinepor el proceso LGWR, aunque esto no

suele ser un problema ya que este proceso suele estar enviando esta informacin continuamente:

Cada 3 segundos

Si se ha llenado un tercio del buffer.

Cuando se realiza un commit

puede un select generar redo?

Al hacer un commit, se hace una limpieza rpida (total o parcial) de las transacciones asociadas a los registros modificados (realmente bloques)

que todava estn en la cache. No as de los registros que el proceso DBWRha volcado a los ficheros de datos y ya no estn en memoria (esta

situacin ocurre sobre todo en transacciones grandes). A este proceso se le llama commit cleanout. Posteriormente, otra sesin puede leer alguno

de estos bloques que tiene transacciones asociadas. Oracle comprueba si las transacciones ya han finalizado y limpia el bloque. Como estaoperacin no se hace como consecuencia de un commit, se le llama delayed block cleanout. Como se trata de una operacin que modifica los

bloques, se genera redo. Por este motivo, es posible que una sentencia selectpueda generar redo, especialmente despus de una actualizacin

grande.

Realizar el block cleanoutes conveniente para evitar que Oracle tenga que comprobar cada vez si el registro pertenece a una transaccin activa.

(*) En teora, no se podr limpiar la transaccin asociada a un registro si hay un cursor abierto antes del comienzo de la transaccin, aunque latransaccin ya haya finalizado.

El caso del rollbackes el opuesto del commit. El tiempo necesario para realizar un rollbackdepende directamente del volumen de datos

modificados. Oracle tiene que leer la informacin que haba antes del cambio de los segmentos de undopara revertir la operacin.

Conclusin: Oracle est optimizado para ser muy rpido haciendo commita costa de que los rollbacksean ms lentos y costosos.


36/145

36

Repaso de las acciones realizadas en un commit:

Antes de que se haga commit, ya se han realizado las siguientes acciones:

La informacin de undose ha generado en la SGA.

Los bloques se han modificado en la SGA.

Se ha generado la informacin de redode las dos acciones anteriores en la SGA. (ver Nota *)

Dependiendo del tamao de la transaccin y el tiempo que dure, parte de la informacin de los tres puntos anteriores puede que se

haya volcado a disco.

Se han activado los bloqueos correspondientes.

Despus del commit:

Se genera el System Change Number (SCN)de la transaccin.

El proceso LGWR escribe el resto del redo log buffera disco y anota tambin el SCN.

La transaccin se marca como finalizada (committed).

Se liberan los bloqueos.

Se hace un barrido rpido de los bloques modificados que an estn en memoria para borrar la informacin sobre transacciones

finalizadas que puedan contener (commit cleanout).

La accin ms costosa es la escritura del resto del redo log buffera disco, y esta accin siempre ser pequea porque Oracle ha ido realizando

la escritura de esta informacin de forma regular durante la transaccin. Aunque el proceso LGWR normalmente se ejecuta de forma asncrona,

esta ltima escritura se realiza de forma sncrona. Hasta que no finalice, Oracle no puede seguir trabajando.

Nota *: como hemos visto, en transacciones grandes puede ser que parte de la informacin ya se haya volcado a disco, aunque la transaccin no

haya an finalizado, por lo que la informacin de undotambin tiene que estar protegida por los ficheros de redo, para, en caso de cada, despus

de recuperar el ltimo estado, poder realizar el rollbackque no se pudo hacer por la cada.

Con la siguiente consulta podemos ver las transacciones activas y el nmero de bloques de undoque contienen:


37/145

37

selecta.sid,a.username,b.used_ublk

fromv$sessiona,v$transactionb

wherea.saddr=b.ses_addr;

Podemos averiguar el tiempo aproximado que le queda a un rollbackpara finalizar, viendo cuando se reduce el valor del campo USED_UBLKen un

intervalo de, digamos, 60 segundos.

4.2.9 Flashback

Mediante esta funcionalidad podemos consultar y recuperar datos pasados.

Utiliza la informacin de undoque generan las transacciones.

createtabletas

selectobject_id,object_name,created

fromall_objects

whereowner='DIMA'

andobject_type='TABLE'

orderbyobject_name;

select*fromtwhereobject_name='REPUESTOS_ET';

updatetsetobject_name='FOO'whereobject_name='REPUESTOS_ET';

select*fromtwhereobject_name='FOO';

select

t.*

fromtversionsbetweenscnminvalueandmaxvalue

whereobject_id=64508;

selectora_rowscn,scn_to_timestamp(ora_rowscn),t.*

fromt



38/145

38

select*

fromtasoftimestampto_timestamp('2014092217:30:30','YYYYMMDDHH24:MI:SS')


select*

fromtasoftimestamp(systimestamp interval'20'minute)


Incluso podemos buscar los registros eliminados o modificados recientemente:

deletefromtwhereobject_name='FOO';

commit;

select*

fromtasoftimestamp(systimestamp interval'10'minute)

minusselect*fromt;

El tiempo que se almacena depende del tamao del tablespacede undoy del parmetro undo_retention(en segundos).

El parmetro undo_retentiondebera ser superior al tiempo de ejecucin de la consulta ms larga.

select*fromv$parameterwherenamelike'%undo_%';

altersystemsetundo_retention=1800;

Para averiguar el tiempo que se estn guardando los segmentos de undo:selectbegin_time,end_time,undoblks,txncount,maxquerylen,tuned_undoretention

fromv$undostat

orderbyend_timedesc;

Cada registro contiene las estadsticas en periodos de 10 minutos:


39/145

39

undoblks: nmero total de bloques de undousados.

txncount: nmero de transacciones ejecutado en el periodo

maxquerylen: duracin en segundos de la consulta ms larga.

tuned_undoretention: tiempo de retencin de los datos de los bloques de undo

Tambin podemos investigar ms a fondo es estado de los segmentos de undo:

selectstatus,

round(sum_bytes/1024/1024)asMB,

round((sum_bytes

/

undo_size)

*

100)

as

PERC

from

(

selectstatus,sum(bytes)sum_bytes

fromdba_undo_extents

groupbystatus

),

(

selectsum(a.bytes)undo_sizefromdba_tablespacesc,

v$tablespaceb,

v$datafilea

wherec.contents='UNDO'

andc.status='ONLINE'

andb.name=c.tablespace_name

anda.ts#=b.ts#

);

Con esta consulta podemos ver la cantidad de undoen cada uno de los tres estados posibles:

ACTIVEEspacio usado por las transacciones activas y que es necesario para poder realizar un rollback.

El parmetro undo_retentionno se utiliza para este tipo de undo. Si no hubiese suficiente espacio en el tablaspace de undopara


40/145

40

guardar la informacin generada por la transaccin, se abortara la transaccin y se producira el error ORA30036unabletoextendsegmentinUndotablespace.

UNEXPIRED

Informacin generada por transacciones finalizadas y antigedad inferior al parmetro undo_retention.. Es necesario para laconsistencia de las lecturas y las funciones de flashback. Si no est toda la informacin necesaria para realizar una lecturaconsistente, aparecera el error ORA01555snapshottooold.

EXPIREDInformacin generada por transacciones finalizadas y antigedad superior al parmetro undo_retention. No es necesario para laconsistencia de las lecturas.

4.2.10Tamaodelabasededatos

select(a.data_size+b.temp_size+c.redo_size)/1024/1024/1024"GB",

free.bytes/1024/1024/1024"FreeGB"

from(selectsum(bytes)data_sizefromdba_data_files)a,

(selectnvl(sum(bytes),0)temp_sizefromdba_temp_files)b,

(selectsum(bytes)redo_sizefromv$log)c,

(selectsum(bytes)bytesfromdba_free_space)free;

4.3 Estructurasdememoria

Las estructuras bsicas de memoria de Oracle son la SGA(System Global Area)y la PGA(Program Global Area).


41/145

41

La SGAest compuesta por un grupo de estructuras de memoria compartida (componentes) que contienen datos e informacin de control de la

instancia.

La comparten todos los procesos, tanto los servidores dedicados (creados con cada sesin) como los procesos que funcionan en background.

Los componentes principales de la SGAson la cache de datos, los redo log buffersy el shared pool.

La PGAes una zona de memoria propia y exclusiva de cada proceso. Se conoce como PGAal conjunto de todas las PGAs.

En este rea se guarda informacin como los valores de las variables enlazadas (bind variables), el estado de ejecucin de la sentencia, cursores

abiertos y tambin 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 gestin automtica de la memoria de la PGA) es demasiado pequeo en relacin con el tamao de la

operacin, el tiempo de respuesta puede dispararse ya que las operaciones han de realizarse sobre segmentos temporales en disco.

4.3.1

Lacachededatos(databasebuffercache)

Es uno de los componentes principales de la SGA.

Contiene una copia en memoria de los ltimos bloques que se han ledo y de los bloques modificados que an no se han escrito en los ficheros de

datos.


42/145

42

Esta lista de bloques es dinmica. 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 haca 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 ms usados, eliminado los que se

hayan usado menos veces.

Qu ocurre con la lectura de tablas grandes (full scan)? podran ocupar todo el buffer y hacer que perdamos los segmentos ms usados?

No. Oracle utiliza dos mecanismos para que esto no ocurra. En primer lugar, nunca utiliza ms del 25% del buffer cuando hace un full scan. Si la

tabla es mayor que el 25% del tamao del buffer, los bloques ms recientes van borrando a los ms antiguos de la misma tabla. En segundo lugar,

si el tamao de la tabla es mayor que el 10% del tamao 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 sern los primeros en salir de la cache.

Este comportamiento podemos modificarlo a nivel de tabla mediante el keepy recycle pool, como veremos ms adelante.

Con la siguiente consulta podemos ver cmo de efectiva est siendo la cache de datos:

selectname,to_char(value/1024/1024,'999,999,999')asM_BLOCKS

fromv$sysstat

wherenamein

(

'physicalreadscache',

'consistentgetsfromcache',

'dbblockgetsfromcache'

);


43/145

43

Bufferhitratio=physicalreads/(consistentgetsfromcache+dbblockgetsfromcache)

Physical reads:los bloques de datos que Oracle lee de disco. Muy costosos.

Buffer gets y logical reads:nmero de veces que se solicita un bloque del buffer cache. Son prcticamente sinnimos. Si el bloque no est en el

buffer cache, la lectura lgica implica una lectura fsica (es uno de los campos de la vista v$sql_monitorque veremos ms adelante).

Consistent gets:son las lecturas de bloques consistentes con un instante determinado (o SCN).

Son las lecturas normales. La mayora de las veces, los bloques que estn en la cache son consistentes y no hay que hacer ninguna operacin

adicional para recuperar la informacin.

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 la

creacin del cursor, aunque otras sesiones modifiquen algunos de esos bloques antes de que hayan sido ledos. Si el bloque en la cache no es

consistente con el momento deseado, Oracle debe reconstruir el bloque con la informacin de los segmentos de rollbackdel tablespace de UNDO.

Si no tuvisemos suficientes segmentos de rollbackpara reconstruir el bloque, nos aparecera el error ORA1555"snapshottooold".

El nmero total de bloques ledos es la suma de Consistent Getsy DB Block gets.

DB block gets:cuando Oracle lee la versin 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 ratiode 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, ms efectiva est siendo la cache.

Estos mismos indicadores podemos verlos a nivel de sesin:

selectnvl(n.username,'ORACLEPROC')||'('||n.sid||')'usernamex,

n.program,


44/145

44

n.machine,

t.name,

to_char(s.value,'999,999,999,999')asvalue,

round((sysdate

n.logon_time)*24)

ashoras,

to_char(s.value/(sysdate n.logon_time)/24,'999,999,999,999')asvalue_per_hour

fromv$sesstats,v$statnamet,v$sessionn

wheres.statistic#=t.statistic#

andn.sid=s.sid

andt.namein

(

'physicalreadscache',

'consistentgetsfromcache',

'dbblockgetsfromcache'

)

ands.value>0

orderbys.valuedesc;

o a nivel de pool:

selectname,physical_reads,db_block_gets,consistent_gets,

1 (physical_reads/(db_block_gets+consistent_gets))"HitRatio"

fromv$buffer_pool_statistics;


45/145

45

Existe una vista que nos proporciona una idea de cmo podra variar este ratio en funcin del tamao de la cache.

selectsize_for_estimate,

to_char(buffers_for_estimate,'999,999,999')asbuffers_for_estimate,

estd_physical_read_factor,

to_char(estd_physical_reads,'999,999,999,999')asestd_physical_reads

fromv$db_cache_advice

wherename

='DEFAULT'

andblock_size=(selectvaluefromv$parameterwherename='db_block_size')

andadvice_status='ON';


46/145

46

Este resultado nos indica que aumentando el tamao de la cache de los 3505 Mb actuales a 6048 Mb, la lectura fsica de bloques se reducira un

50%.

Tener una cache muy grande no siempre hace que Oracle vaya ms rpido. Cierto tipo de operaciones que deben recorrer todos los bloques en

memoria podrian verse afectadas negativamente si el nmero de bloques en la cache fuese muy grande.


47/145

47

Esta consulta nos muestra los objetos con ms bloques en la cache:

selecto.owner,o.object_name,o.object_type,count(*)asnb

fromdba_objectso,v$bhb

whereo.data_object_id=b.objd

and

not

o.owner

like

'SYS%'

groupbyo.owner,o.object_name,o.object_type

havingcount(*)>=100

orderby4desc;


48/145

48

Vista para averiguar los objetos que ms tiempo han permanecido enla cache (hay que ejecutarla como sys):

withb1as

(

selectobj,

max(tch)

as

tch

fromx$bh

groupbyobj

orderby2desc

),

b2as

(


49/145

49

selectobj,tch

fromb1

wheretch>=10

)

selecto.owner,o.object_name,o.object_type,b2.tch

fromdba_objectso,b2

whereo.data_object_id=b2.obj

andnoto.ownerlike'SYS%'

orderby4desc;

Consulta para ver los objetos a los que se est accediendo ms frecuentemente:

selectobject_owner,object_name,count(*)

fromv$sql_plan

wherenotobject_ownerin('SYS','SYSTEM','SYSMAN','DBSNMP','APEX_030200','EXFSYS','MDSYS')

groupbyobject_owner,object_name

orderbycount(*)desc;


50/145

50

Oracle usa por defecto con un buffer de datos: DEFAULT, pero nos permite trabajar con dos ms, KEEPy RECYCLE.

Cuando se accede a un segmento grande (full scan o index range scan), los bloques pertenecientes a segmentos importantes que se usan ms a

menudo quizs no salgan del buffer, pero aquellos bloques pertenecientes a segmentos importantes que se hayan usado menos frecuentemente

es posible que s salgan. Para evitarlo Oracle nos permite definir dos buffers de datos adicionales:


51/145

51

KEEP pool: para mantener los bloques de los segmentos que sabemos que se van a usar muy frecuentemente y queremos que estn (casi) siempre

disponibles.

RECYCLE pool: para evitar que lecturas de segmentos grandes que sabemos que no se van a usar frecuentemente, eliminen a otros objetos deacceso ms frecuente.

El funcionamiento de estos dos buffers es el mismo que el del buffer por defecto, por lo que tambin salen los bloques menos usados para que

entren otros ms recientes.

Es muy fcil 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 parmetros de inicializacin buffer_pool_keepy

buffer_pool_recycle(se especifica el nmero de bloques).

Luego tenemos que asociar los segmentos a estos buffers.

CREATE

TABLE

EMP

(...)

STORAGE

(BUFFER_POOL

KEEP)

CACHE;

ALTERTABLESTORAGE(BUFFER_POOLKEEP|RECYCLE|DEFAULT);

CREATEINDEXSTORAGE(BUFFER_POOLKEEP|RECYCLE|DEFAULT);

CREATETABLESTORAGE(BUFFER_POOLKEEP|RECYCLE|DEFAULT);

ALTERINDEXREBUILDSTORAGE(BUFFER_POOLKEEP|RECYCLE|DEFAULT);

4.3.2 SharedPool

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 cdigo PL-SQL compilado.

Data dictionary: cache: almacena informacin del diccionario de datos.

Server result cache: almacena resultados de consultas (a partir de la versin 11g)

El d j d f i f i d l id d


52/145

52

El tamao de estas reas se ajusta de forma automtica en funcin de las necesidades.

La compilacin de las sentencias SQL es una tarea muy costosa (query plan), por lo que es importante que las que se ejecutan con mayor

frecuencia estn compiladas y disponibles en la library cache. No encontrar una sentencia en la cache es bastante ms 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). 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 an ms aumentado el tamao de la library cache.

Respecto a la server result cache, Oracle asigna un 0,25% del parmetro MEMORY_TARGETa este rea, que va aumentando hasta llegar al valor

mximo permitido, que est definido en el parmetro de inicializacin result_cache_max_size.

select*fromv$parameterwherenamelike'result_cache%';

El valor por defecto del parmetro result_cache_modees MANUAL, que indica que Oracle no almacena resultados a no ser que se especifique con

el hint/*+RESULT_CACHE

*/en la propia consulta. El otro valor posible es FORCE, que se puede activar a nivel de sesin.

El parmetro result_cache_max_resultindica el mximo 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 pequeo de informacin de la lectura de un gran nmero de

registros: agrupaciones, medias, etc., por ejemplo, si vamos a usar varias veces el nmero de movimientospor ao, suponiendo que la tabla

movimientostiene muchos registros.

4 3 3 Administracinde lamemoria


53/145

53

4.3.3 Administracindelamemoria

Oracle recomienda que usemos la gestin automtica de la memoria (Automatic Memory Management).

Con este modo, disponible a partir de Oracle 11g, el administrador tan slo establece el tamao total de la memoria para la instancia y Oracle se

encarga de repartir el espacio dinmicamente entre la SGA y la PGA.

Supongamos que queremos usar 5Gb de memoria con posibilidad de aumentar hasta 8Gb en el futuro:

altersystemsetmemory_max_target=8Gscope=spfile;

altersystemsetmemory_target=5Gscope=spfile;

altersystemsetpga_aggregate_target=0scope=spfile;

altersystemsetsga_target=0scope=spfile;

shutdownimmediate;

startup;

Al establecer los parmetrospga_aggregate_targety sga_targeta 0, dejamos el control total a Oracle.

Con estos parmetros, los tamaos de la SGAy la PGA(suma de las PGAs de todos los procesos) se ajustan automticamente. Si establecisemos

valores para sga_targetopga_aggregate_targetdistintos de 0, los valores se usaran como valores mnimos para las respectivas reas de

memoria.

El parmetro memory_targetse puede modificar dinmicamente, siempre que no supere el valor definido en el parmetro memory_max_target:

altersystemsetmemory_target=8G;

Tamao de las reas de memoria dinmicas:

select*fromv$memory_dynamic_components;

ltimas operaciones de redimensionamiento de memoria:

select*fromv$memory_resize_ops;

Informacin sobre la SGA y la PGA:


54/145

54

Informacin sobre la SGAy la PGA:

select*fromv$sgastat;

select*fromv$pgastat;

4.4 Procesos

selectpname,background

fromv$process

wherenotpnameisnull;

4.4.1

ProcessMonitorProcess(PMON)Es un monitorizador de procesos.

Si una sesin falla o se mata, este proceso es el encargado de iniciar un rollbackde las transacciones en curso y liberar los recursos que estaba

usando: memoria, bloqueos, etc.

Tambin 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 Systemmonitor(SMON)

Realiza la recuperacin de la instancia cuando se vuelve a iniciar despus de un fallo.

Tambin limpia los segmentos temporales que no estn en uso y desfragmenta el espacio libre en los ficheros.

4.4.3 DatabaseWriterProcess(DBWn)

Es el proceso encargado de escribir los bloques modificados (dirty blocks)de la cache a disco. Escribe los bloques que no se han usadorecientemente (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 mayora de los casos, en sistemas con varios procesadores que modifiquen muchos datos puede ser

conveniente tener varios procesos DBWn funcionando simultneamente.

Aunque Oracle propone el nmero de procesos de escritura durante la instalacin podemos modificar este valor con el parmetro


55/145

55

Aunque Oracle propone el nmero de procesos de escritura durante la instalacin, podemos modificar este valor con el parmetro

db_writer_processes.

Hay dos condiciones que hacen que los procesos DBWn escriban a disco:

Que un proceso no pueda encontrar bloques libres despus 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 bloques modificados respaldados por dicho fichero.

4.4.4 LogWriterProcess(LGWR)

Es el encargado de escribir las entradas del buffer de redoa los ficheros de redoen disco. Escribe todas las entradas nuevas desde que se realiz la

ltima escritura.

El buffer de redo es circular, es decir, las entradas que 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 an no se ha escrito

a disco. En este caso avisa al proceso LGWR para que lo haga, quedndose a la espera de que este finalice para continuar.

Cada vez que se hace commitde una transaccin, Oracle asigna un nmero secuencial a la transaccin (SCN: system change number). Este

nmero se almacena con las entradas de redo log generadas por la transaccin.

Con objeto de maximizar la eficiencia del proceso, si durante una escritura generada por un commitse producen varios commitms, estos se

ponen en cola y cuando la primera escritura finaliza, todas las dems se hacen a la vez.

4.4.5 Checkpoint Process (CKPT)


56/145

56

4.4.5 CheckpointProcess(CKPT)

Cuando los procesos DBWn han escrito los bloques modificados a disco, se produce un checkpoint.

Este proceso, que realiza el checkpoint, escribe el SCNen la cabecera de los ficheros de datos y en los ficheros de control.

Un checkpointfinalizado garantiza que todos los datos son correctos hasta el ltimo SCNescrito. Si hay que realizar una recuperacin, tan solo

hay que volver a aplicar los cambios almacenados en los redo log posteriores al ltimo SCNalmacenado (roll forward). Es posible que despus de

roll forwardhaya que realizar un rollbackpara deshacer las transacciones activas no finalizadas.

Oracle sabe si ha habido una cada y se necesita recuperacin si alguno de los SCNsde los ficheros de datos no coincide con el almacenado en el


57/145

57

y p g

fichero de control.

4.4.6

ArchiverProcesses(ARCn)

Es el proceso encargado de copiar los redo log onlinea los destinos especificados cuando la base est en modo ARCHIVELOG. Se lanza despus de

cada log switch. Puede haber hasta 10 de estos procesos y el proceso LGWR se encarga de lanzar ms instancias si el rendimiento de las que hay

activas no es suficiente. En el fichero alert.logqueda constancia de la creacin de nuevos procesos ARCn. Se puede establecer el nmero mximo

mediante el parmetro 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 presentarse en su fichero de trazas. El nombre de estos

ficheros contiene el nombre del proceso que los escribe.

Tambin existe un fichero de trazas genrico llamado alert.logen el que Oracle escribe cronolgicamente mensajes de advertencias y errores:

Errores internos, corrupcin de bloques, deadlocks.

Tareas administrativas realizadas como la creacin o eliminacin de tablespaces, paradas y arranques de la instancia, finalizacin desesiones, etc.

Errores en el refresco de vistas materializadas y otros.

Los ficheros de trazas estn en el directorio:

select*fromv$parameterwherename='background_dump_dest';

5 Tablas

5.1 IndexOrganizedTables(IOTs)

ej. lookup tables

CREATETABLEDIMA.L_UNIDADES


58/145

58

(

CD_USUARIO VARCHAR2(8BYTE) NOTNULL,

CD_UNIDAD VARCHAR2(8BYTE) NOTNULL,CONSTRAINTL_UNIDADES_PKPRIMARYKEY(CD_USUARIO,CD_UNIDAD)

)

ORGANIZATIONINDEX

TABLESPACEDIMA;

5.2 TemporaryTables

Se usan para guardar resultados temporales.

Los datos que se insertan en las tablas temporales solo son visibles para la sesin que los inserta, aunque se haga commit.

Generan mucho menos redo que las tablas normales.

createglobaltemporarytabletemp_table

(

)

oncommitpreserverows;

Aunque la creemos con la clusula oncommitpreserverows, al cerrar la sesin los datos desaparecern.

createglobaltemporarytabletemp_table

(

)

oncommitdeleterows;

se genera redo al trabajar con tablas temporales?

SQL>createglobaltemporarytablegttoncommitpreserverowsasselect*fromall_objectswhere1=0;

Tablacreada.


59/145

59

SQL>setautotraceonstatistics

SQL>insertintogttselect*fromall_objects;

66107filascreadas.

Estadsticas

2144 recursivecalls

5192 dbblockgets

43211 consistentgets

258 physicalreads

367432 redosize

854 bytessentviaSQL*Nettoclient

794 bytesreceivedviaSQL*Netfromclient

3 SQL*Netroundtripsto/fromclient

1088 sorts(memory)

0 sorts(disk)

66107 rowsprocessed

SQL>insert/*+append*/intogttselect*fromall_objects;

66107filascreadas.

Estadsticas

136 recursivecalls

953 dbblockgets

41219 consistentgets

0 physicalreads

68 redosize


60/145

60

839 bytessentviaSQL*Nettoclient

808 bytesreceivedviaSQL*Netfromclient

3 SQL*Netroundtripsto/fromclient1042 sorts(memory)

0 sorts(disk)

66107 rowsprocessed

SQL>setautotraceoff

Al usar la clusula /*+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 cachey 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 tamao de hasta 4Gb. (similares a los campos MEMOde Access)

Adems del segmento que se crea al crear una tabla, Oracle crea dos segmentos adicionales, LOB SEGMENTy LOB INDEX, por cada campo LOB que

contenga la tabla.

El motivo es que si los objetos son muy grandes es ms eficiente guardarlos en segmentos independientes, en lugar de meterlos en la fila dentro

del bloque. Adems, estos objetos grandes se almacenan en trozos (chunks). El tamao de estos chunkses configurable. De ah la necesidad del

segmento LOB INDEX.

Como veremos ahora, estos segmentos no tienen por qu estar en el mismo tablespaceque la tabla, lo cual puede simplificar las tareas

administrativas (backup, gestin del espacio, etc.)

Otro motivo fundamental es que los objetos LOB, por defecto, no se cacheanen el buffer cache.


61/145

61

create

table

t1

(c1

number,

c2

clob);

select*fromuser_segments;

Hasta que no insertamos un registro no se crean los segmentos:

insertintot1(c1,c2)values(1,null);

La definicin completa de la tabla podemos verla con la sentencia:

selectdbms_metadata.get_ddl('TABLE','T1')fromdual;

Se puede modificar la propiedad de cache:

ALTERTABLEtabnameMODIFYLOB(lobname)(CACHE);

ALTERTABLEtabnameMODIFYLOB(lobname)(CACHEREADS);

ALTER TABLE tabname MODIFY LOB (lobname) (NOCACHE); defecto


62/145

62

ALTERTABLEtabnameMODIFYLOB(lobname)(NOCACHE); defecto

Por defecto, si los objetos no ocupan ms de 4Kb, Oracle los guarda junto con la fila en el mismo bloque, lo que hace que el funcionamiento seams eficiente. Este comportamiento se puede modificar cambiando la clusula ENABLE STORAGE IN ROWpor DISABLE STORAGE IN ROWen el script de

creacin de la tabla, o posteriormente con la siguiente sentencia:

ALTERTABLEtestMOVE

TABLESPACEtbs1

LOB(lob1,lob2)

STOREAS(

TABLESPACEtbs2

DISABLESTORAGEINROW);

6 ndices

Los ndices contienen adems de los campos que los definen, un campo adicional llamado ROWID.

Este campo contiene la informacin del fichero de datos y el bloque dentro del fichero de datos que contiene el registro. Con esta informacinOracle puede acceder rpidamente a un registro. Es la forma ms rpida de acceder a un registro (aunque no necesariamente a varios).

Se deberan disear al mismo tiempo que las tablas, no luego sobre la marcha.

En general, no es buena idea aadir ndices a una tabla que ya est en produccin.

Mi apreciacin es que en muchos casos se utilizan demasiados ndices y adems, suelen estar mal diseados.

Ejemplo de lo que no se debera hacer (15 ndices en una misma tabla):

CREATETABLEDIMA.COSES1

(

ID_COSES1 NUMBER,

ANO VARCHAR2(4BYTE) NOTNULL,

CO TIP VARCHAR2(1 BYTE) NOT NULL


63/145

63

CO_TIP VARCHAR2(1BYTE) NOTNULL,

NOC VARCHAR2(13BYTE) NOTNULL,

NU_SERIE_ET VARCHAR2(15BYTE) NOTNULL,CO_UNIDAD_ASIGNADA VARCHAR2(8BYTE),

DS_UCO_ASIGNADA VARCHAR2(25BYTE),

DS_BRIGADA VARCHAR2(25BYTE),

DS_CABECERA VARCHAR2(40BYTE),

DS_VOCES_COLECTIVAS VARCHAR2(40BYTE),

DS_SERV VARCHAR2(50BYTE),

DS_NOMBRE_FAMILIA_CUF VARCHAR2(45BYTE),

DS_FAM_ACPLA VARCHAR2(100BYTE),

DS_CONTROL_VIDA1 VARCHAR2(40BYTE),



CA_CONTROL_VIDA1 NUMBER,



COS NUMBER,

CM NUMBER,

COD NUMBER,

COI NUMBER,

YOLD NUMBER,

COP NUMBER,

COC NUMBER,

ESCALON NUMBER NOTNULL,

A02P NUMBER,

A02C NUMBER,

ET VARCHAR2(2BYTE) DEFAULT'ET' NOTNULL

);

CREATEINDEXDIMA.COSES1_CO_TIP_INDXONDIMA.COSES1(CO_TIP);

CREATEINDEXDIMA.COSES1_CO_UNIDAD_ASIG_INDXONDIMA.COSES1(CO_UNIDAD_ASIGNADA);

CREATEINDEXDIMA.COSES1_DS_BRIGADA_INDXONDIMA.COSES1(DS_BRIGADA);

CREATE INDEX DIMA COSES1 DS CABECERA INDX ON DIMA COSES1 (DS CABECERA);


64/145

64

CREATEINDEXDIMA.COSES1_DS_CABECERA_INDXONDIMA.COSES1(DS_CABECERA);

CREATEINDEXDIMA.COSES1_DS_DS_CNTR_VIDA1_INDXONDIMA.COSES1(DS_CONTROL_VIDA1);

CREATE

INDEX

DIMA.COSES1_DS_DS_CNTR_VIDA2_INDX

ON

DIMA.COSES1

(DS_CONTROL_VIDA2);

CREATEINDEXDIMA.COSES1_DS_DS_CNTR_VIDA3_INDXONDIMA.COSES1(DS_CONTROL_VIDA3);

CREATEINDEXDIMA.COSES1_DS_FAM_ACPLA_INDXONDIMA.COSES1(DS_FAM_ACPLA);

CREATEINDEXDIMA.COSES1_DS_NOMBRE_FAM_CUF_INDXONDIMA.COSES1(DS_NOMBRE_FAMILIA_CUF);

CREATEINDEXDIMA.COSES1_DS_SERV_INDXONDIMA.COSES1(DS_SERV);

CREATEINDEXDIMA.COSES1_DS_UCO_ASIGNADA_INDXONDIMA.COSES1(DS_UCO_ASIGNADA);

CREATEINDEXDIMA.COSES1_DS_VOC_COLECTIV_INDXONDIMA.COSES1(DS_VOCES_COLECTIVAS);

CREATEINDEXDIMA.COSES1_ESC_INDX1ONDIMA.COSES1(ESCALON);

CREATEUNIQUEINDEXDIMA.COSES1_PKONDIMA.COSES1(ID_COSES1);

CREATEUNIQUEINDEXDIMA.COSES1_U01ONDIMA.COSES1(ANO,CO_TIP,NOC,NU_SERIE_ET,ESCALON);

CREATEINDEXDIMA.COSES1_YOLD_INDXONDIMA.COSES1(YOLD);

Consultas til para la consulta de ndices:

selecttable_name,count(*)

fromdba_indexes

wheretable_owner='DIMA'

groupbytable_name

orderby2desc;

selecttable_owner,table_name,index_name,column_name,column_position

fromdba_ind_columns

wheretable_owner='DIMA'

orderbytable_name,index_name,column_position;

Desventajas de definir muchos ndices y/o ndices con muchas columnas:

Las actualizaciones de las tablas son ms costosas. Por cada registro que se inserte, modifique o borre, hay que actualizar todos los ndices

de la tabla.

La eleccin del plan de ejecucin ser ms compleja, aumentando la probabilidad de que el optimizador elija uno que no sea ptimo.

Si un ndice contiene muchos campos, se necesitarn ms lecturas para leerlo y ocupar ms espacio en memoria, por lo que penalizar a

todos los usuarios


65/145

65

todos los usuarios.

Cuando se cargan o se eliminan grandes cantidades de registros de una tabla puede ser bastante ms eficiente deshabilitar algunos ndices yrecrearlos al finalizar la operacin:

ALTERINDEXUNUSABLE;

ALTERINDEXREBUILD;

Tipos de ndices:

B*Tree

Index Organized Tables (IOT)

Birmap

Bitmap Join

Function-based indexes

6.1 B*Tree


66/145

66

Los nodos finales (leaf nodes)que contienen la clave y el RowIddel registro estn enlazados por una doble lista enlazada que permite el barrido

de los registros secuencialmente (index rage scan).

Una de las propiedades de este tipo de rboles es que todos los nodos finales estn al mismo nivel, que se conoce como la profundidad (height)

del ndice. La mayora 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 mltiples inserciones e

eliminaciones este balanceo se puede ir deteriorando.


67/145

valor se aproxima al nmero de bloques de la tabla, significa que los datos en la tabla estn muy ordenados respecto al ndice. Sin embargo, si el

valor es cercano al nmero de registros, los datos estn muy desordenados y su uso sera poco eficiente.


68/145

68

valor es cercano al nmero de registros, los datos estn muy desordenados y su uso sera poco eficiente.

Es uno de los factores que el optimizador tiene en cuenta a la hora de decidir el plan de ejecucin 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

nmero de registros de una tabla.

Existe un modo rpido de lectura de ndices llamado fast full 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 ndicesBitmap

No estn disponibles en la versin Standard, solo en la Enterprise.

Son adecuados para columnas en las que hay pocos valores diferentes comparados con el nmero total de registros en la tabla.

Ejemplo del tipo de consulta en la que son muy eficientes.

selectcount(*)

fromT

wheregenero='H'

andprovinciain(1,10,30)


69/145

69

andgrupo_de_edad='41ysuperior';

Esta consulta sera muy poco eficiente si se utilizan ndices B*Tree, sobre todo si no conocemos a priori cules son las condiciones que se aplicarn.

Estn orientados a sistemas DSS (Decision Support Systems) y Data Warehouses, no sistemas OLTP (Online Transaction Processing).

Cuando se modifican los datos, Oracle necesita bloquear gran parte del ndice para poder recrearlo, lo que podra degradar el funcionamiento de

sistemas OLTP, en los que varios usuarios pueden estar modificando datos simultneamente.

6.3

ndices

Bitmap

Join

Permiten realizar una desnormalizacin, 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 cdigo del tipo de artculo, no nos queda ms remedio que hacer unjoin:

selectcount(*)

fromREPUESTOS_ETR,TIPOS_DE_ARTICULOST

whereR.ID_TIPOS_DE_ARTICULOS=T.ID_TIPOS_DE_ARTICULOS

d T CO TIPOS DE ARTICULOS 'G'


70/145

70

andT.CO_TIPOS_DE_ARTICULOS='G';

|Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |

| 0|SELECTSTATEMENT | | 1| 8| 5992 (1)|00:01:12|

| 1| SORTAGGREGATE | | 1| 8| | |

|* 2| HASHJOIN | |86228| 673K| 5992 (1)|00:01:12|

|* 3| TABLEACCESSFULL|TIPOS_DE_ARTICULOS| 1| 5| 3 (0)|00:00:01|

|* 4| TABLEACCESSFULL|REPUESTOS_ET | 517K| 1515K| 5988 (1)|00:01:12|

PredicateInformation(identifiedbyoperationid):

2 access("R"."ID_TIPOS_DE_ARTICULOS"="T"."ID_TIPOS_DE_ARTICULOS")

3 filter("T"."CO_TIPOS_DE_ARTICULOS"='G')

4 filter("R"."ID_TIPOS_DE_ARTICULOS"ISNOTNULL)

Oracle nos permite crear el siguiente ndice, con el que asociamos al ndice de una tabla un campo de otra tabla:

createbitmapindexREPUESTOS_ET_TIPO_ART_IDX

onREPUESTOS_ET(T.CO_TIPOS_DE_ARTICULOS)

fromREPUESTOS_ETR,TIPOS_DE_ARTICULOST

where

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 lnea 2 es un error del optimizador, que aade una condicin que en este caso es innecesaria y que obliga a Oracle a leer los registros que

cumplen la condicin principal.



71/145

71

|

0

|

SELECT

STATEMENT

|

|

1

|

3

|

4

(0)|

00:00:01

|

| 1| SORTAGGREGATE | | 1| 3| | |

|* 2| TABLEACCESSBYINDEXROWID|REPUESTOS_ET | 103K| 302K| 4 (0)|00:00:01|

| 3| BITMAPCONVERSIONTOROWIDS| | | | | |

|* 4| BITMAPINDEXSINGLEVALUE|REPUESTOS_ET_TIPO_ART_IDX| | | | |


2 filter("R"."ID_TIPOS_DE_ARTICULOS"ISNOTNULL)

4 access("R"."SYS_NC00089$"='G')

6.4 Functionbasedindexes

select*

fromUNIDADwhereUpper(DS_NOMBRE_CORTO)='GACAI/63';



|* 1| TABLEACCESSFULL|UNIDAD| 14| 2492| 13 (0)|00:00:01|


1 filter(UPPER("DS_NOMBRE_CORTO")='GACAI/63')

createindexUNIDAD_NOMBRE_CORTO_IDX

on UNIDAD(DS NOMBRE CORTO);


72/145

72

onUNIDAD(DS_NOMBRE_CORTO);



|* 1| TABLEACCESSFULL|UNIDAD| 14| 2492| 13 (0)|00:00:01|


1 filter(UPPER("DS_NOMBRE_CORTO")='GACAI/63')

dropindexUNIDAD_NOMBRE_CORTO_IDX;

createindexUNIDAD_NOMBRE_CORTO_IDX

onUNIDAD(Upper(DS_NOMBRE_CORTO));



| 1| TABLEACCESSBYINDEXROWID|UNIDAD | 14| 2492| 6 (0)|00:00:01|

|* 2| INDEXRANGESCAN |UNIDAD_NOMBRE_CORTO_IDX| 6| | 1 (0)|00:00:01|


2 access(UPPER("DS_NOMBRE_CORTO")='GACAI/63')

Importante: para poder usar funciones en este tipo de ndices es necesario marcarlas como DETERMINISTIC para indicar a Oracle que para un


73/145

73

Importante: para poder usar funciones en este tipo de ndices, es necesario marcarlas como DETERMINISTIC, para indicar a Oracle que para un

mismo parmetro la funcin siempre devolver el mismo resultado.

functionmi_funcion(parametroinvarchar2)returnvarchar2deterministicas

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 camposENVIADO y NUM_ENVIO porque habra muchos registros con los valores (N, NULL). Puesto que en los ndices B*Treeno se almacenan los

registros en los que todos los campos del ndice sean nulos, podemos definirlo del siguiente modo:

createuniqueindexPEDIDOS_IDX

onPEDIDOS(casewhenENVIADO='N'thenNULLend,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_expressionsnos permite consultar los ndices basados en funciones.

7 Vistasyvistasmaterializadas

Breve descripcin.

Cuando se utiliza una vista, Oracle sustituye la vista por su cdigo.

8 Sql*Loader

CREATEUSERCURSO

IDENTIFIEDBYcurso

DEFAULTTABLESPACEUSERS

TEMPORARY TABLESPACE TEMP


74/145

74

O S C

PROFILE

DEFAULT

ACCOUNTUNLOCK;

Comprobamos que no podemos iniciar una sesin.

GRANTCREATESESSIONTOCURSO;

Creamos la tabla para rellenar a partir de un fichero externo:

CREATETABLECURSO.LDR_TEST

(

CAMPO1 VARCHAR2(20)NOTNULL,

CAMPO2 VARCHAR2(12),

CAMPO3 INTEGERNOTNULL,

CAMPO4 NUMBER,

CAMPO5 DATE,

CAMPO6 DATE,

CAMPO7 VARCHAR2(2000)

);

El usuario no tiene permisos para crear una tabla.

GRANTCREATETABLETOCURSO;

El usuario todava no puede insertar registros en la tabla:

ORA01950:noexistenprivilegiosentablespace'USERS'

GRANTUNLIMITEDTABLESPACETOCURSO;

insertintoCURSO.LDR_TEST(CAMPO1,CAMPO2,CAMPO3,CAMPO4)

values('ABC','FOO',15,10.34);


75/145

75

Vamos a cargar el siguiente fichero:test.csv

uno;sdfg;dos;tres;cuatro;cinco;seis;siete

Prueba1;foo;texto1;115;616,33;04/10/2014;06/10/2014;Comentario1

Prueba2;;texto2;72;;;07/10/201410:15:30;"Comentario2"

Prueba3;;;101

Prueba4;;;225

Prueba

5

Prueba6;;;badnumber

Necesitamos un fichero de control de carga:

test.ctl

LOADDATA

TRUNCATE

INTOTABLELDR_TESTWHENCAMPO1'Prueba4'

FIELDSTERMINATEDBY';'

TRAILINGNULLCOLS

(

CAMPO1,

NO_IMPORTAFILLER,

CAMPO2"upper(:CAMPO2)",

CAMPO3INTEGEREXTERNAL,

CAMPO4DECIMALEXTERNALNULLIFCAMPO4=BLANKS,

CAMPO5DATE"dd/mm/yyyy",

CAMPO6"casewhenlength(:CAMPO6)


76/145

76

test.paruserid=CURSO/curso@ORCL

direct=true

silent=(FEEDBACK,HEADER,DISCARDS)

skip=1

control=test.ctl

data=test.csv

log=test.log

bad=test.bad

discard=test.dis

Ya podemos ejecutar el comando:

C:\>sqlldrparfile=test.par

El fichero test.discontendr los registros que no cumplen el formato especificado en el fichero de control. El nmero de registros rechazados se

puede limitar con el parmetro DISCARDMAX. Por defecto tiene el valor ALLpero se podra poner a 1.

El fichero test.badcontendr los registros que no se han podido insertar porque se ha producido algn error.

En el fichero test.logcontendr las estadsticas de la carga y el motivo de los errores si se han producido.

En el fichero de control podramos haber especificado APPENDen lugar de TRUNCATE.

9 Tablasexternas

Desde un usuario autorizado creamos un directorio de Oracle en el que estar el fichero que queremos leer como si fuese una tabla:

createorreplacedirectoryEXT_TBLas'C:\Users\Alex\Temp\SqlLoader';

Utilizaremos el mismo fichero test.csvusado en el captulo sobre Sql Loader.

El usuario CURSO no tiene acceso al directorio:


77/145

77

selectowner,directory_name,directory_path

fromall_directories;

Habilitamos al acceso del usuario CURSOal directorio EXT_TBLen modo lectura, y escritura porque Oracle generar ficheros de log.

grantread,writeondirectoryEXT_TBLtoCURSO;

CREATETABLETEST_EXT

(

CAMPO1 VARCHAR2(20BYTE),

NO_VALEVARCHAR(10BYTE),






CAMPO7 VARCHAR2(2000BYTE)

)

ORGANIZATIONEXTERNAL

(

TYPEORACLE_LOADER

DEFAULTDIRECTORYEXT_TBL

ACCESSPARAMETERS

(

RECORDSDELIMITEDBYNEWLINE

LOGFILE'test.log'

BADFILE'test.bad'

SKIP1

FIELDSTERMINATEDBY';'OPTIONALLYENCLOSEDBY'"'LDRTRIM

MISSINGFIELDVALUESARENULL

REJECTROWSWITHALLNULL

FIELDS