12c - Statistics During Loads

No Oracle Database 12c, foi introduzida uma nova feature, Online Statistics Gathering for Bulk Loads.

O banco de dados irá coletar estatísticas automaticamente das tabelas durante os seguintes tipos de operações bulk load:

• CREATE TABLE AS SELECT

• INSERT INTO ... SELECT into an empty table using a direct path insert

Por padrão, parallel inserts usam direct path insert. Podemos forçar o direct path insert usando o hint  / * + APPEND * /.

Lembrando que ao coletar as estatísticas, o banco de dados não irá gerar histograms e nem coletar estatísticas para índices existentes. Para isso é necessário utilizar o DBMS_STATS.

Para a criação de índices, desde a versão 10G o Oracle automaticamente coleta as estatísticas do índice após a sua criação.

Para voltar a ter o comportamento anterior à release 12c e não coletar as estatísticas, podemos utilizar o hint NO_GATHER_OPTIMIZER_STATISTICS.

SQL> create table tabela_teste
(
coluna1 number,
coluna2 number
);
Table created.

SQL> begin
for i in 1..1000
loop
insert into tabela_teste values(i, i);
end loop;

commit;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from tabela_teste;
COUNT(*)
----------
1000

Podemos verificar que não existem estatísticas, pois foi um insert normal:

SQL> select table_name, num_rows, last_analyzed from user_tables where table_name='TABELA_TESTE';

TABLE_NAME                     NUM_ROWS   LAST_ANALYZED
------------------------------ ---------- -------------
TABELA_TESTE

Agora utilizando CTAS:

SQL> create table tabela_teste2 as select * from tabela_teste;
Table created.

SQL> select table_name, num_rows, last_analyzed from user_tables where table_name='TABELA_TESTE2';

TABLE_NAME                     NUM_ROWS   LAST_ANALYZED
------------------------------ ---------- -------------
TABELA_TESTE2                  1000       16-APR-14

Utilizando direct path insert:

SQL> create table tabela_teste3
(
  coluna1 number,
  coluna2 number
);
Table created.
 
SQL> insert /*+ APPEND */ into tabela_teste3 select object_id,object_id from user_objects where rownum < 30;
25 rows created.
 
SQL> select table_name, num_rows, last_analyzed from user_tables where table_name='TABELA_TESTE3';
 
TABLE_NAME                           NUM_ROWS         LAST_ANALYZED
------------------------------       ----------        -------------
TABELA_TESTE3                               25         16-APR-14 10:20:00
 
 

Quando a tabela não está vazia, as estatísticas não são atualizadas:

SQL> insert /*+ APPEND */ into tabela_teste3 select object_id,object_id from user_objects where rownum < 30;
25 rows created.

SQL> select table_name, num_rows, last_analyzed from user_tables where table_name='TABELA_TESTE3';

TABLE_NAME                     NUM_ROWS   LAST_ANALYZED
------------------------------ ---------- -------------
TABELA_TESTE3 25               16-APR-14  10:20:00

Verificando o plano de execução:

SQL> explain plan for
create table tabela_teste4 as select * from tabela_teste;

Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'BASIC'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1183779688

------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | |
| 1 | LOAD AS SELECT | TABELA_TESTE2 |
| 2 | OPTIMIZER STATISTICS GATHERING | |
| 3 | TABLE ACCESS FULL | TABELA_TESTE |
------------------------------------------------------------------

10 rows selected.

Obtendo informações sobre a coleta automática de estatísticas:

SQL> select table_name, column_name, num_distinct, notes from user_tab_col_statistics where table_name = ('TABELA_TESTE3');

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NOTES
-------------------- -------------------- ------------ ------------------------------
TABELA_TESTE3        COLUNA1              25            STATS_ON_LOAD
TABELA_TESTE3        COLUNA2              25            STATS_ON_LOAD

Para restrições na coleta automática, consultar a referência abaixo.

Referências:

 http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_statscon.htm#TGSQL344

Abraço,

Alex Zaballa