Páginas

quinta-feira, 24 de maio de 2012

Índices e Chaves Estrangeiras


Vamos a mais um post sobre uma falha de arquitetura que muitas vezes não é percebida pelo desenvolvedor ou DBAs envolvidos no desenvolvimento de uma nova aplicação, ou mesmo de uma aplicação já operacional.

A algumas semanas estava dando manutenção em um sistema e me chamou a atenção o fato de todas as chaves estrangeiras estarem desabilitadas, com a justificativa de que elas tornavam o sistema muito mais lento.

Não vou nem entrar na discução a respeito da questão sobre performance (ficará para um próximo post, mas caso não se queira fazer uso dos recursos de integridade de dados de um banco de dados Relacional, então não use um banco de dados Relacional, existem diversas opções de bancos de dados NoSQL disponíveis no mercado e eles também implementam mecanismos de controle de concorrência e integridade de dados).

Bom, voltando ao assunto, no caso em particular percebi que ao habilitar as constraints que a lentidão ocorria durante o delete de registros em algumas tabelas, onde ocorriam diversos "table locks" nas tabelas filhas, e já me ocorreu, "estão faltando índices".

A documentação do Oracle Database, já fala sobre índices e chaves estrangeiras:
  1. Evitar contenção completa da tabela (lock table) filha em caso de "delete" na tabela pai; 
    • O índice permite o lock na tabela filha como "row lock";
    • Updates na chave primária da tabela pai também caem nesta mesma situação, mas alertando que alterações de chave primária serem consideradas uma má prática;
  2. Evita "full table scan" desnecessários na tabela filha; 
Os índices apenas podem ser ignorados caso a tabela pai não sofra operações de "update", e nem mesmo de "delete" (caso raro).

A algum tempo atrás eu já havia construido uma consulta para exibir todos os índices não existentes para as FKs criadas no esquema do usuário corrente, já gerando os comandos de criação dos índices. Segue:

set lines 180
set pages 10000
col command for a180

alter session set optimizer_features_enable='10.2.0.1';

SELECT 'CREATE INDEX ' || cons.constraint_name || ' ON ' || cons.table_name || '(' || cons.columns || ') TABLESPACE &tablespace;' command
FROM (SELECT constraint_name, table_name,
           LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
      FROM (SELECT dcc.constraint_name,
                   dcc.table_name,
                   dcc.column_name,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) AS curr,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) -1 AS prev
            FROM  user_cons_columns dcc
            INNER JOIN user_constraints dc
                    ON dc.owner = dcc.owner
                   AND dc.constraint_name = dcc.constraint_name
                   AND dcc.table_name = dc.table_name
            WHERE dc.constraint_type = 'R')
    GROUP BY constraint_name, table_name
    CONNECT BY prev = PRIOR curr AND constraint_name = PRIOR constraint_name
    START WITH curr = 1) cons
WHERE NOT EXISTS (SELECT 1 
        FROM 
          (SELECT index_name, table_name,
                  LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
             FROM (SELECT icc.index_name,
                          icc.table_name,
                          icc.column_name,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) AS curr,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) -1 AS prev
                    FROM  user_ind_columns icc)
             GROUP BY index_name, table_name
             CONNECT BY prev = PRIOR curr AND index_name = PRIOR index_name
             START WITH curr = 1) indx
        WHERE cons.table_name = indx.table_name
          AND cons.columns = indx.columns)
ORDER BY cons.table_name;

Prova de conceito

Caso algum developer teimoso ainda queira argumentar a respeito, bom eis então uma prova de conceito sobre o uso de índices em chaves estrangeiras.

Primeiro devemos criar 2 tabelas grandes, onde a tabela BIGTAB2 (com 1 milhão de registros) fará referencia à tabela BIGTAB1 com a clausula "on delete cascade" (para remover automaticamente registros da tabela BIGTAB2 caso o registro pai tenha sido removido na tabela BIGTAB1).

-- Cria tabela PAI
 
create table bigtab1
as
select rownum id, a.*
  from all_objects a
where 1=0;
 
alter table bigtab1 add  primary key (id) using index tablespace USERS;
 
insert into bigtab1
select rownum,
       owner, object_name, subobject_name,
       object_id, data_object_id,
       object_type, created, last_ddl_time,
       timestamp, status, temporary,
       generated, secondary
  from all_objects;
commit;
 
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB1', cascade=>TRUE);
 
-- Cria tabela FILHA
 
create table bigtab2
as
select a.*,
       rownum parent_id
  from bigtab1 a
where 1=0;
 
