Libro de Admon de BBDD

Hola,

Estoy bastante contento porque ya está disponible el libro donde colaboro:

Administración de Sistemas Gestores de Bases de Datos

Hice el capítulo 4 y parte del 5. Pero bueno, tengo que aclarar que Mª Jesús es la encargada de traducir el mundo de enredos que le doy. Así que además de su parte, hace que la mía sea "legible". Muchas gracias Chus.

No es nada comparable con ningún libro de Tuning de los grandes (Lewis, Dyke, Millsap, Poder, Kyte, etc.) y tampoco llega a ser un libro universitario, pero si que intento resumir muchas de las cosas que me han enseñado, además algo de experiencia y mis interminables charlas Shreky. Además es muy práctico, son ejemplos reales y entornos de los que te encuentras en la mayoría de las empresas, así que puede ser útil para aprender o practicar la administración de algunos SGBD.

Resumen I'm happy.

:)

John

cambio en locking en 11gR2

Hola,

Hay un proceso en un cliente que no me gusta nada por la cantidad de eventos de espera :

"enq: TX - row lock contention"

que generan las sesiones. Pero así se ha decido hacer desde hace mucho tiempo.

Si tienen el placer de leer el libro "Expert Oracle Database Architecture" de Tom Kyte, él describe un método para bloquear y no perder información de "updates", en el capítulo 6. El señor Kyte llama a este tipo de bloqueos "Pessimistic Locking", en este caso no se agrega el "nowait" en la sentencia por lo que realmente queda a la espera. También existe en su blog una nota sobre "write consistency"que hace referencia a todo esto.

Por increíble que parezca este comportamiento de Locking ha cambiado (espero que temporalmente, y solo sea un BUG) en la versión 11gR2.

Después de comprobar que realmente cambiaba intenté contactar con la persona más importante en el mundo de Oracle Jonathan Lewis, para ver que parecía. No es nada común que conteste, porque no intenta ser como AskTom, además existe http://forums.oracle.com y a buenas preguntas suele responder.

Expongo el caso si están pensado en pasar a 11.2 y alguno de sus procesos actúa de esta forma, pueden probarlo para las versiones 10gR1, 10gR2, 11gR1 y 11gR2, en Windows, Linux, HP-UX, Solaris y AIX. El resultado es el mismo: la versión 11gR2 cambia.

Se tiene una tabla en el que tienes una columna con valores distintos, la que puede comportarse como una queue table, es decir, obtienes el valor mínimo de esa columna, trabajas con el resto de la información y borras esa filas. Puedes o no tener un índice y el comportamiento es el mimo.

La tabla es la siguiente:

SQL> create table tab1

2 (id number,

3 info varchar2(10),

4 constraint tab1_pk primary key (id)

5 using index

6* (create index idx_tab1_pk on tab1(id)))

La llenamos con unos pocos datos:

SQL> insert into tab1

2 select rownum, 'aaaaaa'||rownum

3 from dual

4 connect by rownum <=5;

5 rows created.

Y la consulta para obtener el bloqueo del mínimo es:

SQL> select id

2 from tab1

3 where id = (select min(id) from tab1)

4 for update;

El caso es que cuando se ejecutan varias sesiones a la vez, digamos 10, la primera consigue el bloqueo y se inicia la transacción, y el resto se quedan esperando a que termine la transacción. En el caso de 2 sesiones tenemos en 10g, 11gR1 y 11gR2, lo siguiente en V$LOCK:

V$lock

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

385247F0 3852481C 71 AE 99 0 4 0 533 0

3852494C 38524978 41 AE 99 0 4 0 291 0

385249C0 385249EC 41 TX 65561 166 0 6 144 0

00DE464C 00DE467C 41 TM 12467 0 3 0 144 0

00DE464C 00DE467C 71 TM 12467 0 3 0 219 0

37FD9C20 37FD9C60 71 TX 65561 166 6 0 219 1

Los tipos de bloqueos AE, solo aparecen en las versiones 11gR1 y 11gR2. Pero se aprecia claramente que la sesión 71 obtuvo el bloqueo exclusivo sobre la fila, y la sesión 41 está a pidiendo un bloqueo exclusivo sobre la misma.

Ahora se borra la fila con la que se ha bloqueado y se "confirma" la transacción:

SQL> delete tab1 where id=1;

1 row deleted.

SQL> COMMIT;

Commit complete.

En las versiones 10g y 11gR1, la sesión que estaba a la espera vuelve con:

SQL> select id

2 from tab1

3 where id = (select min(id) from tab1)

4 for update;

ID

----------

2

¿Qué ha pasado? En teoría la explicación que me han dado, es que cuando existe el cambio en el datos, Oracle relanza la sub-consulta y obtiene de nuevo un datos, para el que después realiza el bloqueo. Y ¿Cual es el problema? que en la versión 11gR2 el resultado es el siguiente:

No row selected.

Al ver este comportamiento y contactar con Mr. Lewis, intentamos buscar el motivo, y ver que se podía hacer para cambiar el comportamiento. Primero ver si el comportamiento proviene del optimizador, y lo más rápido era ver lo planes de ejecución, para la versión 10g y 11gR1:

--------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | FOR UPDATE | | | | | |

|* 2 | INDEX RANGE SCAN | IDX_TAB1_PK | 1 | 13 | 1 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 13 | | |

| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_TAB1_PK | 4 | 52 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Y para la versión 11gR2:

----------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | FOR UPDATE | | | | | |

| 2 | BUFFER SORT | | | | | |

|* 3 | INDEX RANGE SCAN | IDX_TAB1_PK | 1 | 13 | 1 (0)| 00:00:01 |

| 4 | SORT AGGREGATE | | 1 | 13 | | |

| 5 | INDEX FULL SCAN (MIN/MAX)| IDX_TAB1_PK | 4 | 52 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Al parecer había un cambio y esto era algo razonable por lo que la primera prueba consistía en usar el HINT

/*+ optimizer_feature_enable('10.2.0.4') */

Pero el comportamiento seguía igual. También existe el HINT /*+ precompute_subquery */ pero no cambia nada.

Después (un poco tedioso) era buscar en algún BUG solucionado y desactivar el parche con el parámetro "_fix_control" (mirar lock_horror) y encontré un que parecía que iba a cambiarlo:

v$system_fix_control

7306637 --> inject SORT BUFFER under FOR UPDATE

SQL> alter system set "_fix_control"='7306637:OFF' scope=spfile;

System altered.

Y efectivamente cambió el plan de ejecución. Pero el comportamiento no. Al final terminé desactivando todos los BUGNO que estaban en la versión 11.2.0.1 y sigue el comportamiento igual.

Oracle ya sabe de este cambio, y está reportado, por lo ahora estoy a la espera de un parche o algo para que regrese al comportamiento anterior.

Esto no será muy bueno para algunas aplicaciones, aunque seguro que en breve tendrán corregido este problema. Esto era solo una demostración que si tienes algo parecido, no pierdas más tiempo o no cambies tu aplicación.

También se he probado las sentencias :

delete tab2 where id = (select min(id) from tab2);

update tab1 set id=id+10

where id = (select min(id) from tab1);

Y sufre el mismo comportamiento, hay un cambio entre 10g|11gR1 y la 11gR2. La segunda sesión queda a la espera y al final en la versión 11gR2 no hay filas a borrar o a actualizar, mientras que en la anteriores re-ejecuta la consulta, obtiene el mínimo y borra o actualiza la fila.

John Ospino Rivas

un_alias_soluciona_1445

Workaround del ORA-01445 con un simple alias

He visto un ORA-01445 que cuando lo buscas hay incluso hasta un BUG reportado en la versión 10.2.0.3, sinceramente no sé por qué alguien quiere usar un ORDER BY de un ROWID. Pero el caso es que omitiendo las tablas he creado esta prueba en la que es básicamente algo parecido. Si alguien tiene este error puede probar agregando un alias a la columna de la tabla del rowid y “salta” el error.

Pruebas en 10.2.0.4 en Windows, 10.2.0.3|11.1.0.6|11.2.0.1 en linux

Limpiar el entorno

==================

SQL> drop table tab1 purge;

drop table tab1 purge

*

ERROR en lÝnea 1:

ORA-00942: la tabla o vista no existe

SQL> drop table tab2 purge;

drop table tab2 purge

*

ERROR en lÝnea 1:

ORA-00942: la tabla o vista no existe

SQL> drop table tab3 purge;

drop table tab3 purge

*

ERROR en lÝnea 1:

ORA-00942: la tabla o vista no existe

Crear entorno para prueba

=========================

SQL> create table tab1

2 (id number,

3 info varchar2(10));

Tabla creada.

SQL> create table tab2

2 (id number,

3* info varchar2(10))

Tabla creada.

SQL> create table tab3

2 (id number,

3* info varchar2(10))

Tabla creada.

Cargar las tablas

=================

SQL> insert into tab1

2 select rownum, 'a'||rownum

3 from dual

4 connect by rownum <= 20;

20 filas creadas.

SQL> insert into tab2

2 select rownum, 'a'||rownum

3 from dual

4* connect by rownum <= 50

50 filas creadas.

SQL> insert into tab3

2 select rownum, 'a'||rownum

3 from dual

4* connect by rownum <= 500

500 filas creadas.

SQL> commit;

Confirmaci¾n terminada.

SQL> select t1.id, ta2.info

2 from tab1 t1,

3 (select ta1.rowid, ta1.id, ta1.info

4 from tab2 t2,

5 (select rowid, id, info

6 from tab3

7 where id between 10 and 20) ta1

8 where ta1.id = t2.id) ta2

9 where t1.id = ta2.id

10* order by ta2.rowid

from tab1 t1,

*

ERROR en lÝnea 2:

ORA-01445: no se puede seleccionar ROWID, ni tomar un ejemplo, desde una vista de la uni¾n sin una tabla protegida por

Claves

SQL> select t1.id, ta2.info

2 from tab1 t1,

3 (select ta1.rowid r, ta1.id, ta1.info

4 from tab2 t2,

5 (select rowid, id, info

6 from tab3

7 where id between 10 and 20) ta1

8 where ta1.id = t2.id) ta2

9 where t1.id = ta2.id

10* order by ta2.r

ID INFO

---------- ----------

10 a10

11 a11

12 a12

13 a13

14 a14

15 a15

16 a16

17 a17

18 a18

19 a19

20 a20

11 filas seleccionadas.

Como ves es en ocasiones los errores pueden desviar la atención de la solución.

STATUS SNIPED

Hola después de demasiado tiempo. Bueno directo al grano. Dentro de los posibles valores de STATUS se encuentra SNIPED, la documentación dice:

Sesión inactiva, esperando en cliente

El PMON puede realizar la limpieza de la sesión, pero no puede eliminar el proceso del SO. Un ejemplo de esto es que puede ser provocado definiendo un PROFILE y poner un limite en el recurso IDLE_TIME.

Primero creamos el PROFILE:

CREATE PROFILE pu

LIMIT

IDLE_TIME 1;

Después se lo asignamos al usuario

ALTER USER tj PROFILE pu;

Ahora nos conectamos con el usuario tj, y no hacemos nada:

###>sqlplus /nolog

SQL> conn tj

Introduzca la contrase±a:

Conectado.

SQL>

En otra sesión consultamos la vista V$SESSION

select username, program, event, status, state, seconds_in_wait

from v$session

where username is not null

USERNAME PROGRAM EVENT STATUS STATE SECONDS_IN_WAIT

