lunes, 11 de julio de 2011

Cuando el desconocimiento cuesta parte II

Que tal amigos gusto en saludarlos a todos nuevamente, ante todo me excuso por haberme ausentado un tiempo de escribir artículos. Hoy pienso continuar con el artículo cuando el desconocimiento cuesta.
En mi opinion la mayoría de los proyectos culminaría con total éxito, sino se cometieran 2 errores:
1) Contratar personal con poco o ningún conocimiento técnico (me he encontrado con muchas compañías que por "ahorrar" costos de nómina contratan personal sin experiencia, esto no sería problema si se invierte en desarrollarlos, el problema es que este tipo de inversión tampoco se hace).
2) Gerentes de proyecto que tienen menos conocimiento que el personal contratado para el punto 1 (a esto pienso no hacer comentarios, así que todo se lo dejaré a su imaginación).
Apartando esto este artículo va dirigido a las personas del punto número 1, pero que realmente tienen hambre de conocimiento....
En muchas compañías se encontrarán con que "directores", están necesitando tal o cual reporte para ayer...Por tanto omiten responder algunas preguntas necesarias para un buen diseño, tales como:
1) Con cuanta frecuencia se ejecutará ese reporte?.
2) En qué tiempo se necesita correr dicho reporte?
3) Concurrencia....Cuantos usuarios ejecutarán de forma simultánea dicho reporte?
4) Escalabilidad....Cuanto crecerán los datos involucrados en dichos reportes en el tiempo?
Bueno me imagino como estará su sistema si ud. no preguntó estas pequeñas 4 preguntas.....sin embargo es importante que ud. sepa, que el diseño de una consulta o reporte, NO DEBE OBEDECER NUNCA a 2 criterios dañinos:
1) Ya funciona!!! porque sólo trajo los registros que ud. necesita.
2) NUNCA UTILICE SOLO EL RELOJ PARA MEDIR EL TIEMPO DE LA CONSULTA.
En mi opinión si ud. toma encuenta lo descrito anteriormente, estoy seguro, que ud. está encaminado a diseñar buenas consultas. Ahora, con qué herramientas cuento para ello?...Qué tomo en cuenta para desarrollar buenas consultas??...bien respondamos algunas preguntas de este tipo....
1) Evite Full Table Scan (FTS), para ello tome encuenta,

Si los accesos a estas tablas se realizan con cláusulas “where” (toda consulta que posee filtro, al filtro se le denomina predicado), en donde se busca obtener un subconjunto de los datos de la tabla, se deben crear índices adecuados a estos predicados, a fin de evitar el acceso a la tabla a través de un “full scan”, lo cual degrada sensiblemente el rendimiento. Para ello evalue las consultas que son más concurrentes en la aplicación, es decir, cual es la actividad más realizada por el aplicativo durante el día. Una vez detectada por favor verifique la consulta tiene el tipo de índice adecuado, utilizando la instrumentación de Oracle para ello, por ejemplo:
  1. Asumamos que la consulta es la siguiente:

SELECT * FROM HR.EMPLOYEES

WHERE LAST_NAME='Gates';


Ud. Puede utilizar herramientas como el explain plan, para verificar el plan de ejecución que “debería” utilizar el optimizador de costos del manejador de base de datos Oracle (CBO), por favor note que se utiliza la palabra debería, pues el explain plan sirve para hacer estimaciones, sin embargo, en muchas ocaciones, no muestra lo que realmente hace la sentencia. Si la consulta es altamente concurrente (muchos usuarios la ejecutan durante el día y al mismo tiempo), entonces ud. Debe medir el impacto de tal sentencia, en recursos consumidos del manejador (CPU, Memoria, I/O) y que el plan de ejecución sea el más óptimo.

Para medir recursos se puede de la siguiente forma:

SQL>set autotrace traceonly statistics;

SQL>SELECT *

2> FROM HR.EMPLOYEES

3> WHERE LAST_NAMES='Gates';



la salida debería ser algo como esto:

Statistics


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


21 recursive calls


42 db block gets


17 consistent gets


0 physical reads


13224 redo size


1126 bytes sent via SQL*Net to client


419 bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


1 rows processed



Para verificar el plan de ejecución ud. Puede hacerlo de la siguiente forma:


SQL> explain plan for


2 select * from hr.employees


3 where last_name='Gates';




Explained.


SQL> select * from table(dbms_xplan.display);




PLAN_TABLE_OUTPUT


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


Plan hash value: 2077747057




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


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


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


| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |


| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |


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


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




Predicate Information (identified by operation id):




PLAN_TABLE_OUTPUT


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


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




2 - access("LAST_NAME"='Gates')




Note


-----


- SQL plan baseline "SQL_PLAN_5g0nnhjc87sm0d8aea094" used for this statement




18 rows selected.



