Costo en un plan de ejecución

En este post analizaremos qué es ese número que nos aparece en la columna de Cost cuando revisamos los planes de ejecución de alguna consulta.

¿Dónde se ve el costo?

Para comenzar, debemos mostrar un plan de ejecución de una consulta. Es necesario hacer notar que todo desarrollador que crea algún programa que traerá información de una base de datos Oracle, deberá siempre generar los planes de ejecución de sus consultas, para saber que están o no eficientes y corregirlas en éste último caso.

En el plan de ejecución se puede observar una columna llamada Cost, que corresponde al costo de la consulta por cada paso que realiza en el plan de ejecución, así como el total final:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

Plan hash value: 1360010470

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     2 |   188 |     7  (15)| 00:00:01 |
|   1 |  NESTED LOOPS                  |         |     2 |   188 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN                    |         |     2 |   144 |     6  (17)| 00:00:01 |
|   3 |    INLIST ITERATOR             |         |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    78 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL           | BONUS   |     2 |    66 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN           | PK_DEPT |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("E"."ENAME"="B"."ENAME")
   5 - access("E"."EMPNO"=7369 OR "E"."EMPNO"=7654)
   8 - access("E"."DEPTNO"="D"."DEPTNO")

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

26 rows selected.

Fórmula del Costo

Pero, ¿cómo se determina ese costo? ¿qué es? Pongo a continuación la fórmula de cómo es calculado:

La traducción al español es:

El costo es una unidad de tiempo, es igual a

la multiplicación de la cantidad de lecturas de bloque simple por el tiempo que tarda una lectura de bloque simple,

más la multiplicación de la cantidad de lecturas de bloques múltiples por el tiempo que tarda una lectura de bloques múltiples,

más la división de la cantidad de ciclos del procesador entre la velocidad del procesador

todo lo anterior, dividido entre el tiempo que tarda una lectura de bloque simple.

De esta forma, es como se conforma el costo, no es más que una unidad de tiempo que predice cuánto se llevará una consulta al ejecutarse. No corresponde a segundos o alguna otra unidad de tiempo, aunque en el mismo plan de ejecución se puede ver una predicción de este también.

¿Cuánto es un costo adecuado?

Al final, el costo deberá tender a ser lo más pequeño posible. Mientras más álto sea este, más lenta será una consulta.

También hay que considerar que el costo es relativo. Por ejemplo, para un FULL TABLE ACCESS sobre una tabla de 500 registros puede tener un costo de 10, muy pequeño e irrisorio, pero ineficiente. A esta tabla después de agregarle un índice deberá bajar el costo:

Por ejemplo, suponiendo que tenemos las siguientes tablas resultado de uno de mis Scripts útiles para un DBA:

SQL> star rowcnt prueba % %
SQL> set echo   off
------------------------------------------------------------------------------|
Script:  RowCnt | Muestra la cantidad de registros de la(s) tabla(s) parametro.
Uso:     rowcnt propietario tablespace|% nombre_tabla|%
Ejemplo: rowcnt prueba ts_datos|% emp|%
------------------------------------------------------------------------------|

TABLA           TOTAL
--------- -----------
BONUS               2
DEPT                4
EMP                14
SALGRADE            5
T_OBJ1M     1,075,200
T_OBJ500          500
T_OBJECTS       4,199

de las cuales, tomaremos a T_OBJ500 para demostrar el punto. Hay que ver si tiene índices para hacer nuesta demostración, usaré otro de mis Scripts útiles para un DBA:

SQL> star idxtab t_obj500
SQL> set echo   off
------------------------------------------------------------------------------|
Script:  IdxTab | Muestra los indices y sus columnas, de una tabla.
Uso:     idxtab nombre_tabla
Ejemplo: idxtab emp
------------------------------------------------------------------------------|

no rows selected

Como se puede ver, no cuenta con estos. Estos scripts se corren con el usuario SYS todopoderoso. Ahora vamos al usuario PRUEBA para realizar la demostración. Para hacer esto, vamos a “consultar” información sobre la tabla comentada:

