ora-00020: maximum number of processes (%s)

En esta nueva entrada vamos a ver cómo solucionar el error ORA-00020: maximum number of processes (%s) exceded, el cuál nos impide conectar a la Base de Datos. Hay un post muy bueno de Tanel Poder donde nos explica muy bien cómo conectar a la Base de Datos para incrementar los procesos “How to log on even when SYSDBA can’t do so?“.

Errores Obtenidos

ora-12520

Lo primero que llamó mi atención es que al intentar conectar a la Base de Datos conseguí el siguiente error:

ora-12520
ora-12520

ora-00020

Después de revisar el tnsnames, intentar entrar varias veces y seguir con el error ora-12520 intenté entrar como sysdba y conseguí el siguiente error:

ora-00020
ora-00020

Como hemos excedido el número máximo de procesos no podemos conectar ni como sysdba, para lo que yo pensaba que tendríamos alguna conexión reservada. Ante esto leí el post de Tanel Poder  citado anteriormente y vi que podría utilizar la siguiente opción -prelim para “conexiones preliminares” y saltarme ciertos pasos realizados al conectar a sql*plus. Estos pasos llevados a cabo cuando conectamos a sql*plus son los siguientes:

  1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
  2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
  3. The new process allocates process and session state objects and initializes new session structures in SGA

Con la opción -prelim sólo se llevan a cabo los pasos 1 y 2, con lo cuál pude acceder a la consola y reiniciar la Base de Datos, sin tener que tirar el servicio de Windows o matar el proceso directamente:

prelim
prelim

Una vez dentro pude ejecutar un shutdown abort (no me dejó realizar un shutdown immediate), salir y volver a iniciar la instancia:

shutdown
shutdown
connect
connect

Una vez conectados lo que hice fue incrementar el parámetro processes para que no me vuelva a ocurrir el mismo error. Posteriormente reinicié la instancia para que se llevaran a cabo los cambios e inicié la instancia de nuevo:

processes
processes

 

startup
startup

 

 

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

ORA-01555 Instantánea demasiado antigua (problemas con LOBS)

Los errores ORA-1555 pueden ocurrir cuando una consulta no puede retener (el suficiente tiempo necesario) los datos en el UNDO para construir una copia de los datos en el momento que la consulta comenzó, dado que si otra transacción modifica alguno de los datos leídos, debemos poder satisfacer lo que llamamos lectura consistente. Existe un parámetro UNDO_RETENTION que le dice al UNDO que mantenga los datos por ese periodo de tiempo (en segundos) y así garantiza las lecturas consistentes.

En esta entrada vamos a revisar en concreto el siguiente error:

ORA-01555: instantánea demasiado antigua: número de segmento de rollback  con nombre “” demasiado pequeño %j

 El problema parece venir de los LOBS, ya que el error no nos da ningún nombre de segmento.

Las columnas que contienen LOBS pueden permitir almacenar datos en la fila (INLINE LOBS) o no (OUT OF LINE LOBS).

Posibles pasos para solucionar estos problemas en LOBS

ORA-01555 en LOBS significa que no tenemos el (lob_id + versión) que esperamos, por lo tanto puede tener un problema de lectura consistente o una corrupción en el lob segment.

a) Chequear corrupción en LOBS

      Una de las principales razones de los ORA-01555 con LOBS es la corrupción. En caso de corrupción el formato de error será el siguiente:

      ORA-01555: snapshot too old: rollback segment number  with name “” too small
      ORA-22924: snapshot too old

Con la siguiente nota de soporte de Oracle podemos confirmar la corrupción en LOB SEGMENTS.

Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)

b.) Issues with Retention/Pctversion values

PCTVERSION and RETENTION

PCTVERSION afecta a la reclamación de viejas copias de datos LOB. Esto afecta a la disponibilidad de las lecturas consistentes. Si una session necesita usar una vieja version de un LOB y esta se ha sobreescrito (porque el PCTVERSION demasiado pequeño) entonces el usuario verá el siguiente error:

ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old

Alternativamente el parámetro RETENTION es usado para determiner cuanto tiempo (en segundos) el lob undo debería ser retenido. No se puede configurar el valor de RETENTION de forma explícita. La cantidad de tiempo de retención de LOB versions es determinado por el parámetro UNDO_RETENTION. El uso del parámetro RETENTION es soportado solamente en Automatic Undo Management mode.

