lunes, 17 de septiembre de 2007

Estadísticas en tablas temporales

Lo ideal es tener estadísticas "representativas" en las tablas temporales. Suele suceder que muchas veces no sabemos la cantidad de registros que se van a cargar en las tablas temporales, pero podemos tener estadísticas que representen la cantidad de registros que en general suelen cargarse en las tablas. Por otro lado, hay ocasiones en que no sabemos ni eso. No sabemos que cantidad de registros promedio se van a cargar en las tablas. Cuando sucede ésto podemos pensar en algunas alternativas.

Por default, Oracle asume que las tablas temporales van a contener X cantidad de registros (donde X representa a 8,168 de registros en las bases de datos que contiene bloques de 8K).
Como éste valor por default suele ser incierto en las ejecuciones de nuestros procesos utilizando tablas temporales, tenemos que ayudar al CBO en recolectar estadísticas reales de las tablas temporales.

Antes que nada, veamos las estadísticas por default que utiliza el CBO:

SQL_9iR2> SHOW PARAMETER DB_BLOCK_SIZE

db_block_size integer 8192

SQL_9iR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ ALL_ROWS */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 103K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 8168 | 103K| 11 |
--------------------------------------------------------------------

Observamos lo que explicamos anteriormente. El CBO estima las estadísticas en base al parámetro DB_BLOCK_SIZE seteado en la base de datos.

Las 3 soluciones disponibles en lo que concierne a las estadísticas en las tablas temporales son:
1.- Usar el hint DYNAMIC_SAMPLING
2.- Usar el hint CARDINALITY
3.- Usar DBMS_STATS.SET_TABLE_STATS

Veamos cada una de éstas soluciones:

1.- Usar el hint DYNAMIC_SAMPLING

Dynamic Sampling le dice a Oracle que realice rápidamente un "escaneo completo" de la tabla para obtener estadísticas que representen la realidad.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ DYNAMIC_SAMPLING(2) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 1 | 13 | 11 |
--------------------------------------------------------------------

Utilizando Dynamic Sampling (nivel 2), podemos ver que las estadísticas son más representativas de la realidad.

Veamos qué sucede si insertamos 15,000 registros a la tabla temporal:

SQL_9iR2> INSERT INTO test_temp
2 SELECT level
3 FROM dual
4 CONNECT BY level <= 15000 ;

15000 rows created.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ DYNAMIC_SAMPLING(2) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15000 | 190K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 15000 | 190K| 11 |
--------------------------------------------------------------------

En 10g, el CBO obtiene por default, estadísticas utilizando Dynamic Sampling (nivel 2) sobre las tablas temporales.

SQL_10gR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_10gR2> EXPLAIN PLAN FOR
2 SELECT id
3 FROM test_temp ;

Explained.

SQL_10gR2> @explains
Plan hash value: 1559088631

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_TEMP | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2.- Usar el hint CARDINALITY

Otra solución es utilizar el hint CARDINALITY, que le dice a Oracle la cantidad de registros que tiene la tabla. Pero éste valor debemos colocarlos nosotros. No se obtiene dinámicamente.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ CARDINALITY(test_temp 10000) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 10000 | 126K| 11 |
--------------------------------------------------------------------

3.- Usar DBMS_STATS.SET_TABLE_STATS

Por último, podemos utilizar el paquete DBMS_STATS, para cargar estadísticas representativas de la tabla temporal. El procedimiento SET_TABLE_STATS no analiza la tabla en cuestión, sino que setea las estadísticas de la tabla como nosotros queramos.
Podemos realizar ésto cuando creamos la tabla o luego de cargar datos en la tabla.
Si sabemos la cantidad de registros que puede llegar a tener la tabla temporal, podemos ejecutar éste procedimiento y setear las estadísticas a un valor representativo en base a los registros de la tabla.
Algo muy importante a tener en cuenta es que la ejecución de éste procedimiento implica un COMMIT implícito; por lo que tenemos que tener cuidado en el momento en el cual lo ejecutemos. Podrían ejecutarlo dentro de un procedimiento utilizando AUTONOMOUS_TRANSACTION para que no cree ningún tipo de conflictos.

SQL_9iR2> EXEC dbms_stats.set_table_stats( USER, 'TEST_TEMP', numrows => 10000 ) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 10000 | 126K| 11 |
--------------------------------------------------------------------

12 comentarios:

Anónimo dijo...

Excelente Blog que te felicito mucho, espero puedas poner algo de como usar tablas temporales dentro de stores procedures.
Saludos
Mario Cruz

Anónimo dijo...

Me ha gustado mucho la explicación sobre la estadisticas de tablas temporales . Lo de cardinalyty , es muy dificil de implementar porque te obliga a poner un hint diferente cada vez . Lo del dynamic sampling esta mucho mejor no ?

Gracias
Arnaud (Barcelona )

Rodo dijo...

Excelente blog.
Te puedo pedir que me envies a rcandido@gmail.com el contenido de este escript que mencionas en las notas "@explains"
gracias
Saludos

Rodo dijo...

Leonardo execelente las notas que publicas, son muy claras, te podria pedir el scripts @explains ?
Si no te molesta enviarmelo a rcandido@gmail.com

gracias
saludos

Leonardo Horikian dijo...

Hola Rodo, el script EXPLAINS.SQL que uso es el siguiente:

SET LINESIZE 140
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF

SELECT plan_table_output
FROM table(dbms_xplan.display('plan_table',null,'serial'))
/

SET FEEDBACK ON
SET TIMING ON

Anónimo dijo...

Estoy tratando de trabajar con tablas temporales dentro de un procedimiento pero no he logrado hacerlo. Tienes algun ejemplo sobre su uso dentro de un procedimiento.

Saludos,

Leonardo Horikian dijo...

Hola, por favor comentame mas en detalle que necesitas hacer con la tabla temporal en el procedimiento para poder ayudarte mejor.

Saludos.

Saga dijo...

Buen día a todos, Pregunta para Leonardo Horikian, estoy tratando de analizar las ventajas y desventajas de uso de cursores contra tablas temporales, el objetivo es enviarle a los desarrolladores cientos de registros con los que ellos pintaran su aplicación. Al día de hoy se trabaja con cursores pero considerando que la aplicación será para mas de 1000 usuarios que podrían llegar a ser concurrentes, cuál es la opción que debo seguir. Por lo que en base a tu experiencia que recomendarías?? Saludos

Anónimo dijo...

no entiendo el sentido de count(1) en una consulta, por lo que lei no tiene sentido, es asi?
ademas, me podras ayudar con algo, necesito aprender a crear procedimientos y trigeers simples, y ejercicios resueltos sobre esto y sobre optimizacion de consultas, me podras recomendar algo? gracias.

Leonardo Horikian dijo...

Hola, el count(1) es exactamente igual a poner count(*). Muchas personas usan count(1) porque piensan que es más performante, pero en realidad son iguales.

Te dejo algunos links que te van a ser de muchisima utilidad:

- http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
- http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm
- http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

Saludos

Anónimo dijo...

muchas gracias por la respuesta al count(1)

Leonardo Horikian dijo...

Hola Saga,

Para poder ayudarte necesito más detalle al respecto. Qué tipo de sentencias ejecutás en los cursores? En esta sentencia estas loqueando explícitamente los registros resultantes del cursor? Porqué queres utilizar tables temporales? Cuál es el problema con el uso de cursores?

De todas maneras te comento lo siguiente. Yo personalmente no utilizo tablas temporales en aplicaciones concurrentes. Me gustaría entender más tu preocupación en los referente a los cursores.

Saludos