SQL> explain plan for
  2  select *
  3  from   t_obj500
  4  where  object_name = :v_object_name;

Explained.

SQL> star xplan    <-- Como se puede apreciar, vuelvo a usar otro de mis Scripts útiles para un DBA
SQL> set echo off
------------------------------------------------------------------------------|
Script:  XPlan | Muestra el plan de ejecucion despues de un 'explain plan for'
Uso:     xplan
------------------------------------------------------------------------------|

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

Plan hash value: 1802624142

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     5 |   640 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_OBJ500 |     5 |   640 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"=:V_OBJECT_NAME)

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

17 rows selected.

Con lo cual, se puede ver que sí está haciendo un TABLE ACCESS FULL con un costo de 6. Ahora, después de crearle un índice, ¿qué pasa?:

SQL> create index t_obj500_pk on t_obj500 (object_name)
  2  tablespace indices;

Index created.

SQL> explain plan for
  2  select *
  3  from   t_obj500
  4  where  object_name = :v_object_name;

Explained.

SQL> star xplan
SQL> set echo off
------------------------------------------------------------------------------|
Script:  XPlan | Muestra el plan de ejecucion despues de un 'explain plan for'
Uso:     xplan
------------------------------------------------------------------------------|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

Plan hash value: 4044639280

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     5 |   640 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ500    |     5 |   640 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_OBJ500_PK |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"=:V_OBJECT_NAME)

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

18 rows selected.

Con lo que se puede ver que el costo ha disminuido sustancialmente, de momento y a grandes rasgos, este ya es un query eficiente.

Sin embargo por otro lado, un query eficiente que va por índice pero que trae millones de registros, puede tener un costo también eficiente de 280 fácilmente, siendo este el mejor resultado que se puede obtener. Si una consulta tan grande como esta no tuviera índices se podría ir a un costo de miles o diez miles que ya es malísimo. Para esto, tomaremos la tabla que tiene más de un millón de registros de nuestro listado anterior obtenido con uno de mis Scripts útiles para un DBA:

SQL> star rowcnt prueba % %
SQL> set echo   off
------------------------------------------------------------------------------|
Script:  RowCnt | Muestra la cantidad de registros de la(s) tabla(s) parametro.
Uso:     rowcnt propietario tablespace|% nombre_tabla|%
Ejemplo: rowcnt prueba ts_datos|% emp|%
------------------------------------------------------------------------------|

TABLA           TOTAL
--------- -----------
BONUS               2
DEPT                4
EMP                14
SALGRADE            5
T_OBJ1M     1,075,200
T_OBJ500          500
T_OBJECTS       4,199

Para esta tabla, vamos a revisar con qué índices cuenta:

SQL> star idxtab T_OBJ1M;
SQL> set echo   off
------------------------------------------------------------------------------|
Script:  IdxTab | Muestra los indices y sus columnas, de una tabla.
Uso:     idxtab nombre_tabla
Ejemplo: idxtab emp
------------------------------------------------------------------------------|

no rows selected

Como se puede ver, tampoco los tiene, así que vamos a mostrar el plan de ejecución para un query similar al del anterior ejemplo:

SQL> explain plan for
  2  select *
  3  from   t_obj1m
  4  where  object_name = :v_object_name;

Explained.

SQL> star xplan
SQL> set echo off
------------------------------------------------------------------------------|
Script:  XPlan | Muestra el plan de ejecucion despues de un 'explain plan for'
Uso:     xplan
------------------------------------------------------------------------------|

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 3771009113

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 11269 |  1408K|  5228   (2)| 00:01:14 |
|*  1 |  TABLE ACCESS FULL| T_OBJ1M | 11269 |  1408K|  5228   (2)| 00:01:14 |
-----------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"=:V_OBJECT_NAME)

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

17 rows selected.