Si el valor de PCTVERSION o RETENTION es configurado demasiado grande y si las queries en el lob segment son relativamente pequeñas, entonces gastaríamos una porción grande del segment en retener older lob versions. En caso de valores pequeños, ORA-01555 pueden aparecer.

En 11g podemos usar retenciónes específicas para cada LOB segment.

c.) More checks

Si BLOB/CLOB están NOCACHE, entonces chequear CACHE puede ayudar o no tiene efectos.

d.) Bugs

He revisado los posibles BUGS y he encontrado los siguientes:

d.1) BUG 5636728 – LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation

Versions confirmed as being affected – 10.2.0.3

d.2)  Bug 5212539 – LOB corruption for NOCACHE LOBs (ORA-1555/ORA-22924)

Versions confirmed as being affected

9.2.0.6

9.2.0.7

10.1.0.4

10.1.0.5

10.2.0.1

10.2.0.2

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Cómo Calcular Número Transacciones en Base de Datos

¿Qué es una transacción?

Una transacción es una unidad lógica de trabajo que comprende una o más sentencias SQL a cargo de un único usuario. De acuerdo con el estándar SQL ANSI / ISO, con los que es compatible Oracle, una transacción comienza con una primera instrucción SQL ejecutable del usuario. Una transacción termina cuando se realiza un COMMIT o ROLLBACK por parte del usuario de forma explícita.

Las transacciones son definidas por la aplicación, no por la Base de Datos. COMMIT y ROLLBACK es la forma en que la Base de Datos registra las transacciones, pero no como se definen.

Calcular transacciones en Base de Datos

  • SQL para calcular el número medio de transacciones por segundo desde que inicio la Base de Datos:

select round(sum(s.value / (86400 * (SYSDATE – startup_time))),3) “TPS” from v$sysstat s ,v$instance i where s.NAME in (‘user commits’,’transaction rollbacks’);

  •  Con la siguiente query podemos determinar los “commit y rollback de usuario” a una determinada hora y ejecutarlo más tarde para ver el incremento de transacciones:

select to_char(sysdate,’DD-MM-YYYY HH:MI:SS’), sum(value) sum from v$sysstat where name like ‘user commits’ or name like ‘user rollbacks’;

  • Podemos llevar a cabo el siguiente procedimiento para saber el número de sentencias select/delete/update/insert ejecutadas contra la Base de Datos:

Asegurar que la auditoría está activada

connect / as sysdba
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
shutdown immediate
startup

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Ejecutamos la sentencia para ver el resultado:

SELECT count(*), action_name FROM dba_audit_trail WHERE timestamp between <fecha_inicio> and <fecha_fin>;

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

10gR2: Ignorar mayúsculas, minúsculas y acentos

Una de las cuestiones que los desarrolladores muchas veces me hacen es la siguiente:

“Cómo habría que hacer para que esta sucesión aparezca ordenada alfabéticamente?
  • A
  • b
  • C
  • d
  • FrontPage
El orden es “A, C, FrontPage, b, d” cuando el esperado sería “A, b, C, d, FrontPage””
 

Crearé la siguiente tabla, con sus respectivos datos para reproducir la cuestión que nos afecta:

En lo primero que nos vamos a fijar es en los valores de las variables NLS_SORT y NLS_COMP para mi sesión:

Como vemos, el valor de NLS_SORT es SPANISH, con lo cual aplicará la ordenación alfabética de España:

Ahora vamos a comprobar que si le asginamos el valor BINARY a NLS_SORT, hará una ordenación en base al valor binario de los caracteres (y por tanto la ordenación alfabética NO es la que nosotros esperamos):

En el ejemplo anterior hemos jugado simplemente con mayúsculas y minúsculas, pero podemos ver el comportamiento con los acentos:

Vemos como dependiendo del valor de la variable NLS_SORT vamos a ignorar o no tanto las mayúsculas, minúsculas y acentos para la ordenación de la tabla.

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Oracle Instrumentation

Introducción

¿Qué significa instrumentar una aplicación? Cuando una aplicación se ha desarrollado correctamente, proporciona una parte de instrumentación, la cuál suele estar desactivada por defecto y tiene la posibilidad de activarse en caso de querer detectar qué está haciendo dicha aplicación, cómo lo hace, … (en Oracle disponemos de SQL Trace).

Es necesario instrumentar bien una aplicación para poder detectar problemas de rendimiento, qué operaciones realiza, cuánto tiempo llevan esas operaciones, …

Código de Aplicación

