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...