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