alter table bigtab2 add foreign key (parent_id) references bigtab1(id) on delete cascade;
 
declare
    l_cnt  number := 0;
    l_rows number := 1000000;
begin
    -- Generate Rows
    while (l_cnt < l_rows)
    loop
        insert into bigtab2
        select ID+l_cnt, -- Nova chave unica
               owner, object_name, subobject_name,
               object_id, data_object_id,
               object_type, created, last_ddl_time,
               timestamp, status, temporary,
               generated, secondary,
               ID   -- Mantem referencia ao pai na tabela bigtab1
          from bigtab1
         where id <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);

Veja que apenas a chave primária, coluna ID, da tabela BIGTAB1 esta indexada.

Delete sem índice na FK


Agora façamos um teste de deleção na tabela BIGTAB1 gerando trace da execução.

ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_1";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
 
delete from bigtab1 where id between 1 and 100;
 
rollback;


Na execução do TKPROF usamos o parâmetro "sys=yes" para que a deleção dos registros na tabela filha seja exibido no arquivo de saída, assim como o "aggregate=yes" para agregar os deletes.


tkprof rddbdev_ora_22603_DBA_RAFAEL_1.trc trace_delete_1.txt explain=DBA_RAFAEL sys=YES aggregate=yes waits=yes sort=prsela,exeela,fchela


Como podemos ver abaixo a deleção da tabela pai possui o tempo total de execução de 0.04 segundos e plano de execução usando INDEX RANGE SCAN da chave primária, tal como já era de se esperar:


delete from bigtab1
where
id between :"SYS_B_0" and :"SYS_B_1"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.04          0          2        517         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.04          0          2        517         100

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67  (DBA_RAFAEL)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB1 (cr=1520802 pr=0 pw=0 time=7588432 us)
    100   FILTER  (cr=2 pr=0 pw=0 time=829 us)
    100    INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=32 us)(object id 220499)

Se pode perceber o alto custo (CR) e tempo (TIME) associados a operação de DELETE no plano de execução, isto se deve devido ao cascade, uma vez que a deleção dos registros na tabela filha é feito pelo próprio oracle, pois a constraint existe com a clausula "on delete cascade".

Como podemos ver abaixo são feitas mais de 100 execuções (uma para cada chave na tabela pai) do delete, com tempo total de execução de 7.54 segundos, e também podemos observar no plano de execução que é feito um FULL TABLE SCAN na tabel BIGTAB2 a cada execução.


delete from "DBA_RAFAEL"."BIGTAB2"
where
"PARENT_ID" = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      7.72       7.54          0    1520800       1688        1500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      7.72       7.54          0    1520800       1688        1500

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB2 (cr=1520800 pr=0 pw=0 time=7538904 us)
   1500   TABLE ACCESS FULL BIGTAB2 (cr=1520800 pr=0 pw=0 time=7149249 us)





Delete com índice na FK


Com a execução da consulta para criar os índices de acordo com as chaves estrangeiras foi recomendada a criação do seguinte índice:


COMMAND
-------------------------------------------------------------------
CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS;


Após criado o índices executamos novamente a mesma operação de delete.

CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS; 
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);

ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_2";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

delete from bigtab1 where id between 1 and 100;
rollback;

Agora, com tempo de execução menor, a saída do TKPROF nos aponta um custo e tempo muito menor em ambas as operações de delete.

delete from bigtab1
where
 id between :"SYS_B_0" and :"SYS_B_1"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2        615         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          2        615         100


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67  (DBA_RAFAEL)


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB1 (cr=304 pr=0 pw=0 time=50849 us)
    100   FILTER  (cr=2 pr=0 pw=0 time=229 us)
    100    INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=25 us)(object id 220499)


E para o "delete cascade" temos a mesma deleção usando PARENT_ID só que desta vez com uso do índice recem criado, e com uma quantidade muito inferior de LIOs (302) e por consequencia um custo de execução muito menor em relação ao plano de execução do teste anterior com mais 1.5 milhões de LIOs. Diminuindo de 7.54 para 0.04 o tempo de execução do delete na tabela filha.


delete from "DBA_RAFAEL"."BIGTAB2"
where
 "PARENT_ID" = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.04       0.04          0        302       6226        1500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.04       0.04          0        302       6226        1500


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB2 (cr=302 pr=0 pw=0 time=42640 us)
   1500   INDEX RANGE SCAN SYS_C00167103 (cr=302 pr=0 pw=0 time=3648 us)(object id 220502)



Neste exemplo temos então as evidências da melhoria de performance para menos de 1% do tempo de execução anterior.