Como hemos comentado, toda aplicación debería ser instrumentada, pero la más importante decisión que debería llevar a cabo el arquitecto de la aplicación no es instrumentar, sino cómo instrumentar y siempre tomar esta decisión al comienzo del desarrollo de la nueva aplicación.

Una parte muy importante a la hora de monitorizar una aplicación por parte del DBA es que se haya instrumentado las llamadas a Base de Datos, ya que podremos trazar el uso de la aplicación de forma más sencilla y rápida. Sin esta parte de instrumentación, muchas veces no vamos a saber qué módulo o acción se está ejecutando con una determinada sesión para poder monitorizar y ver qué está haciendo. Esto se suele dar sobre todo cuando las conexiones a Base de Datos se realizan a través de un pool de conexiones, ya que la Base de Datos ignora qué usuario final está usando qué sesión.

Desde el lado de la Base de Datos tenemos los siguientes atributos para poder monitorizar una aplicación (client identifier, client information, module name, action name). Estos valores pueden ser obtenidos a través de la vista v$session. Hay otras vistas (v$sql) que también contienen los atributos module y action. En la vista v$sql tenemos que tener cuidado, ya que como sabemos hay sentencias compartidas por distintos usuarios, con lo cual puede que aparezcan con module y action del usuario con el que se compartió la sentencia y no el que la ejecutó a posteriori.

Vamos a poner un par de ejemplo de cómo podemos realizar la implementación en código PL/SQL y JDBC (también podríamos realizarlo con cualquier otro tipo de código).

PL/SQL

Para realizar la instrumentación en PL/SQL podemos utilizar el siguiente código:

BEGIN
 dbms_session.set_identifier(client_id=>'moises.espinosa.es');
 dbms_application_info.set_client_info(client_info=>'Linux x86_64');
 dbms_application_info.set_module(module_name=>'script.sql',
 action_name=>'test session intstumentation');
 END;
 /

Para recuperar los datos de la instrumentación podemos utilizar las siguientes sentencias:

SELECT sys_context('userenv','client_identifier') AS client_identifier,
 sys_context('userenv','client_info') AS client_info,
 sys_context('userenv','module') AS module_name,
 sys_context('userenv','action') AS action_name
 FROM dual;
SELECT client_identifier,
 client_info,
 module AS module_name,
 action AS action_name
 FROM v$session
 WHERE sid = sys_context('userenv','sid');

JDBC

Para configurar client identifier, module name, and action name usaremos el método setEndToEndMetrics dentro de la interface OracleConnection.

El siguiente ejemplo muestra como podemos utilizarlo:

metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX]; metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = “moises.espinosa.es”; metrics[OracleConnection.END_TO_END_MODULE_INDEX] = “SessionAttributes.java”; metrics[OracleConnection.END_TO_END_ACTION_INDEX] = “test session instrumentation”; ((OracleConnection)connection).setEndToEndMetrics(metrics, (short)0);

Conclusión

Con este tipo de trabajos demostramos lo importante que es trabajar juntos el equipo de desarrollo con los DBA desde el comienzo del desarrollo de la aplicación, pudiendo facilitarnos unos a otros el trabajo mediante aportaciones que nos permitirán desarrollar aplicaciones de mayor calidad.

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Expresiones Regulares (REGEXP)

A partir de la versión 10g, con la introducción de las llamadas funciones REGEXP (REGular EXPressions), Oracle nos ofrece una batería de sofisticadas y potentes herramientas nativas que permiten al desarrollador centralizar el procesamiento intensivo de cadenas de caracteres dentro de la base de datos Oracle y con los lenguajes SQL y PL/SQL.

¿Qué son las Expresiones Regulares?

Sin embargo, un buen manejo de las expresiones regulares es algo tan poderoso que vale la pena que nos tomemos un tiempo para aprender a utilizarlas. Además, saber manejar expresiones regulares nos servirá no solamente en el mundo de las bases de datos Oracle; las podremos usar también en otros lenguajes de programación como Perl, Java, .Net, Php y Unix Shell Scripting, entre otros.

Una expresión regular como una cadena de caracteres que definen un patrón de búsqueda. En una expresión regular encontramos literales y metacaracteres. Los literales se leen al pie de la letra. Los metacaracteres son caracteres que tienen un significado especial.

Tomemos por ejemplo la siguiente expresión regular:

[p|m]apa

Los metacaracteres son los corchetes y el símbolo pipe (|). El resto son literales.

Los corchetes agrupan a varios caracteres en un solo carácter.