Con esto se puede observar que al hacer un query similar que antes nos daba un costo de 6, ahora por tener más registros, sube a 5228. Esto pasa porque no es lo mismo buscar un valor en 500 registros que en más de un millón.

Vamos a repetir el paso de crearle un índice y ver qué pasa:

SQL> create index t_obj1m_pk on t_obj1m (object_name)
  2  tablespace indices;

Index created.

SQL> explain plan for
  2  select *
  3  from   t_obj1m
  4  where  object_name = :v_object_name;

Explained.

SQL> star xplan
SQL> set echo off
------------------------------------------------------------------------------|
Script:  XPlan | Muestra el plan de ejecucion despues de un 'explain plan for'
Uso:     xplan
------------------------------------------------------------------------------|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

Plan hash value: 3429448970

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 11269 |  1408K|  2156   (1)| 00:00:31 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ1M    | 11269 |  1408K|  2156   (1)| 00:00:31 |
|*  2 |   INDEX RANGE SCAN          | T_OBJ1M_PK |  4508 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"=:V_OBJECT_NAME)

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

18 rows selected.

Como se puede ver, ya la consulta se va a través del índice que se creó T_OBJ1M_PK. Esto ya es eficiente como tal, sin embargo, con la bind variable v_object_name, nos dá apenas un costo de 2156. Pero, ¿qué pasa si hacemos el mismo query con un valor puntual? Vamos a hacer el experimento de usar un valor duro:

SQL> select object_name
  2  from   t_obj1m
  3  where  rownum < 5;

OBJECT_NAME
------------------------------
AAADGBTyNncpOyZKLzDJsadjUNePoU
AAAHLLfNTBwJixalYYOPSEaLuKXmGy
AAALkRvMJztWbSDjBgQjSSlgHCBiGm
AAAMTmEnpfceYTbLVHPuANbgdyOmGL

4 rows selected.

SQL> explain plan for
  2  select *
  3  from   t_obj1m
  4  where  object_name = 'AAADGBTyNncpOyZKLzDJsadjUNePoU';

Explained.

SQL> star xplan
SQL> set echo off
------------------------------------------------------------------------------|
Script:  XPlan | Muestra el plan de ejecucion despues de un 'explain plan for'
Uso:     xplan
------------------------------------------------------------------------------|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

Plan hash value: 3429448970

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   128 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ1M    |     1 |   128 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_OBJ1M_PK |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='AAADGBTyNncpOyZKLzDJsadjUNePoU')

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

18 rows selected.

Como se puede ver, el costo ya tiene un valor muy pequeño a pesar de que la tabla tenga más de un millón de registros.

Conclusiones

El objetivo de este post, es mostrar el costo y cómo se ve afectado por la forma en cómo accedemos a los datos en una base de datos Oracle. Siempre un costo deberá ser lo más pequeño posible y hay que evitar al máximo los costos altos.

Posteriormente publicaré un post con respecto de los planes de ejecución.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

6 Responses to Costo en un plan de ejecución

  1. bobymetal says:

    excelente articulo…. me has iluminado con esta breve explicacion de costos…..

  2. orlandoolguin says:

    Hola Bobymetal,

    Me alegro que te haya servido, date una vuelta por el otro post de LIOs. PIOs & Hit Ratio.

    Orlando.

  3. oscarlennon says:

    Yeah!!! está muy interesenta , legible y entendible tu entrada, junto a la de los traces me han sido de gran utilidad .

    Gracias Orlis XD

  4. orlandoolguin says:

    Hola Oscar,

    Qué bueno que te haya sido de utilidad.

    Orlando.

  5. ectel says:

    muy buen articulo,… me agrado mucho el que hayas colocado la formula de costo, sabes donde puedo encontrar esa formula en la documentación oficial de Oracle ??

  6. Orlando Olguín Olvera says:

    Hola Ectel.

    No viene la fórmula en la documentación de Oracle, esto lo han expuesto los gurús de Oracle como Jonathan Lewis.

    Orlando.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: