Con este espacio pretendo crear un rincón de información relacionda con las bases de datos Oracle, sus funcionalidades, características, noticias.... etc. Interaré hacer pequeños artículos con ejemplos demostrativos para que resulte más fácil leerlos. No dudéis en mandar vuestros comentarios, críticas, aclaraciones... etc.

Información del Autor

Llevo más de diez años trabajando como DBA Oracle en diferentes compañías y desde el 2004 lo hago de forma freelance a través de una sociedad creada por mí, Infor Consult Soluciones (http://www.inforconsult.es). Si tienes cualquier duda, comentario o propuesta que hacerme, no lo dudes.

viernes, 23 de noviembre de 2007

Gestión automática de la memoria PGA

¿Qué es la PGA?
Las siglas provienen de Program/Private Global Area, y es la memoria privada de cada proceso servidor. En esta memoria cada proceso almacena información que sólo es necesaria para su propio funcionamiento como por ejemplo sus variables globales, el estado actual de cada cursor (SQL) que se ejecuta... etc.

La PGA se compone de:
  • Área SQL privada: cada SQL que se ejecuta necesita de este espacio para poder llevar el control de las operaciones propias de la sentencia. Se asigna cuando se abre el cursor y se libera completamente cuando se cierra. Esta parte de memoria se subdivide en dos: a) area persistente: perdura durante toda la vida del cursor. Guarda las bind variables además de otras cosas; b) area en tiempo de ejecución: se libera cuando finaliza la ejecución de la sentencia SQL (aunque no se haya cerrado el cursor ). Constituyen las áreas de trabajo (working areas) que se explican más adelante. El número máximo de cursores, y por tanto, el número máximo de áreas SQL privadas, que un usuario puede tener abiertos al mismo tiempo se controla con el parámetro OPEN_CURSORS. También hay que tener en cuenta que esta área SQL privada se almacena en la PGA si la Instancia está configurada como servidores dedicados (dedicated servers). En caso de servidors compartidos (shared servers) se almacena en la SGA.
  • Memoria de las sesiones: guarda información relativa a la sesión como el login, variables de sesión... etc. En servidores compartidos (shared servers) este área pasa a ser pública ya que diferentes usuarios comparten los mismos procesos servidores.
¿Qué son las áreas de trabajo (working areas)?
Las área de trabajo (working areas) son el espacio usado por el proceso servidor para realizar ordenaciones y clasificaciones como los ORDER BY, GROUP BY o HASH JOIN. Esta área es la de mayor tamaño y forma parte del área en tiempo de ejecución.

Su tamaño es determinante para obtener buenos tiempos de respuesta en operaciones de este tipo, ya que si el espacio se queda pequeño para realizar estas tareas es necesario liberar espacio usando el tablespace temporal del usuario. Ésta actividad de swapping afecta drásticamente al rendimiento y recibe el nombre de "extra pass".

¿Dónde se almacena la PGA?
La PGA se almacena en la memoria del servidor pero fuera de la SGA. Por tanto, para dimensionar ambas áreas hay que tener en cuenta que la suma de las dos no supere los límites específicos del servidor y la plataforma (tamaño máximo memoria RAM, 2 GB en Windows sin /3GB, 3 GB en Windows con /3GB... etc.).

¿El tamaño de la PGA es configurable?
Sí, pero únicamente es configurable el tamaño de las áreas de trabajo. Desde Oracle9i el DBA puede fijar el tamaño máximo del cónjunto de todas las PGAs de la Instancia a través del parámetro PGA_AGGREGATE_TARGET. De esta forma, Oracle dinámicamente gestiona el tamaño de cada área de trabajo para que la suma de todas las PGAs (no de todas las áreas, que es un parte de la PGA como ya se ha indicado) no exceda del límite impuesto. En versiones anteriores la forma de controlar el tamaño de cada área de trabajo era a través de los parámetros SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE y CREATE_BITMAP_AREA_SIZE.