-------- ------------ ----------------------------- -------- ------------------- ---------------

SYS sqlplus.exe SQL*Net message to client ACTIVE WAITED SHORT TIME 0

TJ sqlplus.exe SQL*Net message from client INACTIVE WAITING 125

Después de un rato observamos:

USERNAME PROGRAM EVENT STATUS STATE SECONDS_IN_WAIT

-------- ------------ ---------------------------- -------- ------------------- ---------------

SYS sqlplus.exe SQL*Net message to client ACTIVE WAITED SHORT TIME 0

TJ sqlplus.exe SQL*Net message from client SNIPED WAITING 213

Es esta prueba simple en Windows, cuando intentamos ejecutar de nuevo cualquier cosa no desconecta la sesión, resultado:

1* select * from dual

select * from dual

*

ERROR en lÝnea 1:

ORA-02396: ha excedido el tiempo mßximo de inactividad, vuelva a conectarse

John

ERROR EJECUTANDO SYSREV

ERROR EJECUTANDO SYSREV

Hola, esto es una problemilla simple y muy tonto que es cuando SYSRESV y aparece :

oracle@mybd:/home/oracle $ sysresv

/usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'.

Killed

 

Y se arregla exportando las variables de entorno de las librerías de Oracle, por ejemplo para HP-UX viene a ser SHLIB_PATH, o en Linux LIBRARY_PATH con $ORACLE_HOME/lib, por ejemplo:

oracle@mybd:/home/oracle $ export SHLIB_PATH=$ORACLE_HOME/lib

oracle@mybd:/home/oracle $ sysresv

 

IPC Resources for ORACLE_SID "MYDB" :

Shared Memory:

ID KEY

21 0x7cdca070

Semaphores:

ID KEY

360486 0xe36a4220

Oracle Instance alive for sid " MYDB "

oracle@mybd:/home/oracle $

 

Particularmente uso SYSRESV para ver los "cachos" de memoria compartida de la que hace uso la SGA, y también de los semáforos. Otra opción es usa IPCS, pero este comando no te muestra el nombre de la instancia, y si tú maquina tiene más de una instancia, no lo sabes que es para quien. Con SYSRESV solo tienes que exportar el ORACLE_SID y te da la dirección de la memoria compartida y los semáforos de esa instancia solamente.

Si un SHUTDOWN ABORT no elimina la memoria compartida y tienes muchas instancias arriba, es otra opción de eliminar ese cacho de memoria.

Es BASTANTE improbable que falle un "SHUTDOWN ABORT", o que la maquina quede con cacho de memoria compartida con el nombre de la instancia, y que no desees hacer un ipcrm, y no sabes cual instancia es y no puedas bajar el resto de instancias, sino que solamente esa es la que es necesaria bajar. Pero si todas estas desgracias en cadena suceden una opción es SYSRESV.

 

ORA-01034 Y ORA-27101 por registro del LISTENER

Un cliente me dice que no se puede conectar a la base de datos, y lo que le sale es:

oracle 42> sqlplus pepe@bbdd1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 2 15:18:02 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

HPUX-ia64 Error: 2: No such file or directory

Lo primero que piensas es que el fichero $ORACLE_HOME/bin/oracle puede que no tenga los permisos adecuados. Después de verificarlo como estás en HP-UX piensas en IPC y verificas que el directorio /var/tmp/.oracle tiene los permisos adecuados. Vas a revisar también que segmentos de memoria compartida pueden estar bloqueando algo (ipcs –m). Lo siguiente es revisar el LISTENER, y tanto el SID_NAME como el ORACLE_HOME están bien. Bueno lo que siempre dice Oracle es que debes revisar el LISTENER. Bajar y subir el LISTENER no es suficiente, ver que tu fichero tnsnames.ora también está bien tampoco.

