jueves, 30 de julio de 2009

Se puede crackear la password de un usuario oracle?

Cualquier usuario con privilegios para ver la vista de catálogo DBA_USERS puede obtener el hash de la password de cualquier usuario. La diferencia entre encriptar y hashear es que el hashing no tiene vuelta atrás, no tiene función inversa. El algoritmo utilizado por Oracle para hashear se conoce y el metodo para obtener la password consiste en aplicar dicho algoritmo al string compuesto por el nombre del user concatenado con la password plana. La probabilidad de que de dos string de entrada distintos se obtenga el mismo valor hash es muy muy baja pero no imposible. Dado el nombre del usuario, el valor hash y el algoritmo lo que hacen la mayoria de los crackers es hashing probando con todas las combinaciones posibles para generar el valor hash y luego comparar con el valor de hash de la columna password de la vista dba_users. Algunos programas muy optimizados generan mas de un millón de posibilidades por segundo. Una persona no muy tecnica a priori pensaría que generando 1 millon de combinaciones por segundo se podrian crackear todas las passwords. Mas abajo, usando la funcion f_get_duration
vamos a ver que tan sorprendente pueden ser los tiempos en "romper" una contraseña de n caracteres.

Googleando se pueden conseguir muchos programas que usan diccionario o fuerza bruta. Algunos usan una combinación, y primero verifican si por ejemplo la password coincide con el nombre del user, si se usó como password una palabra del diccionario español, ingles, etc, y si no tienen exito comienza a generar combinaciones y probar, en la siguiente pagina hay un listado de utilitarios para crackear: Oracle Password Cracker

Voy a mostrarles como usar uno de los programas mas rapidos que encontré en la web, es gratis y pueden bajarselo y conocer mas en: ToolCrypt.

Primero veamos las opciones que tenemos para generar las permutaciones:



C:\Dwn\orabf-v0.7.6>orabf.exe

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------

usage: orabf [hash]:[username] [options]

options:
-c [num] complexity: a number in [1..6] or a filename
- read words from stdin
[file] read words from file
1 numbers
2 alpha
3 alphanum
4 standard oracle (alpha)(alpha,num,_,#,$)... (default)
5 entire keyspace (' '..'~')
6 custom (charset read from first line of file: charset.orabf)
-m [num] max pwd len: must be in the interval [1..14] (default: 14)
-n [num] min pwd len: must be in the interval [1..14] (default: 1)
-r resume: tries to resume a previous session

Por default se usan 39 caracteres, incluidas letras, numeros y caracteres comunes como $, #, etc. Se puede definir largo minimo y maximo de passwords, tipo de caracteres (alpanumericos, numericos y combinaciones) y se puede resumir una busqueda. Presionado cualquier tecla se muestra la cantidad de permutaciones x seg y la ultima combinación probada.

Ahora les voy a mostrar como usarlo. Primero voy a crear un usuario TEST_USR cuya
password es mi nombre: PABLO.


rop@DESA10G> drop user test_usr;

Usuario borrado.

rop@DESA10G> create user test_usr identified by pablo;

Usuario creado.

rop@DESA10G> select password from dba_users where username = 'TEST_USR';

PASSWORD
------------------------------
8ADE17EF4E4AF7F0

rop@DESA10G>

Una vez obtenido el password hasheado, probamos cto demora en encontrar la password
desde el valor hash.



C:\Dwn\orabf-v0.7.6>orabf 8ADE17EF4E4AF7F0:test_usr

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done

Starting brute force session using charset:
#$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_

press 'q' to quit. any other key to see status

current password: P2THP
36569699 passwords tried. elapsed time 00:00:58. t/s:628649

password found: TEST_USR:PABLO

37017020 passwords tried. elapsed time 00:00:58. t/s:628572


Demoró tan solo 58 segundos. Utilizó fuerza bruta, ya que si bien orabf primero prueba usando diccionario, este es muy pequeño y trivial. Existen otras herramientas que usan diccionarios muy completos y para este caso, dado que usé un nombre propio, se podria haber obtenido el resultado mas rápido.
Voy a cambiar la password agregandole el signo $ al final y pruebo nuevamente:


rop@DESA10G> alter user test_usr identified by pablo$;

Usuario modificado.

rop@DESA10G> select password from dba_users where username = 'TEST_USR';

PASSWORD
------------------------------
561AB3687A5DBD44

rop@DESA10G>

C:\Dwn\orabf-v0.7.6>orabf 561AB3687A5DBD44:test_usr

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done

Starting brute force session using charset:
#$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_

press 'q' to quit. any other key to see status

current password: B3YKNE
165693702 passwords tried. elapsed time 00:04:02. t/s:684420

current password: EAHT1_
451564805 passwords tried. elapsed time 00:10:42. t/s:703149

current password: IDIZEX
819470880 passwords tried. elapsed time 00:19:15. t/s:709335

current password: K9IAAT
990627331 passwords tried. elapsed time 00:23:14. t/s:710231

current password: MQW0LR
1211219907 passwords tried. elapsed time 00:28:28. t/s:708795

current password: P#SDCG
1416898294 passwords tried. elapsed time 00:33:17. t/s:709419

password found: TEST_USR:PABLO$

1443663769 passwords tried. elapsed time 00:33:54. t/s:709542


C:\Dwn\orabf-v0.7.6>

Ahora tardó 33 minutos. Mi maquina no es muy rapida y genera alrededor de 700 mil casos por segundo. Solo agregué un caracter adicional y los tiempos aumentaron considerablemente. Abajo calculé aproximadamente cto se tardaria en realizar todas las combinaciones posibles de acuerdo a la cantidad de caracteres de password a testear. Para la prueba se tomó como hipotesis que se generan 1 millón de combinaciones por segundo y se permuta entre 39 caracteres posibles:


rop@DESA10G> select rownum+4 "Caracteres",f_get_duration(power(39,rownum+4)/1000000) "YY MN DD HH MI SS"
2 from dual
3 connect by rownum <= 6;


Caracteres YYYY MN DD HH MI SS
--------------------------------------------------------------
5 0000 00 000 00 01 30
6 0000 00 000 00 58 39
7 0000 00 001 14 07 11
8 0000 02 000 22 40 09
9 0006 07 010 20 06 01
10 0257 11 015 15 54 45


6 filas seleccionadas.

rop@DESA10G>


Se puede observar, con cierta sorpresa, que por ejemplo, para obtener todas las combinaciones de 10 caracteres demoraría 257 años!. Como podemos apreciar, se podria crackear cualquier password (ya que en general no tienen mas de 10 caracteres) pero en un tiempo abismal. Podemos pensar en correr en paralelo en maquinas mas poderosas, etc, pero dudo que se pueda crackear una password de 10 caracteres en menos de 1 dia por ejemplo.
Como se suele decir, es siempre mejor prevenir que curar, por algo en 11g la vista dba_users no muestra mas la password hasheada. Sin hash no puedo comparar, asi que no hay metodo sistematico, sumado a que en 11g se pueden crear password case-sensitives lo que hace mucho mayor el universo de caracteres posibles a permutar.


rop@DESA11G> drop user test_usr;

Usuario borrado.

rop@DESA11G> create user test_usr identified by pablo;

Usuario creado.

rop@DESA11G> select password from dba_users where username = 'TEST_USR';

PASSWORD
------------------------------


rop@DESA11G>


Retorna null. La primera vez que descubrí eso pensé en los scripts que por ejemplo migran los usuarios de base de datos usando la info de la columna password para armar las ddl de la siguiente forma:


create user test_usr identified by values '8ADE17EF4E4AF7F0';

Buscando en la documentación leí que usando el paquete dbms_metadata se puede tomar el valor hasheado:


rop@DESA11G> select dbms_metadata.get_ddl('USER','TEST_USR') from dual;

DBMS_METADATA.GET_DDL('USER','TEST_USR')
--------------------------------------------------------------------------------

CREATE USER "TEST_USR" IDENTIFIED BY VALUES '8ADE17EF4E4AF7F0'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"


Si creo el user normalmente, sin setear el hash con values:


rop@DESA11G> drop user test_usr;

Usuario borrado.

rop@DESA11G> create user test_usr identified by pablo;

Usuario creado.

rop@DESA11G> select dbms_metadata.get_ddl('USER','TEST_USR') from dual;

DBMS_METADATA.GET_DDL('USER','TEST_USR')
--------------------------------------------------------------------------------

CREATE USER "TEST_USR" IDENTIFIED BY VALUES 'S:6E936D6C63B34E82D889412A4C30D5
9D49735F4945805E808446AFC9EA2C;8ADE17EF4E4AF7F0'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
rop@DESA11G>

La password hasheada es distinta y mucho mas larga, ya que a partir de 11g cambia el algoritmo a SHA-1 ( SHA-1), además no usa solo como valor adicional el username sino que agrega otra información no documentada para generar el hash.
Si miramos la tabla interna SYS.USER$, que es base para la vista DBA_USERS pero solo accesible con sys, vemos que se sigue almacenando el antiguo hash en la columna PASSWORD y que se guarda en la columna SPARE4 el nuevo hash.


SQL> select password,spare4 from user$ where name = 'TEST_USR';
PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------------------------
8ADE17EF4E4AF7F0
S:EF6450476A2C84EC1E012A0FD4D93E4B0EDF698150DE2A510FB66E4F429D

Por lo tanto vemos que se siguen guardando los hash a la antigua y de la nueva forma, pero ahora no se pueden ver desde vistas de catalogo.

Como moraleja, aunque para muchos es algo obvio, nunca creen password de menos de 8 caracteres y usen siempre combinación entre caracteres (mayúsculas o minúsculas si estan en 11g y tienen habilitado "sec_case_sensitive_logon=TRUE"), números y signos de puntuación. Lo ideal seria habilitar en la base funciones de control de complejidad de password para directamente evitar el uso de password cortas o triviales. Desde 11g se avanzó en la segurización de ambientes Oracle ya que en esa versión se introdujeron muchas caracteristicas que ayudan y promueven el uso de contraseñas mas complejas que minimizan su hackeo.

domingo, 26 de julio de 2009

Convertir segundos en años, meses, días, horas, minutos y segundos

Hace unos años un desarrollador me preguntó como podia hacer para convertir una columna de segundos acumulados en horas, minutos y segundos ya que se necesitaba mostrar esa información en un reporte para una compania de celulares. La pregunta me resultó muy interesante y la pude resolver de una forma bastante sencilla, utilizando solo sentencias sql (había encontrado otras soluciones pero utilizaban código VB o PL/SQL).
Un tiempo compartí la solución un sitio de Oracle y una semanas despues la publicaron en una sección de códigos utiles: "Convert Seconds to Hours, Minutes, and Seconds"
Viendo de armar una nota sobre seguridad de passwords en Oracle, necesitaba ver las cantidad de tiempo que demandaría crackear una password que requeria mostrar el tiempo necesario en romper una password medido en años, meses, dias, horas, minutos y segundos. Extendiendo el código referenciado arriba armé la siguiente función:

rop@DESA10G> create or replace function f_get_duration (p_sec number) return varchar2
2  is
3   l_dur varchar2(50);
4  begin
5   l_dur := to_char(extract(year from numtoyminterval(months_between(sysdate+(p_sec/60/60/24),sysdate),'month')),'0009') || ' ' ||
6            to_char(extract(month from numtoyminterval(months_between(sysdate+(p_sec/60/60/24),sysdate),'month')),'09')|| ' ' ||
7            to_char(extract(day from numtodsinterval ((sysdate+(p_sec/60/60/24))-add_months(sysdate,trunc(months_between(sysdate+
    (p_sec/60/60/24),sysdate))),'day' )),'009')|| ' ' ||
8            to_char(trunc(mod(p_sec,86400)/60/60),'09') || ' ' ||
9            to_char(trunc(mod(p_sec,3600)/60),'09') || ' ' ||
10           to_char(mod(mod(p_sec,3600),60),'09');
11   return l_dur;
12  end;
13  /

Función creada.
rop@DESA10G>

Abajo les paso unos ejemplos de uso:

Probemos con la cantidad de segundos en 22 horas:
rop@DESA10G> select f_get_duration(60*60*22) "YY MM DD HH MI SS" from dual;

YY MM DD HH MI SS
----------------------------------------------------------------------------------------------------
0000  00  000  22  00  00

Ahora con los segundos en 1 año y 22 horas:
rop@DESA10G> select f_get_duration((60*60*24*365)+(60*60*22))"YY MM DD HH MI SS" from dual;

YY MM DD HH MI SS
----------------------------------------------------------------------------------------------------
0001  00  000  22  00  00

Por ultimo con los segundos en 5 años y alrededor de 5 meses (puede variar porque tomo meses de 30 dias y dependerá del momento en que se corra)
rop@DESA10G>  select f_get_duration((60*60*24*365*5)+(60*60*24*30*5)) "YY MM DD HH MI SS" from dual;

YY MM DD HH MI SS
----------------------------------------------------------------------------------------------------
0005  04  026  00  00  00

rop@DESA10G>

Vemos que dió 5 años, 4 meses y 26 dias.

En una futura nota sobre algoritmos de fuerza bruta para "crackear" passwords les voy a mostrar usando la función extendida F_GET_DURATION cuanto tiempo se demoraría en romper una password de n caracteres.

jueves, 23 de julio de 2009

Lo nuevo en particionamiento de tablas (Partitioning) - Parte 2

Esta nota es la segunda de la serie (ver la primera parte: Lo Nuevo en Particionamiento - Parte 1 relacionada con las nuevas caracteristicas de particionamiento de 11g. En esta nueva nota les voy a mostrar dos nuevos features mas.


Partionamiento por Referencia

En un típico modelo maestro/detalle suele suceder que la clave para particionar esta solo en la tabla maestra y por lo tanto imposibilita que particionemos también la tabla detalle perdiendo así la oportunidad de aprovechar el partitioning. Una forma de solucionar eso es "bajar" la columna o columnas de particionamiento a la tabla detalle, con la consiguiente redundancia de información que eso provoca sumado al cambio de estructura de la tabla. A partir de 11g no se necesita cambiar el modelo de datos ya que se puede definir particiones por referencia. Veamos como funciona con un ejemplo:

Voy a crear 3 tablas: FACTURAS, ITEMS y CLIENTES. Las tablas FACTURAS e ITEMS van a estar particionadas. La tabla CLIENTES solo esta para completar el modelo reducido. La tabla FACTURAS actua como tabla maestra y usa particionamiento por rango sobre el campo fecha. La tabla ITEMS es tabla detalle de la tabla FACTURAS y dado que no tiene campo fecha vamos a aprovechar el nuevo feature para particionar referenciando a su tabla padre (FACTURAS).

rop@ROP11G> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


rop@ROP11G> create table clientes (id_cliente number(8),
2 nombre varchar2(20),
3 direccion varchar2(50),
4 fecha_ingreso date,
5 constraint clientes_pk
6 primary key (id_cliente));

Tabla creada.

rop@ROP11G> create table facturas (id_factura number(8),
2 id_cliente number(8),
3 tipo char(1),
4 fecha date,
5 importe number(15,3),
6 constraint facturas_pk
7 primary key (id_factura))
8 partition by range (fecha)
9 (
10 partition facturas_200901 values less than (to_date('20090201','YYYYMMDD')),
11 partition facturas_200902 values less than (to_date('20090301','YYYYMMDD')),
12 partition facturas_200903 values less than (to_date('20090401','YYYYMMDD')),
13 partition facturas_200904 values less than (to_date('20090501','YYYYMMDD')),
14 partition facturas_200905 values less than (to_date('20090601','YYYYMMDD')),
15 partition facturas_200906 values less than (to_date('20090701','YYYYMMDD')),
16 partition facturas_max values less than (maxvalue)
17 ) ;

Tabla creada.

rop@ROP11G> create table items (id_item number(8),
2 id_factura number(8) not null,
3 detalle varchar2(50),
4 importe number(15,2),
5 constraint facturas_fk
6 foreign key (id_factura)
7 references facturas(id_factura))
8 partition by reference (facturas_fk);

Tabla creada.

rop@ROP11G>

Como vemos para crear ITEMS particionada por referencia debemos crear una constraint referencial sobre id_factura. Además dicha columna debe ser not null.

Haciendo algunas consultas sobre el catalogo:

rop@ROP11G> select table_name,
2 partitioning_type,
3 partition_count,
4 ref_ptn_constraint_name
5 from user_part_tables
6 where table_name = 'ITEMS';

TABLE_NAME PARTITION PARTITION_COUNT REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------- ------------------------------
ITEMS REFERENCE 7 FACTURAS_FK

rop@ROP11G> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name in ('FACTURAS','ITEMS')
4 order by table_name,partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
FACTURAS FACTURAS_MAX MAXVALUE
FACTURAS FACTURAS_200901 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

FACTURAS FACTURAS_200902 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

FACTURAS FACTURAS_200903 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

FACTURAS FACTURAS_200904 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

FACTURAS FACTURAS_200905 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

FACTURAS FACTURAS_200906 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

ITEMS FACTURAS_MAX
ITEMS FACTURAS_200901
ITEMS FACTURAS_200902
ITEMS FACTURAS_200903
ITEMS FACTURAS_200904
ITEMS FACTURAS_200905
ITEMS FACTURAS_200906

14 filas seleccionadas.

Como se observa en el listado de arriba se crearon en la tabla ITEMS tantas particiones como en la tabla FACTURA. Eso se hizo automaticamente y en forma transparente.
Las constraints de referencia (fk's) y not null sobre la tabla ITEMS deben estar habilitadas y validadas y no pueden ser deferrables. Tampoco se puede particionar por referencia sobre columnas virtuales.



Particionamiento Compuesto

El partitioning existe desde la version 8. En esa versión solo se podía particionar por un nivel. Recien cuando apareció la versión 8i se agregó la funcionalidad que permite crear subparticiones lo cual aportó mayor granularidad. El tema era que solo permitia partionar/subparticionar por rango/hash. A partir de 9i se extendió la combinación de partición/subpartición a range/list (la posibilidad de particionar por list se introdujo en 9i). Tuvimos que esperar a 11g para no tener limitaciones para combinar particiones y subparticiones, es decir en 11g podemos usar cualquiera de las siguientes variantes:

* Range-range
* Range-hash
* Range-list
* List-range
* List-hash
* List-list

Veamos con un ejemplo, comparando 10g con 11g:

Voy a tratar de crear una tabla con particion por lista y subparticion por lista sobre una base 10g

rop@ROP10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

rop@ROP10G>

rop@ROP10G> create table movimientos (id_movimiento number(10),
2 id_caja number(8),
3 planta number(1),
4 ubicacion char(10),
5 estado char(2),
6 dia char(2),
7 mes char(2))
8 partition by list (planta)
9 subpartition by list (estado)
10 subpartition template
11 (
12 subpartition E1 values ('E1'),
13 subpartition E2 values ('E2'),
14 subpartition E3 values ('E3'),
15 subpartition E4 values ('E4'),
16 subpartition E5 values ('E5'))
17 (
18 partition mov_P1 values (1),
19 partition mov_P2 values (2),
20 partition mov_P3 values (3),
21 partition mov_P4 values (4)
22 ) ;
subpartition by list (estado)
*
ERROR en línea 9:
ORA-00922: falta la opción o no es válida

El intento, tal cual esperabamos, falló. Veamos ahora de hacerlo en 11g:

rop@ROP10G> conn rop@movi11d
Introduzca la contraseña: ******
Conectado.
rop@ROP11G> create table movimientos (id_movimiento number(10),
2 id_caja number(8),
3 planta number(1),
4 ubicacion char(10),
5 estado char(2),
6 dia char(2),
7 mes char(2))
8 partition by list (planta)
9 subpartition by list (estado)
10 subpartition template
11 (
12 subpartition E1 values ('E1'),
13 subpartition E2 values ('E2'),
14 subpartition E3 values ('E3'),
15 subpartition E4 values ('E4'),
16 subpartition E5 values ('E5'))
17 (
18 partition mov_P1 values (1),
19 partition mov_P2 values (2),
20 partition mov_P3 values (3),
21 partition mov_P4 values (4)
22 ) ;

Tabla creada.

La tabla se creo sin problemas. Veamos las vistas de catalogo para ver como quedó definida la nueva tabla:

rop@ROP11G> select table_name,partition_name,subpartition_name
2 from user_tab_subpartitions
3 where table_name = 'MOVIMIENTOS';

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MOVIMIENTOS MOV_P1 MOV_P1_E5
MOVIMIENTOS MOV_P1 MOV_P1_E4
MOVIMIENTOS MOV_P1 MOV_P1_E3
MOVIMIENTOS MOV_P1 MOV_P1_E2
MOVIMIENTOS MOV_P1 MOV_P1_E1
MOVIMIENTOS MOV_P2 MOV_P2_E5
MOVIMIENTOS MOV_P2 MOV_P2_E4
MOVIMIENTOS MOV_P2 MOV_P2_E3
MOVIMIENTOS MOV_P2 MOV_P2_E2
MOVIMIENTOS MOV_P2 MOV_P2_E1
MOVIMIENTOS MOV_P3 MOV_P3_E5
MOVIMIENTOS MOV_P3 MOV_P3_E4
MOVIMIENTOS MOV_P3 MOV_P3_E3
MOVIMIENTOS MOV_P3 MOV_P3_E2
MOVIMIENTOS MOV_P3 MOV_P3_E1
MOVIMIENTOS MOV_P4 MOV_P4_E5
MOVIMIENTOS MOV_P4 MOV_P4_E4
MOVIMIENTOS MOV_P4 MOV_P4_E3
MOVIMIENTOS MOV_P4 MOV_P4_E2
MOVIMIENTOS MOV_P4 MOV_P4_E1

20 filas seleccionadas.

rop@ROP11G> select table_name,partitioning_type,subpartitioning_type
2 from user_part_tables
3 where table_name = 'MOVIMIENTOS';

TABLE_NAME PARTITION SUBPART
------------------------------ --------- -------
MOVIMIENTOS LIST LIST

rop@ROP11G>


Si bien ahora no tenemos limitación en la combinación habría que pensar cual de esas combinaciones tiene sentido y si se puede aplicar a una situación real.

miércoles, 22 de julio de 2009

Reporte de sentencias que cambiaron su plan durante un cierto periodo de tiempo

Una de las posibles causas de baja de rendimiento de una base puede deberse a que una sentencia central haya cambiado su plan de ejecución. Es importante tener monitoreadas las sentencias mas importantes de manera de poder anticipar problemas de performance generalizado. Para poder observar los cambios de planes les paso el codigo de un script que armé que además de detectar los sqlid's que tienen más de un plan durante un cierto periodo, también genera el plan para poder chequerlo mas rapidamente


set term off
set serveroutput on size unlimited
set pagesize 4000
set echo off
set verify off
set feedback off
set line 1000
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
set term on

prompt *****************************************************************
prompt * Ingrese la cantidad de dias para analizar el cambio de planes *
prompt *****************************************************************
accept dias prompt 'Cantidad de Dias > '
prompt ***************************************************************************
prompt * Ingrese el nombre del archivo y ruta donde desea guardar la información *
prompt ***************************************************************************
accept path prompt 'Path > '

set term off;

spool '&path'


prompt
prompt ***************************************************************
prompt ************* Listado de Consultas y Planes *****************
prompt ***************************************************************
prompt


-- Query para detectar cambios de planes
select a.sql_id,a.timestamp,a.plan_hash_value,a.optimizer,a.cost
from dba_hist_sql_plan a
where a.sql_id in (select b.sql_id
from dba_hist_sql_plan b
where timestamp > trunc(sysdate-&dias)
group by sql_id
having count(distinct b.plan_hash_value) > 1)
and a.timestamp > trunc(sysdate-&dias)
and a.id = 0
order by 1,2;

prompt
prompt
prompt ***************************************************************
prompt ************* Detalle de Consultas y Planes *****************
prompt ***************************************************************
prompt


begin
for i in (select a.sql_id,a.timestamp,a.plan_hash_value,a.optimizer,a.cost
from dba_hist_sql_plan a
where a.sql_id in (select b.sql_id
from dba_hist_sql_plan b
where timestamp > trunc(sysdate-&dias)
group by sql_id
having count(distinct b.plan_hash_value) > 1)
and a.timestamp > trunc(sysdate-&dias)
and a.id = 0
order by 1,2)
loop
for j in (select plan_table_output
from table(dbms_xplan.display_awr(i.sql_id,i.plan_hash_value)))
loop
dbms_output.put_line(j.plan_table_output);
end loop;
end loop;
end;
/

set term on;
prompt Archivo de Salida del reporte --> "&path"
set term off;
set echo on
set verify on
set feedback on
set term on

viernes, 3 de julio de 2009

Nuevos Features en 11g para hacernos mas fácil el mantenimiento y upgrade de aplicaciones


En esta nota les voy a mostrar rapidamente 5 nuevos features de 11g. La mayoria de estos features esta relacionado con mejoras sobre actividades comunes en el mantenimiento y actualización de aplicaciones.


1. Permitir obtener valores de secuencias desde expresiones PL/SQL (Sequences from pl/sql expression)


Cualquier programador PL/SQL se habrá preguntado alguna vez porque hay que realizar un select seq. from dual para obtener el próximo valor o el valor corriente de una secuencia y no hacer una simple llamada desde una linea PL/SQL. Ahora en 11g se puede, y ya no es necesario invocar el motor sql para que nos devuelva el valor sino que se puede invocar con una simple expresión.

Primero veamos que pasa con una versión inferior a 11g

rop@DESA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

rop@DESA10G> create sequence seq;

Secuencia creada.

rop@DESA10G> declare
2 l_nval int;
3 begin
4 l_nval := seq.nextval;
5 end;
6 /
l_nval := seq.nextval;
*
ERROR en línea 4:
ORA-06550: línea 4, columna 27:
PLS-00357: La referencia a la tabla, vista o secuencia 'SEQ.NEXTVAL' no está permitida en este
contexto
ORA-06550: línea 4, columna 13:
PL/SQL: Statement ignored

El mensaje de error es claro y se ve que no es posible obtener el valor desde pl/sql.

Ahora, veamos que pasa en 11g:

rop@DESA11G> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

rop@DESA11G> create sequence seq;

Secuencia creada.

rop@DESA11G> declare
2 l_nval int;
3 begin
4 l_nval := seq.nextval;
5 end;
6 /
1

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> /
2

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G>

Ahora no falla y se obtiene el valor requerido de una forma mas simple.


2 - DDL con opcion de wait (DDL with wait)

Cuando se necesita cambiar la definición de una tabla (sentencia DDL) no puede existir transacciones activas (que no hayan hecho aun commit o rollback) porque sino la DDL fallará.
Eso suena logico ya que no puedo estar cambiando la definición si todavia quedan transacciones
pendientes. Antes de 11g el dba tenia que probar cada cierto tiempo y lograr que en el instante de cambio no hubiese ninguna transaccion activa. Un practica común es armar un bloque pl/sql que vaya probando cada n segundos hasta que se puede realizar con exito el cambio.
Primero vamos a ver que pasa en 10g, voy a usar dos sesiones, desde la primera sesión realizo una transaccion y no la confirmo hasta pasados 30".

En 10g:

Sesion 1:

rop@DESA10G> create table t (x varchar2(1));

Tabla creada.

rop@DESA10G> begin
2 insert into t values ('1');
3 dbms_lock.sleep(30);
4 commit;
5 end;
6 /

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

En la segunda sesion intento modificar la definición de la tabla agregandole modificando el tipo de datos.

Sesion 2:

rop@DESA10G> alter table t modify x varchar2(2);
alter table t modify x varchar2(2)
*
ERROR en línea 1:
ORA-00054: recurso ocupado y obtenido con NOWAIT especificado

No me dejó. Espero 30" y vuelvo a correr la ddl:

rop@DESA10G> /

Tabla modificada.

rop@DESA10G>

Recien una vez que se confirmó la transaccion pude modificar la tabla.
Ahora le voy a mostrar como hacerlo en 11g aprovechando el nuevo feature:

En 11g:

Sesion 1:

rop@DESA11G> create table t (x varchar2(1));

Tabla creada.

rop@DESA11G> begin
2 insert into t values ('1');
3 dbms_lock.sleep(30);
4 commit;
5 end;
6 /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G>

La sesion 1 es exactamente igual que la anterior. En la sesion 2 voy a definir el parametro ddl_lock_timeout a 60" y voy a correr al ddl:

Sesion 2:

rop@DESA11G> alter session set ddl_lock_timeout=60;

Sesión modificada.

rop@DESA11G> alter table t modify x varchar2(2);

Tabla modificada.

rop@DESA11G>

Como se vio, no tuve que intentar nuevamente, es el mismo Oracle el que se encarga de verificar cuando se puede alterar la tabla y lo hace automaticamente sin necesidad de intervención del dba.


3. Indices Invisibles (Invisible Indexes)

Otro feature interesante introducido en 11g es la posibilidad de crear indices invisibles. Muchos se preguntaran para que puede servir eso. Bueno, en principio, se podria crear un indice invisible sobre una tabla productiva y analizar a nivel sesion como cambia el plan y si se aprovecharia es nuevo indice para mejorar los accesos. Es importante aclarar que si bien el indice es invisible se mantiene actualizado con cada dml sobre la tabla donde esta definido. El indice es solo invisible para el optimizador, es decir no es tomado en cuenta en el parsing de las sentencias.

Vamos a ver su uso con un ejemplo. Como siempre voy a crear una tabla de 1M de filas con valores ficticios

rop@DESA11G> create table t (x int,y char(50));

Tabla creada.

rop@DESA11G> insert into t
2 select rownum,
3 dbms_random.string('a',30)
4 from dual
5 connect by rownum <= 1000000; 1000000 filas creadas. rop@DESA11G> commit;

Confirmación terminada.

rop@DESA11G> explain plan for select max(x) from t where y = 'A';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2505 (2)| 00:00:31 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS FULL| T | 100 | 6500 | 2505 (2)| 00:00:31 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("Y"='A')

Note
-----
- dynamic sampling used for this statement

18 filas seleccionadas.

rop@DESA11G> create index t_y_idx on t(y) invisible;

Índice creado.

rop@DESA11G> explain plan for select max(x) from t where y = 'A';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2505 (2)| 00:00:31 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS FULL| T | 100 | 6500 | 2505 (2)| 00:00:31 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("Y"='A')

Note
-----
- dynamic sampling used for this statement

18 filas seleccionadas.

El indice es invisible y como se observó mas arriba no fue tenido en cuenta por el optimizador. Ahora hagamoslo visible:


rop@DESA11G> alter index t_y_idx visible;

Índice modificado.

rop@DESA11G> explain plan for select max(x) from t where y = 'A';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 767882303

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_Y_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("Y"='A')

Note
-----
- dynamic sampling used for this statement

19 filas seleccionadas.

rop@DESA11G> select index_name,visibility from user_indexes where table_name = 'T';

INDEX_NAME VISIBILIT
------------------------------ ---------
T_Y_IDX VISIBLE

Cuando lo hicimos visible el indice fue usado para armar el plan.


rop@DESA11G> alter index t_y_idx invisible;

Índice modificado.

rop@DESA11G> select index_name,visibility from user_indexes where table_name = 'T';

INDEX_NAME VISIBILIT
------------------------------ ---------
T_Y_IDX INVISIBLE

rop@DESA11G> explain plan for select max(x) from t where y = 'A';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2505 (2)| 00:00:31 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS FULL| T | 100 | 6500 | 2505 (2)| 00:00:31 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("Y"='A')

Note
-----
- dynamic sampling used for this statement

18 filas seleccionadas.


Veamos como trabajar con su visibilidad a nivel sesion:

rop@DESA11G> sho parameter optimizer_use_invisible_indexes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
rop@DESA11G> alter session set optimizer_use_invisible_indexes=true;

Sesión modificada.

rop@DESA11G> explain plan for select max(x) from t where y = 'A';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 767882303

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_Y_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("Y"='A')

Note
-----
- dynamic sampling used for this statement

19 filas seleccionadas.

rop@DESA11G>


Siempre que se piensa en agregar un indice hay que analizar el trade-off que existe entre el beneficio de un acceso por indice contra la degradación en las dml's sobre la tabla, ya que ahora habrá un nuevo indice que mantener en cada update/delete/insert/merge. Se puede crear invisible, de forma tal de no repercutir en los planes y ver como es afectada la operatoria dml sobre la tabla y luego ver si conviene ponerlo visible o directamente borrarlo.


4. Tablas de solo lectura (Read Only Tables)

No hay mucho para contarles sobre este nuevo feature, solo para considerar que ahora en 11g
no solo se puede poner la base de datos o algun tablespace en read only sino que podemos ir
mas fino en la granularidad y alterar una tabla para que solo se pueda consultar. Ahi va un ejemplo bien sencillo:


rop@DESA11G> alter table t read only;

Tabla modificada.

rop@DESA11G> insert into t values (1,'a');
insert into t values (1,'a')
*
ERROR en línea 1:
ORA-12081: no se permite la operación de actualización en la tabla "ROP"."T"


rop@DESA11G> alter table t read write;

Tabla modificada.

rop@DESA11G> insert into t values (1,'a');

1 fila creada.

rop@DESA11G> commit;

Confirmación terminada.

rop@DESA11G>


5 - Dependencia entre objetos mas fina (Fine Grained Dependencies)

Quien ha trabajado algunos años con Oracle y haya tenido que realizar modificaciones "en caliente" de algún objeto en producción, seguramente se ha encontrado con invalidaciones de objetos relacionados directa o indirectamente con el objeto modificado. Esto puede tener implicaciones terribles en producción y literalmente "freezar" la actividad sobre las base. En lo posible es recomendable realizar cambios en periodos de minima actividad o idealmente en ventanas de mantenimiento, si las hubiera, para asi evitar riesgos.

Veamos una invalidación en 10g que no es lógica, ya que vamos a ver como una vista definida sobre un select de dos campos de una tabla se invalida al agregar un tercer campo en la tabla que no tiene nada que ver con la vista.

En 10g:

rop@DESA10G> create view t_v as select x,y from t;

Vista creada.

Transcurrido: 00:00:00.17
rop@DESA10G> select status from user_objects where object_name = 'T_V';

STATUS
-------
VALID

Transcurrido: 00:00:00.10
rop@DESA10G> alter table t add z date;

Tabla modificada.

Transcurrido: 00:00:00.60
rop@DESA10G> select status from user_objects where object_name = 'T_V';

STATUS
-------
INVALID

Transcurrido: 00:00:00.10
rop@DESA10G> select count(1) from t_v;

COUNT(1)
----------
1000000

Transcurrido: 00:00:00.89
rop@DESA10G> select status from user_objects where object_name = 'T_V';

STATUS
-------
VALID

Transcurrido: 00:00:00.09
rop@DESA10G>

La vista se invalidó y recien cuando se invocó por primera vez se compiló ok. Imaginemos que de esta vista pueden depender objetos de código como stored procedures, paquetes, funciones, etc y que se descompiladaran en cascada.

En 11g la dependencia es mas fina y evita invalidaciones innecesarias:

En 11g:

rop@DESA11G> create view t_v as select x,y from t;

Vista creada.

Transcurrido: 00:00:00.18
rop@DESA11G> select status from user_objects where object_name = 'T_V';

STATUS
-------
VALID

Transcurrido: 00:00:00.15
rop@DESA11G> alter table t add z date;

Tabla modificada.

Transcurrido: 00:00:00.09
rop@DESA11G> select status from user_objects where object_name = 'T_V';

STATUS
-------
VALID

Transcurrido: 00:00:00.07
rop@DESA11G>