Para que la Instancia tenga el cuenta el parámetro PGA_AGGREGATE_TARGET, el parámetro WORKAREA_SIZE_POLICY se debe asignar a AUTO (por omisión). Con MANUAL, se indica que se trabaja de la forma anterior y se fija cada parámetro *_AREA_SIZE por separado.

¿Cuándo se crea la PGA?
La crea el Listener durante la creación del proceso servidor. Si esta memoria no puede ser asignada o reservada la creación del proceso servidor falla y el Listener devuelve un ORA-12500: TNS:listener failed to start a dedicated server process. Igualmente, si durante la ejecución de alguna sentencia es necesario incrementar el tamaño de la PGA y el sistema operativo no tiene más memoria para darle se devuelve un error indicándolo.

¿Cuándo se destruye la PGA?
Al cerrar el proceso servidor su PGA asociada se libera y se devuelve al sistema operativo el espacio reservado. El proceso servidor se cierra cuando el usuario ejecuta EXIT o DISCONNECT, lanza un Ctrl+C, se pierde la comunicación con el servidor, se produce un ORA-600 que desconecta la sesión, el DBA lanza un KILL SESSION, etc.

¿Cuánto ocupa cada PGA?
En cada versión de Oracle, e incluso en cada release, el tamaño mínimo cambia. No obstante, como el tamaño es cambiante en función del área de trabajo, lo mejor que se puede hacer para comprobar cuánto ocupa o cómo ocupa es monitorizarlas.

¿Cómo se monitoriza la PGA?
Hay diferentes vistas para saber cuánto ocupa cada PGA individual o para saber cuánto ocupan todas las PGAs o incluso para saber si está bien configurado el parámetro PGA_AGGREGGATE_TARGET.

Con V$PROCESS se puede saber el tamaño de la PGA de cada proceso de la Instancia (el proceso puede ser background o foreground -también llamado shadow-). Si es un proceso background se sabé rápidamente qué proceso es porque lleva el nombre del proceso en la columna PROGRAM. Si es un proceso shadow se sabe el usuario que hay detrás cruzando las columnas ADDR.V$PROCESS con PADDR.V$SESSION. Veamos un ejemplo:

SQL> select sid, serial#, username, command, status, machine from v$session where paddr='BE103FA4';

SID SERIAL# USERNAME COMMAND STATUS MACHINE
---- ---------- -------- ------- -------- -------
1386 9 TEST_USR 0 INACTIVE SATURNO


Para obtener unas estadísticas de la PGA se puede usar la vista V$PGASTAT:

SQL> select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------- ---------- -------
aggregate PGA target parameter 256901120 bytes
aggregate PGA auto target 58300416 bytes
global memory bound 51380224 bytes
total PGA inuse 192188416 bytes
total PGA allocated 283714560 bytes
maximum PGA allocated 548391936 bytes
total freeable PGA memory 0 bytes
process count 343
max processes count 404
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 78736384 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 530432 bytes
over allocation count 15599
bytes processed 8,1983E+11 bytes
extra bytes read/written 1,4539E+10 bytes
cache hit percentage 98,25 percent
recompute count (total) 208640


Aggregate PGA target parameter indica el valor del parámetro PGA_AGGREGGATE_TARGET.
Aggregate PGA auto target indica la cantidad de PGA que Oracle puede usar como áreas de trabajo. Este valor varía en función de la carga de trabajo. Total PGA allocated indica el valor total de PGA asignado. Este es el valor que Oracle intenta mantener por debajo de PGA_AGGREGGATE_TARGET. Este valor puede ser más alto que el indicado por el parámetro de inicialización en momentos puntuales de mucha carga o de forma constante si PGA_AGGREGGATE_TARGET está pobremente dimensionado. Over allocation count indica el número de veces que ha habido que sobrepasar el límite impuesto por PGA_AGGREGGATE_TARGET, lo que es síntoma de que hay que aumentar el parámetro.
Cache hit percentage indica el número de veces que se ha podido realizar una operación intensiva de ordenación/clasificación sin incurrir en un "extra pass". Si este valor no es cercano al 100% indica que hay que aumentar el parámetro PGA_AGGREGGATE_TARGET.