Puede llegar a ser un tontería como la siguiente, y es que cuando pasas de 9i a 10g hay pequeño cambio. El caso es que en 9i con levantar el LISTENER aunque que sea no el de por defecto, el puede realizar la conexión si tienes el ORACLE_HOME y el SID_NAME bien, mientras que en la 10g no. Entonces olvidar poner el parámetro LOCAL_LISTENER y crear la entrada en el fichero TNSNAMES.ORA puede ser el problema.

Aunque ejecutando " lsnrctl services list_X" aparece en las 2 versiones como estado UNKNOWN en la 9i si que puedes conectarte. En teoría el PMON se encargaría de registrar la base de datos en el LISTENER que aparece en el LOCAL_LISTENER, y cuando se tiene LISTENER que no es de por defecto, y si no estoy mal, el PMON también hace 2 búsquedas, la primera es que se llame "LISTENER" (por defecto) y la segunda buscar un "LISTENER_xxx". Lo que significa que cuando encuentra el segundo tipo de entrada es decir la palabra listener y algo más, verifica si está en la dirección local TCP/IP en el puerto 1521, y si todo se cumple lo registra. Esto si no hay un valor en LOCAL_LISTENER, si existe ese valor pues lo registra en el nombre de la entrada en el tnsnames.ora que le asignes.

Entonces conclusión, si te aparecen estos errores, puede ser simplemente porque no tengas LOCAL_LISTENER.

Espero que no pierdan tanto tiempo como yo, intentando encontrar algo más raro o complicado.

Tablas con FULL SCAN en la Buffer Cache

Feliz 2010 a los posible lectores :D

La verdad este año tengo el propósito de escribir un poco más a menudo (Siempre digo lo mismo, jejeje). Bueno, empiezo con algo muy rapidito. Buscando y buscando y buscando en google, encontré la descripción de la columna flag.x$bh. Interesante, y encontré que:

FLAG
KCBBHFSQ 0x80000 sequential scan only flag

KCB = Kernel Cache Buffer.

El objetivo es conocer todos los objetos que están en la buffer cache que se suben de forma secuencial, esto "PUEDE" implicar que si son tablas, se un FULL SCAN forma de acceso.
¿Para qué sirve?
Pues para conocer las POSIBLES tablas que están en la Buffer Cache con FULL SCAN, importante por temas de rendimiento y esas cosas :D. Por aquello de "menos CR mejor", aunque como todo "depende", pero en general o porque no, menos I/O mejor.

¿Cómo puedo entonces sacar la lista de esas tablas?
Aquí está una consulta, ya después es a gusto del consumidor lo que hagan con ella. Yo excluyo los las tablas de los objetos, propios de algunos componentes de Oracle.

SELECT distinct o.owner,o.object_name,o.object_type,o.owner
FROM dba_objects o,x$bh x
WHERE x.obj=o.object_id
AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)>0
AND o.owner not in ('SYS','PATROL','FLASHBACKSTATS','INVENTORY', 'SCOTT'
,'OUTLN','TSMSYS','EXFSYS','SYSMAN','SYSTEM', 'MDSYS', 'DMSYS', 'CTXSYS'
,'DBSNMP','WMSYS','SMSEXP', 'ORDPLUGINS', 'ORDSYS', 'PUBLIC', 'OLAPSYS');

El 524288 es el decimal del hexadecimal 80000, por tanto, la columna FLAG contiene muchísima información. Hasta la 9i se de 38 datos sobre el estado del bloque en la buffer cache.

Nota:
Según un compi mío, del cual no quiero dar el nombre porque se cabrea :). Él me dijo que si la cantidad de bloques de la consulta son mayores que el 10% del tamaño de la buffer cache (se puede contralar por parámetro oculto, creo) entonces Oracle protege la SGA y no va a memoria esta cantidad de bloques sino directamente a la UGA. Así bien para server processes dedicados, sería su PGA, pero cuando usan Shared Server, en teoría debería ir a la Large Pool, pero esto todavía lo tendría que confirmar :), si alguien ya lo ha probado me gustaría verlo.