Otimização de comandos DDL

Por Mohamed Houri e Alex Zaballa
Postado em Junho 2014

Introdução

A partir do Oracle 11g, foi introduzida uma nova forma de otimização para melhorar o desempenho das operações de Data Definition Language. Quando você adiciona uma coluna obrigatória a uma tabela existente e, ao mesmo tempo você gostaria de atribuir um valor default para esta coluna recém adicionada, uma nova forma de otimização para DDLs irá permitir que isso seja realizado instantaneamente. Como isso pode ser possível quando a tabela alterada tem milhões de registros que devem ter sua coluna recém adicionada atualizada com o valor default? E será que esta nova otimização não tem efeitos colaterais que devemos estar cientes antes de usá-la? Isto é o que vamos mostrar a você neste artigo.

Conceito

Considere a seguinte tabela, com 3 milhões de registros:

SQL> create table t1
  as select
  rownum n1
  , trunc  ((rownum-1)/3) n2
  , trunc(dbms_random.value(rownum, rownum*10)) n3
  , dbms_random.string('U', 10) c1
  from dual
  connect by level <= 3e6;
SQL>desc t1 Name              Null? Type ----------------------- --------- 1      N1             NUMBER 2      N2                NUMBER 3      N3                NUMBER 4      C1                VARCHAR2(4000 CHAR)

 

Tabela ao qual iremos adicionar uma coluna obrigatória com um valor default:

SQL> alter table t1 add C_DDL  number default 42 not null;

Estão marcadas em negrito as duas palavras cruciais default e not null, porque elas representam as palavras-chaves que impulsionam este novo recurso.
A fim de verificar a diferença no tempo de execução do comando ALTER TABLE acima, iremos executá-lo em duas versões diferentes do banco de dados Oracle, 10.2.0.4.0 e 11.2.0.3.0:


10.2.0.4.0 > alter table t1 add C_DDL number default 42 not null;

Table altered.
Elapsed: 00:00:48.53 11.2.0.3.0> alter table t1 add C_DDL number default 42 not null; Table altered.
Elapsed: 00:00:00.04

Observe a diferença nos tempos de execução. A coluna C_DDL foi adicionada instantaneamente no banco de dados 11gR2 enquanto levou quase 49 segundosno 10gR2.

O que seria esse novo mecanismo que permite um tempo de execução extremamente rápido quando há a adição de uma coluna obrigatória com o valor default para uma tabela existente?

Como poderiam 3 milhõesde registrosserem atualizados em 4 milissegundos?

Vamos verificar visualmente se a atualização foi realmente feita (a partir de agora, quando a versão do Oracle não for especificada estaremos nos referindo a versão a 11.0.2.3).

SQL> select count(1) from t1;

COUNT(1)
----------
3000000
SQL>select count(1) from t1 where c_ddl = 42;
COUNT(1) ---------- 3000000

Embora o Oracle tenha alterado a tabela t1 instantaneamente, a consulta está mostrando que a coluna C_DDL foi atualizada com o seu valor default 42.
Como isso pode ser possível?
Será que o plano de execução pode nos ajudar a desvendar isso?

SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id  |  Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT  STATEMENT   |      |        |       |  3016 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   2999K|  8788K|  3016    (5)| 00:00:10 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)

Observe novamente que o predicado do plano de execução acima, revelou informações vitais para ajudar a entender o que está acontecendo. Apesar de não termos usado a função NVL na consulta, esta aparece na parte do predicado, indicando que internamente o Oracle está mostrando que a coluna C_DDL pode conter valores nulos (o que significa que não foi atualizada) e, como tal, o Oracle está substituindo o valor nulo, por seu valor default 42.

Temos a versão anterior para comparar e verificar a diferença:

10.2.0.4.0>select count(1) from t1 where c_ddl =  42;
COUNT(1)
----------
3000000
10.2.0.4.0> select * from  table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id  |  Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT  STATEMENT   |      |        |       |  4001 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |           |
|*  2 |   TABLE ACCESS FULL| T1   |   3000K|  8789K|  4001    (8)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  filter("C_DDL"=42)