Analizando las salidas podemos obtener la siguiente información:

a) En cuando a las estadisticas:

  • Note los recursos invertidos en procesar la consulta están claramente definidos; ud debe evitar que las consultas tengan excesivas lecturas a disco (physical reads), ya que son más costosas y repercuten directamente en el tiempo de respuesta de la base de datos en procesar su consulta.

  • Note la cantidad de bloques ordenados en disco sorts (disk), Oracle database tiene un area de ordenamiento para la memoria principal denominada pga_aggregate_target (a partir de 11G viene incluida dentro del manejo automático de memoria si se tiene configurado-memory_target). Los ordenamientos en disco, siempre son costosos para el manejador.


Los siguientes aspectos de las estadisticas:

1126 bytes sent via SQL*Net to client


419 bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


Pertenecen a la cantidad de bytes enviados por RED y por Fetch, ud debe reducir la cantidad de fetch (Fetch es la fase de procesamiento de una sentencia sql, encargada de retornar los registros solicitados), note que en esta salida (

SQL*Net roundtrips to/from client) indica que debe hacer 2 fetch para traer un registro, esto se puede reducir haciendo uso de un arraysize grande. En el siguiente ejemplo ud. Podrá apreciar el impacto que tiene un aumento del arraysize en la cantidad de fetch y bytes enviados por servicios de RED:



SQL> set autotrace traceonly statistics;


SQL> select * from hr.employees;




107 rows selected.






Statistics


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


0 recursive calls


0 db block gets


15 consistent gets


0 physical reads


0 redo size


9835 bytes sent via SQL*Net to client


496 bytes received via SQL*Net from client


9 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


107 rows processed




SQL> set arraysize 5000


SQL> /




107 rows selected.






Statistics


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


0 recursive calls


0 db block gets


8 consistent gets


0 physical reads


0 redo size


8925 bytes sent via SQL*Net to client


419 bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


107 rows processed




SQL>

Si ud desea conocer más información a cerca del arraysize, por favor, diríjase a www.oracle.com y descargue la guía de sql para bases de datos 11gr2.

Es importante conocer que los usuarios podrán utilizar este tipo de instrumentación para las consultas, otorgandoles el rol PLUSTRACE, que se crea ejecutando el script $ORACLE_HOME/sqlplus/admin/plustrce.sql.

  1. Adicionalmente ud. A través del explain plan, puede observar los caminos que sigue el COB, al procesar la sentencia, esto le permitirá observar donde se hacen Full Table Scan (FTS), donde no se están tomando los índices adecuados y finalmente el costo (tasa que medida en inversión de CPU y cantidad de operaciones de I/O), y el tiempo promedio de proceso en la consulta; una salida de ejemplo sería como sigue a continuación:


PLAN_TABLE_OUTPUT


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


Plan hash value: 1445457117




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


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


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


| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |


| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |


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




Note


-----




PLAN_TABLE_OUTPUT


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


- SQL plan baseline "SQL_PLAN_9kthr3p6smf74cf314e9e" used for this statement




12 rows selected.

.

Por favor observe que la actividad de TABLE ACCESS FULL hace referencia al FTS, y la columna name indica el objeto sobre el cual se realiza esta operación.

Al tener esta información verifique que los indices están creados adecuadamente (sobre las columnas involucradas en el predicado de la consulta).


Espero realmente que esto le sea de ayuda para iniciarse en el arte de diseñar buenas consultas....


Saludos a todos...

jueves, 24 de marzo de 2011

Cuando el desconocimiento cuesta.

En muchas situaciones me he encontrado ante clientes que manifiestan su alta preocupación porque la base de datos está "lenta". Lo primero que todo cliente debe conocer es que la palabra "lenta" para un administrador de base de datos, es una palabra que realmente no dice nada....Es cómo llevar un automovil al mecánico y decirle: "no está funcionando bien así que te lo dejaré y lo pasaré buscando más tarde", seguramente ud. ya sabe lo que pasará cuando esa persona recoja el automovil...Así que lo primero que se debe definir es cuanto es lento???, para ello ud debe reformularse su problema. Le daré un pequeño ejemplo, existe un reporte que se ejecuta al final del día el cual muestra el total de ventas del día de una sucursal de una tienda por departamentos, el mismo debe ejecutarse al cerrar las cajas de ventas (esto debe ser cuando se cierre la tienda para este ejemplo asumiremos a las 7pm), y todos los días existe una reunión de comité que evalúa las ventas de las sucursales a las 7:30 pm, ud ya debe intuir de que dicho reporte de ventas debe estar en las manos del gerente a mas tardar a las 7:10 pm, es decir tiene un máximo de 5 minutos para correr el mismo. En este caso la palabra "lento" deberia ser algo como esto: "El reporte debe ejecutarse en un máximo de 5 minutos, y está tardando 15 min"....suena diferente, cierto??.
Entonces ud debe definir como corporación muy bien su "meta" para poder comenzar a entonar, ese será nuestro primer paso....
Ahora bien una vez definida la meta, quiero conversarles a cerca de un problema muuuuyyyy común, en cuanto a rendimiento de una base de datos se refiere:
Cómo escribir SQL adecuados:
Probablemente al leer esa frase, venga a su mente como Administrador de base de datos que es algo como esto: "Esa no es su responsabilidad sino la del desarrollador", además el DBA en muchos casos "no puede tocar el código fuente", aunque inicialmente tal declaración parezca cierta dejeme informarle, que la primera persona a la que llamarán cuando los procesos tarden más de lo acordado en su ejecución, será a ud., y muy probablemente sus alegatos no sean comprendidos por el usuario final. Por lo que nuestro primer paso debe ser entender que todo código escrito en la base de datos finalmente debe ser nuestra responsabilidad...Ahora bien, como comente anteriormente un problema muy común es encontrarnos con SQL que necesitan ser re-escritos...Esto tiene mucha tela que cortar, así que iré al grano....Para ello comenzaré con nombrar algunos errores comunes al escribir sentencias SQL:
1) No utilizar variables bind (sobre esto ud hay mucha información escrita por el gurú Tom Kyte en su blog: http://asktom.oracle.com), por lo que no ahondaré mucho en este tema).
2) No utilizar querys escalares: los queries escalares son mejor conocidos como "Select de Select", sin embargo muchos desarrolladores ignoran el hecho de que se pueden ahorrar muchos JOIN los cuales son más costosos con el uso de estos queries escalares, y mejor aún los mismos pueden ser almacenados y ser reutilizados...un pequeño ejemplo, que lo ilustrará ahora:
Supongamos 2 tablas a las que llamaremos t1 y t2:
SQL> CREATE TABLE t10 ( a VARCHAR2(10), b VARCHAR2(10) );

Table created.

SQL> CREATE TABLE t20( b VARCHAR2(10), b_detalle VARCHAR2(100) );

Table created.

SQL> INSERT INTO t10 ( a, b ) VALUES ( 'A1', 'B1' );

1 row created.

SQL> INSERT INTO t10 ( a, b ) VALUES ( 'A1', 'B2' );

1 row created.
SQL> ed
Wrote file afiedt.buf

1* INSERT INTO t20 ( b, b_detalle ) VALUES ( 'B1', 'PARA PROBAR ESTOS VALORES' )
SQL> /

1 row created.

Ahora bien, supongamos que necesitamos una consulta, que nos muestre todo lo que esté en t10 y t20, por lo que es probable que este pensando en algo como esto:

SELECT t10.a, t10.b, t20.b_detalle
FROM
t10
LEFT OUTER JOIN t20 ON ( t20.b = t10.b );

Inicialmente esto puede parecer una buena idea, sin embargo a medida que los outer join se complican los costos son mayores, inspeccionemos el plan de ejecución de tal sentencia:
Execution Plan
----------------------------------------------------------
Plan hash value: 157079380

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 146 | 5 (20)| 00:00:01 |
|* 1| HASH JOIN OUTER | | 2 | 146 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T10 | 2 | 28 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T20 | 1 | 59 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("T20"."B"(+)="T10"."B")

Por favor note el costo en CPU que ocasiona el OUTER JOIN, ahora veremos que ocurre cuando utilizamos queries escalares:
Misma consulta re-escrita:

SELECT t10.a, t10.b, ( SELECT t20.b_detalle FROM t20 WHERE t20.b = t10.b ) b_detalle
FROM t10;
Inspeccionemos su plan de ejecución:
Execution Plan
----------------------------------------------------------
Plan hash value: 1399811295

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 28 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T20| 1 | 59 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T10 | 2 | 28 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("T20"."B"=:B1)

Note
-----
- dynamic sampling used for this statement (level=2)

Por favor note como el costo en CPU es mucho menor, inicialmente ud pensará que no es muy grande la mejora, pero espere, recuerde 2 cosas:
1) Esto no es un ambiente de producción por lo que el outer join es muy sencillo, pero qué pasaría si lo complicamos más, el costo sería muchisimo mayor....
2) En las sentencias SQL, no debe ser solo cuestion de cuanto tiempo tarda una consulta en ejecutarse, sino de recursos y escalabilidad, además tenga en cuenta que todo suma; por ejemplo que sucedería si la misma consulta la ejecutan 500 usuarios de forma simultánea en una 1 hora determinada, seguro se asustará cuando vea los resultados, en todo caso, con esto me basta para mostrarle a ud, cuanto lo puede ayudar conocer un poco más a cerca de como escribir sus SQL.

Espero les haya sido útil, les mostraré más en futuras entregas...
Saludos Cordiales