La vista V$PGA_TARGET_ADVICE es muy útil para predecir cómo cambiaría el comportamiento de la Instancia si modificamos le valor de PGA_AGGREGATE_TARGET. Para que esta vista recopile información el parámetro STATISTICS_LEVEL debe estar configurado a TYPICAL o ALL:

SQL> select pga_target_for_estimate, pga_target_factor,
estd_pga_cache_hit_percentage, estd_overalloc_count
from v$pga_target_advice;

PGA_TGT_FE PGA_TGT_F ESTD_HIT_PCTGE ESTD_OVRALL_COUNT
---------- --------- -------------- -----------------
32112640   ,125      67             67303
64225280   ,25       67             67303
128450560  ,5        67             66960
192675840  ,75       74             40785
256901120  1         97             4652
308281344  1,2       98             73
359661568  1,4       98             0
411041792  1,6       98             0
462422016  1,8       98             0
513802240  2         98             0
770703360  3         98             0
1027604480 4         100            0
1541406720 6         100            0
2055208960 8         100            0

La columna pga_target_for_estimated indica la estimación para las que las demás columnas están dando información. La columna pga_target_factor indica la relación entre el valor de PGA_AGGREGGATE_TARGET actual y el valor de estimación de esa fila. Cuando este factor está a 1 indica la relación 1 a 1 -el tamaño actual-. Si es 2, es 1 a 2 -el doble-, y si es 0,125 es 1 a 0,125 -una octava parte-. La columna estd_pga_cache_hit_percentage indica el porcentage de éxito, operación resueltas sin "extra pass" para ese pga_target_for_estimated. Por último, la columna estd_overalloc_count indica el número de veces que habría que sobrepasar el valor indicado por PGA_AGGREGGATE_TARGET para ese pga_target_for_estimated.

Para este caso el valor óptimo sería subir el valor del parámetro 1,4 veces porque nos da una estimación de 0 "extra passes". Seguir aumentándolo no aportaría ninguna mejora. Si estamos justos de memoria y 73 "extra passes" son asumbles se puede empezar aumentándolo 1,2 veces en vez 1,4 y después de un tiempo de trabajo volver a chequear los valores.

Por último decir que las vistas V$PGASTAT y V$PGA_TARGET_ADVICE guardan información de la Instancia en tiempo real. Cuando se reinicia la Instancia estas vistas empiezan a recopilar datos nuevos. Para ver el histórica entre reinicios están las vistas DBA_HIST_PGASTAT y DBA_HIST_PGA_TARGET_ADVICE respectivamente.

Espero que te haya sido útil. Por favor, deja tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones
www.inforconsult.es

jueves, 22 de noviembre de 2007

La papelera de reciclaje de Oracle10g: Recycle Bin

¿Qué es la papelera de reciclaje de Oracle?
A partir de Oracle10g se incorpora esta nueva funcionalidad, denominada FLASHBACK DROP, que se viene a unir a las demás características que conforman el capítulo de las funcionalidades FLASHBACK. La idea es la misma que tienen algunos sistemas operativos con su papelera de reciclaje, es decir, disponer de una ubicación donde colocar los objetos borrados con el fin de poder recuperarlos después. Por supuesto, todo esto se hace de forma transparente para el usuario.