A ausência da função NVL no predicado, juntamente com o tempo que levou para adicionar a coluna no 10gR2 (00:00:48.53) explicam o conceito introduzido no 11gR1 para otimizar a adição de colunas obrigatórias a uma tabela existente.

Simplificando, a partir do Oracle 11gR1, quando você adiciona uma coluna obrigatória com um valor default, o Oracle não vai atualizar todas as linhas existentes com este valor default. Em vez disso, o Oracle vai armazenar um metadado para esta nova coluna (obrigatória e com valor default 42) e vai permitir que a coluna seja adicionada quase instantaneamente, independente do tamanho da tabela alterada. Claro que isto é possível, ao custo da adição de uma função NVL ao recuperar a coluna adicionada a partir de um bloco da tabela.
Depois de ter explicado este conceito de otimização de DDLs, na próxima seção, iremos investigar um pouco sobre como que esse recurso é gerido pelo Oracle para garantir a rapidez de DDLs e a garantia de resultados corretos e de elevada performance durante a recuperação de dados. Vamos ver particularmente a diferença que existe entre a obtenção da coluna adicionada a partir de um bloco da tabela, em contraste com quando a mesma coluna é trazida por meio de um bloco do índice.

O mecanismo de trabalho

Na tabela alterada

Vimos acima que ganhamos desempenho ao adicionar uma coluna obrigatória com o valor default. Mas vimos também que isso foi possível porque o Oracle adiciona uma função NVL, que é aplicada para a coluna adicionada para que os valores nulos da coluna C_DDL sejam iguais ao seu valor default, graças aos metadados armazenados no dicionário de dados.
Será que este uso implícito da função NVL introduz algum efeito colateral?
Bem, em primeiro lugar, vimos acima que este não tem influência sobre as estimativas feitas pelo CBO, pois ele está estimando com precisão o número de linhas a serem geradas, conforme mostrado abaixo:

SQL> select /*+ gather_plan_statistics */ count(1)  from t1 where C_DDL = 42;
COUNT(1)
----------
3000000
SQL> select * from  table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------
| Id  |  Operation          | Name | Starts |  E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT  STATEMENT   |      |       1 |        |      1 |00:00:00.37 |
|   1 |  SORT AGGREGATE    |       |      1 |      1 |       1 |00:00:00.37 |
|*  2 |   TABLE ACCESS FULL| T1   |       1 |   2999K|   3000K|00:00:00.44 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter(NVL("C_DDL",42)=42)

Mas há um tempo extra (44ms), quando completa um full scan nos blocos da tabela, provavelmente devido ao novo filtro que utiliza a função NVL, quando comparado com o tempo de execução da mesma operação na versão anterior (5ms):

10.2.0.4.0> select /*+ gather_plan_statistics */  count(1) from t1 where C_DDL = 42
COUNT(1)
----------
3000000
10.2.0.4.0> select * from  table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------
| Id  |  Operation          | Name | Starts |  E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |       |      1 |      1 |       1 |00:00:01.06 |
|*  2 |   TABLE ACCESS FULL| T1   |       1 |   3000K|   3000K|00:00:00.05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  filter("C_DDL"=42)

Na colunaC_DDLindexada

Quando uma função é aplicada a uma coluna que figura na parte do predicado, impedirá o uso de qualquer índice que possa existir nesta coluna.
Neste caso particular, a função NVL que é aplicada à coluna C_DDLimpediráque um índice seja usado pelo CBO se esta coluna for indexada? Isso é o que vamos ver aqui.

Considere o seguinte índice:

SQL> create index i1_c_ddl on t1(c_ddl);
Index created.
Elapsed: 00:00:02.14

Executando a querynovamente:

SQL> select /*+ gather_plan_statistics */ count(1)  from t1 where C_DDL = 42;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS  LAST’));
---------------------------------------------------------------------------------
| Id  |  Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time    | 
----------------------------------------------------------------------------------
|   0 | SELECT  STATEMENT      |          |       1 |        |      1 |00:00:00.47 |
|   1 |  SORT AGGREGATE       |          |       1 |      1 |      1 |00:00:00.47 |
|*  2 |   INDEX FAST FULL SCAN| I1_C_DDL |      1 |    2999K|   3000K|00:00:00.75 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  filter("C_DDL"=42)

