Estatísticas em Global Temporary Tables no Oracle Database

Session private statistics for Global Temporary Tables

No Oracle Database 12c, foi introduzida uma nova funcionalidade, que permite gerar estatísticas para Global Temporary Tables. Estas estatísticas existirão apenas para a sessão que irá gerá-las.

Estatísticas em Global Temporary Tables podem ser um grande problema, porque em muitos casos, cada sessão tem um conjunto de dados completamente diferente de outra.

Por default, cada sessão no Oracle Database 12c terá seu próprio conjunto de estatísticas em Global Temporary Tables, permitindo que o otimizador estime corretamente as cardinalidades e gere um plano mais correto.

Para se ter shared  statistics (default no 11G), é necessário trocar a preferência da tabela GLOBAL_TEMP_TABLE_STATS para SHARED.

Verificando o default:

SQL> create global temporary table gtt_teste(coluna1 number,coluna2 varchar2(100)) ON COMMIT PRESERVE ROWS;

Table created. 

SQL> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS','SCOTT','GTT_TESTE') FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','SCOTT','GTT_TESTE')
--------------------------------------------------------------------------------
SESSION

Alterando para SHARED:

SQL> BEGIN
dbms_stats.set_table_prefs('SCOTT','GTT_TESTE','GLOBAL_TEMP_TABLE_STATS','SHARED');
END;
/

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS','SCOTT','GTT_TESTE') FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','SCOTT','GTT_TESTE')
--------------------------------------------------------------------------------
SHARED

Voltando ao default e carregando dados:

BEGIN
dbms_stats.set_table_prefs('SCOTT','GTT_TESTE','GLOBAL_TEMP_TABLE_STATS','SESSION');
END;
/

PL/SQL procedure successfully completed.

SQL> INSERT INTO gtt_teste (SELECT object_id,object_name FROM all_objects WHERE rownum <=1000);

1000 rows created.

Coletando as estatísticas:

SQL> BEGIN
dbms_stats.gather_table_stats( user, 'GTT_TESTE' );
END;
/

PL/SQL procedure successfully completed.

Como podemos ver, as estatísticas são válidas para a sessão e representam o número de registros que carregamos:

SQL> select scope, num_rows
 from user_tab_statistics
 where table_name = 'GTT_TESTE';

SCOPE      NUM_ROWS
------- ----------
SHARED
SESSION       1000

Verificando o comportamento do otimizador ao estimar o número de registros:

SQL> insert into gtt_teste (select object_id,object_name FROM dba_objects);

91519 rows created.

SQL> create table tabela_teste(coluna1,coluna2) as select object_id,object_name from dba_objects where 1=2;

Table created.

SQL> insert into tabela_teste (select * from gtt_teste);

91519 rows created.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID    cqz0k3v81vwd7, child number 0
-------------------------------------

insert into tabela_teste (select * from gtt_teste)

Plan hash value: 1897894522
--------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |         |         |         |       2 (100)|       |
|   1 |  LOAD TABLE CONVENTIONAL |         |         |         |         |      |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL     | GTT_TESTE |       1 |      65 |       2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

Em outra sessão, agora coletando estatísticas:

SQL> insert into gtt_teste (select object_id,object_name FROM dba_objects);

91519 rows created.

SQL> BEGIN
dbms_stats.gather_table_stats( user, 'GTT_TESTE' );
END;
/

PL/SQL procedure successfully completed.

SQL> insert into tabela_teste (select * from gtt_teste);

91519 rows created.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID    cqz0k3v81vwd7, child number 0

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

insert into tabela_teste (select * from gtt_teste)

Plan hash value: 1897894522
--------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |         |         |         |     120 (100)|    |
|   1 |  LOAD TABLE CONVENTIONAL |         |         |         |             |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL     | GTT_TESTE | 91519 |    2681K|     120   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
  - Global temporary table session private statistics used

Abraço