El símbolo pipe indica un símbolo u otro; es decir la ‘p’ o la ‘m’.

Luego, la expresión regular:

[p|m]apa

Coincide con las cadenas:

papa
mapa

¿Para qué sirven las Expresiones Regulares?

En una base de datos Oracle existen diversos escenarios en que la implementación de expresiones regulares constituye una herramienta de gran utilidad:

  • Búsqueda de texto. Las expresiones regulares nos permiten hacer búsquedas complejas de cadenas de caracteres dentro de las columnas de nuestras tablas.
  • Formateo de datos. Podemos modificar los datos proyectados en las sentencias SELECT, invirtiendo palabras, agregando o quitando caracteres, etc.
  • Definición de constraints. A fin de forzar el contenido de una columna para que se ajuste a un formato determinado: casilla de correo, número telefónico, etc.
  • Manipulación de datos. Por ejemplo, regularizando datos en procesos de migración desde aplicaciones legacy y aplicando reglas de búsqueda y reemplazo (Search & Replace).
# Operador Descripción
1 () Considera a toda la expresión entre paréntesis como una unidad. La expresión puede ser una simple cadena de literales o una expresión compleja conteniendo otros metacaracteres.
2 […] Debe coincidir cada carácter de la lista entre corchetes. Ej. ma[pms]a encuentra (mapa, masa, mama) y no encuentra (mata)
3 [^…] No debe coincidir ningún caracter de la lista entre corchete. Ej. ma[^pms]a (mata, mala) y no encuentra (mapa)
4 […..] POSIX collation element.
5 [:…:] POSIX character class. Ver las dos siguientes tablas.
6 [=…=] POSIX character equivalence class. Ej. [=e=] representa e, é, è, ë, …
7 . El metacaracter punto coincide con cualquier carácter. Ej. ma.a (mapa,masa,mata,…)
8 ? Coincide con cero o una  ocurrencia de la subexpresión que le precede al asterisco. Ej. map?a (mapa, maa)
9 * Coincide con cero o más ocurrencias de la subexpresión que le precede al asterisco. Ej. map*a (mapa, mappa, mappppa, maa)
10 + El metacaracter suma coincide con una o más ocurrencias de la subexpresión que lo precede. Ej. ma+a (mapa, mapppa, mappppppa)
11 {n1} Empareja exactamente n1 ocurrencias de la subexpresión precedente. Ej. a{5} (aaaaa)
12 {n1,} Empareja al menos n1 ocurrencias de la subexpresión precedente. Ej. a{5,} (aaaaa, aaaaaa, aaaaaaa, …)
13 {n1,n2} Empareja entre n1 y n2 ocurrencias de la subexpresión precedente. Ej. a{3,5} (aaa, aaaa, aaaaa)
14 Precediendo a un metacaracter con el símbolo de escape, el metacaracter será interpretado como un literal. El doble carácter de escape (\) permite considerar al carácter de escape como literal. Ej. *hola* (*hola*) Ej. \hola (hola)
15 n Debe coincidir la n-ésima subexpresión que le precede.
16 | Logical OR. Ej. [v|b]aca (vaca, baca)
17 ^ Coincide con el principio de línea y el operador está representado con el carácter sombrerito (^). Ej. ^hola (hola, holamundo, …)
18 $ Coincide con el final de línea y el operador está representado con el carácter pesos ($). Ej. hola$ (hola, mundohola, …)

El item 5 son entradas para character classes, los cuales son independientes del character set y su descripción será mostrada en la tabla siguiente .

Character Class All Characters of type
[:alnum:] Representa un carácter alfanumérico. Incluye letras y números. Omite símbolos de puntuación.
[:alpha:] Incluye letras solamente.
[:blank:] Espacio en blanco.
[:cntrl:] Carácter de control.
[:digit:] Carácter Numérico.
[:graph:] Toda la combinación de character class [:punct:] , [:upper:], [:lower:], [:digit:]
[:lower:] Representa un carácter en minúscula.
[:print:] Representa un carácter que se puede imprimir.
[:punct:] Punctuation characters.
[:space:] Representa un espacio.
[:upper:] Representa un carácter en mayúscula.
[:xdigit:] Valid hexadecimal characters.

La siguiente tabla muestra Character Ranges.

Range All Characters of Type
[A-Z] All upercase alphabetic characters.
[a-z] All lowercase characters.
[0-9] All numeric digits.
[1-9] All numeric digits excluding zero.

Regular Expression Function

