Tablespace Temporal y el error ORA-01652

30 Nov Tablespace Temporal y el error ORA-01652

Uno de los errores mas odiados por cualquier programador, es el ORA-01652, esto es por que muchos de ellos no alcanzan a comprender cual es la causa de este, para muchos programadores lo atribuyen a un error de la Base de datos y sencillamente piensan en añadir mas espacio al tablespace temporal, cuando en muchas de las ocasiones es causado por ellos mismos al tener operaciones de tipo SORT (GROUP BY, ORDER BY) que estan causando este error.

Para comprender un poco mas de que se trata este error, primero hay que comprender que es el tablespace Temporal (Temporary Tablespace).
El tablespace temporal contiene datos en transito que permanecen unicamente por la duracion de la sesion, esto significa que una vez que la sesion termina, estos datos dejan de existir. Un tablespace temporal ayuda a mejorar la concurrencia de multiples operaciones tipo SORT que no caben en la memoria.
Un tablespace temporal se usa para guardar lo siguiente

• Tablas temporales e indices temporales
• LOBs temporales
• Resultados temporales de tipo SORT
• Arboles B temporales

Los tablespaces temporales utilizan un tipo de segmento llamado segmento temporal, pero algo importante a saber es que dentro de un tablespace temporal, todas las operaciones tipo SORT comparten un solo segmento SORT. Un segmento SORT es creado por la primer sentencia que, despues de iniciar la base de datos utiliza el tablespace temporal para hacer una operacion tipo SORT y es liberado unicamente cuando la base de datos es apagada.
Ya que sabemos esto, tambien es importante saber que cualquier escritura a un segmento SORT no genera ni redo o undo, este se debe a que los datos que existen en el tablespace temporal no necesitan existir mas alla que para la sesion que los creo.

Una sentencia de SQL puede hacer multiples operaciones de tipo SORT y una sesion puede tener multiples sentencias de SQL activas a la vez, cada una con la potencia de tener multiples operaciones SORT a disco. Cuando los resultados de una operacion SORT a disco ya no son necesitados, los bloques en el segmento SORT son marcados como “no utilizados” y pueden ser usados por otra operacion tipo SORT.

Aqui es donde entra el error ORA-01652, ya que una operacion tipo SORT va a fallar con este error cuando no existen bloques “no utilizados” en el segmento SORT o cuando el tablespace temporal no tenga espacio suficiente para alojar un extent mas.

Una vez que suceda cualquiera de los dos casos anteriores, se va a registrar este error en el log de la base de datos, pero el verdadero problema viene en que, solamente se registra el error, mas no la sentencia que causo el error.

De aqui lo mas recomendable es tener un monitoreo activo, podemos crear en Grid control/database control una metrica con el query que tenemos abajo

select count(1) from (
select trunc(100*(u.tot/d.tot),2) PCTUSED from
     (select sum(u.blocks) tot from v$tempseg_usage u) u,
     (select sum(d.blocks) tot from dba_temp_files d) d )
where PCTUSED > 50;

Si PCTUSED es mayor a 50, nos va a permitir ingresar a la base de datos y poder detectar que sesion es la que esta haciendo una operacion de tipo SORT mayor y de ahi poder detectar la sentencia de SQL que esta consumiendo un segmento de tipo SORT mayor.

Este query de abajo no me pertenece, y no tomo credito por el, de la misma manera debe de ser revisado y ejecutado en un ambiente de prueba antes de ejecutarlo en produccion , me lo encontre en internet hace rato, pero es el que utilizo para hacer una busqueda de las sesiones y los Mbs de sort que estan usando

——–Temp Space Usage
column SID_SERIAL format a15
column OSUSER format a15
column USERNAME format a15
column MODULE format a10
column PROGRAM format a10
column TABLESPACE format a20
set pages 100 lines 130

SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops,T.segtype
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace,T.segtype
ORDER BY mb_used,sid_serial
/

Conclusion
Lo que si debemos de saber es que una vez que sucede el error ORA-01652 con un tablespace temporal, lo que tenemos que hacer es identificar que sesion es la que esta consumiendo mas SORT, si la sesion que estaba consumiendo mas SORT desaparece , no podremos saber que sentencia de SQL estaba consumiendo nuestra tablespace temporal, de ahi que lo mejor que debemos y tenemos que hacer es tener un monitoreo proactivo no reactivo ante esta situacion.