¿Cómo funciona la papelera de reciclaje?
Cuando un usuario lanza la orden DROP sobre una tabla, en realidad, lo que hace Oracle es renombrar ese objeto y todas sus dependencias, tales como triggers, índices, constraints etc. El nombre que recibe el objeto es generado automáticamente por Oracle pero siempre está prefijado por BIN$. Como sólo se produce un renombrado, los objetos siguen existiendo y ocupando espacio sus correspondientes tablespaces. El objeto sólo se borra definitavemente si el usuario o el administrador usan el comando PURGE o el espacioocupado por ese objeto es necesario para acomodar una nueva extensión de un objeto vivo (por esto último, se dice que FLASHBACK DROP no es intrusivo ya que si el espacio ocupado por la papelera es necesario se libera automáticamente).Por el contrario, cuando se borra un tablespace sus objetos no son pasados a la papelera sino que son borrados definitivamente, incluyendo los objetos que estén en la papelera de ese tablespace. Lo mismo ocurre cuando se borra un usuario con opción CASCADE.

¿Se puede ver el contenido de las tablas de la papelera?
Sí. Las tablas borradas que residen en la papelera pueden ser consultados con las mismas restricciones que tenían antes de ser borrados. Para consultarlos hay que usar el nombre nuevo que le asigna Oracle entrecomillándolo.

¿Se puede modificar el contenido de las tablas de la papelera?
No. Las tablas de la papelera son de solo lectura. Si se intenta un transacción DML se obtiene el siguiente error: ORA-38301: no se puede realizar DDL/DML en objetos de la papelera de reciclaje.

¿Se puede restaurar un objeto borrado que está en la papelera?
Sí. Al restaurar un objeto también se restauran sus objetos dependientes, pero estos conservan los nombres que tenían en la papelera. Se puede conseguir de varias formas:
  1. Haciendo un CREATE TABLE nombre_tabla... AS SELECT * FROM nombre_tabla_borrada.
  2. Creando la tabla manualmente mediante CREATE TABLE y después rellenarla usando INSERT INTO nombre_tabla AS SELECT * FROM nombre_tabla_borrada.
  3. Usando FLASHBACK TABLE nombre_tabla TO BEFORE DROP [RENAME TO nuevo_nombre_tabla]. En este caso el nombre_tabla puede ser el nombre original de la tabla asignado por Oracle al meterla en la papelera. IMPORTANTE: si existe más de una tabla en la papelera con el mismo nombre que la tabla original, Oracle restauraría la borrada más o el recientemente. Por esta razón, es más seguro usar el nombre generado por Oracle. Por último, con la cláusula RENAME TO se puede indicar el nuevo nombre que tendrá la tabla después de ser recuperada.
¿Cómo es la papelera de reciclaje?
La vista RECYCLEBIN es un sinónimo que apunta a la vista USER_RECYCLEBIN que su vez apunta a la tabla SYS.RECYCLEBIN$. Por tanto, es indiferente consultar RECYCLEBIN o USER_RECYCLEBIN. La estructura de esta vista es:

Nombre              ¿Nulo?            Tipo
------------------- ----------------- ------------
OBJECT_NAME         NOT NULL          VARCHAR2(30)
ORIGINAL_NAME                         VARCHAR2(32)
OPERATION                             VARCHAR2(9)
TYPE                                  VARCHAR2(25)
TS_NAME                               VARCHAR2(30)
CREATETIME                            VARCHAR2(19)
DROPTIME                              VARCHAR2(19)
DROPSCN                               NUMBER
PARTITION_NAME                        VARCHAR2(32)
CAN_UNDROP                            VARCHAR2(3)
CAN_PURGE                             VARCHAR2(3)
RELATED             NOT NULL          NUMBER
BASE_OBJECT         NOT NULL          NUMBER
PURGE_OBJECT        NOT NULL          NUMBER
SPACE                                 NUMBER

Cada vez que un objeto es borrado con el comando DROP se crea una entrada nueva en esta vista que tiene el nombre nuevo dado al objeto, prefijado por BIN$, su nombre original, el tablespace en el que reside, la fecha y hora de eliminación, el espacio que ocupa, etc.