Para poder hacer uso de todo lo visto hasta ahora Oracle SQL incluye un grupo de nuevas  funciones y condiciones para poder manejar expresiones regulares a partir de la versión 10g :

Regular Expression Funciton Parámetros Descripción
REGEXP_SUBSTR (s1, pattern, p1, n1, m1)s1 – cadena de caracteresPattern – expresión regularp1 – numérico opcional (default 1)n1 – numérico opcional (default 1)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función para extraer de una cadena una subcadena de caracteres que coincidan con un patrón especificado en una expresión regular.
REGEXP_INSTR (s1, pattern, p1, n1, opt1, m1)s1 – cadena de caracteresPattern – expresión regularp1 – numérico opcional (default 1)n1 – numérico opcional (default 1)opt1 – numérico (0 o 1). Opcional (Default 0)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función que permite determinar la posición de inicio de un patrón específico en una cadena de caracteres.
REGEXP_REPLACE (s1, pattern, rep1, p1, o1, m1)s1 – cadena de caracteresPattern – expresión regularRep1 – cadena, opcional.(Default NULL)p1 – numérico opcional (default 1)o1 – numérico opcional (default 0)m1 – 1 o más parámetros (ver tabla siguiente). Opcional Función que permite hacer búsqueda y reemplazo en una cadena de caracteres utilizando expresiones regulares para la búsqueda.

La siguiente tabla muestra los valores posibles del parámetro m1 en las anteriores funciones:

Parameter Value Descripción
‘c’ utiliza una correspondencia sensible a mayúsculas/minúsculas (por defecto)
‘i’ utiliza una correspondencia no sensible a mayúsculas/minúsculas
‘n’ permite el operador de correspondencia con cualquier carácter
‘m’ trata la cadena de origen como varias líneas
‘x’ Ignora caracteres espacios en blanco

Junto con las regular expression functions tenemos una regular expression condition:

Regular Expression Condition Parámetros Descripción
REGEXP_LIKE (s1, pattern, m1)s1 – cadena de caracteresPattern – expresión regularm1 – 1 o más parámetros (ver tabla anterior). Opcional Condición que se puede utilizar en la cláusula WHERE de una sentencia SQL SELECT y que permite retornar aquellas filas que coinciden con el patrón especificado en una expresión regular.

Vamos a ver algunos ejemplos (pulsa sobre la imagen para verlo mejor):

reg_exp1

reg_exp2

Expresiones Regulares y CHECK Constraints

Se pueden utilizar expresiones regulares para filtrar los datos que se permiten entrar en un tabla mediante restricciones. El siguiente ejemplo muestra cómo podría ser configurado una columna para permitir sólo caracteres alfabéticos dentro de una columna VARCHAR2. Esto le denegará signos de puntuación, dígitos, elementos de separación, …

CREATE TABLE t1 (
c1 VARCHAR2(20), CHECK
(REGEXP_LIKE(c1, ‘^[[:alpha:]]+$’)));

INSERT INTO t1 VALUES (‘newuser’);
-> 1 row created.
INSERT INTO t1 VALUES (‘newuser1’);
-> ORA-02290: check constraint violated

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Join Method II

CHOOSING THE RIGHT JOIN METHOD

En muchas ocasiones dejamos al optimizador que decida que join utilizar. Sin embargo, en muchas ocasiones, ya sea por la mala elección del optimizador o por nuestros conocimientos de los datos nos permite una mejor toma de decisiones.

SORT-MERGE/HASH VERSUS NESTED LOOPS