Há boas notícias para enfatizar aqui: a função NVL oculta não é aplicada a coluna C_DDL ao recuperar seu valor a partir do bloco de um índice, o que explica por que o índice foi usado pelo CBO.

Mas você poderia argumentar e dizer que isso é um comportamento normal: um índice não pode conter valores nulos. Então vamos criar um índice multi-column composto com uma coluna obrigatória para proteger os valores não nulos da coluna C_DDL:

SQL> drop index i1_c_ddl;
Index dropped.
SQL> alter table t1 modify n1 not null;
Table altered.
SQL> create index i2_n1_c_ddl on t1(n1,c_ddl);
Index created.
SQL> select /*+ gather_plan_statistics */ count(1)  from t1 where n1= 100 and C_DDL = 42;
COUNT(1)
----------
1
---------------------------------------------------------------------------------
| Id  |  Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time    |
---------------------------------------------------------------------------------
|   0 | SELECT  STATEMENT  |             |      1 |         |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE   |              |      1 |      1 |       1 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN| I2_N1_C_DDL |      1 |       1 |      1 |00:00:00.01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  access("N1"=100 AND "C_DDL"=42)

Mesmo quando a coluna C_DDL adicionada é protegida contra os valores nulos por sua presença num índice composto, não há qualquer vestígio da função oculta NVL aplicada à coluna C_DDL. Isto demonstra claramente que, em contraste com os blocos da tabela, onde não há atualização da coluna C_DDL, um índice que é criado na mesma coluna verá seus blocos imediatamente sendo preenchidos pelo valor padrão da coluna C_DDL.

Antes de terminar esta seção, vamos mostrar uma questão mais interessante. Vimos até agora que, cada vez que o CBO decidiu visitar um bloco de tabela, é aplicada a função NVL para a coluna C_DDL, a fim de garantir a recuperação de valor C_DDL não nulo. Mas temos visto que este filtro é sempre aplicado quando a tabela é totalmente acessada (TABLE ACCESS FULL). Será que o CBO irá aplicar esta função NVL quando a tabela t1 for acessada através de índice (TABLE ACCESS BY INDEX ROWID)? Vamos projetar um caso simples e observar a reação CBO nesta situação particular:

SQL> drop index i2_n1_c_ddl;
SQL> create index i2_n1_c_ddl on t1(n1);
SQL> select /*+ gather_plan_statistics */ count(1)  from t1 where n1= 100 and C_DDL = 42;
-------------------------------------------------------------------------------
| Id  |  Operation                    | Name        | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
|   0 | SELECT  STATEMENT             |             |      1 |         |      1 |
|   1 |  SORT AGGREGATE              |             |      1 |       1 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1          |       1 |      1 |      1 |
|*  3 |    INDEX RANGE SCAN          | I2_N1_C_DDL |      1 |       1 |      1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  filter(NVL("C_DDL",42)=42)
  3 -  access("N1"=100)

A função NVL também é aplicada na coluna C_DDL mesmo quando a tabela t1 é visitada por meio deindex rowid.

Estamos agora confiantes para dizer que cada vez que o CBO visita um bloco de tabela, sendo através de um único bloco ou uma leitura multi-bloco, ele vai aplicar a função NVL para qualquer coluna "DDL otimizada" que tem que filtrar os blocos da tabela. No entanto, o CBO não aplica a função NVL para a coluna "DDL otimizada", se este é adquirido a partir de um bloco de índice.

Oracle 12c and DDL optimization for NULL columns

Com a chegada do 12c, podemos nos questionar se a otimização DDL ainda está disponível nessa versão ou não. Uma imagem vale mais do que mil palavras, então vamos repetir a mesma experiência nesta versão também:

12c > alter table t1 add C_DDL number default 42  not null;
Elapsed: 00:00:00.02

Quase instantâneo. A otimização de DDLs como demonstrado, mais uma vez através do uso da função NVL na parte do predicado da seguinte consulta:

12c> select count(1) from t1 where c_ddl=42;

COUNT(1)
----------
3000000

12c> select * from  table(dbms_xplan.display_cursor);

---------------------------------------------------------------------------
| Id  |  Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT  STATEMENT   |      |        |       |  3802 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   3538K|    43M|  3802    (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter(NVL("C_DDL",42)=42)

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

Mas há, ainda, um pouco além da optimização DDL na versão 12c, quando comparado com a versão 11gR1. No banco de dados 12c, a otimização DDL foi estendida para incluir colunas não obrigatórias com valor default. Considere a seguinte alteração na tabela feito no 11gR2 e 12c, respectivamente, a fim de verificar claramente a diferença:

11.2.0.3.0> alter table t1 add C_DDL_2 number  default 84;

Table altered.
Elapsed: 00:00:58.25

12c> alter table t1 add C_DDL_2 number default 84;

Elapsed: 00:00:00.02 

Ao adicionar a coluna não obrigatória C_DDL_2 levou 58 segundo para terminar o comando 11gR2 e foi instantaneo no 12c.

Esta é uma demonstração clara de que na base de dados 12c, a otimização de DDLs foi estendida para incluir colunas não obrigatórias que têm um valor default. Na verdade, quando você consultar a tabela t1 para obter os valores distintos da coluna recém adicionada (C_DDL_2) você vai perceber que as linhas da tabela inteiras buscam o valor no seu metadado (valor padrão 84), conforme mostrado através da seguinte consulta:

12c> select c_ddl_2, count(1) from t1 group by  c_ddl_2;

C_DDL_2 COUNT(1)
------- ----------
84      3000000

SQL> select count(1) from t1 where c_ddl_2=84;

COUNT(1)
----------
3000000

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

---------------------------------------------------------------------------
| Id  |  Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT  STATEMENT   |      |        |       |  3803 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   3538K|    43M|  3803    (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
  C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84)

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

No entanto, a fim de garantir a otimização de DDLs para colunas NULL com valor default, as coisas tornaram-se mais complexas do que costumavam ser para colunas obrigatórias na versão anterior. Passamos de um uso implícito simples da função NVL para uma parte do predicado mais complexo envolvendo a função não documentadaSYS_OP_VECBITdo Oracle e uma nova coluna SYS_NC00006$ interna, a fim de trazer o valor padrão já que este não foi atualizado fisicamente.

Ao contrário do que se poderia pensar de imediato, a coluna SYS_NC00006$ não é uma coluna virtual. Ela representa uma coluna de sistema oculta, como mostrado abaixo:

12c>  SELECT
  column_name
  ,virtual_column
  ,hidden_column
  ,user_generated
  FROM   
  user_tab_cols
  WHERE table_name = 'T1'
  AND    column_name = ‘SYS_NC00006$’;

COLUMN_NAME          VIR HID USE
--------------------  --- --- ---
SYS_NC00006$         NO   YES NO

Apesar da coluna ser oculta, isso não nos impede de selecioná-la:

12c>select 
  a.c_ddl_2
  ,a.SYS_NC00006$ 
  from t1 a 
  where c_ddl_2 =84 
  andrownum<=5;

C_DDL_2 SYS_NC00006$
------- ------------
     84
     84
     84
     84
     84             

A coluna SYS_NC00006$ permanecerá nula até que seja atribuído um valor que seja diferente do default para a coluna C_DDL_2.Considere os seguintes inserções:

12c> insert into t1 values (0,0,0,'xxxxx',110,130);

1 row created.

12c> insert into t1 values (1,1,1,'xxxxx',140,150);

1 row created.

12c> insert into t1 values  (1,1,1,'xxxxx',200,null);

12c> select 
  a.c_ddl_2
  ,a.SYS_NC00006$ 
  from t1 a 
  wherea.c_ddl_2 in (130,150);

C_DDL_2 SYS_NC00006$
------- ------------
    130 01
    150 01

SQL> select 
  a.c_ddl_2
  ,a.SYS_NC00006$ 
  from t1 a 
  wherea.c_ddl_2 is null;

C_DDL_2 SYS_NC00006$
------- ------------
        01

Observe como o valor da coluna oculta SYS_NC00006$ não é mais NULL quando inserimos um valor diferente dodefault na coluna C_DDL_2 (incluindo o valor NULL explícito).

Juntando as diferentes peças, podemos facilmente compreender o que o predicado reproduzido abaixo está fazendo exatamente:

Predicate Information (identified by operation id):
---------------------------------------------------
  2 -  filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
  C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84)

O Oracle está simplesmente verificandona sua coluna de sistema gerada através da função SYS_OP_VECBIT se considera o valor padrão da coluna C_DDL_2 ou o valor real introduzido por um usuário final ou através de uma instrução de inserção explícita. Vamos imitar o que o Oracle está fazendo com os nossos SYS_NC00006$ acima dos valores de coluna, ou seja,'01'e NULL:

12c>SELECT
  a.c_ddl_2
  ,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0))  cbo_ddl
  FROM t1 a
  WHERE  a.c_ddl_2 IN (130,150)
  UNION ALL
  SELECT
  a.c_ddl_2
  ,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
  FROM t1 a
  WHERE  a.c_ddl_2 IS NULL
  UNION ALL
  SELECT
  a.c_ddl_2
  ,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
  FROM t1 a
  WHERE  c_ddl_2 =84
  AND  rownum<=1
  order by c_ddl_2 nulls last
  ;    

C_DDL_2     CBO_DDL
----------  ---------
        84  {null}
       130  1
       150  1
    {null}  1

Há quatro valores distintos na coluna C_DDL_2, o default (84) e três valores explicitamente inseridos 130,150 e nulo. Quando você usa um predicado na coluna C_DDL_2 para recuperar uma linha a partir de um bloco de tabela, o Oracle CBO irá decodificar o valor de CBO_DDL acima (com base em SYS_NC00006$) para verificar seu valor contra o seu vínculo de entrada (ou literal) variável. Como tal, pode imitar correctamente todos os valores da coluna C_DDL_2 incluindo aqueles que têm um valor default (84) e que não tenham sido fisicamente actualizadas para reflectir este valor default.

Conclusion

O Oracle 11gR1 veio com uma nova funcionalidade que faz com que não nos preocupemos ao adicionar uma coluna obrigatória com o valor default para uma tabela grande. Esse recurso, chamado de otimização de DDLs, permite que a adição da coluna na tabela seja instantâneo e também sem a necessidade de bloquear a tabela. O Oracle 12c estendeu esse recurso para incluir colunas não obrigatórias com valor padrão. A melhor parte, é que não parece haver nenhum efeito colateral consideravel no desempenho durante a recuperação da coluna alterada.



Mohamed Houri tem PhD em Mecânica dos Fluidos (Computação Científica) pela Universidade de Aix-Marseille II, precedido por um diploma em engenharia aeronáutica. Ele tem trabalhado com banco de dados Oracle por mais de 14 anos para diferentes clientes europeus como consultor independente especializado em Tuning e problemas de desempenho. Mohamed também trabalhou como arquiteto para a naval Society of Japan na análise de tsunamis e ondas, utilizando uma análise de sinal poderosa chamada Wavelet Transform. Ele mantém um blog sobre Oracle e é muito ativo no fórum da Oracle Worldwide e no equivalente francês. Seu twiiter é @MohamedHouri. Mohamed é membro do time OraWorld (www.oraworld-team.com).

Alex possui mais de 14 anos de experiência com tecnologias Oracle, é DBA sênior em Luanda e trabalha para o Ministério das Finanças de Angola. Iniciou sua carreira como desenvolvedor Oracle, participando do desenvolvimento de um software ERP desde a definição até a implantação. Alex é Oracle ACE e também um membro do seleto grupo de profissionais altamente qualificados em todo o mundo que têm a credencial OCM - Oracle Certified Master. Entre suas especialidades incluem projeto e implementação de alta escalabilidade, alta disponibilidade e ajuste de desempenho. Alex Zaballa é membro do time OraWorld (www.oraworld-team.com).