¿Cuál es la sintaxis del comando PURGE?
Como ya se ha indicado, la eliminación de los objetos residentes en la papelera se puede hacer manualmente a través del comando PURGE. Con este comando sólo se pueden borrar los objetos del usuario que lanza la orden, salvo que se disponga de permiso SYSDBA o DROP ANY. Las variantes que existen son:
  • PURGE TABLE nombre_tabla: Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.
  • PURGE INDEX nombre_índice: Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.
  • PURGE RECYCLEBIN: Elimina todos los objetos (del usuario que lanza la orden) de la papelera.
  • PURGE DBA_RECYCLEBIN: Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.
  • PURGE TABLESPACE nombre_tablespace: Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.
  • PURGE TABLESPACE nombre_tablespace USER nombre_usuario: Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.
¿Se puede pasar por alto la Papelera de Reciclaje?
Sí. Existen dos modos para evitar que el objeto borrado pase a la papelera:

  • A través de un parámetro del init.ora. En Oracle10g R1 es _RECYCLEBIN, un parámetro oculto porque empieza por el carácter subrayado "_", que por omisión está a TRUE. Asignándole FALSE hacemos que Oracle10g no use la papelera y elimine el objeto definitivamente, como en anteriores versiones. En Oracle10g R2 se usa el parámetro RECYCLEBIN, éste ya no oculto, cuyos valores son ON y OFF siendo ON el valor por omisión. Éste parámetro es dinámico por lo que se puede cambiar para la sesión o para la Instancia.
  • Usando la cláusula PURGE del comando DROP. Con esta opción conseguimos el mismo efecto pero hay que indicarlo en cada comando DROP que usemos.
Ejemplo completo en Oracle10g Release 2:
Creamos una tabla cualquiera llama T1:

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


Comprobamos si existe alguna tabla borrada en la papelera:

SQL> select * from recyclebin; -- o SHOW RECYCLEBIN
ninguna fila seleccionada.


Borramos la tabla T1:

SQL> drop table t1;
Tabla borrada.


Comprobamos que la tabla está ahora en la papelera:

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ ---------------
BIN$ACEfotX6TRqgiRnY6LN6nQ==$0 T1


Volvemos a crear de nuevo la tabla T1 comprobando que la borró de verdad (si no la hubiera borrado no nos dejaría volver a crearla):

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


La volvemos a borrar:

SQL> drop table t1;
Tabla borrada.


Comprobamos que ahora están las dos tablas en la papelera:

SQL> select object_name, original_name from recyclebin;

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ ---------------
BIN$ACEfotX6TRqgiRnY6LN6nQ==$0 T1
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1

Intentamos acceder a alguna de las tablas borradas en selección e inserción:

SQL> select * from "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0";
ninguna fila seleccionada

SQL> insert into "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0" values (1);
insert into "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0" values (1)* ERROR en línea 1:
ORA-38301: no se puede realizar DDL/DML en objetos de la papelera de reciclaje.

Eliminamos la tabla T1 de la papelera. Como hay dos borra la T1 más antigua:

SQL> purge table t1;
Tabla depurada.


SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ -------------
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1


Volvemos a crear la tabla T1:

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


Ahora la borramos sin dejarla ir a la papelera de reciclaje y lo comprobamos:

SQL> drop table t1 PURGE;
Tabla creada.

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ -------------
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1


Por último, restauramos la tabla T1 desde la papelera de reciclaje (si existiese ya una tabla con el nombre T1 obtendríamos el error ORA-38312: el nombre original lo ha utilizado un objeto existente). En este caso usaríamos la cláusula vista anteriormente RENAME TO nuevo_nombre:

SQL> flashback table T1 to before drop;
Flashback terminado.

SQL> select object_name, original_name from recyclebin;
ninguna fila seleccionada


Y hasta aquí por hoy con este artículo. Espero que te haya sido útil. Por favor, deja
tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones
www.inforconsult.es