Sort-merge join y hash join pueden ser considerados de la misma “familia” de joins (proporcionan un buen rendimiento bajo condiciones similares, mientras que los nested loops se adaptan a una categoría diferente de queries. Por lo tanto cuando determines que tipo de join usar, hay que plantearse si un nested loop es apropiada.

La decisión entre hash/sort-merge y nested loop debería estar basada en:

  • Necesidad de rendimiento vs necesidad de tiempo de respuesta. Nested Loop habitualmente ofrece mejor tiempo de respuesta, pero hash/sort-merge pueden ofrecer mejor rendimiento.
  • La proporción de tablas en el join. Un mayor subconjunto de filas procesadas puede ser más rápido con hash/sort-merge.
  • Disponibilidad de índices para soportar el join. Un nested loop es más efectivo cuando un índice puede ser usado en las tablas del join.
  • Memoria y CPU disponible para ordenaciones. Grandes ordenaciones pueden consumir una cantidad excesiva de recursos y puede ralentizar la ejecución.
  • Hash join podría conseguir grandes beneficios en ejecuciones paralelas y operaciones orientadas a partición. Aunque nested loop y sort-merge también pueden ser usadas en paralelización.

Table 1 Determining the Optimal Join Method

WHEN JOINING A TO B (IN THAT ORDER)  CONSIDER SORT-MERGE OR HASH JOIN?  CONSIDER NESTED LOOPS USING AN INDEX ON B?
Both A and B are small. Yes. Maybe, depending on the selectivity of the index and the size of the outer table.
Only selecting a small subset of rows from B (and B has an index). No. Performing a table scan of B will be cost-inefficient. Yes. The index will reduce the number of IOs on B.
Want the first row as quickly as possible. No. The first row won’t bereturned until both A and B are scanned, sorted, and merged or until the hash table has been built. Yes. Rows can be returned as soon as they are fetched using the index.
Want to get all rows as quickly as possible. Maybe. Nested loops might still get all rows before sort-merge or hash join if other conditions apply. Maybe. Nested loops might still get all rows before sort merge if other conditions apply.
Doing a full table scan of A and want to use parallel query. Yes. Yes. Nested loops can be resolved in parallel if the outer (first) table in the join is retrieved via a full table scan.
Memory is limited, especially PGA memory. Maybe not. Large sorts can have significant overhead, especially if memory for sorts is limited. Hash joins can also require more memory than nested loops. Yes. The nested loops join avoids sorting and is, therefore, less affected by memory limitations.

 SORT-MERGE VERSUS HASH JOINS
Sort-merge está disponible desde versiones antiguas de Oracle, mientras que el hash join es más reciente. Hash join puede ser empleado en muchos, pero no todos los caso donde puede ser empleado el sort-merge. Cuando ambos son directamente comparados, el hash join tiende a un mejor rendimiento que un sort-merge join.

Sin embargo, sort-merge join tiene una más amplia aplicabilidad. Hash join puede ser ejecutado sólo cuando la condición join es una condición de igualdad, y los sort-merge pueden ser utilizados para resolver condiciones de no igualdad.

El sort-merge requiere más memoria y para ejecutar eficientemente y más CPU para durante las ordenaciones.

Aunque el hash join habitualmente tiene un rendimiento superior que el sort-merge, si los datos de entrada ya están ordenados, las operaciones de ordenación serán menos costosas y el sort-merge será más efectivo.

Referencias: Oracle Performance Survival Guide (Guy Harrison)

Notas: La Table 1 he preferido no traducirla, ya que los conceptos pueden ser más sencillos de entender.

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Join Methods I

Esta es la primera de una serie de entradas en las que quiero hablar sobre los métodos Join de acceso a datos.  Independientemente de la categoría lógica de join, Oracle tiene tres tipos de algoritmos join: nested loops join, short-merge join y hash join. Cada join es óptimo en diferentes circunstancias.

NESTED LOOPS JOIN

Oracle realiza una búsqueda de la segunda tabla (o inner) por cada fila encontrada en la primera tabla (o outer). Sin un índice en la tabla inner, se necesitará escanear la inner table una vez por cada fila en la outer table. Esto puede provocar que nested table scan tenga un incremento exponencial en cuanto a sobrecarga y tiempo de ejecución dependiendo del crecimiento de la tabla.

nestedloop

El optimizador habitualmente seleccionará una operación nested loops sólo si hay un índice en la inner table. Si se debe acceder a la mayoría de los datos de la inner table un nested loop suele ser menos efectivo que un sort-merge o hash join.

SORT- MERGE JOIN

Cuando se ejecuta un merge-join, Oracle ordena cada tabla (o result set) por el valor de la columna join. Una vez ordenadas, los dos conjuntos de datos son fusionados.

sortmerge

En este tipo de join se suelen leer la mayoría de datos de las tablas o no hay disponible un acceso por índice en la inner table. El hash join (lo veremos más adelante) suele ser más efectivo en los casos donde pueden ser considerados ambos. Sin embargo hay casos donde podemos emplear sort-merge join y no hash join (non-equi join).

HASH JOIN

Cuando ejecutamos un hash join Oracle construye una hash table para una de las dos tablas comprendidas en el join. Esta hash table es utilizada para encontrar registros coincidentes en una forma algo similar a la utiliza en los nested loop con índices.

hashjoin

Como comentamos anteriormente el hash join es habitualmente más efectivo que sort-merge join en circunstancias en las que ambos son aplicables y pudiera ser más efectivo que nested loop donde una gran proporción de registros de la tabla son incluidos.

Referencias: Oracle Performance Survival Guide (Guy Harrison)

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Oracle Processes (I) – Server Processes

Cada proceso Oracle ejecuta una tarea, para la cual ocupará un trozo de memoria interna (memoria PGA). Una instancia de Oracle tiene tres clases de procesos:

  • Server processes: estos procesos ejecutan tareas basadas en peticiones de clientes. Podemos clasificarlos en dedicated y shared servers. Estos son el tipo de procesos que veremos en esta entrada.
  • Background processes: se inician con la Base de Datos y ejecutan varias tareas de mantenimiento, como escrituras de bloques a disco, mantenimiento de online redo logs, …
  • Slave processes: similares a los background processes, ejecutan trabajo extra de cada background o server process.

Un proceso en sistemas como Windows donde Oracle se implementa con threads es equivalente a un thread. En sistemas operativos multiproceso como UNIX, el término proceso es totalmente apropiado.

Server Processes

Podemos tener las siguientes configuraciones:

  • Dedicated server: existe un proceso dedicado en el servidor para cada conexión. Hay una relación uno-a-uno entre una conexión a la base de datos y un proceso servidor o thread.
  • Shared server: Varias sesiones comparten un pool de procesos servidor. Las conexiones van a un dispatcher de la base de datos, no a un proceso de servidor dedicado creado para tú conexión.

Nota: Es importante distinguir entre una conexión y una sesión en Oracle. Una conexión es simplemente una ruta física entre un proceso cliente y una instancia de Oracle (por ejemplo conexión de red entre tú y la instancia). Una sesión es una entidad lógica en la base de datos, donde un proceso cliente puede ejecutar SQL y más. Varias sesiones independientes pueden ser asociadas con una conexión, y estas sesiones pueden existir independientemente de una conexión. Más adelante entraremos en detalle.

Dedicated Server Connections

Como comentamos anteriormente hay una relación uno-a-uno entre una conexión a la base de datos y un proceso servidor o thread. Si tienes 150 dedicated server connections en una máquina UNIX, habrá 150 procesos.

Description of Figure 4-1 follows

Shared Server Connections

En este tipo de arquitecturas la aplicación cliente conecta a el Oracle TNS listener y será redireccionado o manejado a un dispatcher, por lo cual no se puede usar shared server sin usar Oracle TNS listener. El dispatcher actúa de conductor entre la aplicación cliente y el shared server process.

Description of Figure 4-2 follows

Database Resident Connection Pooling (DRCP)

Este es un opcional y nuevo método de conectar a la base de datos y establecer una sesión (11g). Esta característica es útil en aplicaciones que no son desarrolladas como multithreaded (por ejemplo, aplicaciones PHP en un entorno de servidor Web Apache).

Para más información sobre este método de conexión se puede revisar el siguiente white paper de oracle:

http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf

Conexiones vs. Sesiones

Como comentamos anteriormente no es lo mismo una conexión que una sesión. Una conexión puede tener cero, una o más sesiones establecidas en ella. Cada sesión es separable e independiente, un commit en una sesión no afecta a otra sesión dentro de la misma conexión. Una sesión puede o no tener una conexión.

Vamos a ver un ejemplo dónde separamos conexiones y sesiones:

Como vemos en la imagen tenemos una sesión: una simple dedicated server-connection session. La columna PADDR es la dirección del proceso servidor.

Vemos que ahora tenemos dos sesiones que utilizan el mismo dedicated server process, ya que tienen el mismo PADDR. Una de las sesiones es la del AUTOTRACE, de hecho si ponemos off el AUTOTRACE veremos cómo cerrará su sesión. Por lo tanto hemos estado utilizando una conexión con dos sesiones.

Ahora vamos a ver una conexión sin ninguna sesión, para lo cual desconectamos del mismo SQL*Plus (Por lo tanto tenemos una conexión sin sesiones asociadas:)

Podemos ver que no tenemos sesiones, pero seguimos teniendo un proceso, una conexión física con el mismo PADDR:

¿Qué ocurriría si nosotros usamos shared server? Para ello debemos configurar shared server, esto lo podremos ver en otra entrada del blog, pero con shared server tanto el PADDR como el program cambiarían.

Dedicated Server vs. Shared Server vs. DRCP

Cuando usar Dedicated Server

Como vimos anteriormente es un mapeo uno-a-uno entre conexión cliente y proceso servidor. Es el único modo a considerar en entornos no-OLTP. Este modo es altamente recomendado si tienes recursos suficientes para servir el número de dedicated server processes.

Cuando usar Shared Server

Relación muchos-a-uno: muchos clientes a un shared server. En shared server se comparten recursos, con lo cual hay que tener especial cuidado de no monopolizar recursos por un largo periodo de tiempo. Los shared server son sólo apropiados para sistemas OLTP caracterizados por transacciones cortas y frecuentes.

Potenciales beneficios de shared server: reduce el número de procesos/threads del sistema operativo  reduce la memoria necesaria del sistema.

Conclusión: salvo que tu sistema esté sobrecargado o necesites utilizar shared server por alguna característica específica, la mejor opción será probablemente usar dedicated server.

Referencias: Expert Oracle Database Architecture (Thomas Kyte),Oracle® Database Administrator’s Guide 10g Release 2

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Indexing Guidelines

Para cuando nos preguntemos en qué casos debería crear un índice, por qué debería crearlo o por qué no, presentaré un pequeño checklist que nos puede ayudar a decidirnos que hacer:

Guideline Razón
Crear tantos índices como necesites, pero intentando mantener un número mínimo. Añade índices de forma juiciosa. Comprueba primero para tener medidas por las que guiarte. Los índices incrementan el performance, pero también consumen espacio en disco y recursos (CPU, memoria, I/O). No añadas índices innecesariamente.
El performance requerido de una query sobre una tabla debe ser la base de la estrategia de indexaxión. Indexar columnas usadas en queries SQL ayudarán al performance de dicha query.
Considerar usar SQL Tuning Advisor o SQL Access Advisor para recomendaciones de índices. Estas herramientas ofrecen recomendaciones y una importante ayuda en tu decisión de indexación.
Crea PK (Primary Key) constraints para todas las tablas. Esto automáticamente creará un índice B-Tree (si las columnas en la PK no están previamente indexadas).
Crea unique key constraints donde sea apropiado. Esto automáticamente creará un índice B-Tree (si las columnas en la PK no están previamente indexadas).
Crea índices en las columnas que tengan una FK (Foreign Key). Las columnas con FK son habitualmente incluidas en cláusulas WHERE con JOIN de tablas y esto mejorará el performance de sentencias SQL SELECT. Creando un índice B-Tree en la columna FK también reducirá bloqueos cuando actualices e insertes en las tablas hijas.
Seleccionar con cuidado y probar los índices en tablas pequeñas. Incluso en tablas pequeñas un índice puede mejorar el performance de la SQL.
Uso correcto del tipo de índices. Seleccionar el tipo de índice correcto para cada caso puede mejorar el performance.
Usar un índice B-Tree si no se tiene un aumento de performance verificado con un tipo diferente de índice. Los índices B-Tree son adecuados para muchas aplicaciones donde se tiene una alta cardinalidad en los valores de columnas.
Considerar usar índices Bitmap en entornos Data Warehouse. Este tipo de índices son ideales para columnas con baja cardinalidad donde los valores no son actualizados a menudo. Índices Bitmap trabajan bien en columnas con FK donde tú a menudo ejecutas queries con condiciones JOIN usando AND y OR.
Considerar usar tablespace separados para índices (separarlos de las tablas) Los datos de tablas e índices deben tener diferente almacenamiento. Usando diferentes tablespace la administración de los índices se hará de forma separada a la de tablas.
Que el índice herede sus propiedades de almacenamiento desde el tablespace. Esto hará más sencillo de administrar el índice.
Uso de nombres estándar y consistentes. Esto hará el mantenimiento y la resolución de problemas más sencilla.
No haga rebuild de índices a menos que tengas una sólida razón para ello. Realizar un rebuild de índice es habitualmente innecesario salvo que un índice esté corrupto o tú quieras mover el índice a diferente tablespace.
Monitorizar los índices y borrar los que no se utilizan. Esta operación libera espacio y mejora el performance de las operaciones DML.
Antes de borrar un índice, considera marcarlo como unusable o invisible. Esto permitirá determinar la mejora si el no hay pérdida de rendimiento al borrar el índice. Podrás volver a rehacer o realizar rebuild del índice sin requerir ejecutar sentencia DDL para la creación del índice.

Referencias: “Expert Indexing in Oracle Database 11g”

Comparte si te ha gustado ...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn